Question 1Pease use the below table to write SQL statements

Question:

1.Pease use the below table to write SQL statements to create table for PILOT,FLIGHT, PASSENGER,RESERVATION and AIRPLANE.

2. Write a SQL statement to create the RESERVATION Make sure you apply the primary key constraint and two foreign key constraints.

3. Write a SQL statement to remove the RESERVATION table and its data.

4.Write a query to list the numbers and names of those pilots who were born after June 1, 1975.

5. Andrea Forman, whose PilotNumber is 98765, changed her last name to “Johnson”. Write a SQL statement to change her last name.

6. Write a query to list all reservations (all fields) for flight 278 on February 21, 2016.

7. Write a query to list flight number, pilot number for all flights whose destinations are GSO, CLT and MEM using the IN keyword.

8. Write a query to list the AirplaneNumber and PassengerCapacity of all airplanes with their Model values starting with a letter \"P\" or \"M\".

9. Write one query to list the names and telephone numbers of the passengers who have reservations on the flight with FlightNumber 562 on January 15, 2016.

10. How many of each model of Boeing aircraft does Grand Travel have? Write a query to list models and their corresponding numbers.

11.Write a query to list total fair paid by passenger, first name, last name for all passengers in descending order of last name and first name.

Solution

6)Write a query to list all reservations (all fields) for flight 278 on February 21, 2016.

SELECT * FROM RESERVATION
WHERE FLIGHTNUM =278 AND TO_CHAR(DATE,\'DD\')=21 AND TO_CHAR(DATE,\'YY\')=2016 AND TO_CHAR(DATE,\'MON\')=\'FEB\';

7. Write a query to list flight number, pilot number for all flights whose destinations are GSO, CLT and MEM using the IN keyword.

SOL:SELECT FLIGHTNUM,PILOTNUM FROM FLIGHT WHERE DESTINATION IN (\'GSO\',\'CLT\',\'MEM\');

3. Write a SQL statement to remove the RESERVATION table and its data

SOL: DROP TABLE RESERVATION ; COMMENTS// FOR REMOVING TABLE AND ITS DATA