Practice Exercises
-- --------------------------------------------------------------
# Dataset Used: employee_details.csv and Department_Details.csv
# Use subqueries to answer every question
-- --------------------------------------------------------------
1. Retrive employee_id , first_name , last_name and salary details of those employees
whose salary is greater than the average salary of all the employees.(11 Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY from employee_details
where SALARY > (select avg(salary) from employee_details);
2. Display first_name , last_name and department_id of those employee where the
location_id of their department is 1700(3 Rows)
select FIRST_NAME, LAST_NAME, DEPARTMENT_ID from employee_details
where DEPARTMENT_ID in (select DEPARTMENT_ID from department_details where
LOCATION_ID = 1700);
3. From the table employees_details, extract the employee_id, first_name, last_name,
job_id and department_id who work in any of the departments of Shipping,
Executive and Finance.(9 Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID from
employee_details
where DEPARTMENT_ID in (select DEPARTMENT_ID from department_details where
DEPARTMENT_NAME in ('Shipping','Executive','Finance'));
4. Extract employee_id, first_name, last_name,salary, phone_number and email of the
CLERKS who earn more than the salary of any IT_PROGRAMMER.(3 Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, PHONE_NUMBER, EMAIL
from (select * from employee_details where JOB_ID = 'ST_CLERK') as t
where t.SALARY > any(select salary from employee_details where
employee_details.JOB_ID = 'IT_PROG');
5. Extract employee_id, first_name, last_name,salary, phone_number, email of the
AC_ACCOUNTANTs who earn a salary more than all the AD_VPs.(2 Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, PHONE_NUMBER, EMAIL
from
(select * from employee_details where JOB_ID = 'AC_ACCOUNTANT') as t
where t.salary > all(select SALARY from employee_details where
employee_details.JOB_ID = 'AD_VP');
Proprietary content. © Great Learning. All Rights Reserved. Unauthorized use or distribution
prohibited.
6. Write a Query to display the employee_id, first_name, last_name,department_id of
the employees who have been recruited after the middle(avg) hire_date. (10 Rows)
set @mi = (select count(*) from employee_details);
with middle as (select round(if(mod(@mi,2) != 0, (@mi + 1) / 2, ((@mi/2)+((@mi/2)+1))/2))
as c),
mhire as (select HIRE_DATE, row_number() over(order by HIRE_DATE) as r from
employee_details)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID from
employee_details
where hire_date > (select HIRE_DATE from mhire, middle where middle.c = mhire.r);
7. Extract employee_id, first_name, last_name, phone_number, salary and job_id of the
employees belonging to the 'Contracting' department (3 Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER, SALARY,
JOB_ID
from employee_details where DEPARTMENT_ID in (select DEPARTMENT_ID from
department_details where DEPARTMENT_NAME = 'Contracting');
8. Extract employee_id, first_name, last_name, phone_number, salary and job_id of the
employees who does not belong to 'Contracting' department(18 Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER, SALARY,
JOB_ID
from employee_details where DEPARTMENT_ID not in (select DEPARTMENT_ID from
department_details where DEPARTMENT_NAME = 'Contracting');
9. Display the employee_id, first_name, last_name, job_id and department_id of the
employees who were recruited first in the department(7 Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_ID from
employee_details
where EMPLOYEE_ID in (select FIRST_VALUE(EMPLOYEE_ID) over w as
first_person_hired
from employee_details
window w as (partition by JOB_ID order by hire_date desc));
10. Display the employee_id, first_name, last_name, salary and job_id of the employees
who earn maximum salary for every job.( 7Rows)
select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, JOB_ID from
employee_details
where salary in (select max(salary) over(partition by JOB_ID) as max_salary from
employee_details);
Proprietary content. © Great Learning. All Rights Reserved. Unauthorized use or distribution
prohibited.
