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
   216   217   218   219   220   221   222   223   224   225   226