SQL interview Questions

SQL Basics

1. What is SQL, and what is its purpose?

   - SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases. Its purpose is to query, insert, update, and delete data in databases.

2. Explain the differences between SQL and NoSQL databases.

   - SQL databases are relational and use structured tables. NoSQL databases are non-relational and can store data in various formats, like key-value, document, column-family, or graph.

SQL Queries:

3. What are the fundamental SQL CRUD operations, and how are they implemented?

   - CRUD stands for Create, Read, Update, Delete. These operations are implemented in SQL as INSERT, SELECT, UPDATE, and DELETE statements.

4. Explain the SQL SELECT statement.

   - The SELECT statement retrieves data from one or more database tables based on specified criteria. It is used for querying data.

5. What is a SQL JOIN, and how does it work?

   - A JOIN combines rows from two or more tables based on a related column between them. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

SQL Functions and Aggregation:

6. What is the difference between the COUNT(*) and COUNT(column) functions in SQL?

   - COUNT(*) counts all rows in a table, while COUNT(column) counts the number of non-null values in a specific column.

7. Explain SQL aggregate functions like SUM, AVG, MAX, and MIN.

   - Aggregate functions perform calculations on a set of values and return a single value as the result.

SQL Constraints:

8. What are SQL constraints, and why are they used?

   - Constraints define rules for the data in database tables to maintain data integrity. Examples include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.

9. Explain the difference between UNIQUE and PRIMARY KEY constraints.

   - UNIQUE ensures that all values in a column are unique, but it allows NULL values. PRIMARY KEY enforces uniqueness and requires that values are not NULL.

SQL Indexing:

10. What is an SQL index, and why is it important?

    - An index is a database structure used to improve the speed of data retrieval operations on a table. It works like an ordered list of values, making queries faster.

SQL Joins and Subqueries:

11. What is a subquery, and how is it different from a JOIN?

    - A subquery is a query nested within another query. It can return a single value, a table, or a list of values to be used by the outer query. JOINs combine rows from multiple tables based on a related column.

12. Explain the concept of self-join.

    - A self-join is a type of JOIN where a table is joined with itself. It's used when a table has a hierarchical structure or when you need to compare rows within the same table.

SQL Performance Optimization:

13. What are some common strategies for optimizing SQL query performance?

    - Strategies include indexing, proper database design, minimizing the use of SELECT *, avoiding correlated subqueries, and using appropriate data types.

Transactions and ACID Properties:

14. What are the ACID properties in the context of database transactions?

    - ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are reliable and maintain data integrity.

Normalization:

15. What is database normalization, and why is it important?

    - Normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity. It involves dividing tables into smaller, related tables.

Problem-solving questions

1. Find the Nth highest salary from an Employee table.

   - This question tests your knowledge of sorting and limiting results.

   ```sql

   SELECT DISTINCT Salary

   FROM Employee e1

   WHERE N = (

       SELECT COUNT(DISTINCT Salary)

       FROM Employee e2

       WHERE e2.Salary >= e1.Salary

   );

   ```

2. Calculate the average salary for each department from an Employee table.

   - This question assesses your understanding of aggregation.

   ```sql

   SELECT Department, AVG(Salary) AS AvgSalary

   FROM Employee

   GROUP BY Department;

   ```

3. Find employees who earn more than the average salary in their department.

   - This question combines aggregation with filtering.

   ```sql

   SELECT e.EmployeeName, e.Salary, e.Department

   FROM Employee e

   WHERE e.Salary > (

       SELECT AVG(Salary)

       FROM Employee e2

       WHERE e2.Department = e.Department

   );

   ```

4. Calculate the running total of sales for each month in a Sales table.

   - This question tests your knowledge of window functions.

   ```sql

   SELECT Month, SUM(Sales) OVER (ORDER BY Month) AS RunningTotal

   FROM Sales;

   ```

5. Identify duplicate rows in a table.

   - This question checks your ability to find and handle duplicates.

   ```sql

   SELECT Column1, Column2, COUNT(*)

   FROM YourTable

   GROUP BY Column1, Column2

   HAVING COUNT(*) > 1;

   ```

6. Find customers who made their first purchase in the last quarter.

   - This question involves date calculations and subqueries.

   ```sql

   SELECT CustomerName

   FROM Customers

   WHERE CustomerID IN (

       SELECT CustomerID

       FROM Orders

       WHERE OrderDate >= DATEADD(QUARTER, -1, GETDATE())

   );

   ```

7. Calculate the median of a numeric column.

   - This problem assesses your understanding of calculating the median.

   ```sql

   SELECT AVG(val) AS Median

   FROM (

       SELECT val

       FROM YourTable

       ORDER BY val

       LIMIT 2 - (SELECT COUNT(*) FROM YourTable) % 2

       OFFSET (SELECT (COUNT(*) - 1) / 2 FROM YourTable)

   );

   ```

8. Retrieve the top N most frequent items in a sales dataset.

   - This question checks your knowledge of counting and sorting.

   ```sql

   SELECT Item, COUNT(*) AS Frequency

   FROM Sales

   GROUP BY Item

   ORDER BY Frequency DESC

   LIMIT N;

   ```

9. Find employees who have the same manager.

   - This problem involves self-joins to find employees with the same manager.

   ```sql

   SELECT e1.EmployeeName, e2.EmployeeName AS ManagerName

   FROM Employee e1

   JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID;

   ```

10. Calculate the percentage of total sales contributed by each product category.

    - This question tests your understanding of calculating percentages.

    ```sql

    SELECT Category, SUM(Sales) / (SELECT SUM(Sales) FROM Sales) * 100 AS Percentage

    FROM Sales

    GROUP BY Category;

    ```