Page 223 - Informatics_Practices_Fliipbook_Class12
P. 223
Ans. +------------------+-----------+
| Subject_Name | Dept_Name |
+------------------+-----------+
| English | Arts |
| Chemistry | Science |
| Physics | Science |
| Business Studies | Commerce |
| Economics | Arts |
| Accounts | Commerce |
+------------------+-----------+
c. SELECT Student_Name, Subject_Name
FROM SUBJECT AS J, STUDENT AS S
WHERE J.Subject_No = S.Subject_No;
Ans. +-----------------+------------------+
| Student_Name | Subject_Name |
+-----------------+------------------+
| Bhaskar Dhyani | English |
| Dakshinesh | Chemistry |
| Saumit Raj | English |
| Lovepreet Singh | Chemistry |
| Aditya Jayant | Business Studies |
+-----------------+------------------+
Case-based Questions
1. Kirti studies in class 11 and is a sports enthusiast. To promote co-curricular activities and sports, her school conducts
various competitions and events throughout the year. There are five houses, whose names have been inspired from the
rivers of India - Ganga, Jamuna, Saraswati, Narmada, and Jhelum. Each student is allocated one house. In the upcoming
Annual Athletic Meet in her school, various inter-house sports events will be held and she will be representing her
house-Saraswati. Kirti's favourite subject is Computer Science and as she has just studied databases, she wants to maintain
data of all athletes of her school in a table named Athletes. In another table, named House, she wants to store the
house details. She has created both the tables and has inserted records as shown below:
Table: Athletes
AthleteId Name Gender Category Event1 Event2 HouseId
A002 Anushka Girl Sub Junior 100m H01
A003 Prakhar Boy Senior High Jump Boys 200m H02
A004 Piyush Boy Senior Relay 100m H02
A005 Nyasa Girl Junior Long Jump Relay H03
A007 Hamdaan Boy Sub Junior 100m 400m H01
A008 Gurpurab Boy Junior Long Jump Boys H04
A009 Manasvi Girl Junior Relay H05
Table : House
HouseID HouseName HouseMaster HouseCaptain
H01 Ganga Manju Aahana
H02 Saraswati Onkar Kabir
H03 Jamuna Kusum Manasa
H04 Narmada Sunil Prabhjot
H05 Jhelum Pratul Soumya
Help Kirti to write the queries to perform the following tasks:
(i) To display Athlete name and House name for all athletes.
(ii) To display the house name and house master's name for all athletes of category, Seniors.
(iii) To count the number of athletes for each category.
SQL: Working with Two Tables 209

