Mysql query interview questions

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;

Result screenshot

(2) To query students surnamed "Li":

select * from student where student_name like 'Lee%';

Result screenshot

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';

Result screenshot:

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;

Result screenshot:

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;

Result screenshot

(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 ))

Result screenshot:

(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'
);

Result screenshot:

(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');

Result screenshot

(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
)

Result screenshot

(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';

Result screenshot

(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'
);

Result screenshot

(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;

Result screenshot

(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;

Result screenshot

(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
	); 

Result screenshot

Tags: Database MySQL SQL

Posted on Wed, 08 Sep 2021 17:57:12 -0400 by mitwess