SQL Programming Resources on Telegram: “✅ Advanced SQL Practice Questions with Answers �…”

✅ Advanced SQL Practice Questions with Answers 🧠📝

1️⃣ Get the second highest salary from the employees table.

SELECT MAX(salary)  
FROM employees  
WHERE salary < (SELECT MAX(salary) FROM employees);

2️⃣ List employees who earn more than the average salary.

SELECT name, salary  
FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees);

3️⃣ Show department-wise highest paid employee.

SELECT department, name, salary  
FROM (
  SELECT *,  
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk  
  FROM employees
) AS ranked  
WHERE rnk = 1;

4️⃣ Display total sales made by each employee in 2023.

SELECT emp_id, SUM(amount) AS total_sales  
FROM sales  
WHERE YEAR(sale_date) = 2023  
GROUP BY emp_id;

5️⃣ Retrieve products with price above average in their category.

SELECT p.name, p.category, p.price  
FROM products p  
WHERE price > (
  SELECT AVG(price)  
  FROM products  
  WHERE category = p.category
);

6️⃣ Identify duplicate emails in the users table.

SELECT email, COUNT(*)  
FROM users  
GROUP BY email  
HAVING COUNT(*) > 1;

7️⃣ Rank customers based on total purchase amount.

SELECT customer_id,
SUM(amount) AS total_spent,  
       RANK() OVER (ORDER BY SUM(amount) DESC) AS rank  
FROM orders  
GROUP BY customer_id;

💬 Double Tap ❤️ For More!