sql test（1）

student：

学号 姓名

class：

课程号 课程名

sorce：

学号 课程号 成绩

要求：

获得所有成绩都有的学生的姓名，以及课程数量

表结构：

create table student(sno varchar(50) not null,name varchar(50) not null);  
insert into student(sno,name) values('001','张三');  
insert into student(sno,name) values('002','李四');  
insert into student(sno,name) values('003','王五');
insert into student(sno,name) values('004','小明'); 

 
create table class(cno varchar(50) not null,name varchar(50) not null);
insert into class(cno,name) values('c01','数据结构');  
insert into class(cno,name) values('c02','操作系统');  
insert into class(cno,name) values('c03','计算机组成原理');  
insert into class(cno,name) values('c04','网络基础');  
  
create table score(sno varchar(50) not null,cno varchar(50) not null,score decimal(18,2) not null);   
insert into score(sno,cno,score) values('001','c01',80);  
insert into score(sno,cno,score) values('001','c02',85);  
insert into score(sno,cno,score) values('001','c03',89);  
insert into score(sno,cno,score) values('001','c04',87);  
insert into score(sno,cno,score) values('002','c01',80);  
insert into score(sno,cno,score) values('003','c04',70); 
insert into score(sno,cno,score) values('004','c01',80);  
insert into score(sno,cno,score) values('004','c02',85);  
insert into score(sno,cno,score) values('004','c03',89);  
insert into score(sno,cno,score) values('004','c04',87); 

sql：

SELECT 
    stu.name AS 姓名,
    COUNT(*) AS 课程数
FROM
    student AS stu,
    class AS c,
    score AS s
WHERE
    stu.sno = s.sno AND
    c.cno = s.cno
GROUP BY
    姓名
HAVING
    课程数 = (
        SELECT
            COUNT(*)
        FROM
            class
    );

结果：