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)