1, Table structure
1.1 student list
create table student(
sid varchar(10) NOT NULL COMMENT 'student ID',
username varchar(20) NOT NULL COMMENT 'Student name',
sex enum("man","woman") NOT NULL DEFAULT 'man' COMMENT 'Gender',
birthday datetime NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT 'date of birth',
primary key(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Student list';
insert into student values('01', 'Lei Zhao', 'male', '1990-01-01');
insert into student values('02', 'Qian Dian', 'male', '1990-12-21');
insert into student values('03', 'Sun Feng', 'male', '1990-05-20');
insert into student values('04', 'Li Yun', 'male', '1990-08-06');
insert into student values('05', 'Zhou Mei', 'female', '1991-12-01');
insert into student values('06', 'Wu Lan', 'female', '1992-03-01');
insert into student values('07', 'Zheng Zhu', 'female', '1989-07-01');
insert into student values('08', 'Ju Wang', 'female', '1990-01-20');
1.2 list of Teachers
create table teacher(
tid varchar(10) NOT NULL COMMENT 'teacher ID',
tname varchar(20) NOT NULL COMMENT 'Teacher name',
primary key(tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Teacher table';
insert into teacher values('01' , 'Zhang San'),('02' , 'Li Si'),('03' , 'Wang Wu');
1.3 Curriculum
create table course(
cid varchar(10) NOT NULL COMMENT 'curriculum ID',
tid varchar(10) NOT NULL COMMENT 'teacher ID',
cname varchar(20) NOT NULL COMMENT 'Course name',
primary key(cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Class Schedule Card';
insert into course values('01', '02', 'language');
insert into course values('02', '01', 'mathematics');
insert into course values('03', '03', 'English');
1.4 transcript
create table SC(
sid varchar(10) NOT NULL COMMENT 'student ID',
cid varchar(10) NOT NULL COMMENT 'curriculum ID',
score tinyint(3) NOT NULL COMMENT 'fraction'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Transcript';
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);
2, SQL query scenario
2.1 [*] all tables are associated with queries. The left connection is used to avoid students having no score records
select * from student a left join ( select a.tid,a.tname,b.cid,b.score,b.sid,c.cname from teacher a,SC b,course c where a.tid=c.tid and b.cid=c.cid ) b on a.sid=b.sid; +-----+----------+-----+---------------------+------+-------+------+-------+------+-------+ | sid | username | sex | birthday | tid | tname | cid | score | sid | cname | +-----+----------+-----+---------------------+------+-------+------+-------+------+-------+ | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | 02 | Li Si | 01 | 80 | 01 | language | | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | 01 | Zhang San | 02 | 90 | 01 | mathematics | | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | 03 | Wang Wu | 03 | 99 | 01 | English | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 02 | Li Si | 01 | 70 | 02 | language | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 01 | Zhang San | 02 | 60 | 02 | mathematics | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 03 | Wang Wu | 03 | 80 | 02 | English | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | 02 | Li Si | 01 | 80 | 03 | language | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | 01 | Zhang San | 02 | 80 | 03 | mathematics | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | 03 | Wang Wu | 03 | 80 | 03 | English | | 04 | Li Yun | male | 1990-08-06 00:00:00 | 02 | Li Si | 01 | 50 | 04 | language | | 04 | Li Yun | male | 1990-08-06 00:00:00 | 01 | Zhang San | 02 | 30 | 04 | mathematics | | 04 | Li Yun | male | 1990-08-06 00:00:00 | 03 | Wang Wu | 03 | 20 | 04 | English | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | 02 | Li Si | 01 | 76 | 05 | language | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | 01 | Zhang San | 02 | 87 | 05 | mathematics | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | 02 | Li Si | 01 | 31 | 06 | language | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | 03 | Wang Wu | 03 | 34 | 06 | English | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | 01 | Zhang San | 02 | 89 | 07 | mathematics | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | 03 | Wang Wu | 03 | 98 | 07 | English | | 08 | Ju Wang | female | 1990-01-20 00:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | +-----+----------+-----+---------------------+------+-------+------+-------+------+-------+
2.2 query the information and course scores of students with higher scores in Chinese than mathematics
select a.*,b.score language,c.score mathematics from student a,SC b,SC c where a.sid=b.sid and a.sid=c.sid and b.cid='01' and c.cid='02' and b.score>c.score; +-----+----------+-----+---------------------+------+------+ | sid | username | sex | birthday | language | mathematics | +-----+----------+-----+---------------------+------+------+ | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 70 | 60 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | 50 | 30 | +-----+----------+-----+---------------------+------+------+
2.3 query the student number, student name and average score of students whose average score is greater than or equal to 90
select b.sid,a.username,avg(b.score) 'Average score' from student a,SC b where a.sid=b.sid group by b.sid having avg(b.score)>90; +-----+----------+----------+ | sid | username | Average score | +-----+----------+----------+ | 07 | Zheng Zhu | 93.5 | +-----+----------+----------+
2.4 query the information of students without grades
select * from student where student.sid not in (select sid from SC group by sid); +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 08 | Ju Wang | female | 1990-01-20 00:00:00 | +-----+----------+-----+---------------------+
2.5 query the student number, student name, total number of courses selected and the total score of all courses of all students in descending order
select a.sid,a.username,count(b.cid) 'Total number of courses selected',sum(b.score) 'Total score' from student a left join SC b on a.sid=b.sid group by a.sid order by sum(b.score) desc; +-----+----------+----------+--------+ | sid | username | Total number of courses selected | Total score | +-----+----------+----------+--------+ | 01 | Lei Zhao | 3 | 269 | | 03 | Sun Feng | 3 | 240 | | 02 | Qian Dian | 3 | 210 | | 07 | Zheng Zhu | 2 | 187 | | 05 | Zhou Mei | 2 | 163 | | 04 | Li Yun | 3 | 100 | | 06 | Wu Lan | 2 | 65 | | 08 | Ju Wang | 0 | NULL | +-----+----------+----------+--------+
2.6 query the information of all students taught by teacher Zhang San
select a.* from student a, teacher b,course c, SC d where a.sid=d.sid and c.cid=d.cid and b.tid=c.tid and b.tname="Zhang San"; +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | +-----+----------+-----+---------------------+
2.7 query the information of all students that Mr. Zhang San has not taught
(find out the sid of the students who have been taught, and then use sid not in)
select * from student where sid not in( select a.sid from SC a,course b,teacher c where a.cid=b.cid and b.tid=c.tid and c.tname="Zhang San" ); +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 06 | Wu Lan | female | 1992-03-01 00:00:00 | | 08 | Ju Wang | female | 1990-01-20 00:00:00 | +-----+----------+-----+---------------------+
2.8 query and report the information of students in Chinese and mathematics classes at the same time
Find out the records of learning Chinese or mathematics, and then group them by Student id, which is greater than or equal to 2
select a.*,count(b.cid) from student a,SC b where a.sid=b.sid and b.cid in ( select cid from course where cname="language" or cname="mathematics" ) group by b.sid having count(b.cid)>=2; +-----+----------+-----+---------------------+--------------+ | sid | username | sex | birthday | count(b.cid) | +-----+----------+-----+---------------------+--------------+ | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | 2 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 2 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | 2 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | 2 | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | 2 | +-----+----------+-----+---------------------+--------------+
2.9 query the information of students who reported Chinese but did not report mathematics class
select c.* from student c,SC d where c.sid=d.sid and c.sid not in ( select sid from SC a,course b where a.cid=b.cid and b.cname="mathematics" ) and d.cid=(select cid from course where course.cname="language"); +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 06 | Wu Lan | female | 1992-03-01 00:00:00 | +-----+----------+-----+---------------------+
2.10 query the information of students who have not studied all courses
select a.* from student a,SC b where a.sid=b.sid group by b.sid having count(b.cid)<(select count(*) from course); +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | +-----+----------+-----+---------------------+
2.11 query students who have intersection with Wu Lan's course
select a.* from student a,SC b where a.sid=b.sid and b.cid in (select cid from student a,SC b where a.sid=b.sid and a.username="Wu Lan") group by b.sid; +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | +-----+----------+-----+---------------------+
2.12 query the student information exactly the same as Zhao Lei's course
Find out Zhao Lei's classmate records of each class. After the student id is grouped, the number of courses = Zhao Lei's number of courses
select a.*,count(b.cid) from student a,SC b where a.sid=b.sid and b.cid in ( select cid from student a,SC b where a.sid=b.sid and a.username="Lei Zhao" ) group by sid having count(cid)=( select count(b.cid) from student a,SC b where a.sid=b.sid and a.username="Lei Zhao" ); +-----+----------+-----+---------------------+--------------+ | sid | username | sex | birthday | count(b.cid) | +-----+----------+-----+---------------------+--------------+ | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | 3 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 3 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | 3 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | 3 | +-----+----------+-----+---------------------+--------------+
2.13 query the names of students who have not studied any course taught by "Zhang San"
First find out the student records of Zhang San, and then reverse query
select a.* from student a,SC b where a.sid=b.sid and a.sid not in (select b.sid from teacher a,SC b,course c where a.tid=c.tid and b.cid=c.cid and a.tname="Zhang San") group by a.sid; +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 06 | Wu Lan | female | 1992-03-01 00:00:00 | +-----+----------+-----+---------------------+
2.14 query the student number, name and average score of students who fail two or more courses
select b.sid,a.username,count(b.cid),avg(b.score)'Average score' from student a,SC b where a.sid=b.sid and b.score<60 group by sid having count(b.cid)>=2; +-----+----------+--------------+----------+ | sid | username | count(b.cid) | Average score | +-----+----------+--------------+----------+ | 04 | Li Yun | 3 | 33.3333 | | 06 | Wu Lan | 2 | 32.5 | +-----+----------+--------------+----------+
2.15 search for student information with Chinese score less than 60 in descending order
select a.*,b.score from student a,SC b,course c where a.sid=b.sid and b.cid=c.cid and c.cname='language' and b.score<60 order by b.score desc; +-----+----------+-----+---------------------+-------+ | sid | username | sex | birthday | score | +-----+----------+-----+---------------------+-------+ | 04 | Li Yun | male | 1990-08-06 00:00:00 | 50 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | 31 | +-----+----------+-----+---------------------+-------+
2.16 display the scores and average scores of all courses of all students from high to low
– first calculate the average score record corresponding to the student ID = = > SID, AVG
– connect on Sid > SID, AVG, CID, score on the left of the grade sheet
– query > SID, CID, score, AVG, CNAME with the course schedule connection
– connect on sid to the left of the student table and sort according to avg
select * from student left join ( select c.cname,d.sid,d.cid,d.score,d.avg from course c, ( select a.sid,cid,score,avg from SC a left join ( select b.sid,avg(score) avg from SC b group by b.sid ) b on a.sid=b.sid ) d where c.cid=d.cid ) b on student.sid=b.sid order by b.avg desc; +-----+----------+-----+---------------------+-------+------+------+-------+---------+ | sid | username | sex | birthday | cname | sid | cid | score | avg | +-----+----------+-----+---------------------+-------+------+------+-------+---------+ | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | mathematics | 07 | 02 | 89 | 93.5 | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | English | 07 | 03 | 98 | 93.5 | | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | language | 01 | 01 | 80 | 89.6667 | | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | mathematics | 01 | 02 | 90 | 89.6667 | | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | English | 01 | 03 | 99 | 89.6667 | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | language | 05 | 01 | 76 | 81.5 | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | mathematics | 05 | 02 | 87 | 81.5 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | language | 03 | 01 | 80 | 80 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | mathematics | 03 | 02 | 80 | 80 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | English | 03 | 03 | 80 | 80 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | mathematics | 02 | 02 | 60 | 70 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | English | 02 | 03 | 80 | 70 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | language | 02 | 01 | 70 | 70 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | mathematics | 04 | 02 | 30 | 33.3333 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | English | 04 | 03 | 20 | 33.3333 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | language | 04 | 01 | 50 | 33.3333 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | English | 06 | 03 | 34 | 32.5 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | language | 06 | 01 | 31 | 32.5 | | 08 | Ju Wang | female | 1990-01-20 00:00:00 | NULL | NULL | NULL | NULL | NULL | +-----+----------+-----+---------------------+-------+------+------+-------+---------+
2.17 query students' total scores and rank them
select a.*,sum(b.score) from student a,SC b where a.sid=b.sid group by sid order by sum(b.score) desc; -- Using the left link is to avoid empty results select * from student a left join (select SC.sid,sum(SC.score) sum from SC group by SC.sid) b on a.sid=b.sid order by sum desc; +-----+----------+-----+---------------------+------+------+ | sid | username | sex | birthday | sid | sum | +-----+----------+-----+---------------------+------+------+ | 01 | Lei Zhao | male | 1990-01-01 00:00:00 | 01 | 269 | | 03 | Sun Feng | male | 1990-05-20 00:00:00 | 03 | 240 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 02 | 210 | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | 07 | 187 | | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | 05 | 163 | | 04 | Li Yun | male | 1990-08-06 00:00:00 | 04 | 100 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | 06 | 65 | | 08 | Ju Wang | female | 1990-01-20 00:00:00 | NULL | NULL | +-----+----------+-----+---------------------+------+------+
2.18 query the average score of different courses taught by different teachers from high to low
(the left connection is to avoid some teachers not teaching)
select b.cid,c.cname,d.tname,avg(b.score) from SC b,course c,teacher d where b.cid=c.cid and c.tid=d.tid group by b.cid order by avg(b.score) desc; +-----+-------+-------+--------------+ | cid | cname | tname | avg(b.score) | +-----+-------+-------+--------------+ | 02 | mathematics | Zhang San | 72.6667 | | 03 | English | Wang Wu | 68.5 | | 01 | language | Li Si | 64.5 | +-----+-------+-------+--------------+
2.19 query the information of students from the 2nd to the 3rd in the total score and the course score
select a.*,sum(b.score) from student a, SC b where a.sid=b.sid group by b.sid order by sum(b.score) desc limit 1,2; +-----+----------+-----+---------------------+--------------+ | sid | username | sex | birthday | sum(b.score) | +-----+----------+-----+---------------------+--------------+ | 03 | Sun Feng | male | 1990-05-20 00:00:00 | 240 | | 02 | Qian Dian | male | 1990-12-21 00:00:00 | 210 | +-----+----------+-----+---------------------+--------------+
2.20 count the number of students in each score segment of each subject: course number, course name, 100-85, 85-70, 70-60, 0-60 and their percentage
select a.cid,b.cname,sum(a.score>85) '85-100',sum(a.score>=60 and a.score<85) '60-84',sum(a.score<60) '0-59' from SC a,course b where a.cid=b.cid group by a.cid; +-----+-------+--------+-------+------+ | cid | cname | 85-100 | 60-84 | 0-59 | +-----+-------+--------+-------+------+ | 01 | language | 0 | 4 | 2 | | 02 | mathematics | 3 | 2 | 1 | | 03 | English | 2 | 2 | 2 | +-----+-------+--------+-------+------+
2.21 query the number of electives in each subject
select a.cid,b.cname,count(a.sid) from SC a,course b where a.cid=b.cid group by a.cid; +-----+-------+--------------+ | cid | cname | count(a.sid) | +-----+-------+--------------+ | 01 | language | 6 | | 02 | mathematics | 6 | | 03 | English | 6 | +-----+-------+--------------+
2.22 query the information of students who only take two courses
select a.*,b.sid,count(b.cid) from student a,SC b where a.sid=b.sid group by b.sid having count(b.cid)=2; +-----+----------+-----+---------------------+-----+--------------+ | sid | username | sex | birthday | sid | count(b.cid) | +-----+----------+-----+---------------------+-----+--------------+ | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | 05 | 2 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | 06 | 2 | | 07 | Zheng Zhu | female | 1989-07-01 00:00:00 | 07 | 2 | +-----+----------+-----+---------------------+-----+--------------+
2.23 query the number of boys and girls
select sex,count(sid) from student group by sex; +-----+------------+ | sex | count(sid) | +-----+------------+ | male | 4 | | female | 4 | +-----+------------+
2.24 query the list of students born after 1990 (Note: the type of Sage column in the Student table is datetime)
select * from student where year(birthday)>1990; +-----+----------+-----+---------------------+ | sid | username | sex | birthday | +-----+----------+-----+---------------------+ | 05 | Zhou Mei | female | 1991-12-01 00:00:00 | | 06 | Wu Lan | female | 1992-03-01 00:00:00 | +-----+----------+-----+---------------------+
2.25 query the average score of each course, and the results are arranged in descending order according to the average score. If the average score is the same, it is arranged in ascending order according to the course number
select a.cid,b.cname,avg(a.score) from SC a,course b where a.cid=b.cid group by a.cid order by avg(a.score) desc,a.cid asc; +-----+-------+--------------+ | cid | cname | avg(a.score) | +-----+-------+--------------+ | 02 | mathematics | 72.6667 | | 03 | English | 68.5 | | 01 | language | 64.5 | +-----+-------+--------------+
2.26 query the student number, name and average score of all students whose average score is greater than or equal to 85
select a.sid,b.username,avg(a.score) avg from SC a,student b where a.sid=b.sid group by a.sid having avg>85; +-----+----------+---------+ | sid | username | avg | +-----+----------+---------+ | 01 | Lei Zhao | 89.6667 | | 07 | Zheng Zhu | 93.5 | +-----+----------+---------+
2.27 query the names and scores of students whose mathematics course scores are lower than 60
select c.username,a.score from SC a,course b,student c where a.sid=c.sid and a.cid=b.cid and b.cname='mathematics' and a.score <60; +-----+----------+-------+ | sid | username | score | +-----+----------+-------+ | 04 | Li Yun | 30 | +-----+----------+-------+
2.28 query the name, course name and score of any course with a score of more than 70
select a.username,c.cname,b.score from student a,SC b,course c where a.sid=b.sid and b.cid=c.cid and score>70; +----------+-------+-------+ | username | cname | score | +----------+-------+-------+ | Lei Zhao | language | 80 | | Lei Zhao | mathematics | 90 | | Lei Zhao | English | 99 | | Qian Dian | English | 80 | | Sun Feng | language | 80 | | Sun Feng | mathematics | 80 | | Sun Feng | English | 80 | | Zhou Mei | language | 76 | | Zhou Mei | mathematics | 87 | | Zheng Zhu | mathematics | 89 | | Zheng Zhu | English | 98 | +----------+-------+-------+
2.29 query the information of students who have taken all courses
select a.sid,b.username,count(cid) from SC a,student b where a.sid=b.sid group by sid having count(cid)=(select count(*) from course); +-----+----------+------------+ | sid | username | count(cid) | +-----+----------+------------+ | 01 | Lei Zhao | 3 | | 02 | Qian Dian | 3 | | 03 | Sun Feng | 3 | | 04 | Li Yun | 3 | +-----+----------+------------+