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.
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.
Get a listing of all clients who have
registered with DreamHome since April
1st, 2007. Your result table should look like the following:
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.
Get a list of London based staff who have
registered one or more clients. Your query should generate the following results
table:
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:
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:
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:
Get Free Quote!
394 Experts Online