PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
- Relational Database Management System
- MySQL, SQLite
What are some advantages of learning a relational database?
- Support good guarantees about data integrity
- Store and modify data in a way that makes data corruption as unlikely as possible
- Most widely used kind of database
What is one way to see if PostgreSQL is running?
- Running the top command and checking to see postgres processes
- sudo service postgresql status
What is a database schema?
-Collection of tables
What is a table?
- Data stored in relations in relational database
- List of rows each having the same set of attributes
What is a row?
-A row represents a complete record of a specific data
What is SQL and how is it different from languages like JavaScript?
- SQL is declarative programming language and JavaScript is imperative programming language
- Programmers describe the result
How do you retrieve specific columns from a database table?
-select statement
How do you filter rows based on some specific criteria?
-where clause
What are the benefits of formatting your SQL?
-Easy to find
What are four comparison operators that can be used in a where clause?
- , =, !=
How do you limit the number of rows returned in a result set?
-limit clause
How do you retrieve all columns from a database table?
-asterisk
How do you control the sort order of a result set?
-order by clause
How do you add a row to a SQL table?
-insert keyword
What is a tuple?
-One row
How do you add multiple rows to a SQL table at once?
-Specifying more than one tuple of values, separated by commas
How do you get back the row being inserted into a table without a separate select statement?
-return *
How do you update rows in a database table?
- update keyword and set keyword
- include a where clause
Why is it important to include a where clause in your update statements?
-If a where clause is missing, it would update every row in the table
How do you delete rows from a database table?
- delete from keyword
- where clause
How do you accidentally delete all rows from a table?
-Not including the where clause
What is a foreign key?
-An attribute in a table that refers to the primary key of another table
How do you join two SQL tables?
-join clause
How do you temporarily rename columns or tables in a SQL statement?
-Alias column names by using as keyword
What are some examples of aggregate functions?
- max()
- avg()
- sum()
What is the purpose of a group by clause?
-To separate rows into groups and perform aggregate functions on those groups
