Database table teacher Student Teachers ' questionnaire exercises

#创建数据库
Create Database Z_stu;
#创建表
/*
Whether the property name data type can be null meaning
Sno varchar (20) No study number (main code)
Sname varchar (20) No student name
Ssex varchar (20) No student sex
Sbirthday Datetime Student Birth date
Class varchar (20) can be student's class
*/
CREATE table if not EXISTS student (
Sno varchar () NOT NULL comment "study number",
Sname varchar (a) NOT null comment "Student name",
Ssex varchar () NOT NULL comment "Student sex",
Sbirthday datetime comment "Student Birth date",
Class varchar (comment) "Student class",
Primary KEY (SNO)
)
CharSet UTF8;
/*
Whether the property name data type can be null meaning
Cno varchar (20) No course number (main code)
Cname varchar (20) No course name
Tno varchar (20) No Faculty number (external code)
*/
CREATE table if not EXISTS course (
CNO varchar () NOT NULL comment "course number",
CNAME varchar (a) NOT NULL comment "course name",
TNO varchar () Not NULL comment "teacher number",
Primary KEY (CNO)
) CharSet UTF8;
/*
Whether the property name data type can be null meaning
Sno varchar (20) No study number (external code)
Cno varchar (20) No course number (outside code)
Degree Decimal (4,1) can score

Main code: sno+ Cno
*/
CREATE table if not EXISTS score (
Sno varchar () NOT NULL comment "study number",
CNO varchar () NOT NULL comment "course number",
degree Decimal (4,1),
Primary KEY (SNO,CNO)
) CharSet UTF8;
/*
Whether the property name data type can be null meaning
Tno varchar (20) No Faculty number (main code)
Tname varchar (20) No Faculty name
Tsex varchar (20) No Faculty sex
Tbirthday DateTime Faculty Birth date
Prof varchar (20) Available title
Depart varchar (20) No Faculty Department
*/
CREATE table if not EXISTS teacher (
TNO varchar () NOT NULL comment "faculty number",
Tname varchar () Not NULL comment "teacher name",
Tsex varchar () NOT NULL comment "gender",
Tbirthday datetime comment "Birthday",
Prof varchar (comment) "title",
Depart varchar () NOT NULL comment "faculty Department",
Primary KEY (TNO)
) CharSet UTF8;

There are 4 tables created here, student, course, score, teacher, and now add data to the table (that is, increase):

Student

Course

Score:

Teacher

Based on these tables, do a series of database data manipulation exercises:

11, Query the sname, Ssex, and class columns of all records in the student table. 2Select Sname,ssex,classfrom student;32, query teachers all units that are not duplicated depart column. 4 select distinct depart from teacher;5   63, querying all records of the student table. 7SELECT *from student;84, query all records in the score table with scores from 60 to 80. 9SELECT * from score where degree between and 80;Ten5, query the records in the score table for grades 85, 86, or 88.  OneSELECT * from score where degree in (85, 86, 88) ; A6, Query student table "95031"class or gender" is a "female" student record.  -SELECT * FROM student whereclass= "95031" or ssex = "female"; - queries all records of the student table in descending order of class.  the queries all records of the score table in CNO Ascending, degree descending order.  -Query "95031The number of students in the class.  -10, query the student number and course number of the highest score in the score table. (sub-query or sort) -Select Cno,sno from score where degree = (selectMax(degree) from score); + check the average score for each course.  -12, the average score of a course that has at least 5 students enrolled in the score table and starts with 3.  +Select AVG (degree) from score Group by CNO haveCount(*) >=5 and CNO like "3%" A the query score is greater than 70, less than the Sno column of 90.  at Select Sno from score where - Query the Sname, CNO, and degree columns for all students.  -15, query all students for SNO, CNAME, and degree columns.  -Select A.sname,b.cname,c.degree -From student asA,course asB,score asC -where A.sno = C.sno and B.cno = c.CNO in16, query all students for sname, CNAME, and degree columns.  -Select Sname,cname,degree from student to JoinScore on student.sno = score.Sno + JoinCourse on course.cno = score.CNO -  the17, inquiry "95033The average class of students.  *Select AVG (degree) from score where Sno in (select Sno from student whereclass= "95033" ); $20, query score the number of students who choose to learn more courses in the score is not the highest scores record. Panax NotoginsengSELECT *From score where Sno in (select Sno from Score Group by Sno) -and degree not in (selectMax(degree) from score); theor select *From score where Sno in (select Sno from Score Group by Sno) +and degree not in (selectMax(degree) from score Group by CNO); A  the21, the inquiry result is higher than the study number is "109", the course number is "3-105"All records of the scores.  + Select degree from score where degree -> (SELECTMax(degree) from score where cno = "3-105" and sno = "109") $22, inquiry and study number 108 students of the same year were born of all students Sno, sname and Sbirthday column.  $Select Left (sbirthday,4) as Datefrom student; -Select Sno,sname,sbirthday from student where Sbirthday like Concat ((select Left (sbirthday,4) as DateFrom student where sno= "108"), "", "%"); -  the23, query "Zhang Xu" teachers to teach students results.  -1, check students ' grades from the score tableWuyi  the2, the condition is that this course is "Zhang Xu" teacher Classroom - A) Check the name of the teacher from the table called Zhang Xu number Wu B) Find the corresponding course number in the curriculum according to the teacher's number -Select depart from teacher where Tname= "Zhang Xu"; AboutSelect CNO from Course where depart= (select depart from teacher where Tname= "Zhang Xu"); $ the students ' achievements in the introduction of computer examination -  -Query all Columns/The field from the score table (score) condition is - Course Number: Course name (CNAME) in Curriculum (course) is a course number called Introduction to Computers A  +Course name (CNAME) in Curriculum (course) is referred to as the course number of computer introduction ===3-105 the  -Query all columns/fields from the score table (score) condition is the course number: 3-105 $  the Query the name of the course Sung teacher teaches the  the which department is the teacher who teaches higher mathematics? the  -  in24, the name of the teacher who has more than 5 students who have enrolled in a course.  theSelect CNO from Score GROUP by CNO haveCount(*) > "5"; theSelect TNO from Course where CNO in (select CNO from Score GROUP by CNO haveCount(*) > "5"); AboutSelect Tname from teacher where tno= (select Tno from Course where CNO in (select CNO from Score GROUP by CNO haveCount(*) > "5")); the  the25, check the records of 95033 classes and 95031 classes of all students.  theSELECT * FROM student whereclassIn (95033,95031); +26, the inquiry existence has 85 points above the course CNO. -Select CNO from score where degree > "85" the27, and the results of the "computer department" Teachers ' teaching courses were queried. BayiSelect TNO from teacher where depart= "computer system"; theSelect CNO from Course where TNO in (select TNO from teacher where depart= "computer system"); theSELECT * FROM score where CNO in (select CNO from Course where TNO in (select TNO from teacher where depart= "computer system")); -  -29, inquires the elective number is "3-105" the course and the result is at least higher than the elective number is "3-245" schoolmate's CNO, Sno and degree,and sort by degree from highest to lowest order.  the30, 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. theSELECT * FROM score where cno= ' 3-105 ' and degree> (selectMax(degree) from score where Cno = ' 3-245 ')   the  the31. Check the name, sex and birthday of all teachers and classmates. -Select Sname,ssex,Sbirthday from Student the Union theSelect Tname,tsex,tbirthday from teacher; the32, query All "female" teacher and "female" classmate's name, Sex and birthday.94Select Sname,ssex,sbirthday from student where ssex= "female" the Union theSelect Tname,tsex,tbirthday from teacher where tsex= "female"; the33, and the results of students who have a lower average score than the course. 98SELECT * FROM Score GROUP by CNO have degree<avg (degree); About34. Check the Tname and depart of all classroom teachers. - Select CNO from Score Group by CNO;101 Select TNO from Course where CNO in (select CNO from Score Group by CNO);102Select Tname,depart from teacher where TNO in (select Tno from Course where CNO in (select CNO from Score Group by CNO);10335. Inquire about the Tname and depart of all teachers who have not lectured.104Select Tname,depart from teacher where TNO not in (select TNO from Course where CNO in (select CNO from Score Group by CNO); the36, check the class number of at least 2 men. 106SelectclassFrom student where ssex= ' Men ' GROUP byclassHavingCount(*) >110737, query the student table, not the surname "Wang" student records. 108SELECT * FROM student where sname don't like "King%";10938, query the name and age of each student in the student table.  theSelect Sname, (2018-left (sbirthday,4)) asAge from student;11139, querying the largest and smallest sbirthday date values in the student table.  theSelectMax(Sbirthday),min(Sbirthday) from student;11340, querying all records in the student table in the order of class number and age from large to small.  theSELECT * FROM Student ORDER BYclassDesc, (2018-left (sbirthday,4) desc; the41, query "male" teachers and their courses.  theSelect TNO from teacher where tsex= "male";117Select Teacher.tname,course.cname from Teacher,course where Course.tno in (select TNO from teacher where tsex= "male") and cour se.tno= teacher.TNO;11842, Query the SNO, CNO and degree columns of the highest score. 119SelectMax(degree) from score; -SELECT * FROM score where degree= (selectMax(degree) from score);12143, inquiries and "Li June" with the gender of all students sname.122Select Ssex from student where Sname= "Li June";123Select sname from student where ssex= (select Ssex from student where Sname= "Li June");12444, inquiries and "Li June" with the same sex and classmates sname. theSelectclassFrom student where sname= "Li June";126Select sname from student where ssex= (select Ssex from student where Sname= "Li June") andclass= (SELECTclassFrom student where sname= "Li June");12745, and query all the "male" students who have enrolled in the "Introduction to Computer" course.  -Select CNO from course where cname= "Introduction to Computers";129Select Sno from student where ssex= "male"; theSELECT * FROM score where Sno in (select Sno from student where ssex= "male") and CNO in (select CNO from course where cname= "calculation Machine Introduction ");

45 Operation code exercises

Database table teacher Student Teachers ' questionnaire exercises