I'm using MySQL workbench Is the questions I have done correct? 1-Display the addresses of all the departments. Show locationID, street address,city,state and country name in the -- output. (Use Locations table) SELECT l.ID, L.ADDRESS, L.CITY, L.STATE, L.COUNTRY FROM Locations L, DEPARTMENTS D WHERE L.ID= D.LOCATION_ID; -- 2-Display the last name, department number and deparment name for all employees. SELECT E.last_name, E.department, D.DEPARTMENT_NAME FROM employees E,departments D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID; -- 3-Display the last name, job, department number, and department name for all employees work in -- Toronto. (Employees,Departments,Locations) SELECT e.LAST_NAME, e.JOB_ID, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.LOCATION_ID = l.LOCATION_ID AND UPPER(l.CITY) = 'TORONTO'; -- 4-Display employees' last name and employee number along with their manager's last name and manager number. -- Label the columns Employee, Emp#, Manager Mgr#, respectively. SELECT e.LAST_NAME AS "Employee", e.EMPLOYEE_ID AS "Emp#", m.LAST_NAME AS "Manager", m.EMPLOYEE_ID AS "Mgr#" FROM EMPLOYEES e, EMPLOYEES m WHERE e.MANAGER_ID = m.EMPLOYEE_ID; -- 5-Display the last name, department number,department name and region name for all employees who work in -- Europe. -- 6-What is the name of the manager for employee WINSTON (first name). SELECT m.first_name, m.last_name FROM Employees e JOIN Employees m ON e.manager_id = m.employee_number WHERE UPPER(e.first_name) = 'WINSTON'; -- 7-Display last name of employees whose manager is KING. SELECT e1.last_name FROM employees e JOIN employees m ON e1.manager_id = e2.employee_id JOIN departments d ON e1.department_id = d.department_id WHERE UPPER(e.last_name) = 'KING'; -- 8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and -- Jobs) SELECT e.last_name, e.salary, j.job_title FROM employees e, jobs j WHERE e.job_id = j.job_id and e.salary > MIN(e.salary); -- 9-Display all employees including King, who has no manager. Order the results by the employee number. SELECT e.employee_id, e.first_name, e.last_name, e.manager_id FROM employees e WHERE e.manager_id = m.employee_id AND m.employee_id IS NULL OR e.employee_id = 100 ORDER BY e.employee_id; -- 10-Display the name and hire date of any employee hired after employee Davis SELECT e.last_name, e.hire_date FROM employees e, employees d WHERE d.last_name = 'DAVIS' AND e.hire_date > d.hire_date;
I'm using MySQL workbench
Is the questions I have done correct?
1-Display the addresses of all the departments. Show locationID, street address,city,state and country name in the
-- output. (Use Locations table)
SELECT l.ID, L.ADDRESS, L.CITY, L.STATE, L.COUNTRY
FROM Locations L, DEPARTMENTS D
WHERE L.ID= D.LOCATION_ID;
-- 2-Display the last name, department number and deparment name for all employees.
SELECT E.last_name, E.department, D.DEPARTMENT_NAME
FROM employees E,departments D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 3-Display the last name, job, department number, and department name for all employees work in
-- Toronto. (Employees,Departments,Locations)
SELECT e.LAST_NAME, e.JOB_ID, e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND d.LOCATION_ID = l.LOCATION_ID
AND UPPER(l.CITY) = 'TORONTO';
-- 4-Display employees' last name and employee number along with their manager's last name and manager number.
-- Label the columns Employee, Emp#, Manager Mgr#, respectively.
SELECT e.LAST_NAME AS "Employee", e.EMPLOYEE_ID AS "Emp#", m.LAST_NAME AS "Manager", m.EMPLOYEE_ID AS "Mgr#"
FROM EMPLOYEES e, EMPLOYEES m
WHERE e.MANAGER_ID = m.EMPLOYEE_ID;
-- 5-Display the last name, department number,department name and region name for all employees who work in
-- Europe.
-- 6-What is the name of the manager for employee WINSTON (first name).
SELECT m.first_name, m.last_name
FROM Employees e
JOIN Employees m ON e.manager_id = m.employee_number
WHERE UPPER(e.first_name) = 'WINSTON';
-- 7-Display last name of employees whose manager is KING.
SELECT e1.last_name
FROM employees e
JOIN employees m ON e1.manager_id = e2.employee_id
JOIN departments d ON e1.department_id = d.department_id
WHERE UPPER(e.last_name) = 'KING';
-- 8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and
-- Jobs)
SELECT e.last_name, e.salary, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
and e.salary > MIN(e.salary);
-- 9-Display all employees including King, who has no manager. Order the results by the employee number.
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id
FROM employees e
WHERE e.manager_id = m.employee_id
AND m.employee_id IS NULL OR e.employee_id = 100
ORDER BY e.employee_id;
-- 10-Display the name and hire date of any employee hired after employee Davis
SELECT e.last_name, e.hire_date
FROM employees e, employees d
WHERE d.last_name = 'DAVIS' AND e.hire_date > d.hire_date;
Step by step
Solved in 3 steps
