CS-Practical-2023-24/17. SQL Query Based on Two Tables.txt at main · arunishrajput/CS-Practical-2023-24 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
SQL Query Based on Two Tables

Q. Write the create command to create the following tables with suitable constraints

    STUDENTS TABLE
    +-------+----------------+-------+-----+-----+-----------------------+-----------------+
    | AdmNo | Name           | Class | Sec | RNo | Address               | Phone           |
    +-------+----------------+-------+-----+-----+-----------------------+-----------------+
    | 1271  | Utkarsh Madaan | 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/21, Chand Nagar    | 145654          |
    | 1326  | 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/32B, Vishal Enclave| 24356154        |
    | 2371  | Mohini Mehta   | 11    | C   | 12  | 37 Raja Garden        | 435654, 6765787 |
    +-------+----------------+-------+-----+-----+-----------------------+-----------------+

    SPORTS TABLE
    +-------+-------------+--------------+-------+
    | AdmNo | Game        | CoachName    | Grade |
    +-------+-------------+--------------+-------+
    | 1324  | Cricket     | Narendra     | A     |
    | 1364  | 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     | A     |
    | 1364  | Basket Ball | I . Malhotra | B     |
    +-------+-------------+-------------+--------+

A.

STUDENTS TABLE:-

    CREATE TABLE Students (
        AdmNo INT PRIMARY KEY,
        Name VARCHAR(255) NOT NULL,
        Class INT NOT NULL,
        Sec CHAR(2) NOT NULL,
        RNo INT NOT NULL,
        Address VARCHAR(255),
        Phone VARCHAR(255)
    );

SPORTS TABLE:-

    CREATE TABLE Sports (
        AdmNo INT NOT NULL,
        Game VARCHAR(50) NOT NULL,
        CoachName VARCHAR(255) NOT NULL,
        Grade CHAR(2)
    );

------------------------------------------------------------------------------

Based on these tables write SQL statements for the following queries:

Q 01. Display the lowest and the highest classes for the game Cricket.
A 01. SELECT MIN(Class) AS LowestClass, MAX(Class) AS HighestClass FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo AND Game = 'Cricket';

Q 02. Display the details of students in ascending order of admission number who play any game.
A 02. SELECT STUDENTS.* FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo ORDER BY STUDENTS.AdmNo ASC;

Q 03. Display the Admission number, name, class, section, and roll number of the students whose grade in the Sports table is 'A'.
A 03. SELECT STUDENTS.AdmNo, Name, Class, Sec, RNo FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo AND Grade = 'A';

Q 04. Display the name and phone number of the students of class 12 who play some game.
A 04. SELECT DISTINCT Name, Phone FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo AND Class = 12;

Q 05. Display details of the students of the Cricket team.
A 05. SELECT STUDENTS.* FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo AND Game = 'Cricket';

Q 06. Display the names and phone numbers of the students whose grade is 'A' and whose coach is Narendra.
A 06. SELECT Name, Phone FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo AND Grade = 'A' AND CoachName = 'Narendra';

Q 07. Display the number of students in each class who play any game.
A 07. SELECT Class, COUNT(DISTINCT Name) AS NumberOfStudents FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo GROUP BY Class;

Q 08. Display the number of students in class 10 who have grade 'B' in sports.
A 08. SELECT COUNT(Name) AS NumberOfStudents FROM STUDENTS, SPORTS WHERE STUDENTS.AdmNo = SPORTS.AdmNo AND Class = 10 AND Grade = 'B';

Q 09. Display the Number of students with each coach.
A 09. SELECT CoachName, COUNT(AdmNo) AS NumberOfStudents FROM SPORTS GROUP BY CoachName;

Q 10. Display the number of students for each game.
A 10. SELECT Game, COUNT(AdmNo) AS NumberOfStudents FROM SPORTS GROUP BY Game;