Discuss the below in detail:
Review the attachment for tables:
Q1. Create a query to produce the output shown in the figure(attachment) below. The output, derived from the CHARTER and MODEL tables, is limited to February 6, 2004. (Hint: The JOIN passes through another table. Note that the "connection" between CHARTER and MODEL requires the existence of AIRCRAFT, because the CHARTER table does not contain a foreign key to MODEL. However, CHARTER does contain AC_NUMBER, a foreign key to AIRCRAFT, which contains a foreign key to MODEL.)
Q2. Modify the query above to include data from the CUSTOMER table. This time, the output is limited to charter records generated since February 9, 2004. The query results are shown in the figure below.
Q3. Modify the query in Q2 above to produce the output shown in the figure(attachment) below. The date limitation in Problem 2 applies to this problem, too. Note that this query includes data from the CREW and EMPLOYEE tables. Note: You may wonder why the date restriction seems to generate more records than it did in previous problems. Actually, the number of (CHARTER) records is the same, but there are several records that are listed twice to reflect a crew of two: a pilot and a copilot. For example, the record for the 09-Feb-2004 flight to GNV, using aircraft 2289L, required a crew consisting of a pilot (Lange) and a copilot (Lewis).
Q4. Modify the query in Q1 above to include the computed (derived) attribute "fuel per hour." (Hint: It is possible to use SQL to produce computed "attributes" that are not stored in any table. For example, the following SQL query is perfectly acceptable:
SELECT CHAR_DISTANCE, CHAR_FUEL_GALLONS/CHAR_DISTANCE
FROM CHARTER;
(The above query produces the "gallons per mile flown" value.) Use a similar technique on joined tables to produce the "gallons per hour" output shown in the figure below. (Note that 67.2 gallons/1.5 hours produces 44.8 gallons per hour.)
Attachment:- Tabular data.rar