SQL query Statement Contact

Set up four tables, namely student table, timetable, score table and teacher information table

Insert Information:

Topic:

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

Select Sname,ssex,class from Student

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

Select distinct depart from Teacher

3. Query all records of student table.

SELECT * FROM Student

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

SELECT * from score where degree between and 80

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

SELECT * from score where degree in (85,86,88)

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

SELECT * FROM student where Class = 95031 or Ssex = ' female '

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

SELECT * FROM student ORDER BY Class DESC

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

SELECT * FROM Score ORDER by Cno ASC, Degree desc

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

Select COUNT (*) Number of people from student where Class =95031

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

Select Sno,cno from score where degree = (select MAX (degree) from score)

11. Check the average score of each course.

Select AVG (degree) average score from score group by Cno

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

Select AVG (degree) from score where Cno like ' 3% ' GROUP by Cno have COUNT (*) >=5

Idea: First the score table query out, and then use the function AVG to the score table of the average sub-query out, and then use the where conditional sentence to start with 3 of the course this condition to filter out,

Finally, with having the 5 students to choose the condition of the screening out

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

Select Sno from score where degree >70 and degree <90

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

Select Sname,cno,degree from score left join student on Student.sno=score.sno

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

Select Cname,sno,degree from score join course on COURSE.CNO=SCORE.CNO

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

Select Cname,sname,degree from score join course on COURSE.CNO=SCORE.CNO join student on Student.sno=score.sno

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

Select AVG (degree) from score where Sno in (select Sno from student where Class = ' 95033 ')

18. Assume that a grade table is created using the following command:

CREATE table grade (low int (3), Upp int (3), rank char (1))

Insert into grade values (90,100, ' A ')

Insert into grade values (80,89, ' B ')

Insert into grade values (70,79, ' C ')

Insert into grade values (60,69, ' D ')

Insert into grade values (0,59, ' E ')

The SNO, CNO and rank columns of all students are now queried.

19, the query elective "3-105" course performance is higher than the "109" student scores of all the students record.

SELECT * FROM score where Cno = ' 3-105 ' and degree > No (select degree from score where Sno = ' 105 ')

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

Solution 1,

SELECT * FROM score where Sno in (select Sno from score where Sno! = (SELECT * from score where degree = ' * ") GROUP by Sno Having COUNT (*) >1)

Ideas:

Solution 2,

SELECT * FROM score where Sno in (select Sno from score where Sno
! = (SELECT * from score where degree = ' * ") group by Sno have COUNT (*) >1)

Ideas:

21, the results of the inquiry is higher than the number of "109", the course number is "3-105" of all records.

SELECT * FROM score where cno= ' 3-105 ' and degree > No (select 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, sbrithday from student where year (sbrithday) = (select year (sbrithday) from student where Sno =108)

23, inquires "Zhang Xu" the Teacher classroom student achievement.

SELECT * FROM score where CNO in (select CNO from Course where TNO in (select TNO from teacher where Tname= ' Zhang Xu '))

24. The name of the teacher who has more than 5 students who have enrolled in a course.

Select Tname from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score GROUP by Cno has COU NT (*) >=5))

Idea: First put this problem to use the Teacher,score,course table to look up, first with group by and have to the number of students more than five people of the course code of this condition screening out,

And then the teacher to teach this course code query out, know the teacher's code name and then the teacher's names query out

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

SELECT * FROM student where Class in (95033,95031)

26, the inquiry existence has 85 points above the course CNO.

Select distinct Cno from score where degree >85

27. Find out the results table of the "computer Department" teacher teaching course.

SELECT * FROM course where TNO in (select TNO from teacher where depart= ' computer system ')

28, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof.

SELECT * from Teacher where depart = ' computer system ' and Prof not in (select Prof from Teacher where depart = ' Electronic Engineering Department ')
Union
SELECT * from Teacher where depart = ' Electronic Engineering ' and PROF not in (select Prof from Teacher where depart = ' computer system ')

29, the inquiry elective number is "3-105" course and the result is at least higher than the elective number "3-245" classmate's CNO, Sno and degree, and according to degree from high to low order.

SELECT * FROM score where Cno = ' 3-105 ' and degree > (select MAX (degree) from score where Cno = ' 3-245 ') Order by degree Desc

Idea: First the score table in the course number is 3-245 of the average score query out, and then in the Query score table in the course number is 3-105 and the result is greater than 86, in descending order

30, inquires the elective number is "3-105" and the result is higher than the elective number is "3-245" the course schoolmate's CNO, Sno and degree.

SELECT * FROM score S1 where Sno in (103,109,105) and Cno = ' 3-105 ' and exists (
SELECT * FROM score S2 where Sno in (103,109,105) and Cno = ' 3-245 ' and S1. Sno =s2. Sno and S1. Degree >s2. degree)

Ideas:

31. Check the name, sex and birthday of all teachers and classmates.

Select Tname, Tsex, tbrithday from Teacher
Union
Select Sname, Ssex, sbrithday from student

32, query All "female" teacher and "female" classmate's name, Sex and birthday.

Select Tname, Tsex, tbrithday from Teacher where tsex = ' female '
Union
Select Sname, Ssex, sbrithday from student where Ssex = ' female '

33. Check the scores of students who have a lower average score than the course.

SELECT * FROM score S1 where exists (
Select Cno,avg (degree) from score S2 GROUP by Cno have S1. Cno =s2. Cno and S1. Degree <avg (degree))

34. Check the Tname and depart of all classroom teachers.

Solution 1,

Select Tname,depart from Teacher where exists
(SELECT * from Course where Teacher.tno =course.tno)

Solution 2,

Select Tname, depart from Teacher where Tno in (select distinct Tno from Course)

Solution 3,

Select Tname, depart from Course left joins Teacher on Teacher.tno =course.tno

35. Inquire about the Tname and depart of all teachers who have not lectured.

SELECT * from teacher where TNO not in (select TNO from Course)

36. Check the class number of at least 2 boys.

Select Class from student where Ssex = ' male ' GROUP by Class has COUNT (*) >=2

37, inquires the student table the surname "the king" the classmate record.

SELECT * FROM student where Sname don't like ' King% '

38. Check the name and age of each student in the student table.

Select Sname, DateDiff (year,sbrithday, ' 2014-11-9 ') age from student

39. Query the maximum and minimum sbirthday date values in the student table.

Select Max (sbrithday) maximum, min (sbrithday) minimum from student

40. Check all records in the student table in order of class number and age from large to small.

SELECT * FROM Student ORDER by Class Desc,sbrithday ASC

41. Query "male" teachers and their courses.

Select Cno from Course where Tno in (select Tno from Teacher where tsex = ' male ')

42. Check the SNO, CNO and degree columns of the students with the highest score.

Select Sno,cno, degree from score where degree on (select MAX (degree) from score)

43, inquiries and "Li June" with the gender of all students sname.

Select Sname from student where ssex= (select Ssex from student where Sname = ' Li June ')

44, inquiries and "Li June" with the same sex and classmates sname.

Select Sname from student where Class in (select Class from student where Sname = ' Li June ')
and Ssex = (select Ssex from student where Sname = ' Li June ')

45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course.

SELECT * FROM score where Cno in (select Cno from Course where Cname = ' Introduction to Computers ')
and Sno in (select Sno from student where Ssex = ' male ')

SQL query Statement Contact