COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

(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 ( [email protected] )