Mysql for databases (5) -- [reprint] mysql exercises and mysql exercises

Mysql for databases (5) -- [reprint] mysql exercises and mysql exercises
Original post 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: 50624 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; -- Optimize Table structure for 'class' -- ------------------------ drop table if exists 'class '; create table 'class' ('cid' int (11) not null AUTO_INCREMENT, 'caption 'varchar (32) not null, primary key ('cid ')) ENGINE = InnoDB AUTO_INCREMENT = 5 default charset = utf8; -- returns Records of 'class' -- -------------------------- BEGIN; insert into 'class' VALUES ('1', 'second class '), ('2', 'three-year, three-class'), ('3', 'two-year, two-class'), ('4', 'two-year, nine class'); COMMIT; -- ---------------------------- Table structure for 'Course' -- ------------------------------ drop table if exists 'Course'; create table 'Course' ('cid' int (11) not null AUTO_INCREMENT, 'cname' varchar (32) not null, 'Teacher _ id' int (11) not null, primary key ('cid '), KEY 'fk _ course_teacher '('Teacher _ id'), CONSTRAINT 'fk _ course_teacher' foreign key ('Teacher _ id') REFERENCES 'teacher' ('tid ')) ENGINE = InnoDB AUTO_INCREMENT = 5 default charset = utf8; -- Restore Records of 'Course' -- ---------------------------- BEGIN; insert into 'Course' VALUES ('1', 'bi ', '1'), ('2', 'Physical ', '2'), ('3', 'Sports', '3'), ('4 ', 'artbe', '2'); COMMIT; -- ---------------------------- Table structure for 'score '-- ---------------------------- drop table if exists 'score '; create table 'score '('sid' int (11) not null AUTO_INCREMENT, 'student _ id' int (11) not null, 'course _ id' int (11) not null, 'num' int (11) 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 ', '123'), ('18', '5', '1', '79 '), ('19', '5', '2 ', '11'), ('20', '5', '3', '67'), ('21', '5', '4 ', '123'), ('22', '6', '1', '9'), ('23', '6', '2 ', '123'), ('24', '6', '3', '67'), ('25', '6', '4 ', '123'), ('26', '7', '1', '9'), ('27', '7', '2 ', '123'), ('28', '7', '3', '67'), ('29', '7', '4 ', '88 '), ('30', '8', '1', '9'), ('31', '8', '2 ', '123'), ('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 'studen' -- ---------------------------- drop table if exists 'studen'; create table 'studen' ('sid' int (11) not null AUTO_INCREMENT, 'Gender' char (1) not null, 'class _ id' int (11) not null, 'sname' varchar (32) not null, primary key ('sid '), KEY 'fk _ class' ('class _ id'), CONSTRAINT 'fk _ class' foreign key ('class _ id') REFERENCES 'class' ('cid ')) ENGINE = InnoDB AUTO_INCREMENT = 17 default charset = utf8; -- returns Records of 'studen' -- -------------------------- BEGIN; insert into 'studen' VALUES ('1', 'male ', '1', 'comprehension'), ('2', 'female ', '1', 'steel eg'), ('3', 'male ', '1', 'zhang san'), ('4', 'mal', '1', 'zhang yi'), ('5', 'female, '1 ', 'zhang 2'), ('6', 'male', '1', 'zhang si'), ('7', 'female ', '2 ', 'hammer '), ('8', 'male', '2', 'Lee 3'), ('9', 'male', '2 ', 'Li yi'), ('10', 'female ', '2', 'Li 2'), ('11', 'mal', '2 ', 'Li si'), ('12', 'female ', '3', 'ru. hua'), ('13', 'mal', '3 ', 'Liu san'), ('14', 'male', '3', 'Liu yi'), ('15', 'female, '3 ', 'Liu 2'), ('16', 'male', '3', 'Liu si'); COMMIT; -- ---------------------------- Table structure for 'teacher' -- ------------------------------ drop table if exists 'teacher'; create table 'teacher' ('tid' int (11) not null AUTO_INCREMENT, 'tname' varchar (32) not null, primary key ('tid') ENGINE = InnoDB AUTO_INCREMENT = 6 default charset = utf8; -- reset Records of 'teacher' -- ------------------------ BEGIN; insert into 'teacher' VALUES ('1', 'Mr. Zhang Lei '), ('2', 'Mr. Liping'), ('3', 'Mr. Liu Haiyan '), ('4', 'Miss Zhu Yunhai '), ('5', Miss Li Jie'); COMMIT; SET FOREIGN_KEY_CHECKS = 1; table structure and data

 

Import the above data to the database first.

 

Export existing database data:

• Mysqldump-u username-p Password Database Name> export file path # structure + Data
• Mysqldump-u username-p password-d Database Name> export file path # Structure

Import existing database data:

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

 

2. query the student ID of all students whose score is higher than that of the "physical" course. Train of Thought: Obtain the student ID and score of all students whose score is higher than that of the "physical" course) -obtain the student ID and score of all students with physical courses from the temporary table. The temporary table is connected to two temporary tables based on the student ID: Student ID, physical score, biological score, and then select. student_id, sw, ty from (select student_id, num as sw from score left join course on score. course_id = course. cid where course. cname = 'bio') 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. student_id = B. student_id where sw> if (isnull (ty), 0, ty); 3. query the student ID and average score of students whose average score is greater than 60; Train of Thought: Based on the student group, use avg to obtain the average value and use having to filter avg select student_id, avg (num) from score group by student_id having avg (num)> 60 4. query the student ID, name, number of course selections, and total score of all students. select score. student_id, sum (score. num), count (score. student_id), student. sname from score left join student on score. student_id = student. sid group by score. student_id 5, Query the number of teachers surnamed "Li"; select count (tid) from teacher where tname like 'Lee % 'select count (1) from (select tid from teacher where tname like 'Li % ') as B 6. query the student ID and name of the student who has not learned "ye ping". Train of Thought: select * from student where sid not in (select DISTINCT student_id from score where score. course_id in (select cid from course left join teacher on course. teacher_id = teacher. tid where tna Me = 'instructor Liping ') 7. query the student ID and name of the student who has learned "001" and has also learned the course number "002". Train of Thought: first, all students who select both 001 and 002 are grouped by students. If the number of students is equal to 2, select student_id, sname from (select student_id, course_id from score where course_id = 1 or course_id = 2) as B left join student on B. student_id = student. sid group by student_id HAVING count (student_id)> 1 8. query the student ID and name of all students who have learned "ye ping, change 001 and 002 to in (all courses of instructor ye ping) 9. query the score ratio of the course No. "002" to "00 ". 1 "indicates the student ID and name of all students with low courses; for example, question 10, and query the student ID and name of students with a score less than 60; select sid, sname from student where sid in (select distinct student_id from score where num <60) 11. query the student ID and name of all students who have not completed the course. Train of Thought: in the score table, students are grouped to obtain the number of course selections for each student. If the number = Total number of courses, select student_id and sname from score left join student on score are selected. student_id = student. sid group by student_id HAVING count (course_id) = (select count (1) from course) 12. The query contains at least one The student ID and name of the same course as the student whose student ID is "001". Train of Thought: obtain all the courses selected by 001. obtain the information of all the students in the course and all the courses. Then, connect them to the student table and obtain the names select student_id, sname, count (course_id) from score left join student 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. query the student ID and name of all students whose student ID is "001; first, find the owner who has learned and 001, and then count = 001 all subjects = Other people may choose more select student_id, sname, count (course_id) from score left join student 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 having count (course_id) = (select count (course_id) from score where student_id = 1) 14. query other students whose courses are the same as those of "002". If the number of students is the same as 002, select student_id is also used, sname from score left join student on score. student_id = student. sid where student_id in (select student_id from score where student_id! = 1 group by student_id HAVING count (course_id) = (select 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 HAVING count (course_id) = (select count (1) from score where student_id = 1) 15. Delete the score Table Record of the Learning Course "ye ping; delete from score where course_id in (select cid from course left join teacher on course. teacher_id = teacher. Tid where teacher. name = 'peiping ') 16. insert some records into the SC table. These records must meet the following requirements: ① students who have not passed the "002" course number; ② insert the average score of course 002; Train of Thought: Because insert supports inset into tb1 (xx, xx) select x1, x2 from tb2; All, obtain all people who have not passed 002, and obtain the average score of 002: insert into score (student_id, course_id, num) select sid, 2, (select avg (num) from score where course_id = 2) from student where sid not in (select student_id from score where course_id = 2) 17. Show all students from low to high by average score The scores of the "Chinese", "Mathematics", and "English" courses are displayed in the following form: Student ID, Chinese, mathematics, English, and number of valid courses, with an average score; 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 cour Se on score. course_id = course. cid where course. cname = "Sports" 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. The highest score and lowest score of each subject are displayed as follows: course ID, highest score, lowest score; select course_id, max (num) as max_num, min (num) as min_num from score group by course_id; 19. The average score of each subject ranges from low to high and from high to low. Train of Thought: case when .. then select course_id, avg (num) as avgnum, sum (Case when score. num> 60 then 1 else 0 END)/count (1) x 100 as percent from score group by course_id order by avgnum asc, percent desc; 20. The average score of the course is displayed from high to low (real instructor); 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 the top three records of each subject score: (parallel score is not considered) select score. sid, s Core. 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 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. second_nu M 22. query the number of students selected for each course. select course_id, count (1) from score group by course_id; 23. Check the student ID and name of all students who have selected only one course. select student. sid, student. sname, count (1) from score left join student on score. student_id = student. sid group by course_id having count (1) = 1 24. query the number of boys and girls. select * from (select count (1) as man from student where gender = 'male ') as A, (select count (1) as feman from student where gender = 'femal') S B 25. query the student list with the last name "Zhang"; select sname from student where sname like 'zhang % '; 26. query the student list with the same name and surname and count the number of students with the same name; select sname, count (1) as count from student group by sname; 27. query the average scores of each course. The results are sorted in ascending order based on the average scores. The average scores are the same, sort by course number in descending order; select course_id, avg (if (isnull (num), 0, num) as avg from score group by course_id order by avg asc, course_id desc; 28. query the student ID, name, and average score of all students whose average score is greater than 85; select student_id, sname, avg (if (isnull (num), 0, num) fr Om score left join student on score. student_id = student. sid group by student_id; 29. query the names and scores of students whose course names are "Mathematics" and whose scores are less than 60. select student. sname, score. num from score left join course on score. course_id = course. cid left join student on score. student_id = student. sid where score. num <60 and course. cname = 'bio' 30. query the student ID and name of the student whose course number is 003 and whose course score is higher than 80. select * from score where score. student_id = 3 and score. num> 80 31. 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. query the names and scores of the students that have the highest scores among the students that take the course "Yang Yan". select sname, num from score left join student on score. student_id = student. sid where score. course_id in (select course. cid from course left join teacher on course. teacher_id = teacher. tid where tname = 'Mr. Zhang Lei ') or Der by num desc limit 1; 33. query each course and the number of optional students; select course. cname, count (1) from score left join course on score. course_id = course. cid group by course_id; 34. query the student id, course number, and student score of different courses with the same scores; select DISTINCT s1.course _ id, s2.course _ id, s1.num, s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course _ id! = S2.course _ id; 35. query the first two of the best course scores; 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 1, 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. second_num 36. Retrieve the student ID of at least two optional courses. select student_id from score group by student_id having count (student_id)> 1 37. query the course number and Course name of all optional courses; select course_id, count (1) from score group by course_id having count (1) = (select count (1) from student); 38. query the names of students who have not learned any course taught by "ye ping"; select student_id, student. sname from score left join student on score. student_id = student. sid where score. course_id not in (select cid from course left join teacher on course. teacher_id = teacher. tid where tname = 'Mr. Zhang Lei ') group by student_id 39. query the student ID and average score of two or more failed courses. select student_id, count (1) from score where num <60 group by student_id having count (1)> 2 40. Search for students whose scores are less than 60 in 004 and sorted in descending order; select student_id from score where num <60 and course_id = 4 order by num desc; 41. Delete the score of the "001" course of "002; delete from score where course_id = 1 and student_id = 2