45 Questions to change and delete

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 #去重用distinct

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
or select * from score where degree >=60 && degree <=80 # && can be used with and

5. Check the record of 85, 86 or 88 in the score table.
SELECT * FROM score where degree = 85 | | degree = 86 | | Degree =88
or 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= ' woman ' #or可以用 | |

7. Query the student table for all records in descending order of class.
SELECT * FROM student ORDER BY Class desc #排序order by descending desc ascending asc

8, in CNO Ascending, Degree descending query score all records of the table.
SELECT * FROM score ORDER BY degree DESC,CNO ASC

9. Check the number of students in "95031" class.
Select COUNT (*) 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)
Method One sort: Select Sno,cno from score order BY degree desc limit 0,1 #从0索引取1个
Method Two: Select Sno,cno from score where degree = (select Max (degree) from score)
Query statements Query one or a list of results, can be used as parameters of other query statements, this is the subquery, that is, query nesting

11. Check the average score of each course.
Select AVG (degree), CNO 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.
Idea: Query the average score for a course starting with 3
Select CNO, AVG (degree) from score where CNO like ' 3% ' GROUP by CNO
Check CNO for at least 5 students
Select CNO from Score GROUP by CNO have Count (*) >=5
Synthesis: Select AVG (degree), CNO from score where CNO in
(select CNO from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5) GROUP by CNO

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. Check all students ' sname, CNO and degree #是student和score的结合
Method One: Select Sname,cno,degree from student,score where Student.sno = Score.sno #不建议
Method Two: Select Sname,cno,degree from student join score on student.sno = Score.sno #常规的做法
Method Three: Select (select Sname from student where Student.sno = Score.sno), Cno,degree from score #子查询比较麻烦

15. Check the SNO, CNAME and degree columns of all students.
Method One: Select Sno,cname,degree from score,course where score.cno = Course.cno
Method Two: Select Sno,cname,degree from score join course on score.cno = Course.cno

16. Check the sname, CNAME and degree columns of all students.
Select Sname,cname,degree from student,course,score where score.cno = Course.cno
and Student.sno =score.sno

17. Check the average score of "95033" class students.
Own: Select AVG (degree), CNO from score where Sno in
(select Sno from student where class = ' 95033 ') group by CNO #选修的课程不一样
Teacher's: 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.
Select Sno,cno,rank from Score,grade where degree between low and UPP
Select Sno,cno,rank from score join grade on degree between low and UPP
#between Low and UPP is the unused sort left in the score range

19, the query elective "3-105" course performance is higher than the "109" student scores of all the students record.
Topic Understanding: Elective 3-105 and score greater than 109th elective 3-105
SELECT * FROM score where cno = ' 3-105 ' and degree >
(select degree from score where sno = ' 109 ' and CNO = ' 3-105 ')

20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores.
Subject comprehension: The non-highest of the course, ambiguous to see their own understanding
Ideas: 1) query score is not the highest score
SELECT * from score a where degree <
(select Max (degree) from score b where b.cno = A.cno)
2) Query The Sno of the students who have elective multiple doors
Select Sno from score where Sno in
(select Sno from Score GROUP by SNO have Count (*) >1)
After summary: SELECT * FROM score a where degree<
(select Max (degree) from score b where b.cno = A.cno)
and Sno in (select Sno from Score GROUP by SNO have Count (*) >1)

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 degree >
(select degree from score where sno = ' 109 ' and CNO = ' 3-105 ')

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
Where year (sbirthday) = (select year (sbirthday) from student where Sno = ' 108 ') #取年的函数YEAR (Sbirthday)

23, inquires "Zhang Xu" the Teacher classroom student achievement.
Train of thought: Main inquiry is student's achievement, take CNO Main line;
TNO #从teacher到course再到score Zhang Xu in Teacher, and CNO in course TNO
SELECT * FROM score where CNO in
(select CNO from course where TNO =
(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. #从score到course再到teacher
Idea: 1) Select Cno from Score GROUP by Cno have Count (*) >5 #在score表中查询选修课程人数大于5的
Summary: Select Tname from teacher where TNO in
(select Tno from course where CNO in
(select CNO from Score GROUP by CNO have Count (*) >5))

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. #从teacher到course再到score
SELECT * FROM score where CNO in
(select CNO from course where TNO in
(select TNO from teacher where depart = ' Computer Department ')

28, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof.
Understand the difference: my idea is to look at the computer system, which is different from the electrical Engineering Department #只有计算机系
Select tname,prof from teacher where depart = ' computer system ' and
Prof not in ( Select prof from teacher where depart = ' Electronic Engineering Department ')
the teacher's idea is: to find out two departments in different titles #包含两个系的内容
Select * from teacher where Prof not I N
(select Prof from teacher where depart = ' computer system ' and
Prof in (select Prof from teacher where depart = ' Electronic Engineering Department '))
Another method: SELECT * from Teacher where Prof in
(select Prof from Teacher where depart = ' computer system ' and Prof
not in (select P ROF from Teacher where depart = ' Electronic Engineering Department ')
Union
Select * from Teacher where Prof in
(select Prof from Teacher Where depart = ' Electronic Engineering ' and Prof
not in (select Prof from Teacher where depart = ' computer system ')
It's like my idea. Union Union

29, the inquiry elective number is "3-105" course and the result is at least higher than the elective number "3-245"
The CNO, Sno, and degree of the classmates, and sorted by degree from highest to lowest order. #都是按sno相同时来
Select Cno,sno,degree from score where cno = ' 3-105 ' and degree >
Any (select degree from score where cno = ' 3-245 ') Order BY degree DESC

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 Cno,sno,degree from score where cno = ' 3-105 ' and degree >
All (select degree from score where cno = ' 3-245 ') #All is all meaning, in contrast to the above any is at least

31. Check the name, sex and birthday of all teachers and classmates.
Select Sname,ssex,sbirthday from Student
Union select Tname,tsex,tbirthday from teacher

32, query All "female" teacher and "female" classmate's name, Sex and birthday.
Select Sname,ssex,sbirthday from student where ssex = ' Female ' union
Select Tname,tsex,tbirthday from teacher where tsex = ' female '

33. Check the scores of students who have a lower average score than the course.
SELECT * from score a where degree <
(select AVG (degree) from score b where A.cno =b.cno)

34. Check the Tname and depart of all classroom teachers. #score表中的 from score to course to teacher.
Select Tname,depart from teacher where TNO in
(select Tno from Course where CNO on (select CNO from Score))

35. Inquire about the Tname and depart of all teachers who have not lectured.
Select Tname,depart from teacher where TNO not in
(select Tno from Course where CNO on (select CNO from Score))

36. Check the class number of at least 2 boys.
Select class from student where Ssex = ' man ' and group by class have count (*) >1

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,year (now ())-year (sbirthday) from student

39. Query the maximum and minimum sbirthday date values in the student table.
Select Max (sbirthday), Min (sbirthday) from student

40. Check all records in the student table in order of class number and age from large to small.
Select Class,sbirthday from Student order by class Desc,sbirthday ASC #asc可以省略

41. Query "male" teachers and their courses. #从teacher到course
Select CNAME from course where TNO in
(select TNO from teacher where tsex = ' male ') #只是查出教的课程
Own: Select Tname,cname from teacher,course where Teacher.tno = Course.tno
and tsex = ' Male '

42. Check the SNO, CNO and degree columns of the students with the highest score.
Sort: Select Sno,cno,degree from score order BY degree DESC limit 0,1
Subquery: Select Sno,cno,degree from score where degree = (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 ssex =
(select Ssex from student where sname = ' Li June ') and class =
(select class from student where Sname= ' Li June ')

45. Check the scores of all the "male" students who have enrolled in the "Introduction to Computer" course. #从course到score, from Studen to score
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 ')

Adding and deleting 45 questions to be changed