Loading...
Loading...
Loading...
Core CS and SQL interview guide
Queries, joins, grouping, subqueries, constraints, indexes and design. Every answer is short enough to say aloud, and every question links to the lesson that teaches it.
60 questionsLast updated
With a half-open range: paid_at >= '2025-06-01' AND paid_at < '2025-07-01'. It covers every second of 30 June and nothing from July, and it works for any month length.
Not with the default MySQL 8 collation, utf8mb4_0900_ai_ci, which ignores case for =, LIKE and REGEXP. A case-sensitive column collation changes that, and REGEXP_LIKE(col, pattern, 'c') makes one regular expression check case-sensitive.
Any comparison with NULL gives UNKNOWN, not TRUE, and WHERE keeps only TRUE rows. Even NULL = NULL is unknown. The correct test is column IS NULL.
One part of the sum, such as a gift-wrap charge or a discount, is NULL for those orders, and NULL makes the whole sum NULL. I wrap the optional part in COALESCE(gift_wrap, 0) so a missing value counts as zero. I would also check whether the column should be NOT NULL DEFAULT 0 in the table design.
MySQL sorts by the first column, then uses the second only for rows that are equal in the first, and so on. Each column has its own direction, as in ORDER BY points DESC, nrr DESC.
ORDER BY id LIMIT 10 OFFSET 10. In general the offset is (page - 1) * size. The ORDER BY must be unique, so I add the id as a tie-breaker; otherwise rows can repeat or go missing between pages.
DISTINCT removes duplicate rows from the result. It always applies to the whole SELECT list, so SELECT DISTINCT branch, year removes only rows where both values repeat. Writing DISTINCT(branch) does not limit it to one column.
MySQL stops at the first WHEN that is true. If marks >= 40 comes before marks >= 90, a 95 is labelled with the first one and never reaches the second. I order range tests from the strictest to the loosest, and put the NULL test first.
LENGTH returns the size in bytes and CHAR_LENGTH the number of characters. For plain English text they match, but in utf8mb4 ₹ is 3 bytes and many Indian-language characters are 3 bytes each. For limits a user sees, like a 40-character name, I use CHAR_LENGTH.
With the default collation it is not needed, because comparison already ignores case. It also stops MySQL from using an index on city to jump straight to the rows, so it must check every row. The Indexes & Query Performance module explains this in detail.
Either works for display, and FORMAT(n, 2, 'en_IN') gives Indian grouping directly. But the formatted value is text, so it cannot be sorted or added correctly. I keep raw numbers in queries that feed calculations and format only at the final display step.
It is MySQL changing a value's type by itself so two values can be compared or added. It does not fail; it gives a quiet wrong answer, like '10' < '9' being true or 'abc' = 0 being true. It can also stop an index from being used when a text column is compared with a number.
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY). The function is applied to the fixed side, not to the column, so MySQL can still use an index on order_date.
The missing rows have NULL in dept_id. NULL <> 1 is UNKNOWN, and WHERE keeps only TRUE rows. If those rows belong in the result, I write dept_id <> 1 OR dept_id IS NULL.
WHERE name LIKE '%word%'. With the default collation it ignores case. On a large table a leading % means every row is checked, so big apps use a full-text index or a search service, but LIKE is right for a small catalogue.
COUNT(*), COUNT(1) and COUNT(column)?COUNT(*) and COUNT(1) both count rows; in MySQL's InnoDB engine they run the same way. COUNT(column) counts only the rows where that column is not NULL, so it can be smaller. I use COUNT(*) for "how many rows" and COUNT(column) for "how many have a value".
SELECT name, MAX(salary) FROM employees; fail? How would you get the highest-paid employee?The aggregate squeezes all rows into one, but name has many values, so with ONLY_FULL_GROUP_BY MySQL refuses with error 1140 instead of picking a random name. I would write SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1. If ties matter, a subquery such as WHERE salary = (SELECT MAX(salary) ...) returns all top earners.
SELECT DISTINCT city and SELECT city ... GROUP BY city?For just listing the unique cities, both give the same rows. GROUP BY is needed as soon as you want a number per city, such as COUNT(*), because DISTINCT cannot compute anything per group. I use DISTINCT for plain unique lists and GROUP BY for summaries.
All rows with NULL in the grouping column go into a single group, shown as NULL. So an employee without a department appears in one NULL row rather than being dropped. If I want a label, I write COALESCE(dept_id, 'none') or handle it in the report.
WHERE filters individual rows before they are grouped, so it cannot use aggregates. HAVING filters the groups after GROUP BY, so it can use COUNT, SUM and the other aggregates. In one query, WHERE decides what goes into the groups and HAVING decides which groups come out.
Yes. Then the whole result of WHERE is treated as one group. For example, SELECT COUNT(*) FROM orders HAVING COUNT(*) > 10; returns the count only if it is above 10, and no row otherwise. It is rare in practice.
FROM, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY and finally LIMIT. Each clause works on what the previous one produced. That is why WHERE cannot use aggregates or aliases, and why ORDER BY can use both.
In ORDER BY everywhere, and in GROUP BY and HAVING as a MySQL extension. Not in WHERE, and not in another column of the same SELECT. For SQL that should run on other databases too, I repeat the expression in GROUP BY and HAVING.
HAVING COUNT(*) > 1 work but WHERE COUNT(*) > 1 fail?COUNT(*) needs groups, which exist only after GROUP BY. HAVING runs after grouping, so the count is known; WHERE runs before it, so MySQL reports error 1111, invalid use of group function.
It adds summary rows to a grouped result: a subtotal for each value of the earlier grouping columns and a grand total at the end. The rolled-up columns show NULL in those rows. It saves running separate queries for detail, subtotals and the total.
One row per city, so GROUP BY city. The number is COUNT(*). Only last month's orders count, so the date range goes in WHERE. Only cities above 50 are shown, so HAVING COUNT(*) > 50. Then ORDER BY the count descending, and I check the total against a plain count.
AVG ignores NULLs and divides by the number of non-NULL values. That is right for "average of the values we have" but wrong when a missing value really means zero, such as a free visit when you want income per visit. Then I use AVG(COALESCE(x, 0)).
With GROUP_CONCAT(DISTINCT col ORDER BY col SEPARATOR ', '). DISTINCT drops repeats, ORDER BY fixes the order inside the list, and SEPARATOR sets the text between values. The result is cut at 1024 bytes by default (group_concat_max_len).
Because the same fact would be repeated on many rows. Updating it means changing every copy, and missing one leaves the data contradicting itself. Separate tables store each fact once, and joins combine them when a report needs them.
Lesson: Why data is split across tables, and joinsNo. A SELECT with a join only reads both tables and builds a result. The stored rows stay exactly as they were.
An inner join returns one row for every pair of rows from the two tables that satisfies the ON condition. Rows with no matching partner are left out. In MySQL, JOIN on its own is an inner join.
Yes. Joining 10 customers to 15 orders gives 15 rows, more than the customers table. And if the key is duplicated on both sides, each copy pairs with each copy, so rows multiply even further.
Lesson: INNER JOIN step by stepAn inner join returns only the matched pairs. A left join returns every row of the left table: matched rows are paired as usual, and unmatched rows appear once with NULL in every right-table column.
Because the query uses COUNT(*), which counts rows, and the left join gives that customer one row filled with NULLs. COUNT(o.order_id) counts only non-NULL values and returns 0.
Use COALESCE(SUM(e.salary), 0). SUM over no rows returns NULL, and COALESCE returns the first non-NULL argument, which is 0 in that case.
Both queries must return the same number of columns, in the same order, with compatible types. The column names come from the first query, and a single ORDER BY at the end sorts the whole result.
FROM a FULL JOIN b USING (id) in MySQL and it worked. Is it a full join?No. FULL is not a reserved word in MySQL, so it is read as an alias for a, and the query is an ordinary inner join. Unmatched rows from both sides are silently missing.
Both copies have the same table name, so without aliases MySQL cannot tell which copy a column belongs to and stops with error 1066, not unique table/alias. Aliases such as e and m also make the roles clear.
Self join employees to their managers, then GROUP BY the manager and COUNT(*). Each joined row is one report, so the count is the number of people who report directly to that manager.
For inner joins the result is the same and MySQL's optimizer chooses its own execution order, so I write them in the order that reads best: the main table first, then outward along the keys. With outer joins the order matters, because it decides which table's rows are kept.
Lesson: Joining three or more tablesProbably a later inner join uses the left-joined table. Its columns are NULL for the unmatched rows, so the inner join finds no match and drops them. Every join that hangs off a left-joined table must also be a left join.
It returns the rows of one table that have no matching row in another. In SQL it is written as LEFT JOIN ... WHERE right_key IS NULL, or with NOT EXISTS.
Left join bookings with the train number, the date and any status rule all in ON, then keep WHERE b.pnr IS NULL. If the date were in WHERE, the trains with no booking would have a NULL date and be removed.
The minimum is 0, when nothing matches. The maximum is 15, when every row matches every row, for example when all rows share the same key value. A left join returns at least 5, and at most 15.
Lesson: Practice: joinsI run the join without GROUP BY and check COUNT(*) and a few raw rows. Usually the new table has several rows per existing row, so rows repeat; I then count with COUNT(DISTINCT key), drop the join if the report does not need it, or total that table separately.
A single-row (scalar) subquery returns one value, so it works with =, <, > and the other comparison operators. A multi-row subquery returns a list, so it needs IN, NOT IN, ANY or ALL. Using = with a multi-row subquery gives MySQL error 1242.
When I only need columns from the outer table and just want to filter it. IN returns each outer row once, while a one-to-many join repeats a row per match and needs DISTINCT. If I need columns from both tables, a join is the right tool.
A non-correlated subquery does not depend on the outer query, so it gives one answer for all rows. A correlated subquery uses a column from the current outer row, such as x.dept_id = e.dept_id, so it gives a different answer for each row and logically runs once per row.
When I need columns or several totals from the other table, a join with GROUP BY does it in one pass, while subqueries would repeat the lookup. For a single value or a yes/no membership test, a subquery usually reads more naturally.
EXISTS (subquery) is true when the subquery returns at least one row. It does not look at the values, so the selected columns do not matter; SELECT 1 simply makes that clear. MySQL can stop at the first matching row.
EXISTS filters the outer table and returns each outer row at most once. A join returns a row per match and gives access to the other table's columns. For "customers who bought an accessory", EXISTS lists each customer once; a join repeats them per item.
Both produce a temporary result for one statement. A CTE is written at the top and can be referenced several times and by later CTEs; a derived table sits inside FROM and must be written out again to reuse it. Only CTEs can be recursive.
With WITH RECURSIVE: the anchor selects the employee with manager_id IS NULL, and the recursive member joins employees e to the CTE on e.manager_id = org.emp_id, adding level + 1 and a growing name chain. Sorting by the chain prints the tree in order.
A view is a named, saved SELECT stored in the database. You query it like a table, but it stores no rows; each time it is used, MySQL runs the query on the current data.
Yes, if the view is updatable: each view row must map to one row of one base table, so no GROUP BY, aggregates, DISTINCT or UNION. Otherwise MySQL refuses with error 1288 or 1471. WITH CHECK OPTION also stops changes that would move a row outside the view.
A window function computes a value over a set of rows related to the current row, written as function() OVER (...). Unlike GROUP BY, it keeps every row and adds the value as a column, so I can show each employee with their rank or the department average.
Because WHERE is applied before window functions are computed; the ranking does not exist yet at that point. I compute the ranking in a CTE or derived table and filter it in the outer query.
That is RANK(), the way competitive exams count: two students at 98 are both 1st and the next student is 3rd. If the school says the next student is 2nd, I use DENSE_RANK() instead.
LAG(col) returns the value from the previous row in the window's order and LEAD(col) from the next row. They return NULL when there is no such row, unless I pass a default as the third argument, like LAG(col, 1, 0).
Because window functions run after WHERE. If the query filtered out the earlier rows, they are not in the window. I compute LAG in a CTE over the full data and filter in the outer query.
Each lesson has quizzes, practice and more interview questions on the same topic.
Open the first lesson