Sql Practice Questions

Only available on StudyMode
  • Topic: Name, .jobs, SQL
  • Pages : 7 (1077 words )
  • Download(s) : 158
  • Published : June 1, 2013
Open Document
Text Preview
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 branch whose stree adress is ‘163 Main Street’

SELECT staffNo, fName, lName,
FROM Staff
WHERE _branchNo________ = (SELECT branchNo
FROM _branch__________
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
FROM Staff
WHERE _salary > ALL__________(SELECT salary
FROM __staff_______________
WHERE brancNo=’B003’)

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

(SELECT city
FROM Branch)
___INTERSECT________
(SELECT city
FROM _PropertyforRent__)

12. Give all managers 5% increase to their salary

UPDATE __staff_____________
SET __salary=salary*1.05
WHERE position=’Manager’

13. Delete all viewings that belong to property with property number PG4.

DELETE FROM __viewing__________
WHERE _propertyNo=’P64’__

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...
tracking img