Top 50 SQL Interview Questions for Freshers (with Answers)
SQLbeginnerinterview-prepsql-server
Codevity Admin · 10/25/2026
Step 25 of 25 · SQL Interview Preparation for Beginners · Stage 6: Interview readiness
Codevity Admin · 10/25/2026
These are the 50 questions freshers meet most often, grouped by topic. Each has a priority (High, Medium or Low, by how often it is asked) and a short answer you can say out loud. Answers lean towards Microsoft SQL Server, with notes where other databases differ. Start with the High priority questions.
High priority. DELETE is DML: it removes the rows you choose (with WHERE), logs each row, fires triggers and does not reset identity. TRUNCATE removes all rows quickly by deallocating pages, has no WHERE, resets identity, and is blocked if a foreign key references the table. DROP removes the whole table, including its structure and indexes. In SQL Server TRUNCATE and DROP can be rolled back inside a transaction.
High priority. A table has one primary key, it cannot be NULL, and in SQL Server it creates a clustered index by default. A table can have many unique keys, which create non-clustered indexes. A unique key accepts NULL: SQL Server allows one NULL, while PostgreSQL and MySQL allow several.
High priority. A foreign key is a column that must match a primary or unique key in another table, so orphan rows cannot exist. ON DELETE and ON UPDATE choose what happens to child rows: NO ACTION rejects the change, CASCADE applies it to the children, SET NULL and SET DEFAULT change the foreign key column.
Medium priority. Yes, as long as the referenced column (or columns) has a UNIQUE constraint or unique index.
Medium priority. A primary key made of two or more columns that are unique together, such as (order_id, product_id) in an order items table.
Medium priority. An identity column generates increasing numbers automatically, for example IDENTITY(1,1). Values used by a rolled-back or failed insert are not reused, so gaps appear. TRUNCATE resets the counter, DELETE does not.
Medium priority. CHAR(n) is fixed length and padded with spaces. VARCHAR(n) is variable length and non-Unicode. NVARCHAR(n) is variable length and Unicode, using two bytes per character, so it is the choice for text in many languages.
High priority. filters individual rows before grouping and cannot use aggregates. filters groups after aggregation and can use aggregates such as .
High priority. and , then , , , , , , and finally or . This is why a alias works in but not in .
High priority. means unknown, so evaluates to , never , and no rows are returned. Use or .
High priority. No. runs before grouping, so aggregates do not exist yet. Use , or calculate the aggregate in a subquery and compare against it.
High priority. Every row is changed or removed. Protect yourself: run the matching first, use a transaction, check before committing, and have a backup.
High priority. Use a half-open range: . Avoid for datetimes, because it includes the end value and misses the rest of the last day, and avoid wrapping the column in functions such as .
Medium priority. is T-SQL only, takes two arguments and returns the data type of the first. is ANSI standard, takes any number of arguments and returns the first non-, with the type decided by data type precedence. can truncate a longer replacement value.
Low priority. replaces a with . does the opposite: it returns when , otherwise . It is often used to avoid dividing by zero: .
Low priority. means , so both ends are included.
Medium priority. returns the first rows (optionally ). (SQL Server 2012 and later) skips rows and then takes , which makes it the standard way to page. In SQL Server it requires .
High priority. counts all rows. counts only rows where that column is not . counts distinct non- values.
High priority. combines results and removes duplicates, which needs an extra sort or hash step. keeps every row and is faster. Both need the same number of columns with compatible types.
Medium priority. Portable way: conditional aggregation such as . SQL Server also has the operator, but you must list the output columns, so unknown columns need dynamic SQL.
High priority. returns only rows with a match in both tables. returns all left rows plus matches, with where there is none. is the mirror image. returns all rows from both sides. See SQL joins explained.
High priority. A table joined to itself using two aliases. The classic case is employees and managers: . It also models hierarchies such as categories and sub-categories. See employees and managers.
Medium priority. A cross join returns every combination of rows: 1,000 rows by 1,000 rows gives 1,000,000. It is useful on purpose (all sizes by all colours) and a disaster by accident, when a join condition is missing.
High priority. Use an anti-join: , or a with . Avoid when the subquery column can be . See customers without orders.
High priority. means . The last part is , so the whole condition can never be . Use instead.
Medium priority. A subquery that refers to a column of the outer query. Logically it is evaluated once per outer row, although the optimizer may rewrite it as a join. Example: employees who earn more than their own department's average.
High priority. A named result set, defined with , that exists for a single statement. Use it to break complex queries into readable steps, to reference the same result several times, and for recursive queries such as hierarchies. It is not stored and not automatically faster.
High priority. checks whether any matching row exists and can stop at the first one. compares against a list of values. Optimizers often produce the same plan for both. The real difference is handling: breaks when the list contains , while does not.
High priority. All number rows by an order. gives unique numbers. gives ties the same number and then skips (1, 2, 2, 4). gives ties the same number without skipping (1, 2, 2, 3). See window functions.
High priority. Rank the distinct salaries with in a CTE and keep the rank you want. Alternatively . See Nth highest salary.
High priority. Find duplicates with . To delete, number the rows in each duplicate group with and delete the rows where the number is greater than 1. See delete duplicate rows.
Medium priority. reads a value from a previous row and from a following row, in the order you specify. Use case: month-over-month change, .
High priority. . Always specify , because the default frame treats ties as one group and is slower. See running total.
High priority. A clustered index stores the table's rows in the order of the index key, so the table is the index. A non-clustered index is a separate structure with key values and pointers to the rows. A table has one clustered index and many non-clustered ones.
High priority. No. The rows can be physically ordered in only one way, so there is one clustered index per table.
High priority. A SARGable predicate lets the engine use an index seek. Wrapping the column, as in , forces the engine to compute the function for every row, so it scans. Rewrite it as a range on the bare column.
Medium priority. A seek navigates the index tree directly to the matching rows. A scan reads the whole index or a large part of it. Scans are fine on small tables or when most rows are needed.
Medium priority. A plan shows how the database executes (or will execute) a query: which indexes, which joins, estimated and actual row counts. In SSMS press Ctrl+L for the estimated plan, or Ctrl+M and run the query for the actual plan. adds page-read counts.
Medium priority. A non-clustered index that contains every column a query needs, as key columns or columns, so SQL Server does not have to look up the table rows.
Medium priority. When two different types are compared, SQL Server converts one side by data type precedence. If the column gets converted (for example a column compared with an value), the index cannot be used for a seek and a scan happens. Match the types.
High priority. Atomicity (all or nothing), Consistency (the database moves from one valid state to another), Isolation (concurrent transactions do not interfere), Durability (committed data survives a crash).
High priority. 1NF: atomic values and no repeating groups. 2NF: 1NF plus no column depends on only part of a composite key. 3NF: 2NF plus no non-key column depends on another non-key column. "The key, the whole key, and nothing but the key." See normalization.
Medium priority. A dirty read sees uncommitted data. A non-repeatable read gets a different value when the same row is read twice. A phantom read gets extra or missing rows when the same range query is repeated. Higher isolation levels prevent more of them.
Medium priority. Two transactions hold locks the other needs. SQL Server detects the cycle, chooses a victim (the cheaper one to roll back), rolls it back and raises error 1205. The application should retry. Prevent deadlocks with consistent access order and short transactions.
Medium priority. Deliberately adding redundant data to make reads faster, for example precomputed totals or flattened reporting tables. Common in data warehouses (OLAP), where reads dominate. The cost is storage and keeping copies consistent.
Medium priority. A saved that behaves like a virtual table. A normal view stores only its definition, not data. It simplifies queries and restricts access to columns. An indexed (materialized) view does store results physically.
High priority. A stored procedure can change data, return several result sets, take output parameters and control transactions, and it is called with . A function must return a value, is used inside queries, and is restricted from changing data.
Medium priority. A scalar function returns one value. A table-valued function returns a table you can query. Inline table-valued functions (a single ) are usually fast. Multi-statement ones and some scalar functions can be slow because the optimizer estimates them poorly.
Medium priority. tables live in tempdb, have statistics, can be indexed and altered, and suit larger sets. variables are scoped to the batch, have no statistics (so row estimates are poor for big sets), and are not affected by a transaction rollback. Use for large intermediate results.
Medium priority. A trigger is code that runs automatically on , or . Inside it, holds the new row versions and holds the old ones (an update has both). Use triggers sparingly: they hide logic and add cost.
Answer each question aloud in two or three sentences, then check yourself against the answer. For the High priority items, also write the query or the example. Then take the SQL interview quizzes in this collection.
WHEREHAVINGCOUNT(*)FROMJOINWHEREGROUP BYHAVINGSELECTDISTINCTORDER BYTOPOFFSET ... FETCHSELECTORDER BYWHERENULLcolumn = NULLUNKNOWNTRUEIS NULLIS NOT NULLWHEREHAVINGSELECT@@ROWCOUNTWHERE d >= '20261001' AND d < '20261101'BETWEENYEAR()ISNULL(a, b)COALESCENULLISNULLISNULL(a, b)NULLbNULLIF(a, b)NULLa = bax / NULLIF(y, 0)x BETWEEN a AND bx >= a AND x <= bTOP (n)nWITH TIESOFFSET m ROWS FETCH NEXT n ROWS ONLYmnORDER BYCOUNT(*)COUNT(column)NULLCOUNT(DISTINCT column)NULLUNIONUNION ALLSUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)PIVOTINNER JOINLEFT JOINNULLRIGHT JOINFULL OUTER JOINFROM employees e LEFT JOIN employees m ON m.id = e.manager_idNOT EXISTSLEFT JOINWHERE b.key IS NULLNOT INNULLx NOT IN (1, 2, NULL)x <> 1 AND x <> 2 AND x <> NULLUNKNOWNTRUENOT EXISTSWITHEXISTSINNULLNOT INNULLNOT EXISTSROW_NUMBERRANKDENSE_RANKDENSE_RANK() OVER (ORDER BY salary DESC)SELECT DISTINCT salary ... ORDER BY salary DESC OFFSET N-1 ROWS FETCH NEXT 1 ROWS ONLYGROUP BY ... HAVING COUNT(*) > 1ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY id)LAGLEADamount - LAG(amount) OVER (ORDER BY month)SUM(amount) OVER (ORDER BY date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)ROWSRANGEWHERE YEAR(order_date) = 2026SET STATISTICS IO ONINCLUDEVARCHARNVARCHARSELECTEXECSELECT#Temp@Table#TempINSERTUPDATEDELETEinserteddeleted