PostgreSQL Flashcards
What is PostgreSQL and what are some alternative relational databases?
- PostgreSQL is an open source relational database system
- SQLite, MySQL, SQL Server, Oracle, Snowflake
What are some advantages of learning a relational database?
- Nearly all applications utilize a database, many of them relational databases
- SQL is a commonly used and therefore highly transferrable language to learn
- Allow you to query data!
What is one way to see if PostgreSQL is running?
- Use the
topcommand in the terminal to see if there are any PostgreSQL processes runningsudo service postgresql status
How do you start and stop a PostgreSQL server from the CLI?
Start: sudo service postgresql start
See UI: pgweb and then visit localhost address
Stop: sudo service postgresql stop
What is ACID?
Atomicity - Transactions are singular units, and these units get accepted or rejected entirely (not halfway)
Consistency - There are rules in place (e.g. table schema enforcement) that prevent invalid data from being written (data could be incorrect, but not invalid)
Isolation - Processes may be run in parallel, but they must affect the database as if they were done sequentially
Durability - Once a transaction has been committed, it stays committed (saved the non-volatile memory)
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, for accessing databases
SQL is a declarative language as opposed to an imperative language like JavaScript, meaning developers code the intent rather than the operations
How do you retrieve specific columns from a database table?
SELECT "col1", "col2"
How do you filter rows based on some specific criteria?
... WHERE criteria
Called a “predicate”
What are the benefits of formatting your SQL?
Readability
What are four comparison operators that can be used in awhereclause?
=, >, <, !=, and the “__ than or equal to” variations
How do you limit the number of rows returned in a result set?
... LIMIT n at the end of the query
How do you retrieve all columns from a database table?
SELECT *
How do you control the sort order of a result set?
ORDER BY "col1" ASC/DESC or you can use the numbers to refer to which column as well
How do you add a row to a SQL table?
Use the INSERT clause
INSERT INTO "table" ("col1", "col2", ...) VALUES ('col1Value', 23);
What is a tuple?
- A grouping of data values, usually comma separated and enclosed in parentheses
- A finite ordered list of elements
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 separateselectstatement?
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?
Use the UPDATE clause WITH A WHERE CLAUSE!
UPDATE "table" SET "column" = 'new value' WHERE "id" = 123 RETURNING *;
Why is it important to include awhereclause in yourupdatestatements?
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?
Use the DELETE clause WITH A WHERE CLAUSE!
DELETE FROM "table" WHERE "id" = 123 RETURNING *;
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 agroup byclause?
To indicate which of the columns that are not being aggregated should be used as unique groups to aggregate values up to
How do you prevent SQL injection attacks when sending a query with user input to a SQL database?
When using db.query(), pass in two arguments: sql and params
-
sqlhas the query with the user input placeholders as$1,$2, etc. -
paramsis an array of the items to interpolate/substitute
