Top SQL Queries for Practice (With Short Answers) - Aggregate Queries (16–30)

 

Aggregate Queries (16–30)

16. Count total employees

SELECT COUNT(*) FROM Employee;

17. Count employees in HR

SELECT COUNT(*) FROM Employee WHERE department = 'HR';

18. Find total salary payout

SELECT SUM(salary) FROM Employee;

19. Find average salary

SELECT AVG(salary) FROM Employee;

20. Find highest salary

SELECT MAX(salary) FROM Employee;

21. Find lowest salary

SELECT MIN(salary) FROM Employee;

22. Count employees by department

SELECT department, COUNT(*) FROM Employee GROUP BY department;

23. Find avg salary by department

SELECT department, AVG(salary) FROM Employee GROUP BY department;

24. Find highest salary by department

SELECT department, MAX(salary) FROM Employee GROUP BY department;

25. Find departments with more than 5 employees

SELECT department, COUNT(*) 
FROM Employee
GROUP BY department
HAVING COUNT(*) > 5;

26. Find departments with avg salary > 60000

SELECT department, AVG(salary)
FROM Employee
GROUP BY department
HAVING AVG(salary) > 60000;

27. Count distinct departments

SELECT COUNT(DISTINCT department) FROM Employee;

28. Find total salary by department

SELECT department, SUM(salary) FROM Employee GROUP BY department;

29. Find total employees in each location

SELECT location, COUNT(*) FROM Employee GROUP BY location;

30. Find max salary where department = IT

SELECT MAX(salary) FROM Employee WHERE department = 'IT';

---

SQL Scenario-Based Interview Questions ( 85 -100)

 

86. How do you find least-selling product?

Group by product and sort by total sales asc.

---

87. How do you calculate average order value?

Total revenue ÷ total orders.

---

88. How do you find repeat customers?

Find customers with more than one order.

---

89. How do you identify one-time customers?

Find customers with exactly one order.

---

90. How do you segment customers by spending?

Use CASE on total spend ranges.

---

91. How do you find highest sales month?

Group by month and sort by total sales desc.

---

92. How do you compare YoY sales?

Compare same period current year vs previous year.

---

93. How do you detect sales drop?

Compare current period sales with previous period using LAG().

---

94. How do you find seasonal trends?

Aggregate sales by month/quarter across years.

---

95. How do you calculate contribution % by category?

Category sales ÷ total sales * 100.

---

96. How do you rank products by sales?

Use RANK() on sales descending.

---

97. How do you find top customer per region?

Use ROW_NUMBER() partitioned by region ordered by sales desc.

---

98. How do you identify null-heavy columns?

Profile columns using NULL counts.

---

99. How do you perform data quality check in SQL?

Validate nulls, duplicates, formats, and referential integrity.

---

100. How do you explain SQL approach in interview?

Explain logic first, then SQL method, then optimization approach.

SQL Scenario-Based Interview Questions ( 51 - 70)

 

51. How do you find common records between two tables?

Use INNER JOIN or INTERSECT.

---

52. How do you find records in table A not in B?

Use LEFT JOIN ... IS NULL or NOT EXISTS.

---

53. How do you find records in B not in A?

Use reverse LEFT JOIN or NOT EXISTS.

---

54. How do you merge data from two tables?

Use UNION, JOIN, or MERGE based on need.

---

55. How do you combine results from two queries?

Use UNION or UNION ALL.

---

56. Difference between UNION and UNION ALL?

UNION removes duplicates; UNION ALL keeps all rows.

---

57. How do you find intersection of two tables?

Use INTERSECT.

---

58. How do you update one table from another?

Use UPDATE with JOIN.

---

59. How do you insert missing records from one table to another?

Use INSERT INTO ... SELECT with NOT EXISTS.

---

60. How do you synchronize two tables?

Use MERGE.

---

61. How do you detect slowly changing data?

Compare current and incoming records using business key.

---

62. How do you implement SCD Type 1?

Overwrite old value with new value.

---

63. How do you implement SCD Type 2?

Expire old record and insert new record with version/date.

---

64. How do you implement SCD Type 3?

Store current and previous value in same row.

---

65. How do you load only changed records?

Use incremental load with timestamp or CDC logic.

---

66. How do you validate row counts after ETL?

Compare source and target row counts.

---

67. How do you validate duplicate data after load?

Run duplicate checks on business key.

---

68. How do you identify rejected records?

Filter rows failing validation rules.

---

69. How do you audit data load?

Store load date, row count, status, and error logs.

---

70. How do you troubleshoot missing records in target?

Compare source vs target using keys and load filters.

SQL Scenario-Based Interview Questions (36 - 50)

 

36. How do you identify inactive customers?

Find customers with no orders in last N months.

---

37. How do you find repeated transactions?

Group by transaction attributes and filter count > 1.

---

38. How do you identify fraud transactions in SQL?

Flag duplicate, unusual, high-frequency, or abnormal-value transactions.

---

39. How do you find consecutive duplicate values?

Use LAG() to compare current row with previous row.

---

40. How do you split full name into first and last name?

Use string functions like SUBSTRING, CHARINDEX, or SPLIT_PART.

---

41. How do you combine first and last name?

Use concatenation (first_name || last_name or CONCAT()).

---

42. How do you remove leading and trailing spaces?

Use TRIM().

---

43. How do you convert text to uppercase?

Use UPPER().

---

44. How do you convert text to lowercase?

Use LOWER().

---

45. How do you extract year from date?

Use YEAR(date_column).

---

46. How do you extract month from date?

Use MONTH(date_column).

---

47. How do you extract day from date?

Use DAY(date_column).

---

48. How do you calculate age from DOB?

Subtract birth year from current year with date adjustment.

---

49. How do you find weekend dates?

Filter dates where weekday is Saturday or Sunday.

---

50. How do you find business days only?

Exclude weekends and holidays.

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516 

SQL Scenario-Based Interview Questions (21 - 35)

 

21. How do you find top 3 salaries in each department?

Use DENSE_RANK() partitioned by department.

---

22. How do you find highest salary in each department?

Use MAX(salary) with GROUP BY department or RANK().

---

23. How do you find lowest salary in each department?

Use MIN(salary) with GROUP BY department.

---

24. How do you find employees above department average salary?

Use correlated subquery comparing employee salary with department AVG.

---

25. How do you find employees below company average salary?

Compare salary with subquery using AVG(salary).

---

26. How do you swap values of two columns?

Use UPDATE table SET col1 = col2, col2 = col1 with temp logic.

---

27. How do you transpose rows into columns?

Use PIVOT.

---

28. How do you convert columns into rows?

Use UNPIVOT.

---

29. How do you find missing values in sequence?

Compare current row with next expected value using LAG() / LEAD().

---

30. How do you generate row numbers in SQL?

Use ROW_NUMBER().

---

31. How do you remove NULL values from result?

Use WHERE column IS NOT NULL.

---

32. How do you replace NULL with default value?

Use COALESCE() or ISNULL().

---

33. How do you find records updated today?

Filter on update date = current date.

---

34. How do you fetch data for current month only?

Filter using month and year from current date.

---

35. How do you fetch records between two dates?

Use BETWEEN start_date AND end_date.

SQL Scenario-Based Interview Questions (1-20)

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516 

SQL Scenario-Based Interview Questions (1-20)

---

1. How do you find duplicate records in a table?

Use GROUP BY with HAVING COUNT(*) > 1 on the duplicate column(s).

---

2. How do you delete duplicate records but keep one?

Use ROW_NUMBER() to mark duplicates and delete rows where row number > 1.

---

3. How do you find the 2nd highest salary?

Use subquery with MAX(salary) less than highest salary, or use DENSE_RANK().

---

4. How do you find the 3rd highest salary?

Use DENSE_RANK() and filter where rank = 3.

---

5. How do you find employees earning more than their manager?

Self join employee table and compare employee salary with manager salary.

---

6. How do you find employees who do not have a manager?

Filter rows where manager_id IS NULL.

---

7. How do you find departments with no employees?

Use LEFT JOIN from department to employee and filter employee as NULL.

---

8. How do you find employees who joined in the last 30 days?

Filter using join_date >= CURRENT_DATE - 30.

---

9. How do you find employees with same salary?

Group by salary and filter HAVING COUNT(*) > 1.

---

10. How do you fetch only even-numbered rows?

Use row numbering logic and filter rows divisible by 2.

---

11. How do you fetch only odd-numbered rows?

Use row numbering logic and filter rows not divisible by 2.

---

12. How do you get the latest record for each customer?

Use ROW_NUMBER() partitioned by customer ordered by date desc.

---

13. How do you get the first order of each customer?

Use ROW_NUMBER() partitioned by customer ordered by order date asc.

---

14. How do you find customers who never placed an order?

Use LEFT JOIN customer to orders and filter NULL order records.

---

15. How do you find customers with more than 5 orders?

Group by customer and use HAVING COUNT(*) > 5.

---

16. How do you calculate running total?

Use SUM(column) OVER (ORDER BY date).

---

17. How do you calculate cumulative sales by month?

Use window function with SUM(sales) OVER (ORDER BY month).

---

18. How do you compare current row with previous row?

Use LAG().

---

19. How do you compare current row with next row?

Use LEAD().

---

20. How do you find month-over-month sales growth?

Use LAG(sales) and subtract previous month sales.

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516 

Indexes, Views & Performance (89–100)

 

Indexes, Views & Performance (89–100)

89. What is an index?
    An index improves query performance.
90. Disadvantage of index?
    Slows down inserts/updates and uses storage.
91. What is a clustered index?
    Sorts and stores table data physically.
92. What is non-clustered index?
    Stores index separately from actual data.
93. What is a view?
    A virtual table based on query.
94. Why use a view?
    For abstraction, security, and reusable logic.
95. What is a stored procedure?
    A precompiled SQL block stored in database.
96. What is a trigger?
    A trigger executes automatically on INSERT/UPDATE/DELETE.
97. How to improve SQL query performance?
    Use indexes, avoid SELECT *, optimize joins, filter early.
98. What is normalization?
    Organizing data to reduce redundancy.
99. What is denormalization?
    Adding redundancy to improve read performance.
100. Scenario: Query is slow, what will you check first?
     Check execution plan, indexes, joins, filters, and data volume.

Subqueries & CTEs (66–78)

 

66. What is a subquery?
    A query inside another query.
67. Types of subqueries?
    Single-row, multi-row, correlated.
68. What is correlated subquery?
    A subquery that depends on outer query.
69. What is CTE?
    Common Table Expression is a temporary result set.
70. Why use CTE?
    Improves readability and simplifies complex queries.
71. Difference between CTE and subquery?
    CTE is reusable in same query; subquery is not.
72. Can CTE be recursive?
    Yes.
73. Find 2nd highest salary using subquery.
    SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);
74. Find employees earning above average salary.
    Use subquery with AVG(salary).
75. What is EXISTS?
    Checks if subquery returns rows.
76. What is NOT EXISTS?
    Checks if subquery returns no rows.
77. Difference between IN and EXISTS?
    IN compares values; EXISTS checks row existence.
78. Which performs better: IN or EXISTS?
    EXISTS is better for large datasets.

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

Aggregate Functions & Grouping (51–65)

 

Aggregate Functions & Grouping (51–65)

51. What is an aggregate function?
    It performs calculation on multiple rows and returns one result.
52. Common aggregate functions?
    COUNT, SUM, AVG, MIN, MAX.
53. What does COUNT(*) do?
    Counts all rows including NULLs.
54. Difference between COUNT(*) and COUNT(column)?
    COUNT(column) ignores NULLs.
55. What is GROUP BY?
    Groups rows with same values for aggregation.
56. What is HAVING?
    Filters grouped data.
57. Can we use WHERE with aggregate?
    No, use HAVING.
58. Find total salary by department.
    SELECT dept, SUM(salary) FROM emp GROUP BY dept;
59. Find departments with more than 5 employees.
    Use GROUP BY dept HAVING COUNT(*) > 5.
60. What is AVG?
    Returns average value.
61. What is MAX?
    Returns highest value.
62. What is MIN?
    Returns lowest value.
63. Can GROUP BY be used with multiple columns?
    Yes.
64. What is the order of execution?
    FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
65. Can we use alias in GROUP BY?
    Usually no (depends on DB).

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

Joins (36–50)

36. What is a JOIN?
    A JOIN combines rows from multiple tables.
37. What is INNER JOIN?
    Returns matching rows from both tables.
38. What is LEFT JOIN?
    Returns all rows from left table and matching rows from right.
39. What is RIGHT JOIN?
    Returns all rows from right table and matching rows from left.
40. What is FULL OUTER JOIN?
    Returns all matching and non-matching rows from both tables.
41. What is CROSS JOIN?
    Returns Cartesian product of both tables.
42. What is SELF JOIN?
    A table joined with itself.
43. Difference between INNER and LEFT JOIN?
    INNER returns matches only; LEFT returns all left rows.
44. What happens if no match in LEFT JOIN?
    NULL values are returned for right table columns.
45. What is Cartesian product?
    Every row from first table joins with every row from second.
46. Can we join more than 2 tables?
    Yes, multiple joins are allowed.
47. Which join is used to find unmatched records?
    LEFT JOIN with WHERE right_table.id IS NULL.
48. Scenario: Find customers with no orders.
    Use LEFT JOIN between customers and orders, filter NULL orders.
49. Scenario: Get employee with manager name.
    Use SELF JOIN on employee table.
50. Can we join tables without primary key?
    Yes, using common columns. 

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

Filtering & Sorting (21–35)

 

21. What does WHERE clause do?
    It filters rows based on conditions.
22. Difference between WHERE and HAVING?
    WHERE filters rows before grouping; HAVING filters groups after aggregation.
23. What is ORDER BY?
    It sorts query results in ascending or descending order.
24. Default sort order in SQL?
    Ascending (ASC) by default.
25. What is DISTINCT?
    It removes duplicate values from result.
26. How do you fetch top 5 records?
    SELECT TOP 5 * FROM table; (SQL Server)
27. How do you fetch first 5 rows in MySQL?
    SELECT * FROM table LIMIT 5;
28. What is BETWEEN?
    It filters values within a range.
29. What is IN operator?
    It matches values from a given list.
30. What is LIKE used for?
    It is used for pattern matching.
31. What does % mean in LIKE?
    It matches zero or more characters.
32. What does _ mean in LIKE?
    It matches exactly one character.
33. How do you handle NULL values?
    Use IS NULL or IS NOT NULL.
34. Can we use = NULL?
    No, use IS NULL.
35. What is alias in SQL?
    A temporary name given to a column or table.

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

SQL Basics (1–20)

1. What is SQL?
   SQL (Structured Query Language) is used to store, retrieve, manage, and manipulate data in databases.
2. What are the main types of SQL commands?
   DDL, DML, DQL, DCL, and TCL.
3. What is DDL?
   Data Definition Language is used to define database objects like tables (CREATE, ALTER, DROP).
4. What is DML?
   Data Manipulation Language is used to modify data (INSERT, UPDATE, DELETE).
5. What is DQL?
   Data Query Language is used to fetch data (SELECT).
6. What is DCL?
   Data Control Language is used for permissions (GRANT, REVOKE).
7. What is TCL?
   Transaction Control Language is used to manage transactions (COMMIT, ROLLBACK).
8. What is a database?
   A database is an organized collection of structured data.
9. What is a table?
   A table is a collection of rows and columns used to store data.
10. What is a row in SQL?
    A row represents a single record in a table.
11. What is a column in SQL?
    A column represents a specific attribute of data in a table.
12. What is a schema?
    A schema is a logical container for database objects like tables and views.
13. What is a primary key?
    A primary key uniquely identifies each record in a table.
14. What is a foreign key?
    A foreign key links one table to another using a referenced key.
15. What is a unique key?
    It ensures all values in a column are unique.
16. What is the difference between primary key and unique key?
    Primary key does not allow NULL; unique key allows one NULL (DB dependent).
17. What is NOT NULL?
    It ensures a column cannot store NULL values.
18. What is DEFAULT constraint?
    It assigns a default value when no value is provided.
19. What is CHECK constraint?
    It restricts values allowed in a column.
20. What is the difference between DELETE, TRUNCATE, and DROP?
    DELETE removes rows, TRUNCATE removes all rows, DROP removes the table.

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516

Key Concepts - Primary Keys and Foreign Keys

 Primary Keys and Foreign Keys 

• Explain the role of primary keys and foreign keys in maintaining data integrity. 

Primary keys and foreign keys are essential database constraints that play a crucial role in maintaining data integrity within a relational database system. They ensure that data is accurate, consistent, and reliable. Here's an explanation of their roles in data integrity: 

Primary Keys: 

• Definition: A primary key is a unique identifier for each record (row) in a database table. It ensures that each record in the table can be uniquely identified and distinguished from others. 

• Uniqueness: A primary key constraint enforces the uniqueness of values in the designated column(s). No two records in the table can have the same primary key value. 

• Data Integrity: Primary keys enforce data integrity by preventing duplicate or null values in the identifier column(s). This ensures that each record is uniquely identifiable. 

• Indexing: Primary keys are often indexed, which allows for efficient data retrieval. Queries that involve searching for specific records or joining tables benefit from this indexing. 

• Relationships: Primary keys serve as the basis for establishing relationships with other tables. In related tables, the primary key of one table can be used as a foreign key in another, creating referential integrity. 

Foreign Keys: 

• Definition: A foreign key is a column or set of columns in a table that is used to establish a link between the data in two tables. It creates a relationship between the tables based on a common attribute. 

• Referential Integrity: Foreign keys enforce referential integrity, ensuring that data in the related tables remains consistent. They define a relationship between a child table (containing the foreign key) and a parent table (containing the primary key). 

• Data Consistency: When a foreign key exists in a child table, it must refer to a valid primary key value in the parent table. This ensures that the data in the child table is consistent and accurate. 

• Cascading Actions: Foreign keys can be configured with cascading actions, such as CASCADE DELETE or CASCADE UPDATE, which automatically propagate changes from the parent table to the child table, maintaining data consistency. 

• Enforcement of Relationships: Foreign keys enforce relationships between tables, preventing or controlling actions that would violate these relationships, such as attempting to insert a record with a foreign key value that does not exist in the parent table. 

• Navigation: Foreign keys provide a way to navigate and retrieve related data across tables. They enable the creation of joins and help retrieve data from multiple related tables. 

Roles in Maintaining Data Integrity: 

• Primary keys ensure that each record in a table is uniquely identified, preventing duplicate or null values. They are the foundation for establishing relationships with other tables. 

• Foreign keys enforce referential integrity, ensuring that data in related tables remains consistent. They control the relationships between tables and prevent actions that could compromise data integrity. 

Together, primary keys and foreign keys create a structure that promotes data accuracy and consistency within a relational database. They are essential for maintaining the integrity of the data and ensuring that it remains reliable and meaningful.