sql test（1）

来源：互联网发布：淘宝加盟开店合同编辑：程序博客网时间：2024/05/15 01:36

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 sWHERE    stu.sno = s.sno AND    c.cno = s.cnoGROUP BY    姓名HAVING    课程数 = (        SELECT            COUNT(*)        FROM            class    );

结果：

0 0