Page 221 - Informatics_Practices_Fliipbook_Class12
P. 221
SUBJECT
Subject_No Subject_Name Dept_No
1 English 1
2 Chemistry 3
3 Physics 3
4 Business Studies 2
5 Economics 1
6 Accounts 2
7 Mathematics 3
8 History 1
9 Geography 1
10 Computer Science 3
STUDENT
Roll_Num Subject_Name Subject_No
1 Bhaskar Dhyani 1
2 Dakshinesh 2
3 Saumit Raj 1
4 Lovepreet Singh 2
5 Aditya Jayant 4
6 Venkata Ramana 10
7 Mohd. Abdullah 3
8 Ram Prashad 1
9 Rohith P. 2
10 Manoj Kundu 8
(i) Using the tables TEACHER and SUBJECT, write a SELECT statement for each of the following:
a. For each teacher, list his/her first and last name and the name of the subject he/she teaches.
Ans. SELECT First_Name, Last_Name, Subject_Name
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No;
b. List subject number and subject name for the subjects for which teachers have been currently hired. You should
avoid duplicate rows.
Ans. SELECT DISTINCT S.Subject_No, Subject_Name
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No;
c. List the name of the subjects for which teachers have been currently hired.
Ans. SELECT DISTINCT Subject_Name
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No;
d. For each subject, list the subject number, subject name and the first and last names of teachers teaching that
subject. The output of the query should be arranged subject-wise.
Ans. SELECT DISTINCT S.Subject_No, Subject_Name, First_Name, Last_Name
FROM SUBJECT S, TEACHER T
WHERE T.Subject_No = S.Subject_No
ORDER BY S.Subject_No;
SQL: Working with Two Tables 207

