SQL Interview Questions for Developers and DBAs

A SQL interview comes down to a few problems on a small schema you have never seen. You read the tables, work out what the answer has to be, and write it as one query. Four of those problems follow, on PostgreSQL 17. Try each one before you read the solution.

The first three problems use two tables and this data:

CREATE TABLE countries (
  country_code char(2) NOT NULL,
  country_name varchar(100) NOT NULL,
  CONSTRAINT pk_countries_country_code PRIMARY KEY (country_code)
);

CREATE TABLE cities (
  city_id      integer NOT NULL,
  city_name    varchar(100) NOT NULL,
  country_code char(2) NOT NULL,
  inhabitants  bigint NOT NULL,
  CONSTRAINT pk_cities_city_id PRIMARY KEY (city_id)
);

ALTER TABLE cities ADD CONSTRAINT fk_cities_countries
  FOREIGN KEY (country_code) REFERENCES countries (country_code);

INSERT INTO countries (country_code, country_name) VALUES
  ('BB', 'Bartinia'), ('CF', 'Cliffinia'), ('GM', 'Gemerani'), ('LT', 'Lituania'),
  ('MF', 'Memrish'),  ('NR', 'Nirania'),   ('SC', 'Scanji'),   ('RM', 'Rimani');

INSERT INTO cities (city_id, city_name, country_code, inhabitants) VALUES
  ( 0, 'Fremont',    'MF', 731057),
  ( 1, 'Fresno',     'BB',  70992),
  ( 2, 'Portland',   'SC',  67120),
  ( 3, 'Buffalo',    'CF', 768156),
  ( 4, 'Detroit',    'GM', 227334),
  ( 5, 'Las Vegas',  'MF', 660319),
  ( 6, 'St. Paul',   'SC', 806673),
  ( 7, 'Wichita',    'CF',  29817),
  ( 8, 'Oakland',    'MF', 811170),
  ( 9, 'Las Vegas',  'SC', 945416),
  (10, 'Norfolk',    'SC', 152733),
  (11, 'Boston',     'GM', 910276),
  (12, 'St. Louis',  'NR', 999146),
  (13, 'Norfolk',    'GM', 539992),
  (14, 'Greensboro', 'CF', 977512),
  (15, 'Milwaukee',  'MF', 158437),
  (16, 'Tulsa',      'MF', 549088),
  (17, 'St. Paul',   'NR',  93545),
  (18, 'Tulsa',      'SC', 443390),
  (19, 'Shreveport', 'GM', 412861);

Two countries have no city at all, one has a single city, and the largest have five each. That spread is what the first three problems are built on.

Countries with more than two million inhabitants

List every country whose cities together hold more than two million people, largest first.

The population of a country is not stored anywhere: it is the sum of the inhabitants of its cities, so the query has to group the cities by country and add them up. A condition on that sum belongs in HAVING, because WHERE is evaluated before the rows are grouped and has no sum to look at yet.

SELECT co.country_name, sum(ci.inhabitants)
FROM countries co
  JOIN cities ci ON co.country_code = ci.country_code
GROUP BY co.country_name
HAVING sum(ci.inhabitants) > 2000000
ORDER BY sum(ci.inhabitants) DESC;

Cliffinia adds up to 1775485 and Nirania to 1092691, so both fall under the threshold and neither reaches the result. The column is called sum because the expression has no alias, and PostgreSQL names an unaliased aggregate column after the function.

Countries with no city

List the countries that have no city in the cities table.

An inner join cannot answer this one. Joining countries to cities keeps only the countries that match a city, which is the opposite of what the question asks for. NOT EXISTS asks the question directly: keep the country when the subquery finds no city carrying its country code.

SELECT country_name
FROM countries co
WHERE NOT EXISTS (
  SELECT 1 FROM cities ci WHERE ci.country_code = co.country_code
)
ORDER BY country_name;

The subquery selects the literal 1 rather than a column, because NOT EXISTS looks at whether a row comes back and never at what is in it. Without the ORDER BY, both names still come back, in whatever order PostgreSQL happens to read them.

More SQL interview problems

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

The last two problems are the ones that separate candidates. The third stays on the countries and cities above and puts an aggregate inside a filter on individual rows. The fourth brings two tables of its own and turns a number into a label.

Cities in a country that has at least five cities

List the country code and the city name of every city whose country has five cities or more.

The condition is about the country, and the rows you have to return are cities. That is what makes it harder than it looks: grouping the cities by country gives you the countries that qualify, but grouping also throws away the individual city rows you have to print. So the group happens in a subquery, and the outer query reads the cities table again and keeps the rows whose country came back.

SELECT country_code, city_name
FROM cities
WHERE country_code IN (
  SELECT country_code FROM cities GROUP BY country_code HAVING count(*) >= 5
)
ORDER BY city_id;

Memrish and Scanji have five cities each; Gemerani has four and does not qualify:

Rating a task from the average score of its reports

The last problem has its own two tables. A task collects reports, each report holds one candidate's score, and the task has to be rated from the average of those scores: 20 or below is Hard, above 20 up to 60 is Medium, above 60 is Easy. Show only the tasks that have at least one report, ordered by task id.

CREATE TABLE tasks (
  id   integer NOT NULL,
  name varchar(40) NOT NULL,
  UNIQUE (id)
);

CREATE TABLE reports (
  id        integer NOT NULL,
  task_id   integer NOT NULL,
  candidate varchar(40) NOT NULL,
  score     integer NOT NULL,
  UNIQUE (id)
);

INSERT INTO tasks (id, name) VALUES
  (101, 'MinDist'), (123, 'MinDist'), (142, 'MinDist'), (300, 'Tricoloring');

INSERT INTO reports (id, task_id, candidate, score) VALUES
  (13, 101, 'John Smith',     100),
  (24, 123, 'Delaney Lloyd',   34),
  (37, 300, 'Monroe Jimenez',  50),
  (49, 101, 'Stanley Price',   45),
  (51, 142, 'Tanner Sears',    37),
  (68, 142, 'Lara Fraser',      3),
  (83, 300, 'Tanner Sears',     0);

Three pieces have to happen in one statement. The average is an aggregate, so the query groups by task. The rating is a chain of conditions on that average, which is what CASE is for. The requirement to skip tasks without reports needs no filter at all: an inner join to reports drops those tasks by itself.

SELECT
  t.id AS task_id,
  t.name AS task_name,
  CASE
    WHEN avg(r.score) <= 20 THEN 'Hard'
    WHEN avg(r.score) <= 60 THEN 'Medium'
    ELSE 'Easy'
  END AS difficulty
FROM tasks t
  JOIN reports r ON t.id = r.task_id
GROUP BY t.id, t.name
ORDER BY t.id;

Task 142 is the one to check by hand. Its two scores are 37 and 3, so the average is exactly 20, and the first branch tests for 20 or below, which puts it in Hard. Task 101 averages 72.5 and falls past both branches into Easy.

A second data set makes the boundaries do more work. Empty both tables first, because these rows are meant to be the only ones in them:

DELETE FROM reports;
DELETE FROM tasks;

INSERT INTO tasks (id, name) VALUES
  (3, 'Cake'), (6, 'GameOfNuts'), (7, 'CircleIntersectionArea'), (9, 'JessicaAndBrian');

INSERT INTO reports (id, task_id, candidate, score) VALUES
  ( 2, 6, 'Paul Sat',     0),
  ( 3, 3, 'Karen M.',    30),
  ( 5, 3, 'Oscar Glad',  10),
  ( 6, 9, 'Karen M.',    60),
  (11, 6, 'Paul Sat',    81),
  (13, 6, 'Paul Sat',   100);

The same query, unchanged, returns three rows:

Task 7 has no report, so the inner join leaves it out. Task 9 has a single score of 60, and 60 is not above 60, so the second branch catches it and it comes back Medium. Task 6 is the interesting one: its three scores add up to 181, and 181 divided by 3 is 60.333, which is past the second branch and lands in Easy. That last answer depends on the type PostgreSQL gives back: avg over an integer column returns numeric, not an integer, so the value stays 60.333 instead of being cut to 60 and rated Medium.

Every query here runs as written in the SQL Editor of the free DbSchema Community Edition. Download it at https://dbschema.com/download.html, connect to a PostgreSQL database, run the first block to create the tables, and answer the four problems before you look at the solutions again.