Fill in the blanks
1. Consider the database formed by the following tables: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress)

1) List full details of all hotels. SELECT ______________ FROM hotel;

2) List full details of all hotels in London. SELECT * FROM hotel WHERE address ______________;

3) List the names and addresses of all guests in London, alphabetically ordered by name. SELECT ______________, address FROM guest WHERE address LIKE ‘%London’ ______________;

4) List all double or family rooms with a price below 40.00 per night, in ascending order of price. SELECT * FROM room WHERE ______________ AND ______________ ______________;

5) List the bookings for which no date_to has been specified. SELECT * FROM booking WHERE dateTo ______________;

6) How many hotels are there? SELECT ______________FROM hotel;

7) What is the average price of a room? SELECT ______________FROM room;

8) What is the total revenue per night from all double rooms? SELECT ______________FROM room WHERE ______________;

2. 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)

For each case below, fill in the blanks such that the SQL queries correspond to the English language queries stated.

1. List the address of all branch offices in London or Bristol. SELECT ______________ FROM ______________ WHERE city=’London’ ______________

2. List the staff with a salary between $10000 and $30000. SELECT staff_No FROM Staff WHERE ______________

3. List the staff in descending order of salary. SELECT staff_No, salary FROM Staff ORDER BY ______________

4. Find the number of different properties viewed in April 2004. SELECT ______________ FROM Viewing WHERE viewDate BETWEEN ‘1-Apr-04’ AND ’30-Apr-04’

5. Find the minimum, maximum and average staff salary. SELECT ______________, ______________, ______________ 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, ______________, ______________ FROM Staff GROUP BY branchNo HAVING ______________ >1

9. Give all managers 5% increase to their salary UPDATE ______________ SET ______________ WHERE position=’Manager’

10. Delete all viewings that belong to property with property number PG4. DELETE FROM ______________ WHERE ______________

[pic]

a) Select the title of all movies. SELECT ______________FROM ______________;

b) Show all the distinct ratings in the database. SELECT ______________Rating FROM Movies;

c) Show all unrated movies. SELECT * FROM Movies WHERE Rating ______________;

d) Select all movie theaters that are not currently showing a movie. SELECT * FROM MovieTheaters WHERE ______________;

e) Add the unrated movie "One, Two, Three". INSERT INTO ______________VALUES ______________;

f) Set the rating of all unrated movies to "G". ______________ Movies SET Rating="G" WHERE ______________;

Trace the Output
1. Assume that you have the tables below; show the result of the following queries.
[pic]

1. SELECT Employees.Name, LastName FROM Employees INNER JOIN Departments ON Employees.Department = Departments.Code AND Departments.Budget > 60000;

2. SELECT Name, LastName FROM Employees WHERE Department IN (SELECT Code FROM Departments WHERE Budget > 60000);

3. SELECT * FROM Departments WHERE Budget > ( SELECT AVG(Budget) FROM Departments);

4. SELECT Name FROM Departments WHERE Code IN ( SELECT Department FROM Employees GROUP BY Department HAVING COUNT(*) > 2);

5. SELECT Departments.Name FROM Employees INNER JOIN Departments ON Department = Code GROUP BY Departments.Name HAVING COUNT(*) > 2;

2. Assume that you have the tables below; show the result of the following queries.
[pic]
a) SELECT Course, Title, Count(*), Sum(Enrolled) FROM Schedule WHERE Days LIKE ‘%M%’ GROUP BY Course, Title HAVING Count(*) >=3;

b) SELECT Instructor, Sum(Enrolled) FROM Schedule WHERE Instructor NOT IN (SELECT Instructor FROM Schedule WHERE Days LIKE ‘%M%’) GROUP BY Instructor;

3. Assume that you have the tables below; show the result of the following queries.

|Table name: Employee |Table name: Owner |
|Initials |OwnerID |
|Name |Name |
|CellPhone |Email |
|ExperienceLevel |Type |
| | |
|DRM |1 |
|Dale R. Murray |Mary Jone |
|(206) 254-3456 |Mary@somewhere.com |
|Junior |Individual |
| | |
|JHE |2 |
|John H. Evanston |DT Enterprises |
|(206) 254-2345 |DTE@somewhere.com |
|Senior |Corporation |
| | |
|NSJ |3 |
|Nancy S. Johnson |Sam Douglas |
|(312) 678-7354 |Sam@somewhere.com |
|Junior |Individual |
| | |
|SJS | |
|Sam J. Smith | |
|(206) 254-1234 | |
|Master | |
| | |
| | |
|Table name: Property |Table name: Service |
|PropertyID |PropertyID |
|Street |Initials |
|City |Date |
|State |HoursWorked |
|Zip | |
|OwnerID |1 |
| |NSJ |
|1 |2/24/2002 |
|One Lake |5 |
|Chicago | |
|IL |1 |
|43001 |NSJ |
|2 |2/25/2002 |
| |7 |
|2 | |
|Two Lake |1 |
|Chicago |NSJ |
|IL |3/3/2002 |
|43003 |2 |
|2 | |
| |2 |
|3 |NSJ |
|12 Elm Street |6/6/2002 |
|Flint |5 |
|MI | |
|32009 |4 |
|1 |NSJ |
| |7/7/2002 |
|4 |7 |
|2700 Lafayette | |
|Chicago |6 |
|IL |SJS |
|43002 |5/5/2002 |
|2 |3 |
| | |
|5 |7 |
|7 Mt View |DRM |
|Denver |5/7/2002 |
|CO |5 |
|80210 | |
|3 |8 |
| |JHE |
|6 |5/17/2002 |
|123 Eastlake |11 |
|Seattle | |
|WA | |
|98119 | |
|1 | |
| | |
|7 | |
|65 32nd Avenue | |
|Rochester | |
|NY | |
|14606 | |
|2 | |
| | |
|8 | |
|4 East Elm | |
|Lynwood | |
|WA | |
|98223 | |
|3 | |
| | |

1. SELECT Name, CellPhone FROM EMPLOYEE WHERE ExperienceLevel = 'Master' AND Name LIKE 'J%';

2. SELECT Name FROM EMPLOYEE, SERVICE, PROPERTY WHERE EMPLOYEE.Initials = SERVICE.Initials AND SERVICE.PropertyID = PROPERTY.PropertyID AND State = 'NY';

3. SELECT Name FROM EMPLOYEE WHERE Initials IN (SELECT Initials FROM SERVICE WHERE PropertyID IN (SELECT PropertyID FROM PROPERTY WHERE OwnerID IN (SELECT OwnerID FROM OWNER WHERE Type = 'Corporation')));

4. SELECT ExperienceLevel, Sum (HoursWorked) FROM EMPLOYEE, SERVICE WHERE EMPLOYEE.Initials = SERVICE.Initials GROUP BY ExperienceLevel ORDER BY ExperienceLevel DESC;