create table Student(sid int ,name nvarchar(100))
create table Couse(cid int,cname nvarchar(100))
create table Sc(sid int,cid int,s int)
go
insert into Student values ('1','张三');
insert into Student values ('2','李四');
insert into Student values ('3','王五');
insert into Student values ('4','赵六');
insert into Student values ('5','马八');
insert into Couse values ('01','语文');
insert into Couse values ('02','数学');
insert into Couse values ('03','英语');
insert into Couse values ('04','物理');
insert into Couse values ('05','计算机');
insert into Sc values ('1','01','20');
insert into Sc values ('2','02','30');
insert into Sc values ('3','03','40');
insert into Sc values ('4','04','50');
insert into Sc values ('5','05','60');insert into Sc values ('1','02','20');
insert into Sc values ('2','01','30');
insert into Sc values ('3','02','40');
insert into Sc values ('4','02','50');
insert into Sc values ('5','02','60');insert into Sc values ('5','03','30');
insert into Sc values ('5','01','60');
insert into Sc values ('5','02','30');
insert into Sc values ('5','03','30');
insert into Sc values ('5','04','30');
select * from Student
select * from Couse
select * from Sc--1.求学生名字..选修的课程中 分数必须大于60分..(比如 选修了2门,那这2个门的分数必须大于60的)
--2.求 选修了 课程表中 所有 课程 的学生..--为什么感觉,这么简单,但就是写不出来...
create table Couse(cid int,cname nvarchar(100))
create table Sc(sid int,cid int,s int)
go
insert into Student values ('1','张三');
insert into Student values ('2','李四');
insert into Student values ('3','王五');
insert into Student values ('4','赵六');
insert into Student values ('5','马八');
insert into Couse values ('01','语文');
insert into Couse values ('02','数学');
insert into Couse values ('03','英语');
insert into Couse values ('04','物理');
insert into Couse values ('05','计算机');
insert into Sc values ('1','01','20');
insert into Sc values ('2','02','30');
insert into Sc values ('3','03','40');
insert into Sc values ('4','04','50');
insert into Sc values ('5','05','60');insert into Sc values ('1','02','20');
insert into Sc values ('2','01','30');
insert into Sc values ('3','02','40');
insert into Sc values ('4','02','50');
insert into Sc values ('5','02','60');insert into Sc values ('5','03','30');
insert into Sc values ('5','01','60');
insert into Sc values ('5','02','30');
insert into Sc values ('5','03','30');
insert into Sc values ('5','04','30');
select * from Student
select * from Couse
select * from Sc--1.求学生名字..选修的课程中 分数必须大于60分..(比如 选修了2门,那这2个门的分数必须大于60的)
--2.求 选修了 课程表中 所有 课程 的学生..--为什么感觉,这么简单,但就是写不出来...
select Student.name from Student ,couse,sc where (select COUNT(couse.cid))=(select COUNT(sc.cid))
应该是这样,没有测试!
刚才没注意到是60。
from student a,couse b,sc c
where a.sid = c.sid
and b.cid = c.cid
and c.s >= 60select aa.name
from
(select a.sid,a.name,count(a.sid) as cid
from student a,sc b
where a.sid = b.sid group by a.sid,a.name) aa,
(select count(cid) as cid from couse) bb
where aa.sid = bb.cid
select sid from scores b where a.sid=b.sid and 60<all(select s from scores c where c.sid=a.sid))
select a.sid from sc a group by a.sid having count(*)=(select count(*) from couse
create table Student(sid int ,name nvarchar(100))
create table Couse(cid int,cname nvarchar(100))
create table Sc(sid int,cid int,s int)insert into Student values ('1','张三');
insert into Student values ('2','李四');
insert into Student values ('3','王五');
insert into Student values ('4','赵六');
insert into Student values ('5','马八');
insert into Couse values ('01','语文');
insert into Couse values ('02','数学');
insert into Couse values ('03','英语');
insert into Couse values ('04','物理');
insert into Couse values ('05','计算机');
insert into Sc values ('1','01','70');
insert into Sc values ('2','02','45');
insert into Sc values ('3','03','80');
insert into Sc values ('4','04','66');
insert into Sc values ('5','05','56');insert into Sc values ('1','02','100');
insert into Sc values ('2','03','52');
insert into Sc values ('3','02','60');
insert into Sc values ('4','02','50');
insert into Sc values ('5','02','60');insert into Sc values ('5','03','50');
insert into Sc values ('5','05','60');
insert into Sc values ('5','04','58');
select * from Student
select * from Couse
select * from Sc
SELECT DISTINCT [name] FROM Student a,Sc b WHERE a.SID=b.SID AND NAME NOT IN(
SELECT DISTINCT [name] FROM Student a,Sc b WHERE a.SID=b.SID AND b.s<=60)SELECT [name] FROM student a,
(SELECT sid,COUNT(*) AS total FROM Sc
GROUP BY SID
HAVING COUNT(*)=(SELECT COUNT(*) FROM couse)
) b
WHERE a.SID=b.sid
不太会用 not exists,就用土法子了!
帮你修改了下测试数据,一个学生可以选2次同一门课嘛,那不乱套了啊!
left join Couse c on c.cid = a.cid
where a.s >= 60
group by b.name,c.cname
(select 1 from Couse a where not exists(select 1 from Sc b where a.cid=b.cid and c.sid=b.sid))
不知道对不对
(Select sc.sid from sc group by sid having count(sc.sid)=(Select count(couse.cid) from couse))
1、select distinct Student.name from Student,Sc where Student.sid=Sc.sid and Sc.s>'60'
2、select name 选修所有课程名单 from student where sid in
(Select sc.sid from sc group by sid having count(sc.sid)=(Select count(couse.cid) from couse))
第一题:select * from student where sid in
(select sid from sc group by sid having count(cid)=(select count(*) from couse))