Chapter – 8 . STRUCTURED QUERY LANGUAGE

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) :