Relational and SQL Exercises

(From "Database Management Systems" by Ramakrishnan and Gehrke)

1. Consider the following relations containing airline flight information:

    Flight(flno: integer, from: strong, to: string, distance: integer, departs: time, arrives: time)

    Aircraft(aid: integer, aname: string, cruiserange: integer)

    Certified(eid: integer, aid: integer)

    Employees(eid: integer, ename: string, salary: integer)

Note that the Employees relation describes pilots and other kinds of employees as well; every pilot is certified for some aircraft (otherwise, he/she is not qualify as pilot), and only pilots are certified to fly.

Write the following queries in relational algebra and SQL. Note that some queries might not be expressible in relational algebra and/or SQL. For such queries, informally explain why they cannot be expressed.

a. Find the eids of pilots certified for some Boeing aircraft.

b. Find the names of pilots certified for some Boeing aircraft.

c. Find the aids of all aircraft that can be used on non-stop flights from Bonn to Madras.

d. Identify the flights that can be piloted by every pilot whose salary is more than $100,000.00

e. Find the names of pilots who can operate planes with a range greater than 3,000 miles but are not certified on any Boeing aircraft.

f. Find the eids of employees who makes the highest salary.

g. Find the eids of employees who makes the secondhighest salary.

h. Find the eids of employees who are certified for the largest number of aircraft.

i. Find the eids of employees who are certified for exactly three aircraft.

j. Find the total amount paid to employees as salaries.

k. Is there a sequence of flights from Madison to Timbuktu? Each flight in the sequence is required to depart from the city that is the destination of the previous flight; the first flight must leave Madison; the last flight must reach Timbuktu; and there is no restriction on the number of intermediate flights. Your query must determine whether a sequence of flights from Madison to Timbuktu  exists for any input Flights relation instance.

Write the following queries in SQL:

a. Find the names of aircraft such that all pilots certified to operate them earn more than $80,000.

b. For each pilot who is certified for more than three aircraft, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.

c. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

d. For all aircraft with cruisingrange over 1000 miles, find the name of the aircraft and the average salary of all pilots certified for this aircraft.

e. Find the names of pilots certified for some Boeing aircraft.

f. Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.

g. Identify the routes that can be piloted by every pilot who makes more than $100,000.

h. Print the enames of pilots who can operate planes with cruisingrange greater than 3000 miles but are not certified on any Boeing aircraft.

i. A customer wants to travel from Madison to New York with no more than two changes of flights. List the choice of departure times from Madison if the customer wants to arrive in New York by 6 p.m.

j. Compute the difference between the average salary of a pilot and the average salary of all employees.

k. Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.

l. Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles.

m. Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles but on at least two such aircrafts.

n. Print the names of employees who are certified only on aircrafts with cruising range longer than 1000 miles and who are certified on some Boeing aircraft.

Want to do it on the computer? Here is a script with sample data for this exercise.

2. Consider the following schema:

    Student(snum: integer, sname: string, major: string, level: string, age: integer)

    Classes(name: string, meets_at: time, room: string, fid: integer) [fid refers to fid of Faculty]

    Enrolled(snum: integer, cname: string)     [cname refers to name of Classes]

    Faculty(fid: integer, fname: string, deptid: integer)

Keys are underlined.

a. Write SQL statements to create these relations, including the appropriate primary and foreign key integrity constraints.

b. Express each of the following integrity constrains in SQL:

    - Every class has a minimum enrollment of 5 students and a maximum enrollment of 30 students

    - Every faculty member must teach at least two courses

    - Only faculty in the department with deptid=33 teach more than 3 courses