create database exam ; use exam; -- Employees table CREATE TABLE Employees ( emp_id INT, name VARCHAR(50), dept_id INT, salary INT, join_date DATE );
-- Departments table CREATE TABLE Departments ( dept_id INT, dept_name VARCHAR(50) );
-- Sales table CREATE TABLE Sales ( sale_id INT, emp_id INT, amount INT, sale_date DATE );
-- Insert sample data INSERT INTO Employees VALUES (1, 'Alice', 10, 60000, '2020-01-01'), (2, 'Bob', 20, 50000, '2021-01-15'), (3, 'Charlie', 10, 55000, '2020-03-10'), (4, 'David', 30, 70000, '2019-07-20'), (5, 'Eva', 20, 50000, '2022-06-01');
INSERT INTO Departments VALUES (10, 'HR'), (20, 'Sales'), (30, 'IT');
INSERT INTO Sales VALUES (101, 1, 1000, '2023-01-10'), (102, 1, 1200, '2023-01-15'), (103, 2, 500, '2023-01-12'), (104, 3, 700, '2023-01-11'), (105, 4, 1500,'2023-01-14'); -- SQL Practice Questions - JOINs, Window Functions, and CTEs -- JOINs -- 1. List all employees with their department names. -- 2. Find employees who have not made any sales. -- 3. List departments that have no employees. -- 4. List employees and their total sales amount. -- 5. List each sale with the employee name and department. -- 6. Find the highest-paid employee in each department. -- 7. Get employees who joined before 2021 and are in the Sales department. -- Window Functions -- 8. Rank employees based on salary within each department. -- 9. Find the running total of sales per employee ordered by date. -- 10. Calculate average salary per department and show it alongside each employee. -- 11. Find employees whose salary is above the average salary of their department. -- 12. Find 2nd highest salary in each department. -- 13. Show previous and next salary for each employee (use LAG, LEAD). -- 14. List employees with their % of total department salary. -- CTEs -- 15. Use a CTE to get top 2 highest salary employees per department. -- 16. With CTE, find total sales by employee and filter those with more than 1500. -- 17. Create a CTE to find departments with more than 1 employee. -- 18. With CTE, calculate average sales per employee and show who is above average. -- 19. Use a recursive CTE to generate numbers from 1 to 10. -- 20. Use CTE to find employees who joined earliest in their department.
-- #ans1 select e.name as employees_name ,d.dept_name as department_name from employees e join departments d on e.dept_id=d.dept_id -- #ans2 select e.name as employees_name,s.sale_id as sale_id from employees e left join sales s on e.emp_id=s.emp_id where s.sale_id is null; -- #ans3 SELECT d.dept_name FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL; -- #ans4 select e.name ,sum(s.amount) as total_sales from employees e left join sales s on e.emp_id=s.emp_id group by e.name; -- #ans5 select e.name as employees_name, s.sale_id ,d.dept_name ,s.amount from sales s join employees e on s.emp_id=e.emp_id join departments d on e.dept_id=d.dept_id; -- #ans6 SELECT * FROM ( SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM employees e ) ranked WHERE rnk = 1; -- #ans7 select e.name as employess_name from employees e join departments d on e.dept_id=d.dept_id where d.dept_name = 'sales' and e.join_date > '2021-01-01';
-- #an8 select name ,dept_id,salary, rank()over(partition by dept_id order by salary desc )as salary_rank from employees;
-- #ans9 select emp_id,amount,sale_date, sum(amount)over(partition by emp_id order by sale_date)as running_sale from sales;
-- #ans10 select name,dept_id,salary, avg(salary)over(partition by dept_id order by salary desc )as average_salary from employees;
-- #ans11 SELECT * FROM ( SELECT name, dept_id, salary, AVG(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS avg_salary FROM employees ) AS sub WHERE salary > avg_salary;
-- #ans12 select*from( select * , dense_rank() over (partition by dept_id order by salary desc )as rnk from employees ) ranked where rnk=2;
-- #ans13 select name ,salary, lag(salary)over(order by salary)as prev_salary, lead(salary)over(order by salary)as prev_salary from employees;
-- #ans14 SELECT name, dept_id, salary, ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY dept_id), 2) AS percent_of_dept FROM employees;
-- #ans15 select * from( SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM employees ) ranked WHERE rnk <= 2;
-- #ans16 select * from ( SELECT emp_id, SUM(amount) AS total FROM sales GROUP BY emp_id ) total_sales WHERE total > 1500;
-- #ans17 select * from ( SELECT dept_id, COUNT(*) AS emp_total FROM employees GROUP BY dept_id ) emp_count WHERE emp_total > 1;
-- #ans18 WITH emp_sales AS ( SELECT emp_id, SUM(amount) AS total_sales FROM sales GROUP BY emp_id ), avg_sales AS ( SELECT AVG(total_sales) AS avg_sale FROM emp_sales ) SELECT es.* FROM emp_sales es, avg_sales WHERE es.total_sales > avg_sales.avg_sale;
-- #ans19 WITH RECURSIVE nums AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 10 ) SELECT * FROM nums;
-- #ans20 select * from ( SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY join_date ASC) AS rnk FROM employees ) ranked WHERE rnk = 1;