10 Shivprasad Koirala SQL Interview Questions and Answers

Shivprasad Koirala’s SQL resources have become a cornerstone for many aspiring database professionals. His comprehensive approach to SQL, covering everything from basic queries to complex database management, has made his work a go-to reference for those looking to deepen their understanding of SQL. The clarity and depth of his explanations make even the most intricate concepts accessible to learners at all levels.

This article compiles a selection of interview questions inspired by Koirala’s teachings, designed to help you demonstrate your SQL expertise effectively. By working through these questions and answers, you will be better prepared to tackle the challenges posed in technical interviews and showcase your proficiency in SQL.

Shivprasad Koirala SQL Interview Questions and Answers

1. Write a SQL query to retrieve all records from a table named ‘Employees’ where the ‘Department’ is ‘Sales’.

To retrieve all records from a table named ‘Employees’ where the ‘Department’ is ‘Sales’, use the following SQL query:

SELECT * FROM Employees WHERE Department = 'Sales';

This query fetches all columns from the ‘Employees’ table, filtering records to include only those with ‘Department’ set to ‘Sales’.

2. Write a SQL query to join two tables, ‘Orders’ and ‘Customers’, on the ‘CustomerID’ field.

To join two tables, ‘Orders’ and ‘Customers’, on the ‘CustomerID’ field, use an SQL JOIN operation. This combines rows from both tables based on a related column.

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

This query selects ‘OrderID’ from ‘Orders’, ‘CustomerName’ from ‘Customers’, and ‘OrderDate’ from ‘Orders’, using the JOIN keyword to combine rows based on matching ‘CustomerID’ fields.

3. Write a SQL query using a subquery to find employees who earn more than the average salary in their department.

A subquery in SQL is a query nested inside another query, useful for operations requiring multiple steps. To find employees earning more than the average salary in their department, use a subquery to calculate the average salary and compare each employee’s salary to this average.

Example:

SELECT e1.EmployeeID, e1.EmployeeName, e1.Salary, e1.DepartmentID
FROM Employees e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DepartmentID = e1.DepartmentID
);

The subquery calculates the average salary for each department, and the outer query selects employees whose salary exceeds this average.

4. Write a SQL view that combines data from ‘Orders’ and ‘OrderDetails’ tables to show order summaries.

A SQL view is a virtual table that simplifies complex queries by combining data from multiple tables. It doesn’t store data but allows querying as if it were a single table. To create a view combining ‘Orders’ and ‘OrderDetails’ for order summaries:

CREATE VIEW OrderSummaries AS
SELECT 
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    SUM(od.Quantity * od.UnitPrice) AS TotalAmount
FROM 
    Orders o
JOIN 
    OrderDetails od ON o.OrderID = od.OrderID
GROUP BY 
    o.OrderID, o.CustomerID, o.OrderDate;

This view joins ‘Orders’ and ‘OrderDetails’ on OrderID, using SUM to calculate the total amount for each order, grouped by OrderID, CustomerID, and OrderDate.

5. Write a SQL query using window functions to calculate the running total of sales in the ‘Sales’ table.

Window functions in SQL perform calculations across a set of table rows related to the current row. To calculate the running total of sales in the ‘Sales’ table, use the SUM function with the OVER clause.

Example:

SELECT 
    SalesDate,
    SalesAmount,
    SUM(SalesAmount) OVER (ORDER BY SalesDate) AS RunningTotal
FROM 
    Sales;

This query calculates the running total of sales amounts ordered by the sales date, with the OVER clause defining the window of rows for the calculation.

6. Discuss techniques you would use to optimize a slow-running SQL query.

To optimize a slow-running SQL query, consider these techniques:

• Indexing: Create indexes on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses to speed up query execution.
• Query Rewriting: Rewrite queries for better performance, such as using EXISTS instead of IN, avoiding SELECT *, and simplifying complex queries.
• Analyzing Execution Plans: Use tools like EXPLAIN to understand the query execution plan and identify bottlenecks.
• Partitioning: Partition large tables to improve performance by scanning only relevant partitions.
• Optimizing Joins: Ensure joins are on indexed columns and use appropriate join types.
• Caching: Implement caching for frequently accessed data to reduce database load.
• Database Configuration: Tune database configuration parameters like buffer pool size and cache size.

7. Discuss advanced query optimization techniques such as indexing strategies, query rewriting, and execution plan analysis.

Advanced query optimization techniques include indexing strategies, query rewriting, and execution plan analysis.

Indexing strategies involve creating indexes on columns frequently used in query conditions. Proper indexing reduces data scanning, leading to faster execution. However, balance the number of indexes to avoid increased maintenance and slower write operations.

Query rewriting transforms a query into a more efficient form without changing its result, using techniques like subquery unnesting and predicate pushdown.

Execution plan analysis examines the execution plan to understand query execution, identifying potential bottlenecks for improvement.

8. Explain the concept of Common Table Expressions (CTEs) and provide an example.

Common Table Expressions (CTEs) create temporary result sets referenced within a query, enhancing readability and maintainability by breaking down complex queries.

Example:

WITH Sales_CTE AS (
    SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY SalesPersonID
)
SELECT SalesPersonID, TotalSales
FROM Sales_CTE
WHERE TotalSales > 10000;

This CTE calculates total sales for each salesperson, and the main query selects those with total sales over 10,000.

9. What are some SQL security best practices you follow to protect sensitive data?

To protect sensitive data in SQL databases, follow these best practices:

• Access Control: Implement the principle of least privilege, granting users minimal access necessary for their tasks.
• Encryption: Encrypt sensitive data at rest and in transit using strong algorithms.
• Authentication: Use strong authentication mechanisms like multi-factor authentication.
• Auditing and Monitoring: Enable auditing and logging to track access and changes, reviewing logs regularly.
• Input Validation: Validate and sanitize user inputs to prevent SQL injection attacks.
• Regular Updates: Keep database software updated with the latest security patches.
• Backup and Recovery: Regularly back up the database and ensure secure storage of backups.

10. How do you handle NULL values in SQL? Provide examples.

In SQL, NULL represents the absence of a value. Handling NULL values is important as they can affect query results. Here are methods to handle NULL values:

• Using IS NULL and IS NOT NULL: Filter records with NULL or non-NULL values.
• Using COALESCE: Return the first non-NULL value in a list of expressions.
• Using IFNULL (MySQL) or ISNULL (SQL Server): Replace NULL with a specified value.
• Using CASE statements: Handle NULL values conditionally.

Examples:

-- Using IS NULL and IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

-- Using COALESCE
SELECT employee_id, COALESCE(manager_id, 'No Manager') AS manager_id FROM employees;

-- Using IFNULL (MySQL) or ISNULL (SQL Server)
SELECT employee_id, IFNULL(manager_id, 'No Manager') AS manager_id FROM employees; -- MySQL
SELECT employee_id, ISNULL(manager_id, 'No Manager') AS manager_id FROM employees; -- SQL Server

-- Using CASE statements
SELECT employee_id,
       CASE
           WHEN manager_id IS NULL THEN 'No Manager'
           ELSE manager_id
       END AS manager_id
FROM employees;