-- -- Basic SQL

-- -- Q1. Insert an employee with emp_id = 4, name Priya, age 25, and salary 55000.

-- -- Q2. Display only the name and salary of all employees.

-- -- Q3. Display employees whose salary is greater than 50000.

-- -- Q4. Display employees whose age is 23 AND salary is greater than 60000.

-- -- Q5. Display employees whose age is 23 OR salary is greater than 60000.

-- -- Q6. Display employees whose age is not 23.

-- -- Q7. Display employees in ascending order of salary.

-- -- Q8. Display employees in descending order of salary.

-- -- Q9. Update the salary of the employee with emp_id = 4 to 65000.

-- -- Q10. Delete the employee with emp_id = 4.

-- -- Mixed Practice

-- -- Q11. Insert an employee with emp_id = 5, name Aman, age 30, and salary 70000. Then display only Aman.

-- -- Q12. Display the name and age of employees whose age is greater than 25 AND salary is less than 70000.

-- -- Q13. Display employees whose age is less than 25 OR salary is greater than 60000, and sort the result by name in ascending order.

-- -- Q14. Update the age of the employee with emp_id = 5 from 30 to 32.

-- -- Q15. Delete the employee with emp_id = 5.

-- -- DISTINCT

-- -- Q16. Display all unique ages.

-- -- Q17. Display all unique salaries.

-- -- Q18. Count the number of different age groups.

-- -- Aggregate Functions

-- -- Q19. Count the total number of employees.

-- -- Q20. Find the maximum salary.

-- -- Q21. Find the minimum salary.

-- -- Q22. Find the average salary.

-- -- Q23. Find the total salary of all employees.

-- -- Q24. Count the number of employees whose salary is greater than 50000.

-- -- Q25. Find the average salary of employees whose salary is greater than 50000.

-- -- GROUP BY

-- -- Q26. Count the number of employees in each age group.

-- -- Q27. Find the average salary for each age group.

-- -- Q28. Find the maximum salary for each age group.

-- -- Q29. Find the total salary for each age group.

-- -- Q30. Count employees in each age group and display only groups having more than 1 employee.

-- -- WHERE + GROUP BY + HAVING

-- -- Q31. Consider only employees whose salary is greater than 50000. Group them by age and display the employee count for each age group.

-- -- Q32. Count employees in each age group and display only age groups having more than 1 employee.

-- -- Q33. Consider only employees whose salary is greater than 50000. Group them by age and display only groups having more than 1 employee.

-- -- Q34. Find the average salary for each age group and display only groups whose average salary is greater than 50000.






-- -- solutions:
-- CREATE TABLE employees (
--     emp_id INT PRIMARY KEY,
--     name VARCHAR(50),
--     age INT,
--     salary INT
-- );

-- INSERT INTO employees VALUES
-- (1, 'Rahul', 43, 23000),
-- (2, 'Sakshi Mishra', 23, 67000),
-- (3, 'Sahil', 23, 64000);

-- -- inserting
-- INSERT INTO employees VALUES (4,'priya',54,230);

-- -- deleting
-- DELETE FROM employees WHERE emp_id=2;

-- -- update
-- UPDATE employees
--     SET name='xoxo', salary=209
--     WHERE emp_id=1;


-- -- drop
-- -- DROP TABLE employees;

-- -- TRUNCATE
-- -- TRUNCATE TABLE employees;
-- -- OR 
-- -- DELETE FROM employees;


-- -- ALTER- CHANGE STRUCTURE OF TABLE
-- -- E.G. ADD NEW COL 
-- ALTER TABLE employees
--     add email varchar(100);


-- -- inserting data in email
-- -- INSERT INTO employees (email) values('sakshi@1');

-- -- OR
-- UPDATE employees
-- SET email = 'sakshi@1';

-- select * from employees;

-- select name, salary from employees;

-- select * from employees where salary>50000;


-- select * from employees where age=23 AND salary>60000;


-- select * from employees where not age=23;



-- -- ascending order -- default order

-- SELECT * from employees order by salary ASC;

-- SELECT * from employees order by salary DESC;

-- UPDATE EMPLOYEES SET SALARY=500 WHERE EMP_ID=4;


-- DELETE from employees where emp_id=4;

-- INSERT INTO employees values(5,'aman', 30,64000, 'bewa@1');


-- select name,age from employees where age > 25 AND salary < 70000;
-- select * from employees where age < 25 OR salary > 60000;


-- update employees set age=20 where emp_id=1;

-- -- delete from employees where emp_id=5;

-- -- DISTINCT

-- SELECT DISTINCT age from employees;

-- SELECT DISTINCT salary from employees;

-- SELECT count(age) from employees;



-- -- aggregate functions

-- -- count
-- select count(*) from employees;
-- -- max
-- select max(salary) from employees;

-- -- min
-- select min(salary) from employees;


-- -- avg

-- select avg(salary) from employees;

-- -- sum
-- select sum(salary) from employees;

-- select count(*) from employees where salary>50000;



-- -- group by
-- select age, count(*) from employees
-- group by age;


-- select age, avg(salary) from employees group by age;

-- select age, max(salary) from employees group by age;

-- select age, sum(salary) from employees group by age;

-- select age, count(*) from employees
-- group by age having count(*)>1;

-- -- having vs where

-- select age, count(*) from employees where salary>5000 Group by age; 

-- select age,count(*) from employees group by age having count(*)>1;

-- select age, count(*) from employees where salary>50000 group by age having count(*)>1;

-- select age,avg(salary) from employees group by age having avg(salary)>5000;

-- select * from employees;


-- -- CASE WHEN — Practice Q

-- -- employees table se name aur age display karo, aur ek new column age_group banao:

-- -- age < 25 → "Young"
-- -- age 25–35 → "Adult"
-- -- age > 35 → "Senior"



-- SELECT name,age,
-- CASE 
-- WHEN age<25 THEN 'young'
-- WHEN age>=25 AND age<=35 THEN 'Adult'
-- ELSE 'senior'
-- END AS age_output
-- from employees;



-- -- String function
-- -- employees table se name aur name ki length display karo.
-- -- Output columns hone chahiye:
-- -- name | name_length
-- SELECT name, LENGTH(name) AS name_length from employees;
-- SELECT name, UPPER(name) AS name_upper from employees;

-- SELECT name, email, CONCAT(name,' ','-',email) from employees;


-- SELECT name,SUBSTRING(name, 1, 3)
-- FROM employees;


-- SELECT name, Replace(name, 'a','@') from employees;



-- JOINS 

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary INT
);

INSERT INTO employees VALUES
(1, 'Rahul', 43, 23000),
(2, 'Sakshi', 23, 67000),
(3, 'Sahil', 23, 64000),
(4, 'Priya', 25, 55000),
(5, 'Aman', 30, 70000);


CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    emp_id INT,
    department VARCHAR(50)
);

INSERT INTO departments VALUES
(101, 1, 'HR'),
(102, 2, 'IT'),
(103, 3, 'Finance'),
(104, 4, 'IT');


-- SELECT * from employees;
-- select * from departments;

-- INNER JOINS
-- QUE: Using employees and departments, display employee name, salary, and department for employees who have a matching department. Use INNER JOIN
SELECT e.name, e.salary, d.department
FROM employees e
INNER JOIN departments d
ON e.emp_id = d.emp_id;


-- INNER JOIN + WHERE
-- QUE: Display the employee name and department of employees who belong to the IT department.
SELECT e.name, d.department FROM employees e INNER JOIN departments d ON e.emp_id = d.emp_id WHERE d.department= 'IT';



-- LEFT JOIN

-- Using employees and departments, display employee name and department for all employees, including employees who do not have a department.Use LEFT JOIN.

SELECT e.name,d.department FROM employees e LEFT JOIN departments d ON  e.emp_id = d.emp_id;


-- right join 
-- que : Using employees and departments, display department name and employee name for all departments, including departments that have no matching employee.

SELECT e.name, d.department
FROM employees e
RIGHT JOIN departments d
ON e.emp_id = d.emp_id;

SELECT name
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE department IN ('IT', 'HR')
);



EXPLAIN
SELECT *
FROM employees
WHERE salary > 50000;