PostgreSQL

Question 1

What is PostgreSQL and what are some alternative relational databases?

Answer 1

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

Enforces rules that the data you are storing is useful and can be queried

Question 3

What is one way to see if PostgreSQL is running?

Answer 3

• sudo service postgresql status

Question 4

What is a database schema?

Answer 4

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

Question 5

What is a table?

Answer 5

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

Question 6

What is a row?

Answer 6

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

Question 7

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

Answer 7

Structured Query Language is used for accessing database.

Question 8

How do you retrieve specific columns from a database table?

Answer 8

select “thing1”

Question 9

How do you filter rows based on some specific criteria?

Answer 9

where

Question 10

What are the benefits of formatting your SQL?

Answer 10

Easier to read

Question 11

What are 4 comparison operators that can be used in a ‘where’ clause?

Answer 11

=, <, >, !=

Question 12

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

Answer 12

limit -insert number-

Question 13

How do you retrieve all columns from a database table?

Answer 13

select *

Question 14

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

Answer 14

order by, then direction (ascd (a-z) or desc(z-a))

Question 15

How do you add a row to a SQL table?

Answer 15

insert into

Question 16

What is a tuple?

Answer 16

A grouping of data values, usually comma separated and enclosed in parentheses

Question 17

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

Answer 17

Use multiple tuples in the VALUES clause, separated by commas

Question 18

How do you get back the row being inserted into a table without a separate ‘select’ statement?

Answer 18

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

Question 19

How do you update rows in a database table?

Answer 19

update clause

Question 20

Why is it important to include a ‘where’ clause in your ‘update’ statements?

Answer 20

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

Question 21

How do you delete rows from a database table?

Answer 21

delete statement

Question 22

How do you accidentally delete all rows from a table?

Answer 22

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

Question 23

What is a foreign key?

Answer 23

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

Question 24

How do you join two SQL tables?

Answer 24

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 25

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

Answer 25

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

Question 26

What are some examples of aggregate functions?

Answer 26

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

Question 27

What is the purpose of a ‘group by’ clause?

Answer 27

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