Basic SQL Interview Questions

What is SQL?

What are the different types of SQL statements?
  DDL, DML, DCL, TCL
What is the difference between WHERE and HAVING?
What is a primary key?
What is a foreign key?
What is the difference between INNER JOIN and LEFT JOIN?
What is the difference between DELETE, TRUNCATE, and DROP?
What is normalization? Explain its types.
What is denormalization?
What are constraints in SQL?
    NOT NULL, UNIQUE, CHECK, etc.

Intermediate SQL Interview Questions

What is a subquery? How is it different from a JOIN?
What are indexes? What types are there?
What are aggregate functions in SQL?
SUM(), AVG(), COUNT(), etc.
Explain GROUP BY and ORDER BY.
What is the difference between UNION and UNION ALL?
What are views? How are they useful?
What is a stored procedure?
What is a trigger in SQL?
Explain window functions with examples.
What is the difference between correlated and non-correlated subqueries?

Advanced SQL Interview Questions

How do you optimize SQL queries for performance?
What is a CTE (Common Table Expression)? How is it used?
Explain indexing strategies.
How does SQL handle transactions?
ACID properties
What is a deadlock in SQL? How do you resolve it?
What are recursive queries? Give an example.
Explain RANK(), DENSE_RANK(), and ROW_NUMBER().
What is the difference between EXISTS and IN?
What does EXPLAIN PLAN do?
What is the difference between horizontal and vertical partitioning?

Practical Scenario-Based Questions

Write a SQL query to find the second highest salary.
Write a query to find duplicate records in a table.
Write a query to transpose rows to columns.
How do you update one table based on data from another table?
How do you retrieve the Nth highest value from a column?

What is SQL?

SQL (Structured Query Language) is a standard programming language used to communicate with and manage data in relational database management systems (RDBMS) like MySQL, PostgreSQL, Microsoft SQL Server, and Oracle.

What SQL is used for:

SQL allows you to:

• Query data from a database (SELECT)

• Insert new records (INSERT)

• Update existing records (UPDATE)

• Delete records (DELETE)

• Create and modify database structures like tables and indexes (CREATE, ALTER, DROP)

• Control access to data (GRANT, REVOKE)

What are the different types of SQL statements?

SQL statements are categorized into five main types, each serving a specific purpose in managing and manipulating relational databases:

1. Data Definition Language (DDL)

These statements define the structure of the database objects like tables, indexes, and views.

Statements      Purpose

CREATE        Creates a new table, view, or database

ALTER           Modifies an existing object

DROP            Deletes an object

TRUNCATE   Removes all records from a table

RENAME      Renames a database object

2. Data Manipulation Language (DML)

These statements are used to manipulate data within tables.

Statements      Purpose

SELECT       Retrieves data

INSERT           Adds new records

UPDATE         Modifies existing records

DELETE         Removes records

3. Data Control Language (DCL)

These statements manage access to the data in the database.

Statements      Purpose

GRANT         Gives user access privileges

REVOKE       Withdraws access privileges

4. Transaction Control Language (TCL)

These statements manage changes made by DML statements and control transaction behavior.

Statements      Purpose

COMMIT       Saves all changes made in the transaction

ROOLBACK   Undoes changes if something goes wrong

SAVEPOINT    Sets a point to roll back to later

SET TRANSACTION Defines transaction properties

5. Data Query Language (DQL)

Some categorize SELECT separately as DQL since it's used strictly for querying (not changing) data.

Statements      Purpose

SELECT         Retrieves data from tables

What is the difference between WHERE and HAVING?

The main difference between WHERE and HAVING in SQL is when and how they are used to filter data.

WHERE

• Filters rows before any grouping or aggregation.

• Used with SELECT, UPDATE, DELETE statements.

• Cannot be used with aggregate functions (like SUM(), AVG(), etc.)

Example:

sql

SELECT * FROM employees
WHERE department = 'Sales';

Filters individual rows where department = 'Sales'.

HAVING

• Filters groups after aggregation (GROUP BY).

• Used only with GROUP BY or aggregate functions.

• Can use aggregate functions like COUNT(), SUM().

Example:

sql

SELECT department, COUNT(*) as total
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Filters departments with more than 5 employees (after grouping).

Summary Table:
Feature
WHERE
HAVING
Filters
   Before grouping

After grouping
Used with
   All types of queries
Only with GROUP BY / aggregates
Aggregates
   Cannot use aggregate functions    
Can use aggregate functions
Execution
   Row-level filtering
Group-level filtering

What is a primary key?

A primary key is a column or set of columns in a database table that uniquely 
identifies each row in that table.

Key Characteristics of a Primary Key:

1. Uniqueness: Each value must be unique across the table.

2. Not Null: A primary key column cannot contain NULL values.

3. Only one per table: A table can have only one primary key, but it can consist of multiple columns (called a composite key).

Example:

sql

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

Here, student_id is the primary key, ensuring every student has a unique ID.

Composite Primary Key Example:
sql

CREATE TABLE course_enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

This ensures that each student can enroll in a course only once — the 
combination must be unique.
Why Primary Keys Are Important:
They prevent duplicate records.
They establish relationships between tables (e.g., foreign keys).
They improve data integrity and query performance.

What is a foreign key?

A foreign key is a column (or set of columns) in one table that links to the primary key 
in another table. It is used to establish a relationship between two tables in a 
relational database.

Key Characteristics of a Foreign Key:
Maintains referential integrity between related tables.
The values in a foreign key column must match values in 
the referenced primary key column.
A foreign key can contain duplicate values and NULLs (unless specified otherwise).
Example:
SQL

-- Parent table
CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(100)
);

-- Child table with foreign key
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

In this example:
departments.dept_id is the primary key.
employees.dept_id is a foreign key pointing to departments.dept_id.
Why Use Foreign Keys?
To link related data across tables.
To ensure data consistency 
(e.g., you can't assign an employee to a non-existent department).
To help define relationships in normalized databases 
(e.g., one-to-many, many-to-many).

What is the difference between INNER JOIN and LEFT JOIN?

Use an inner join when you want to retrieve only the rows where there is a match in both tables based on the join condition. Use a left join when you want to retrieve all rows from the left table and the matched rows from the right table, with NULL values for non-matching rows.

INNER JOIN 
  Returns only matching rows from both tables. 
 If there's no match, the row is excluded from the result.

LEFT JOIN (or LEFT OUTER JOIN) 
Returns all rows from the left table and matched rows from the right table. 
 If there's no match, columns from the right table return NULL.

What is the difference between DELETE, TRUNCATE, and DROP?

The DROP command in SQL removes the table from the database, the DELETE command removes one or more records from the table, and the TRUNCATE command removes all the rows from the existing table.

1. DELETE 
 Removes specific rows from a table using a WHERE clause.
 Can be rolled back if used within a transaction. 
 Triggers (if defined) will fire. 
 Table structure remains intact.

SQL
DELETE FROM employees WHERE dept_id = 5;

 2. TRUNCATE 
 Removes all rows from a table very quickly. 
 Cannot be rolled back in many databases (especially outside of transactions).
 Triggers do not fire. 
 Table structure and schema stay intact. 
 Resets auto-increment counters (in most RDBMS).

SQL
TRUNCATE TABLE employees;

 3. DROP 
 Completely deletes the table from the database. 
 Removes data, structure, constraints, indexes, and triggers. 
 Cannot be rolled back. 
 Table is no longer usable unless recreated.

SQL 
DROP TABLE employees;

What is normalization? Explain its types.