Link to Github
These are the list of tables which includes employees, projects and department table. It also shows the relationship between these tables
1) SELECT EMPLOYEES FROM
DEPARTMENT WHERE SALARY IS GREATER THAN 40K
using join
simple query which displays only id
output
2) SELECT EMPLOYEES FROM DEPARTMENT
WHERE SALARY IS 40K
output
3) SELECT EMPLOYEE WHO HAS MAX SALARY
output
4) SELECT THE SECOND HIGHEST SALARY
5) SELECT TOP 3 EMPLOYEE SALARIES IN EACH DEPARTMENT
6) SELECT MAX SALARY FOR EACH DEPARTMENT FOR EACH EMPLOYEE
These are the list of tables which includes employees, projects and department table. It also shows the relationship between these tables
employees projects +---------------+---------+ +---------------+---------+ | id | int |<----+ +->| id | int | | first_name | varchar | | | | title | varchar | | last_name | varchar | | | | start_date | date | | salary | int | | | | end_date | date | | department_id | int |--+ | | | budget | int | +---------------+---------+ | | | +---------------+---------+ | | | departments | | | employees_projects +---------------+---------+ | | | +---------------+---------+ | id | int |<-+ | +--| project_id | int | | name | varchar | +-----| employee_id | int | +---------------+---------+ +---------------+---------+
1) SELECT EMPLOYEES FROM
DEPARTMENT WHERE SALARY IS GREATER THAN 40K
select concat(employees.first_name,' ',employees.last_name) as employee_name,names from employees,departments where employees.department_id=departments.id group by employees.id,employees.salary having employees.salary > 40000;
using join
select e.id,concat(e.first_name," ",e.last_name) as employee_name, d.name,e.salary
from employees e join departments d on e.department_id = d.id group by salary,e.id,employee_name,e.salary,d.name having salary >40000;
simple query which displays only id
select id from employees where salary > 40000;
+----+---------------+-------------+--------+ | id | employee_name | name | salary | +----+---------------+-------------+--------+ | 5 | Ian Peterson | Engineering | 80000 | | 6 | John Mills | Marketing | 50000 | +----+---------------+-------------+--------+
2) SELECT EMPLOYEES FROM DEPARTMENT
WHERE SALARY IS 40K
select e.id,concat(e.first_name," ",e.last_name) as employee_name, d.name,e.salary from employees e join departments d on e.department_id = d.id group by salary,e.id,employee_name,e.salary,d.name having salary = 40000; #using multiple group by condition select e.id,concat(e.first_name," ",e.last_name) as employee_name, d.name,e.salary from employees e join departments d on e.department_id = d.id group by salary,e.id,employee_name,e.salary,d.name having salary >40000 and salary < 70000;
+----+---------------+-----------+--------+ | id | employee_name | name | salary | +----+---------------+-----------+--------+ | 6 | John Mills | Marketing | 50000 | +----+---------------+-----------+--------+
3) SELECT EMPLOYEE WHO HAS MAX SALARY
select id,max(salary) from employees group by salary,id order by salary desc limit 1; #without using limit select id, salary from employees where salary in ( select max(salary) from employees); #using CTE with result as (select id,first_name,salary, DENSE_RANK() over (order by salary desc) as rnk from employees) select * from result where rnk=1;
| id | first_name | salary | rnk | +----+------------+--------+-----+ | 5 | Ian | 80000 | 1 | +----+------------+--------+-----+
4) SELECT THE SECOND HIGHEST SALARY
#logic using less than select max(salary) from employees where salary < (select max(salary) from employees); +-------------+ | max(salary) | +-------------+ | 50000 | +-------------+ select id,max(salary) from employees where salary not in (select max(salary) from employees) group by id order by salary desc limit 1; +----+-------------+ | id | max(salary) | +----+-------------+ | 6 | 50000 | +----+-------------+ with result as (select id,first_name,salary, DENSE_RANK() over (order by salary desc) as rnk from employees) select * from result where rnk=2; +----+------------+--------+-----+ | id | first_name | salary | rnk | +----+------------+--------+-----+ | 6 | John | 50000 | 2 | +----+------------+--------+-----+ # second highest salary without using order by and limit select id,salary from employees where salary in (select max(salary) from employees where salary not in (select max(salary) from employees)); +----+--------+ | id | salary | +----+--------+ | 6 | 50000 | +----+--------+ # Here we have multiple sub query trying to filter max salary to the outer query # Here we also create table based on queries select id,salary from employees where salary in (select max(t.salary) as max_salary from (select max(salary) as salary from employees where salary not in(select max(salary) from employees) group by id,salary)as t);
5) SELECT TOP 3 EMPLOYEE SALARIES IN EACH DEPARTMENT
with cte as (select id,department_id,salary,DENSE_RANK()
over (partition by department_id order by salary desc) as rnk from employees)
select * from cte where rnk <=2; +----+---------------+--------+-----+ | id | department_id | salary | rnk | +----+---------------+--------+-----+ | 1 | 1 | 20000 | 1 | | 5 | 2 | 80000 | 1 | | 3 | 2 | 30000 | 2 | | 6 | 3 | 50000 | 1 | | 2 | 5 | 10000 | 1 | +----+---------------+--------+-----+
6) SELECT MAX SALARY FOR EACH DEPARTMENT FOR EACH EMPLOYEE
select t.department_id,e.id,e.salary from employees e join (select max(salary) as max_salary,department_id from employees group by department_id)as t on e.salary=t.max_salary and e.department_id=t.department_id; +---------------+----+--------+ | department_id | id | salary | +---------------+----+--------+ | 1 | 1 | 20000 | | 5 | 2 | 10000 | | 2 | 5 | 80000 | | 3 | 6 | 50000 | +---------------+----+--------+
No comments:
Post a Comment