Computer Science By Mr. MRK
OUTSIDE DELHI(2006)
5.b) Study the following tables FLIGHTS and FARES and write SQL commands for the questions (i) to (iv) and give outputs for SQL quires (v) to(vi).
| FL_NO | STARTING | ENDING | NO |
| IC301 | MUMBAI | DELHI | 8 0 |
| C799I | BANGALORE | DELHI | 2 1 |
| MC101 | INDORE | MUMBAI | 3 0 |
| IC302 | DELHI | MUMBAI | 8 0 |
| AM812 | KANPUR | BANGLORE | 3 1 |
| IC899 | MUMBAI | KOCHI | 1 4 |
| AM501 | DELHI | TRIVENDRUM | 1 5 |
| MU499 | MUMBAI | MADRAS | 3 3 |
| IC701 | DELHI | AHMEDABAD | 4 0 |
(i) Display FL_NO and NO_FLIGHTS from “KANPUR” TO “BANGALORE” from the table FLIGHTS.
Ans : Select FL_NO, NO_FLIGHTS from FLIGHTS where Starting=”KANPUR” AND ENDING=”BANGALORE”
(ii) Arrange the contents of the table FLIGHTS in the ascending order of FL_NO.
Ans. (Children, Try this as an assignment)
(iii) Display the FL_NO and fare to be paid for the flights from DELHI to MUMBAI using the tables FLIGHTS and FARES, where the fare to be paid = FARE+FARE+TAX%/100.
Ans. Select FL_NO, FARE+FARE+(TAX%/100) from FLIGHTS, FARES where Starting=”DELHI” AND Ending=”MUMBAI”
(iv) Display the minimum fare “Indian Airlines” is offering from the tables FARES.
Ans. Select min(FARE) from FARES Where AIRLINES=”Indian Airlines”
(v) Select FL_NO,NO_FLIGHTS,AIRLINES from FLIGHTS, FARES Where STARTING = “DELHI” AND FLIGHTS.FL_NO = FARES.FL_NO
Ans. FL_NO NO_FLIGHTS AIRLINES IC799 2 Indian Airlines
(vi) SELECT count (distinct ENDING) from FLIGHTS.
Ans. (Children, Try this answer as an assignment)
DELHI 2006:
5.b) Study the following tables DOCTOR and SALARY and write SQL commands for the questions (i) to (iv) and give outputs for SQL queries (v) to (vi) :