Scribd
Upload
75 views

MySQL JOINS

MYSql JOINS Query Handmade

Uploaded by

Akash
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
75 views

MySQL JOINS

MYSql JOINS Query Handmade

Uploaded by

Akash
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/ 3

MySQL JOINS - Exercises, Practice, Solution

https://www.w3resource.com/mysql-exercises/join-exercises/

1. Write a query to find the addresses (location_id, street_address, city,


state_province, country_name) of all the departments.
Use NATURAL JOIN.
SELECT location_id, street_address, city, state_province, country_name FROM
locations NATURAL JOIN countries;

2.Write a query to find the name (first_name, last name), department ID and
name of all the employees

SELECT first_name, last_name, E.DEPARTMENT_ID, D.DEPARTMENT_NAME FROM emplo


yees AS E INNER JOIN departments AS 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 first_name, last_name, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, J.JOB_TIT


LE FROM employees AS E INNER JOIN departments AS D ON E.DEPARTMENT_ID = D.D
EPARTMENT_ID INNER JOIN jobs AS J ON J.JOB_ID = E.JOB_ID INNER JOIN locatio
ns AS L ON L.LOCATION_ID = D.LOCATION_ID WHERE L.CITY = 'LONDON';

4.Write a query to find the employee id, name (last_name) along with their
manager_id and name (last_name).

SELECT E.EMPLOYEE_ID, E.LAST_NAME, M.EMPLOYEE_ID, M.LAST_NAME FROM employee


s AS E INNER JOIN employees AS M ON E.MANAGER_ID = M.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 AS E1 INNER
JOIN employees AS E2 ON E2.LAST_NAME = 'Jones' WHERE E1.HIRE_DATE > E2.HIR
E_DATE;
6.Write a query to get the department name and number of employees in the
department.
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, COUNT(*) AS employeeCount FROM d
epartments AS D INNER JOIN employees AS E ON D.DEPARTMENT_ID = E.DEPARTMENT
_ID GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME;

7.Write a query to find the employee ID, job title, number of days between
ending date and starting date for all jobs in department 90.

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, (JH.END_DATE - JH.START_DATE) AS DAYS F


ROM employees AS E INNER JOIN jobs AS J ON E.JOB_ID = J.JOB_ID INNER JOIN j
ob_history AS JH ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID AND J.JOB_ID = JH.JOB_ID
WHERE JH.department_id=90;

8.Write a query to display the department ID and name and first name of
manager.
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, E.FIRST_NAME AS MANAGER_NAME FRO
M departments AS D INNER JOIN employees AS E ON D.MANAGER_ID = E.EMPLOYEE_I
D;

9. Write a query to display the department name, manager name, and city.
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, E.FIRST_NAME AS MANAGER_NAME, L.
CITY FROM departments AS D INNER JOIN employees AS E ON D.MANAGER_ID = E.EM
PLOYEE_ID INNER JOIN locations AS L ON D.LOCATION_ID = L.LOCATION_ID;

10. Write a query to display the job title and average salary of employees.
SELECT J.JOB_TITLE, AVG(E.SALARY) AS AVGSALARY FROM employees AS E INNER JO
IN jobs AS J ON E.JOB_ID = J.JOB_ID GROUP BY J.JOB_TITLE;

11. Write a query to display job title, employee name, and the difference
between salary of the employee and minimum salary for the job

SELECT J.JOB_TITLE, E.FIRST_NAME, E.SALARY - J.MIN_SALARY AS DIFFSALARY FRO


M employees AS E INNER JOIN jobs AS J ON E.JOB_ID = J.JOB_ID;

12. Write a query to display the job history that were done by any employee
who is currently drawing more than 10000 of salary.
SELECT first_name, last_name, hire_date, salary, (DATEDIFF(now(), hire_date
))/365 Experience FROM departments d JOIN employees e ON (d.manager_id = e.
employee_id) WHERE (DATEDIFF(now(), hire_date))/365>15;
13. 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 first_name, last_name, hire_date, salary, (DATEDIFF(now(), hire_date
))/365 Experience FROM departments d JOIN employees e ON (d.manager_id = e.
employee_id) WHERE (DATEDIFF(now(), hire_date))/365>15;

You might also like