MYSQL PRACTICE

CREATE TABLE Employee (
emp_id INTEGER PRIMARY KEY,
emp_name TEXT,
department TEXT,
salary INTEGER,
age INTEGER,
city TEXT,
joining_date DATE
);

INSERT INTO Employee VALUES
(101, 'Rahul', 'IT', 70000, 25, 'Delhi', '2022-01-10'),
(102, 'Priya', 'HR', 50000, 28, 'Mumbai', '2021-05-15'),
(103, 'Amit', 'IT', 80000, 30, 'Pune', '2020-03-20'),
(104, 'Sneha', 'Finance', 60000, 29, 'Delhi', '2023-07-01'),
(105, 'Rohan', 'IT', 90000, 35, 'Bangalore', '2019-11-11'),
(106, 'Neha', 'HR', 55000, 27, 'Mumbai', '2022-08-25'),
(107, 'Vikas', 'Finance', 75000, 32, 'Pune', '2018-02-18'),
(108, 'Anjali', 'Sales', 45000, 24, 'Delhi', '2024-01-12'),
(109, 'Karan', 'Sales', 52000, 31, 'Bangalore', '2021-09-09'),
(110, 'Pooja', 'IT', 85000, 29, 'Mumbai', '2020-06-30'),
(111, 'Arjun', 'Finance', 65000, 33, 'Delhi', '2019-12-01'),
(112, 'Meera', 'HR', 58000, 26, 'Pune', '2023-03-15'),
(113, 'Nikhil', 'IT', 72000, 28, 'Delhi', '2021-04-22'),
(114, 'Simran', 'Sales', 48000, 27, 'Mumbai', '2022-10-10'),
(115, 'Deepak', 'Finance', 82000, 36, 'Bangalore', '2017-05-05');

SELECT * FROM Employee;

-- Find the total number of employees.

-- SELECT count() FROM Employee;
-- recommeded and count() haar column ki row count karo
-- SELECT count(emp_id) FROM Employee; not this

-- Find the highest salary.
select MAX(salary) from Employee;
-- Find the lowest salary.Employee
SELECT min(salary) from Employee;
-- Find the average salary.Employee
SELECT avg(salary) from Employee;
-- Find the total salary paid to all employees.Employee
SELECT sum(salary) from Employee;
-- Count employees in the IT department.
SELECT count(department) from Employee where department='IT';
-- Find the highest age
SELECT max(age) from Employee;
-- Find the lowest age.
SELECT min(age) from Employee;
-- Find the average age.
SELECT avg(age) from Employee;
-- Find the total number of distinct departments.
SELECT count(DISTINCT(department)) from Employee;

SELECT DISTINCT(department) from Employee;

-- Level 2: WHERE + Aggregate (11–20)
-- Find the highest salary in the IT department.
SELECT MAX(salary),department from Employee WHERE salary < (SELECT MAX(salary) from Employee);

-- Find the lowest salary in the HR department.
-- Find the average salary of Finance employees.
-- Count employees from Delhi.
-- Count employees whose salary is greater than 70,000.
-- Find the total salary of employees from Mumbai.
-- Find the maximum salary of employees older than 30.
-- Find the minimum salary of employees younger than 30.
-- Count employees whose age is between 25 and 30.
-- Find the average salary of employees who joined after 2021.

-- Level 3: GROUP BY (21–30)
-- Count employees in each department.
-- Find the average salary of each department.
-- Find the maximum salary in each department.
-- Find the minimum salary in each department.
-- Find the total salary of each department.
-- Count employees in each city.
-- Find the average age in each city.
-- Find the highest salary in each city.
-- Find the total salary paid in each city.
-- Count employees by department and city.

-- Level 4: HAVING (31–38)
-- Show departments having more than 2 employees.
-- Show cities having more than 2 employees.
-- Show departments where the average salary is greater than 70,000.
-- Show departments whose total salary is more than 2,50,000.
-- Show cities where the maximum salary is greater than 80,000.
-- Show departments where the minimum salary is less than 50,000.
-- Show departments having at least 3 employees.
-- Show cities where the average age is greater than 28.

-- Level 5: Real Interview Questions (39–50)
-- Find the second highest salary.
-- Find the third highest salary.
-- Find all employees earning the highest salary.
-- Find all employees earning the lowest salary.
-- Find employees whose salary is greater than the average salary.
-- Find departments whose average salary is greater than the companys average salary.
-- Find the department with the highest average salary.
-- Find the city with the maximum number of employees.
-- Find the department that pays the highest total salary.
-- Find departments where every employee earns more than 50,000.
-- Find departments where at least one employee earns more than 85,000.
-- Find the difference between the highest and lowest salary.

-- After these 50, practice these advanced questions:

-- Find the Nth highest salary.
-- Find duplicate salaries.
-- Find employees with the same salary.
-- Find top 3 highest-paid employees.
-- Find bottom 3 lowest-paid employees.
-- Find department-wise highest-paid employee.
-- Find department-wise lowest-paid employee.
-- Find employees earning above their department average.
-- Find employees earning below their department average.