MySQL Exam summary

Use school;--class table Create TABLE Class (CID TINYINT PRIMARY KEY auto_increment, caption VARCHAR); INSERT into Class ( Caption) VALUES ("Three year Two Classes"), ("Three shifts a year"), ("Three year Class"); SELECT * from class;--teacher tables create TABLE teacher (tid TINYINT PRIMARY KEY auto_increment, Tname VARCHAR); INSERT in To Teacher (Tname) VALUES ("Wave"), ("Cang-Empty"), ("Rice Island"); SELECT * FROM teacher;--Student table CREATE TABLE student (SID TINYINT PRIMARY KEY auto_increment, sname VARCHAR), Gend Er VARCHAR (Ten), class_id TINYINT, FOREIGN KEY (class_id) REFERENCES class (CID)); INSERT into student (SNAME,GENDER,CLA ss_id) VALUES ("Steel Egg", "female", 1), ("Hammer", "female", 1), ("Shanbao", "Male", 2); SELECT * FROM student;--Curriculum create TABLE course (CID TINYINT PRIMARY KEY auto_increment, CNAME VARCHAR), teach er_id TINYINT, FOREIGN KEY (teacher_id) REFERENCES teacher (TID)); ALTER TABLE Course MODIFY CID TINYINT, DROP PRIMARY KEY; ALTER TABLE course ADD CONSTRAINT xx FOREIGN KEY (CID) REFERENCES class (CID);D ESC course; SHOW CREATE TABLE Course;iNsert into Course (cname,teacher_id) VALUES ("Biology", 1), ("Sport", 1), ("Physics", 2); SELECT * from course;--score table CREATE TABLE score (SID TINYINT PRIMARY KEY auto_increment, student_id TINYINT, course _id TINYINT, number INT, FOREIGN key (student_id) REFERENCES student (SID), FOREIGN KEY (course_id) REFERENCES cou RSE (CID)); INSERT into score (student_id, course_id, number) VALUES (1,1,60), (1,2,59), (2,2,100);  SELECT * from Score;delete from score WHERE sid=6;# second, Operation table # # 1, create test data by yourself # 2, Query "Biology" course is higher than "physics" of all students of the school number; SELECT b.student_id From (SELECT Score.student_id,score.number to score INNER JOIN course on score.course_id = Course.cid WHERE course.cname = "Creature") as A INNER join (SELECT score.student_id,score.number from score INNER join course on score.course_id = Course.ci D WHERE course.cname= "physical") as B on a.student_id=b.student_id and a.number>b.number;# 3, query average score is greater than 60 points of the student's number and average score; in SERT into score (student_id, course_id, number) VALUES (2,3,56), (3,1,46), (3,2,59), (3,3,71), (4,1,90), (4,2,27); SELECT Student_id,avg average score from score GROUP by STUDENT_ID have avg (number) >60;# 4, check all students ' number, name, course selection, total Select Student.sid,student.sname,count (student.class_id), sum (number), total, from student INNER JOIN score on student. Sid = Score.student_id GROUP by sname the ORDER by total; # 5, the number of teachers who queried the surname "Wave"; SELECT count (*) wave teacher number from teacher WHERE tname like "wave % "; insert into teacher (tname) VALUES (" big Wave "); # 6, inquiry did not learn the" cotyledons "teacher class of the student's school number, name;--Get all the students to learn the course and their corresponding teachers, and then the corresponding filter INSERT into teacher ( Tname) VALUES ("Cotyledons"), insert into course (CNAME, teacher_id) VALUES ("History", 5); INSERT into score (student_id, course_id, Number) VALUES (1,5,81); SELECT Student.sid,student.sname,a.tname from score INNER joins student on score.student_id = Student.sid INNER Join (SELECT DISTINCT course.cid,course.teacher_id,course.cname,teacher.tname from course INNER JOIN teacher on Course.teach er_id = Teacher.tid) as A on score.course_id = A.cid GROUP by a.tname have a.tname!= "cotyledons"; # 7, Inquiry learned "1" and also learned the number"2" The student's number, name; INSERT into student (sname,gender,class_id) VALUES ("Zhang San", "Male", 3); INSERT into score (student_id, course_id , number) VALUES (5,2,63); Select b.sid number, b.sname name from (SELECT student.sid,student.sname from score INNER JOIN student on score.student_id = s Tudent.sidwhere course_id=1) as A INNER join (SELECT student.sid,student.sname from score INNER join student on Scor    e.student_id = Student.sidwhere course_id=2) as B on a.sid = b.sid;# 8, the inquiry learns "Cotyledons" the student's school number, name of all classes taught by the teacher; SELECT * from student; SELECT * FROM Score;insert to student (sname, Gender, class_id) VALUES ("Harry", "Male", 3); INSERT into score (student_id, course _id, number) VALUES (8,5,93); SELECT student.sid number, student.sname name from score INNER JOIN student on score.student_id = Student.sid and course_i d=5;# 9, Query the course number "2" of the grade than the course number "1" of all the students of the lower class number, name; INSERT into student (sname, Gender, class_id) VALUES ("Zhao Liu", "female", 2); insert Into score (student_id, course_id, number) VALUES (9,2,65), (9,1,70); UPDATE score SET number = n WHERE sid=21;SELECT * FROM student where (SELECT number from score where student.sid=score.student_id and score.course_id=2) < (select number from score WHERE student.sid=score.student_id and score.course_id=1); # 10, check the students who have a course score of less than 60 points, the name of the student; SELECT Student.sid,student.sname from student INNER joins score on student.sid = score.student_id WHERE score.number<60 GROU P by Student.sname;insert into student (sname, Gender, class_id) VALUES ("Steel Coins", "male", 2); INSERT into score (student_id, Course_ ID, number) VALUES (4,3,48); # 11, the query does not learn all the class of the students ' school numbers, names;--Test select COUNT (CID) Total course count from course; SELECT count (course_id) Study_course from score GROUP by student_id; SELECT * FROM (SELECT COUNT (CID) Total course count from course) as A INNER JOIN (select count (course_id) Study_course from score GROUP by student_id) as B on A. Total Course number = b.study_course;--correct answer select Student.sid,student.sname,count (course_id) Learning course Number FRO M score INNER JOIN student on score.student_id = Student.sid GROUP by student_id have count (course_id) = (SELECT count (CID) from course); # 12, inquire at least one class with the student number is "001" students learn the same student number and name; # 13, Inquire at least study number for the "001" Students of all the class of the other students study number and name; # 14, Query and "2 "Number of the students to study the course of the same class of other students and names; # 15. Delete the SC table record of the" cotyledons "teacher class; # 16. Insert records into the SC table that require the following criteria: ① did not have the number" 002 "course of the student number; ② Insert" 002 " Number of courses; # 17, average grades from low to high show all students of the "Chinese", "math", "English" the course scores, as shown in the following form: Student ID, language, Mathematics, English, effective course number, effective average; # 18, Search for the highest and lowest scores for each section: show the course ID, highest score, lowest score, SELECT Course_id,max (number), min (number) min. from score GROUP by Course_id;i  Nsert into score (student_id, course_id, number) VALUES (2,1,76); # 19, according to the average grades of each section from low to high and the percentage of passing rate from high to low order;--analysis of the average score and calculation of the pass rate select AVG (number) course average score from score GROUP by course_id; SELECT count (student_id) The number of failed subjects from the score WHERE number>60 GROUP by course_id;    SELECT count (student_id) Total number of sections from score GROUP by course_id; --The answer is as follows SELECT A. Course average, b. Number of failed sections/C. Total number of subjects as pass rate from (SELECT course_id,avg (number) course average score from score GROUP by course_id As A INNER JOIN (SELECT course_id,count (student_id) The number of failed subjects from score WHERE number>60 GROUP by course_id)  As B      INNER JOIN (SELECT course_id,count (student_id) Total number of sections from score GROUP by course_id) as C on a.course_id = b.co urse_id and a.course_id = c.course_id ORDER by A. Average grade of the course; # 20, course average score from high to low display (show instructor); SELECT Teacher.tname,course.cname from T Eacher,course WHERE course.teacher_id = Teacher.tid; The Select AVG (number) course is divided by the Teacher,score GROUP by course_id;--answer as follows select A.tname Instructor, B. Course average from (select teacher. Tname,course.cname,course.cid from teacher,course WHERE course.teacher_id = teacher.tid) as A INNER JOIN (SELECT avg (number) course average, teacher.tname,score.course_id from Teacher,score GROUP by course_id) as B on a.cid=b.course_id ORDER by B. Average desc;# 21, check the records of the top three grades of each section: (regardless of the results) SELECT * from score ORDER by course_id;# 22, the number of students who are enrolled in each course;--analysis according to the score according to the course class, according to student ID statistics Select SCORE.COURSE_ID course Id,count (student_id) course number of visitors from score GROUP by course_id;# 23, Check out the number and name of all students who have only one course of study; Select Student.sid,student.sname from (SELECT * from Score GROUP by STUDENT_ID have count (sTUDENT_ID) =1) as A INNER JOIN student on a.student_id = student.sid;# 24, the number of boys and girls to query; Select Boy, Girl. Female from (select Count (gender) male from student where gender= "male") as Boy, (select count (gender) female from student WHERE gender= "female") as girl;  # 25. Check the list of students whose surname is "Zhang"; select Sid,sname from student WHERE sname like "Zhang%"; # 26. Check the names of students with the same name, and count the number of the same name; SELECT sname name, COUNT (sname) Number of names from student GROUP by sname;# 27, the average score of each course is queried, the results are sorted in ascending order by average, and the average grade is the same, in descending order by course number; Select A.cid Course number, B. Average division from (select T Eacher.tname,course.cname,course.cid from teacher,course WHERE course.teacher_id = teacher.tid) as A INNER JOIN (SEL ECT avg (number) course score, teacher.tname,score.course_id from Teacher,score GROUP by course_id) as B on a.cid=b.course_id OR    DER by B. Average desc;# 28, the number, name and average scores of all students with average scores greater than 85, SELECT Student.sid, student.sname name, avg (score.number) average score from Student INNER JOIN score on student.sid = score.student_id GROUP by sname;# 29, the name and score of the student whose course is named "Biology" and whose score is less than 60; SELECT course.c Name course, Student.sname name,Score.number score from score INNER join student INNER Join course on score.student_id = Student.sid and Score.course_id=c Ourse.cid where course.cname= "creature" and score.number<60;# 30, the number and name of the student whose course number is 3 and the course score is 80 or more; SELECT Student.sid School Number, Student.sname name from score INNER joins student on score.student_id = Student.sid and score.course_id=3 and Score.number >80;# 31. Number of students selected for the course insert into student (sname, Gender, class_id) VALUES ("John Doe", "Male", 3); INSERT into student (sname, gender , class_id) VALUES ("Win seven", "female", 2), INSERT into score (student_id, course_id, number) values (7,1,74);--the answer is as follows select COUNT ( A.STUDENT_ID) Number of participants from (select DISTINCT student_id from score) as a;# 32, the name of the students with the highest scores and their achievements in the courses offered by the "Cang-empty" teacher; SELECT student . sname name, max (number) score from score INNER join Student INNER Join course INNER join teacher on Score.student_id=student . Sid and Score.course_id=course.cid and Course.teacher_id=teacher.tid WHERE teacher.tname= "Cang-Empty"; # 33, check each course and the corresponding number of elective ; SELECT score.course_id Course Id,count (student_id) Number of courses from score GROUP by course_id;# 34, students who have the same grades in different courses but who have the same grade, the course number, the students ' scores; # 35, the top two of the best results for each course; # 36, the number of students who have at least two elective courses;--analysis of the root According to the Student ID Group, statistical course occurrence, screening courses more than equal to 2 students select student_id from score GROUP by student_id have count (course_id) >=2;# 37, Check the course number and course name of the course that all students take; Select score.student_id Student id,course.cname selected course, course.cid course ID from score INNER JOIN course on SCO re.course_id = Course.cid ORDER by Student id;# 38, the name of the student who has not learned any of the courses taught by the "cotyledons" teacher; # 39, check the number of students with two or more failed courses and their average scores; SELECT A.student_ ID number, B. Average score from (SELECT student_id from score WHERE number<60 GROUP by student_id have count (number) >=2) as A INNER JOIN (SELECT student_id,avg (number) average score from score GROUP by student_id) as B on a.student_id=b.student_id;# 40, check The "1" course score is less than 60, according to the number of students in descending order of fractions, select student_id from score WHERE course_id=1 and number<60;# 41, delete "2" Students of the "1" course of the results;--this question has been For

　　

MySQL Exam summary