CBSE Class 12 Computer Science
Question 20 of 78
Simple Queries in SQL — Question 13
Back to all questions 13
Question Write SQL commands for the following on the basis of given table STUDENT1 :
Table : STUDENT1
| No. | Name | Stipend | Stream | AvgMark | Grade | Class |
|---|---|---|---|---|---|---|
| 1 | Karan | 400.00 | Medical | 78.5 | B | 12B |
| 2 | Divakar | 450.00 | Commerce | 89.2 | A | 11C |
| 3 | Divya | 300.00 | Commerce | 68.6 | C | 12C |
| 4 | Arun | 350.00 | Humanities | 73.1 | B | 12C |
| 5 | Sabina | 500.00 | Nonmedical | 90.6 | A | 11A |
| 6 | John | 400.00 | Medical | 75.4 | B | 12B |
| 7 | Robert | 250.00 | Humanities | 64.4 | C | 11A |
| 8 | Rubina | 450.00 | Nonmedical | 88.5 | A | 12A |
| 9 | Vikas | 500.00 | Nonmedical | 92.0 | A | 12A |
| 10 | Mohan | 300.00 | Commerce | 67.5 | C | 12C |
- Select all the Nonmedical stream students from STUDENT1.
- List the names of those students who are in class 12 sorted by Stipend.
- List all students sorted by AvgMark in descending order.
- Display a report, listing Name, Stipend, Stream and amount of stipend received in a year assuming that the Stipend is paid every month.
1.
SELECT *
FROM STUDENT1
WHERE Stream = 'Nonmedical' ;+-----+--------+---------+------------+---------+-------+-------+
| No. | Name | Stipend | Stream | AvgMark | Grade | Class |
+-----+--------+---------+------------+---------+-------+-------+
| 5 | Sabina | 500 | Nonmedical | 90.6 | A | 11A |
| 8 | Rubina | 450 | Nonmedical | 88.5 | A | 12A |
| 9 | Vikas | 500 | Nonmedical | 92.0 | A | 12A |
+-----+--------+---------+------------+---------+-------+-------+
2.
SELECT Name
FROM STUDENT1
WHERE Class LIKE '12%'
ORDER BY Stipend ;+--------+
| Name |
+--------+
| Divya |
| Mohan |
| Arun |
| Karan |
| John |
| Rubina |
| Vikas |
+--------+
3.
SELECT *
FROM STUDENT1
ORDER BY AvgMark DESC ;+-----+---------+---------+------------+---------+-------+-------+
| No. | Name | Stipend | Stream | AvgMark | Grade | Class |
+-----+---------+---------+------------+---------+-------+-------+
| 9 | Vikas | 500 | Nonmedical | 92.0 | A | 12A |
| 5 | Sabina | 500 | Nonmedical | 90.6 | A | 11A |
| 2 | Divakar | 450 | Commerce | 89.2 | A | 11C |
| 8 | Rubina | 450 | Nonmedical | 88.5 | A | 12A |
| 1 | Karan | 400 | Medical | 78.5 | B | 12B |
| 6 | John | 400 | Medical | 75.4 | B | 12B |
| 4 | Arun | 350 | Humanities | 73.1 | B | 12C |
| 3 | Divya | 300 | Commerce | 68.6 | C | 12C |
| 10 | Mohan | 300 | Commerce | 67.5 | C | 12C |
| 7 | Robert | 250 | Humanities | 64.4 | C | 11A |
+-----+---------+---------+------------+---------+-------+-------+
4.
SELECT Name, Stipend, Stream, (Stipend * 12) AS Yearly_Stipend
FROM STUDENT1 ;+---------+---------+------------+----------------+
| Name | Stipend | Stream | Yearly_Stipend |
+---------+---------+------------+----------------+
| Karan | 400 | Medical | 4800 |
| Divakar | 450 | Commerce | 5400 |
| Divya | 300 | Commerce | 3600 |
| Arun | 350 | Humanities | 4200 |
| Sabina | 500 | Nonmedical | 6000 |
| John | 400 | Medical | 4800 |
| Robert | 250 | Humanities | 3000 |
| Rubina | 450 | Nonmedical | 5400 |
| Vikas | 500 | Nonmedical | 6000 |
| Mohan | 300 | Commerce | 3600 |
+---------+---------+------------+----------------+