Here are more advanced SQL questions that focus on complex concepts and scenarios, often asked in interviews for candidates with 3 years of experience. These questions cover a range of topics, including data manipulation, performance tuning, and advanced SQL functions.

Advanced SQL Interview Questions

1. SQL Performance Tuning

Question: What strategies would you employ to optimize slow-running queries? Provide specific examples of techniques you have used.

Expected Response: Discuss methods such as analyzing execution plans, adding indexes, optimizing joins, using proper data types, avoiding unnecessary calculations in queries, and breaking complex queries into smaller parts.

1. Aggregate Functions with Grouping Sets

Question: Write a SQL query that uses GROUPING SETS to calculate the total sales for each product category and overall sales from a sales table with category and amount columns.

Expected Response:

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((category), ());

1. Handling Large Data Sets with Partitioning

Question: Describe how you would implement partitioning in a large table to improve query performance. Provide a scenario where partitioning is beneficial.

Expected Response: Discuss partitioning strategies (e.g., range, list, hash) and their benefits, such as reducing the amount of data scanned. Example scenario: partitioning a transactions table by year to improve performance for queries filtering by date.

1. Using Common Table Expressions (CTEs)

Question: Write a SQL query using a CTE to find the top 5 products with the highest sales in each category from a products and sales table.

Expected Response:

WITH ranked_sales AS (
SELECT p.product_id, p.category, SUM(s.amount) AS total_sales,
ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(s.amount) DESC) AS sales_rank
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.category
)
SELECT * FROM ranked_sales WHERE sales_rank <= 5;

1. Using Triggers for Data Validation

Question: Explain how you would use triggers to enforce business rules in a database. Provide an example of a trigger you have implemented.

Expected Response: Discuss using triggers to automatically validate data before insertion or updates. Example: a trigger that prevents inserting an employee record with a salary below the minimum wage:

CREATE OR REPLACE TRIGGER validate_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < minimum_wage THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary must be above minimum wage');
END IF;
END;

1. Data Consistency and ACID Properties

Question: Describe ACID properties in the context of database transactions. Why are they important?

Expected Response: Explain each property:

Atomicity: Transactions are all-or-nothing.

Consistency: Transactions bring the database from one valid state to another.

Isolation: Concurrent transactions do not affect each other's execution.

Durability: Once a transaction is committed, it remains so even in the event of a failure.

1. Window Functions for Complex Calculations

Question: Write a query to calculate the moving average of sales over the last 3 months from a monthly_sales table with sales_date and amount columns.

Expected Response:

SELECT sales_date,
amount,
AVG(amount) OVER (ORDER BY sales_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM monthly_sales;

1. Cross Joins and Cartesian Products

Question: Explain what a cross join is and provide a scenario where it might be useful.

Expected Response: A cross join produces a Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table. Useful in scenarios where you need to compare all combinations, such as generating a list of all products and their potential discounts.

1. Recursive Queries for Hierarchical Data

Question: Describe how you would query hierarchical data using recursive CTEs. Provide an example scenario.

Expected Response: Explain that recursive CTEs allow querying hierarchical data, such as employee-manager relationships. Example:

WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id, name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.name
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

1. Advanced Data Types and JSON Manipulation

Question: How can you work with JSON data in SQL? Provide an example of extracting data from a JSON column.

Expected Response: Discuss using SQL functions to manipulate JSON data, such as JSON_VALUE or JSON_TABLE. Example:

SELECT JSON_VALUE(json_column, '$.name') AS employee_name
FROM employees
WHERE JSON_EXISTS(json_column, '$.active') AND JSON_VALUE(json_column, '$.active') = 'true';

These questions cover a range of advanced topics in SQL, requiring candidates to demonstrate their understanding of complex SQL features and their practical application in real-world scenarios. Preparing for these questions will help candidates showcase their SQL expertise effectively.