Scribd
Upload
19 views2 pages

SQL Subquery Practice Exercises

The document contains a series of SQL queries designed to extract specific employee data from two datasets: employee_details.csv and department_details.csv. Each query utilizes subqueries to filter results based on various conditions, such as salary comparisons, department locations, and hire dates. The queries aim to retrieve employee identifiers, names, job details, and other relevant information based on specified criteria.

Uploaded by

hasakib195
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
19 views2 pages

SQL Subquery Practice Exercises

The document contains a series of SQL queries designed to extract specific employee data from two datasets: employee_details.csv and department_details.csv. Each query utilizes subqueries to filter results based on various conditions, such as salary comparisons, department locations, and hire dates. The queries aim to retrieve employee identifiers, names, job details, and other relevant information based on specified criteria.

Uploaded by

hasakib195
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

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 [Link] > 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 [Link] > 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.

Common questions

Powered by AI

To find the first hired employee per job department, use: 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)).

To get employees from the 'Contracting' department, use: 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').

To find clerks earning more than any IT programmer, use: 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 JOB_ID = 'IT_PROG').

To find employees hired after the average hire date, use: 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).

Use this query: 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').

Identify such accountants using: 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 JOB_ID = 'AD_VP').

To retrieve employee details where salary exceeds the average, use: SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM employee_details WHERE SALARY > (SELECT AVG(SALARY) FROM employee_details).

To extract employees with the highest salary within their job, the query is: 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).

Use the following query: 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')).

The query to find employees in a department with a specific location is: SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM employee_details WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM department_details WHERE LOCATION_ID = 1700).

You might also like