Pick an option and check it. The explanation shows after each answer. Take as long as you like; nothing is timed and nothing is sent.
20 questions
Question 1 · easy
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customers(id INTEGER, name TEXT);
CREATE TABLE orders(id INTEGER, customer_id INTEGER);
INSERT INTO customers VALUES (1, 'asha'), (2, 'ravi'), (3, 'meena');
INSERT INTO orders VALUES (10, 1), (11, 1), (12, 2), (13, 9);
""")
inner = "SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id"
left = "SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id"
print(con.execute(inner).fetchone()[0], con.execute(left).fetchone()[0])
The inner join keeps only matching pairs: asha's two orders and ravi's one give 3 rows, and order 13 points at no customer, so it is dropped. The LEFT JOIN keeps every customer, so meena appears once more with NULL order columns, giving 4. A left join never adds rows for unmatched rows of the right-hand table.
Question 2 · medium
Table orders has columns (id, customer_id) with rows (10, 1), (11, 1) and (12, 2). Table addresses has columns (customer_id, city) with rows (1, 'Puri'), (1, 'Cuttack') and (3, 'Rourkela').
SELECT o.id, a.city
FROM orders o
JOIN addresses a ON a.customer_id = o.customer_id;
How many rows does the query return?
A join pairs every matching left row with every matching right row. Customer 1 has two orders and two addresses, so it produces 2 x 2 = 4 rows, while customers 2 and 3 have no partner on the other side. This fan-out is why joining two one-to-many tables on the same key inflates counts and sums.
Question 3 · easy
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE emp(id INTEGER, name TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO emp VALUES (1, 'anil', 90, NULL), (2, 'bina', 95, 1),
(3, 'chetan', 60, 1), (4, 'divya', 70, 3), (5, 'esha', 50, 3);
""")
sql = """SELECT e.name, m.name FROM emp e
JOIN emp m ON e.manager_id = m.id
WHERE e.salary > m.salary ORDER BY e.name"""
print(con.execute(sql).fetchall())
A self join gives the same table two aliases: e is the employee and m is that employee's manager, matched on e.manager_id = m.id. bina (95) earns more than anil (90) and divya (70) more than chetan (60), while chetan and esha earn less than their managers. anil has no manager, so the inner join drops him.
Question 4 · medium
The report must list every department with its number of active employees, showing 0 where there are none. The Support department, whose employees are all inactive, is missing from the result.
SELECT d.name, COUNT(e.id) AS active_staff
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.active = 1
GROUP BY d.name;
Why is Support missing?
WHERE runs after the join, so Support's joined rows (inactive employees, or NULL-padded rows for a department with no staff) all fail e.active = 1 and vanish before grouping. Writing LEFT JOIN employees e ON e.dept_id = d.id AND e.active = 1 filters which employees match while still keeping every department. COUNT(e.id) then reports 0 for departments with no active match.
Question 5 · hard
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE staff(name TEXT, dept_code TEXT);
CREATE TABLE depts(code TEXT, title TEXT);
INSERT INTO staff VALUES ('asha', 'HR'), ('ravi', NULL), ('meena', 'IT');
INSERT INTO depts VALUES ('HR', 'People'), (NULL, 'Unassigned');
""")
sql = """SELECT s.name, d.title FROM staff s
LEFT JOIN depts d ON d.code = s.dept_code ORDER BY s.name"""
print(con.execute(sql).fetchall())
In SQL NULL = NULL is unknown, not true, so ravi's NULL dept_code never matches the NULL code row and the 'Unassigned' department is never joined. The LEFT JOIN still keeps ravi and meena with a NULL title. To treat missing codes specially, use COALESCE(d.title, 'Unassigned') in the select list instead of a NULL key row.
Question 6 · easy
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE leads(id INTEGER, city TEXT);
INSERT INTO leads VALUES (1, 'Puri'), (2, 'Puri'), (3, NULL), (4, 'Cuttack'), (5, NULL);
""")
sql = "SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM leads"
print(con.execute(sql).fetchone())
COUNT(*) counts rows, NULLs included, so it is 5. COUNT(city) counts only non-NULL values (3), and COUNT(DISTINCT city) counts the distinct non-NULL values, Puri and Cuttack, so 2. Every aggregate except COUNT(*) ignores NULLs.
Question 7 · easy
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE users(id INTEGER, email TEXT);
INSERT INTO users VALUES (1, 'a@x.in'), (2, 'b@x.in'), (3, 'a@x.in'),
(4, 'c@x.in'), (5, 'c@x.in'), (6, 'c@x.in');
""")
sql = """SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1 ORDER BY email"""
print(con.execute(sql).fetchall())
GROUP BY email makes one group per address and COUNT(*) counts all rows in it, so a@x.in has 2 and c@x.in has 3. HAVING COUNT(*) > 1 then keeps only the groups that occur more than once, which is the standard way to find duplicate values. b@x.in appears once and is filtered out.
Question 8 · medium
This query should list the departments whose average salary is above 50000, but the database rejects it (SQLite says misuse of aggregate: AVG()).
SELECT dept, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 50000
GROUP BY dept;
What is the correct fix?
WHERE filters individual rows before any groups exist, so an aggregate cannot appear there, and an alias that stands for the aggregate does not help. HAVING runs after GROUP BY and filters whole groups by their aggregates. Adding salary to GROUP BY would make a group per salary value and change the meaning of the average.
Question 9 · medium
Table sales has columns (region, amount) with rows ('east', 50), ('east', 200), ('east', 150), ('west', 400), ('north', 90), ('north', 95), ('north', 180), ('south', 110) and ('south', 120).
SELECT region, SUM(amount)
FROM sales
WHERE amount > 100
GROUP BY region
HAVING SUM(amount) > 300;
How many rows does the query return?
WHERE removes rows first, so only sales above 100 are summed: east 350, west 400, north 180 and south 230. HAVING then keeps groups whose sum is above 300, which leaves east and west. Applying HAVING to the unfiltered totals would wrongly include north (365) as well.
Question 10 · medium
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders(customer TEXT, status TEXT, amount INTEGER);
INSERT INTO orders VALUES ('asha', 'paid', 300), ('asha', 'refunded', 100),
('ravi', 'paid', 150), ('ravi', 'paid', 50), ('meena', 'refunded', 400);
""")
sql = """SELECT customer, COUNT(*),
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)
FROM orders GROUP BY customer HAVING COUNT(*) >= 2 ORDER BY customer"""
print(con.execute(sql).fetchall())
COUNT(*) counts every order in the group, refunded ones included, while the CASE expression adds an amount only when the status is paid, so asha's paid total is 300. This conditional aggregation computes several filtered totals in one pass over the table. meena has a single order, so HAVING COUNT(*) >= 2 removes her group.
Question 11 · easy
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE products(name TEXT, price INTEGER);
INSERT INTO products VALUES ('pen', 10), ('book', 50), ('bag', 90), ('lamp', 70);
""")
sql = """SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products) ORDER BY name"""
print(con.execute(sql).fetchall())
The scalar subquery (SELECT AVG(price) FROM products) returns one value, 55, and the outer query compares each row's price with it. Only bag (90) and lamp (70) are above 55. An aggregate cannot be compared row by row in WHERE directly, which is why the average is computed in a subquery.
Question 12 · medium
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE emp(name TEXT, salary INTEGER);
INSERT INTO emp VALUES ('anil', 90), ('bina', 90), ('chetan', 80), ('divya', 70);
""")
q1 = "SELECT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 1"
q2 = "SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)"
print(con.execute(q1).fetchone()[0], con.execute(q2).fetchone()[0])
LIMIT 1 OFFSET 1 returns the second row, and with two employees tied at 90 that row is still 90. The subquery version first finds the top salary (90) and then takes the largest salary below it, 80, so it gives the second-highest distinct salary and returns NULL when there is none. SELECT DISTINCT salary ... LIMIT 1 OFFSET 1 or DENSE_RANK() also handle ties.
Question 13 · easy
Table orders has columns (customer_id, amount) with rows (1, 60), (1, 70), (2, 40), (3, 150), (3, 20) and (4, 90).
WITH totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT customer_id
FROM totals
WHERE total > 100
ORDER BY customer_id;
Which customer ids does the query return?
The CTE totals is a named result set with one row per customer: 130, 40, 170 and 90. The outer query then filters those totals, so customers 1 and 3 are returned. Filtering individual orders above 100 would have kept only customer 3, which is why the aggregate step comes first.
Question 14 · hard
Table customers has a column id with rows 1, 2 and 3. Table orders has a column customer_id with rows 1 and NULL (a guest checkout).
SELECT id
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders)
ORDER BY id;
What does the query return?
2 NOT IN (1, NULL) means 2 <> 1 AND 2 <> NULL, and any comparison with NULL is unknown, so the whole condition is unknown and the row is dropped; the same happens for every id. One NULL in a NOT IN list therefore empties the result. Use NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id), or filter customer_id IS NOT NULL in the subquery.
Question 15 · medium
Table emp has columns (name, dept, salary). You need the highest-paid employee in each department, keeping both employees when two share the top salary, in a query that runs on PostgreSQL as well as SQLite. Which query should you write?
The correlated subquery computes the maximum for the current row's department and keeps every employee who matches it, so ties are kept. Selecting name without grouping by it is rejected by PostgreSQL, and where it is accepted it still returns only one row per department, so ties are lost, while grouping by dept, name makes every employee their own group. A join to a CTE of per-department maximums, or RANK(), gives the same result.
Question 16 · easy
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE scores(name TEXT, marks INTEGER);
INSERT INTO scores VALUES ('anil', 90), ('bina', 95), ('chetan', 90), ('divya', 80);
""")
sql = """SELECT name,
ROW_NUMBER() OVER (ORDER BY marks DESC, name),
RANK() OVER (ORDER BY marks DESC),
DENSE_RANK() OVER (ORDER BY marks DESC)
FROM scores ORDER BY marks DESC, name"""
print(con.execute(sql).fetchall())
ROW_NUMBER() always numbers rows 1, 2, 3, 4, even across ties. RANK() gives tied rows the same rank and then skips, so divya is 4th, while DENSE_RANK() leaves no gap and gives her 3. Pick DENSE_RANK() for 'Nth highest distinct value' questions and ROW_NUMBER() when exactly one row per position is needed.
Question 17 · medium
What does this Python 3.12 program print?
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE txns(acct TEXT, day INTEGER, amount INTEGER);
INSERT INTO txns VALUES ('A', 1, 100), ('B', 1, 50), ('A', 2, -30),
('B', 3, 20), ('A', 3, 10);
""")
sql = """SELECT acct, day, SUM(amount) OVER (PARTITION BY acct ORDER BY day)
FROM txns ORDER BY acct, day"""
print(con.execute(sql).fetchall())
SUM(amount) OVER (PARTITION BY acct ORDER BY day) is a running total: within each account it adds up the rows from the first day to the current one, and it restarts for every new partition. Unlike GROUP BY, a window function keeps every input row. Without ORDER BY inside OVER, each row would instead show its account's full total.
Question 18 · hard
Table sales has columns (day, amount) with rows (1, 10), (2, 20), (2, 30) and (3, 40).
SELECT day, amount,
SUM(amount) OVER (ORDER BY day) AS running
FROM sales;
What is running on the two rows for day 2?
With ORDER BY and no frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes every peer row with the same day. Both day-2 rows therefore see 10 + 20 + 30 = 60. For a strict row-by-row total, write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and order by a unique tiebreaker such as id.
Question 19 · medium
Table emp has columns (name, dept, salary). Which query returns the two highest-paid employees in each department?
Window functions are computed after WHERE, so they cannot be filtered in the same query level; compute the row number in a CTE or derived table and filter it outside. PARTITION BY dept restarts the numbering for each department, which is what makes this top-N per group. LIMIT 2 gives two rows overall, and use RANK() or DENSE_RANK() instead of ROW_NUMBER() if tied salaries must all be kept.
Question 20 · easy
A report must show every employee's name and salary with the average salary of their department beside it, one row per employee. Table emp has columns (name, dept, salary). Which approach is the idiomatic one?
A window aggregate computes the average over each department's partition while keeping every employee row, in one query. GROUP BY dept, name averages each employee with only themselves, and an aggregate without GROUP BY collapses the table to one row. Stitching results together in Python works but moves logic and extra round trips out of the database for no gain.
Your answers stay in this browser tab. Nothing is sent or saved.