SQL & Databases — 60 questions
Play ▸- #1 · Which name does this query return? -- emp(name, salary): ('Ann',100), ('Bob',300), ('
- #2 · How many rows match the pattern? -- names: 'Jn', 'Jane', 'Jonah', 'Jean', 'Jan' SEL
- #3 · How many rows does this return? -- t(a, b): (1,'x'), (1,'x'), (1,'y'), (2,'x') SEL
- #4 · How many rows does this return? SELECT 1 UNION SELECT 1 UNION ALL SELECT 1;
- #5 · Why does PostgreSQL reject this query? -- PostgreSQL SELECT price * qty AS total FROM ite
- #6 · What does the query return? WITH RECURSIVE n(x) AS ( SELECT 1 UNION ALL SELECT
- #7 · How many rows does the join return? -- a.id: 1, 2, 3 -- b.id: 2, 3, 4 SELECT * FROM a
- #8 · Which join keeps every row of the left table, even when nothing matches on the right?
- #9 · How many rows does this return? -- sizes has 3 rows, colors has 4 rows SELECT * FR
- #10 · How many rows does the join produce? -- a has 2 rows with id = 1 -- b has 3 rows with i
- #11 · How many rows does this return? -- a.id: 1, 2, 3 -- b.id: 2, 3, 4 SELECT * FROM a
- #12 · Why is the CEO missing from the result? SELECT e.name, m.name AS boss FROM emp e JOIN emp
- #13 · What does the query return? -- emp.dept: 'A', 'A', 'B', 'C' SELECT COUNT(DISTI
- #14 · How many rows does the result contain? -- orders.region: 'EU', 'US', 'EU', 'APAC', 'US' S
- #15 · What does the query return? SELECT SUM(amount) FROM orders WHERE 1 = 0;
- #16 · What happens when PostgreSQL runs this? -- PostgreSQL SELECT dept, name, MAX(salary) FROM
- #17 · How many rows does each query return? SELECT COUNT(*) FROM t WHERE 1 = 0; SELECT COUNT(*
- #18 · How many rows does this return? -- PostgreSQL; sales has regions EU, US, APAC SELE
- #19 · What is the main trade-off of adding an index to a table?
- #20 · Which filter can use this index as a direct seek? CREATE INDEX idx_name ON people (last_name, first_
- #21 · The query ignores the index. What fixes it? CREATE INDEX idx_email ON users (email); SELECT *
- #22 · What does this PostgreSQL
EXPLAIN line tell you? Seq Scan on orders (cost=0.00..18334.00 rows=52 wi
- #23 · What does
INCLUDE (total) make possible? -- PostgreSQL 11+ CREATE INDEX ON orders (customer
- #24 · Why can't a B-tree index seek for this filter? CREATE INDEX idx_name ON users (name); SELECT * FR
- #25 · In ACID, what does atomicity guarantee for a transaction?
- #26 · What does the final
SELECT return? -- acct: id 1, bal 100 BEGIN; UPDATE acct SET bal
- #27 · Which values are in
t afterwards? BEGIN; INSERT INTO t VALUES (1); SAVEPOINT s; INSE
- #28 · Which anomaly did T1 experience? T1: SELECT price FROM item WHERE id = 7; -- 10 T2:
- #29 · How does a database like PostgreSQL resolve this? T1: UPDATE acct SET bal = 0 WHERE id = 1; T2: UPDA
- #30 · One deposit is lost. Which change prevents it? -- two sessions run this at the same time SELECT b
- #31 · What must a primary key's values be?
- #32 · With foreign keys enforced, what happens? CREATE TABLE orders ( id INT PRIMARY KEY, customer
- #33 · Which normal form does this table violate first? order_items(order_id, product_id, qty, product_nam
- #34 · Students take many courses and courses have many students. How is this usually modeled?
- #35 · What happens to post 5's comments? CREATE TABLE comments ( id INT PRIMARY KEY, post_i
- #36 · Which column type is the safest choice for storing money amounts?
- #37 · What does this return? SELECT 5 + NULL;
- #38 · What does the query return? -- t.x: 5, NULL, 7 SELECT COUNT(*) FROM t WHERE x
- #39 · What are the two results? -- SQLite SELECT NULL OR 1, NULL AND 0;
- #40 · What does the final query return? -- SQLite / PostgreSQL CREATE TABLE u (email TEXT
- #41 · What happens to the insert? CREATE TABLE line ( qty INT CHECK (qty > 0) ); INS
- #42 · Where does the NULL appear in the output? -- PostgreSQL; score: 3, NULL, 1 SELECT score FROM
- #43 · How does
SUM(x) OVER (...) differ from SUM(x) with GROUP BY?
- #44 · What is
r for the score 80? -- scores: 90, 90, 80 SELECT score, RANK() OVER (O
- #45 · What is
run on the row with id 2? -- t(id, x): (1,1), (2,2), (3,3) SELECT id, SUM(x)
- #46 · What is
prev on the row with id 1? -- t(id, x): (1,10), (2,20), (3,30) SELECT id, LAG
- #47 · What is
s on the row (1,1)? -- t(d, x): (1,1), (1,2), (2,3) SELECT d, x, SUM(x
- #48 · Why does this fail, and what is the usual fix? SELECT * FROM emp WHERE ROW_NUMBER() OVER ( PARTIT
- #49 · What happens to the analyst's
UPDATE? -- run as an admin GRANT SELECT ON orders TO analy
- #50 · Which SQL statement takes a privilege away from a user?
- #51 · Why does this not work for choosing the table? # Python DB-API cur.execute("SELECT * FROM %s", (t
- #52 · What kind of vulnerability is this? -- signup stores the name safely with a placeholde
- #53 · What effect does this policy have on queries? -- PostgreSQL ALTER TABLE docs ENABLE ROW LEVEL SE
- #54 · Why can this return items not containing "50%"? # input = '50%' (parameterized) cur.execute( "SELE
- #55 · In what format does MongoDB store documents on disk?
- #56 · What kind of data store is Redis primarily?
- #57 · What does this MongoDB query return? db.users.find({ age: { $gt: 30 } })
- #58 · Under the CAP theorem, what must a distributed store choose between during a network partition?
- #59 · Why does Cassandra reject this query? -- Cassandra (CQL) -- PRIMARY KEY ((user_id), ts)
- #60 · In MongoDB, when is embedding a sub-document usually better than referencing another collection?