SQL Interview Questions for Freshers (with Answers)

Every query runs against one small, fictional online store in Kochi that sells spices, tea and snacks. Set the tables up once in MySQL 8 or PostgreSQL (the foreign keys are written as separate lines because MySQL 8 silently ignores a REFERENCES written next to a column), add ten or fifteen rows of your own, and run every answer yourself. Reading SQL is not the same as writing it.

The practice dataset: a small online store in Kochi

CREATE TABLE customers (
  customer_id  INT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  email        VARCHAR(150),
  city         VARCHAR(60),
  referred_by  INT,
  joined_on    DATE,
  FOREIGN KEY (referred_by) REFERENCES customers(customer_id)
);

CREATE TABLE products (
  product_id    INT PRIMARY KEY,
  product_name  VARCHAR(100) NOT NULL,
  category      VARCHAR(50),
  price         DECIMAL(10,2)
);

CREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  customer_id  INT,            -- NULL for guest checkout
  order_date   DATE,
  status       VARCHAR(20),    -- 'delivered', 'cancelled', 'pending'
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_id    INT,
  product_id  INT,
  quantity    INT,
  FOREIGN KEY (order_id)   REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Keep one detail in mind. Guest orders have a NULL customer_id, and that single NULL causes one of the most common wrong answers in SQL rounds. You'll meet it in question 18.

Basic SQL interview questions for freshers

1. What is SQL, and how is it different from MySQL or PostgreSQL?

SQL (Structured Query Language) is the language you use to ask a relational database for data and to change it. MySQL and PostgreSQL are database systems that understand SQL. Both follow the ISO SQL standard closely, but each adds its own functions and skips a few standard features, which is why some answers below have a MySQL version and a PostgreSQL version.

2. What is the difference between a primary key and a unique key?

Each row is identified by its primary key. It must be unique, it can never be NULL, and a table has only one. A unique constraint also blocks duplicates, but a table can have several, and they allow NULLs. In our store, customer_id is the primary key, and email is a good candidate for a unique constraint.

3. What is a foreign key?

It's a column whose value must match a key in another table. orders.customer_id points to customers.customer_id, so the database refuses an order for a customer who doesn't exist. A NULL foreign key is allowed, which is how guest orders get in.

4. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows you choose with a WHERE clause and can be rolled back inside a transaction. TRUNCATE empties the whole table in one go and is much faster on big tables. DROP removes the table itself, structure and all. The two databases differ on one point. In PostgreSQL a TRUNCATE inside a transaction can be rolled back, while MySQL commits it immediately.

DELETE FROM orders WHERE status = 'cancelled';
TRUNCATE TABLE order_items;
DROP TABLE order_items;

5. What is the difference between WHERE and HAVING?

WHERE filters rows before they are grouped. HAVING filters groups after GROUP BY has run, which is why it can use aggregates like COUNT(*) and WHERE can't. Question 14 shows both in one query.

6. List all customers from Kochi

SELECT customer_id, name, email
FROM customers
WHERE city = 'Kochi';

Name the columns you need instead of writing SELECT *. Interviewers notice, and you'll want the habit on real tables with forty columns.

7. Find products priced from ₹200 to ₹500

SELECT product_name, price
FROM products
WHERE price BETWEEN 200 AND 500;

BETWEEN includes both ends, so a ₹500 packet of cardamom is in the result. Ask candidates directly and plenty of them get this wrong.

8. Find customers whose name starts with "A"

SELECT name
FROM customers
WHERE name LIKE 'A%';

% matches any run of characters and _ matches exactly one. Here the databases differ: with MySQL's default collation, LIKE ignores case, so 'a%' also finds "Anjali". In PostgreSQL, LIKE is case sensitive, and you use ILIKE when you want it to ignore case.

9. Find customers whose city is missing

SELECT name
FROM customers
WHERE city IS NULL;

city = NULL returns nothing. NULL means "unknown", and comparing anything to unknown is never true, so use IS NULL or IS NOT NULL.

10. Show the three most expensive products

-- MySQL and PostgreSQL
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3;

-- Standard SQL, works in PostgreSQL (not in MySQL)
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;

If two products tie for third place, LIMIT picks one of them arbitrarily. Say that out loud in the interview: it shows you think about the data, and it leads straight into question 27 later on.

11. List every city you have customers in, once each

SELECT DISTINCT city
FROM customers
ORDER BY city;

12. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(city) counts rows where city is not NULL. COUNT(DISTINCT city) counts different non-NULL cities.

SELECT COUNT(*)              AS all_customers,
       COUNT(city)           AS customers_with_city,
       COUNT(DISTINCT city)  AS cities
FROM customers;

GROUP BY and aggregate questions

13. How many orders has each customer placed?

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY order_count DESC;

Every column in the SELECT list must either be in GROUP BY or sit inside an aggregate. PostgreSQL is strict about this. So is MySQL 8 by default, through its ONLY_FULL_GROUP_BY mode, though some older setups switch it off and return misleading results.

14. Which categories have an average price above ₹300?

SELECT category, AVG(price) AS avg_price
FROM products
WHERE price IS NOT NULL
GROUP BY category
HAVING AVG(price) > 300;

WHERE throws out rows before grouping; HAVING then drops whole categories once they're averaged.

15. What is the total value of each order?

SELECT oi.order_id,
       SUM(oi.quantity * p.price) AS order_value
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY oi.order_id;

If you have a moment, point out that this uses today's price. A real store saves the price on each order line at the time of sale, because prices change.

16. In what order does the database process a SELECT query?

Logically, it goes FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, and finally LIMIT. Plenty of errors make sense once you know it. You can't use a column alias from SELECT inside WHERE, because WHERE runs first. You can use it in ORDER BY in both databases. MySQL also accepts an alias in HAVING, while PostgreSQL makes you repeat the expression.

SQL joins interview questions

17. What is the difference between an INNER JOIN and a LEFT JOIN?

Only rows that match in both tables survive an INNER JOIN. A LEFT JOIN keeps every row from the left table and fills the right side with NULLs where nothing matches.

-- Only customers who have ordered
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

-- Every customer, with NULL order_id if they never ordered
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

18. Find customers who have never placed an order

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A lot of candidates write WHERE customer_id NOT IN (SELECT customer_id FROM orders) instead. On our data that returns no rows at all. The guest orders put a NULL in the list, and NOT IN with a NULL inside can never be true. Use the LEFT JOIN above or NOT EXISTS (next question). Explain this trap in an interview and the panel will remember you.

19. Find products that have never been sold

SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.product_id = p.product_id
);

20. Show each customer with the name of the person who referred them

SELECT c.name AS customer,
       r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON r.customer_id = c.referred_by;

The same table appears twice here, under two aliases, which makes it a self join. LEFT JOIN keeps customers who came without a referral.

21. How do you get a FULL OUTER JOIN in MySQL?

This join keeps unmatched rows from both sides. Here that means customers with no orders and guest orders with no customer. PostgreSQL supports it directly. MySQL doesn't, so you combine a LEFT JOIN and a RIGHT JOIN with UNION. Keep a key column like customer_id in the SELECT, or UNION may merge two different people who share a name.

-- PostgreSQL
SELECT c.customer_id, c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

-- MySQL
SELECT c.customer_id, c.name, o.order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION
SELECT c.customer_id, c.name, o.order_id
FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id;

22. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result. UNION ALL skips that step, keeps every row and runs faster. Default to UNION ALL unless you actually need the duplicates gone.

Subquery and CASE questions

23. Find products priced above the average price

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

24. Find the second highest price

SELECT MAX(price) AS second_highest
FROM products
WHERE price < (SELECT MAX(price) FROM products);

This handles ties at the top correctly and returns NULL if every product has the same price. The next question is sometimes the Nth highest, and for that DENSE_RANK from question 27 is cleaner.

25. Find email addresses that appear more than once

SELECT email, COUNT(*) AS times_used
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

An analyst cleaning a customer list runs checks like this every day.

26. Label each order as small, medium or large by value

SELECT order_id,
       order_value,
       CASE
         WHEN order_value >= 2000 THEN 'large'
         WHEN order_value >= 500  THEN 'medium'
         ELSE 'small'
       END AS size_band
FROM (
  SELECT oi.order_id, SUM(oi.quantity * p.price) AS order_value
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY oi.order_id
) AS totals;

CASE checks its conditions top to bottom and stops at the first true one, so the order of the WHEN lines matters. The subquery in FROM is a derived table, and it needs an alias (totals) in both MySQL and PostgreSQL.

Window function questions

GROUP BY collapses a set of rows into one. A window function calculates across the same set and leaves every row in place. PostgreSQL has had them for many years. MySQL added them, together with CTEs (the WITH clause), in version 8.0, so they won't run on MySQL 5.7.

27. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

Take prices 900, 750, 750 and 600. ROW_NUMBER gives 1, 2, 3, 4, splitting the tie arbitrarily. RANK gives 1, 2, 2, 4 and leaves a gap. DENSE_RANK gives 1, 2, 2, 3 with no gap.

SELECT product_name, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
       RANK()       OVER (ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
FROM products;

28. Find the most expensive product in each category

WITH ranked AS (
  SELECT product_name, category, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT product_name, category, price
FROM ranked
WHERE rn = 1;

PARTITION BY restarts the numbering for each category. WHERE runs before window functions, so you can't filter on rn in the SELECT that creates it; wrap it in a CTE instead. Swap ROW_NUMBER for RANK if tied products should all appear.

29. Show daily revenue with a running total

WITH daily AS (
  SELECT o.order_date,
         SUM(oi.quantity * p.price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p     ON p.product_id = oi.product_id
  WHERE o.status = 'delivered'
  GROUP BY o.order_date
)
SELECT order_date,
       revenue,
       SUM(revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily
ORDER BY order_date;

Since each date appears once, you don't strictly need to write the ROWS frame out. Doing it anyway shows the interviewer you know what the default frame does.

30. Compare each day's revenue with the previous day

WITH daily AS (
  SELECT o.order_date,
         SUM(oi.quantity * p.price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p     ON p.product_id = oi.product_id
  WHERE o.status = 'delivered'
  GROUP BY o.order_date
)
SELECT order_date,
       revenue,
       LAG(revenue) OVER (ORDER BY order_date)          AS previous_day,
       revenue-LAG(revenue) OVER (ORDER BY order_date)  AS day_change
FROM daily;

LAG reads a value from the row before, and LEAD from the row after. The first day's previous_day is NULL because there is nothing before it. If the interviewer asks for month-wise figures instead, group by EXTRACT(YEAR FROM order_date) and EXTRACT(MONTH FROM order_date), which both databases accept. PostgreSQL also offers DATE_TRUNC('month', order_date), and MySQL has DATE_FORMAT(order_date, '%Y-%m').

How to prepare for the SQL round

Type every query above yourself, then change one thing and predict the output before you run it. Swap the LEFT JOIN in question 18 for an INNER JOIN. Remove the WHERE from question 14. The interview tests exactly this kind of prediction, and most people never practise it.

Applying for analyst roles? Then your resume should mention SQL where you used it, not only in a skills list. Our guide to the data analyst resume for freshers shows how to write a SQL project so a recruiter believes it. Most analyst rounds pair SQL with a spreadsheet test, so work through these Excel interview questions for data analysts in the same week.

Once you can do all 30 without notes, sit a timed test. Most data analyst hiring includes an aptitude section too; Interview Prep has timed practice for it, and a free timed challenge on Compete shows where you stand against students across India.

Start with a timed set on Interview Prep this week, then take the next free Compete challenge.