Guide IV
Chapters 6 8 SQL
6.1 What are the main advantages and disadvantages of SQL?
Advantages
Satisfies ideals for database language
(Relatively) Easy to learn
Disadvantages
Impedance mismatch mixing programming paradigms with embedded access
Lack of orthogonality many different ways to express some queries
6.2 Consider the following relational schema:
Staff (staffNo, name, dept, skillCode)
Skill (skillCode, description, chargeOutRate)
Project (projectNo, startDate, endDate, budget, projectManagerStaffNo)
Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn)
where: Staff contains staff details and staffNo is the key.
Skill contains descriptions of skill codes (e.g. Programmer, Analyst,
Manager, etc.) and the charge out rate per hour for that skill; the
key is skillCode.
Project contains project details and projectNo is the key.
Booking contains details of the date and the number of hours that a member of
staff worked on a project and the key is staffNo/projectNo.
Formulate the following queries using SQL:
(a) (1) List all skills with a charge out rate greater than 60 per hour, in alphabetical
order of description.
(2)
nt.
(3) For all projects that were active in July 1995, list the staff name, project
number and the date and number of hours worked on the project, ordered by
staff name, within staff name by the project number and within project number
by date.
(4)
(5) List all projects that have at least two staff booking to it.
(6) List the average charge out rate.
(7) List all staff with a charge out rate greater than the average charge out rate.
Guide IV
(b) Create a view of staff details giving the staff number, staff name, skill description, and
department, but excluding the skill number and charge out rate.
6.3 The following tables form part of a database held in a Relational Database Management System:
(a) (1) List all employees in alphabetical order of surname and within surname, first name.
(2) List all the details of employees who are female.
(3) List the names and addresses of all employees who are Managers.
Guide IV
(4)
department.
(5) Produce a complete list of all managers who are due to retire this year, in alphabetical
order of surname.
(6)
(7) Produce a report of the total hours worked by each employee, arranged in order of
department number and within department, alphabetically by employee surname.
(8) For each project on which more than two employees worked, list the project number,
project name and the number of employees who work on that project.
(9) List the total number of employees in each department for those departments with
more than 10 employees. Create an appropriate heading for the columns of the
results table.
(b)
excluding department number.
6.4 The following tables form part of a database held in a Relational Database Management System
for a printing company that handles printing jobs for book publishers:
Publisher (pubID, pubName, street, city, postcode, telNo, creditCode)
BookJob (jobID, pubID, jobDate, description, jobType)
PurchaseOrder (jobID, poID, poDate)
POItem (jobID, poID, itemID, quantity)
Item (itemID, description, onHand, price)
where Publisher contains publisher details and pubID is the key.
BookJob contains details of the printing jobs (books or part books) and jobID
is the key.
PurchaseOrder A printing job requires the use of materials, such as paper and ink,
which are assigned to a job via purchase orders. This table contains
details of the purchase orders for each job and the key is
Formulate the following queries using SQL:
Guide IV
(a) (1) List all publishers in alphabetical order of name.
(2)
(3)
(4) List the dates of all the pu
(5) How many publisher fall into each credit code category?
(6)
(7) List the average price of all items.
(8) List all items with a price below the average price of an item.
(b) Create a view of publisher details for all publisher who have a rush printing job,
excluding their credit code.
6.5 The relational schema shown below is part of a hospital database. The primary keys are
highlighted in bold.
Formulate the following SQL statements:
(1)
(2)
(3) List all the patients admitted today.
(4)
Guide IV
(5)
(6) What is the maximum, minimum and average number of beds in a ward? Create
appropriate column headings for the results table.
(7) For each ward that admitted more than 10 patients today, list the ward number, ward
type and number of beds in each ward.
(8) List the numbers and names of all patients and the drugNo and number of units of
their medication. The list should also include the details of patients that are not
prescribed medication.
5.6 A relational database contains details about journeys from Paisley to a variety of destinations and
contains the following relations:
Operator (opCode, opName)
Guide IV
Each operator is assigned a unique code (opCode) and the relation operator records the
association between this code and the operator s name (opName). Each destination has a unique
code (destinationCode) and the relation destination records the association between this code
and the destination name (destinationName), and the distance of the destination from Paisley.
The relation Journey records the price of an adult fare from Paisley to the given destination by
as specified operator, several operators may operate over the same route.
Formulate the following queries using SQL (the answers to these queries in relational algebra,
tuple relational calculus, and domain relational calculus were given in the previous section):
(a) List the details of journeys less than £100.
(b) List the names of all destinations.
(c) Find the names of all destinations within 20 miles.
(d) List the names of all operators with at least one journey priced at under £5.
(e) List the names of all operators and price
(f) List the names of all destinations that do not have any operators.
6.7 The following tables form part of a database held in a Relational Database Management System:
Employee (empNo, eName, salary, position)
Aircraft (aircraftNo, aName, aModel, flyingRange)
Flight (flightNo, from, to, flightDistance, departTime, arriveTime)
Certified (empNo, aircraftNo)
Formulate the following queries in SQL (the answers to these queries in relational algebra, tuple relational
calculus, and domain relational calculus were given in the previous section).
(1) List all Boeing aircraft.
(2) List all Boeing 737 aircraft.
(3) List the employee numbers of pilots certified for Boeing aircraft.
(4) List the names of pilots certified for Boeing aircraft.
(5) List the aircraft that can fly nonstop from Glasgow to New York (flyingRange >
flightDistance).
Guide IV
(6) List the employee numbers of employees who have the highest salary.
(7) List the employee numbers of employees who have the second highest salary.
(8) List the employee numbers of employees who are certified for exactly three aircraft.