SQL comes up in almost every developer interview, whether the role is backend, full-stack, data or analyst. This set is about writing and reading queries: the different joins, grouping and filtering totals, how NULL behaves, subqueries and window functions. Unless I say otherwise, the questions use standard SQL that works in both PostgreSQL and MySQL.
How to practise: read each question and tap the option you think is correct. You will see right away whether you got it, along with a short explanation, and your score is tracked at the bottom of the screen. This set has 15 questions.
Topics covered in this set
- INNER, LEFT, RIGHT, FULL and self joins
- Logical order of query execution
- GROUP BY, aggregates and HAVING
- NULL handling and COUNT variants
- Subqueries, EXISTS and IN
- Window functions: ROW_NUMBER, RANK and running totals
SQL MCQs with answers and explanations
Q1. What does a LEFT JOIN return?
a) All rows from the left table, with matching rows from the right table or NULLs where there is no match
b) All rows from both tables
c) Only rows from the right table
d) Only rows that match in both tables
Q2. How do you find customers who have never placed an order?
a) SELECT * FROM orders WHERE customer_id = NULL
b) SELECT c.* FROM customers c JOIN orders o ON o.customer_id = c.id
c) SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL
d) SELECT c.* FROM customers c WHERE c.id IN (NULL)
Q3. In which logical order are these clauses evaluated?
a) FROM, SELECT, WHERE, ORDER BY
b) WHERE, FROM, SELECT, HAVING
c) SELECT, FROM, WHERE, GROUP BY
d) FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
Q4. What is the difference between WHERE and HAVING?
a) HAVING is faster
b) WHERE can only use numbers
c) WHERE filters rows before grouping, HAVING filters groups after aggregation
d) There is none
Q5. A table has 10 rows and the email column is NULL in 3 of them. What do COUNT(*) and COUNT(email) return?
a) 10 and 7
b) 7 and 10
c) 7 and 7
d) 10 and 10
Q6. What does SELECT * FROM users WHERE phone = NULL return?
a) All users
b) No rows, because comparing with NULL using = yields unknown
c) All users with no phone
d) A syntax error in every database
Q7. Which query returns the number of employees per department, only for departments with more than 5 employees?
a) SELECT COUNT(dept) FROM emp > 5
b) SELECT dept, COUNT(*) FROM emp WHERE COUNT(*) > 5 GROUP BY dept
c) SELECT dept FROM emp HAVING dept > 5
d) SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 5
Q8. What is a self join used for?
a) Removing duplicates
b) Joining a table to a view
c) Joining a table to itself, for example to match employees with their managers stored in the same table
d) Creating an index
Q9. What is the difference between UNION and UNION ALL?
a) There is no difference
b) UNION ALL removes duplicates
c) UNION removes duplicate rows, UNION ALL keeps them and is usually faster
d) UNION works only on two columns
Q10. Which query finds the second highest salary?
a) SELECT MIN(salary) FROM emp
b) SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)
c) SELECT salary FROM emp WHERE salary = 2
d) SELECT MAX(salary) FROM emp
Q11. What is a correlated subquery?
a) A subquery using UNION
b) A subquery that returns one value
c) A subquery in the FROM clause
d) A subquery that references columns of the outer query, so it is evaluated per outer row
Q12. Why can NOT IN (subquery) give surprising results?
a) It is not supported in PostgreSQL
b) If the subquery returns any NULL, the condition is never true, so no rows are returned
c) It ignores duplicates
d) It always returns all rows
Q13. How do RANK() and DENSE_RANK() differ for scores 100, 90, 90, 80?
a) Both give 1,2,3,4
b) RANK: 1,2,2,4 and DENSE_RANK: 1,2,2,3
c) Both give 1,2,2,3
d) RANK: 1,2,2,3 and DENSE_RANK: 1,2,2,4
Q14. What does this compute?
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
a) A running (cumulative) total ordered by date
b) A syntax error
c) The average amount
d) The total of all orders on every row
Q15. Which join returns every combination of rows from two tables?
a) INNER JOIN
b) CROSS JOIN
c) NATURAL JOIN
d) LEFT JOIN
Quick revision notes
- LEFT JOIN plus IS NULL finds rows without a match.
- Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
- COUNT(*) counts rows and COUNT(col) skips NULLs. Check for NULL with IS NULL.
- Prefer NOT EXISTS over NOT IN when NULLs are possible.
- Window functions add calculations without collapsing rows.
Frequently asked questions
Are these questions specific to MySQL or PostgreSQL?
They use standard SQL that works in both. For interviews, it still helps to know the differences, like LIMIT syntax or FULL OUTER JOIN (MySQL doesn't have it).
How do I practise SQL for interviews?
Install PostgreSQL or use an online playground. Load a sample schema like employees or orders, and write queries for everyday tasks: top N per group, running totals, finding duplicates and gaps.
Is JOIN the same as INNER JOIN?
Yes. JOIN on its own means INNER JOIN in standard SQL.
Comments
Post a Comment