SQL PRACTICE - 5 (SELF PRACTICE)

Consider the following tables STUDENT and STREAM. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (h).

TABLE: STUDENT

SCODE	NAME	AGE	STRCDE	POINTS	GRADE
101	Amit	16	1	6	NULL
102	Arjun	13	3	4	NULL
103	Zaheer	14	2	1	NULL
105	Gagan	15	5	2	NULL
108	Kumar	13	6	8	NULL
109	Rajesh	17	5	8	NULL
110	Naveen	13	3	9	NULL
113	Ajay	16	2	3	NULL
115	Kapil	14	3	2	NULL
120	Gurdeep	15	2	6	NULL

TABLE: STREAM

STRCODE	STRNAME
1	SCIENCE+COMP
2	SCIENCE+BIO
3	SCIENCE+ECO
4	COMMERCE+MATHS
5	COMMERCE+SOCIO
6	ARTS+MATHS
7	ARTS+SOCIA

(a)   To display the name of streams in alphabetical order from table STREAM.

(b)   To display the number of students whose POINTS are more than 5.

(c)   To update GRADE to ‘A’ for all those students, who are getting more than 8 as POINTS.

(d)   ARTS+MATHS stream is no more available. Make necessary change in table STREAM.

(e)   SELECT SUM(POINTS) FROM STUDENT WHRE AGE > 14;

(f)   SELECT STRCDE, MAX(POINTS) FROM STUDENT GROUP BY STRCDE HAVING SCODE BETWEEN 105 AND 130;

(g)   SELECE AVG(AGE) FROM STUDENT WHERE SCODE IN (102, 105, 110, 120);

(h)   SEELCT COUNT(STRNAME) FROM STREAM WHERE STRNAME LIKE “SCI%”;