Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 60 of 79

Database Query using SQL — Question 18

Back to all questions
18
Question

Question 18

Consider the table RESULT given below. Write commands in MySQL for (a) to (d) and output for (e) to (g):

Table: RESULT

NoNameStipendSubjectAverageDivision
1Sharon400English38THIRD
2Amal680Mathematics72FIRST
3Vedant500Accounts67FIRST
4Shakeer200Informatics55SECOND
5Anandha400History85FIRST
6Upasna550Geography45THIRD

(a) To list the names of those students who have obtained Division as FIRST in the ascending order of NAME.

(b) To display a report listing Name, Subject and Annual Stipend received assuming that the Stipend column has monthly stipend.

(c) To count the number of students who have either Accounts or Informatics as subject.

(d) To insert a new row in the table RESULT: 7, "Mohan", 500, "English", 73, "Second"

(e) SELECT AVG(STIPEND) FROM RESULT WHERE DIVISION = "THIRD";

(f) SELECT COUNT(DISTINCT Subject) FROM RESULT;

(g) SELECT MIN(Average) FROM RESULT WHERE Subject = "English";

Answer

(a)

SELECT NAME 
FROM RESULT 
WHERE DIVISION = 'FIRST' 
ORDER BY NAME;
Output
+---------+
| NAME    |
+---------+
| AMAL    |
| ANANDHA |
| VEDANT  |
+---------+

(b)

SELECT NAME, SUBJECT, (STIPEND * 12) AS ANNUAL_STIPEND 
FROM RESULT;
Output
+---------+-------------+----------------+
| NAME    | SUBJECT     | ANNUAL_STIPEND |
+---------+-------------+----------------+
| SHARON  | ENGLISH     |           4800 |
| AMAL    | MATHEMATICS |           8160 |
| VEDANT  | ACCOUNTS    |           6000 |
| SHAKEER | INFORMATICS |           2400 |
| ANANDHA | HISTORY     |           4800 |
| UPASNA  | GEOGRAPHY   |           6600 |
+---------+-------------+----------------+

(c)

SELECT COUNT(*) 
FROM RESULT 
WHERE SUBJECT IN ('ACCOUNTS', 'INFORMATICS');
Output
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(d)

INSERT INTO RESULT 
VALUES(7, "MOHAN", 500, "ENGLISH", 73, "SECOND");

(e)

Output
+--------------+
| AVG(STIPEND) |
+--------------+
|     475.0000 |
+--------------+

(f)

Output
+-------------------------+
| COUNT(DISTINCT Subject) |
+-------------------------+
|                       6 |
+-------------------------+

(g)

Output
+--------------+
| MIN(Average) |
+--------------+
|           38 |
+--------------+