|
SQL DML EXERCISES
SQL DML
1. Use the following HOTEL relational schema. Click
here to download the script to run SQL queries.
Hotel(hotelNo,
hotelName, city)
Room( roomNo, hotelNo, Type, price)
Booking(hotelNo, guestNo, dateFrom, dateTo,
roomNo)
Guest(guestNo, guestName, guestAddress)
(1) List full details (all attributes) of all hotels.
(2) List full details (all attributes) of all hotels in London.
(3) List the names and addresses of all guests living in London,
alphabetically ordered by name.
(4) List all double or family rooms with a price below 40.00 per night,
in ascending order of price.
(5) List the bookings (all attributes) for which no dateTo has been specified.
2. Use the following PROPERTY relational schema. Click
here to download the script to run SQL queries.
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)
(1) List the staff (staff number or name or both) who manage properties
for rent in Glassgow.
(2) List the staff numbers of staff who currently do not manage any properties.
Hint: This query is "all staff numbers" MINUS "staff numbers of staff who manage
properties".
(3) List the names of clients who have viewed a property for rent in
Glassgow.
(4) List all the cities where there is a branch office but no
properties for rent.
(5) List all cities where there is either a branch office or a property
for rent.
3. Use the HOTEL relational schema. Click
here to download the script to run SQL queries.
(1) How many hotels are there?
(2) What is the average price of a room?
(3) What is the total revenue per night from all double rooms?
(4) How many different guests have made bookings since May 2004?
(5) List the names of all guests
who have stayed at the Grosvenor hotel.
(6) What is the total income from bookings for the Grosvenor hotel?
HARD -
SKIP FOR NOW.
(7) List the rooms that are currently unoccupied at the Grosvenor
hotel.
HARD -- SKIP FOR NOW.
(8) What is the lost income from unoccupied rooms at the Grosvenor
hotel?
HARD -- SKIP FOR NOW.
(9) For each hotel, list the hotel number and the number of rooms in that hotel. This query needs a GROUP BY.
(10) For each hotel in London, list the hotel number and the number of rooms. This query needs a GROUP
BY and a JOIN of Room and Hotel.
(11) For each hotel, what is the total
number of bookings for April 30 2004? This query needs a GROUP BY. You can
use DateFrom = April 30 or you can look for April 30 occurring in between the
DateFrom and the DateTo (more complex).
(12) For each hotel in London, and for each room type, how many bookings have
been made? This query needs a GROUP BY. More complex: What the the most
commonly booked room type for each hotel in London.
(13) What is the lost income from unoccupied rooms at each hotel today?
HARD -- SKIP FOR NOW.
4. Use the following relational schema.
WORKS_ON(ESSN,
PNUMBER, HOURS)
EMPLOYEE(SSN, LNAME, SuperSSN, DNO)
PROJECT(PNO, PNAME, DNO)
(1) Find
employees who work on projects P5 and P6.
(2) Which projects (name and no) employ more than 2 people?
(3) List the last names of employees and their supervisors.
(4) For each project, list its name and the number of Dept. 5 employees
working in the project.
(5) Select those projects on which at least two employees supervised by
Franklin Wong work.
(6) Count the number of employees who work for more than 10 hours on a
project, for those projects that employ at least 5 employees.
(7) Count the number of employees whose salaries are greater than 40K
in those departments having more than 5 employees. 5. Use the following COMPANY relational schema. Click
here to download the script to run SQL queries.
Employee(fName, lName, SSN, bDate, address, sex, salary, superSSN, dNo)
Department(dName, dNumber, mgrSSN, mgrStartDate)
Dept_locations(dNumber, dLocation)
Project(pName, pNumber, pLocation, dNum)
Works_on(eSSN, pNo, hours)
Dependent(eSSN, dependent_name, sex, bDate, relationship)
(1) Retrieve the names and addresses of all employees who work for the
Research department. JOIN.
(2) For every project located in Stafford, list the project number, the
controlling department number, and the department manager's last name and
address. JOIN MULTIPLE TABLES.
(3) Find the names of all employees who work on ANY of the projects
controlled by department 5. JOIN THREE TABLES.
(4) Make a list of project numbers for all projects that hire an employee
named Smith (last name) either as a manager of the department that controls
the project or as a worker. MULTIPLE JOINS and SUBQUERY using UNION.
(5) List the names of all employees with two or more dependents. JOIN and
GROUP BY and HAVING and AGGREGATE FUNCTIONS.
(6) List the names of employees who have dependents and the number of
dependents. JOIN and GROUP BY and AGGREGATE FUNCTIONS.
(7) Retrieve the names of employees who have NO dependents. Subqueries
using MINUS.
(8) A simpler query is to retrieve the SSN of employees who have no
dependents.
(9) List the names of managers who have at least one dependent. JOIN.
(10) List the names of employees who work on BOTH projects 1 and 2. JOIN
multiple tables. Requires a self join of works_on or an intersect. 6. Use the following relational schema. Click
here to download the script to run SQL queries.
Suppliers(SID, sName, status,
sCity)
Parts(PID, pName, color, weight, pCity)
Projects(JID, jName, city)
SPJ(SID, PID, JID, quantity)
(1) Get part numbers and names for all parts
supplied to any project in LONDON.
(2) Get supplier numbers and names for suppliers of BLUE parts to projects
in PARIS.
(3) Get part numbers and names for all parts supplied by both 10000002 and 10000005.
(4) List the total number of suppliers supplying part 10000003 for each
project.
7. Use the
following relational schema.
Employee(EMPNO,
ENAME, EADDR, ECITY, EPHONE)
Assignment(ASSNO, ANAME, ADESC, PROJNO, ASSMGRNO)
Project(PROJNO, PNAME, PADDR, PROJMGRNO)
Works(EMPNO, ASSNO, HRS, STARTDATE)
Salary(EMPNO, PROJNO, SALARY)
(1) Count
all the employees who work in assignment A18 and whose salary paid by
project P10 is more than 40000
(2) For each project, select the project NAME and NUMBER, TOTAL BUDGET
based on salary, and the MAXIMUM SALARY of any employees on that
project.
(3) Retrieve the assignment NAMES of all assignments that employ more
than five people, each of whom works more than 20 hours on that
assignment.
(4) Retrieve the assignment NAMES of all assignments that employ more
than 5 people who are paid a salary by project P10.
8. Use the
following relational schema.
Student(Stud#,
SName, SAddr, SPhone)
Assignment(Ass#, Aname, Adesc, AMgr#)
Department(Dept#, DName, DAddr, DMgr#)
Works-Assign(Stud#, Ass#, TotalHrs)
Works-Dept(Stud#, Dept#, TotalHrs)
(1) Count
the number of students who work in assignment with Ass# A18 and whose
total hours worked in department with Dept# D10 is more than 40.
(2) For each department, select the department name, number, total
number of hours worked in that department by the students, and the
maximum number of hours of any student in that department.
(3) Retrieve all assignments where the total number of hours worked by
all the students is more than 200.
(4) Retrieve those departments with at least five students who work
more than 20 hours on assignment with Ass# A18.
New Stuff:
- Use the HOTEL Schema. These
queries require GROUP BY and HAVING.
For each hotel (hotel name) list the
total number of rooms and the
average price if the total number of rooms is more than 1.
For each hotel (hotel name) list the
total number of rooms of each
room type and the average price if the average price is more than 10.
For each hotel (hotel name) list the
total number of bookings if it
is more than 2.
For each hotel (hotel name) list the
total number of bookings for each
room type if it is more than 2.
For each city list the total number
of hotels and the total number of
rooms if both values are greater than 2.
- Use the Supplier Part Project
SPJ schema. These queries require a
GROUP BY and HAVING.
(1) Get part numbers and names for
all parts supplied to at least
two projects (in LONDON).
(2) Get supplier numbers and names
for suppliers of parts to at
least two projects.
(3) Get supplier numbers and names
for suppliers of BLUE parts to
at least two projects.
(4) Get supplier numbers and names
for suppliers of the same part
to at least two projects.
(5) Get supplier numbers and names
for suppliers who ship at least
5 parts (quantity).
(6) Get supplier numbers and names
for suppliers who ship at least
5 parts to at least two projects.
|
