查询刘同学没选的课程的课程号

[hrlhrl0]

我这样写
select Cpno
from Course
except
select Sname
from Student,SC
where
SC.Sno=Student.Sno and Student.Sname like '刘%'
;

CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(15));
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY ,
Cname CHAR(40) ,
Cpno CHAR(4) ,
Ccredit SMALLINT ,
FOREIGN KEY (Cpno) REFERENCES Course(Cno));
CREATE TABLE SC
(Sno CHAR(9) ,
Cno CHAR(4) ,
Grade SMALLINT ,
PRIMARY KEY(Sno, Cno) ,
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno));
有语法或逻辑错误

和
检索全部学生都选修的课程的课程号、课程名
帮改改。

[htl258_Tony]

--1
select Cno
from Course
except
select Cno  
from Student,SC
where SC.Sno=Student.Sno and Student.Sname like '刘%'

[hrlhrl0]

我也是这样的

在关键字 'except' 附近有语法错误。

[htl258_Tony]

--2.不知有没有误解，你试试

SELECT Cno,Cname
FROM Course A
	JOIN SC B ON A.Cno=B.Cno
GROUP BY Cno,Cname
HAVING count(DISTINCT sno)=(SELECT count(1) FROM Student)

[htl258_Tony]

[Quote=引用 2 楼 hrlhrl0 的回复:]
我也是这样的

在关键字 'except' 附近有语法错误。
[/Quote]难道你是SQL2000

[htl258_Tony]

--1.
select Cno
from Course t
where not exists(
select 1  
from Student,SC
where SC.Sno=Student.Sno 
	and t.cno=sc.cno
	and Student.Sname like '刘%')

[hrlhrl0]

你那样对了
是2000的，能说下为什么吗

[htl258_Tony]

[Quote=引用 6 楼 hrlhrl0 的回复:]
你那样对了
是2000的，能说下为什么吗
[/Quote]2000的不支持EXCEPT关键字

[hrlhrl0]

服务器: 消息 209，级别 16，状态 1，行 1
列名 'Cno' 不明确。
服务器: 消息 209，级别 16，状态 1，行 1
列名 'Cno' 不明确。

[htl258_Tony]

SELECT a.Cno,a.Cname
FROM Course A
    JOIN SC B ON A.Cno=B.Cno
GROUP BY a.Cno,a.Cname
HAVING count(DISTINCT a.sno)=(SELECT count(1) FROM Student)

[hrlhrl0]

SELECT A.Cno,Cname
FROM Course A
JOIN SC B ON A.Cno=B.Cno
GROUP BY A.Cno,Cname
HAVING count(DISTINCT sno)=(SELECT count(1) FROM Student)
这样可以通过

[htl258_Tony]

SELECT a.Cno,a.Cname
FROM Course A
    JOIN SC B ON A.Cno=B.Cno
GROUP BY a.Cno,a.Cname
HAVING count(DISTINCT b.sno)=(SELECT count(1) FROM Student)

晕倒，b.sno

[hrlhrl0]

强大啊，就是看不懂

[htl258_Tony]

我可是边吃边回，正确你要帮我结贴哦，看不懂怕怕就会消化的。