Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 87 of 103

Review of Database Concepts & SQL — Question 36

Back to all questions
36
Question

Question 25

Write SQL commands for (a) to (d) and write the output for (e) and (f) on the basis of given table GRADUATE:

Table: GRADUATE

S NONAMESTIPENDSUBJECTAVERAGERANK
1KARAN400PHYSICS681
2RAJ450CHEMISTRY681
3DEEP300MATHS622
4DIVYA350CHEMISTRY631
5GAURAV500PHYSICS701
6MANAV400CHEMISTRY552
7VARUN250MATHS641
8LIZA450COMPUTER681
9PUJA500PHYSICS621
10NISHA300COMPUTER572

(a) List the names of those students who have obtained rank 1.

(b) Display a list of all those names whose average is greater than 65.

(c) Display the names of those students who have opted for computer as a subject with average of more than 60.

(d) List the names of all students whose name ends with 'a'.

(e) SELECT * FROM GRADUATE WHERE Subject = "Physics";

(f) SELECT DISTINCT RANK FROM GRADUATE;

Answer

(a)

SELECT NAME FROM GRADUATE 
WHERE `RANK` = 1;
Output
+--------+
| NAME   |
+--------+
| KARAN  |
| RAJ    |
| DIVYA  |
| GAURAV |
| VARUN  |
| LIZA   |
| PUJA   |
+--------+

(b)

SELECT NAME
FROM GRADUATE
WHERE AVERAGE > 65;
Output
+--------+
| NAME   |
+--------+
| KARAN  |
| RAJ    |
| GAURAV |
| LIZA   |
+--------+

(c)

SELECT NAME
FROM GRADUATE
WHERE SUBJECT = 'COMPUTER' AND AVERAGE > 60;
Output
+------+
| NAME |
+------+
| LIZA |
+------+

(d)

SELECT NAME FROM GRADUATE 
WHERE NAME LIKE "%a";
Output
+-------+
| NAME  |
+-------+
| DIVYA |
| LIZA  |
| PUJA  |
| NISHA |
+-------+

(e)

SELECT * FROM GRADUATE WHERE Subject = "Physics";
Output
+-------+--------+---------+---------+---------+------+
| S.No. | name   | stipend | subject | average | RANK |
+-------+--------+---------+---------+---------+------+
|     1 | KARAN  |     400 | PHYSICS |      68 |    1 |
|     5 | GAURAV |     500 | PHYSICS |      70 |    1 |
|     9 | PUJA   |     500 | PHYSICS |      62 |    1 |
+-------+--------+---------+---------+---------+------+

(f) Since 'RANK' is a reserved keyword in SQL, we encounter an error while running this query. To avoid such errors, we can enclose the column name 'RANK' in backticks to treat it as a literal identifier.

The corrected query is :

SELECT DISTINCT `RANK` FROM GRADUATE;
Output
+------+
| RANK |
+------+
|    1 |
|    2 |
+------+