✅ BASIC SQL INTERVIEW QUESTIONS WITH ANSWERS & REAL-WORLD EXAMPLES

1. What is the difference between SQL and MySQL?

2. SQL (Structured Query Language) is a standard language used to communicate with relational
databases.
3. MySQL is a popular open-source relational database management system (RDBMS) that uses SQL to
manage and query data.

4. Example: SQL is the language, while MySQL is the software that understands SQL. Like English
(language) vs Google Translate (tool).

5. What are tables and fields?

6. A table is a collection of data organized in rows and columns.

7. Fields are the columns in a table that define the type of data stored.

8. Example: In a table called 'Employees', fields could be 'ID', 'Name', 'Department', and 'Salary'.

9. What is a Primary Key and Foreign Key?

10. Primary Key uniquely identifies each row in a table and cannot be NULL.
11. Foreign Key is a field in one table that refers to the Primary Key of another table.

12. Example: 'employee_id' is a primary key in 'Employees'. In 'Attendance', 'employee_id' is a foreign key.

13. What is the difference between CHAR and VARCHAR?

14. CHAR: Fixed length string (e.g., CHAR(10) will store 10 characters, padding if needed).
15. VARCHAR: Variable length string (e.g., VARCHAR(10) stores only the actual length).

16. Example: Use CHAR for fixed formats like PIN codes; VARCHAR for names or emails.

17. What is a constraint? Name a few.

18. Constraints are rules enforced on data in tables to ensure data integrity.
19. Examples: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

20. Example: Use CHECK constraint to ensure age > 18 in a 'Users' table.

21. What does SELECT DISTINCT do?

22. It returns only unique (non-duplicate) values from a column.

23. Example: SELECT DISTINCT country FROM customers; -- returns unique country names.

24. What is the difference between NULL and 0?

1
 25. NULL means no value or unknown. 0 is a numeric value.

26. Example: In a 'Temperature' table, NULL means not recorded; 0 means recorded as zero.

27. What is the use of the LIKE operator?

28. LIKE is used for pattern matching in WHERE clause.

29. Example: SELECT name FROM users WHERE name LIKE 'A%'; -- names starting with A.

30. What is the purpose of the BETWEEN operator?

31. Used to filter data within a range.

32. Example: SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

33. What is the use of the IN operator?

34. Used to match any value from a list.

35. Example: SELECT * FROM employees WHERE department IN ('HR', 'Finance', 'IT');

✅ INTERMEDIATE SQL INTERVIEW QUESTIONS

1. Explain different types of JOINs with examples.

2. INNER JOIN: Returns matching rows from both tables.
3. LEFT JOIN: All rows from left + matched from right.
4. RIGHT JOIN: All from right + matched from left.
5. FULL JOIN: All rows from both tables.

6. Example: Join 'employees' and 'departments' tables on department_id.

7. What is UNION vs UNION ALL?

8. UNION removes duplicates.

9. UNION ALL includes duplicates.

10. Example: Combine customers from two regions.

11. How does GROUP BY work?

12. GROUP BY groups rows by column(s) and allows aggregate functions like COUNT(), SUM().

13. Example: SELECT department, COUNT(*) FROM employees GROUP BY department;

14. Difference between WHERE and HAVING?

15. WHERE filters rows before GROUP BY.

16. HAVING filters groups after GROUP BY.

2
 17. Example: SELECT dept, COUNT() FROM employees GROUP BY dept HAVING COUNT() > 5;

18. INNER JOIN vs LEFT JOIN?

19. INNER JOIN: Only matching rows.

20. LEFT JOIN: All rows from left, with NULLs if no match in right.

21. Example: Get all employees and their department names.

22. Fetch the nth highest salary. SELECT MAX(salary) FROM employees WHERE salary < (SELECT
MAX(salary) FROM employees);

23. What are aggregate functions? Examples?

24. COUNT(), SUM(), AVG(), MIN(), MAX().

25. Example: SELECT AVG(salary) FROM employees;

26. What is a subquery and correlated subquery?

27. Subquery: Query inside another query.

28. Correlated Subquery: Inner query depends on outer query.

29. Example: Get employees who earn more than the average salary.

30. DELETE vs TRUNCATE vs DROP?

31. DELETE: Removes rows with WHERE; can rollback.

32. TRUNCATE: Removes all rows; can't rollback.

33. DROP: Deletes entire table structure.

34. How to find number of rows in a table? SELECT COUNT(*) FROM table_name;

✅ ADVANCED SQL INTERVIEW QUESTIONS

1. What are views?

2. A view is a virtual table based on SQL query.

3. Example: CREATE VIEW high_salary AS SELECT * FROM employees WHERE salary > 80000;

4. What are indexes?

5. Indexes improve query speed by providing quick data lookup.

6. Example: CREATE INDEX idx_name ON employees(name);

7. What are stored procedures?

3
 8. Predefined SQL code that you can reuse.

9. Example: CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT) BEGIN SELECT * FROM

employees WHERE department_id = dept_id; END;

10. Explain ROW_NUMBER(), RANK(), DENSE_RANK().

11. ROW_NUMBER: Unique row number.

12. RANK: Skips rank if tie.
13. DENSE_RANK: No gaps in ranking.

14. Example: Rank employees by salary.

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

16. A temporary result set defined with WITH keyword.

17. Example: WITH top_emps AS (SELECT * FROM employees WHERE salary > 80000) SELECT * FROM
top_emps;

18. What is a window function?

19. Performs calculations across a set of rows related to current row.

20. Example: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

21. How to remove duplicates? Using ROW_NUMBER: WITH cte AS (SELECT *, ROW_NUMBER() OVER
(PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id
FROM cte WHERE rn > 1);

22. What is normalization?

23. Process of organizing data to reduce redundancy.

24. 1NF: Atomic columns
25. 2NF: No partial dependency

26. 3NF: No transitive dependency

27. What are ACID properties?

28. Atomicity: All or nothing

29. Consistency: Valid data only
30. Isolation: No interference between transactions

31. Durability: Once committed, changes are saved

32. Explain transactions in SQL.

4
 33. Group of operations treated as single unit.
34. Use BEGIN, COMMIT, ROLLBACK
35. Example: Money transfer: debit + credit.

✅ SCENARIO-BASED SQL QUERIES

1. Find employees who earn more than average salary: SELECT * FROM employees WHERE salary >
(SELECT AVG(salary) FROM employees);

2. Count total employees in each department: SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id;

3. Find top 3 highest-paid employees: SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

4. Update salary by 10% for IT department: UPDATE employees SET salary = salary * 1.10 WHERE
department = 'IT';

5. Delete duplicate rows: WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY
id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM cte WHERE rn > 1);

6. Find users who haven’t placed any orders: SELECT [Link], [Link] FROM users u LEFT JOIN orders o ON
[Link] = o.user_id WHERE [Link] IS NULL;

7. Get the most frequently ordered product: SELECT product_id, COUNT(*) AS order_count FROM
order_items GROUP BY product_id ORDER BY order_count DESC LIMIT 1;

8. Departments with more than 5 employees: SELECT department_id, COUNT() FROM employees GROUP
BY department_id HAVING COUNT() > 5;

9. Display salary difference between employees in same department: SELECT [Link], [Link],
[Link] - [Link] AS salary_diff FROM employees e1 JOIN employees e2 ON e1.department_id =
e2.department_id AND [Link] != [Link];

10. Swap values of two columns: UPDATE table_name SET column1 = column2, column2 = column1;