SQL查询试题
use text_1_mode
create table s(sno int,sName varchar(100))
create table c(cno int,cName varchar(100),CTEACHER varchar(100))
create table sc(sno int,cno int,scGrade int)
insert into s
values(1,'lfm1')
insert into s
values(2,'lfm2')
insert into s
values(3,'lfm3')
insert into s
values(4,'lfm4')
insert into s
values(5,'lfm5')
insert into s
values(6,'lfm6')
insert into c
values(1,'ch1','李明')
insert into c
values(2,'ch2','王刚')
insert into c
values(3,'ch3','ll')
insert into c
values(4,'ch4','ff')
insert into c
values(5,'ch5','ffd')
insert into c
values(6,'ch6','se')
insert into c
values(7,'ch7','s')
insert into sc
values(1,2,80)
insert into sc
values(2,2,50)
insert into sc
values(1,1,60)
insert into sc
values(1,3,90)
insert into sc
values(3,2,55)
insert into sc
values(1,6,77)
insert into sc
values(3,4,80)
insert into sc
values(4,2,70)
select * from s
select * from c
select * from sc
--1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码：
SELECT SNAME FROM S WHERE sname NOT in(SELECT sname FROM SC,C,S WHERE https://www.docsj.com/doc/f115265640.html,O=https://www.docsj.com/doc/f115265640.html,O AND cteacher='李明' AND S.SNO = SC.SNO)
--2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
--实现代码：
SELECT S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)
FROM S,SC,(SELECT SNO FROM SC WHERE SCGRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME
--3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
--实现代码：
select S.SNO,S.SNAME from sc,s
where cno=1 and sc.sno in(select sno from sc where cno=2) and s.sno=sc.sno
--4.列出“1”号课成绩比“2”号课程成绩高的所有学生的学号
SELECT SC1.sno
FROM SC SC1,SC SC2
WHERE https://www.docsj.com/doc/f115265640.html,o='1'
AND https://www.docsj.com/doc/f115265640.html,o='2'
AND SC1.sno=SC2.sno
AND SC1.SCGRADE>SC2.SCGRADE
--5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及成绩
SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE
FROM SC SC1,SC SC2
WHERE https://www.docsj.com/doc/f115265640.html,O='1'
AND https://www.docsj.com/doc/f115265640.html,O='2'
AND SC1.SNO=SC2.SNO
AND SC1.SCGRADE>SC2.SCGRADE
use text_2_mode
drop table S;
drop table C;
drop table SC;
create table S
(
S# varchar(10),
SN varchar (25),
SD varchar (25),
SA int
)
create table C
(
C# varchar(10),
CN varchar (25)
)
create table SC
(
S# varchar(10),
C# varchar(10),
G int
Primary Key(S#, C#)
)
insert into S values ('10001','Students1','department1',23)
insert into S values ('10002','Students2','department1',24)
insert into S values ('10003','Students3','department2',25)
insert into S values ('10004','Students4','department2',26)
insert into S values ('10005','Students5','department3',23)
insert into S values ('10006','Students6','department3',24)
insert into S values ('10007','Students7','department3',25)
insert into S values ('10008','Students8','department4',25)
insert into C values ('C1','数
学')
insert into C values ('C2','物理')
insert into C values ('C3','化学')
insert into C values ('C4','英语')
insert into C values ('C5','中文')
insert into C values ('C6','税收基础')
insert into C values ('C7','传媒')
insert into C values ('C8','日语')
insert into SC values ('10001','C1',67)
insert into SC values ('10001','C2',77)
insert into SC values ('10001','C3',87)
insert into SC values ('10001','C4',97)
insert into SC values ('10001','C5',57)
insert into SC values ('10001','C6',47)
insert into SC values ('10002','C1',62)
insert into SC values ('10002','C2',72)
insert into SC values ('10002','C3',82)
insert into SC values ('10002','C4',92)
insert into SC values ('10002','C5',52)
insert into SC values ('10002','C6',42)
insert into SC values ('10004','C2',74)
insert into SC values ('10004','C5',54)
insert into SC values ('10004','C6',44)
insert into SC values ('10008','C2',44)
insert into SC values ('10007','C5',44)
select * from C
select * from S
select * from SC
--(1)使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
select S#,SN from S where S# in (select S# from C, SC where C.C#=SC.C# and https://www.docsj.com/doc/f115265640.html,='税收基础')
--(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
select S.SN,S.SD from S,SC where S.S#=SC.S# and SC.C#='C2'
--(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
select distinct S.SN,S.SD from S where S.S# not in (select S.S# from S,SC where S.S#=SC.S# and SC.C#='C5')
--(4) 查询选修了课程的学员人数?
select 学员人数=count(distinct s#) from sc
--(5) 查询选修课程超过5门的学员学号和所属单位?
select S#,SD from S where S.S# in (select SC.S# from SC group by SC.S# having count(*)>5)