.1.
Write a query to find the addresses (location_id, street_address, city,
state_province, country_name) of all the departments
SELECT d.location_id, l.street_address, [Link], l.state_province, c.country_name
FROM departments d
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id;
[Link] a query to find the name (first_name, last name), department ID and
name of all the employes
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. Write a query to find the name (first_name, last_name), job, department ID
and name of the employees who works in London
SELECT e.first_name, e.last_name, j.job_title, d.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id
JOIN locations l ON d.location_id = l.location_id
WHERE [Link] = 'London';
4. Write a query to find the employee id, name (last_name) along with their
manager_id and name (last_name)
SELECT e1.employee_id, e1.last_name, e1.manager_id, e2.last_name AS manager_last_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
5. Write a query to find the name (first_name, last_name) and hire date of the
employees who was hired after 'Jones'
SELECT e1.first_name, e1.last_name, e1.hire_date
FROM employees e1
JOIN employees e2 ON e1.hire_date > e2.hire_date
WHERE e2.last_name = 'Jones';
6. Write a query to get the department name and number of employees in the
department
SELECT d.department_name, COUNT(e.employee_id) AS num_employees
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
7. Write a query to display department name, name (first_name, last_name),
hire date, salary of the manager for all managers whose experience is more
than 15 years
SELECT d.department_name, e.first_name, e.last_name, e.hire_date, [Link]
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id
WHERE (YEAR(CURDATE()) - YEAR(e.hire_date)) > 15;
8. Write a query to find the name (first_name, last_name) and the salary of the
employees who have a higher salary than the employee whose
last_name='Bull'
SELECT e1.first_name, e1.last_name, [Link]
FROM employees e1
JOIN employees e2 ON [Link] > [Link]
WHERE e2.last_name = 'Bull';
9. Write a query to find the name (first_name, last_name) of all employees
who works 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. Write a query to find the name (first_name, last_name) of the employees
who have a manager and worked in a USA based department
SELECT e.first_name, e.last_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id
WHERE e.manager_id IS NOT NULL AND c.country_name = 'United States of America';
11. Write a query to find the name (first_name, last_name), and salary of the
employees whose salary is greater than the average salary
SELECT e.first_name, e.last_name, [Link]
FROM employees e
WHERE [Link] > (SELECT AVG(salary) FROM employees);
12. Write a query to find the name (first_name, last_name), and salary of the
employees whose salary is equal to the minimum salary for their job grade
SELECT e.first_name, e.last_name, [Link]
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
WHERE [Link] = j.min_salary;
13. Write a query to find the name (first_name, last_name), and salary of the
employees who earns more than the average salary and works in any of the IT
departments
SELECT e.first_name, e.last_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE [Link] > (SELECT AVG(salary) FROM employees) AND d.department_name = 'IT';
14. Write a query to find the name (first_name, last_name), and salary of the
employees who earn the same salary as the minimum salary for all
departments.
SELECT e.first_name, e.last_name, [Link]
FROM employees e
WHERE [Link] = (SELECT MIN(salary) FROM employees);
15. Write a query to find the name (first_name, last_name) and salary of the
employees who earn a salary that is higher than the salary of all the Shipping
Clerk (JOB_ID = 'SH_CLERK'). Sort the results of the salary of the lowest to
highest
SELECT e.first_name, e.last_name, [Link]
FROM employees e
WHERE [Link] > (SELECT MAX(salary) FROM employees WHERE job_id = 'SH_CLERK')
ORDER BY [Link];
FROM employees e
JOIN departments d ON e.department_id = d.department_id;ECT [Link] e.first_name, e.last_name,
e.departmenirst_name, e.last_name, e.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;LECT 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;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;