Consider the following schema definitions:
Branch (branchNo, street, city, postcode)
Staff (staffNo, fName,lName, position, sex, DOB, salary, branchNo) PropertyforRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
Client (clientNo, fName, lName, telNo, prefType, maxRent)
PrivateOwner (ownerNo, fName, lName, address, telNo)
Viewing (clientNo, propertyNo, viewDate, comment)
Registration (clientNo, branchNo, staffNo, dateJoined)
An instance of the above schemas is given in the last page of the examination. (You may detach and use it if necessary)
For each case below, fill in the blanks such that the SQL queries correspond to the English language queries stated. Each blank is worth 2 points.
1. List the address of all branch offices in London or Bristol.
WHERE city=’London’ _OR city=’bristol’______
2. List the staff with a salary between $10000 and $30000.
WHERE __salary between 10000 AND 30000________________
3. List the staff in descending order of salary.
SELECT staff_No, salary
ORDER BY __salary DESC__________________
4. Find the number of different properties viewed in April 2004.
SELECT __count (distinct propert_no)
WHERE viewDate BETWEEN ‘1-Apr-04’ AND ’30-Apr-04’
5. Find the minimum, maximum and average staff salary.
SELECT _min(salary)____, _max(salary)_, _avg(salary)_____ FROM Staff
6. For each branch office with more than one member of staff, find the number of staff working in each branch and the sum of their salaries.
SELECT branchNo, _count(staffno)_, __sum(salary)___
GROUP BY branchNo
HAVING __count(staffNo) >1
7. List the staff who work in the branch whose stree adress is ‘163 Main Street’
SELECT staffNo, fName, lName,
WHERE _branchNo________ = (SELECT branchNo
WHERE _street=’163 Main str’_)
8. Find all staff whose salary is larger than the salary of every staff member at branch with branchNo B003. SELECT staffNo, fName, lName, position, salary
WHERE _salary > ALL__________(SELECT salary
9. For each branch, list the numbers and names of staff who manage properties, including the city in which the branch is located and the properties that the staff manage.
SELECT b.branchNo, b.city, s.staffNo, fName, lName, properyNo FROM Branch AS b, Staff AS s, _propertyforRent p
WHERE b.branchNo = s.branchNo AND _s.staffNo=p.staffno
10. List the clients who have viewed a property.
SELECT clientNo, fName, lName, propertyNo, viewDate
FROM __client natural innerjoin viewing_____
11. Find the list of all cities where there is both a branch office and a property
12. Give all managers 5% increase to their salary
13. Delete all viewings that belong to property with property number PG4.
DELETE FROM __viewing__________
A- (21 points) Given the following schema definitions:
message (message_id, subject, body)
sent_to(message_id, email, senddate)
customer (email, name, family_size, address)
For each case below, fill in the blanks such that the SQL queries correspond to the English language queries stated.
1- Find the names and emails of all customers who have sent the message with subject “Happy...