Medium-Level SQL Questions & Answers

1. What is the difference between INNER JOIN and LEFT JOIN?

• INNER JOIN returns only those rows where there is a match in both tables based on the join
condition.

• LEFT JOIN returns all records from the left table and the matched ones from the right table;
unmatched right-side values become NULL.

• It’s useful when you need complete data from one table but only matched data from
another.

• Example: Fetch all employees with their department names.

• If some employees don’t belong to a department, LEFT JOIN will still show them with NULL
department names.

2. What is a SELF JOIN?

• A SELF JOIN is when a table joins with itself as if it were two different tables.

• You use table aliases to differentiate them.

• Common use case: hierarchical data such as employees and managers stored in one table.

• It helps to compare rows in the same table.

• Example: Show each employee’s manager name from the same employee table.

3. What is a CROSS JOIN?

• A CROSS JOIN returns a Cartesian product — every row from table A is paired with every row
from table B.

• If Table A has 3 rows and Table B has 4, output = 12 rows.

• It’s rarely used in production but can generate combinations.

• It does not require any join condition.

• Example: Generating all color-size combinations for a product list.

4. What is the difference between JOIN and UNION?

• JOIN combines columns from multiple tables horizontally.

• UNION combines rows vertically from multiple queries.

• JOINs use related columns, while UNION needs the same column count and datatype.
• UNION removes duplicates unless you use UNION ALL.

• Example: Combine two SELECT statements from the same structure tables using UNION.

5. What is an EQUI JOIN?

• An EQUI JOIN joins two or more tables using equality (=) in the condition.

• It’s the most common form of join (like INNER JOIN).

• Example: SELECT * FROM emp e JOIN dept d ON e.dept_id = d.dept_id;

• It retrieves data where values in the join columns match.

• It can also be used with WHERE clause joins in older SQL syntax.

6. What is a NATURAL JOIN?

• A NATURAL JOIN automatically joins tables using all columns with the same names and
datatypes.

• It doesn’t require explicit ON conditions.

• However, it can cause issues if unintended same-named columns exist.

• It simplifies simple joins but is less safe in complex schemas.

• Example: SELECT * FROM emp NATURAL JOIN dept;

7. What is a subquery and its types?

• A subquery is a query nested inside another query.

• It helps filter, calculate, or aggregate data dynamically.

• Types include single-row, multi-row, correlated, and scalar subqueries.

• Example: SELECT name FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

• Correlated subqueries depend on outer query rows for execution.

8. What is a correlated subquery?

• A correlated subquery runs once per row of the outer query.

• It references a column from the outer query inside the inner one.

• Useful for comparisons like “find employees earning above their department average.”

• It’s slower because it executes multiple times.

• Example:
• SELECT name

• FROM emp e

• WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept_id = e.dept_id);

9. What is a Common Table Expression (CTE)?

• A CTE is a temporary result set defined using WITH clause.

• It makes queries cleaner and reusable, like a temporary view.

• It’s used in recursion, simplifying subqueries, and improving readability.

• You can reference it multiple times in the main query.

• Example:

• WITH temp AS (SELECT dept_id, AVG(salary) avg_sal FROM emp GROUP BY dept_id)

• SELECT * FROM temp WHERE avg_sal > 50000;

10. What is the difference between WHERE and HAVING?

• WHERE filters rows before grouping (works on raw data).

• HAVING filters after grouping (works on aggregated results).

• You can’t use aggregate functions in WHERE.

• Commonly, both are used in the same query.

• Example:

• SELECT dept_id, AVG(salary)

• FROM emp

• GROUP BY dept_id

• HAVING AVG(salary) > 50000;

11. What is the difference between UNION and UNION ALL?

• UNION removes duplicates, UNION ALL keeps them.

• UNION performs sorting for uniqueness, making it slower.

• Both require same number and type of columns.

• Use UNION ALL when you need performance and don’t mind duplicates.

• Example: SELECT name FROM emp1 UNION ALL SELECT name FROM emp2;
12. What is the difference between DELETE, TRUNCATE, and DROP?

• DELETE removes specific rows and can have WHERE condition.

• TRUNCATE removes all rows quickly, no WHERE allowed.

• DROP deletes entire table structure from the database.

• DELETE is DML, TRUNCATE & DROP are DDL.

• TRUNCATE resets identity values, DELETE does not by default.

13. What is normalization?

• Normalization organizes data to reduce redundancy and improve data integrity.

• It divides large tables into smaller related ones.

• Common forms: 1NF, 2NF, 3NF, BCNF.

• It helps in efficient storage and avoids anomalies in insertion/update/delete.

• Example: Splitting Employee table into Employee and Department to avoid duplication.

14. What is denormalization?

• Denormalization combines tables to improve performance.

• It’s opposite of normalization.

• Used in analytical databases where read speed is more important than storage efficiency.

• It can introduce redundancy but reduces JOIN operations.

• Example: Storing department name directly in employee table.

15. What is a primary key?

• A primary key uniquely identifies each record in a table.

• It cannot contain NULL or duplicate values.

• Each table can have only one primary key.

• It ensures data integrity and is used for relationships.

• Example: emp_id in Employee table.

16. What is a foreign key?

• A foreign key links one table to another.

• It ensures referential integrity by enforcing valid references.

• It prevents deleting a department that still has employees.

• It maintains consistency across tables.

17. What is an index in SQL?

• An index improves query speed by allowing faster data lookup.

• Works like an index in a book — helps locate rows without scanning entire table.

• Types include clustered and non-clustered indexes.

• Overuse can slow down insert/update/delete operations.

• Example: CREATE INDEX idx_name ON emp(name);

18. What is the difference between clustered and non-clustered index?

• Clustered index defines the physical order of data in a table.

• A table can have only one clustered index.

• Non-clustered index creates a separate structure pointing to table rows.

• You can have multiple non-clustered indexes.

• Example: PRIMARY KEY creates a clustered index by default.

19. What is a view?

• A view is a virtual table based on SQL query results.

• It doesn’t store data physically.

• Used to simplify complex queries and restrict data access.

• Can be updatable in certain conditions.

• Example: CREATE VIEW emp_view AS SELECT name, salary FROM emp;

20. What is the difference between a view and a table?

• A table stores actual data; a view stores only SQL logic.

• Views fetch data dynamically from base tables.

• Views help control data visibility and simplify reports.

• No physical storage is used by a view except for materialized views.

• Tables support indexes and constraints; views do not (except indexed views).