Page 227 - Informatics_Practices_Fliipbook_Class12
P. 227
2. Using the tables DEPARTMENT and SUBJECT from the STUDENT database on page no 184-185, write a SELECT
statement for each of the following:
(i) For each department, list the department name and subjects offered by that department.
(ii) For each department, list the department name and the number of subjects offered by that department.
(iii) List names of the subjects and the department number of the associated department for the subjects for which
teachers have been currently hired.
3. Write SQL queries for (a) to (d) and write outputs for SQL queries (i) and (ii) of part (e), which are based on the tables given
below: (2019)
TRAINS
TNO TNAME START END
11096 Ahimsa Express Pune Junction Ahmedabad Junction
12015 Ajmer Shatabdi New Delhi Ajmer Junction
1651 Pune Hbj Special Pune Junction Habibganj
13005 Amritsar Mail Howrah Junction Amritsar Junction
12002 Bhopal Shatabdi New Delhi Habibganj
12417 Prayag Raj Express Allahabad Junction New Delhi
14673 Shaheed Express Jaynagar Amritsar Junction
12314 Sealdah Rajdhani New Delhi Sealdah
12498 Shan-e-Punjab Amritsar Junction New Delhi
12451 Shram Shakti Express Kanpur Central New Delhi
12030 Swarna Shatabdi Amritsar Junction New Delhi
PASSENGERS
PNR TNO PNAME GENDER AGE TRAVELDATE
P001 13005 R N AGRAWAL MALE 45 2018-12-25
P002 12015 P TIWARY MALE 28 2018-11-10
P003 12015 S TIWARY FEMALE 22 2018-11-10
P004 12030 S K SAXENA MALE 42 2018-10-12
P005 12030 S SAXENA FEMALE 35 2018-10-12
P006 12030 P SAXENA FEMALE 12 2018-10-12
P007 13005 N S SINGH MALE 52 2018-05-09
P008 12030 J K SHARMA MALE 65 2018-05-09
P009 12030 R SHARMA FEMALE 58 2018-05-09
NOTE : All Dates are given in 'YYYY-MM-DD' format.
(i) To display details of all passengers travelling in train whose TNO is 12030
(ii) To count the number of FEMALE passengers travelling in train whose TNO is 12030
(iii) To display the names of the passengers along with the train names in which they are travelling.
(iv) To display the date of travel for the train, 'Ajmer Shatabdi'.
(v) Write the output of the following queries:
a. SELECT TNAME, PNAME
FROM TRAINS T, PASSENGERS P
WHERE (T.TNO = P.TNO) AND (AGE BETWEEN 40 AND 50);
SQL: Working with Two Tables 213

