Top SQL Interview Questions and Answers

SQL (Structured Query Language) is a programming language. We use SQL to manage relational databases. Using SQL, users can create, read, update, and delete stored data in a table. 

a) INNER JOIN – retrieves only the records that have corresponding entries in both tables

b) LEFT JOIN – retrieves all records from the first table and matches ones from the second

c) RIGHT JOIN – retrieves all records from the second table and matches ones with the first

d) FULL JOIN – retrieves all records that have a match in either of the two tables

Normalisation organises data in a database to reduce duplication and improve data integrity. It divides a database into tables and sets relationships between them. Normalisation follows some rules.

1NF (First Normal Form): Ensures each column contains only atomic values and there are no repeating groups or arrays.
Example: Split comma-separated values into individual rows.

2NF (Second Normal Form): To be in 2NF, a table must already satisfy all the rules of 1NF. It removes partial dependency (non-key columns depend on the whole primary key, not part of it).
Example: Move data dependent on part of a composite key to a separate table.

3NF (Third Normal Form): To be in 3NF, a table must already satisfy all the rules of 2NF. It removes transitive dependency (non-key columns depend only on the primary key).

Example: Move data like the city name that depends on a zip code to its table.

DELETE removes selected rows, can use WHERE, logs each row, and can be rolled back. 

TRUNCATE removes all rows, no WHERE, faster, minimal logging, can’t be rolled back (in most DBs).

We use keys to identify records and build relationships between tables. The Primary Key uniquely identifies each row in a table. A Foreign Key links one table to another. Unique Key ensures all values in a column are unique. A Composite Key is a combination of columns used as a primary key. 

A secondary key is a column or group of columns used to help find data in different ways. It’s not the main key (primary key), but it helps speed up searches on other fields in the table. A secondary key doesn’t always have to be unique. It’s mainly used to make querying easier and faster.

This is different from a candidate key, which is a column that can uniquely identify a row and could be chosen as the primary key. So while candidate keys focus on uniqueness, secondary keys focus on flexibility and performance when looking up data.

CHAR(n) has a fixed length. It always uses n characters. Pads with spaces if the input is shorter. We can use CHAR when values are always the same length (e.g., country codes).
Example: Note that the resultant string has 5 characters in total. The last three characters are spaces.

'Hi' in CHAR(5) → ‘Hi ’

VARCHAR(n) has a variable length. It uses only the needed space (up to n). We can use VARCHAR when values vary in length (e.g., names, emails).
Example: Note that the resultant string has only two characters. No spaces will be added.

'Hi' in VARCHAR(5) → ‘Hi’

A view is a virtual table based on the query result. It doesn’t store data itself but displays data from one or more tables. You can use a view to simplify complex queries, hide data complexity, or restrict access to certain columns.

Here, EmployeeView shows only active employees with selected columns.

Aggregate functions perform calculations on multiple rows and return one result. Common aggregate functions are COUNT() to count rows, SUM() to add values, AVG() to calculate the average, MAX() to find the highest value and MIN() to find the lowest value.

• DDL: Defines the database structure — CREATE, ALTER, DROP.
• DML: Works with the data — SELECT, INSERT, UPDATE, DELETE.
• DCL: Manages permissions — GRANT, REVOKE.
• TCL: Controls transactions — COMMIT, ROLLBACK, SAVEPOINT.
• DQL: Queries data — mainly the SELECT statement.

With pattern matching, we can find data that matches a particular pattern using the LIKE keyword.

Here, % means any number of characters. This query finds all users whose names start with A, like "Alice", "Adam", or just "A".

Here, _ means exactly one character in that position. This query matches "Jon", "Jan", and "Jin".

WHERE filters rows before grouping.

HAVING filters groups after grouping.

A query inside another query is called a subquery. A subquery returns the results used by the outer query. Subquery types are:

• Single-row subquery: Returns one row
• Multiple-row subquery: Returns multiple rows
• Correlated subquery: Depends on the outer query for values

Here, SELECT MAX(salary) FROM employees is the subquery.

An index is like a shortcut in a database that helps you find information faster by quickly locating data in specific columns.

This creates an index on the name column to make searches faster.

A Clustered Index sorts and stores the actual data rows in the table based on the key. Only one per table. For example, a phone book is sorted by last name (data is sorted). So the pages themselves are in order.

A Non-Clustered Index creates a separate structure that points to the data rows. You can have many per table. For example, there’s a separate list at the back of the phonebook with names and the page numbers where you can find them.

A stored procedure is a saved collection of SQL statements. You can reuse and run them as a single unit. It saves time (write once, use many times), improves performance, and helps keep code organised and secure.

You can call it like this to reuse wherever required.

A trigger is a particular stored procedure which runs automatically when some events happen in a table. The events can be INSERT, UPDATE, or DELETE. Triggers can be used to enforce rules automatically, to log or audit changes or to maintain consistency between related tables.

LogDelete is the trigger here. After a delete happens on Employee table, this trigger inserts EmpID with DeletedDate to DeletedLog table.

UNION combines results from two queries and removes duplicates from the final result. You can use UNION when you want unique values.

UNION ALL combines results from two queries but keeps all records, including duplicates.  We can use UNION ALL when performance matters and are fine with duplicates.

We can use indexes on columns used in WHERE, JOIN, and ORDER BY. Select only the columns you need, not SELECT *. Avoid unnecessary calculations or functions in WHERE clauses. Use JOINs properly, avoid Cartesian products. Filter data early using WHERE before joins. Use EXPLAIN to analyse query plans and spot bottlenecks. Avoid subqueries if a JOIN can do the job. Limit returned rows with LIMIT if possible.

A transaction is a sequence of operations performed as a single logical unit of work. It ensures that all the operations either complete successfully together or fail together, maintaining data integrity. Key features of a transaction are called shortly as ACID.

• Atomicity: All steps succeed or none do.
• Consistency: Data remains valid before and after.
• Isolation: Transactions don’t interfere with each other.
• Durability: Once committed, changes are permanent.

Imagine a bank transfer from Account 1 to Account 2.

• Start the transaction. Any changes made after this point won't be final until committed.

• Deducts ₹500 from Account 1.

• Adds ₹500 to Account 2.

• Finalises the transaction. Both updates become permanent in the database.

• If an error occurs (e.g., Account 2 doesn’t exist), you can use

This cancels the entire transaction. So, no debit or credit happens.

A FULL JOIN returns all records from both tables. It matches rows where possible, fills with NULL where there's no match. Use a FULL JOIN to merge data with possible nulls.

Returns all employees and all departments, even if they don’t match.

 A CROSS JOIN returns the Cartesian product: every row from the first table is combined with every row from the second. Use CROSS JOIN when you need all combinations (like colour-size variations).

If Colours has 3 rows and Sizes has 4, the result = 3 × 4 = 12 rows.

OLTP (Online Transaction Processing) handles daily transactional data (e.g., banking, sales). It focuses on fast inserts, updates, and deletes. 
OLAP (Online Analytical Processing) is used for data analysis and reporting. It handles large volumes of historical data for complex queries. 

A Common Table Expression (CTE) is a temporary named result set you can use within a query to simplify complex queries. It’s defined with WITH and acts like a temporary table. In the query below, RecentOrders is the CTE.

ROW_NUMBER() assigns a unique sequential number to each row. Every row gets a different number. 
RANK() assigns ranks with gaps if there are ties. 
DENSE_RANK() assigns ranks without gaps even if there are ties.

How RANK() works:

Alice = 1 Bob = 2
Carol = 2 (same score as Bob, so same rank)
Dave = 4 (rank 3 is skipped because Bob and Carol tied at 2)

How DENSE_RANK() works:

Alice = 1 Bob = 2
Carol = 2 (tie)
Dave = 3 (no gap after tie)

The COALESCE function returns the first non-NULL value from a list of expressions. We can use it to handle NULL values by providing a default. It checks each value left to right and returns the first value which isn’t NULL.

Use case

If the Phone is NULL, it shows 'Not Given'.

All three remove data, but in different ways. DELETE removes specific rows based on a WHERE clause and can be rolled back. TRUNCATE quickly removes all rows from a table but can't be rolled back. DROP deletes the entire table, including its structure, so it's gone from the database.

We can use the DISTINCT keyword with ORDER BY and LIMIT, or use a subquery. 

The ORDER BY salary DESC arranges the salary values starting from the largest down to the smallest. Using LIMIT 1 OFFSET 1 means the query skips the top salary (offset by 1) and then retrieves the very next salary, effectively selecting the second-highest salary value.

We can use LAG() and LEAD() to access data from previous or next rows without using a self-join. LAG() lets you look backward at a previous row’s value. LEAD() lets you look forward to the next row’s value.

This query selects each employee’s salary, the previous employee’s salary, and the next employee’s salary based on employee_id order.

To handle NULLs in aggregations, SQL aggregate functions usually ignore NULL values by default. For example, SUM(), AVG(), COUNT() (except COUNT(*)), etc., skip NULLs automatically.
 If you want to include or replace NULLs, you can use functions like COALESCE() or IFNULL() to convert NULLs to a default value.

Here, any NULL salary is treated as 0 before summing.

MERGE (UPSERT) lets us update existing rows or insert new ones in a table based on matching data.

It updates if a match exists, inserts if not.

We can calculate a rolling average using the AVG() window function with OVER() and a frame specifying the range.

This calculates the average sales for the current row and the two previous rows (a 3-day rolling average).