50 SQL Interview Questions

Basic Concepts

1. What is SQL?
SQL (Structured Query Language) is a domain-specific programming language used for
managing and manipulating relational databases. It serves as the standard interface for
database management systems, allowing users to query data, update records, create
structures, and control access. SQL uses declarative statements to specify desired
outcomes rather than procedural steps and is widely used in systems like MySQL,
PostgreSQL, Oracle, and SQL Server.

2. What is a Database?
A database is an organized collection of structured information stored electronically in a
computer system. It represents a systematic approach to data storage that enables efficient
data retrieval, modification, and management. Databases eliminate data redundancy,
ensure data integrity, and provide concurrent access to multiple users while maintaining
consistency.

3. What is a RDBMS?
A Relational Database Management System (RDBMS) is software that manages
relational databases, organizing data into tables with rows and columns and establishing
relationships through keys. RDBMSs implement ACID properties (Atomicity,
Consistency, Isolation, Durability) to ensure transaction reliability and data integrity.
Examples include MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server.

4. What are Tables and Fields?

Tables are two-dimensional structures that store data in rows and columns within a
relational database. Fields (columns) define the data types and properties, while rows
represent individual records or instances of the entity.

5. What is a Primary Key?

A Primary Key is a column or combination of columns that uniquely identifies each row
in a table. It cannot contain NULL values and ensures entity integrity by preventing
duplicate records.

6. What is a Foreign Key?

A Foreign Key is a column that references the primary key of another table, establishing
relationships between tables. It enforces referential integrity by ensuring values
correspond to existing records in the referenced table.

7. What is a Unique Key?

A Unique Key constraint ensures all values in specified columns are distinct across table
rows. Unlike primary keys, it can accept NULL values and multiple unique constraints
can exist per table.
 8. What are SQL Constraints?
SQL Constraints are rules applied to columns or tables to enforce data integrity and
prevent invalid data entry. Common types include NOT NULL, CHECK, DEFAULT,
PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints.

9. What are the different types of SQL commands (subsets)?

1. DDL (Data Definition Language): Commands that define and modify database
structure, including CREATE, ALTER, DROP, and TRUNCATE statements.

2. DML (Data Manipulation Language): Commands for data manipulation operations

such as INSERT, UPDATE, DELETE, and SELECT statements.

3. DCL (Data Control Language): Commands controlling access permissions and

security, including GRANT and REVOKE statements.

4. TCL (Transaction Control Language): Commands managing database transactions,

including COMMIT, ROLLBACK, and SAVEPOINT statements.

10. What is a NULL value? How is it different from zero or a blank space?
NULL represents the absence of data or unknown values in a database field. Unlike zero
(numeric value) or blank spaces (actual characters), NULL indicates no data exists and
requires special operators like IS NULL for comparison operations.

Data Retrieval and Manipulation

11. Explain the SELECT statement.

The fundamental SQL command used to retrieve data from database tables. It specifies
which columns to fetch and from which tables.

12. How do you retrieve all columns from a table?

Use SELECT * FROM table_name where the asterisk (*) represents all columns in the
table.

13. How do you select specific columns from a table?

List column names after SELECT: SELECT column1, column2 FROM table_name to
retrieve only those specified columns.

14. How do you filter data using the WHERE clause?

Add conditions after the table name: SELECT * FROM table_name WHERE condition to
filter rows based on specific criteria.

15. What is the DISTINCT keyword used for?

Removes duplicate rows from query results. Use SELECT DISTINCT column_name FROM
table_name to get unique values only.

16. How do you sort results in SQL?

 Use ORDER BY clause: SELECT * FROM table_name ORDER BY column_name
ASC/DESC to sort in ascending or descending order.

17. What are Aggregate Functions? List some common ones.

Perform calculations on groups of rows. Common ones include COUNT(), SUM(),
AVG(), MAX(), MIN().

18. Explain the GROUP BY clause.

Groups rows with the same values in specified columns together, typically used with
aggregate functions to perform calculations per group.

19. What is the HAVING clause and how is it different from WHERE?
WHERE filters individual rows before grouping, while HAVING filters groups after
GROUP BY and aggregate functions are applied.

20. How do you insert data into a table?

Use INSERT INTO table_name (columns) VALUES (values) or INSERT INTO table_name
VALUES (values) for all columns.

21. How do you update existing data in a table?

Use UPDATE table_name SET column1 = value1 WHERE condition to modify existing
records matching the condition.

22. How do you delete data from a table?

Use DELETE FROM table_name WHERE condition to remove specific rows that match the
given condition.

23. What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows and can use WHERE clause, while TRUNCATE
removes all rows faster but cannot be conditional.

24. What is the difference between TRUNCATE and DROP?

TRUNCATE removes all data but keeps table structure, while DROP removes the entire
table including structure and data permanently.

Joins and Relationships

25. What is a JOIN in SQL?

A JOIN combines rows from two or more tables based on a related column between
them, allowing you to retrieve data from multiple tables in a single query.

26. Explain the different types of JOINs (INNER, LEFT, RIGHT, FULL OUTER).

1. INNER JOIN: Returns only matching rows from both tables

2. LEFT JOIN: Returns all rows from left table and matching rows from right table
3. RIGHT JOIN: Returns all rows from right table and matching rows from left table
 4. FULL OUTER JOIN: Returns all rows from both tables, with NULLs where no
match exists

27. What is a Self-Join?

A join where a table is joined with itself, typically used to compare rows within the
same table or find hierarchical relationships.

28. What is a Cross Join?

Returns the Cartesian product of two tables, combining every row from the first table
with every row from the second table. No join condition is specified.

29. What are the different types of relationships in a database?

1. One-to-One: Each record in one table relates to exactly one record in another
2. One-to-Many: One record relates to multiple records in another table
3. Many-to-Many: Multiple records in one table relate to multiple records in another
table

Advanced Concepts

30. What is a Subquery? What are its types?

A query nested inside another query. Types include scalar (returns single value), row
(returns single row), column (returns single column), and table subqueries (returns
multiple rows/columns).

31. What is a View in SQL? What are its benefits?

A virtual table based on SQL query results that doesn't store data physically. Benefits
include data security, query simplification, and presenting data in different formats
without altering base tables.

32. What are Indexes in SQL? What are their types and benefits?
Database structures that improve query performance by creating shortcuts to data. Types
include clustered, non-clustered, unique, and composite indexes. Benefits are faster
SELECT queries and efficient sorting.

33. Explain the difference between a Clustered and Non-Clustered Index.

Clustered index physically reorders table data and allows only one per table, while non-
clustered index creates separate structure pointing to data rows.

34. What are Stored Procedures? What are their advantages?

Pre-compiled SQL code blocks stored in database. Advantages include better
performance, code reusability, enhanced security, and centralized business logic
implementation.
35. What are Triggers in SQL?
Special stored procedures that automatically execute in response to database events like
INSERT, UPDATE, or DELETE operations on tables.

36. Explain ACID properties in the context of database transactions.

Atomicity (all-or-nothing transactions), Consistency (database remains valid), Isolation
(concurrent transactions don't interfere), Durability (committed changes persist
permanently).

37. What is Normalization? Explain different Normal Forms (1NF, 2NF, 3NF).
Process of organizing data to reduce redundancy. 1NF eliminates repeating groups, 2NF
removes partial dependencies, 3NF eliminates transitive dependencies.

38. What is Denormalization? When would you use it?

Intentionally introducing redundancy to improve query performance. Used when read
performance is more critical than storage space or when complex joins slow down
queries.

39. What are Window Functions? Give an example.

Perform calculations across related rows without grouping them. Example:
ROW_NUMBER() OVER (ORDER BY salary DESC) assigns sequential numbers to
rows.

40. What are Common Table Expressions (CTEs)?

Temporary named result sets that exist only during query execution. Useful for breaking
complex queries into readable parts and recursive operations.

41. How do you handle duplicate rows in SQL?

Use DISTINCT to remove duplicates in SELECT, or identify duplicates using GROUP
BY with HAVING COUNT(*) > 1, then DELETE using ROW_NUMBER() window
function.

42. How can you find the Nth highest salary in a table?
Use SELECT DISTINCT salary FROM table ORDER BY salary DESC LIMIT 1
OFFSET N-1 or window functions like DENSE_RANK() OVER (ORDER BY salary
DESC).

43. What are SQL functions? What are the different types?
Built-in operations that process data. Types include aggregate (SUM, COUNT), scalar
(UPPER, LOWER), date/time (NOW, DATEADD), and string functions (CONCAT,
SUBSTRING).

44. How do you prevent SQL Injection attacks?

Use parameterized queries/prepared statements, input validation, stored procedures, and
avoid dynamic SQL construction with user input concatenation.
45. What is UNION vs UNION ALL?
UNION removes duplicate rows and sorts results, while UNION ALL includes all rows
including duplicates and is faster.

46. What is the CASE statement in SQL?

Conditional logic in SQL that returns different values based on conditions. Syntax: CASE
WHEN condition THEN result ELSE default_result END.

47. How would you optimize a slow-performing SQL query?

Add appropriate indexes, avoid SELECT *, use WHERE clauses effectively, optimize
JOINs, analyze execution plans, and consider query rewriting.

48. What is a Schema in SQL?

Logical container that organizes database objects like tables, views, and procedures.
Provides namespace separation and security boundaries within a database.

49. What is a Transaction in SQL?

Group of SQL statements that execute as a single unit, following ACID properties. Either
all statements succeed (COMMIT) or all fail (ROLLBACK).

50. Explain the order of execution of a SELECT statement.

The execution order of a SELECT statement, from top to bottom, is: FROM, WHERE,
GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT/OFFSET.