Computer Science
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.
Answer
1.
SELECT *
FROM STUDENT1
WHERE Stream = 'Nonmedical' ;
Output
+-----+--------+---------+------------+---------+-------+-------+
| 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 ;
Output
+--------+
| Name |
+--------+
| Divya |
| Mohan |
| Arun |
| Karan |
| John |
| Rubina |
| Vikas |
+--------+
3.
SELECT *
FROM STUDENT1
ORDER BY AvgMark DESC ;
Output
+-----+---------+---------+------------+---------+-------+-------+
| 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 ;
Output
+---------+---------+------------+----------------+
| 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 |
+---------+---------+------------+----------------+
Related Questions
Write SQL commands for the following on the basis of given table SPORTS :
Table : SPORTS
StudentNo. Class Name Game1 Grade1 Game2 Grade2 10 7 Sameer Cricket B Swimming A 11 8 Sujit Tennis A Skating C 12 7 Kamal Swimming B Football B 13 7 Venna Tennis C Tennis A 14 9 Archana Basketball A Cricket A 15 10 Arpit Cricket A Athletics C - Display the names of the students who have grade 'C' in either Game1 or Game2 or both.
- Display the names of the students who have same game for both Game1 and Game2.
- Display the games taken up by the students, whose name starts with 'A'.
Write SQL commands for the following on the basis of given table CLUB :
Table : CLUB
COACH_ID COACHNAME AGE SPORTS PAY SEX DATOFAPP 1 KUKREJA 35 KARATE 1000 M 1996-03-27 2 RAVINA 34 KARATE 1200 F 1998-01-20 3 KARAN 34 SQUASH 2000 M 1998-02-19 4 TARUN 33 BASKETBALL 1500 M 1998-01-01 5 ZUBIN 36 SWIMMING 750 M 1998-01-12 6 KETAKI 36 SWIMMING 800 F 1998-02-24 7 ANKITA 39 SQUASH 2200 F 1998-02-20 8 ZAREEN 37 KARATE 1100 F 1998-02-22 9 KUSH 41 SWIMMING 900 M 1998-01-13 10 SHAILYA 37 BASKETBALL 1700 M 1998-02-19 - To show all information about the swimming coaches in the club.
- To list names of all coaches with their date of appointment (DATOFAPP) in descending order.
- To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.
Consider the table Student1 of Q. 13. Give the output of following SQL statement :
- SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75 ;
- SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B' ;
- SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A' ;
- SELECT RIGHT(Stream, 2) FROM Student1 ;
Given the following table :
Table : STUDENT
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 Give the output of following SQL statements :
- SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;
- SELECT SUM(Stipend) FROM STUDENT WHERE Grade = 'B' ;
- SELECT AVG(Stipend) FROM STUDENT WHERE Class = '12A' ;
- SELECT COUNT(DISTINCT) FROM STUDENT ;