MySQL Database (5)--"reprint" MySQL Exercises

Original address: http://www.cnblogs.com/wupeiqi/articles/5748496.html

Sample Data sql:

/* Navicat Premium Data Transfer source server:localhost Source Server type:mysql Source server Version:5  0624 source Host:localhost source Database:sqlexam target server type:mysql target server Version : 50624 File encoding:utf-8 date:10/21/2016 06:46:46 am*/set NAMES UTF8; SET foreign_key_checks = 0;--------------------------------Table structure for ' class '------------------------------ DROP TABLE IF EXISTS ' class '; CREATE TABLE ' class ' (' cid ' int (one) not null auto_increment, ' caption ' varchar (+) NOT NULL, PRIMARY KEY (' CID ')) ENGI Ne=innodb auto_increment=5 DEFAULT Charset=utf8,--------------------------------Records of ' class '----------------- -------------Begin;insert into ' class ' VALUES (' 1 ', ' three years '), (' 2 ', ' Three ', ' three Shifts '), (' 3 ', ' Two classes a year '), (' 4 ', ' nine classes in two years '); COMMIT;--------------------------------table structure for ' course '------------------------------DROP table IF EXISTS ' Course '; CREATE TABLE ' Course ' (' CID ' int (one) not NULL auto_increment, ' cname ' varchar (+) NOT null, ' teacher_id ' int (one) not NULL, PRIMARY key (' CID '), key ' Fk_course_tea Cher ' (' teacher_id '), CONSTRAINT ' Fk_course_teacher ' FOREIGN KEY (' teacher_id ') REFERENCES ' teacher ' (' tid ')) Engine=inn ODB auto_increment=5 DEFAULT Charset=utf8,--------------------------------Records of ' course '---------------------- --------Begin;insert into ' Course ' VALUES (' 1 ', ' creature ', ' 1 '), (' 2 ', ' Physical ', ' 2 '), (' 3 ', ' Sport ', ' 3 '), (' 4 ', ' Art ', ' 2 '); COMMIT;--------------------------------table structure for ' score '------------------------------DROP table IF EXISTS ' Score '; CREATE TABLE ' score ' (' Sid ' Int (one) not null auto_increment, ' student_id ' int (one) not null, ' course_id ' int (one) ' not ' NU LL, ' num ' int (one) not NULL, PRIMARY key (' Sid '), key ' fk_score_student ' (' student_id '), key ' Fk_score_course ' (' Course  _id '), CONSTRAINT ' Fk_score_course ' FOREIGN KEY (' course_id ') REFERENCES ' Course ' (' CID '), CONSTRAINT ' fk_score_student ' FOREIGN KEY (' student_id ') REFerences ' student ' (' Sid ')) Engine=innodb auto_increment=53 DEFAULT Charset=utf8;-------------------------------- Records of ' score '------------------------------begin;insert into ' score ' VALUES (' 1 ', ' 1 ', ' 1 ', ' 10 '), (' 2 ', ' 1 ', ' 2 ', ' 9 '), (' 5 ', ' 1 ', ' 4 ', ' 66 '), (' 6 ', ' 2 ', ' 1 ', ' 8 '), (' 8 ', ' 2 ', ' 3 ', ' 68 '), (' 9 ', ' 2 ', ' 4 ', ' 99 '), (' 10 ', ' 3 ', ' 1 ', ' 77 '), (' 11 ', ' 3 ', ' 2 ', ' 66 '), (' 12 ', ' 3 ', ' 3 ', ' 87 '), (' 13 ', ' 3 ', ' 4 ', ' 99 '), (' 14 ', ' 4 ', ' 1 ', ' 79 '), (' 15 ', ' 4 ', ' 2 ', ' 11 '), (' 16 ', ' 4 ', ' 3 ', ' 67 '), (' 17 ', ' 4 ', ' 4 ', ' 100 '), (' 18 ', ' 5 ', ' 1 ', ' 79 '), (' 19 ', ' 5 ', ' 2 ', ' 11 '), (' 20 ', ' 5 ', ' 3 ', ' 67 '), (' 21 ') , ' 5 ', ' 4 ', ' 100 '), (' 22 ', ' 6 ', ' 1 ', ' 9 '), (' 23 ', ' 6 ', ' 2 ', ' 100 '), (' 24 ', ' 6 ', ' 3 ', ' 67 '), (' 25 ', ' 6 ', ' 4 ', ' 100 '), (' 26 ') , ' 7 ', ' 1 ', ' 9 '), (' 27 ', ' 7 ', ' 2 ', ' 100 '), (' 28 ', ' 7 ', ' 3 ', ' 67 '), (' 29 ', ' 7 ', ' 4 ', ' 88 '), (' 30 ', ' 8 ', ' 1 ', ' 9 '), (' 31 ', ' 8 ', ' 2 ', ' 100 '), (' 32 ', ' 8 ', ' 3 ', ' 67 '), (' 33 ', ' 8 ', ' 4 ', ' 88 '), (' 34 ', ' 9 ', ' 1 ', ' 91 '), (' 35 ', ' 9 ', ' 2 ', ' 88 '), (' 36 ', ' 9 '), ', ' 3 ', ' 67 '), (' 37 ', ' 9 ', ' 4 '), ' 22 '), (' 38 ', ' 10 ', ' 1 ', ' 90 '), (' 39 ', ' 10 ', ' 2 ', ' 77 '), (' 40 ', ' 10 ', ' 3 ', ' 43 '), (' 41 ', ' 10 ', ' 4 ', ' 87 '), (' 42 ', ' 11 '), ' 1 ', ' 90 '), (' 43 ', ' 11 ', ' 2 ', ' 77 '), (' 44 ', ' 11 ', ' 3 ', ' 43 '), (' 45 ', ' 11 ', ' 4 ', ' 87 '), (' 46 ', ' 12 ', ' 1 ', ' 90 '), (' 47 ', ' 12 '), ', ' 2 ', ' 77 '), (' 48 ', ' 12 ', ' 3 ', ' 43 '), (' 49 ', ' 12 ', ' 4 ', ' 87 '), (' 52 ', ' 13 ', ' 3 ', ' 87 '); COMMIT;--------------------------------table structure for ' student '------------------------------DROP table IF EXISTS ' student ';   CREATE TABLE ' student ' (' Sid ' Int (one) not NULL auto_increment, ' Gender ' char (1) is not NULL, ' class_id ' int (one) ' is not NULL, ' sname ' varchar (+) not NULL, PRIMARY key (' Sid '), key ' Fk_class ' (' class_id '), CONSTRAINT ' Fk_class ' FOREIGN KEY (' C lass_id ') REFERENCES ' class ' (' CID ')) Engine=innodb auto_increment=17 DEFAULT Charset=utf8;--------------------------  ------Records of ' student '------------------------------begin;insert into ' student ' VALUES (' 1 ', ' Male ', ' 1 ', ' Understanding '), (' 2 ', ' Female ', ' 1 ', ' Steel Egg '), (' 3 ', ' Male ', ' 1 ', ' Zhang San '), (' 4 ', ' Male '), ' 1 ', ' Zhang Yi '), (' 5 ', ' Female ', ' 1 ', ' Zhang Yi '), (' 6 ', ' Male ', ' 1 ', ' Zhang Si '), (' 7 ', ' Female ', ' 2 ', ' Hammer '), (' 8 ', ' Male ', ' 2 ', ' lie Triple '), (' 9 ', ' Male ', ' 2 ', ' Lee '), (' 10 ', ' Female ', ' 2 ', ' Li II '), (' 11 ', ' Male ', ' 2 ', ' John Doe '), (' 12 ', ' Female ', ' 3 ', ' Like Flower '), (' 13 ', ' Male ', ' 3 ', ' Liu San '), (' 14 ', ' Male ', ' 3 ', ' Liu One '), (' 15 ', ' Female ', ' 3 ', ' Ryuji '), (' 16 ', ' Male ', ' 3 ', ' Liusi '); COMMIT;--------------------------------table structure for ' teacher '------------------------------DROP table IF EXISTS ' teacher '; CREATE TABLE ' teacher ' (' tid ' int (one) not null auto_increment, ' tname ' varchar (+) NOT NULL, PRIMARY KEY (' tid ')) ENGI Ne=innodb auto_increment=6 DEFAULT Charset=utf8,--------------------------------Records of ' teacher '--------------- ---------------Begin;insert into ' teacher ' VALUES (' 1 ', ' Zhang Lei '), (' 2 ', ' Miss Li '), (' 3 ', ' Liu Haiyan Teacher '), (' 4 ', ' Zhu Yunhai teacher '), (' 5 ', ' Li Jie old Division '); COMMIT; SET foreign_key_checks = 1; table structure and data

You first import the above data into the database

To export existing database data:

• Mysqldump-u User name-p password database name > export file path # structure + data
• Mysqldump-u User name-p password-d database name > export file path # structure

Import existing database data:

• Mysqldump-uroot-p Password Database name < file path

2. Inquire about the number of all students who have higher grades in "biology" course than the "physics" course; train of thought: Get all the people who have a biology course (student number, score)-temporary table get all the people who have a physics course (student number, score)-temporary table connect two temporary tables according to the "Student number": Physics score Biological scores are then screened select A.student_id,sw,ty from (select Student_id,num as SW from score left join Cours E on score.course_id = course.cid where course.cname = ' creature ') as A left join (select Student_id,num as Ty From score left JOIN course on score.course_id = course.cid where course.cname = ' sport ') as B on a.student_id = B.st UDENT_ID where SW > if (isnull (Ty), 0,ty); 3, inquiry average score is more than 60 points of the students of the school number and average score; train of thought: According to the student group, use AVG to get average, filter by having the AVG Select STUDENT_ID,AVG (num) from score g Roup by STUDENT_ID has avg (num) > 60 4, inquires all the students of the number, name, number of courses selected, total; select Score.student_id,sum (Score.num), COUNT (score. student_id), Student.sname from score left joins student on score.student_id = Student.sid GROUP BY Score.stu DENT_ID 5. The number of teachers who queried the surname "Li"; select count (tid) from teacher where tname like ' li% ' select count (1) FROm (select tid from teacher where tname like ' li% ') as B 6, inquiry did not learn the "cotyledons" teacher class students of the school number, name; Train of thought: First find "Li ping" teacher taught all the lessons ID received All student IDs selected for the class are filtered in the student table select * FROM student where SID is not in (select DISTINCT student_id from score where s core.course_id in (select CID from course left JOIN teacher on course.teacher_id = teacher.tid where tname = ' Li Ping ') 7, the inquiry learned "001" and also learned the number "002" course of the students of the school number, name; Train of thought: first of all students who choose 001 and choose 002 Courses According to the students group, if the number of students equals 2, two The door has been selected select Student_id,sname from (select student_id,course_id from score where course_id = 1 or course_id = 2) A S B left join student on b.student_id = Student.sid GROUP BY student_id have count (student_id) > 1 8, Inquiry learned "leaf The student's number and name of all the classes taught by the teacher, as above, except that 001 and 002 become in (all classes of teacher cotyledons) 9, the course number "002" is less than the course number "001" of all the students of the school number, name; the 1th question 10, the query has a course score of less than 60 points  The student's school number, name; Select Sid,sname from student where SID in (select distinct student_id from score where num < 60) 11, inquiry did not learn the entireThe number of students in a class, the name of the students; Train of thought: in the score table according to the student group, get each student to choose the number of courses if the quantity = = Total Course quantity, indicates that all courses have been selected select Student_id,sname From score left join student on score.student_id = Student.sid GROUP BY student_id have count (course_id) = (SE        Lect count (1) from course) 12, inquire at least one class with the student number is "001" students learn the same student number and name; Train of thought: Get 001 Students choose all the courses in which all the courses are available and all courses Based on student screening, get all student information then connect with student table, get name Select Student_id,sname, Count (course_id) from score left join S Tudent on score.student_id = STUDENT.SID where student_id! = 1 and course_id in (select course_id from score where student_id = 1) Group BY student_id 13, inquire at least study number for "001" Students of all classes of the other students study number and name; first find and 001 of all the learned people then number = 001 all disciplines = = More Select Student_id,sname other people may choose, count (course_id) from score left joins student on score.student_id = Stu Dent.sid where student_id! = 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student _ID has count (course_ID) = (select count (course_id) from score where student_id = 1) 14, query and the "002" number of students to study the course of the same class of other students with the same number and name; 002 learned also learned select Student_id,sname from score left join student on score.student_id = STUDENT.SID where stude nt_id in (select student_id from score where student_id! = 1 GROUP BY student_id have count (course_id) = (s Elect count (1) from score where student_id = 1), and course_id in (select course_id from score where student_id = 1) Group by STUDENT_ID have count (course_id) = (select count (1) from score where student_id = 1) 15, delete learning "cotyledons" Teacher lesson score Memento Delete from score where course_id in (select CID from course left JOIN teacher on course.teacher_id = teach Er.tid where teacher.name = ' cotyledons ') 16. Insert some records into the SC table, which require that the following conditions be met: ① did not have the student number of the "002" course; ② insert the average score of the course "002"; ideas: Because in        SERT supports inset into TB1 (xx,xx) select x1,x2 from TB2; All, get all the people who haven't taken 002 lessons, get 002 Average results insert INTO score (student_id, course_id, num) Select sid,2, (select AVG (num) from score where course_id = 2) from student where Sid is not in (select S tudent_id from score where course_id = 2) 17, the average score from low to high display all students of the "Chinese", "math", "English" the course scores, as shown in the following form: Student ID, language, Mathematics, English, effective course number, there Select sc.student_id, (select num from score left JOIN course on score.course_id = course.cid where course  . CNAME = "creature" and score.student_id=sc.student_id) as Sy, (select num from score left join course on score.course_id  = course.cid where course.cname = "physical" and score.student_id=sc.student_id) as WL, (select num from score left JOIN Course on score.course_id = course.cid where course.cname = "sport" and score.student_id=sc.student_id) as Ty, COUNT ( SC.COURSE_ID), AVG (Sc.num) from score as SC GROUP BY student_id Desc 18, queries the highest and lowest scores for each section: shown in the following form: Course ID, maximum Select course_id, max (num) as max_num, Min (num) as Min_num from score Group by COURSE_ID; 19, according to the average performance of the sections from low to high and the percentage of passing rate from high to low order; Train of thought: caseWhen: Then select COURSE_ID, AVG (num) as avgnum,sum (case is score.num > then 1 else 0 END)/count (1) *100 as percent fr OM Score GROUP BY course_id ORDER by Avgnum asc,percent desc; 20, the course average score from high to low display (real classroom teacher); Select AVG (If (IsNull (score.num), 0,score.num)), Teacher.tname from course left join score on Course.cid = score.course_id left join teacher on course.teacher_id = Teacher.tid GROUP by score.course_id 21, query each section Record of top three records: (regardless of performance) Select Score.sid,score.course_id,score.num,t.first_num,t.second_num from score left join (s Elect SID, (select num from score as s2 where s2.course_id = s1.course_id order BY num DESC limit 0,1) as fi    Rst_num, (select num from score as s2 where s2.course_id = s1.course_id order BY num DESC limit 3,1) as Second_num From score as S1) as-T on Score.sid =t.sid where Score.num <= t.first_num and Score.num >= t.sec Ond_num 22, the number of students who are enrolled in each course is queried; select course_id, COUNT (1) from score GrouP by course_id; 23. Find out the number and name of all students who have only one course; Select Student.sid, Student.sname, COUNT (1) from score left join student on Score.studen t_id = Student.sid GROUP BY course_id have count (1) = 1 24, query the number of boys and girls; select * FROM (select COUNT (1) as Ma    N from student where gender= ' man ') as A, (select COUNT (1) as Feman from student where gender= ' female ') as B 25, the list of students whose surname "Zhang" is queried; Select 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,count (1) as count from student group by Sname; 27, the average score of each course is queried, the results are arranged in ascending order of average grade, and the average result is the same, in descending order by the course number. Select Course_id,avg (if (IsNull (num), 0, num)) as AVG from score Group by C OURSE_ID ORDER by AVG asc,course_id Desc; 28. Query the number, name and average score of all students with average scores greater than 85; Select Student_id,sname, Avg (if (ISNULL (num), 0, num)) from score left join student on S core.student_id = Student.sid GROUP by student_id; 29. The name and score of the student whose course title is "mathematics" and the score is less than 60; Select Student.sname,score.num from score left JOIN course on score.course_id = Cou Rse.cid left Join student on score.student_id = STUDENT.SID where Score.num < and course.cname = ' creature ' 30, query course number 003 and course score of 80 + Student's number and name; select * FROM score where score.student_id = 3 and Score.num > 80 31, the number of students selected for the course select COUNT (distinct student_id) from score Select COUNT (c) from (select COUNT (student_id) as C from score Group by student_id) as A 32. The name and result of the students with the highest achievement in the course of the Linda teacher; Select Sname,num from score left joins student on score.student_id = Stud Ent.sid where score.course_id in (select Course.cid from course left joins teacher on course.teacher_id = Teacher.tid WH Ere Tname= ' Zhang's teacher ') Order by num DESC LIMIT 1;  33. Check each course and the corresponding number of elective persons; Select Course.cname,count (1) from score left JOIN course on score.course_id = Course.cid Group  by course_id; 34. Check the student's number, course number and student achievement of different courses but with the same results; Select DISTINCT S1.course_id,s2.course_id,s1.num,s2.num from score as S1, score as s2 WH ere s1.num = S2.num and s1.course_id! = s2.course_id; 35. Check the top two of each course score; Select SCore.sid,score.course_id,score.num,t.first_num,t.second_num from score left join (select SID, (select  Num from score as s2 where s2.course_id = s1.course_id ORDER BY num desc-limit 0,1) as First_num, (select num from Score as s2 where s2.course_id = s1.course_id order BY num DESC limit () as Second_num from score as S1) As T on Score.sid =t.sid where Score.num <= t.first_num and Score.num >= t.second_num 36. Retrieve the student number of at least two elective courses; s Elect student_id from score Group by STUDENT_ID have count (student_id) > 1 37, check the course number and course name of all students taking courses; Select Course_ Id,count (1) from score Group by COURSE_ID have count (1) = (select count (1) from student); 38, the inquiry did not learn the "cotyledons" teacher taught any course of the student's name; Select Student_id,student.sname from score left join student on score.student_id = Stud Ent.sid where score.course_id not in (select CID from course left joins teacher on course.teacher_id = teacher.t ID where Tname = ' Mr. Zhang Lei ') group by student_id 39, inquiring the students of two or more failed courses and their average scores; select Student_id,count (1) from score where num < GROUP by student_id have count (1) > 2 40, the search "004" course score is less than 60, in descending order of the number of students in the grade; Select student_id from score where num< and course_id = 4 ORDER by Num de Sc 41, delete the "002" Students of the "001" course results; Delete from score where course_id = 1 and student_id = 2

　　

MySQL Database (5)--"reprint" MySQL Exercises