Introduction
This assignment consists of ten exercises involving the use of SQL queries which are a little more complex than those in Assignment 1. Each exercise is worth 10%. Marks may be awarded for attempts which are not wholly correct. Even if you are not sure of a solution it is better to write something than nothing.
When doing assessed work, staff will not be able to give you the answer to the question.
They will however assist you if you are stuck with a question (perhaps by pointing you
towards some reading) so please ask if there is something you do not understand.
Use Workbench to develop your queries and store this work in a single SQL script file (please do not submit 10 separate files). This file must be submitted through Blackboard. Please note that material sent via email will not be marked.
Remember to read the submission notes at the end of the lab script.
Exercise 1: Branches and Number of Staff
List full branch details of branches which have more than one member of staff. Your result table should resemble the following:
Hint: this is a multi-table query.
Exercise 2: Client Details
Get a listing of all clients who have registered with DreamHome since April 1st, 2007. Your result table should look like the following:
Exercise 3: Property Pairs
DreamHome have been approached by a wealthy businessman who wishes to rent two properties in the same city. The city can be anywhere in the UK. Generate details of all such possible pairings. You should get the following:
Note: that this is a more challenging question. If you struggle then move on to the next question and come back to it later.
Exercise 4: London Staff Report
Get a list of London based staff who have registered one or more clients. Your query should generate the following results table:
Exercise 5: Hard to Rent Property List
Get a list of properties which have, as yet, not been viewed or have a rent greater than £600. Order the list by ascending property number. You must use the UNION set operator in your solution. Your query should generate the following results table:
Exercise 6: Rental Survey
Find all rental properties where the rent is greater than the rent of every property registered in branch B003. Your query should generate the following results table:
Exercise 7: Salary Statistics
List staff members and salaries where the salary is less than or equal to the average salary increased by 50% but also greater than or equal to the average salary decreased by 50%. Your query should generate the following results table:
Exercise 8: Multiple Salary Totals
Develop a query to display the following result table:
The column salary total is a little complicated. First a sub-total is computed for each gender of employee in each branch, then a total salary bill for the branch is computed and finally a grand total of all salaries is computed. So on the first row the figure of £36000 represents the total salary bill for female employees based in branch B003. On the third row is the salary bill for all employees in branch B003. The last row is grand total.
To complete this exercise you will need to research the documentation relating to SQL functions and operators.
Exercise 9: Staff at Branch B005 with Clients
Get the name and staff number of any members of staff who are based in branch B005 and who have one or more registered clients. This task must be completed by means of a nested query.
Exercise 10: Staff managing properties
Get the name and staff number of all staff along with the property number and address of properties they manage, where the property is a house. Include members of staff who do not manage any houses in the results.
The results should look as follows:
Comments