SQL classic exercises (teacher, student, grade)

Time:2020-11-24

SQL classic exercises (MySQL version)

 

Link to the original text: Classic SQL exercises (MySQL version)

 
   I did it myself, and I feel that I have benefited a lot, so I will share it with you. Among them, due to my poor mastery of SQL functions, 15-19 questions have not been done, which is the original reference. If there are errors or better SQL statements, you can comment directly below.
 

Create data table

Student:

create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
Insert into student values ('01 ','zhao Lei','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 ',' Wang Ju ',' 1990-01-20 ','female');

 
SC score sheet:

create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

 
Course:

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
Insert into course values ('01 ','Chinese','02 ');
Insert into course values ('02 ','mathematics','01');
Insert into course values ('03 ','English','03 ');

 
Teacher:

create table Teacher(Tid varchar(10),Tname varchar(10));
Insert into teacher values ('01 ','zhang San');
Insert into teacher values ('02 ','li Si');
Insert into teacher values ('03 ','wangwu')

 
The relationship between tables is as follows:
SQL classic exercises (teacher, student, grade)
 
 

practice

1. Query the information and course scores of students with higher scores in “01” course than “02” course

select student.*,a.score from student,(select * from sc where cid=01) as a, (select * from sc where cid=02) as b where student.sid=a.sid and student.sid=b.sid and a.score>b.score;
+------+--------+---------------------+------+-------+
| Sid  | Sname  | Sage                | Ssex | score |
+------+--------+---------------------+------+-------+
|02 | Qian Dian | 1990-12-21 00:00:00 | male | 70.0|
|04 | Li Yun | 1990-08-06 00:00:00 | male | 50.0|
+------+--------+---------------------+------+-------+
2 rows in set (0.00 sec)

 

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

select student.sid,sname,avg(a.score) as avg from student,(select * from sc) as a where student.sid=a.sid group by student.sid having avg>=60;
+------+--------+----------+
| sid  | sname  | avg      |
+------+--------+----------+
|01 Zhao Lei 89.66667|
|02 | Qian Dian | 70.00000|
|03 | sun Feng | 80.00000|
|05 Zhou Mei 81.50000|
|07 Zheng Zhu 93.50000|
+------+--------+----------+
5 rows in set (0.00 sec)

 

3. Query the information of students with scores in SC form

select * from student where sid in (select sid from sc where score is not null);
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|01 | Zhao Lei | 1990-01-01 00:00 | male|
|02 | Qian Dian | 1990-12-21 00:00:00 | male|
|03 | sun Feng | 1990-05-20 00:00:00 | male|
|04 | Li Yun | 1990-08-06 00:00:00 | male|
|05 Zhou Mei 1991-12-01 00:00:00 female|
|06 | Wu Lan | 1992-03-01 00:00 | female|
|07 | Zheng Zhu | 1989-07-01 00:00 | female|
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

 

4. Query all students’ student numbers, students’ names, total number of selected courses, and total scores of all courses (the display without grades is null)

select student.sid,student.sname,count(sc.sid) as count,sum(sc.score) as sum from student left join sc on student.sid=sc.sid group by student.sid;
+------+--------+-------+-------+
| sid  | sname  | count | sum   |
+------+--------+-------+-------+
|01 Zhao Lei 3 269.0|
|02 | Qian Dian | 3 | 210.0|
|03 | sun Feng | 3 | 240.0|
|04 | Li Yun | 3 | 100.0|
|05 Zhou Mei 2 163.0|
|06 | Wu Lan | 2 | 65.0|
|07 | Zheng Zhu | 2 | 187.0|
|08 | Wang Ju | 0 | null|
+------+--------+-------+-------+
8 rows in set (0.00 sec)

 

5. Check the information of students with achievements

select student.* from student,(select * from sc group by sid) as a where student.sid=a.sid;
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|01 | Zhao Lei | 1990-01-01 00:00 | male|
|02 | Qian Dian | 1990-12-21 00:00:00 | male|
|03 | sun Feng | 1990-05-20 00:00:00 | male|
|04 | Li Yun | 1990-08-06 00:00:00 | male|
|05 Zhou Mei 1991-12-01 00:00:00 female|
|06 | Wu Lan | 1992-03-01 00:00 | female|
|07 | Zheng Zhu | 1989-07-01 00:00 | female|
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

 

6. Check the number of “Li” teachers

Select count (*) from teacher where tname like 'Lee%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

 

Three teachers who have studied

select student.* from student,(select sid from sc,course,teacher where  sc.cid=course .cid and  course.tid=teacher .tid and  teacher.tname= As b where student.sid=b .sid;
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|01 | Zhao Lei | 1990-01-01 00:00 | male|
|02 | Qian Dian | 1990-12-21 00:00:00 | male|
|03 | sun Feng | 1990-05-20 00:00:00 | male|
|04 | Li Yun | 1990-08-06 00:00:00 | male|
|05 Zhou Mei 1991-12-01 00:00:00 female|
|07 | Zheng Zhu | 1989-07-01 00:00 | female|
+------+--------+---------------------+------+
6 rows in set (0.00 sec)

 

8. Query the information of students who have not learned all the courses

select * from student where sid in (select sid from sc group by sid having count(cid) < 3);
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|05 Zhou Mei 1991-12-01 00:00:00 female|
|06 | Wu Lan | 1992-03-01 00:00 | female|
|07 | Zheng Zhu | 1989-07-01 00:00 | female|
+------+--------+---------------------+------+
3 rows in set (0.00 sec)

 

9. Inquire about the information of other students whose courses are exactly the same as those of “01”

select student.* from student,(select b.sid from (select sid,group_concat(cid) as g from sc group by sid) as a,(select sid,group_concat(cid) as g from sc group by sid) as b where a.sid=01 and a.g=b.g and b.sid!=01) as c where student.sid=c.sid;
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|02 | Qian Dian | 1990-12-21 00:00:00 | male|
|03 | sun Feng | 1990-05-20 00:00:00 | male|
|04 | Li Yun | 1990-08-06 00:00:00 | male|
+------+--------+---------------------+------+
3 rows in set (0.00 sec)

 

10. Query the information of the students who have at least one course the same as the students whose student number is “01”

select * from Student where Sid in (select distinct Sid from SC where Cid in (select Cid from SC where Sid='01'));
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|01 | Zhao Lei | 1990-01-01 00:00 | male|
|02 | Qian Dian | 1990-12-21 00:00:00 | male|
|03 | sun Feng | 1990-05-20 00:00:00 | male|
|04 | Li Yun | 1990-08-06 00:00:00 | male|
|05 Zhou Mei 1991-12-01 00:00:00 female|
|06 | Wu Lan | 1992-03-01 00:00 | female|
|07 | Zheng Zhu | 1989-07-01 00:00 | female|
+------+--------+---------------------+------+
7 rows in set (0.00 sec)

 

11. Query the names of students who have not learned any course taught by Mr. Zhang San

select sname from student where sname not in (select s.sname from student as s, course as c, teacher as t, sc where s.sid =  sc.sid  and  sc.cid  =C.cid and c.tid = t.tid and t.tname ='zhang San ');
+--------+
| sname  |
+--------+
|Wu Lan|
|Wang Ju|
+--------+
2 rows in set (0.01 sec)

 

12. Inquire about the student number, name and average score of students who failed two or more courses

select student.sid,student.sname,avg(sc.score) from student,sc,(select sid from sc where score<60 group by sid having count(score)>=2) as a where student.sid=sc.sid and student.sid=a.sid group by sid;
+------+--------+---------------+
| sid  | sname  | avg(sc.score) |
+------+--------+---------------+
|04 | Li Yun | 33.33333|
|06 Wu Lan 32.50000|
+------+--------+---------------+
2 rows in set (0.00 sec)

 

13. Retrieve the information of students whose “01” course score is less than 60 and arranged in descending order

select student.* from sc,student where sc.sid=student.sid and cid=01 and score<60 order by sc.score desc;
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|04 | Li Yun | 1990-08-06 00:00:00 | male|
|06 | Wu Lan | 1992-03-01 00:00 | female|
+------+--------+---------------------+------+
2 rows in set (0.00 sec)

 

14. Show all students’ grades in all courses and average grades from high to low

select sc.sid,score_01.score as score_01,score_02.score as score_02,score_03.score as score_03,avg(sc.score) as avg_score from sc left join (select * from sc where cid=01) as score_01 on score_01.sid=sc.sid left join (select * from sc where cid=02) as score_02 on score_02.sid=sc.sid left join (select * from sc where cid=03) as score_03 on score_03.sid=sc.sid group by sid order by avg_score desc;
+------+----------+----------+----------+-----------+
| sid  | score_01 | score_02 | score_03 | avg_score |
+------+----------+----------+----------+-----------+
| 07   |     NULL |     89.0 |     98.0 |  93.50000 |
| 01   |     80.0 |     90.0 |     99.0 |  89.66667 |
| 05   |     76.0 |     87.0 |     NULL |  81.50000 |
| 03   |     80.0 |     80.0 |     80.0 |  80.00000 |
| 02   |     70.0 |     60.0 |     80.0 |  70.00000 |
| 04   |     50.0 |     30.0 |     20.0 |  33.33333 |
| 06   |     31.0 |     NULL |     34.0 |  32.50000 |
+------+----------+----------+----------+-----------+
7 rows in set (0.00 sec)

 

15. Query the highest score, lowest score and average score of each subject 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 score: 70-80, excellent score: 80-90, excellent score: > = 90). It is required to output the course number and the number of elective students. The query results are arranged in descending order according to the number of students. If the number of students is the same, it is arranged in ascending order according to the course number. (convenient display, I changed the field name)

select c.cid as cid, c.cname as cname, count(*) as count,
    max(score) as max, min(score) as min, avg(score) as avg,
    sum(case when score >= 60 then 1 else 0 end)/count(*) as pass,
    sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(*) as medium,
    sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(*) as good,
    sum(case when score >= 90 then 1 else 0 end)/count(*) as excellent
from sc, course c
where c.cid = sc.cid
group by c.cid
order by count(*) desc, c.cid asc;
+------+--------+-------+------+------+----------+--------+--------+--------+-----------+
| cid  | cname  | count | max  | min  | avg      | pass   | medium | good   | excellent |
+------+--------+-------+------+------+----------+--------+--------+--------+-----------+
|01 | Chinese | 6 | 80.0 | 31.0 | 64.50000 | 0.6667 | 0.3333 | 0.3333 | 0.0000|
|02 | mathematics | 6 | 90.0 | 30.0 | 72.66667 | 0.8333 | 0.0000 | 0.5000 | 0.1667|
|03 | English | 6 | 99.0 | 20.0 | 68.50000 | 0.6667 | 0.0000 | 0.3333 | 0.3333|
+------+--------+-------+------+------+----------+--------+--------+--------+-----------+
3 rows in set (0.00 sec)

 

16. Sort according to the average score, display the total ranking and the ranking of each subject, and reserve the vacancy when the score is repeated

select s.*, rank_01, rank_02, rank_03, rank_total
from student s
left join (select sid, rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc;
+------+--------+---------------------+------+---------+---------+---------+------------+
| Sid  | Sname  | Sage                | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
+------+--------+---------------------+------+---------+---------+---------+------------+
|Wang Ju, 1990-01-20 00:00:00, male, null, null, null|
|07 | Zheng Zhu | 1989-07-01 00:00:00 | female | null | 2 | 2 | 1|
|01 | Zhao Lei | 1990-01-01 00:00:00 | male | 1 | 1 | 1 | 2|
|05 | Zhou Mei | 1991-12-01 00:00:00 | female | 3 | 3 | null | 3|
|03 | sun Feng | 1990-05-20 00:00:00 | male | 1 | 4 | 3 | 4|
|02 | Qian Dian | 1990-12-21 00:00:00 | male | 4 | 5 | 3 | 5|
|04 | Li Yun | 1990-08-06 00:00:00 | male | 5 | 6 | 6|
|06 | Wu Lan | 1992-03-01 00:00:00 | female | 6 | null | 5 | 7|
+------+--------+---------------------+------+---------+---------+---------+------------+
8 rows in set (0.02 sec)

 

17. Sort according to the average score, display the total ranking and the ranking of each subject, and merge the ranking when the score is repeated

select s.*, rank_01, rank_02, rank_03, rank_total
from student s
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, dense_rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc;
+------+--------+---------------------+------+---------+---------+---------+------------+
| Sid  | Sname  | Sage                | Ssex | rank_01 | rank_02 | rank_03 | rank_total |
+------+--------+---------------------+------+---------+---------+---------+------------+
|Wang Ju, 1990-01-20 00:00:00, male, null, null, null|
|07 | Zheng Zhu | 1989-07-01 00:00:00 | female | null | 2 | 2 | 1|
|01 | Zhao Lei | 1990-01-01 00:00:00 | male | 1 | 1 | 1 | 2|
|Zhou Mei, 1991-12-01 00:00:00, female, 2, 3, null, 3|
|03 | sun Feng | 1990-05-20 00:00:00 | male | 1 | 4 | 3 | 4|
|02 | Qian Dian | 1990-12-21 00:00:00 | male | 3 | 5 | 3 | 5|
|04 | Li Yun | 1990-08-06 00:00:00 | male | 4 | 6 | 5 | 6|
|06 | Wu Lan | 1992-03-01 00:00:00 | female | 5 | null | 4 | 7|
+------+--------+---------------------+------+---------+---------+---------+------------+
8 rows in set (0.00 sec)

 

18. Count the number of students in each score segment: course number, course name, [100-85], [85-70], [70-60], [60-0] and the percentage

Select c.cid as course number, c.cname as course name, a*
from course as c,
(select cid,
    sum(case when score >= 85 then 1 else 0 end)/count(*) as 100_85,
    sum(case when score >= 70 and score < 85 then 1 else 0 end)/count(*) as 85_70,
    sum(case when score >= 60 and score < 70 then 1 else 0 end)/count(*) as 70_60,
    sum(case when score < 60 then 1 else 0 end)/count(*) as 60_0
from sc group by cid) as A
where c.cid = A.cid;
+--------------+--------------+------+--------+--------+--------+--------+
|Course code | course name | CID | 100_ 85 | 85_ 70  | 70_ 60  | 60_ 0   |
+--------------+--------------+------+--------+--------+--------+--------+
|01 | Chinese | 01 | 0.0000 | 0.6667 | 0.0000 | 0.3333|
|02 | mathematics | 02 | 0.5000 | 0.1667 | 0.1667 | 0.1667|
|03 | English | 03 | 0.3333 | 0.3333 | 0.0000 | 0.3333|
+--------------+--------------+------+--------+--------+--------+--------+
3 rows in set (0.00 sec)

 

19. Query the records of the top three in each subject

select * from (select *, rank() over(partition by cid order by score desc) as graderank from sc) A where A.graderank <= 3;
+------+------+-------+-----------+
| Sid  | Cid  | score | graderank |
+------+------+-------+-----------+
| 01   | 01   |  80.0 |         1 |
| 03   | 01   |  80.0 |         1 |
| 05   | 01   |  76.0 |         3 |
| 01   | 02   |  90.0 |         1 |
| 07   | 02   |  89.0 |         2 |
| 05   | 02   |  87.0 |         3 |
| 01   | 03   |  99.0 |         1 |
| 07   | 03   |  98.0 |         2 |
| 02   | 03   |  80.0 |         3 |
| 03   | 03   |  80.0 |         3 |
+------+------+-------+-----------+
10 rows in set (0.00 sec)

 

20. Find out the student number and name of students who only take two courses

select student.sid,student.sname from sc,student where student.sid=sc.sid group by sid having count(*)=2;
+------+--------+
| sid  | sname  |
+------+--------+
|05 Zhou Mei|
|06 Wu Lan|
|07 Zheng Zhu|
+------+--------+
3 rows in set (0.00 sec)

 

21. Query the information of students with the word “wind” in their names

Select * from student where snake like '% wind%';
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|03 | sun Feng | 1990-05-20 00:00:00 | male|
+------+--------+---------------------+------+
1 row in set (0.00 sec)

 

22. Check the list of students born in 1990

select * from student where year(sage)=1990;
+------+--------+---------------------+------+
| Sid  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
|01 | Zhao Lei | 1990-01-01 00:00 | male|
|02 | Qian Dian | 1990-12-21 00:00:00 | male|
|03 | sun Feng | 1990-05-20 00:00:00 | male|
|04 | Li Yun | 1990-08-06 00:00:00 | male|
|08 Wang Ju 1990-01-20 00:00:00 male|
+------+--------+---------------------+------+
5 rows in set (0.00 sec)

 

23. There is no repetition of scores. Please check the information of the students who have the highest scores and their scores among the students who take the courses taught by Mr. Zhang San

select student.*,max( sc.score ) from student,sc,course,teacher where  student.sid=sc .sid and  sc.cid=course .cid and  course.tid=teacher .tid and  teacher.tname= Zhang San;
+------+--------+---------------------+------+---------------+
| Sid  | Sname  | Sage                | Ssex | max(sc.score) |
+------+--------+---------------------+------+---------------+
|01 | Zhao Lei | 1990-01-01 00:00:00 | male | 90.0|
+------+--------+---------------------+------+---------------+
1 row in set (0.00 sec)

 

24. In case of repeated scores, please check the information of the students who have the highest scores and their scores among the students who have taken the courses taught by Mr. Zhang San

select * from (select *, DENSE_ Rank() over (order by score DESC) a from SC where CID = (select CID from course where TID = (select TID from teacher where tname ='zhang San ')) b where B.A = 1;
+------+------+-------+---+
| Sid  | Cid  | score | A |
+------+------+-------+---+
| 01   | 02   |  90.0 | 1 |
+------+------+-------+---+
1 row in set (0.00 sec)

 

25. Query the age of each student by year only

select sname,year(now())-year(sage) as age from student;
+--------+------+
| sname  | age  |
+--------+------+
|Zhao Lei 30|
|Qian Dian | 30|
|Sun Feng 30|
|Li Yun 30|
|Zhou Mei 29|
|Wu Lan | 28|
|Zheng Zhu 31|
|Wang Ju | 30|
+--------+------+
8 rows in set (0.00 sec)

 

26. If the date of birth is less than the date of birth, the age will be reduced by one

select sname, timestampdiff(year, sage, now()) as age from student;
+--------+------+
| sname  | age  |
+--------+------+
|Zhao Lei 30|
|Qian Dian 29|
|Sun Feng 29|
|Li Yun 29|
|Zhou Mei 28|
|Wu Lan | 28|
|Zheng Zhu 30|
|Wang Ju | 30|
+--------+------+
8 rows in set (0.01 sec)

 

27. Query students who have birthday this week

select * from student where week(now()) = week(sage);
Empty set (0.00 sec)

 

28. Query the students who have a birthday this month

select * from student where month(now()) = month(sage);
Empty set (0.00 sec)

 

29. Check the students who have their birthday next month

select * from student where month(now()+1)=month(sage);
Empty set (0.00 sec)

 

Error reporting and solution:

If you encounter the following errors:

ERROR 1055 (42000): Expression 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.Sname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

You can execute the following statement to solve the problem (it will fail after MySQL restarts)

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Recommended Today

PHP 12th week function learning record

sha1() effect sha1()Function to evaluate the value of a stringSHA-1Hash. usage sha1(string,raw) case <?php $str = “Hello”; echo sha1($str); ?> result f7ff9e8b7bb2e09b70935a5d785e0cc5d9d0abf0 sha1_file() effect sha1_file()Function calculation fileSHA-1Hash. usage sha1_file(file,raw) case <?php $filename = “test.txt”; $sha1file = sha1_file($filename); echo $sha1file; ?> result aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d similar_text() effect similar_text()Function to calculate the similarity between two strings. usage similar_text(string1,string2,percent) case […]