sql测试题

为管理岗位业务培训信息，建立3个表:

S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 C (C#,CN ) C#,CN 分别代表课程编号、课程名称

SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

--1. 使用标准SQL嵌套语句查询选修课程名称为’C#’的学员学号和姓名

select s#,sn from s where s# in (select s# from sc where c#=(select c# from c where cn='c#'))

--2. 使用标准SQL嵌套语句查询选修课程编号为’C002’的学员姓名和所属单位

select sn,sd from s where s# in (select s# from sc where c#='C002') --3. 使用标准SQL嵌套语句查询不选修课程编号为’C002’的学员姓名和所属单位

select sn,sd from s where s# not in (select s# from sc where c#='C002') --4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

select sn,sd from s where s# in (select s# from sc group by s# having count(c#)=(select count(c#) from c)) --5. 查询选修了课程的学员人数

select count(distinct(s#)) from sc

--6. 查询选修课程超过门的学员学号和所属单位

select s#,sd from s where s# in (select s# from sc group by s# having count(c#)>1)

题目2 问题描述: 已知关系模式:

S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

--1. 找出没有选修过“张老师”讲授课程的所有学生姓名(包括没选修任何课程的人) select sname from s where sno not in (select sno from sc where cno in (select cno from c where cteacher='张老师'))

（解析：没有选修过\"张老师\"的课程包括：选修其他老师课程、没有选修课任何程）

--2. 列出有二门以上(含二门)不及格课程的学生姓名及其平均成绩

select sname,avgrade from s, (select sno,avg(scgrade) avgrade from sc where sno in

(select sno from (select sno,scgrade from sc where scgrade<60) s group by sno having count(sno)>=2)

group by sno) agrade where s.sno=agrade.sno

（解析:先查出不及格的的记录条数,然后根据sno分组count(sno)>=2就说明这个人至少门不及格,然后和S表连接查出姓名即可）

--3. 列出既学过“C001”号课程，又学过“C004”号课程的所有学生姓名

select sname from s where sno in (select sno from (select sno,cno from sc where cno in ('C001','C004')) a group by sno having count(sno)=2)

（解析：根据where条件查出只有\"C001\"和\"C004\"课程的记录,然后根据sno分count(cno),数量=2的就说明两者都选择了）

--4. 列出“C001”号课成绩比“C004”号课成绩高的所有学生的学号

select c.sno from (select sno,cno,scgrade from sc where sno in (select sno from (select sno,cno from sc where cno in ('C001','C004')) a group by sno having count(sno)=2) and cno='C001') c,

(select sno,cno,scgrade from sc where sno in (select sno from (select sno,cno from sc where cno in ('C001','C004')) a

group by sno having count(sno)=2) and cno='C004') d where c.sno=d.sno and c.scgrade>d.scgrade

（解析:\"C001\"号课程和\"C002\"号课程位于同一列中，如果对同一列中的值比较大小，那么要

把它拆开在两个表中：

C表：select sno,cno,scgrade from sc where sno in (select sno from (select sno,cno from sc where cno in ('C001','C004')) a group by sno having count(sno)=2) and cno='C001'

D表：select sno,cno,scgrade from sc where sno in (select sno from (select sno,cno from sc where cno in ('C001','C004')) a group by sno having count(sno)=2) and cno='C004'

）

--5. 列出“C001”号课成绩比“C004”号课成绩高的所有学生的学号及其“C001”号课和“C004”号课的成绩

select c.sno,c.scgrade,d.scgrade from (select sno,cno,scgrade from sc where sno in (select sno from (select sno,cno from sc where cno in ('C001','C004')) a

group by sno having count(sno)=2) and cno='C001') c,

(select sno,cno,scgrade from sc where sno in (select sno from (select sno,cno from sc where cno in ('C001','C004')) a

group by sno having count(sno)=2) and cno='C004') d where c.sno=d.sno and c.scgrade>d.scgrade

（解析:和4同理）