SQL

Question 1

Replace Value with a default when NULL

Answer 1

coalesce(column, default) as column

Question 2

Select and deduplicate results

Answer 2

SELECT DISTINCT * FROM table

Question 3

How to concatenate items

Answer 3

||

Question 4

How to alias a column

Answer 4

SELECT column AS alias

Question 5

Sort by name descending with nulls last

Answer 5

ORDER BY name DESC NULLS LAST

Question 6

Start postgres console

Answer 6

psql -p 5555

Question 7

How to import rows from a text file

Answer 7

COPY

Question 8

Difference between WHERE and HAVING

Answer 8

WHERE is computed before retrieving rows and cannot have aggregates

HAVING is run after rows are retrieved and is used for filtering based on aggregates.

Question 9

What is a foreign key

Answer 9

A foreign key references a column on another table, and that other column is checked for the existence of the key upon insert of a record in the first table.

Question 10

What does Postgres use to read a table when there is no index

Answer 10

Sequential scan

Question 11

What is the problem with OFFSET

Answer 11

Performance issues as it gets larger because the database needs to count the rows

Question 12

What is a having clause for

Answer 12

Filtering aggregates such as GROUP BY

Question 13

What does NULL indicate?

Answer 13

Unknown value, it does not mean false.

That is why NULL = NULL returns NULL rather than false, and we need to use IS NOT NULL instead.

Question 14

How can we cache results in SQL

Answer 14

Materialized views

Question 15

How to test SQL without committing to DB (so can iterate).

Answer 15

BEGIN;

(Code)

ROLLBACK;

Question 16

What should we use in where statements to compare against true, false, and null?

Answer 16

IS ( not = )

Question 17

How to get the year from a datetime column

Answer 17

extract(year from datetime) as year

Question 18

How to count only a subset

Answer 18

count(*) filter(where column = ‘banana’) as bananas

Question 19

How do we embed an aggregate value column of all the results along with a list of the results?

Answer 19

Window function (OVER)

Question 20

What are directories called in SQL, and what is the default one called?

Answer 20

Schemas, public

Question 21

How to get info back after inserting / updating / deleting without a new query

Answer 21

RETURNING

Question 22

Difference between ON and WHERE in a join

Answer 22

ON is computed before the join, WHERE is computed after

Question 23

If using LIMIT and OFFSET, what else must be used

Answer 23

ORDER BY, as otherwise a random result will be returned