SQL Interview Questions for Freshers
SQL interview questions for freshers and students: SELECT basics, WHERE vs HAVING, joins, GROUP BY, keys, NULL handling, normalisation, indexes and ACID. Each answer explains why the other options are wrong.
CoversGROUP BY and aggregatesKeys and constraintsIndexesJoinsNormalisationNULL handlingSQL basicsTransactions
Practice questions with answers
Tap an option: green is right, red is wrong, and the explanation says why.
What does SQL stand for, and what is it used for?
Show answer and explanation
Answer: B. Structured Query Language: the standard language for defining, querying and changing data in relational databases
SQL is Structured Query Language, the standard language for relational databases such as MySQL, PostgreSQL, SQL Server and SQLite. It covers defining tables (DDL: CREATE, ALTER), reading data (SELECT), changing it (DML: INSERT, UPDATE, DELETE) and permissions (GRANT). It is not a driver language, a file format or a network protocol; MySQL's wire protocol is a separate thing.
What is the difference between a primary key and a unique key in MySQL?
Show answer and explanation
Answer: C. A table has one primary key and it can't be NULL; it can have many unique keys, and a unique column may hold NULLs
Both stop duplicate values, but a table has exactly one primary key, every primary-key column is NOT NULL, and in InnoDB the primary key is the clustered index that physically orders the rows. A table can have several unique keys, and a unique column may contain more than one NULL because NULL is not equal to NULL. Primary keys never allow duplicates, so the last option is wrong.
What is the difference between WHERE and HAVING?
Show answer and explanation
Answer: B. WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregates like COUNT(*)
A query runs in a logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. WHERE removes individual rows before they are grouped, so it can't use an aggregate. HAVING runs on the groups, so HAVING COUNT(*) > 5 keeps only groups with more than five rows. Filter in WHERE whenever you can: fewer rows reach the grouping step. They are not synonyms and neither is limited to a data type.
What does this query return?
CREATE TABLE users (id INT, phone VARCHAR(20));
INSERT INTO users VALUES (1, '555-0101'), (2, NULL), (3, '555-0103');
SELECT COUNT(*), COUNT(phone) FROM users;Show answer and explanation
Answer: D. 3, 2
COUNT(*) counts rows, so it returns 3. COUNT(phone) counts only rows where phone is not NULL, so it returns 2. Every aggregate except COUNT(*) skips NULLs, which is why AVG(col) divides by the number of non-NULL values, not the number of rows. COUNT never returns NULL; on an empty table both counts are 0.
Which join returns every row from the left table, with NULLs where the right table has no match?
Show answer and explanation
Answer: D. LEFT JOIN (LEFT OUTER JOIN)
A LEFT JOIN keeps every row of the left table; when the right table has no matching row, its columns come back as NULL. An INNER JOIN keeps only rows that match on both sides. A CROSS JOIN pairs every left row with every right row (a Cartesian product). A NATURAL JOIN is an inner join on all same-named columns, which is fragile because adding a column can change the result. A common use of LEFT JOIN is finding missing data: WHERE right.id IS NULL.
How do DELETE, TRUNCATE and DROP differ in MySQL?
Show answer and explanation
Answer: A. DELETE removes chosen rows and can be rolled back; TRUNCATE empties the table quickly and resets AUTO_INCREMENT; DROP removes the table itself
DELETE is row-by-row DML: it takes a WHERE clause, fires delete triggers and can be rolled back inside a transaction. TRUNCATE TABLE is DDL in MySQL: it drops and re-creates the table, so it is fast, has no WHERE, causes an implicit commit and resets AUTO_INCREMENT. DROP TABLE removes the data and the table definition. Mixing these up in production is a classic outage.
Why does SELECT * FROM users WHERE phone = NULL return no rows, even when some phones are NULL?
Show answer and explanation
Answer: B. Comparing anything with NULL gives UNKNOWN, not TRUE; you must write phone IS NULL
SQL uses three-valued logic: a comparison with NULL is neither true nor false but UNKNOWN, and WHERE keeps only rows where the condition is TRUE. So phone = NULL never matches anything. Use IS NULL or IS NOT NULL (MySQL also has the NULL-safe operator <=>). Writing 'NULL' compares with the four-letter text, which is a different value. NULLs are stored, and nothing hides them.
What is normalisation, and why do we normalise a database?
Show answer and explanation
Answer: C. Organising tables so each fact is stored once, which removes duplicate data and update anomalies
Normalisation splits data into related tables so each fact lives in one place. In first normal form each column holds one value; in second, non-key columns depend on the whole key; in third, they depend on nothing but the key. The payoff is no update anomalies: change a customer's address once instead of in every order row. Teams sometimes denormalise on purpose for read-heavy reports, but that is a trade-off. It has nothing to do with data types, indexes or compression.
What is an index in MySQL, and what does it cost?
Show answer and explanation
Answer: C. A sorted structure (a B+tree in InnoDB) that finds rows without scanning the table; it speeds reads but uses space and slows writes
An index is like the index of a book: InnoDB keeps the indexed columns sorted in a B+tree with pointers to the rows, so WHERE email = ? becomes a few page reads instead of a full scan. Every INSERT, UPDATE and DELETE must also update each index, and each index uses disk and buffer-pool memory, so you index the columns your queries filter, join and sort on, not everything. Only a unique index stops duplicates, and it is not a copy of the table.
What does ACID stand for in database transactions?
Show answer and explanation
Answer: D. Atomicity, consistency, isolation, durability
ACID describes what a transaction guarantees. Atomicity: all of its changes happen or none do (a transfer debits and credits together). Consistency: it moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions don't see each other's half-done work, to the degree the isolation level promises. Durability: once committed, it survives a crash (InnoDB uses its redo log for this). The other expansions mix in unrelated words such as availability and caching.
Take it as a timed test
- 12 questions
- 15 min
- Pass mark 60%
- Answers shown at the end
Sign in with your email to take the test: we send a 6-digit code, no password. Your scores and reports are saved to your account. The test also uses 10 questions that aren't shown above, so practise for understanding, not memory.
About this paper
This freshers paper covers the SQL basics every interviewer checks, whether the role is backend developer, data analyst or QA: how primary, unique and foreign keys differ, WHERE versus HAVING, what a LEFT JOIN returns, how COUNT treats NULLs, DELETE versus TRUNCATE versus DROP, why normalisation matters and what an index costs. Ten questions are on this page with full explanations. Each timed test draws 12 questions from a larger bank, including ones that aren't shown here, so every attempt is different: practise for understanding rather than memory.
Answer each one before opening the explanation, then take the timed test: 15 minutes, a pass mark of 60%, and a report that shows which topics cost you marks.