Answer the following questions:
Students
| Adm No | Name | Class | Sec | R No. | Address | Phone |
| 1271 | Utkarsh Madam | 12 | C | 1 | C-32, Punjabi Bagh | 4356154 |
| 1324 | Naresh Sharma | 10 | A | 1 | 31, Mohan Nagar | 435654 |
| 1325 | Md. Yusuf | 10 | A | 2 | 12/12, Chand Nagar | 145654 |
| 1328 | Sumedha | 10 | B | 23 | 59, Moti Nagar | 4135654 |
| 1364 | Subya Akhtar | 11 | B | 13 | 12, Janak Puri | Null |
| 1434 | Varuna | 12 | B | 21 | 69, Rohini | Null |
| 1461 | David DSouza | 11 | B | 1 | D-34, Model Town | 243554. 98787665 |
| 2324 | Satinder Singh | 12 | C | 1 | 1/2, Gulmohar Park | 143654 |
| 2328 | Peter Jones | 10 | A | 18 | 21/328, Vishal Enclave | 24356154 |
| 2371 | Mohini Mehta | 11 | C | 12 | 37, Raja Garden | 435654, 6765787 |
Sports
| Adm No | Game | CoachName | Grade |
| 1324 | Cricket | Narendra | A |
| 1326 | Volleball | M.P. Singh | A |
| 1271 | Volleball | M.P. Singh | B |
| 1434 | Basket Ball | I. Malhotra | B |
| 1461 | Cricket | Narendra | B |
| 2328 | Basket Ball | I. Malhotra | A |
| 2371 | Basket Ball | I. Malhotra | A |
| 1271 | Basket Ball | I. Malhotra | A |
| 1434 | Cricket | Narendra | A |
| 2328 | Cricket | Narendra | B |
| 1364 | Basket Ball | I. Malhotra | B |
(a) Based on these tables write SQL statements for the following queries:
(i) Display the lowest and the highest classes from the table STUDENTS.
(ii) Display the number of students in each class from the table STUDENTS.
(iii) Display the number of students in class 10.
(iv) Display details of the students of Cricket team.
(v) Display the Admission number, name, class, section, and roll number of the students whose grade in Sports table is 'A'.
(vi) Display the name and phone number of the students of class 12 who are play some game.
(vii) Display the Number of students with each coach.
(viii) Display the names and phone numbers of the students whose grade is 'A' and whose coach is Narendra.
(b) Identify the Foreign Keys (if any) of these tables. Justify your choices.
(c) Predict the output of each of the following SQL statements, and then verify the output by actually entering these statements:
(i) SELECT class, sec, count(*) FROM students GROUP BY class, sec;
(ii) SELECT Game, COUNT(*) FROM Sports GROUP BY Game;]
(iii) SELECT Game FROM students, Sports WHERE students.admno = sports.admno AND Students.AdmNo = 1434;
(a) (i) SELECT MIN(CLASS),MAX(CLASS) from student;
(ii) SELECT Class, count(*) from Student group by students;
(iii) SELECT count(*) from students where class=10;
(iv) SELECT Students.* from Students A, Sports B Where A.Admno=B.Admno and Game="Cricket ";
(v) SELECT A.Admno,name,class,section,rno from Students A, Sports B Where A.Admno=B.Admno and grade=’A ’;
(vi) SELECT name, phone from from Students A, Sports B Where A.Admno=B.Admno and class=12;
(vii) Select coachname, count(*) from sports group by coachname;
(viii) SELECT NAME, phone from from Students A, Sports B where A.Admno=B.Admno and coachname=’Narendra ’and grade=’A ’;
(b) Foreign Key –Sports, because it is duplicating in the table.
(c) (i)]
| Class | Sec | Count(*) |
|
10 10 11 11 12 12 |
A B B C B C |
3 1 2 1 1 2 |
6 rows in set (0.08 sec)
(ii)
| Class | Count(*) |
|
Basket Ball Cricket Volleball |
4 4 2 |
3 rows in set (0.03 sec)
(iii)
| Game | Name | Address |
|
Cricket Volleball Basket Ball Basket Ball Cricket |
Naresh Sharma Subya Akhtar Peter Jones Mohini Mehta Varuna |
31, Mohan Nagar 12, Janak Puri 21/32B, Vishali Enclave 37, Raja Garden 69, Rohini |
