Share
Explore BrainMass

SQL queries

Consider the 3 tables with these attributes:

TABLE MECHANIC
(
LAST_NAME VARCHAR(50),
BIRTHDATE DATETIME,
PHONE_NO CHAR(10)
)

TABLE AIRPLANE
(
SERIAL_NO CHAR(12),
MODEL VARCHAR(40),
CAPACITY INT
)

TABLE WORKS_ON
(
MECHANIC_LAST_NAME VARCHAR(50),
MECHANIC_BIRTHDATE DATETIME,
AIRPLANE_SERIAL_NO CHAR(12),
CONTRACT_AMOUNT MONEY
)

Write SQL queries to answer the following questions:

(a) Find the last names, birth dates, and phone numbers of all mechanics who have earned at least $50000 from contracts on Boeing 747 airplanes.

(b) Find the average amount of money that it has taken to do maintenance on each different model of jumbo jet (defined to be any airplane model whose capacity is at least 150).

(c) Double the capacity of each DC-10 airplane.

(d) Add to the database, for each DC-9 model airplane,
a DC-10 model airplane with a capacity 50 greater than that of the DC-9 and a serial number
the same as that of the DC-9 except with an extra leading "1".

Solution Preview

Please find the required SQL queries below.

(a)
SELECT WORKS_ON.MECHANIC_LAST_NAME, WORKS_ON.MECHANIC_BIRTHDATE, MECHANIC.PHONE_NO
FROM WORKS_ON, AIRPLANE, MECHANIC
WHERE WORKS_ON.CONTRACT_AMOUNT > 50000
AND ...

Solution Summary

SQL queries that answer specified requirements such as finding the last names, birth dates, and phone numbers of all mechanics who have earned at least $50000 from contracts on Boeing 747 airplanes. Find the average amount of money that it has taken to do maintenance on each different model of jumbo jet.

$2.19