Question
1. Write a query which displays the full name, job_id, hire date and annual salary for all employees with a Job ID that ends in
1. Write a query which displays the full name, job_id, hire date and annual salary for all employees with a Job ID that ends in REP and whose last names do not begin with G through to P. Display the data in the format shown below, capitalise the surname and name the columns as shown below. Sort the display in descending order of last name
Output:
Employee job_id hire_date Annual Salary
---------------------- ---------- ----------------------- ---------------------------------
BRETT, Kimberely SA_REP 1999-05-24 00:00:00.000 84000.00
TAYLOR, Jonathon SA_REP 1998-03-24 00:00:00.000 103200.00
FAY, Pat MK_REP 1997-08-17 00:00:00.000 72000.00
(3 row(s) affected)
2. Management would like to know how many employees work in each department. Write a query which shows the number of employees, their department names and the cities and countries in which they work. Sort the output in descending order of the number of employees
(20 marks)
Output:
No. of Employees department_name city country_name
---------------- ------------------ ---------------------- -----------------------------------
5 Shipping South San Francisco United States of America
3 Sales Oxford United Kingdom
3 Executive Seattle United States of America
3 IT Southlake United States of America
2 Marketing Toronto Canada
2 Accounting Seattle United States of America
1 Administration Seattle United States of America
0 Contracting Seattle United States of America
Warning: Null value is eliminated by an aggregate or other SET operation.
(8 row(s) affected)
3. Write a query which selects the last name, job ID and salary of all employees who are not Reps (i.e. their job ID does not contain the string REP), and who earn more than any of the Reps. Only show those employees who earn more than $6,000.
(30 marks)
Output:
last_name job_id salary
------------------------- ---------- ---------------------------------------
Lorentz IT_PROG 8200.00
Whalen AD_ASST 14400.00
(2 row(s) affected)
4. Write a query which displays the city, the department name, the number of employees and the average salary of employees in departments that have 2 or less employees. Also show the number of employees who have not been allocated to a department. Replace NULL values in the City and Department columns with Not allocated. Sort the results by number of employees, then average salary. Show average salary as an integer value.
(40 marks)
Output:
City Department No. of Employees Average Salary
------------------------------ ------------------------------ ---------------- --------------
Seattle Administration 1 4400
Not allocated Not allocated 1 7000
Toronto Marketing 2 9500
Seattle Accounting 2 10150
(4 row(s) affected)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started