Link : https://www.w3resource.com/sql-exercises/joins-hr/index.

php
=======================SQL JOINS ===================
/*
From the following tables, write a SQL query to find those employees whose first
name contains a letter ‘z’.
Return first name, last name, department, city, and state province.
*/

select e.first_name, e.last_name, d.department_id , d.department_name , l.city ,

l.state_province
from employees e
inner join departments d
on e.manager_id = d.manager_id
inner join locations l
on d.location_id = l.location_id
where substr(e.first_name,1,1) = 'A';

/*Create job_grades table with column grade_level , lowest_sal, highest_sal */

CREATE TABLE JOB_GRADES (
grade_level char(1),
lowest_sal NUMBER(8,2),
highest_sal NUMBER(8,2)
);

/* insert values into job_grades_table

A 1000 2999
B 3000 5999
C 6000 9999
D 10000 14999
E 15000 24999
F 25000 40000
*/
insert into job_grades values ('A',1000,2999);
insert into job_grades values ('B',3000,5999);
insert into job_grades values ('C',6000,9999);
insert into job_grades values ('D',10000,14999);
insert into job_grades values ('E',15000,24999);
insert into job_grades values ('F',25000,40000);

/*
From the following table, write a SQL query to find the first name, last name,
salary, and job grade for all employees.
*/

select e.first_name, e.last_name, e.salary, j.grade_level

from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;

/*
From the following table, write a SQL query to find all departments including those
without any employee.
Return first name, last name, department ID, department name.
*/
--right join
select e.first_name , e.last_name ,d.department_id ,d.department_name
from employees e
right join departments d
on e.department_id = d.department_id;

/*
From the following table, write a SQL query to find the employees and their
managers.
Return the first name of the employee and manager.
*/
--self join
select e1.first_name as emp_name, e2.first_name as Manager_name
from employees e1, employees e2
where e1.manager_id = e2.employee_id;

/*
From the following tables, write a SQL query to display the department name, city,
and state province for each department.
*/
select distinct(d.department_name),l.city,l.STATE_PROVINCE
from departments d
inner join locations l
on d.location_id = l.location_id;

/*
From the following tables, write a SQL query to find those employees who have or
not any department.
Return first name, last name, department ID, department name.
*/
--left join
select e.first_name, e.last_name , d.department_id , d.department_name
from employees e
left join departments d
on e.department_id = d.department_id;

/*
From the following table, write a SQL query to find the employees and their
managers.
These managers do not work under any manager. Return the first name of the
employee and manager.
*/

/*
From the following tables, write a SQL query to find those employees who work in a
department
where the employee of last name 'Taylor' works. Return first name, last name and
department ID.
*/

/*
From the following tables, write a SQL query to find those employees who joined
between 1st January 1993 and 31 August 1997.
Return job title, department name, employee name, and joining date of the job.
*/

/*
From the following tables, write a SQL query to find the difference between maximum
salary of the job and salary of
the employees. Return job title, employee name, and salary difference.
*/
/*
From the following table, write a SQL query to compute the average salary,
number of employees received commission in that department. Return department name,
average salary and number of employees.
*/

/*
From the following tables, write a SQL query to compute the difference between
maximum salary and salary of all the
employees who works the department of ID 80. Return job title, employee name and
salary difference.
*/

/*
From the following table, write a SQL query to find the name of the country, city,
and departments, which are running there.
*/

/*
From the following tables, write a SQL query to find the department name and
the full name (first and last name) of the manager.
*/

/*
From the following table, write a SQL query to compute the average salary of
employees for each job title.
*/

/*
From the following tables, write a SQL query to find those departments where at
least 2 employees work. Group the result set on country name and city. Return
country name, city, and number of departments.
*/

/*
From the following tables, write a SQL query to compute the number of days worked
by employees in a department of ID 80. Return employee ID, job title, number of
days worked.
*/

/*
From the following tables, write a SQL query to find full name (first and last
name), and salary of those employees who work in any department located in 'London'
city.
*/

/*
From the following tables, write a SQL query to find full name (first and last
name), job title, starting and ending date of last jobs of employees who worked
without a commission percentage.
*/

/*
From the following tables, write a SQL query to find the full name (first and last
name) of the employee with ID and name of the country presently where he/she is
working.
*/
======== SUBQUERIES =======
https://www.w3resource.com/sql-exercises/sql-subqueries-exercises.php

/* 1. From the following table, write a SQL query to find those employees who get
higher salary than the employee whose ID is 163. Return first name, last name.
*/
>>> SELECT FIRST_NAME , SALARY FROM EMPLOYEES
WHERE SALARY = (SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 163);

/*
2. From the following table, write a SQL query to find those employees whose
salary matches the smallest salary of any of the departments. Return first name,
last name and department ID.
*/

[WRONG ANSWER : TOO MANY VALUES FOR SALARY COMING OUT OF SUBQUERY
>>>SELECT FIRST_NAME, SALARY, DEPARTMENT_ID FROM EMPLOYEES
WHERE SALARY IN (SELECT department_id,MIN(SALARY) AS SALARY FROM EMPLOYEES GROUP
BY department_id);

[CORRECT ANSWER]
>>>SELECT FIRST_NAME, SALARY, DEPARTMENT_ID FROM EMPLOYEES
WHERE SALARY IN (SELECT MIN(SALARY) AS SALARY FROM EMPLOYEES GROUP BY
department_id);

/*
3. From the following table, write a SQL query to find those employees who earn
more than the average salary. Return employee ID, first name, last name.
*/

>>>SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM EMPLOYEES

WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);

/*
4. From the following table, write a SQL query to find those employees who report
that manager whose first name is ‘Payam’. Return first name, last name, employee ID
and salary.
*/

[My Solution]
>>>select e1.first_name, e1.last_name , e2.first_name as Manager_Name from
employees e1, employees e2
where e1.manager_id = e2.employee_id
and e2.first_name = 'Payam';

[subquery solution]
>>>select first_name, last_name from employees
where manager_id = (select employee_id from employees where first_name = 'Payam');

/*
5. From the following tables, write a SQL query to find all those employees who
work in the Finance department. Return department ID, name (first), job ID and
department name.
*/

select first_name from employees

where department_id in (select department_id from departments where department_name
= 'Finance');

/*
6. From the following table, write a SQL query to find the employee whose salary is
3000 and reporting person’s ID is 121. Return all fields.
*/

>>>select first_name from employees

where salary = 3000 and manager_id = (select employee_id from employees where
employee_id = 121);

/*
7. From the following table and write a SQL query to find those employees whose
salary is in the range of smallest salary, and 2500. Return all the fields.
*/
>>>select first_name , salary from employees
where salary between (select min(salary) from employees) and 2500;

/*
8. From the following tables, write a SQL query to find those employees who do not
work in those departments where manager ids are in the range 100, 200 (Begin and
end values are included.) Return all the fields of the employees.
*/
NOTE : The BETWEEN operator is inclusive: begin and end values are included.

>>>SELECT FIRST_NAME,SALARY FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE MANAGER_ID NOT
BETWEEN 100 AND 200);

/*
9. From the following table, write a SQL query to find those employees who get
second-lowest salary. Return all the fields of the employees.
*/
NOTE : The where statement gets executed before the order by
[My Solution]
>>>SELECT FIRST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY) FROM (SELECT DISTINCT(SALARY)
FROM EMPLOYEES
ORDER BY SALARY ASC) WHERE ROWNUM < 3);

/*
10. From the following tables, write a SQL query to find those employees who work
in the same department where ‘Clara’ works. Exclude all those records where first
name is ‘Clara’. Return first name, last name and hire date.
*/

>>>select first_name from employees

where department_id in (
select department_id from employees where first_name = 'Clara'
)
and first_name <> 'Clara'
order by first_name;

/*
11. From the following tables, write a SQL query to find those employees who earn
more than the average salary and work in a department with any employee whose first
name contains a character a 'J'. Return employee ID, first name and salary.
*/

>>>select * from employees

where salary > (select avg(salary) from employees)
and department_id in (select department_id from employees where first_name like '%J
%');

/*
12. write a query to display the records of employees earning highest and second
highest in each department
*/

select * from (
select employee_id , department_id , salary,
rank() over (partition by department_id order by department_id asc,salary desc) as
sal_rank
from employees)
where sal_rank in (1,2);

/*
14. From the following table, write a SQL query to find those employees whose
department located at 'Toronto'. Return first name, last name, employee ID, job ID.
*/

--solved using subquery

>>>select first_name,department_id from employees
where department_id in (
select department_id from departments where location_id in (
select location_id from locations where city = 'Toronto'
)
);

--solved using joins

>>>select e.first_name,e.department_id,l.city
from employees e
inner JOIN departments d
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id
where city = 'Toronto';

/*
15. From the following table, write a SQL query to find those employees whose
salary is lower than any salary of those employees whose job id is 'MK_MAN'.
Exclude employees of Job id ‘MK_MAN’. Return employee ID, first name, last name,
job ID.
*/

>>>SELECT employee_id,first_name,last_name,job_id
FROM employees
WHERE salary < ANY
( SELECT salary
FROM employees
WHERE job_id = 'MK_MAN' );

/*
16. Write a query to display the employee id, name ( first name and last name ) and
the job id column with a modified title SALESMAN for those employees whose job id
is ST_MAN and DEVELOPER for whose job id is IT_PROG.
*/

>>>select employee_id, first_name ||' '|| last_name as NAME,

case job_id
when 'ST_MAN' then 'SALESMAN'
when 'IT_PROG' then 'DEVELOPER'
else job_id
END as Designation, salary
from employees;

/*
17. Write a query to display the employee id, name ( first name and last name ),
SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the
SalaryStatus column with a title HIGH and LOW respectively for those employees
whose salary is more than and less than the average salary of all employees.
*/
[Wrong Answer][FInd out why is it wrong]
select employee_id, first_name||'-'||last_name as NAME,salary as SalaryDrawn,
round(salary - (select avg(salary) from employees) ,2) AS AvgCompare,
case
when (AvgCompare > 0) then 'High'
else 'low'
end as SalaryStatus
from employees;

[Correct Answer]
>>>select employee_id, first_name||'-'||last_name as NAME,salary as SalaryDrawn,
round(salary - (select avg(salary) from employees) ,2) AS AvgCompare,
case
when (salary > (select avg(salary) from employees)) then 'High'
else 'low'
end as SalaryStatus
from employees;

/*
18. From the following table, write a SQL query to find all those departments
where at least one or more employees work.Return department name.
*/

select department_id,sum(salary) from employees

where department_id in (
select department_id from (
select department_id,count(employee_id)
from employees
group by department_id
having count(employee_id) > 0
)
)
group by department_id
order by department_id;

/*
21. From the following tables, write a SQL query to find those employees whose
salary is greater than 50% of their department's total salary bill. Return first
name, last name.
*/

[This solution feels too memory-taking because it keeps calculating Sum everytime
instead of calculation sum of each dept salary and storing it at once]

select e1.first_name,e1.salary from employees e1

where salary > (
select sum(salary)/2
from employees e2
where e1.department_id = e2.department_id
);

https://medium.com/@karthikjoshi95/aggregate-functions-using-group-by-over-and-
partition-by-clause-in-sql-ed2517c3110
[My solution which is better]
select * from (
select first_name,salary,department_id, sum(salary) over(partition by
department_id) as dept_sal
from employees
)
where salary > dept_sal/2;

/*
22. From the following tables, write a SQL query to find those employees who are
managers. Return all the fields of employees table.
*/

[Wesbite Solution][Wrong Answer because department column got missing manager_ids]

[it would have been correct if the data wasnt missing]
SELECT *
FROM employees e
WHERE EXISTS
(SELECT *
FROM departments d
WHERE d.manager_id = e.employee_id);

[My Solution]
select first_name ||' ' || last_name as name, employee_id from employees
where employee_id in (
select emp_id from(
select distinct(manager_id) as emp_id,
count(employee_id) over(partition by manager_id) as emp_count
from employees
)
where emp_count > 0
)
order by employee_id;

/*
23. From the following table, write a SQL query to find those employees who get
such a salary, which is the maximum of salaried employee, joining within January
1st, 2002 and December 31st, 2003. Return employee ID, first name, last name,
salary, department name and city.
*/

/*
24. From the following table, write a SQL query to find those employees who joined
after the employee whose ID is 165. Return first name, last name and hire date.
*/

/*
25. From the following tables, write a SQL query to find those departments where
maximum salary is 7000 and above. The employees worked in those departments have
already completed one or more jobs. Return all the fields of the departments.
*/

/*
26. From the following table, write a SQL query to find those managers who
supervise four or more employees. Return manager name, department ID.
*/

/*
27.
From the following table, write a SQL query to find those employees who did not
have any job in the past. Return all the fields of employees.
*/

--==== PIVOT QUERIES ===

/*
1. write a query to pivot table with department(10,20,30) as columns
and find sum of salary and count of employees in each dept
*/

select * from(select department_id,salary from employees)

pivot (
sum(salary) as sal_count,count(*) as emp_count
for department_id in (10 dept_10,20 dept_20,30 dept_30)
);

/*
2. Write a query to pivot tables with depart(40,50,60) as columns , rows as job_id
and find
salary of job_id in that dept
*/
select * from (select job_id,department_id,salary from employees)
pivot (
sum(salary)
for department_id in (40,50,60)
);

--==== REGEX QUERIES =====

/*
1. Write an sql query to show all names(first_name + last_name) of employees who
name starts with vowels
*/
-- ^ : ^ matches the beginning of a String
select * from (select first_name ||' '||last_name as emp_name from employees)
where REGEXP_LIKE (emp_name,'^[A,E,I,O,U]')
order by emp_name;

/*
2. Write an sql query to show all names(first_name + last_name) of employees who
name starts AND ends with vowels
*/
-- * Matches zero or more instances of the preceding String
-- . Matches any single character
select * from (select first_name || ' ' || last_name as emp_name from employees)
where regexp_like (emp_name, '^[A,E,I,O,U].*[a,e,i,o,u]$')
order by emp_name;

/*
3. Write an sql query to show all names(first_name + last_name) of employees that
do not start with a vowel
*/
--[^abc] Matches any character not listed in between the square brackets
select * from (select first_name || ' ' || last_name as emp_name from employees)
where REGEXP_LIKE (emp_name, '^[^A,E,I,O,U]')
order by emp_name;

/*
4. Write an sql query to show all names(first_name + last_name) of employees that
do not end with a vowel
*/
select * from (select first_name || ' ' || last_name as emp_name from employees)
where regexp_like (emp_name,'[^a,e,i,o,u]$')
order by emp_name;

/*
5. Write an sql query to show all names(first_name + last_name) of employees who
name starts with a vowel [OR] ends with a vowel
*/
-- p1|p2|p3 Mathes any of the specified pattern
select * from (select first_name || ' ' || last_name as emp_name from employees)
where regexp_like ( emp_name, '^[A,E,I,O,U].|[a,e,i,o,u]$')
order by emp_name;

/*
6. Write an sql query to show all names(first_name + last_name) of employees who
name does not starts and ends with a vowel
*/
select * from (select first_name || ' ' || last_name as emp_name from employees)
where regexp_like ( emp_name, '^[^A,E,I,O,U].*[^a,e,i,o,u]$')
order by emp_name;