20 questions · untimed

Python interview: SQL round

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])

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?

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())

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?

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())

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())

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())

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?

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?

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())

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())

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])

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?

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?

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?

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())

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())

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?

Question 19 · medium

Table emp has columns (name, dept, salary). Which query returns the two highest-paid employees in each department?

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?

Next: Backend and design round