Discuss the below:
Q: 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 response 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".