Assuming the simple database with one table holding student

Assuming the simple database with one table holding student names, departments and gpa, write the SQL to

1extract all the fields of all the records in the database and put them in order by name

2…. put them in order by gpa, highest to lowest

3….put them in order by gpa, highest to lowest, only listing the top 3

4…. put them in order by department

5extract a list of the departments cited in the database

6extract the name and gpa information for all students in the \'music\' department

7extract and calculate the number of students and the average gpa for each department, ordered by average gpa, highest to lowest

8extract and calculate the number of students and the average gpa for each deparment with more than 2 students, ordered by average gpa, highest to lowest

STUDENT_NAME DEPARTMENT GPA
-------------------- -------------------- ----------------------
ramu dance 92
ramesh music 78
rajesh sports 65
koti music 56
dinesh dance 58
bala music 95

6 rows selected

STUDENT_NAME DEPARTMENT GPA
-------------------- -------------------- ----------------------
bala music 95
ramu dance 92
ramesh music 78
rajesh sports 65
dinesh dance 58
koti music 56

6 rows selected

OUTPUT

STUDENT_NAME DEPARTMENT GPA STURANK

-------------------- -------------------- ---------------------- ----------------------
bala music 95 1
ramu dance 92 2
ramesh music 78 3

4)select * from students order by department desc;

STUDENT_NAME DEPARTMENT GPA
-------------------- -------------------- ----------------------
rajesh sports 65
koti music 56
ramesh music 78
bala music 95
dinesh dance 58
ramu dance 92

6 rows selected

5)select department from students GROUP BY DEPARTMENT;

DEPARTMENT
--------------------
music
sports
dance

6)select student_name,gpa from students where department=\'music\';

STUDENT_NAME GPA
-------------------- ----------------------
koti 56
ramesh 78
bala 95

7)SELECT COUNT(1),AVG(GPA),DEPARTMENT FROM STUDENTS GROUP BY DEPARTMENT ORDER BY AVG(GPA) DESC;

COUNT(1) AVG(GPA) DEPARTMENT

---------------------- ---------------------- --------------------
3 76.33333 music
2 75 dance
1 65 sports