# Create SQL_ Query database CREATE DATABASE IF NOT EXISTS SQL_QUESTION DEFAULT CHARACTER SET utf8 COLLATE USE SQL_QUESTION; # Using SQL_ Query database # Table building - student table CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); # Schedule building - Curriculum CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); # Table building - teacher table CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); # Construction table - score table CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); # Insert data - insert student table test data insert into Student values('01' , 'Lei Zhao' , '1990-01-01' , 'male'); insert into Student values('02' , 'Qian Dian' , '1990-12-21' , 'male'); insert into Student values('03' , 'Sun Feng' , '1990-05-20' , 'male'); insert into Student values('04' , 'Li Yun' , '1990-08-06' , 'male'); insert into Student values('05' , 'Zhou Mei' , '1991-12-01' , 'female'); insert into Student values('06' , 'Wu Lan' , '1992-03-01' , 'female'); insert into Student values('07' , 'Zheng Zhu' , '1989-07-01' , 'female'); insert into Student values('08' , 'Ju Wang' , '1990-01-20' , 'female'); insert into Course values('01' , 'language' , '02'); insert into Course values('02' , 'mathematics' , '01'); insert into Course values('03' , 'English' , '03'); # Insert data - teacher table test data insert into Teacher values('01' , 'Zhang San'); insert into Teacher values('02' , 'Li Si'); insert into Teacher values('03' , 'Wang Wu'); # Insert data - score sheet test data insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98);
1. Query the information and course scores of students with higher scores in "01" than "02" Query the student number, student name and average score of students whose average score is greater than or equal to 60
select s.*,e.s_score from student s inner join score e on e.s_id = s.s_id inner join (select * from score where c_id='02') k on k.s_id = s.s_id where e.s_score > k.s_score and e.s_id = s.s_id and e.c_id ='01';
2. Query the information of students with scores in SC form
select * from score k left join student s on s.s_id = k.s_id;
3. Query the student number, student name, total number of courses selected and the total score of all courses of all students (null if there is no score)
select s.s_id,s.s_name,count(*) from student s inner join score k on k.s_id = s.s_id group by k.s_id;
4. Check the information of students with grades
select * from score k left join student s on s.s_id = k.s_id;
5. Inquire about the number of teachers surnamed "Li"
select count(*) from teacher where t_name like 'Lee_';
6. Inquire about the information of students who have learned "Zhang San"
select s.* from student s,teacher t,score k,course c where k.c_id = c.c_id and c.t_id = t.t_id and k.s_id = s.s_id and t.t_name = 'Zhang San';
7. Query the information of students who have not studied all courses
8. Query the information of at least one course that is the same as that of the student with student number "01"
9. Query the information of other students whose courses are exactly the same as those of "01"
10. Query the names of students who have not studied any course taught by teacher Zhang San
11. Check the student number, name and average score of students who fail two or more courses
12. Retrieve the student information of "01" course whose score is less than 60 and arranged in descending order
13. Display the scores and average scores of all courses of all students from high to low
14. Query the highest score, lowest score and average score of each subject: displayed in the following form: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate, excellent rate (pass rate > = 60, medium rate: 70-80, excellent rate: 80-90, excellent rate: > = 90)
15. It is required to output the course number and the number of electives. The query results are arranged in descending order according to the number of people. If the number of people is the same, they are arranged in ascending order according to the course number
16. Sort according to the scores of each subject and display the ranking. If the Score is repeated, the ranking vacancy will be reserved
17. Sort according to the scores of each subject and display the ranking. If the Score is repeated, the ranking will be merged
18. Query the total score of students and rank them. If the total score is repeated, the ranking vacancy will be reserved
19. Query the total score of students and rank them. If the total score is repeated, the ranking vacancy will not be reserved
20. Count the number of students in each score segment of each subject: course number, course name, [100-85], [85-70], [70-60], [60-0] and percentage
21. Check the records of the top three in each subject
22. Query the number of students selected for each course
23. Find out the student number and name of students who only take two courses
24. Check the number of boys and girls
25. Query the information of students whose names contain the word "Feng"
26. Check the list of same-sex students with the same name and count the number of students with the same name
27. Check the list of students born in 1990
28. Query the average score of each course, and the results are arranged in descending order according to the average score. If the average score is the same, it is arranged in ascending order according to the course number
29. Query the student number, name and average score of all students whose average score is greater than or equal to 85
30. Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60
31. Check the courses and scores of all students (there are cases where students have no grades and have not selected courses)
32. Check the name, course name and score of any course with a score of more than 70
33. Inquire about failed courses
34. Query the student number and name of students with course number 01 and course score above 80
35. Ask for the number of students in each course
36. The results are not repeated. Query the information of the students with the highest scores and their scores among the students taking the courses taught by teacher Zhang San
37. In case of repeated grades, query the information of the students with the highest grades and their grades among the students taking the courses taught by teacher Zhang San 38. Query the student number, course number and student score of students with the same score in different courses
39. Check the top two with the best performance in each subject
40. Count the number of students in each course (only for courses with more than 5 people)
41. Search the student number of students taking at least two courses
42. Query the information of students who have taken all courses
43. Check the age of each student, only by year
44. If the date of birth is less than the date of birth, the age will be reduced by one
45. Inquire about the students whose birthday is this week
46. Check the students whose birthday is next week
47. Inquire about the students whose birthday is this month
48. Inquire about the students whose birthday is next month