Scribd
Upload
12 views

HR Database SQL Queries Watermarked

The document contains a series of SQL queries related to an HR database. These queries retrieve information about employees, departments, salaries, and locations, including specific conditions such as employees hired after a certain date or those working in specific locations. The queries utilize various SQL operations like JOINs, GROUP BY, and subqueries to extract relevant data.

Uploaded by

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

HR Database SQL Queries Watermarked

The document contains a series of SQL queries related to an HR database. These queries retrieve information about employees, departments, salaries, and locations, including specific conditions such as employees hired after a certain date or those working in specific locations. The queries utilize various SQL operations like JOINs, GROUP BY, and subqueries to extract relevant data.

Uploaded by

Bittu Gupta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

HR Database SQL Queries

1. Addresses of all the departments:

SELECT loc.location_id, loc.street_address, loc.city, loc.state_province, c.country_name

FROM locations loc

JOIN countries c ON loc.country_id = c.country_id;

2. Name, department ID and name of all the employees:

SELECT e.first_name, e.last_name, e.department_id, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id;

3. Employees who work in London:


ta
up

SELECT e.first_name, e.last_name, e.job_id, e.department_id, d.department_name

FROM employees e
G

JOIN departments d ON e.department_id = d.department_id


ttu

JOIN locations l ON d.location_id = l.location_id


bi

WHERE l.city = 'London';

4. Employee id, name along with their manager_id and name:

SELECT e.employee_id, e.last_name AS employee_last_name, e.manager_id, m.last_name AS manager_last_name

FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

5. Employees who were hired after 'Jones':

SELECT e.first_name, e.last_name, e.hire_date

FROM employees e

JOIN employees j ON j.last_name = 'Jones'

WHERE e.hire_date > j.hire_date;

6. Department name and number of employees in the department:

SELECT d.department_name, COUNT(e.employee_id) AS number_of_employees

FROM departments d

LEFT JOIN employees e ON d.department_id = e.department_id


ta

GROUP BY d.department_name;
up
G

7. Managers whose experience is more than 15 years:


ttu

SELECT d.department_name, e.first_name, e.last_name, e.hire_date, e.salary


bi

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE e.employee_id IN (SELECT DISTINCT manager_id FROM employees)

AND e.hire_date < DATE_SUB(CURDATE(), INTERVAL 15 YEAR);

8. Employees with a higher salary than 'Bull':


SELECT e.first_name, e.last_name, e.salary

FROM employees e

WHERE e.salary > (SELECT salary FROM employees WHERE last_name = 'Bull');

9. Employees in the IT department:

SELECT e.first_name, e.last_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = 'IT';

10. Employees who have a manager and worked in a USA based department:
ta

SELECT e.first_name, e.last_name


up

FROM employees e

JOIN departments d ON e.department_id = d.department_id


G

JOIN locations l ON d.location_id = l.location_id


ttu

JOIN countries c ON l.country_id = c.country_id


bi

WHERE e.manager_id IS NOT NULL AND c.country_name = 'USA';

11. Employees with salary greater than the average salary:

SELECT e.first_name, e.last_name, e.salary

FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees);

12. Employees whose salary is equal to the minimum salary for their job grade:

SELECT e.first_name, e.last_name, e.salary

FROM employees e

WHERE e.salary = (SELECT MIN(salary) FROM employees WHERE job_id = e.job_id);

13. Employees who earn more than the average salary and work in IT departments:

SELECT e.first_name, e.last_name, e.salary

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE e.salary > (SELECT AVG(salary) FROM employees)


ta

AND d.department_name = 'IT';


up
G

14. Employees who earn the same salary as the minimum salary for all departments:
ttu

SELECT e.first_name, e.last_name, e.salary


bi

FROM employees e

WHERE e.salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);

15. Employees who earn higher than all Shipping Clerks:

SELECT e.first_name, e.last_name, e.salary


FROM employees e

WHERE e.salary > (SELECT MAX(salary) FROM employees WHERE job_id = 'SH_CLERK')

ORDER BY e.salary ASC;

ta
up
G
ttu
bi

You might also like