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.

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. 

 

Key Concepts in Data Modelling - Normalization

  Normalization 

Normalization is a crucial concept in database design that involves structuring a relational database in a way that reduces data redundancy and ensures data integrity. It is particularly important in Online Transaction Processing (OLTP) databases, which are designed for day-to-day transactional operations. Here, we'll introduce the concept of normalization and discuss its significance in OLTP databases: 

1. Definition of Normalization: 

• Normalization is a systematic process of organizing data in a relational database to eliminate data anomalies and redundancies. 

• It divides a database into multiple related tables and establishes relationships between them, ensuring that each piece of data is stored in only one place. 

2. Importance in OLTP Databases: 

A. Data Integrity: 

• Normalization enhances data integrity by minimizing data redundancy. Redundant data can lead to inconsistencies and anomalies, such as update anomalies, insertion anomalies, and deletion anomalies. 

• In OLTP databases, maintaining data accuracy and consistency is critical because they handle real-time transactional activities (e.g., order processing, inventory management, and customer interactions). 

B. Space Efficiency: 

• Normalization reduces storage space requirements by eliminating redundant data. This can lead to more efficient storage utilization, which is beneficial for OLTP databases where data storage costs can be significant. 

C. Query Performance: 

• While normalization may create more tables and relationships, it can improve query performance by reducing the amount of data that needs to be accessed and processed. 

• In OLTP databases, where rapid retrieval and updates of specific transactional data are essential, query efficiency is crucial. 

D. Simplified Updates: 

• Normalization simplifies data updates and maintenance. With non-redundant data, changes need to be made in only one place, reducing the risk of inconsistent or incomplete updates. 

• In OLTP databases, where data is frequently updated, this simplification helps maintain data accuracy. 

E. Adherence to Business Rules: 

• Normalization encourages the proper representation of business rules and constraints in the database schema. This ensures that the database enforces data integrity rules and follows the logic of the business processes. 

F. Scalability: 

• Normalization supports the scalability of OLTP databases. As the volume of transactional data increases, a well-normalized schema is easier to scale and adapt to changing requirements. 

3. Normal Forms: 

• Normalization is typically achieved through a series of steps called normal forms (e.g., First Normal Form, Second Normal Form, Third Normal Form, and Boyce-Codd Normal Form). Each normal form addresses specific data integrity and redundancy issues. 

• OLTP databases are often normalized up to at least Third Normal Form (3NF) to strike a balance between data integrity and performance. 

In summary, normalization is essential in OLTP databases to ensure data integrity, optimize storage space, improve query performance, simplify updates, and maintain adherence to business rules. By organizing data efficiently, normalization helps OLTP databases handle high volumes of transactions and ensures that the data remains accurate and consistent, which is critical for day-to-day operations.