Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 74 of 91

Relational Database and SQL — Question 35

Back to all questions
35
Question

Question 28

Write SQL Commands/output for the following on the basis of the given table GRADUATE:

Table: GRADUATE

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

(i) List the names of those students who have obtained rank 1 sorted by NAME.

(ii) Display a list of all those names whose AVERAGE is greater than 65.

(iii) Display the names of those students who have opted COMPUTER as a SUBJECT with an AVERAGE of more than 60.

(iv) List the names of all the students in alphabetical order.

(v) SELECT * FROM GRADUATE WHERE NAME LIKE "%I%";

(vi) SELECT DISTINCT RANK FROM GRADUATE;

Answer

(i)

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

(ii)

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

(iii)

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

(iv)

SELECT NAME
FROM GRADUATE
ORDER BY NAME;
Output
+--------+
| NAME   |
+--------+
| DEEP   |
| DIVYA  |
| GAURAV |
| KARAN  |
| LIZA   |
| MANAV  |
| NISHA  |
| PUJA   |
| RAJ    |
| VARUN  |
+--------+

(v) SELECT * FROM GRADUATE WHERE NAME LIKE "%I%";

Output
+-------+-------+---------+-----------+---------+------+
| S.No. | name  | stipend | subject   | average | rank |
+-------+-------+---------+-----------+---------+------+
|     4 | DIVYA |     350 | CHEMISTRY |      63 |    1 |
|     8 | LIZA  |     450 | COMPUTER  |      68 |    1 |
|    10 | NISHA |     300 | COMPUTER  |      57 |    2 |
+-------+-------+---------+-----------+---------+------+

(vi) 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 |
+------+