SELECT NULL = NULL AS same;

What does the query return?

CREATE TABLE users (id int, status text);
INSERT INTO users VALUES (1, 'active'), (2, 'banned'), (3, NULL);

SELECT id FROM users WHERE status <> 'banned';

Which ids are returned?

SELECT v FROM t WHERE v BETWEEN 1 AND 10;

Which values does the condition accept?

CREATE TABLE orders (id int, customer_id int);
INSERT INTO orders VALUES (1, 10), (2, NULL);

SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

customers has ids 10, 20 and 30. How many rows are returned?

SELECT 1 IN (1, NULL) AS a, 2 IN (1, NULL) AS b;

What does the query return?

CREATE TABLE t (v int);
INSERT INTO t VALUES (1), (NULL), (1), (NULL);

SELECT count(*), count(v), count(DISTINCT v) FROM t;

What does the query return?

CREATE TABLE scores (v int);
INSERT INTO scores VALUES (10), (NULL), (20);

SELECT avg(v) FROM scores;

What does the query return?

SELECT sum(amount), count(*) FROM payments WHERE 1 = 0;

What does the query return?

SELECT
  count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
  sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_too
FROM invoices;

Which statement about the two columns is correct?

SELECT (NULL AND false) AS a, (NULL OR true) AS b, (NOT NULL) AS c;

What does the query return?

CREATE TABLE t (v int);
INSERT INTO t VALUES (2), (NULL), (1);

SELECT v FROM t ORDER BY v DESC;

In what order are the values returned in PostgreSQL?

CREATE TABLE t (v text);
INSERT INTO t VALUES ('10'), ('9'), ('100');

SELECT v FROM t ORDER BY v;

In what order are the values returned?

SELECT total / nullif(quantity, 0) AS unit_price FROM lines;

What does nullif do here?

CREATE TABLE t (region text);
INSERT INTO t VALUES ('eu'), (NULL), ('eu'), (NULL);

SELECT region, count(*) FROM t GROUP BY region;

How many rows are returned?

SELECT department, name, count(*)
FROM employees
GROUP BY department;

What happens in PostgreSQL?

SELECT customer_id, count(*)
FROM orders
WHERE count(*) > 1
GROUP BY customer_id;

What happens?

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';

What does the WHERE clause do to the LEFT JOIN?

SELECT c.name, count(*)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

What is reported for a customer with no orders?

SELECT * FROM a, b;

a has 3 rows and b has 4. How many rows are returned?

SELECT *
FROM t1 JOIN t2 ON t1.k = t2.k;

t1 has 2 rows with k = 1; t2 has 3 rows with k = 1. How many rows does the join produce?

SELECT count(*)
FROM t1 JOIN t2 ON t1.k = t2.k;

Both tables have exactly one row, and in both of them k is NULL. What is returned?

CREATE TABLE events (at timestamp);
INSERT INTO events VALUES
  ('2024-01-31 00:00:00'),
  ('2024-01-31 15:30:00'),
  ('2024-02-01 00:00:00');

SELECT count(*) FROM events
WHERE at BETWEEN '2024-01-01' AND '2024-01-31';

How many rows are counted?

SELECT date '2024-01-31' + interval '1 month';

What does the query return?

SELECT 7 / 2 AS a, 7 / 2.0 AS b, 7 % 2 AS c;

What does the query return?

SELECT 'Ada' || ' ' || NULL AS a, concat('Ada', ' ', NULL) AS b;

What does the query return?

SELECT 'abc' LIKE 'a_c' AS a, 'a_c' LIKE 'a\_c' AS b, 'ABC' LIKE 'abc' AS c;

What does the query return?

CREATE TABLE t (code varchar(3));
INSERT INTO t VALUES ('abcdef');

What happens on the INSERT?

SELECT length('ab'::char(5)) AS len, 'ab'::char(5) || '|' AS joined;

What does the query return?

CREATE TABLE "Users" (id int);
SELECT * FROM users;

What happens on the SELECT?

SELECT price * quantity AS total
FROM lines
WHERE total > 100
ORDER BY total;

What happens in PostgreSQL?

In what order are the clauses of a SELECT logically evaluated?

SELECT DISTINCT city, country FROM addresses;

What does DISTINCT apply to?

SELECT DISTINCT ON (customer_id) customer_id, placed_at, total
FROM orders
ORDER BY customer_id, placed_at DESC;

What does the query return?

SELECT DISTINCT name FROM users ORDER BY created_at;

What happens in PostgreSQL?

SELECT count(*) FROM t;
SELECT region, count(*) FROM t GROUP BY region;

t is empty. How many rows does each query return?

SELECT id FROM users LIMIT 10;

Which rows does this return?

SELECT 1 UNION SELECT 1;
SELECT 1 UNION ALL SELECT 1;

How many rows does each query return?

SELECT total / quantity
FROM lines
WHERE quantity <> 0 AND total / quantity > 10;

Is the division protected from quantity = 0?

CREATE TABLE scores (name text, score int);
INSERT INTO scores VALUES ('a', 90), ('b', 90), ('c', 80);

SELECT name,
  rank()       OVER (ORDER BY score DESC) AS r,
  dense_rank() OVER (ORDER BY score DESC) AS d,
  row_number() OVER (ORDER BY score DESC) AS n
FROM scores;

What is returned for c?

CREATE TABLE sales (day int, amount int);
INSERT INTO sales VALUES (1, 10), (2, 20), (2, 30), (3, 40);

SELECT day, amount, sum(amount) OVER (ORDER BY day) AS running
FROM sales;

What is running for the two rows on day 2?

SELECT name, score
FROM scores
WHERE rank() OVER (ORDER BY score DESC) <= 3;

What happens?

CREATE TABLE p (a int, b int);
INSERT INTO p VALUES (1, 2);

UPDATE p SET a = b, b = a;
SELECT * FROM p;

What does the SELECT return?

CREATE TABLE users (id int, email text UNIQUE);
INSERT INTO users VALUES (1, NULL);
INSERT INTO users VALUES (2, NULL);

What happens on the second INSERT?

CREATE TABLE items (qty int CHECK (qty > 0));
INSERT INTO items VALUES (NULL);

What happens on the INSERT?

CREATE TABLE t (id serial PRIMARY KEY, v text);
BEGIN;
INSERT INTO t (v) VALUES ('a');
ROLLBACK;
INSERT INTO t (v) VALUES ('b');
SELECT id FROM t;

What id does row b get?