Building tables and inserting data

-- Building tables
-- 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`)
);
-- Class Schedule Card
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- Teacher table
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- Achievement Sheet
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

-- Insert Test Data from Student Table
insert into Student values('01' , 'Zhao Lei','1990-01-01','male'); 
insert into Student values('02' , 'Money and electricity','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' , 'Wulan','1992-03-01','female'); 
insert into Student values('07' , 'Zheng Zhu','1989-07-01','female'); 
insert into Student values('08' , 'Chrysanthemum','1990-01-20','female');
-- Course schedule test data
insert into Course values('01' , 'Chinese' , '02'); 
insert into Course values('02' , 'Mathematics' , '01'); 
insert into Course values('03' , 'English' , '03');
-- Teacher table test data
insert into Teacher values('01' , 'Zhang San'); 
insert into Teacher values('02' , 'Li Si'); 
insert into Teacher values('03' , 'King Five');
-- Achievement 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);

Title List

# 01 Query information and course scores of students with higher grades in 01 than 02

# 02 Query information and course scores of students with lower scores in "01" than in "02" (Topic 1 is higher)

# 03 Query the student number, name and average score of students whose average score is greater than or equal to 60

# 04 Query the student number and name and average score of students with average score less than 60 (both with and without results)

# 05 Query all classmates'student numbers, student names, total number of courses selected, total results of all courses

# 06 Query the number of teachers with Li's surname

# 07 Query the information of the students who have learned the lessons given by Teacher Zhang San

# 08 Find the students who have not studied Teacher Zhang San's course

# 09 Query student information for course number 01 and 02

# 10 Query the information of students who have studied 01 but have not studied 02 (note the difference between the above 9 questions)

# 11 Query information about classmates who did not complete the course

# 12 Query the information of at least one student in the same course as the one with number 01

# 13 Query and 01 classmates'information about the exact same classes

# 14 Query the names of students who have not taken any of the courses taught by Teacher Zhang San

# 15 Query the number, name and average score of two or more failing classes

# 16 Retrieve student information in descending order with 01 course scores less than 60

# 17 Show all students'results and average results for all courses in descending order from high to low average

# 18 Query the highest, lowest and average scores of each subject: show in the following forms: course ID, course name, highest, lowest, average, pass rate, medium rate, excellent rate, excellent rate; Pass: >=60, medium: 70-80, excellent: 80-90, excellent: >=90

# 19 Sort by subject and show ranking

# 20 Query students'total scores and rank them

# 21 Query the average score of different courses taught by different teachers from high to low

# 22 Query the information of the students who are ranked 2nd to 3rd in all courses and the results of the course

# 23 Count the number of students in each grade of each subject: course number, course name, [100-85], [85-70], [70-60], [0-60] and percentage

# 24 Query students'average scores and rankings

# 25 Query the top three records of each subject

# 26 Query the number of students selected for each course

# 27 Query out the number and name of all the students in only two courses

# 28 Query the number of male and female students

# 29 Query for information about students whose names contain typeface

# 30 Query the list of students with the same name and count the number of students with the same name

# 31 Query information about students born in 1990

# 32 Query the average results of each course, and the results are in descending order according to the average results; With the same average score, by course number c_id ascending

# 33 Query the number, name and average score of all students whose average score is greater than or equal to 85

# 34 Query the names and scores of students with math course names and scores below 60

# 35 Query all students'courses and scores

# 36 Query the name, course name and score of any course with a score of more than 70

# 37 Query failed courses

# 38 Query the number and name of a student whose course number is 01 and whose course grade is greater than or equal to 80

# 39 Number of students per course

# 40 Query the information of the students with the highest scores and their results among the students taking the course given by Teacher Zhang San

# 41 Query the student number, the course number, the student's results of the same students in different courses

# 42 Query top two for each achievement

# 43 Count the number of students taking each course (more than 5 courses are counted). Require output of course number and number of elections, query results are in descending order by number of people, if the number is the same, in ascending order by course number

# 44 Retrieve student numbers for at least two courses

# 45 Query student information for all courses

# 46 Query the age of each student: according to the date of birth, the current month day is less than the month day of birth year, the age is reduced by 1

# 47 Query students for birthdays this week

# 48 Query students who have birthdays next week

# 49 Query students who passed this month

# 50 Query students who pass next month

01

Query information and course scores of students with higher grades in 01 than 02

essence	Row operations: comparison operations
Method	1. Direct comparison: subquery 2. Make it into two columns: self-association (pay attention to explicit joins to speed up queries)
Remarks	Achievements in any subject are not to be compared

# 1. Direct comparison: subquery
select s.*,sc.s_score
from (
select sc1.s_id,sc1.s_score
from score sc1
where sc1.c_id='01'
and sc1.s_score>(select s_score from score where s_id=sc1.s_id and c_id='02')) sc join student s on sc.s_id=s.s_id


# 2. Make two columns: self-association (note explicit joins, speed up queries)
select s.*,sc3.s_score 
from
	(
	select sc1.s_id,sc1.s_score 
	from
		( select s_id, s_score from score where c_id = '01' ) sc1
		join ( select s_id, s_score from score where c_id = '02' ) sc2 on sc1.s_id = sc2.s_id and sc1.s_score > sc2.s_score 
	) sc3
	join student s on sc3.s_id = s.s_id

# 2.2 Direct Autocorrelation
select s.*,sc1.s_score
from student s 
join score sc1 on s.s_id=sc1.s_id and sc1.c_id='01'
join score sc2 on s.s_id=sc2.s_id and sc2.c_id='02' and sc1.s_score>sc2.s_score

02

Query information and course scores of students with lower scores in 01 than 02 (Topic 1 is higher)

Ditto

03

Query student numbers, names and average scores of students with average scores greater than or equal to 60

essence	Row aggregation, post-having filtering
Method	Direct aggregation followed by having to filter aggregation functions

select sc.s_id,s.s_name,round(avg(sc.s_score),0) as score
from score sc join student s on sc.s_id=s.s_id
group by sc.s_id
having score>=60

# Additional questions: students with total score over 200
select sc.s_id,s.s_name,sum(sc.s_score) as score
from score sc join student s on sc.s_id=s.s_id
group by sc.s_id
having score>200

04

Query the student number and name and average score of students with average scores less than 60 (both with and without results)

Note: Ctrl+R executes SQL statements

essence	Row aggregation, post-having filtering
Method	Direct aggregation, have filtering after aggregation, note the main table
Remarks	1. The main table is the student table, which includes the need for no grades, so left join 2. where can be considered as an aggregate function: null judgement, comparison operation, subquery 3. Use ifnull to handle empty/left join cases

# null judgment
select s.s_id,s.s_name,round(avg(sc.s_score),0) as score
from student s 
left join score sc on s.s_id=sc.s_id 
group by s.s_id
having score is null or score < 60

# ifnull function: more compatible
select s.s_id,s.s_name,round(avg(ifnull(sc.s_score,0)),0) as score
from student s 
left join score sc on s.s_id=sc.s_id 
group by s.s_id
having score < 60

05

Query all classmates'student numbers, names, total number of courses selected, total results of all courses

essence	Row Aggregation
Method	Direct aggregation, attention to object scope
Remarks	The main table is the student table. Consider that there are no courses or no grades

#This topic sql
select s.s_id,s.s_name,
count(distinct sc.c_id) as c_num,
sum(sc.s_score) as score
from student s left join score sc on s.s_id=sc.s_id 
group by s.s_id

#If post-aggregation filtering is required, use the if and ifnull functions
#If and if NULL are good functions and are very convenient to handle null situations. Note: ifnull is used with the value operation function, and if is used with the count function
select s.s_id,s.s_name,
if(sc.s_id is null,0,count(distinct sc.c_id)) as c_num,
sum(ifnull(sc.s_score,0)) as score
from student s left join score sc on s.s_id=sc.s_id 
group by s.s_id

06

Query the number of teachers with Li's surname

essence	Row Filter Aggregation
Method	Filter Count
Remarks

#Question: Is like optimal?
#like wildcards
SELECT count(DISTINCT t_id) as t_num
FROM Teacher
WHERE t_name like 'plum%'

#Truncate last name and judge
SELECT count(DISTINCT t_id) as t_num
FROM Teacher
WHERE SUBSTR(t_name,1,1)='plum'

07

Query the information of the students who have learnt from Mr. Zhang San

essence	Row Filtering: Level by Level
Method	Table Associated or Subquery
Remarks

# Step by step subqueries for better understanding
SELECT s.*
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id in(SELECT c_id FROM Course WHERE t_id in(SELECT t_id FROM Teacher WHERE t_name='Zhang San'))


# Table Associations
select s.*
from score sc
join course c on c.c_id=sc.c_id
join teacher t on t.t_id=c.t_id and t.t_name='Zhang San'
join student s on sc.s_id=s.s_id

08

Find out the students who have not studied Teacher Zhang San's course

essence	Reverse row filtering
Method	Filter according to what you've learned, and end up with the opposite left join or right join, and is null
Remarks

# Table Associations
select s.*
from score sc 
join course c on c.c_id=sc.c_id
join teacher t on t.t_id=c.t_id and t.t_name='Zhang San'
right join student s on sc.s_id=s.s_id
where sc.s_id is null

# not in, that's a good idea
select *
from student
where s_id not in(
	select sc.s_id
	from score sc 
	join course c on c.c_id=sc.c_id
	join teacher t on t.t_id=c.t_id and t.t_name='Zhang San'
	)

09

Query student information for course number 01 and 02

essence	Row Filtering: Take Intersection
Method	Auto-related or subquery
Remarks

# Autocorrelation
SELECT s.*
FROM Score sc1
JOIN Score sc2 on sc1.s_id=sc2.s_id and sc1.c_id='01' and sc2.c_id='02'
JOIN Student s on sc1.s_id=s.s_id

# Subquery
SELECT s.*
FROM Score sc1
JOIN Student s on sc1.s_id=s.s_id
WHERE sc1.c_id='01'
and sc1.s_id in(SELECT s_id FROM Score sc2 WHERE sc1.s_id=sc2.s_id and sc2.c_id='02')

10

Query the information of students who have taken course 01 but have not taken course 02 (note the difference between the above 9 questions)

essence	Row Filtering: Take Intersection
Method	Subquery, cannot use self-association, because a cid record cannot be judged with or without
Remarks

# Subquery
SELECT s.*
FROM Score sc1
JOIN Student s on sc1.s_id=s.s_id
WHERE sc1.c_id='01'
and sc1.s_id not in(SELECT s_id FROM Score sc2 WHERE sc1.s_id=sc2.s_id and sc2.c_id='02')

11

Query information about classmates who did not complete the course

essence	Row aggregation, post-having filtering
Method	Direct aggregation, note table object range
Remarks	where can be seen as an aggregate function: null judgment, comparison operation, subquery

select s.*
from student s
left join score sc on sc.s_id=s.s_id 
group by s.s_id
having count(distinct sc.c_id)<(select count(*) from course)

#Discovery screening, first select the students of all courses
SELECT *
FROM Student
WHERE s_id not in(SELECT s_id FROM Score GROUP BY s_id HAVING count(c_id)=(SELECT count(*) FROM Course))

12

Query for information about at least one class that is the same as the class number 01

essence	Row Filtering: Subquery
Method	1. No. 01 Course c_ Id01 2, number not equal to 01 and c_id in c_id01
Remarks

select s.*
from score sc 
join student s on sc.s_id=s.s_id and sc.s_id<>'01'
where sc.c_id in(select c_id from score where s_id='01')
group by s.s_id

13***

Query the information of the same classmates as 01

essence	Row Filtering: Subquery
Method	1. Number 01 Course 2, the same course as 01 and the number of courses equals 01 Course Number
Remarks	The key is the way of thinking

group_concat() function usage

group_concat([DISTINCT] Field [Order BY ASC/DESC sort field] [Separator 'Separator'])

# Method 1 Lessons of the same number
select s.*
from score sc 
join student s on sc.s_id=s.s_id
#Equal number of courses
where sc.s_id in(SELECT s_id FROM Score WHERE s_id <>'01' GROUP BY s_id HAVING count(distinct c_id)=(select count(distinct c_id) from score where s_id='01'))
#Same number of courses
and c_id in(select c_id from score where s_id='01')
group by s.s_id
having count(distinct sc.c_id)=(select count(distinct c_id) from score where s_id='01')


# Method 2 uses group_concat() function, if the number of records is large, method 1 is recommended
select s.*
from student s
join score sc on s.s_id=sc.s_id and sc.s_id<>'01'
group by s.s_id
having group_concat(sc.c_id order by sc.c_id)=
(select group_concat(c_id order by c_id) from score where s_id='01' group by s_id)

14

Query the names of students who have not taken any of the courses taught by Teacher Zhang San

essence	Reverse row filtering
Method	Teacher Zhang San does not meet the requirements after any course
Remarks	Note that the table is for all students, and the main table is for students, because for s_id filter, so no longer associated with score

select s_name
from student
where s_id not in(
select s_id from score
where c_id in(select c_id from course where t_id=(select t_id from teacher where t_name='Zhang San')))

15*

Query the number, name and average score of two or more failing classes

essence	Row Filtering and Aggregation
Method	1. Row Screening: Pass or Fail 2, Fail Courses 3, Have Screening
Remarks	If it is repaired, it will pass or fail, so score is the main table

# Mode 1
select sc.s_id,s.s_name,round(avg(sc.s_score),0) as avg_score
from score sc
join student s on sc.s_id=s.s_id
group by sc.s_id
having sum(if(sc.s_score<60,1,0))>=2

# Mode 2 queries smaller tables
select sc.s_id,s.s_name,round(avg(sc.s_score),0) as avg_score
from score sc
join student s on sc.s_id=s.s_id and sc.s_score<60
group by sc.s_id
having count(*)>=2

16

Retrieve student information in descending order with 01 course scores less than 60

essence	Row Filtering and Sorting
Method	Score <60, descending
Remarks	Sort Function Difference 1,row_number()over: 123 2,rank()over(): 113 3,dense_rank()over(): 112

SELECT s.*
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id='01'
AND s_score<60
ORDER BY s_score DESC

17

Show all course results and average results for all students in descending order from high to low average

essence	Table Association and Sorting
Method	Find the main table, find the sort field
Remarks	How to associate sort fields with other fields is the key point The average score here is the average score for the selected course

# Vertical table
SELECT s.s_name,c.c_name,sc.s_score,sc2.avg_score
FROM Student s 
JOIN Course c 
LEFT JOIN Score sc ON s.s_id=sc.s_id AND c.c_id=sc.c_id
LEFT JOIN (SELECT s_id,ROUND(AVG(s_score),0) AS avg_score FROM Score GROUP BY s_id) sc2 ON s.s_id=sc2.s_id
ORDER BY sc2.avg_score DESC

#Horizontal table
SELECT s.s_name,
MAX(CASE sc.c_id WHEN '01' THEN sc.s_score END) AS 'Chinese',
MAX(CASE sc.c_id WHEN '02' THEN sc.s_score END) AS 'Mathematics',
MAX(CASE sc.c_id WHEN '03' THEN sc.s_score END) AS 'English',
sc2.avg_score as 'Average Scores'
FROM Student s 
JOIN Course c 
LEFT JOIN Score sc ON s.s_id=sc.s_id AND c.c_id=sc.c_id
LEFT JOIN (SELECT s_id,ROUND(AVG(s_score),0) AS avg_score FROM Score GROUP BY s_id) sc2 ON s.s_id=sc2.s_id
GROUP BY s.s_name,sc2.avg_score
ORDER BY sc2.avg_score DESC

18

Query the highest, lowest and average scores of each subject: show in the following forms: course ID, course name, highest, lowest, average, pass rate, medium rate, excellent rate, excellent rate; Pass: >=60, medium: 70-80, excellent: 80-90, excellent: >=90

essence	Table Association and Aggregation
Method	Main table course, left join score, maximum, mean, and grouping
Remarks	Notice the decimal and percentage symbols

SELECT c.c_id AS 'curriculum id',c.c_name AS 'curriculum name',
MAX(sc.s_score) AS 'Top Score',
MIN(sc.s_score) AS 'Minimum score',
ROUND(AVG(sc.s_score),2) AS 'Average',
CONCAT(ROUND(SUM(if(sc.s_score>=60,1,0))/COUNT(sc.s_id)*100,2),'%') AS 'pass rate',
CONCAT(ROUND(SUM(if(sc.s_score>=70 AND sc.s_score<80,1,0))/COUNT(sc.s_id)*100,2),'%') AS 'Medium Rate',
CONCAT(ROUND(SUM(if(sc.s_score>=80 AND sc.s_score<90,1,0))/COUNT(sc.s_id)*100,2),'%') AS 'Excellent rate',
CONCAT(ROUND(SUM(if(sc.s_score>=90,1,0))/COUNT(sc.s_id)*100,2),'%') AS 'Excellence rate'
FROM Course c
LEFT JOIN Score sc ON c.c_id=sc.c_id
GROUP BY c.c_id

19

Sort by subject and show ranking

essence	Grouping Sort
Method	Select Sort Base and Rank
Remarks

SELECT c.c_name,s.s_name,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS 'ranking'
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
JOIN Course c ON sc.c_id=c.c_id

20

Query and rank students'total results

essence	Sort after row aggregation
Method	sum, sort
Remarks

SELECT s.s_name,SUM(sc.s_score) AS 'Total results',
ROW_NUMBER()OVER(ORDER BY SUM(sc.s_score) DESC) AS 'ranking'
FROM Student s 
LEFT JOIN Score sc ON s.s_id=sc.s_id
GROUP BY s.s_name

21

Query the average score of different courses taught by different teachers from high to low

essence	Aggregate reordering after table Association
Method	The main table is the teacher table and the course Cartesian product of the curriculum table, the left join score, and order by after average
Remarks

SELECT t.t_name,c.c_name,ROUND(AVG(sc.s_score),2) AS 'Average'
FROM Teacher t
JOIN Course c ON t.t_id=c.t_id
LEFT JOIN Score sc ON c.c_id=sc.c_id
GROUP BY t.t_name,c.c_name
ORDER BY AVG(sc.s_score) DESC

22

Query the information of students who are ranked 2nd to 3rd in all courses and the results of the course

essence	Filter after row sorting
Method	Top 2 and 3 for all courses
Remarks

SELECT r.c_name,r.rank_num,s.s_name,r.s_score
FROM
(SELECT c.c_name,sc.s_id,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num
FROM Course c
LEFT JOIN Score sc ON c.c_id=sc.c_id)r
JOIN Student s ON r.s_id=s.s_id and r.rank_num in(2,3)

23

Count the number of students in each grade of each subject: course number, course name, [100-85], [85-70], [70-60], [0-60] and percentage

essence	Row Grouping Aggregation
Method	First basic: subjects, results, then group count
Remarks

SELECT sc.c_id,c.c_name,
SUM(if(sc.s_score>=85,1,0)) AS '[100-85]Number of people',
SUM(if(sc.s_score>=70 AND sc.s_score<85,1,0)) AS '[85-70]Number of people',
SUM(if(sc.s_score>=60 AND sc.s_score<70,1,0)) AS '[70-60]Number of people',
SUM(if(sc.s_score<60,1,0)) AS '[0-60]Number of people',
CONCAT(ROUND(SUM(if(sc.s_score>=85,1,0))/COUNT(*)*100,2),'%') AS '[100-85]Percentage',
CONCAT(ROUND(SUM(if(sc.s_score>=70 AND sc.s_score<85,1,0))/COUNT(*)*100,2),'%') AS '[85-70]Percentage',
CONCAT(ROUND(SUM(if(sc.s_score>=60 AND sc.s_score<70,1,0))/COUNT(*)*100,2),'%') AS '[70-60]Percentage',
CONCAT(ROUND(SUM(if(sc.s_score<60,1,0))/COUNT(*)*100,2),'%') AS '[0-60]Percentage'
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
GROUP BY sc.c_id

24

Query students'average scores and rankings

essence	Sort after row aggregation
Method	Average results, post-order
Remarks

SELECT s.s_name,ROUND(AVG(sc.s_score),2) AS 'Total results',
ROW_NUMBER()OVER(ORDER BY AVG(sc.s_score) DESC) AS 'ranking'
FROM Student s 
LEFT JOIN Score sc ON s.s_id=sc.s_id
GROUP BY s.s_name

25

Query the top three records of each subject

essence	Filter after row sorting
Method	Ranking of Achievements in Subjects, Top 3
Remarks

SELECT r.c_name,r.rank_num,s.s_name,r.s_score
FROM
(SELECT c.c_name,sc.s_id,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num
FROM Course c
LEFT JOIN Score sc ON c.c_id=sc.c_id)r
JOIN Student s ON r.s_id=s.s_id and r.rank_num<=3

26

Query the number of students selected for each course

essence	Row Aggregation
Method	Achievement Sheet s_by Subject ID count
Remarks

SELECT c.c_name,COUNT(DISTINCT sc.s_id) AS s_num
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
GROUP BY c.c_name

27

Query the number and name of all the students in only two courses

essence	Row Aggregation
Method	C_by Student ID count
Remarks

SELECT s.s_id,s.s_name
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT sc.c_id)=2

28

Query the number of male and female students

essence	Row Filter Aggregation
Method	Student table student counts by gender
Remarks

SELECT s_sex,COUNT(DISTINCT s_id) AS 'Number of people'
FROM Student
GROUP BY s_sex

29

Query for information about students whose names contain typeface

essence	Fuzzy Filtering
Method
Remarks

SELECT * 
FROM Student
WHERE s_name LIKE '%wind%'

30

Query the list of students with the same name and count the number of students with the same name

essence	Grouping Aggregation
Method	having filter after grouping count
Remarks

SELECT s1.s_name,s1.s_sex,s2.num AS 'Number of people of the same name'
FROM Student s1 
JOIN (SELECT s_name,COUNT(*) AS num FROM Student GROUP BY s_name HAVING COUNT(*)>=2)s2 ON s1.s_name=s2.s_name
GROUP BY s1.s_name,s1.s_sex
HAVING COUNT(*)>=2

31

Query information about students born in 1990

essence	Row Filtering
Method	Get birth year and filter
Remarks	DATE() converts varchar to date The YEAR() function returns a yearly value from a specified date or time, ranging from 1000 to 9999, and the YEAR() function returns 0 if the date is zero

SELECT *
FROM Student
WHERE YEAR(DATE(s_birth))=1990

32

Query the average results of each course, and the results are sorted in descending order according to the average results. With the same average score, by course number c_id ascending

essence	Sort after row aggregation
Method	Average courses, then sort them
Remarks	Possible aggregate functions after order by

SELECT c.c_name,ROUND(AVG(sc.s_score),2) AS 'Average'
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
GROUP BY c.c_id
ORDER BY AVG(sc.s_score) DESC,c.c_id ASC

33

Query the number, name, and average score of all students whose average score is greater than or equal to 85

essence	Row Aggregation Post-Filtering
Method	Average student performance, then filter
Remarks

SELECT s.s_id,s.s_name,ROUND(AVG(sc.s_score),2) AS 'Average Scores'
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score)>=85

34

Query the names and scores of students with math course names and scores below 60

essence	Row Filtering
Method	Filter on demand, use subqueries
Remarks

SELECT s.s_name,sc.s_score
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id=(SELECT c_id FROM Course WHERE c_name='Mathematics')
AND s_score<60

35

Query all students'courses and scores

essence	Table post-association aggregation
Method	The Cartesian product of the student and curriculum tables, which captures all students and all courses, then associates the result tables to get results, and finally aggregates them Note: The report form is narrow, so the outer layer needs aggregation
Remarks

SELECT s.s_name,
SUM(CASE c.c_name WHEN 'Chinese' THEN sc.s_score ELSE 0 END) AS 'Chinese',
SUM(CASE c.c_name WHEN 'Mathematics' THEN sc.s_score ELSE 0 END) AS 'Mathematics',
SUM(CASE c.c_name WHEN 'English' THEN sc.s_score ELSE 0 END) AS 'English',
SUM(sc.s_score) AS 'Total score'
FROM Student s
JOIN Course c
LEFT JOIN Score sc ON s.s_id=sc.s_id AND c.c_id=sc.c_id
GROUP BY s.s_name

36

Query the name, course name, and score of any course with a score of more than 70

essence	Row Filtering
Method	Score Screening>70
Remarks

SELECT s.s_name,c.c_name,sc.s_score
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
JOIN Course c ON sc.s_id=c.c_id
WHERE s_score>70

37

Query failed courses

essence	Row Filtering
Method
Remarks

SELECT sc.c_id,c.c_name,sc.s_score
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
WHERE sc.s_score<60

38

Query the number and name of a student whose course number is 01 and whose course grade is greater than or equal to 80

essence	Row Filtering
Method
Remarks

SELECT s.s_id,s.s_name
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id='01'
AND s_score>=80

39

Number of students per course

essence	Row Aggregation
Method
Remarks

SELECT c.c_name,count(DISTINCT sc.s_id) AS 'Number of people'
FROM Score sc
JOIN Course c ON sc.c_id=c.c_id
GROUP BY c.c_name

40

Query the information of the students with the highest scores and their results among the students taking the course given by Teacher Zhang San

essence	Row Filtered Aggregation
Method	The students with the highest scores (max, order by, row_number()over) in the course given by Mr. Zhang San 1. max requires associated table matching 2. order by and limit 1 are recommended 3. row_number()over requires outer table restriction rank_num=1
Remarks

SELECT s.*,s_score
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id=(SELECT c_id FROM Course WHERE t_id=(SELECT t_id FROM Teacher WHERE t_name='Zhang San'))
ORDER BY s_score DESC
LIMIT 1

41

Query student numbers, course numbers, student results of students with the same results in different courses

essence	Row Filtering
Method	Qualified Achievements: Grouped by Achievements, Counted by Course id and >2
Remarks

SELECT *
FROM Score
WHERE s_score in(SELECT s_score FROM Score GROUP BY s_score HAVING COUNT(DISTINCT c_id)>=2)

42

Query the top two for each course

essence	Filter after row sorting
Method	The first two are grouped here, requiring the window function row_number()over
Remarks

SELECT r.*
FROM(
SELECT c_name,s_id,s_score,
ROW_NUMBER()OVER(PARTITION BY c_name ORDER BY s_score DESC) as rank_num
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id)r
WHERE r.rank_num<=2

43

Count the number of students taking each course (more than 5 courses are counted). Require output of course number and number of elections, query results are in descending order by number of people, if the number is the same, in ascending order by course number

essence	Sort after row aggregation
Method
Remarks

SELECT c_id,COUNT(DISTINCT s_id) AS 'Number of people'
FROM Score
GROUP BY c_id
ORDER BY 'Number of people' DESC,c_id ASC

44

Retrieve student numbers for at least two courses

essence	Row Aggregation Post-Filtering
Method
Remarks

SELECT s_id
FROM Score
GROUP BY s_id
HAVING COUNT(DISTINCT c_id)>=2

45

Query student information for all courses

essence	Row Aggregation Post-Filtering
Method
Remarks

SELECT s.*
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT c_id)=(SELECT COUNT(*) FROM Course)

46

Query the age of each student: according to the date of birth, the current month day is less than the month day of birth year, the age is reduced by 1

essence	Row Filtering: Date-Year-Month-Day
Method	year(), month(), day()
Remarks

SELECT *,
CASE 
	WHEN MONTH(NOW())<MONTH(DATE(s_birth)) THEN
		YEAR(NOW())-YEAR(DATE(s_birth))-1
	WHEN MONTH(NOW())=MONTH(DATE(s_birth)) AND DAY(NOW())<DAY(DATE(s_birth)) THEN
		YEAR(NOW())-YEAR(DATE(s_birth))-1
	ELSE
		YEAR(NOW())-YEAR(DATE(s_birth))
END AS age
FROM Student

47

Query students for birthdays this week

essence	Row Filtering: Date-Week
Method	week()
Remarks

SELECT *
FROM Student
WHERE WEEK(DATE(s_birth))=WEEK(NOW())

48

Query students for birthdays next week

essence	Row Filtering: Date-Week
Method	week()
Remarks

SELECT *
FROM Student
WHERE WEEK(DATE(s_birth))=WEEK(NOW())+1

49

Query students who have passed this month

essence	Row Filtering: Date-Month
Method
Remarks

SELECT *
FROM Student
WHERE MONTH(DATE(s_birth))=MONTH(NOW())

50

Query the students who passed the next month

essence	Row Filtering: Date-Month
Method
Remarks

SELECT *
FROM Student
WHERE MONTH(DATE(s_birth))=MONTH(NOW())+1