PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

• PostgreSQL is an open source relational database system

- SQLite, MySQL, SQL Server, Oracle, Snowflake

Question 2

What are some advantages of learning a relational database?

Answer 2

• 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!

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

• Use the top command in the terminal to see if there are any PostgreSQL processes running
  
  • sudo service postgresql status

Question 4

How do you start and stop a PostgreSQL server from the CLI?

Answer 4

Start: sudo service postgresql start
See UI: pgweb and then visit localhost address
Stop: sudo service postgresql stop

Question 5

What is ACID?

Answer 5

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)

Question 6

What is a database schema?

Answer 6

A collection of databases, it determines how data will be organized

Question 7

What is a table?

Answer 7

A table is made up of rows of data that all have the same attributes represented as columns

Question 8

What is a row?

Answer 8

A row is made up of data elements, one for each attribute

Question 9

What is SQL and how is it different from languages like JavaScript?

Answer 9

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

Question 10

How do you retrieve specific columns from a database table?

Answer 10

SELECT "col1", "col2"

Question 11

How do you filter rows based on some specific criteria?

Answer 11

... WHERE criteria

Called a “predicate”

Question 12

What are the benefits of formatting your SQL?

Answer 12

Readability

Question 13

What are four comparison operators that can be used in awhereclause?

Answer 13

=, >, <, !=, and the “__ than or equal to” variations

Question 14

How do you limit the number of rows returned in a result set?

Answer 14

... LIMIT n at the end of the query

Question 15

How do you retrieve all columns from a database table?

Answer 15

SELECT *

Question 16

How do you control the sort order of a result set?

Answer 16

ORDER BY "col1" ASC/DESC or you can use the numbers to refer to which column as well

Question 17

How do you add a row to a SQL table?

Answer 17

Use the INSERT clause

INSERT INTO "table" ("col1", "col2", ...) VALUES ('col1Value', 23);

Question 18

What is a tuple?

Answer 18

• A grouping of data values, usually comma separated and enclosed in parentheses
  
  • A finite ordered list of elements

Question 19

How do you add multiple rows to a SQL table at once?

Answer 19

Use multiple tuples in the VALUES clause, separated by commas

Question 20

How do you get back the row being inserted into a table without a separateselectstatement?

Answer 20

At the end of the INSERT statement, include the RETURNING clause followed by the columns you want returned

Question 21

How do you update rows in a database table?

Answer 21

Use the UPDATE clause WITH A WHERE CLAUSE!

UPDATE "table" SET "column" = 'new value' WHERE "id" = 123 RETURNING *;

Question 22

Why is it important to include awhereclause in yourupdatestatements?

Answer 22

If you don’t, all values in the specified columns in the table will be set to a single value

Question 23

How do you delete rows from a database table?

Answer 23

Use the DELETE clause WITH A WHERE CLAUSE!

DELETE FROM "table" WHERE "id" = 123 RETURNING *;

Question 24

How do you accidentally delete all rows from a table?

Answer 24

If you leave off a WHERE clause, the delete operation will happen for every row in the table

Question 25

What is a foreign key?

Answer 25

A column that contain values that refer to another column in another table, used to join the two tables together

Question 26

How do you join two SQL tables?

Answer 26

After the FROM, use the JOIN clause followed by the table and the join method
FROM "table1" JOIN "table2" USING ("commonId")
We can use ON as well

Question 27

How do you temporarily rename columns or tables in a SQL statement?

Answer 27

Give them an alias using AS after the column in the SELECT clause or the table in the FROM/JOIN

Question 28

What are some examples of aggregate functions?

Answer 28

SUM, AVG, MIN, MAX, COUNT, STRING_AGG, JSON_AGG

Question 29

What is the purpose of agroup byclause?

Answer 29

To indicate which of the columns that are not being aggregated should be used as unique groups to aggregate values up to

Question 30

How do you prevent SQL injection attacks when sending a query with user input to a SQL database?

Answer 30

When using db.query(), pass in two arguments: sql and params

• sql has the query with the user input placeholders as $1, $2, etc.
• params is an array of the items to interpolate/substitute