Sql Practice Questions

SQL Practice Questions

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.

SELECT _______*_______ FROM ___branch______ WHERE city=’London’ _OR city=’bristol’______

2. List the staff with a salary between $10000 and $30000.

SELECT staff_No FROM Staff WHERE __salary between 10000 AND 30000________________

3. List the staff in descending order of salary.

SELECT staff_No, salary FROM Staff ORDER BY __salary DESC__________________

4. Find the number of different properties viewed in April 2004.

SELECT __count (distinct propert_no) FROM Viewing 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)___ FROM Staff GROUP BY branchNo HAVING __count(staffNo) >1

7. List the staff who work in the