Question

Write the SQL queries for (i) to (iv) and give the output of the SQL statements:
Display name and stream for those students getting average marks more than 80.
Display name, stipend and grade class wise.
(iii) Display class, name and stream for those students whose stream is not commerce.
(iv) Display the names and average marks of student in the ascending order of grades.
(v) SELECT sum( stipend) from student where Grade = "B":
(vi) SELECT count(distinct stream) from student;​

Answer 1

1) Display the name and the stream for those students getting average marks more than 80.

• Select Name, Stream from Student where AvgMark > 80;

2) Display the name, the stipend and the grade class wise.

• Select Name, Stipend, Grade from Student order by Class;

3) Display the class, the name and the stream for those students whose stream is not commerce.

• Select Name, Stream, Class from Student where Stream != 'Commerce';

4) Display the names and the average marks of the students in the ascending order of grades.

• Select Name, AvgMark from Student order by Grade;

5)

$+\ -\ -\ -\ -\ -\ -\ -\ -\ -\ +\\\sf \ | \ \ \ \ \ \ \ \ \ \ \ \ su m(Stipend)\ \ \ \ \ \ \ \ \ \ | \\+\ -\ -\ -\ -\ -\ -\ -\ -\ -\ +\\\sf \ \ \ \ | \ \ \ \ \ \ \ \ \ \ \ \ \ \ 3900\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ | \\+\ -\ -\ -\ -\ -\ -\ -\ -\ -\ +$

6)

$+\ -\ -\ -\ -\ -\ -\ -\ -\ -\ +\\\sf \ | \ \ \ \ \ \ count(distinct\ stream)\ \ \ \ \ | \\+\ -\ -\ -\ -\ -\ -\ -\ -\ -\ +\\\sf \ \ \ \ | \ \ \ \ \ \ \ \ \ \ \ \ \ \ 4\ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ \ | \\+\ -\ -\ -\ -\ -\ -\ -\ -\ -\ +$