Nitish picked these ten from his own experience of giving and taking SQL interviews. His claim: if you sit for an interview, especially at fresher or junior level (0–2 years), at least three or four of these will be asked, often as small variations. They are arranged from easiest to hardest.
- Read the task and try writing the query yourself in the editor. Every editor starts with the sample tables already loaded.
- Run it (Ctrl/⌘ + Enter). Errors are fine; that's practice.
- Only then open Show solution, compare, and read why it works.
Each question links to where it's solved in the live video.
Setup and the sample tables
In the live class Nitish used XAMPP + phpMyAdmin (as in Chapter 2): start Apache and MySQL, open localhost/phpmyadmin, create a database and import the data files linked in the video description. To let you practise right here, every editor on this page runs on small stand-in versions of the same tables (values approximate):
26 films; score = IMDb rating
manager_id → emp_id
12 real passengers
contains duplicates
membershipmembership_id, user_id, group_id
Q1 · Insert multiple rows in one query
users table in a single query.Nitish was asked this in his own college placement (how would you insert many rows at once?) and couldn't answer then. He knew INSERT, but not that one statement can carry many rows.
Show solution
- One
VALUESkeyword, then one bracket per row, separated by commas. You can add 3 rows or 3,000 this way. user_idisAUTO_INCREMENT, so passingNULLlets MySQL fill it.- Writing each row on its own line is just for readability.
Q2 · Add / delete multiple columns
email and password, to users in one query. Then drop both in one query.Modifying an existing table is a DDL job, so you need ALTER TABLE (revise Chapter 7).
Show solution
Nitish first wrote email VARCHAR(255) NOT NULL UNIQUE and it failed. Why? The table already had rows. A new NOT NULL column gets the same empty value for every existing row, so a UNIQUE rule is broken immediately. He removed UNIQUE to proceed. To make email unique, first fill in real emails, then ADD CONSTRAINT … UNIQUE.
Q3 · Find the 2nd highest value
Follow-up from the class: find the second-lowest.
"Sit for three interviews and one of them will ask this," says Nitish. Variations ask for the 3rd, 4th or Nth.
Show solution
ORDER BY score DESCputs the highest score on top.LIMIT 5keeps the top 5 rows. With two numbers,LIMIT offset, count: skip offset rows, then show count.LIMIT 0, 5= top five;LIMIT 1, 1= only the 2nd;LIMIT 2, 1= only the 3rd. In general, the Nth isLIMIT N-1, 1.- Same thing in standard syntax:
LIMIT 1 OFFSET 1.
If two movies share the top score, LIMIT 1, 1 returns the second of the tied pair, not the second-highest score. For the second-highest distinct value use SELECT MAX(score) FROM movies WHERE score < (SELECT MAX(score) FROM movies) or ORDER BY over SELECT DISTINCT score. Mention this and you'll stand out.
Q4 · Find the max value without ORDER BY
Sorting is expensive. Good comparison sorts take O(n log n) time (simple ones O(n²)). On 10 million rows that's a lot of work just to find one value, while finding a maximum needs only one pass, O(n). The rule of thumb Nitish gives: in SQL, sorting should be your last option.
Show solution
- This is a nested query (subquery): the inner query runs first, returns one number, and the outer query uses it in its
WHERE. - The answer is Titanic, the costliest film in the class dataset too.
In the downloaded data, budget was a text column. MAX on text compares alphabetically, so '95000000' beats '200000000' (because '9' > '2') and you get the wrong movie. Nitish first converted the column with ALTER TABLE … MODIFY COLUMN budget BIGINT (BIGINT because budgets can exceed INT's ~2.1 billion). Here the sample budget is already numeric, so the lab just skips the MODIFY.
Q5 · Find the managers of all employees
employee_name and manager_name, for every employee. The employees table has emp_id, first_name, last_name and manager_id, where manager_id is itself an emp_id.Hard if you don't spot the trick, easy once you do. Managers are employees too, so their names live in the same table.
Show solution
- A self join joins a table with itself. Give the two copies different aliases (
e1= the employee,e2= their manager) so SQL, and you, aren't confused. - Join condition: the employee's
manager_idequals the manager'semp_id. - Because both copies have
first_name, you must say which one:e1.first_namevse2.first_name.
The CEO has no manager (manager_id is NULL), so an inner join drops them. To list every employee, use LEFT JOIN: the CEO then appears with a NULL manager.
Q6 · Use of wildcards
Task BFind all movies whose name ends with "man" (Spider-Man, Superman, Batman…).
% percent
Any number of characters (zero or more).
_ underscore
Exactly one character.
Show solution
'A____': one A, then four underscores = exactly 5 characters.'%man': the name must end in "man".'%man%'would match "man" anywhere (e.g. "Mandalay").LIKEis case-insensitive under MySQL's default collation, which is why "Spider-Man" matches.
Q7 · If-else questions (CASE)
name, their family_size, and a family_type column: small if size ≤ 2, medium if 3–5, large if more than 5.Family size isn't stored directly. Titanic has sibsp (siblings/spouses aboard) and parch (parents/children aboard), so family size = sibsp + parch.
Show solution
CASE … WHEN … THEN … ELSE … ENDis SQL's if / else-if / else. Conditions are checked top to bottom and the first true one wins.- You can do arithmetic on columns and name the result with
AS. WHEN (sibsp + parch) BETWEEN 3 AND 5also works. In classBETWEENseemed not to work; the real bug turned out to be a missing comma between two selected columns. Watch your commas!
Q8 · Find the top value in each category
genre, name, score.The same pattern appears everywhere: top-paid employee per department, best-selling product per category, top dish per restaurant. First, how many genres are there? SELECT DISTINCT genre FROM movies gives the unique values.
Show solution
In Q4 the inner query was independent: it ran once, produced one number, done.
Here the inner query uses m1.genre, a value from the outer row. It's a correlated subquery: it is conceptually re-evaluated for every outer row.
- The outer query picks a movie, say Forrest Gump (genre Drama, 8.8).
- The inner query computes the max score among Drama movies: 9.3.
- 8.8 ≠ 9.3, so Forrest Gump isn't the top Drama movie: skip.
- Later the outer query reaches The Shawshank Redemption (Drama, 9.3). 9.3 = 9.3: print it.
In class there were 17 genres but 18 rows came back. Two Thriller movies shared the top score, so both matched. In the sample data here, Alien and The Shining tie in Horror. Ask the interviewer whether ties should all be shown (this query) or broken by another column.
Q9 · Find and delete duplicate rows
contacts table has some people entered more than once (same first name, last name and email, different id). (a) List the duplicate rows. (b) Delete them, keeping one copy of each person.Show solution
GROUP BY first_name, last_name, emailputs every copy of the same person in one group.MIN(id)picks one survivor per group, the first one entered.- Every row whose id is not in that survivor list is an extra copy. Swap
SELECT *forDELETEand they're gone (14 rows → 10). - To just count duplicates:
SELECT first_name, last_name, email, COUNT(*) FROM contacts GROUP BY first_name, last_name, email HAVING COUNT(*) > 1.
- IN vs NOT IN. In the live class the first attempt used
IN, which returns the survivors, not the duplicates, so the result looked wrong.NOT INfixed it. - MySQL error 1093. Real MySQL refuses to
DELETEfrom a table while a subquery reads the same table ("You can't specify target table … for update in FROM clause"). XAMPP's MariaDB allows it. On MySQL, wrap the subquery in a derived table:… NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM contacts GROUP BY …) AS keep).
Q10 · Joining tables
name, age and the group_name of every group they belong to.The answer is spread across three tables: the name and age are in users, the group name is in groups, and which user is in which group is in membership. This is a many-to-many relationship with a junction table, exactly as in Chapter 4.
Show solution
- Join two tables on their common column, then chain another
JOIN … ON. The same approach scales to 4 or 10 tables. - Build it step by step like Nitish did: first get
group_id, check the output, then add the next join. GROUPSis a reserved word in MySQL 8, so the table name needs backticks there:`groups`.
Revision: one line per question
| # | Question | Key idea |
|---|---|---|
| 1 | Insert multiple rows | INSERT … VALUES (…), (…), (…) |
| 2 | Add / drop multiple columns | ALTER TABLE t ADD COLUMN a …, ADD COLUMN b … |
| 3 | 2nd highest | ORDER BY x DESC LIMIT 1, 1 |
| 4 | Max without ORDER BY | WHERE x = (SELECT MAX(x) …); mind the column type |
| 5 | Employee → manager | Self join with aliases |
| 6 | Wildcards | % = any length, _ = one character |
| 7 | If-else | CASE WHEN … THEN … ELSE … END AS col |
| 8 | Top per category | Correlated subquery on the category |
| 9 | Duplicates | id NOT IN (SELECT MIN(id) … GROUP BY all columns) |
| 10 | Joins | Chain JOIN … ON through the junction table |
Check yourself
What does LIMIT 3, 2 return?
MySQL's two-number form is LIMIT offset, count.
Why is WHERE budget = (SELECT MAX(budget) FROM movies) preferred over ORDER BY budget DESC LIMIT 1 on huge tables?
Avoid sorting when you only need an extreme value. (With an index on budget, both can be fast, which is a good follow-up point.)
Which pattern matches names of exactly 4 characters ending in "x"?
Three underscores (3 characters) plus "x" = 4 characters.
What makes the subquery in Q8 "correlated"?
Correlated = depends on the outer row. Q4's subquery didn't, so it was independent.
In the self join for Q5, why does the CEO not appear in the result?
Use a LEFT JOIN to keep employees with no manager.
In Q9, what would id IN (SELECT MIN(id) … GROUP BY …) (IN instead of NOT IN) return?
That's the "keep" list. Handy for SELECT-ing a clean copy, but not for finding duplicates.
Users and groups have a many-to-many relationship. What's the minimum number of tables you must join to print user names next to group names?
M:N relationships always go through a junction table.
What's next in this interview series
This was part 1 of a 3-part live series. The question sheet Nitish shared also lists what the next sessions cover, so it's worth previewing:
- SQL vs NoSQL databases
- Tables required for a many-to-many relationship
- Draw a schema design for Swiggy
- Types of joins; UNION vs UNION ALL
- OLAP vs OLTP (normalised vs denormalised data)
- Database keys
- Types of normalisation
- Indexes and their types
- Transactions and ACID properties
- Views and stored procedures
Several are already covered in these notes: keys, M:N tables, Swiggy schema, SQL vs NoSQL, transactions.
Tinder-like app: model users and right-swipes as tables; list everyone a user has sent requests to; find the user with the 2nd most requests sent; find matches for a user.
Swiggy: customers who never ordered; average price per dish; top restaurant by orders in a month; monthly sales above X; a customer's favourite food; restaurant with most repeat customers; month-over-month revenue growth; most paired products.