Master 100+ SQL Interview Questions and Answers (2025)

SQL interview questions are a critical part of technical hiring for data analysts, data engineers, backend developers, and database administrators. Mastering SQL interview questions helps you land your dream job and sharpens your ability to work with real-world datasets. In this post, we’ve compiled the 100+ SQL interview questions and Answers covering basic queries, advanced joins, optimization techniques, and real-world problem-solving, ensuring you’re fully prepared for any technical screening.

For advanced topics, don’t miss this guide on SQL Analytical Functions Interview Questions and Answers

Q1. What is SQL? How is it different from NoSQL?

SQL (Structured Query Language) is a standard programming language designed for managing and manipulating relational databases. It allows users to create, read, update, and delete data in a structured format using tables with predefined schemas.

Key Characteristics of SQL:

• Uses tables with rows and columns
• Follows ACID properties (Atomicity, Consistency, Isolation, Durability)
• Requires predefined schema
• Excellent for complex queries
• Vertical scaling (scale-up by adding more power to a single server)

Example SQL Database (MySQL):

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    registration_date DATE
);

INSERT INTO customers (customer_id, name, email, registration_date)
VALUES (1, 'John Doe', 'john@example.com', '2023-01-15');

SELECT * FROM customers WHERE registration_date > '2023-01-01';

What is NoSQL?

NoSQL (Not Only SQL) databases provide a mechanism for storage and retrieval of data that is modeled differently from relational databases. They are designed for distributed data stores and big data applications.

Key Characteristics of NoSQL:

• Schema-less data model
• Horizontal scaling (scale-out by adding more servers)
• BASE properties (Basically Available, Soft state, Eventual consistency)
• Various data models: document, key-value, wide-column, graph
• Optimized for specific data patterns and access

Example NoSQL Database (MongoDB):

// Insert a document
db.customers.insertOne({
    _id: 1,
    name: "John Doe",
    email: "john@example.com",
    registration_date: new Date("2023-01-15"),
    addresses: [
        { type: "home", street: "123 Main St", city: "Anytown" }
    ]
});

// Find documents
db.customers.find({ registration_date: { $gt: new Date("2023-01-01") } });

Key Differences Between SQL and NoSQL

Feature	SQL Databases	NoSQL Databases
Data Model	Table-based with fixed schema	Flexible (document, key-value, etc.)
Scalability	Vertical	Horizontal
Transactions	ACID compliant	BASE principles (eventual consistency)
Joins	Supported	Typically not supported
Schema	Required before writing data	Dynamic, can vary per document
Examples	MySQL, PostgreSQL, Oracle	MongoDB, Cassandra, Redis
Best Use Cases	Complex queries, transactions	Large datasets, flexible schema

Q2. Explain SELECT, INSERT, UPDATE, DELETE commands.

1. DDL (Data Definition Language)

Commands used to define the database structure/schema.

Key DDL Commands:

• CREATE: Creates database objects (tables, views, etc.)
• ALTER: Modifies existing database objects
• DROP: Removes database objects
• TRUNCATE: Removes all records from a table (but keeps the structure)
• RENAME: Renames database objects

Example:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

ALTER TABLE employees ADD COLUMN hire_date DATE;

DROP TABLE employees;

2. DML (Data Manipulation Language)

Commands used for managing data within schema objects.

Key DML Commands:

• SELECT: Retrieves data from the database
• INSERT: Adds new data to a table
• UPDATE: Modifies existing data
• DELETE: Removes data from a table
• MERGE: Performs insert, update, or delete operations in a single statement

Example:

INSERT INTO employees (emp_id, name, department, salary)
VALUES (101, 'Alice Smith', 'Marketing', 75000.00);

UPDATE employees SET salary = 80000.00 WHERE emp_id = 101;

DELETE FROM employees WHERE emp_id = 101;

SELECT * FROM employees WHERE department = 'Marketing';

3. TCL (Transaction Control Language)

Commands used to manage transactions in the database.

Key TCL Commands:

• COMMIT: Saves all transactions to the database
• ROLLBACK: Undoes transactions that haven’t been committed
• SAVEPOINT: Creates points within groups of transactions to roll back to

Example:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;

-- If everything is OK
COMMIT;

-- If there's an error
ROLLBACK;

4. DCL (Data Control Language)

Commands used for rights, permissions, and other controls.

Key DCL Commands:

• GRANT: Gives user access privileges
• REVOKE: Takes back privileges granted to user

Example:

GRANT SELECT, INSERT ON employees TO user1;

REVOKE INSERT ON employees FROM user1;

Detailed Explanation of SELECT, INSERT, UPDATE, DELETE

SELECT Command

Retrieves data from one or more tables.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s)
LIMIT number;

Examples:

-- Simple select
SELECT * FROM employees;

-- Select with conditions
SELECT name, salary FROM employees 
WHERE department = 'Sales' AND salary > 50000;

-- Aggregate functions
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Join example
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

INSERT Command

Adds new rows to a table.

Basic Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Examples:

-- Insert single row
INSERT INTO employees (emp_id, name, department, salary)
VALUES (102, 'Bob Johnson', 'IT', 85000.00);

-- Insert multiple rows
INSERT INTO employees (emp_id, name, department, salary)
VALUES 
    (103, 'Carol Williams', 'HR', 65000.00),
    (104, 'David Brown', 'IT', 90000.00);

-- Insert from another table
INSERT INTO managers (emp_id, name, department)
SELECT emp_id, name, department FROM employees
WHERE salary > 80000;

UPDATE Command

Modifies existing data in a table.

Basic Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Examples:

-- Update specific rows
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT';

-- Update multiple columns
UPDATE employees
SET salary = 95000.00, department = 'Management'
WHERE emp_id = 104;

-- Update with subquery
UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE emp_id = 102;

DELETE Command

Removes rows from a table.

DELETE FROM table_name
WHERE condition;

Examples:

-- Delete specific rows
DELETE FROM employees
WHERE emp_id = 102;

-- Delete with condition
DELETE FROM employees
WHERE department = 'HR' AND salary < 50000;

-- Delete all rows (careful!)
DELETE FROM employees;

Q3. Difference between TRUNCATE and DELETE.

DELETE Command

• Removes rows one by one and generates an entry in the transaction log for every row deleted.
• Can be used with WHERE clause to filter rows
• Can be rolled back (if within a transaction)
• Doesn’t reset the high water mark
• Fires triggers if they exist
• Doesn’t release storage space back to the system

Example:

BEGIN TRANSACTION;
DELETE FROM large_table WHERE condition;
-- Can rollback if needed
ROLLBACK;
-- Or commit
COMMIT;

TRUNCATE Command

• Doesn’t remove row by row. Instead, it deallocates the data pages that store the table rows (much faster).
• Cannot use WHERE clause (always removes all rows)
• Cannot be rolled back (in most databases)
• Resets the high water mark
• Doesn’t fire triggers
• Releases storage space back to the system

Example:

TRUNCATE TABLE large_table;
-- Cannot rollback after execution

Q4. What is High Water Mark?

The high water mark (HWM) is the maximum amount of space that has been used by a table segment. When data is inserted into a table, the HWM moves up. When data is deleted, the HWM stays where it is (unless you truncate the table).

Performance Impact:

• Full table scans must read all blocks up to the HWM, even if they’re empty
• This can significantly impact performance for tables that had lots of data but now have little
• TRUNCATE resets the HWM, improving performance for subsequent operations
• DELETE doesn’t reset the HWM, so performance may degrade over time with many deletes

Example Scenario:

1. Create and populate a large table:

CREATE TABLE test_table (id NUMBER, data VARCHAR2(4000));

-- Insert 1 million rows
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO test_table VALUES (i, RPAD('X', 4000, 'X'));
  END LOOP;
  COMMIT;
END;

2. Check the space usage (HWM is high now)
3. Delete all rows:

DELETE FROM test_table;
COMMIT;
-- HWM remains at the same high level

4. Queries will still scan all blocks up to the original HWM, even though the table is empty
5. Truncate instead:

TRUNCATE TABLE test_table;
-- HWM is reset to zero
-- Future queries will be much faster as they don't scan empty blocks

When to Use Each:

• Use DELETE when:
  • You need to remove specific rows (with WHERE clause)
  • You need the operation to be transactional (can rollback)
  • You have triggers that need to fire
• Use TRUNCATE when:
  • You need to remove all rows from a table
  • You want better performance (much faster for large tables)
  • You want to reset the HWM and free up space
  • You don’t need the operation to be transactional