问几个SQL的问题

Question

请以SQL查询语句写出这些问题，谢谢! 以学生-课程数据库(包含student、sc、course三个数据表，见附表)为例，用SQL SERVER 2000完成如下查询: 1.查询同时只选修了1号和2号课程的学生的学号 2.查询至少选修了1号和2号课程的学生的学号，按学号降序排列 3.查询被3门以上(包含3门)课程作为直接先行课的课程号 4.查询选修课程的总学分大于6的学生的学号，姓名和系别 5.查询平均分在80分以下的学生的学号和选修的课程名 0020 表student 学号 姓名 性别 年龄 所在系 Sno Sname Ssex Sage Sdept 200215121 李勇 男 20 CS 200215122 刘晨 女 19 IS 200215123 王敏 女 18 MA 200215125 张立 男 19 IS 表course 课程号 课程名 先行课 学分 Cno Cname Cpno Ccredit 1 数据库 5 4 2 数学 2 3 信息系统 1 4 4 操作系统 6 3 5 数据结构 7 4 6 数据处理 2 7 PASCAL语言 6 4 表sc 学号 课程号 成绩 Sno Cno Grade 200215121 1 92 200215121 2 85 200215121 3 88 200215122 2 90 200215122 3 80

Answer 1

1.
select
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
AND
NOT
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO!=1
AND
B.CNO!=2)
2.
select
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
ORDER
BY
SNO
DESC
3.
SELECT
CPNO
FROM
COURSE
GROUP
BY
CPNO
HAVING
COUNT(1)>=3
4.
SELECT
SNO,SNAME,SDEPT
FROM
STUDENT
WHERE
ISNULL((SELECT
SUM(CCREDIT)
FROM
COURSE,SC
WHERE
SC.SNO=STUDENT.SNO
AND
SC.CNO=COURSE.CNO),0)>6
5.
SELECT
SNO,CNAME
FROM
COURSE,SC
WHERE
COURSE.CNO=SC.CNO
AND
EXISTS
(SELECT
SNO,AVG(GRADE)
FROM
SC
A
WHERE
A.SNO=SC.SNO
HAVING
AVG(GRADE)<80)
======================================================================================
测试后的答案(哈哈，请不要抄龚，把错误也抄去了):
1.
select
distinct
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
AND
NOT
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO!=1
AND
B.CNO!=2)
2.
select
distinct
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
ORDER
BY
SNO
DESC
3.
SELECT
CPNO
FROM
COURSE
GROUP
BY
CPNO
HAVING
COUNT(1)>=3
and
cpno
!=''
and
cpno
is
not
null
4.
SELECT
SNO,SNAME,SDEPT
FROM
STUDENT
WHERE
ISNULL((SELECT
SUM(CCREDIT)
FROM
COURSE,SC
WHERE
SC.SNO=STUDENT.SNO
AND
SC.CNO=COURSE.CNO),0)>6
5.
SELECT
SNO,CNAME
FROM
COURSE,SC
WHERE
COURSE.CNO=SC.CNO
AND
EXISTS
(SELECT
SNO,AVG(GRADE)
FROM
SC
A
WHERE
a.SNO=SC.SNO
group
by
a.sno
HAVING
AVG(GRADE)<80)
====忙了半宿，答了五个问题，还测试了，最少要加200分啊!