_ Student Elective Database SQL statement exercises

1. Query the sname, Ssex, and class columns of all records in the student table.

Select Sname,ssex,t.sclass from STUDENT t

2, the inquiry teacher all units namely does not duplicate depart column.

Select distinct T.depart from TEACHER t

3. Query all records of student table.

SELECT * FROM STUDENT t

4. Query all records from 60 to 80 of the scores in the score table.

SELECT * FROM score t where degree > degree < 80

5. Check the record of 85, 86 or 88 in the score table.

SELECT * FROM score t where degree = degree = or degree = 88

6, inquires the student table "95031" class or the sex is "the female" the classmate record.

SELECT * from STUDENT t where t.sclass=9305 or t.ssex= ' female '

7. Query the student table for all records in descending order of class.

SELECT * FROM STUDENT t ORDER by t.sclass DESC

8, in CNO Ascending, Degree descending query score all records of the table.

SELECT * FROM score T ORDER by T.CNO Asc,t.degree desc

9. Check the number of students in "95031" class.

Select COUNT (*) from STUDENT t where t.sclass=95031

10. Check the student number and course number of the highest score in the score table. (sub-query or sort)

11. Check the average score of each course.

Select AVG (t.degree) from score T GROUP by T.cno -- Group

12. Check the average score of the course with at least 5 students enrolled in the score table and begin with 3.

Select T.cno,avg (t.degree) from score T where t.cno like ' 3% ' GROUP by T.cno -- grouping added to the back

13, the query score is greater than 70, less than 90 of the SNO column.

1select count (T.sno) from score T where t.degree>60 or t.degree<90

2select T.sno from score T where T.degree between and 90

First, why not?

14. Sname, CNO and degree columns for all students are queried.

Select C.sname,t.cno,t.degree from student C,score T where C.sno=t.sno

15. Check the SNO, CNAME and degree columns of all students.

Select A.sno,b.cname,c.degree from student A,course B,score C where B.cno=c.cno and C.sno=a.sno

16. Check the sname, CNAME and degree columns of all students.

Select A.sname,b.cname,c.degree from student A,course B, score C where A.sno=c.sno and B.cno=c.cno

17. Check the average score of "95033" class students.

Select AVG (degree) from student A,score b where a.class= ' 95033 ' and B.sno=a.sno

20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores.

Select T.sno from Score t where t.degree< (select Max (t.degree) from score T) Group by SNO have Count (CNO) >1

21. The result of the inquiry is higher than the record of "109" and the grade of the course number "3-105".

SELECT * FROM score where cno= ' 3-105 ' and degree> (select Max (degree) from score where Sno = ' 109 ')

22. The SNO, sname and Sbirthday of all students who were born in the same year were queried and studied for 108.

Select Sno,sname,sbirthday from Student t where t.sbirthday= (select Sbirthday from student where sno= ' 108 ') 23, query "Zhang Xu" teacher Classroom Student performance.

25. Check the records of all students in class 95033 and 95031.

SELECT * from STUDENT t,score s where t.sclass=95033 or t.sclass=95031

_ Student Elective Database SQL statement exercises