1, Test data:
1. Data sheet data
(1) student form:
(2) teacher table:
(3) course schedule (partial data):
(4) Partial data of score table:
2. Association relationship
2, Query exercise
1. Simple query
(1) Query all data in student table
select * from student;
(2) To query students surnamed "Li":
select * from student where student_name like 'Lee%';
Note: This is not allowed in the database tuning phase. Using wildcards may invalidate the index and reduce the query efficiency.
(3) Number of male teachers
select count(teacher_id) as Number of male teachers from teacher where teacher_sex = 'male';
Note: when using count, it is better to use a column with an index to improve efficiency. The primary key is a unique index without duplication. As is used as an alias in the statement, and as can be omitted.
(4) Query the highest and lowest scores of each subject and display them in the form of course ID, course highest score and course lowest score.
select course_id as curriculum ID,max(student_score) as Maximum course score,min(student_score) as Minimum course score from score group by course_id;
The above is a simple query for a single table.
2. Complex query
(1) Query the highest and lowest scores of each subject, and display them in the form of course ID, course name, teaching teacher, highest score and lowest score.
select c.course_id as curriculum ID,c.course_name as Course name,t.teacher_name as Instructor, max(s.student_score) as Maximum course score,min(s.student_score) as Minimum course score from score s,course c,teacher t where s.course_id = c.course_id and c.teacher_id = t.teacher_id group by s.course_id;
(2) Query the student number and name of the students who have learned all the lessons taught by "teacher Wei Ping"
Analysis: the first step is to find the course number taught by Mr. Wei Ping
Step 2: we need to query the students who have studied Mr. Wei Ping's courses (they may have only studied one or two but not all)
The third step is to compare the number of courses taught by Wei Ping and the number of courses learned by students. If the number is equal, it is qualified.
SELECT * FROM student WHERE student_id IN ( -- Query qualified student information SELECT student_id -- Query students ID FROM ( -- Query all students who have studied Mr. Wei Ping's class, and take the result as a query table SELECT student_id, count( course_id ) num FROM score WHERE course_id IN ( -- Query all the courses taught by Mr. Wei Ping ID SELECT course.course_id FROM teacher, course WHERE teacher.teacher_name = 'Wei Ping' AND teacher.teacher_id = course.teacher_id ) GROUP BY student_id) stc -- Alias stc ) WHERE stc.num = ( -- The number of teacher Wei Ping's courses learned by the student SELECT count( course.course_id ) FROM teacher, course WHERE teacher.teacher_name = 'Wei Ping' AND teacher.teacher_id = course.teacher_id ))
(3) Query the student number and name of students who have not learned the lessons taught by "Lin Chunyan"
select student.student_id,student.student_name from student where student_id not in ( select score.student_id from score,course,teacher where score.course_id = course.course_id and course.teacher_id = teacher.teacher_id and teacher.teacher_name = 'Lin Chunyan' );
(4) Query the student number and name of students with course ID '320' and '330'
select student.student_id Student number,student.student_name Student name from student,score where student.student_id = score.student_id -- 330 courses and score.course_id = '330' -- I also took 320 courses and exists (select * from score as sc where sc.course_id = '320');
(5) Query the student number and name of students with a course score less than or equal to 60
select student.student_id Student number,student.student_name full name from student where student.student_id in ( select student.student_id from student,score where student.student_id = score.student_id and student_score <= 60 group by student_id )
(6) Query the student number and name of students whose at least one course is the same as that of students with student number "1001"
-- distinct Remove the weight and pay attention to it select after select distinct student.student_id Student number,student.student_name full name from student,score where student.student_id = score.student_id and course_id in (select course_id from score where student_id = '1001') -- The course is the same as 1001 and is not 1001 and score.student_id!='1001';
(7) Query the student numbers of students who have studied all courses with student number 1010
select student_id from score -- The classmate is not himself where student_id<>'1010' and -- Learned the courses that classmate 1 learned course_id in ( select course_id from score where student_id = '1010' ) -- According to students ID grouping group by student_id having count(*) = ( select count(*) from score where student_id = '1010' );
(8) It is arranged from high to low according to the percentage of passing rate of each subject, and displayed in the following form: course number, course name, average score and passing rate.
select course.course_id Course number,course.course_name Course name,avg(score.student_score) Average score, -- case when ifnull(score.student_score,0)>=60 then 1 else 0 end -- case when ifnull It means that if the student's score is empty, it will be replaced by 0. Otherwise, it depends on whether the student's score is greater than 60 points. If yes, it will be counted as 1 head, otherwise it will be counted as 0 100 * sum(case when ifnull(score.student_score,0)>=60 then 1 else 0 end)/count(*) pass rate from course,score where score.course_id = course.course_id group by score.course_id order by pass rate desc;
(9) Query the average score of different courses taught by different teachers from high to low
select teacher.teacher_id Faculty number,teacher.teacher_name Teacher name,course.course_id Course number,avg(score.student_score) Average score from score,teacher,course where score.course_id = course.course_id and course.teacher_id = teacher.teacher_id -- Group by course number group by score.course_id -- Sort by course average in descending order order by avg(score.student_score) desc;
(10) Query the name of the student with the highest score among the students taking the course taught by "duanshuige", the course name and its score
select student.student_name full name,score.student_score fraction,course.course_name Course name from student,score,course,teacher where teacher.teacher_name = 'Duan Shuige' -- Find out what the teacher taught and teacher.teacher_id = course.teacher_id and course.course_id = score.course_id and score.student_id = student.student_id -- The course score is the highest, and the course number is the course taught by teacher Duan and score.student_score = ( select max(student_score) from score where course_id = course.course_id );