SQL-CODE

===Step 1: Create Table=== CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), age INT, salary INT, department VARCHAR(30) );

===Step 2: Insert Sample Data=== INSERT INTO employees VALUES (1, 'Ravi', 28, 45000, 'IT'); INSERT INTO employees VALUES (2, 'Asha', 32, 52000, 'HR'); INSERT INTO employees VALUES (3, 'Manu', 45, 68000, 'Finance'); INSERT INTO employees VALUES (4, 'Sanjay', 29, 40000, 'IT'); INSERT INTO employees VALUES (5, 'Divya', 35, 75000, 'Sales'); INSERT INTO employees VALUES (6, 'Kiran', 26, 30000, 'Sales'); INSERT INTO employees VALUES (7, 'Meera', 41, 60000, 'Finance'); INSERT INTO employees VALUES (8, 'Vikram', 30, 55000, 'HR');

====SQL Query Examples===

1. Select all rows SELECT * FROM employees;

2. Select specific columns SELECT name, salary FROM employees;

3. Filter using WHERE SELECT * FROM employees WHERE age > 30;

4. AND + OR conditions SELECT * FROM employees WHERE department='IT' AND salary > 42000;

5. ORDER BY descending SELECT * FROM employees ORDER BY salary DESC;

6. LIMIT SELECT * FROM employees LIMIT 3;

7. COUNT rows SELECT COUNT(*) AS total_employees FROM employees;

8. GROUP BY (avg salary by department) SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;

9. MIN value SELECT MIN(salary) AS min_salary FROM employees;

10. MAX value SELECT MAX(salary) AS max_salary FROM employees;

11. Update row UPDATE employees SET salary = salary + 5000 WHERE department='Sales';

12. Delete row DELETE FROM employees WHERE age < 27;

13. Insert new employee INSERT INTO employees (id, name, age, salary, department) VALUES (9, 'Harish', 34, 48000, 'IT');

14. LIKE pattern search SELECT * FROM employees WHERE name LIKE 'D%';

15. BETWEEN SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

16. IN operator SELECT * FROM employees WHERE department IN ('IT', 'HR');

17. Create departments table CREATE TABLE departments ( dept_name VARCHAR(30) PRIMARY KEY, location VARCHAR(50) );

18. Insert into departments INSERT INTO departments VALUES ('IT', 'Bangalore'); INSERT INTO departments VALUES ('HR', 'Mumbai'); INSERT INTO departments VALUES ('Sales', 'Chennai'); INSERT INTO departments VALUES ('Finance', 'Delhi');

19. INNER JOIN SELECT e.name, e.department, d.location FROM employees e INNER JOIN departments d ON e.department = d.dept_name;

20. LEFT JOIN SELECT e.name, e.department, d.location FROM employees e LEFT JOIN departments d ON e.department = d.dept_name;

===Advanced SQL Queries===

1. Find the second highest salary SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

2. Top 3 highest salaries SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

3. Employees earning above department average SELECT name, salary, department FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department );

4. Count employees in each department (HAVING filter) SELECT department, COUNT() AS total FROM employees GROUP BY department HAVING COUNT() > 2;

5. Department-wise highest salary (using subquery) SELECT * FROM employees e WHERE salary = ( SELECT MAX(salary) FROM employees WHERE department = e.department );

6. Rank employees by salary (RANK window function) SELECT name, department, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;

7. Running total of salaries SELECT name, salary, SUM(salary) OVER (ORDER BY id) AS running_total FROM employees;

8. Partition-wise salary ranking SELECT name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;

9. Get details of employees who joined the company in the last 30 days SELECT * FROM employees WHERE join_date >= CURRENT_DATE - INTERVAL '30 days';

10. List employees who have never checked in (LEFT JOIN missing rows) SELECT e.id, e.name FROM employees e LEFT JOIN attendance a ON e.id = a.emp_id WHERE a.emp_id IS NULL;

11. Find duplicate employee names SELECT name, COUNT() FROM employees GROUP BY name HAVING COUNT() > 1;

12. Delete duplicate rows DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY name, age, salary, department );

13. Display employees who work on more than 1 project SELECT emp_id, COUNT(project_id) AS projects FROM projects GROUP BY emp_id HAVING COUNT(project_id) > 1;

14. Highest-paid employee in each department (JOIN + Window function) SELECT name, department, salary FROM ( SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) x WHERE rn = 1;

15. Employees whose salary is within top 20% SELECT name, salary FROM ( SELECT name, salary, NTILE(5) OVER (ORDER BY salary DESC) AS percentile_bucket FROM employees ) t WHERE percentile_bucket = 1;

16. Total salary per department & grand total (ROLLUP) SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY ROLLUP(department);

17. Create a VIEW for high-salary employees CREATE VIEW high_salary_emps AS SELECT * FROM employees WHERE salary > 60000;

18. Recursive CTE example (generate 1-10 numbers) WITH RECURSIVE nums AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 10 ) SELECT * FROM nums;

19. Find employees who earn more than their manager

(Assume employees table has manager_id)

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;

20. Find average daily check-in time by employee (using window aggregate) SELECT emp_id, AVG(check_in_time) AS avg_check_in FROM attendance GROUP BY emp_id;