Mysql Sql statement exercises (50 questions)

--Create table --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`)
);
--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`)
);
--Score table CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--Insert test data into the student table insert into Student values('01', '赵雷', '1990-01-01', '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05', '周梅', '1991-12-01', '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08', '王菊', '1990-01-20', '女');
--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');

--Insert into Teacher values('01', '张三');
insert into Teacher values('02', '李四');
insert into Teacher values('03', '王五');

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

-- Prepare the conditions and remove ONLY_FULL_GROUP_BY in sql_mode, otherwise an error will be reported in this case:
-- Expression #1 of select list is not in group by clause and contains nonaggregated column 'userinfo.
-- reason:
-- MySQL 5.7.5 and up implements detection of functional dependencies. If the only_full_group_by SQL mode is enabled (which it is by default),
-- then MySQL rejects queries whose select lists, conditions, or order lists refer to unnamed nonaggregate columns in the group by without being functionally dependent on them.
-- (Prior to 5.7.5, MySQL did not detect feature dependencies and only_full_group_by was not enabled by default. See the MySQL 5.6 Reference Manual for a description of pre-5.7.5 behavior.)
-- Execute the following command to view the content of sql_mode.
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;
select @@sql_mode;
-- Change set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 1. Query the information and course scores of students whose scores in course "01" are higher than those in course "02" select st.*,sc.s_score as 'Chinese' ,sc2.s_score 'Mathematics' 
from student st
LEFT JOIN score sc on sc.s_id=st.s_id and sc.c_id='01' 
LEFT JOIN score sc2 on sc2.s_id=st.s_id and sc2.c_id='02' 
where sc.s_score>sc2.s_score

-- 2. Query the information and course scores of students whose scores in course "01" are lower than those in course "02" select st.*,sc.s_score 'Chinese',sc2.s_score 'Mathematics' from student st
LEFT JOIN score sc on sc.s_id=st.s_id and sc.c_id='01'
LEFT JOIN score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'
where sc.s_score<sc2.s_score

-- 3. Query the student ID, name and average score of students whose average score is greater than or equal to 60 points. select st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) cjScore from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id having AVG(sc.s_score)>=60

-- 4. Query the student ID, name and average score of students with average scores less than 60 points-- (including those with scores and those without scores)
select st.s_id,st.s_name,(case when ROUND(AVG(sc.s_score),2) is null then 0 else ROUND(AVG(sc.s_score)) end ) cjScore from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL

-- 5. Query the student ID, student name, total number of courses, and total scores of all courses of all classmates select st.s_id,st.s_name,count(c.c_id),( case when SUM(sc.s_score) is null or sum(sc.s_score)="" then 0 else SUM(sc.s_score) end) from student st
left join score sc on sc.s_id =st.s_id 
left join course c on c.c_id=sc.c_id
group by st.s_id

-- 6. Query the number of teachers with the surname "Li" select t.t_name,count(t.t_id) from teacher t
group by t.t_id having t.t_name like "李%"; 

-- 7. Query the information of students who have studied with teacher "Zhang San" select st.* from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
left join course c on c.c_id=sc.c_id
left join teacher t on t.t_id=c.t_id
 where t.t_name="Zhang San"

-- 8. Query the information of students who have not studied the course taught by teacher "Zhang San" -- Courses taught by teacher Zhang San select c.* from course c left join teacher t on t.t_id=c.t_id where t.t_name="Zhang San"
 -- st.s_id with teacher Zhang San's class scores
 select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name="张三")
 -- The student information of st.s_id not found above, that is, the student information of those who have not studied the class taught by teacher Zhang San select st.* from student st where st.s_id not in(
 select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name="张三")
 )

-- 9. Query the information of students who have studied the course numbered "01" and "02". select st.* from student st 
inner join score sc on sc.s_id = st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id="01"
where st.s_id in (
select st2.s_id from student st2 
inner join score sc2 on sc2.s_id = st2.s_id
inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02"
)


Ideas provided by netizens (awesome~):
SELECT st.*
FROM student st
INNER JOIN score sc ON sc.`s_id`=st.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`c_id`="01" OR sc.`c_id`="02" ,1,0))>1

-- 10. Query the information of students who have studied the course numbered "01" but have not studied the course numbered "02" select st.* from student st 
inner join score sc on sc.s_id = st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id="01"
where st.s_id not in (
select st2.s_id from student st2 
inner join score sc2 on sc2.s_id = st2.s_id
inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02"
)

-- 11. Query the information of students who have not completed all courses -- Too complicated, try another way next time to see if there is a simpler method -- The idea here is to query the ID of students who have completed all courses, and then inline to get the opposite select * from student where s_id not in (
select st.s_id from student st 
inner join score sc on sc.s_id = st.s_id and sc.c_id="01"
where st.s_id in (
select st2.s_id from student st2 
inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id="02"
) and st.s_id in (
select st2.s_id from student st2 
inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id="03"
))
-- The idea comes from the netizen on the first floor, left join, group by student ID and filter out the results whose number is less than the total number of courses in the course table (show me his code), which is much simpler.
select st.* from Student st
left join Score S
on st.s_id = S.s_id
group by st.s_id
having count(c_id)<(select count(c_id) from Course)





-- 12. Query the information of students who have at least one course in the same class as the student with student number "01" select distinct st.* from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
where sc.c_id in (
select sc2.c_id from student st2
LEFT JOIN score sc2 on sc2.s_id=st2.s_id
where st2.s_id = '01'
)

-- 13. Query the information of other students who have taken the same course as student No. 01. select st.* from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id
having group_concat(sc.c_id) = 
(
select group_concat(sc2.c_id) from student st2
LEFT JOIN score sc2 on sc2.s_id=st2.s_id
where st2.s_id = '01'
)

-- 14. Query the names of students who have not studied any course taught by teacher "Zhang San" select st.s_name from student st 
where st.s_id not in (
select sc.s_id from score sc 
inner join course c on c.c_id=sc.c_id
inner join teacher t on t.t_id=c.t_id and t.t_name="张三"
)

-- 15. Query the student ID, name and average score of students who failed two or more courses. select st.s_id,st.s_name,avg(sc.s_score) from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
where sc.s_id in (
select sc.s_id from score sc 
where sc.s_score<60 or sc.s_score is NULL
group by sc.s_id having COUNT(sc.s_id)>=2
)
group by st.s_id

-- 16. Retrieve the student information whose score of course "01" is less than 60, sorted by score in descending order. select st.*,sc.s_score from student st 
inner join score sc on sc.s_id=st.s_id and sc.c_id="01" and sc.s_score<60
order by sc.s_score desc

-- 17. Display the grades and average grades of all courses of all students from high to low by average grades-- You can add round, case when then else end to make the display more perfect select st.s_id,st.s_name,avg(sc4.s_score) "Average score",sc.s_score "Chinese",sc2.s_score "Mathematics",sc3.s_score "English" from student st
left join score sc on sc.s_id=st.s_id and sc.c_id="01"
left join score sc2 on sc2.s_id=st.s_id and sc2.c_id="02"
left join score sc3 on sc3.s_id=st.s_id and sc3.c_id="03"
LEFT JOIN score sc4 on sc4.s_id=st.s_id
group by st.s_id 
order by SUM(sc4.s_score) desc

-- 18. Query the highest, lowest and average scores of each subject: Display in the following format: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, good rate, excellent rate-- Pass is >=60, medium is: 70-80, good is: 80-90, excellent is: >=90
select c.c_id,c.c_name,max(sc.s_score) "highest score",MIN(sc2.s_score) "lowest score",avg(sc3.s_score) "average score" 
,((select count(s_id) from score where s_score>=60 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "pass rate"
,((select count(s_id) from score where s_score>=70 and s_score<80 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "Medium rate"
,((select count(s_id) from score where s_score>=80 and s_score<90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "Excellent Rate"
,((select count(s_id) from score where s_score>=90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) "Excellent Rate"
from course c
LEFT JOIN score sc ON sc.c_id=c.c_id 
left join score sc2 on sc2.c_id=c.c_id 
left join score sc3 on sc3.c_id=c.c_id 
group by c.c_id

-- 19. Sort by subject scores and display rankings (not fully implemented)
-- MySQL does not have a rank function -- Adding @score is to prevent the order from being disrupted after using union all select c1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1 from (select c.c_name,sc.* from course c 
LEFT JOIN score sc ON sc.c_id=c.c_id
where c.c_id="01" order by sc.s_score desc) c1 ,
(select @i:=0) a
union all 
select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 from (select c.c_name,sc.* from course c 
LEFT JOIN score sc ON sc.c_id=c.c_id
where c.c_id="02" order by sc.s_score desc) c2 ,
(select @ii:=0) aa 
union all
select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 from (select c.c_name,sc.* from course c 
LEFT JOIN score sc ON sc.c_id=c.c_id
where c.c_id="03" order by sc.s_score desc) c3;
set @iii=0;


-- 20. Query the total scores of students and rank them select st.s_id,st.s_name
,(case when sum(sc.s_score) is null then 0 else sum(sc.s_score) end)
 from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id order by sum(sc.s_score) desc

-- 21. Query the average scores of different courses taught by different teachers from high to low select t.t_id,t.t_name,c.c_name,avg(sc.s_score) from teacher t 
left join course c on c.t_id=t.t_id 
left join score sc on sc.c_id =c.c_id
group by t.t_id
order by avg(sc.s_score) desc

-- 22. Query the information of the students ranked 2nd to 3rd in all courses and their course scores select a.* from (
select st.*,c.c_id,c.c_name,sc.s_score from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id="01"
order by sc.s_score desc LIMIT 1,2 ) a
union all
select b.* from (
select st.*,c.c_id,c.c_name,sc.s_score from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id="02"
order by sc.s_score desc LIMIT 1,2) b
union all
select c.* from (
select st.*,c.c_id,c.c_name,sc.s_score from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id="03"
order by sc.s_score desc LIMIT 1,2) c

-- 23. Count the number of students in each score range for each subject: course number, course name, [100-85], [85-70], [70-60], [0-60] and percentage select c.c_id, c.c_name 
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=100 and sc.s_score>80)/(select count(1) from score sc where sc.c_id=c.c_id )) "100-85"
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=85 and sc.s_score>70)/(select count(1) from score sc where sc.c_id=c.c_id )) "85-70"
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=70 and sc.s_score>60)/(select count(1) from score sc where sc.c_id=c.c_id )) "70-60"
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=60 and sc.s_score>=0)/(select count(1) from score sc where sc.c_id=c.c_id )) "60-0"
from course c order by c.c_id

-- 24. Query students' average scores and rankings set @i=0;
select a.*,@i:=@i+1 from (
select st.s_id,st.s_name,round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end),2) "Average score" from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id order by sc.s_score desc) a

-- 25. Query the top three scores in each subject select a.* from (
 select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
 LEFT JOIN score sc ON sc.s_id=st.s_id
 inner join course c on c.c_id=sc.c_id and c.c_id='01'
 order by sc.s_score desc LIMIT 0,3) a
union all 
select b.* from (
 select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
 LEFT JOIN score sc ON sc.s_id=st.s_id
 inner join course c on c.c_id=sc.c_id and c.c_id='02'
 order by sc.s_score desc LIMIT 0,3) b
union all
select c.* from (
 select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
 LEFT JOIN score sc ON sc.s_id=st.s_id
 inner join course c on c.c_id=sc.c_id and c.c_id='03'
 order by sc.s_score desc LIMIT 0,3) c

-- 26. Query the number of students who have chosen each course select c.c_id,c.c_name,count(1) from course c 
LEFT JOIN score sc ON sc.c_id=c.c_id
inner join student st on st.s_id=c.c_id
group by st.s_id

-- 27. Query the student ID and name of all students who have only two courses. select st.s_id,st.s_name from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id 
group by st.s_id having count(1)=2

-- 28. Query the number of boys and girls select st.s_sex,count(1) from student st group by st.s_sex

-- 29. Query the student information whose names contain the word "风" select st.* from student st where st.s_name like "%风%";

-- 30. Query the list of students with the same name and sex, and count the number of people with the same name select st.*,count(1) from student st group by st.s_name,st.s_sex having count(1)>1

-- 31. Query the list of students born in 1990. select st.* from student st where st.s_birth like "1990%";

-- 32. Query the average score of each course. The results are sorted in descending order by average score. If the average scores are the same, they are sorted in ascending order by course number. select c.c_id,c.c_name,avg(sc.s_score) from course c
inner join score sc 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 student ID, name and average score of all students whose average score is greater than or equal to 85. select st.s_id,st.s_name,avg(sc.s_score) from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id having avg(sc.s_score)>=85

-- 34. Query the names and scores of students whose course name is "Mathematics" and whose scores are less than 60. select st.s_id,st.s_name,sc.s_score from student st
inner join score sc on sc.s_id=st.s_id and sc.s_score<60
inner join course c on c.c_id=sc.c_id and c.c_name ="Mathematics" 

-- 35. Check the courses and scores of all students;
select st.s_id,st.s_name,c.c_name,sc.s_score from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
left join course c on c.c_id =sc.c_id
order by st.s_id,c.c_name

-- 36. Query the name, course name and score of any course with a score above 70. select st2.s_id,st2.s_name,c2.c_name,sc2.s_score from student st2
LEFT JOIN score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id 
where st2.s_id in(
select st.s_id from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id 
group by st.s_id having min(sc.s_score)>=70)
order by s_id

-- 37. Query failed courses select st.s_id,c.c_name,st.s_name,sc.s_score from student st
inner join score sc on sc.s_id=st.s_id and sc.s_score<60
inner join course c on c.c_id=sc.c_id 

-- 38. Query the student ID and name of the student whose course number is 01 and whose course score is above 80 points. select st.s_id,st.s_name,sc.s_score from student st
inner join score sc on sc.s_id=st.s_id and sc.c_id="01" and sc.s_score>=80

-- 39. Find the number of students in each course select c.c_id,c.c_name,count(1) from course c
inner join score sc on sc.c_id=c.c_id
group by c.c_id

-- 40. Query the information of the student with the highest score among the students who have chosen the course taught by teacher "Zhang San" and their score select st.*,c.c_name,sc.s_score,t.t_name from student st
inner join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id 
inner join teacher t on t.t_id=c.t_id and t.t_name="张三"
order by sc.s_score desc
limit 0,1

-- 41. Query the student ID, course ID, and student score of students with the same scores in different courses. select st.s_id,st.s_name,sc.c_id,sc.s_score from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
left join course c on c.c_id=sc.c_id
where (
select count(1) from student st2 
LEFT JOIN score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id
where sc.s_score=sc2.s_score and c.c_id!=c2.c_id 
)>1

-- 42. Query the top two students with the best scores in each subject. select a.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id="01"
order by sc.s_score desc limit 0,2) a
union all
select b.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id="02"
order by sc.s_score desc limit 0,2) b
union all
select c.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
LEFT JOIN score sc ON sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id="03"
order by sc.s_score desc limit 0,2) c
 
-- Reference (more accurate and beautiful):
 select a.s_id,a.c_id,a.s_score from score a
 where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 order by a.c_id

-- 43. Count the number of students taking each course (only courses with more than 5 students are counted). The course number and the number of students taking the course are required to be output, and the query results are sorted in descending order by the number of students.
-- If the number of students is the same, sort by course number in ascending order select sc.c_id,count(1) from score sc
left join course c on c.c_id=sc.c_id
group by c.c_id having count(1)>5
order by count(1) desc,sc.c_id asc

-- 44. Retrieve the student ID of a student who has taken at least two courses. select st.s_id from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id having count(1)>=2

-- 45. Query the information of students who have taken all courses select st.* from student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
group by st.s_id having count(1)=(select count(1) from course)

-- 46. Query the age of each student select st.*,timestampdiff(year,st.s_birth,now()) from student st

-- 47. Query students who have birthdays this week -- There may be a problem here. The week function takes the week number of the current year. 2017-12-12 is the 50th week and 2018-12-12 is the 49th week. You can take the month, day, and day of the week (%w).
 -- Then judge whether this week will continue to the next month. It is too troublesome. I don't know how to write select st.* from student st 
where week(now())=week(date_format(st.s_birth,'%Y%m%d'))

-- 48. Query students whose birthdays are next week select st.* from student st 
where week(now())+1=week(date_format(st.s_birth,'%Y%m%d'))

-- 49. Query students whose birthdays are this month select st.* from student st 
where month(now())=month(date_format(st.s_birth,'%Y%m%d'))

-- 50. Query students whose birthdays are next month -- Note: When the current month is 12, use month(now())+1 to get 13 instead of 1. You can use timestampadd() function or mod to select st.* from student st 
where month(timestampadd(month,1,now()))=month(date_format(st.s_birth,'%Y%m%d'))
-- or select st.* from student st where (month(now()) + 1) mod 12 = month(date_format(st.s_birth,'%Y%m%d'))