[Assignments] Simple Queries in SQL — Chapter 12 Assignments (Q/A)
Table of Contents
15. Given the following table:
Give the output of following SQL statements:
| 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 |
(i) SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;
(ii) SELECT SUM(Stipend) FROM Student WHERE Grade = ‘B’ ;
(iii) SELECT AVG(Stipend) FROM Student WHERE Class = ’12A’ ;
(iv) SELECT COUNT(DISTINCT) FROM Student ;
Solution:
| MIN(AvgMark) |
| 64.4 |
| SUM(Stipend) |
| 1150.00 |
| AVG(Stipend) |
| 475.00 |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM Student' at line 1
This error message indicates that there’s a syntax error in your SQL query near the DISTINCT keyword. MySQL is indicating that it expected something after the DISTINCT keyword, such as a column name, but found nothing. That is, you need to specify which column you want to count the distinct values of.
If you want to count the distinct values in all columns, you would have to repeat the COUNT(DISTINCT) for each column or use an asterisk * to count all columns’ distinct values.
The correct syntax would be:
SELECT COUNT(DISTINCT column_name) FROM Student;