Top 10 SQL Theory Questions Every Data Analyst Should Master in 2025

Vinayak October 11, 2025

 

SQL (Structured Query Language) is the foundation of data analysis, business intelligence, and data engineering.

Whether you’re a fresher preparing for interviews or a professional looking to strengthen your fundamentals, mastering SQL theory will help you stand out from the crowd.

Let’s dive into the Top 10 SQL Theory Questions that are frequently asked in interviews — explained simply and practically 👇

🧠 1️⃣ What is the difference between DDL, DML, and DCL in SQL?

DDL (Data Definition Language) – Deals with structure of the database.
Examples:

  • CREATE – Create new tables or databases

  • ALTER – Modify existing table

  • DROP – Delete table/database

DML (Data Manipulation Language) – Deals with data inside the tables.
Examples:

  • INSERT, UPDATE, DELETE, SELECT

DCL (Data Control Language) – Deals with permissions and access control.
Examples:

  • GRANT, REVOKE

💡 Tip: Remember — DDL changes structure, DML changes data, DCL changes rights.

🧠 2️⃣ What is the difference between WHERE and HAVING clause?

  • WHERE is used to filter rows before aggregation.

  • HAVING is used to filter groups after aggregation.

Example:

SELECT department, COUNT(*) AS EmpCount
FROM employees
WHERE salary > 30000
GROUP BY department
HAVING COUNT(*) > 5;

Here, WHERE filters data before grouping, while HAVING filters the grouped results.

💬 Think of WHERE as row-level filter and HAVING as aggregate-level filter.

🧠 3️⃣ What are joins in SQL?

A JOIN combines rows from two or more tables based on a related column.
Types of joins:

  1. INNER JOIN – Returns only matching rows.

  2. LEFT JOIN – All rows from left table + matching from right.

  3. RIGHT JOIN – All rows from right table + matching from left.

  4. FULL OUTER JOIN – Returns all rows when there’s a match in either table.

  5. CROSS JOIN – Cartesian product (every row from table A with every row from table B).

💡 Remember:

  • INNER = intersection

  • LEFT = left + intersection

  • FULL = union of both sides

🧠 4️⃣ What is Normalization and why is it important?

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

Normal forms (NF):

  • 1NF: No repeating groups or arrays.

  • 2NF: Must be in 1NF and have no partial dependency.

  • 3NF: Must be in 2NF and have no transitive dependency.

Benefits:
✅ Eliminates duplicate data
✅ Increases data consistency
✅ Easier updates and maintenance

💬 Example: Splitting customer and order details into separate tables linked by a foreign key is a classic normalization step.

🧠 5️⃣ What is the difference between Primary Key, Unique Key, and Foreign Key?

  • Primary Key:

    • Uniquely identifies each row in a table.

    • Cannot be NULL.

  • Unique Key:

    • Ensures all values in a column are unique.

    • Can contain one NULL value.

  • Foreign Key:

    • Establishes a link between two tables.

    • References the primary key of another table.

💡 Example:

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

🧠 6️⃣ What are Indexes in SQL and why are they used?

An Index improves the speed of data retrieval by creating a quick lookup reference for columns.

Types:

  • Clustered Index: Sorts the actual data rows. (One per table)

  • Non-Clustered Index: Creates a separate structure pointing to the data.

Trade-off:
✅ Faster SELECT
❌ Slower INSERT / UPDATE (since indexes must be maintained)

💬 Tip: Create indexes on columns used frequently in WHERE, JOIN, and ORDER BY.

🧠 7️⃣ What is the difference between DELETE, TRUNCATE, and DROP?

CommandActionRollbackAffects StructureSpeed
DELETERemoves rows✅ Yes❌ NoSlow
TRUNCATERemoves all rows❌ No❌ NoFast
DROPDeletes table❌ No✅ YesVery Fast

💬 Use DELETE when you need to conditionally remove rows, TRUNCATE for clearing data, and DROP when removing the entire table.

🧠 8️⃣ What are Views in SQL?

A View is a virtual table based on a SQL query. It doesn’t store data physically — it just stores the query definition.

Example:

CREATE VIEW SalesSummary AS
SELECT region, SUM(amount) AS TotalSales
FROM Sales
GROUP BY region;

Benefits:
✅ Simplifies complex queries
✅ Provides security by restricting direct table access
✅ Enhances reusability

💡 Think of it as a “window” to see data in a specific way.

🧠 9️⃣ What is ACID property in SQL transactions?

ACID stands for:

  • A – Atomicity: Transaction is all-or-nothing.

  • C – Consistency: Database remains valid before and after transaction.

  • I – Isolation: Transactions are independent of each other.

  • D – Durability: Once committed, data is permanent.

💬 Example: When transferring money between two accounts, both debit and credit must happen — or none should. That’s atomicity in action.

🧠 🔟 What is the difference between INNER JOIN and SUBQUERY?

Both can return the same results, but they differ in structure:

INNER JOIN: Combines tables horizontally.
SUBQUERY: Fetches data vertically (query inside another query).

Example:

-- Using JOIN
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- Using SUBQUERY
SELECT name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'NY');

💡 Use JOIN for performance when combining multiple tables; SUBQUERY is useful for nested logic or filtering.