What are some advanced SQL exercises I can practice?

What are some advanced SQL exercises I can practice?

Here are a few advanced SQL exercises you can try: 1. Write a query to find the second highest salary in a table. 2. Create a query that returns the top 3 products by sales for each category. 3. Write a recursive query to display a hierarchy of employees.

Can you explain how to find the second highest salary?

Sure! You can use the following SQL query: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); This will give you the second highest salary.

What about the top 3 products by sales for each category?

You can use a Common Table Expression (CTE) with the ROW_NUMBER() function. Here's an example:

WITH RankedProducts AS (
    SELECT product_id, category_id, sales,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) as rank
    FROM products
)
SELECT * FROM RankedProducts WHERE rank <= 3;

Thanks! Can you give me a recursive query example?

Certainly! Here's a simple recursive query to display an employee hierarchy:

WITH RECURSIVE EmployeeCTE 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
    INNER JOIN EmployeeCTE cte ON e.manager_id = cte.employee_id
)
SELECT * FROM EmployeeCTE;