Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 67 of 79

Database Query using SQL — Question 25

Back to all questions
25
Question

Question 25

Write SQL commands and the output for the following queries:

Table: SPORTS

StudentNoClassNameGame1Grade1Game2Grade2
107SameerCricketBSwimmingA
118SujitTennisASkatingC
127KamalSwimmingBFootballB
137VeenaTennisCTennisA
149ArchanaBasketballACricketA
1510ArpitCricketAAthleticsC

(a) Display the names of the students who have grade 'A' in either Game1 or Game2 or both.

(b) Display the number of students having game 'Cricket'.

(c) Display the names of students who have the same game for both Game1 and Game2.

(d) Display the games taken by the students whose name starts with 'A'.

(e) Give the output of the following sql statements:

  1. SELECT COUNT(*) FROM SPORTS;
  2. SELECT DISTINCT CLASS FROM SPORTS;
  3. SELECT MAX(Class) FROM SPORTS;
  4. SELECT COUNT(*) FROM SPORTS GROUP BY Game1;
Answer

(a)

SELECT NAME FROM SPORTS 
WHERE GRADE1 = 'A' OR GRADE2 = 'A';
Output
+---------+
| NAME    |
+---------+
| SAMEER  |
| SUJIT   |
| VEENA   |
| ARCHANA |
| ARPIT   |
+---------+

(b)

SELECT COUNT(*) FROM SPORTS 
WHERE GAME1 = 'CRICKET' OR GAME2 = 'CRICKET' ;
Output
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

(c)

SELECT NAME FROM SPORTS 
WHERE GAME1 = GAME2;
Output
+-------+
| NAME  |
+-------+
| VEENA |
+-------+

(d)

SELECT NAME, GAME1, GAME2 
FROM SPORTS 
WHERE NAME LIKE 'A%';
Output
+---------+------------+-----------+
| NAME    | GAME1      | GAME2     |
+---------+------------+-----------+
| ARCHANA | BASKETBALL | CRICKET   |
| ARPIT   | CRICKET    | ATHLETICS |
+---------+------------+-----------+

(e)

1. SELECT COUNT(*) FROM SPORTS;

Output
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

2. SELECT DISTINCT CLASS FROM SPORTS;

Output
+-------+
| CLASS |
+-------+
|     7 |
|     8 |
|     9 |
|    10 |
+-------+

3. SELECT MAX(Class) FROM SPORTS;

Output
+------------+
| MAX(Class) |
+------------+
|         10 |
+------------+

4. SELECT COUNT(*) FROM SPORTS GROUP BY Game1;

Output
+----------+
| COUNT(*) |
+----------+
|        2 |
|        2 |
|        1 |
|        1 |
+----------+