Question

问几个SQL的问题

请以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分啊！

Answer 2

生成表：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Course]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tb]
GO

CREATE TABLE [dbo].[Course] (
[Cno] [int] NOT NULL ,
[Cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cpno] [int] NULL ,
[Ccredit] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sc] (
[Sno] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cno] [int] NULL ,
[Grade] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[student] (
[Sno] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sname] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[Ssex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[Sage] [int] NULL ,
[Sdept] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tb] (
[ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[ss] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

相关问题

1．查询同时只选修了1号和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)
AND NOT EXISTS (SELECT * FROM SC B WHERE B.SNO=A.SNO AND B.CNO!=1 AND B.CNO!=2)

2．查询至少选修了1号和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．查询被3门以上（包含3门）课程作为直接先行课的课程号
SELECT CPNO FROM COURSE GROUP BY CPNO HAVING COUNT(1)>=3

4．查询选修课程的总学分大于6的学生的学号，姓名和系别
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．查询平均分在80分以下的学生的学号和选修的课程名
SELECT SNO,CNAME FROM COURSE,SC WHERE COURSE.CNO=SC.CNO AND EXISTS
(SELECT SNO,AVG(GRADE) FROM SC GROUP BY SNO HAVING AVG(GRADE)<80)

Answer 3

WHITE_WIN 大哥 首先辛苦你了 可是你写得都好复杂啊！有没有简单一点的呀，怕老师会深刻怀疑啊……