Data preparation

• 1. Student form
  Student(SId,Sname,Sage,Ssex)
  – SId student number, Sname student name, Sage date of birth, Ssex student gender

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , 'Lei Zhao' , '1990-01-01' , 'male');
insert into Student values('02' , 'Qian Dian' , '1990-12-21' , 'male');
insert into Student values('03' , 'Sun Feng' , '1990-12-20' , 'male');
insert into Student values('04' , 'Li Yun' , '1990-12-06' , 'male');
insert into Student values('05' , 'Zhou Mei' , '1991-12-01' , 'female');
insert into Student values('06' , 'Wu Lan' , '1992-01-01' , 'female');
insert into Student values('07' , 'Zheng Zhu' , '1989-01-01' , 'female');
insert into Student values('09' , 'Zhang San' , '2017-12-20' , 'female');
insert into Student values('10' , 'Li Si' , '2017-12-25' , 'female');
insert into Student values('11' , 'Li Si' , '2012-06-06' , 'female');
insert into Student values('12' , 'Zhao Liu' , '2013-06-13' , 'female');
insert into Student values('13' , 'Sun Qi' , '2014-06-01' , 'female');

• 2. Curriculum
  Course(CId,Cname,TId)
  – CId course number, Cname course name, TId teacher number

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , 'language' , '02');
insert into Course values('02' , 'mathematics' , '01');
insert into Course values('03' , 'English' , '03');

• 3. Teacher list
  Teacher(TId,Tname)
  – TId teacher number, Tname teacher name

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' , 'Wang Wu');

• 4. Transcript
  SC(SId,CId,score)
  – SId student number, CId course number, score

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

Exercise topic

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

Because you need all the student information, you need to get the qualified SId in the sc table and join it with the student table, either left join or right join

select * from Student RIGHT JOIN (
    select t1.SId, class1, class2 from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as class2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2
)r 
on Student.SId = r.SId;

select * from  (
    select t1.SId, class1, class2 
    from
        (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1, 
        (SELECT SId, score as class2 FROM sc WHERE sc.CId = '02') AS t2
    where t1.SId = t2.SId and t1.class1 > t2.class2
) r 
LEFT JOIN Student
ON Student.SId = r.SId;

Query the results of "01" and "02" courses at the same time

select * from 
    (select * from sc where sc.CId = '01') as t1, 
    (select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

Query the score of "01" course but may not exist "02" course (null if it does not exist)

This is the obvious need to use join. 02 may not exist, that is, it is the right side of left join or the left side of right join

select * from 
(select * from sc where sc.CId = '01') as t1
left join 
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;

select * from 
(select * from sc where sc.CId = '02') as t2
right join 
(select * from sc where sc.CId = '01') as t1
on t1.SId = t2.SId;

Query the results of "01" but "02" courses that do not exist

select * from sc
where sc.SId not in (
    select SId from sc 
    where sc.CId = '01'
) 
AND sc.CId= '02';

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

Here, we can only group the scores according to the student ID, average the scores in the group, and finally select those with AVG greater than 60 in the results Note that an alias must be given to the calculated AVG result (AS ss)
When getting student information, you can use either join or general joint search

select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    GROUP BY SId 
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

select Student.SId, Student.Sname, r.ss from Student right join(
      select SId, AVG(score) AS ss from sc
      GROUP BY SId
      HAVING AVG(score)> 60
)r on Student.SId = r.SId;

select s.SId,ss,Sname from(
select SId, AVG(score) as ss from sc  
GROUP BY SId 
HAVING AVG(score)> 60
)r left join 
(select Student.SId, Student.Sname from
Student)s on s.SId = r.SId;

Query the information of students with scores in SC table

select DISTINCT student.*
from student,sc
where student.SId=sc.SId

Query the student number, student name, total number of courses selected and the total score of all courses of all students (null if there is no score)

Joint query will not display students who have not selected courses:

select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
group by sc.sid)r
where student.sid = r.sid;

To display students who have not selected a course (displayed as NULL), you need to use left join:

select s.sid, s.sname,r.coursenumber,r.scoresum
from (
    (select student.sid,student.sname 
    from student
    )s 
    left join 
    (select 
        sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber
        from sc 
        group by sc.sid
    )r 
   on s.sid = r.sid
);

Check the information of students with grades

This question involves the usage of in and exists. In this small table, the efficiency of the two methods is similar. Please refer to the specific difference analysis between in and exists https://www.jianshu.com/p/f212527d76ff，
When the number of records in Table 2 is very large, using EXISTS is much more efficient than in EXISTS is used to check whether a subquery will return at least one row of data. The subquery does not actually return any data, but returns the value True or False
Conclusion: IN() is suitable for the situation that the data in table B is smaller than that in table A
Conclusion: EXISTS() is suitable for the situation that the data of table B is larger than that of table A

select * from student 
where exists (select sc.sid from sc where student.sid = sc.sid);
#perhaps
select * from student 
where exists (select 1 from sc where student.sid = sc.sid);

select * from student
where student.sid in (select sc.sid from sc);

Query the number of teachers surnamed "Li"

select count(*)
from teacher
where tname like 'Lee%';

Query the information of students who have learned "Zhang San"

Multi table joint query

select student.* from student,teacher,course,sc
where 
    student.sid = sc.sid 
    and course.cid=sc.cid 
    and course.tid = teacher.tid 
    and tname = 'Zhang San';

Query the information of students who have not studied all courses

Because some students didn't choose any courses, think backwards. First query the students who chose all courses, and then select the students other than these people

select * from student
where student.sid not in (
  select sc.sid from sc
  group by sc.sid
  having count(sc.cid)= (select count(cid) from course)
);

Query the information of at least one course that is the same as the student with student number "01"

This joint query is OK, but the logic is not clear. I think the clearer logic is as follows: query all the course cid of 01 students from the sc table – query all the students' Sid from the sc table if their cid is in the previous results – query all the students' information from the student table if the sid is in the previous results

select * from student 
where student.sid in (
    select sc.sid from sc 
    where sc.cid in(
        select sc.cid from sc 
        where sc.sid = '01'
    )
);

Query the information of other students who have exactly the same courses as the students of "01"

Query the names of students who have not studied any course taught by "Zhang San"

It is still nested, three-tier nested, or multi table joint query

select * from student
    where student.sid not in(
        select sc.sid from sc where sc.cid in(
            select course.cid from course where course.tid in(
                select teacher.tid from teacher where tname = "Zhang San"
            )
        )
    );

select * from student
where student.sid not in(
    select sc.sid from sc,course,teacher 
    where
        sc.cid = course.cid
        and course.tid = teacher.tid
        and teacher.tname= "Zhang San"
);

Query the student number, name and average score of students who fail two or more courses

select student.SId, student.Sname,b.avg
from student RIGHT JOIN
(select sid, AVG(score) as avg from sc
    where sid in (
              select sid from sc 
              where score<60 
              GROUP BY sid 
              HAVING count(score)>1)
    GROUP BY sid) b on student.sid=b.sid;

Retrieve the student information in descending order of "01" course score less than 60

select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;

Displays the scores of all courses and average scores of all students from high to low

select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )r 
on sc.sid = r.sid
order by avscore desc;

Query the highest score, lowest score and average score of each subject:

It is displayed in the following form: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate and excellent rate

Pass is > = 60, medium is 70-80, excellent is 80-90, excellent is > = 90

It is required to output the course number and the number of electives. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number

select 
sc.CId ,
max(sc.score)as Highest score,
min(sc.score)as Lowest score,
AVG(sc.score)as average,
count(*)as Number of electives,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as pass rate,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as Medium rate,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as Excellent rate,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as Excellent rate 
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC

Sort according to the scores of each subject and display the ranking. If the Score is repeated, the ranking vacancy will be reserved

This problem is a little tricky. You can use variables, but there is also a simpler method, that is, self intersection (left intersection)
Compare the score in sc with yourself to calculate "how many scores are higher than the current score".
Sort according to the scores of each subject and display the ranking. When Score is repeated, the ranking will be merged

select a.cid, a.sid, a.score, count(b.score)+1 as rank
from sc as a 
left join sc as b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;

Query the total score of students and rank them. If the total score is repeated, the ranking vacancy will be reserved

Here we mainly learn how to use variables. In SQL, variables are identified with @.

set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
select sc.sid, sum(sc.score) as total from sc
group by sc.sid
order by total desc)q;

Query the total score of students and rank them. If the total score is repeated, the ranking vacancy will not be reserved

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

Sometimes I feel like a dead brain. The alias cannot be used for the query results after group by, so don't think about calculating the results from a single table group by and then adding the course information from the second table. Instead, join the two tables together to get all the desired attributes, and then perform statistical calculation on the total table. There is no percentage here. The reason is the same.
Note that the statistics after case when returns 1 are not count but sum

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;

Query the top three records of each subject

There are several records whose scores are greater than your own. If it is less than 3, select, because there will be no more than 3 scores greater than your own for the top three. Finally, rank all the selected results according to the scores and course number.

select * from sc
where (
select count(*) from sc as a 
where sc.cid = a.cid and sc.score<a.score 
)< 3
order by cid asc, sc.score desc;

Query the number of students selected for each course

select cid, count(sid) from sc 
group by cid;

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

select student.sid, student.sname from student
where student.sid in
(select sc.sid from sc
group by sc.sid
having count(sc.cid)=2
);

Or joint query

select student.SId,student.Sname
from sc,student
where student.SId=sc.SId  
GROUP BY sc.SId
HAVING count(*)=2；

Query the number of boys and girls

select ssex, count(*) from student
group by ssex

Query the student information with the word "wind" in the name

select *
from student 
where student.Sname like '%wind%'

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

select sname, count(*) from student
group by sname
having count(*)>1;

Nested query lists the information of all students with the same name

select * from student
where sname in (
select sname from student
group by sname
having count(*)>1
);

Check the list of students born in 1990

select *
from student
where YEAR(student.Sage)=1990;

Query the average score of each course. The results are arranged in descending order according to the average score. If the average score is the same, they are arranged in ascending order according to the course number

select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, course
where sc.cid = course.cid
group by sc.cid 
order by average desc,cid asc;

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

select student.sid, student.sname, AVG(sc.score) as aver from student, sc
where student.sid = sc.sid
group by sc.sid
having aver > 85;

Query the names and scores of students whose course name is "Mathematics" and whose score is less than 60

select student.sname, sc.score from student, sc, course
where student.sid = sc.sid
and course.cid = sc.cid
and course.cname = "mathematics"
and sc.score < 60;

Query the courses and scores of all students (there are cases where students do not have grades and do not choose courses)

select student.sname, cid, score from student
left join sc
on student.sid = sc.sid;

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

select student.sname, course.cname,sc.score from student,course,sc
where sc.score>70
and student.sid = sc.sid
and sc.cid = course.cid;

Query failed courses

group by or distinct can be used to get unique

select cid from sc
where score< 60
group by cid;

select DISTINCT sc.CId
from sc
where sc.score <60;

Query the student number and name of students with course number 01 and course score above 80

select student.sid,student.sname 
from student,sc
where cid="01"
and score>=80
and student.sid = sc.sid;

Number of students per course

select sc.CId,count(*) as Number of students
from sc
GROUP BY sc.CId;

The results are not repeated. Query the information and results of the students with the highest scores among the students taking the courses taught by teacher Zhang San

Using having max() is also theoretically correct, but the following sort by score and then take limit 1 is more intuitive and reliable

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "Zhang San"
having max(sc.score);

Taking limit 1 is more intuitive and reliable

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "Zhang San"
order by score desc
limit 1;

In case of repeated scores, query the information of the students with the highest scores and their scores among the students taking the courses taught by teacher Zhang San

In order to verify this problem, modify the original data first

UPDATE sc SET score=90
where sid = "07"
and cid ="02";

In this way, two students in class 02 taught by Mr. Zhang San got the highest score of 90 at the same time.
The idea of this question continues from the previous question. We have found the highest score that meets the limited conditions. At this time, just compare this table and find the record that all scores are equal to the highest score. It seems a little complicated.

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "Zhang San"
and sc.score = (
    select Max(sc.score) 
    from sc,student, teacher, course
    where teacher.tid = course.tid
    and sc.sid = student.sid
    and sc.cid = course.cid
    and teacher.tname = "Zhang San"
);

Query the student number, course number and student score of students with the same score in different courses

The same as above. After using inner join here, there will be a duplicate record of the concept: "01 and 03" = "03 and 01", so the only one here can be used directly

select  a.cid, a.sid,  a.score from sc as a
inner join 
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;

Query the top two with the best scores in each skill

select a.sid,a.cid,a.score from sc as a 
left join sc as b 
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<2
order by a.cid;

Count the number of students in each course (only courses with more than 5 people are counted).

select sc.cid, count(sid) as cc from sc
group by cid
having cc >5;

Retrieve the student number of at least two courses

select sid, count(cid) as cc from sc
group by sid
having cc>=2;

Query the student information of all courses

select student.*
from sc ,student 
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )

Query the age of each student, calculated by year only

According to the date of birth, if the current month day is less than the month day of the year of birth, the age will be reduced by one

select student.SId as Student number,student.Sname  as  Student name,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as Student age
from student

Inquire about the students whose birthday is this week

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());

Check the students whose birthday is next week

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

Query students whose birthdays are this month

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE());

Query the students whose birthday is next month

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE())+1;

Original text: https://www.jianshu.com/p/476b52ee4f1b