86%(44)38 out of 44 people found this document helpful
This preview shows page 15 - 17 out of 28 pages.
11. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading. Practice 5: Solutions 1. Write a query for the HR department to produce the addresses of all the departments. Use the LOCATIONS and COUNTRIES tables. Show the location ID, street address, city, state or province, and country in the output. Use a NATURAL JOIN to produce the results. 2. The HR department needs a report of all employees. Write a query to display the last name, department number, and department name for all employees.
16 JOIN departments USING (department_id); 3. The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto. 4. Create a report to display employees’ last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab_05_04.sql. 5. Modify lab_05_04.sql to display all employees including King, who has no manager. Order the results by the employee number. Place your SQL statement in a text file named lab_05_05.sql. Run the query in lab_05_05.sql. 6. Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. Save the script to a file named lab_05_06.sql.