Mysql database basic statement Training Questions, mysql database

Mysql database basic statement Training Questions, mysql database

SET FOREIGN_KEY_CHECKS = 0; -- analyze Table structure for course -- -------------------------- drop table if exists 'Course'; create table 'Course' ('cno' varchar (10) default null, 'cname' varchar (255) default null, 'tno' varchar (10) default null) ENGINE = InnoDB default charset = utf8; -- ---------------------------- Records of course -- ---------------------------- insert into 'Course' VALUES ('01', 'China', '02'); insert into 'Course' VALUES ('02 ', 'mat', '01'); insert into 'Course' VALUES ('03', 'English ', '03 '); -- ------------------------------ Table structure for SC -- -------------------------- DROP TABLE IF EXISTS 'scs'; CREATE TABLE 'scs' ('sno' varchar (10) DEFAULT NULL, 'cno' varchar (10) default null, 'score 'decimal () default null) ENGINE = InnoDB default charset = utf8; -- returns Records of SC -- -------------------------- insert into 'SC 'values ('01 ', '01', '80. 0 '); insert into' SC 'VALUES ('01', '02', '90. 0 '); insert into' SC 'VALUES ('01', '03', '99. 0 '); insert into' SC 'VALUES ('02', '02 ', '60. 0 '); insert into' SC 'VALUES ('02', '03', '80. 0 '); insert into' SC 'VALUES ('03', '01', '80. 0 '); insert into' SC 'VALUES ('03', '02', '80. 0 '); insert into' SC 'VALUES ('03', '03', '80. 0 '); insert into' SC 'VALUES ('04', '01', '50. 0 '); insert into' SC 'VALUES ('04', '02 ', '30. 0 '); insert into' SC 'VALUES ('04', '03', '20. 0 '); insert into' SC 'VALUES ('05', '01', '76. 0 '); insert into' SC 'VALUES ('05', '02 ', '87. 0 '); insert into' SC 'VALUES ('06', '01', '31. 0 '); insert into' SC 'VALUES ('06', '03', '34. 0 '); insert into' SC 'VALUES ('07', '02 ', '89. 0 '); insert into' SC 'VALUES ('07', '03', '98. 0 '); -- ------------------------------ Table structure for student -- -------------------------- drop table if exists 'studen'; create table 'studen' ('sno' varchar (10) default null, 'sname' varchar (255) default null, 'sage' datetime default null, 'ssex 'varchar (255) default null) ENGINE = InnoDB default charset = utf8; -- ---------------------------- Records of student -- ---------------------------- insert into 'student 'values ('01', 'zhaole', '2017-01-01 00:00:00 ', 'male '); insert into 'student 'values ('02 ', 'money electric', '2017-12-21 00:00:00 ', 'male '); insert into 'student 'values ('03', 'Sun feng', '2017-05-20 00:00:00 ', 'mal '); insert into 'studen' VALUES ('04 ', 'Li yun', '2017-08-06 00:00:00', 'male'); insert into 'studen' VALUES ('05 ', 'zhou mei', '2017-12-01 00:00:00 ', 'female'); insert into 'student 'values ('06', 'huland ', '2017-03-01 00:00:00 ', 'female'); insert into 'student 'values ('07 ', 'zheng Zhu', '2017-07-01 00:00:00 ', 'femal'); insert into 'student 'values ('08 ', 'wangju', '2017-01-20 00:00:00', 'female '); -- Optimize Table structure for teacher -- -------------------------- drop table if exists 'teacher'; create table 'teacher' ('tno' varchar (10) default null, 'tname' varchar (255) default null) ENGINE = InnoDB default charset = utf8; -- -------------------------------- Records of teacher -- -------------------------- insert into 'teacher' VALUES ('01', 'zhang san '); insert into 'teacher' VALUES ('02 ', 'Li si'); insert into 'teacher' VALUES ('03', 'wang 5 '); -- Optimize Table structure for users -- -------------------------- drop table if exists 'users'; create table 'users' ('id' int (10) not null AUTO_INCREMENT, 'username' varchar (20) default null, 'Password' varchar (20) default null, primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = utf8; -- ---------------------------- Records of users -- ---------------------------- insert into 'users' VALUES ('1', 'aaa', 'AB \ 'C ');

Student (Sno, Sname, Sage, Ssex) Student table Course (Cno, Cname, Tno) curriculum SC (Sno, Cno, score) Instructor table question: 1. query the student ID of all students whose score is higher than that of the "002" course; 2. query the student ID and average score of students whose average score is greater than 60; 3. query the student ID, name, number of course selections, and total score of all students; 4. query the number of teachers surnamed "Li; 5. query the student ID and name of the student who has not learned the course "ye ping; 6. query the student ID and name of the student who has learned the course 001 and has also learned the course 002; 7. query the student ID and name of all students who have learned the course taught by instructor ye Ping; 8. query the student ID and name of all students whose score is smaller than the score of course no. "002" than the course No. "001; 9. query the student ID and name of all students whose course scores are less than 60; 10. query the student ID and name of all students who have not completed the course; 11. query at least one course that is the same as the course number "1001 ". Student ID and name; 12. query the student ID and name of at least one student whose student ID is "001; 13. Change the average score of the Course taught by instructor ye Ping in the SC table; 14. query the student ID and name of other students whose courses are identical to those of "1002". 15. Delete the SC Table Record of the course of "ye ping; 16. insert some records into the SC table. These records must meet the following requirements: average score of students whose student ID is no "003; 17. Show the "Database", "enterprise management", and "English" course scores of all students based on average scores in the following format: Student ID ,, database, enterprise management, English, number of valid courses, valid average score 18, query the highest score and lowest score of each subject: displayed in the following form: course ID, highest score, score 19, by the average score of each subject from low to high and the percentage of pass rate from high to low 20, query the average score and pass rate of the following courses (displayed in "1 line ): enterprise Management (001), Marx (002), OO & UML (0 03) database (004) 21. query the average scores of different courses taught by different teachers from high to low. 22. query the transcript of the following course scores of 3rd to 6th students: enterprise Management (001), Marx (002), UML (003), Database (004) 23. Print the scores of each subject, Number of students in each score segment: course ID, course name, [100-85], [85-70], [70-60], [<60] 24. query the average score and rankings of students 25. query the top three records of the scores of each subject (excluding the parallel score) 26. query the number of students selected for each course, the number of male and female students, the number of female students, the number of students surnamed "Zhang", and the number of students with the same name, count the number of students with the same name 31 and 1981 (note: the type of the Sage column in the Student table is datetime) 32. query the average score of each course, the results are sorted in ascending order of average scores. The average scores are the same, sort by course number in descending order 33. query the student ID, name, and average score of all students whose average score is greater than 85. query the course name as "Database ", The names and scores of students whose scores are less than 60 are 35. query the Course selections of all students; 36. query the names, course names, and scores of any course whose scores are greater than 70; 37. query the failed courses, and arrange the number of students from large to small. 38. query the student ID and name of the students whose number is 003 and whose score is higher than 80; 39. The number of students who have selected the course is 40. Among the students who have chosen the course "ye ping, name of the student with the highest score and 41 of the score, query the student ID, course number, and student score of each course with the same scores. 42 the top two with the best scores: 44. count the number of students enrolled in each course (only when the number of students exceeds 10 ). The course number and number of electives must be output. The query results are sorted in descending order of the number of students. If the number of students is the same, sort by course number in ascending order 45. Search for student number 46 with at least two optional courses, query course number and Course name 47 for all students that have not taken courses, and check for teachers who have not learned "ye ping "the names of the students whose courses are taught are 48, the student ID and average score of the two or more failed courses are 49, and the score of the "004" course is less than 60, students whose student IDs are listed in descending order of scores 50. The score of the "001" course of "002" is deleted.