PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
An open source relational database system
SQLite, MySQL, SQL Server, Oracle, Snowflake
What are some advantages of learning a relational database?
Enforces rules that the data you are storing is useful and can be queried
What is one way to see if PostgreSQL is running?
sudo service postgresql status
What is a database schema?
A collection of databases, it determines how data will be organized
What is a table?
A table is made up of rows of data that all have the same attributes represented as columns
What is a row?
A row is made up of data elements, one for each attribute
What is SQL and how is it different from languages like JavaScript?
Structured Query Language is used for accessing database.
How do you retrieve specific columns from a database table?
select “thing1”
How do you filter rows based on some specific criteria?
where
What are the benefits of formatting your SQL?
Easier to read
What are 4 comparison operators that can be used in a ‘where’ clause?
=, <, >, !=
How do you limit the number of rows returned in a result set?
limit -insert number-
How do you retrieve all columns from a database table?
select *
How do you control the sort order of a result set?
order by, then direction (ascd (a-z) or desc(z-a))
How do you add a row to a SQL table?
insert into
What is a tuple?
A grouping of data values, usually comma separated and enclosed in parentheses
How do you add multiple rows to a SQL table at once?
Use multiple tuples in the VALUES clause, separated by commas
How do you get back the row being inserted into a table without a separate ‘select’ statement?
At the end of the INSERT statement, include the RETURNING clause followed by the columns you want returned
How do you update rows in a database table?
update clause
Why is it important to include a ‘where’ clause in your ‘update’ statements?
If you don’t, all values in the specified columns in the table will be set to a single value
How do you delete rows from a database table?
delete statement
How do you accidentally delete all rows from a table?
If you leave off a WHERE clause, the delete operation will happen for every row in the table
What is a foreign key?
A column that contain values that refer to another column in another table, used to join the two tables together
How do you join two SQL tables?
After the FROM, use the JOIN clause followed by the table and the join methodFROM "table1" JOIN "table2" USING ("commonId")
We can use ON as well
How do you temporarily rename columns or tables in a SQL statement?
Give them an alias using AS after the column in the SELECT clause or the table in the FROM/JOIN
What are some examples of aggregate functions?
SUM, AVG, MIN, MAX, COUNT, STRING_AGG, JSON_AGG
What is the purpose of a ‘group by’ clause?
To indicate which of the columns that are not being aggregated should be used as unique groups to aggregate values up to
