Mysql database classic 50 questions and answers

# 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

Keywords: Java

Added by leonglass on Mon, 07 Feb 2022 09:58:05 +0200