1. Assume a database with the following schema: AIRPORT(name,city,country,number

Question

1. Assume a database with the following schema: AIRPORT(name,city,country,number-runaways) FLIGHT(flightid,depart,depart-time,arrive,arrive-time, pid, price) PLANE(planeid,type,number-passangers,radius,payload,max-altitude,max-speed) where AIRPORT gives information about airports, and name is the primary key; FLIGHT gives information for direct flights from depart to arrive, both of which are foreign keys to AIRPORT (and flightid is the primary key); and PLANE gives information about planes, with planeid the primary key (pid in FLIGHT is a foreign key to PLANE).

Write the following questions in SQL:

(a) List the flightids of flights from any airport in Chicago that uses Boeing 727s (a type of plane) and cost less than $1,000.

(b) List the flightids of international flights (a flight is international if it goes from an airport in some country to another airport in a different country).

(c) List the pair of flightids for flights that go from O’Hare airport to Standiford Field airport with one stop (i.e. in two legs) and the total cost.

(d) List the types of planes that are used in flights starting from O’Hare airport or Midway airport.

(e) List the types of planes that are used in flights starting from O’Hare airport and flights starting from Midway airport.

2. Assume a database with schema ACTOR(name,age,address,nationality) MOVIE(title,year,budget,director-name,studio) APPEARS(name,title,salary) where the table ACTOR contains information about actors and actresses, and name is the primary key; MOVIE contains information about movies (their title, the year they were made, their budget, the name of the person who directed the movie and the studio who produced it) and title is the primary key; and APPEARS tells us which actor appeared in which movie (so name and title are foreign keys in APPEARS), and how much s/he was paid for acting in the movie. Note that an actor may have appeared in several movies, and a movie may have several actors appearing on it; the key of APPEARS is (name,title).

Write the following questions in SQL: 1

(a) List the names of actors that have never appeared in any movie produced by studio ’MGM’.

(b) For each director, list the number of movies (i.e. how many) with budget over $1,000,000 that s/he has directed.

(c) List the studios that produced more movies in the 90’s (1990-1999) then in the 80’s (1980-1989).

(d) The payroll of a movie is the sum of salaries of all actors in that movie. Find movies where the payroll is larger than the budget.

(e) Find the number of Australian actors on each movie but only if there are at least 5 actors (of any nationality) on that movie

Explanation / Answer

1. (a)

SELECT flight.flight_id
FROM flight
INNER JOIN airport ON flight.depart = airport.name
INNER JOIN plane ON flight.p_id = plane.plane_id

WHERE airport.city = 'Chicago'
AND
plane.type = 'Boeing 727s'
AND
flight.price<1000;

1. (b)

SELECT flight_id FROM flight
INNER JOIN airport a1 ON flight.depart = a1.name
INNER JOIN airport a2 ON flight.arrive = a2.name
WHERE a1.country!=a2.country;

1. (d)
SELECT type FROM plane
INNER JOIN flight ON plane.plane_id = flight.p_id
WHERE flight.depart = 'O’Hare' OR flight.depart = 'Midway';

1. (e) This is same as the (d) part because in (d) part we are asked to tell the type of planes used in flights that go from O'Hare or Midway.
In (e) part only the language of writing the query is changed, but it essentially means the same as (d) part.

SELECT type FROM plane
INNER JOIN flight ON plane.plane_id = flight.p_id
WHERE flight.depart = 'O’Hare' OR flight.depart = 'Midway';

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.