PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
It is a relational database system. Alternatives include: SQLite, MySQL
What is one way to see if PostgreSQL is running?
sudo service postgresql status
What is a database schema?
A database schema is a collection of tables
What is a table?
A table is a list of rows each having the same set of attributes
What is a row?
A row is a collection of related data
What is SQL and how is it different from languages like JavaScript?
It is a declarative language like HTML, and CSS. Meaning: All we do is tell SQL what we want to happen and it forms the plan.
How do you retrieve specific columns from a database table?
You would retrieve it with: SELECT "columnName"
How do you filter rows based on some specific criteria?
You would use the WHERE keyword
What are the benefits of formatting your SQL?
Pure readability
What are four comparison operators that can be used in a where clause?
You can use:
- =
- !=
- <
- >
How do you limit the number of rows returned in a result set?
You would use the LIMIT keyword (ex. LIMIT 5
How do you limit the number of rows returned in a result set?
You would use the LIMIT keyword (ex. LIMIT 5)
How do you retrieve all columns from a database table?
You would retrieve it with: SELECT *
How do you control the sort order of a result set?
One can order it with the keywords ORDER BY "columnName" (DESC/ASC)
Where if none are specified it defaults to ASC
How do you add a row to a SQL table?
~~~
INSERT INTO (“attribute1”, …“attributeN”)
VALUES (“value1”, …“valueN”);
````
What is a tuple?
In SQL, a list of values is referred to as a tuple.
How do you add multiple rows to a SQL table at once?
One would simply list more tuples
How do you get back the row being inserted into a table without a separate select statement?
One can use the RETURNING statement followed by an * or any attributes desired
How do you update rows in a database table?
One would need to use the UPDATE keyword and then use SET to change the value (ex. `UPDATE artists
How do you update rows in a database table?
One would need to use the UPDATE keyword and then use SET to change the value (ex. UPDATE artists SET name='example')
Why is it important to include a where clause in your update statements?
So you can select what data is being updated as opposed to every row in the table
What is a foreign key?
A key that is used to link values from one table with another (ie. they intersect)
If a value is a foreign key in a table then it references a primary key in another table.
How do you join two SQL tables?
With the JOIN and USING keywords
How do you temporarily rename columns or tables in a SQL statement?
With the AS keyword
What are some examples of aggregate functions?
COUNT()MAX()MIN()SUM()AVG()
What is the purpose of a group by clause?
In order to group rows that we wish to report on
