PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
PostgreSQL is a free open source relational database management system
Alternative relational databases include: MySQL (also free), SQL Server by Microsoft, and Oracle by Oracle Corporation.
What are some advantages of learning a relational database?
- If you are storing related data, then a relational database is a good choice.
- Relational databases support good guarantees about data integrity.
- Relational databases are arguably the most widely used kind of database.
- And knowing the SQL language is a very portable skill given that there are so many relational databases driven by almost the same language (SQL)
What is one way to see if PostgreSQL is running?
- Use the terminal command sudo service postgresql status.
- Check use the terminal top command to see if postgres is running
What is a database schema?
A collection of tables is called a schema. A schema defines how the data in a relational database should be organized.
What is a table?
A table is a set of relational data. A table is a list of rows, the rows all have the same attributes
What is a row?
A row is a single instance of a record in a spreadsheet/table
What is SQL and how is it different from languages like JavaScript?
Structured Query Language (SQL) is the primary way of interacting with relational databases. It is a powerful way of retrieving, creating, and manipulating data in a relational database.
JavaScript is an imperative programming language, where you basically tell the JavaScript runtime what to do and how to do it.
SQL is a declarative programming language where programmers describe the results they want and the programming environment comes up with its own plan for getting those results. (like html & css)
How do you retrieve specific columns from a database table?
Start with the select keyword.
The select keyword is followed by a comma-separated list of column names, each surrounded by “double quotes”.
e.g: select “firstName”,
“lastName”
How do you filter rows based on some specific criteria?
Use the where keyword followed by the column you want to filter by in “double quotes”, followed by an optional comparison operator, followed by an optional value of that column that you want to see in ‘single quotes’ (if the value is a number, do not use quotes at all.)
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?
Use the limit keyword followed by the number of rows you want to see.
e.g: limit 10;
How do you retrieve all columns from a database table?
Use the select keyword followed by *
e.g: select *
How do you control the sort order of a result set?
Use the order by keyword followed by the column you want to order by in “double quotes.” order will be in ascending order by default - add desc keyword after “specified column” to sort in descending order.
How do you control the sort order of a result set?
Use the order by keyword followed by the column you want to order by in “double quotes.” order will be in ascending order by default - add desc keyword after “specified column” to sort in descending order.
How do you add a row to a SQL table?
insert into “table” (“columnName”, “columnName”,… )
values (‘value’, ‘value’, ‘value’, …);
values are in the same order as the columns they belong to
What is a tuple?
A list of values
How do you add multiple rows to a SQL table at once?
Multiple data rows can be inserted into a database table at once by 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?
after the insert into statement and the values statement, follow with
returning clause.
e.g:
insert into “products” (“name”, “description”, “price”, “category”)
values (‘Ostrich Pillow’, ‘Feel comfy and cozy!’, 99, ‘self care’)
returning *;
How do you update rows in a database table?
use the update keyword followed by the “table”
then use the set keyword followed by the “column” or “columns”, “columns” you want to update.
Follow with = ‘value’
Why is it important to include a where clause in your update statements?
Unless you intend on updating all rows, include a where clause in your update statement to only target specific rows
How do you delete rows from a database table?
Use SQL delete statement to remove rows from tables
How do you accidentally delete all rows from a table?
delete from “table”;
since there is no where statement targeting specific rows - all rows are deleted (THERE IS NO UNDO!)
What is a foreign key?
A column whose values refer to the values in a column on another table
A column that links one table to another
How do you join two SQL tables?
Using the join keyword followed by the referenced table, followed by the using keyword followed by the connecting column
e.g:
join “otherTable” using (“columnAttribute”);
How do you temporarily rename columns or tables in a SQL statement?
Use the as keyword. (column is renamed just for the result.
e.g:
select “products”.”name” as “product”,
“suppliers”.”name” as “supplier”
from “products”
join “suppliers” using (“supplierId”);
What are some examples of aggregate functions?
max( ), avg( ), count( ), min( ), sum( ), every( ), json functions are also available - for more: https://www.postgresql.org/docs/10/functions-aggregate.html
What is the purpose of a group by clause?
To separate rows into groups and perform aggregate functions on those groups of rows
