Informatics Practices
Write a query to find out the sum, average, lowest and highest marks of the students in Student table grouped by STREAM.
Answer
The Student table is as follows:
| ROLLNO | NAME | GENDER | MARKS | DOB | MOBILE_NO | STREAM |
|---|---|---|---|---|---|---|
| 1 | RAJ KUMAR | M | 93 | 2000-11-17 | 9586774748 | SCIENCE |
| 2 | DEEP SINGH | M | 98 | 1996-08-22 | 8988886577 | COMMERCE |
| 3 | ANKIT SHARMA | M | 76 | 2000-02-02 | NULL | SCIENCE |
| 4 | RADHIKA GUPTA | F | 78 | 1999-12-03 | 9818675444 | HUMANITIES |
| 5 | PAYAL GOEL | F | 82 | 1998-04-21 | 9845639990 | VOCATIONAL |
| 6 | DIKSHA SHARMA | F | 80 | 1999-12-17 | 9897666650 | HUMANITIES |
| 7 | GURPREET KAUR | F | NULL | 2000-01-04 | 7560875609 | SCIENCE |
| 8 | AKSHAY DUREJA | M | 90 | 1997-05-05 | 9560567890 | COMMERCE |
| 9 | SHREYA ANAND | F | 70 | 1999-10-08 | NULL | VOCATIONAL |
| 10 | PRATEEK MITTAL | M | 75 | 2000-12-25 | 9999967543 | SCIENCE |
SELECT STREAM, SUM(MARKS) AS Total_Sum,
AVG(MARKS) AS Average_Marks, MIN(MARKS) AS Lowest_Marks,
MAX(MARKS) AS Highest_Marks
FROM Student
GROUP BY STREAM;
Output
+------------+-----------+---------------+--------------+---------------+
| STREAM | Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks |
+------------+-----------+---------------+--------------+---------------+
| SCIENCE | 244 | 81.3333 | 75 | 93 |
| COMMERCE | 188 | 94.0000 | 90 | 98 |
| HUMANITIES | 158 | 79.0000 | 78 | 80 |
| VOCATIONAL | 152 | 76.0000 | 70 | 82 |
+------------+-----------+---------------+--------------+---------------+
Related Questions
What is the difference between WHERE and HAVING clause in SQL select command?
Write a query to find out the sum, average, lowest and highest marks in Student table.
Consider the table "Item" given below and give the outputs on the basis of it:
Table: ITEM
Itemno Iname Price (₹) Quantity 101 Soap 50 100 102 Powder 100 50 103 Facecream 150 25 104 Pen 50 200 105 Soapbox 20 100 (a) SELECT SUM(Price) FROM ITEM;
(b) SELECT AVG(Price) FROM ITEM;
(c) SELECT MIN(Price) FROM ITEM;
(d) SELECT MAX(Price) FROM ITEM;
(e) SELECT COUNT(Price) FROM ITEM;
(f) SELECT DISTINCT Price FROM ITEM;
(g) SELECT COUNT(DISTINCT Price) FROM ITEM;
(h) SELECT Iname, Price*Quantity FROM ITEM;
Define a function. Why are they useful?