(vi) SELECT A.SenderName A, B.RecName FROM Sender A, Recipient B WHERE A.SenderID=B. SenderID AND B.RecCity=’Mumbai’;
Ans: SenderName RecName
R.Jain H.Singh
S.Jha P.K.Swamy
(vii) SELECT RecName,RecAddress FROMRecipient WHERE RecCity Not IN (‘Mumbai’,Kolkata’);
Ans: RecName RecAddressS
Mahajan 116, A Vihar
S Tripati 13, B1 D, Mayur Vihar
(viii) SELECT RecID, RecName FROM Recipient WHERE SenderID = ‘MU02’ OR
SenderID = ‘ND50’;
Ans: RecID RecName
ND08 S Mahajan
ND48 S Tripathi
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).
TABLE: FLIGHTS
| FL_NO | STARTING | ENDING | NO_ FLGHTS | NO_ STOPS |
|---|---|---|---|---|
| IC301 | MUMBAI | DELHI | 8 | 0 |
| IC799 | 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 |
TABLE:FLIGHTS
| FL_NO | AIRLINES | FARE | TAX% |
|---|---|---|---|
| IC701 | INDIAN AIRLINES | 6500 | 10 |
| MU499 | SAHARA | 9400 | 5 |
| AM501 | JET AIRWAYS | 13450 | 8 |
| IC899 | INDIAN AIRLINES | 8300 | 4 |
| IC302 | INDIAN AIRLINES | 4300 | 10 |
| IC799 | INDIAN AIRLINES | 1050 | 10 |
| MC101 | DECCAN AIRLINES | 3500 | 4 |
(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 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)
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( mrkdata@yahoo.com )
