Description

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 (10%)

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 (10%)

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 (10%)

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 (10%)

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 (10%)

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 (10%)

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 (10%)

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: