SQL (Structured Query Language) remains one of the most in-demand skills for data analysts, database administrators, and backend developers. Whether you’re preparing for your first technical interview or brushing up on fundamentals, these 20 questions cover the essential concepts you’re likely to encounter.
1. What is SQL, and what are its different types?
SQL is a standardized programming language used for managing and manipulating relational databases. There are several types of SQL commands:
- DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE – used to define database schema
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE – used to manipulate data
- DCL (Data Control Language): GRANT, REVOKE – used to control access to data
- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT – used to manage transactions
2. What is the difference between DELETE and TRUNCATE?
DELETE is a DML command that removes rows one at a time and logs each deletion, allowing you to use a WHERE clause to delete specific rows. It can be rolled back, and triggers are activated. TRUNCATE is a DDL command that removes all rows from a table at once without logging individual row deletions. It’s faster, cannot be rolled back (in most databases), doesn’t activate triggers, and resets identity columns.
3. Explain the different types of JOINs in SQL
- INNER JOIN: Returns only matching rows from both tables
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table; NULL for non-matches
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table; NULL for non-matches
- FULL JOIN (or FULL OUTER JOIN): Returns all rows when there’s a match in either table; NULL for non-matches
- CROSS JOIN: Returns the Cartesian product of both tables
- SELF JOIN: A table joined with itself
4. What are a Primary Key and a Foreign Key?
A Primary Key uniquely identifies each record in a table. It cannot contain NULL values, and each table can have only one primary key (which can consist of single or multiple columns). A Foreign Key is a column or set of columns in one table that references the primary key in another table. It establishes relationships between tables and helps maintain referential integrity.
5. What is the difference between WHERE and HAVING clauses?
WHERE is used to filter rows before grouping occurs and cannot be used with aggregate functions. It works with individual rows. HAVING is used to filter groups after the GROUP BY clause has been applied and can be used with aggregate functions. For example, WHERE filters employees before calculating department averages, while HAVING filters departments after calculating those averages.
6. Explain SQL indexes and their types
Indexes are database objects that improve query performance by providing faster data retrieval. Types include:
- Clustered Index: Determines the physical order of data in a table; only one per table
- Non-Clustered Index: Creates a separate structure with pointers to data; multiple allowed per table
- Unique Index: Ensures all values in indexed columns are unique
- Composite Index: Index on multiple columns
- Full-Text Index: Used for text searching in large text columns
7. What is normalization, and what are its types?
Normalization is the process of organizing data to reduce redundancy and improve data integrity. The normal forms are:
- 1NF (First Normal Form): Eliminate repeating groups; each column contains atomic values
- 2NF (Second Normal Form): Meet 1NF and remove partial dependencies
- 3NF (Third Normal Form): Meet 2NF and remove transitive dependencies
- BCNF (Boyce-Codd Normal Form): A stricter version of 3NF
- 4NF and 5NF: Address multi-valued and join dependencies
8. What are aggregate functions in SQL?
Aggregate functions perform calculations on a set of values and return a single value. Common ones include COUNT (counts rows), SUM (adds numeric values), AVG (calculates average), MAX (finds maximum value), MIN (finds minimum value), and GROUP_CONCAT or STRING_AGG (concatenates strings from multiple rows).
9. What is a subquery, and what are its types?
A subquery is a query nested inside another query. Types include:
- Single-row subquery: Returns one row
- Multiple-row subquery: Returns multiple rows
- Correlated subquery: References columns from the outer query
- Non-correlated subquery: Independent of the outer query
- Scalar subquery: Returns a single value
10. Explain the difference between UNION and UNION ALL
UNION combines result sets from multiple SELECT statements and removes duplicate rows, requiring additional processing. UNION ALL also combines result sets but keeps all rows, including duplicates, making it faster. Both require the same number of columns with compatible data types in the same order.
11. What are constraints in SQL?
Constraints enforce rules on data in tables. Common ones include:
- NOT NULL: Column cannot have NULL values
- UNIQUE: All values in the column must be different
- PRIMARY KEY: Combination of NOT NULL and UNIQUE
- FOREIGN KEY: Links two tables together
- CHECK: Ensures values meet a specific condition
- DEFAULT: Sets a default value for a column
12. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?
These are window functions used for ranking. ROW_NUMBER assigns unique sequential numbers (1, 2, 3, 4…) regardless of duplicates. RANK assigns the same rank to ties but skips subsequent ranks (1, 2, 2, 4…). DENSE_RANK assigns the same rank to ties without skipping ranks (1, 2, 2, 3…).
13. Explain transactions and ACID properties
A transaction is a logical unit of work containing one or more SQL statements. ACID properties ensure reliable processing:
- Atomicity: All operations succeed, or all fail
- Consistency: Database moves from one valid state to another
- Isolation: Concurrent transactions don’t interfere with each other
- Durability: Committed changes are permanent
14. What is the difference between CHAR and VARCHAR?
CHAR is a fixed-length data type that always uses the specified amount of storage, padding with spaces if necessary. It’s faster for fixed-length data. VARCHAR is a variable-length data type that uses only the space needed for the actual data plus overhead bytes. It’s more storage-efficient for varying lengths.
15. What are views in SQL?
A view is a virtual table based on a SQL query. It doesn’t store data itself but displays data from one or more tables. Views simplify complex queries, provide security by restricting access to specific data, present data in different formats, and maintain logical data independence. Views can be either updatable or read-only, depending on their complexity.
16. Explain the GROUP BY clause
GROUP BY groups rows with the same values in specified columns into summary rows. It’s typically used with aggregate functions to perform calculations on each group. For example, grouping sales by region to calculate total sales per region, or grouping employees by department to count employees per department.
17. What is a stored procedure, and what are its advantages?
A stored procedure is a prepared SQL code that you can save and reuse. Advantages include improved performance through precompilation, reduced network traffic, enhanced security through access control, code reusability, easier maintenance, and the ability to encapsulate complex business logic.
18. What are triggers in SQL?
Triggers are special stored procedures that automatically execute when specific events occur in a database. Types include BEFORE triggers (execute before an operation), AFTER triggers (execute after an operation), and INSTEAD OF triggers (replace the operation). They’re used for enforcing business rules, maintaining audit trails, validating data, and synchronizing tables.
19. Explain the difference between clustered and non-clustered indexes
A clustered index determines the physical order of data storage in the table, meaning the table data is sorted according to the clustered index key. Only one clustered index can exist per table. A non-clustered index creates a separate structure that contains the indexed columns and a pointer to the actual data row. Multiple non-clustered indexes can exist on a table.
20. What is a CTE (Common Table Expression)?
A CTE is a temporary named result set that exists within the scope of a single statement. Defined using the WITH clause, CTEs improve query readability, can be referenced multiple times in the same query, and support recursion. They’re useful for breaking down complex queries, performing recursive operations such as those found in organizational hierarchies, and making code more maintainable.
Final Tips for SQL Interviews
When preparing for SQL interviews, practice writing queries by hand, understand the underlying concepts rather than memorizing syntax, and be ready to explain your thought process. Many interviewers value a problem-solving approach as much as the correct answer. Consider practicing on platforms like LeetCode, HackerRank, or SQLZoo to gain hands-on experience with various query scenarios.