Question

Consider the tables'Flights' & 'Fares' given below:

            **Table: FLIGHTS**

| FLCODE | SOURCE | DESTINATION | NO_STOPS | NO_FLIGHTS |
| IC3O1 | Mumbai | Bangalore | 3 | 2 |
| IC799 | Bangalore | Kolkata | 8 | 3 |
| MC101 | Delhi | Varanasi | 6 | 0 |
| IC3O2 | Mumbai | Kochi | 1 | 4 |
| AM812 | Lucknow | Delhi | 4 | 0 |
| MU499 | Delhi | Chennai | 3 | 3 |

Table: FARES

| FNO | AIRLINES | FARE | TAX |
| IC301 | Indian Airlines | 9425 | 5 |
| 1C799 | Spice Jet | 8846 | 10 |
| MC101 | Deccan Airlines | 4210 | 7 |
| IC302 | Jet Airways | 13894 | 5 |
| AM1812 | Indian Airlines | 4500 | 6 |
| MU499 | Sahara | 12000 | 4 |

With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii) below:

(i) To display flight number source, airlines of those flights where fare is less than Rs. 10000.

(ii) To count total no of Indian Airlines flights starting from various cities.

(iii) SELECT FLIGFITS.FNO, NO_OF_FL, AIRLINES FROM FLIGHTS,FARES WHERE FLIGHTS.

FNO = FARES.FNO AND SOURCE='DELHI';

Answer 1

(i) select flights.fno,source,airlines from flights,fares where flight.fno=fares.fno and fare < 10000;

(ii) select sum(no_of_fl) from flights,fares where flights.fno=fares.fno group by source having Airlines ='Indian Airlines';

(iii) MC101 6 Deccan Airlines MU499 3 Sahara