# =======================================

(Select ……);//查询语句

1. 嵌套查询 （查询结果存储为 T5_1_题目序号，要求 SQL 脚本必须按嵌套式的方式实现）

(1) 查询与“陈伟”在同一系的学生。

create view T5_1_1 as
select *
from student
where sname!='陈伟'
and sdno=( select sdno
from student
where sname='陈伟'
);

//等价的连接查询

create view T5_1_1_1 as
select s1.*
from student s1,student s2
where s1.sdno=s2.sdno
and s2.sname='陈伟'
and s1.sname!='陈伟';

(2) 查询选修了课程名为“数据库”的学生学号和姓名。

create view T5_1_2 as
select sno,sname
from student
where sno in( select sno
from sc,course
where sc.cno=course.cno
and cname='数据库'
);

//等价的连接查询

create view T5_1_2_1 as
select student.sno,sname
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
and cname='数据库';

(3) 查询每个学生超过他选修课程平均成绩的学号、课程号和成绩。

（有两种理解，第一种：本课程成绩>所有课程的平均成绩；第二种：本课程成绩>本课程的平均成绩）

create view T5_1_3_1_1 as
from sc c1
from sc c2
where c1.sno=c2.sno
group by c2.sno
);

create view T5_1_3_1_2 as
from sc,( select sno,avg(grade) avg from sc group by sno ) t1
where sc.sno=t1.sno
and grade>t1.avg;

create view T5_1_3_2_1 as
from sc c1
from sc c2
where c1.cno=c2.cno
group by c2.cno
);

create view T5_1_3_2_2 as
from sc,( select cno,avg(grade) avg from sc group by cno ) t1
where sc.cno=t1.cno
order by sno,sc.cno;

(4) 查询没有选修课程名为“数据库”的学生学号和姓名。

create view T5_1_4 as
select distinct student.sno,sname
from student
where student.sno not in (select sc.sno
from sc,course
where sc.cno=course.cno
and cname='数据库'
);

create view T5_1_4_1 as
select student.sno,sname
from student
minus select student.sno,sname
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
and cname='数据库';

(5) 列出所有成绩都在 70 分以上的学生姓名。

create view T5_1_5 as
select sname
from student
where sno not in( select sno
from sc
group by sno
);

create view T5_1_5_1 as
select sname
from student
where sno in( select sno
from sc
group by sno
);

create view T5_1_5_2 as
select sname
from student,(select sno from sc group by sno having min(grade)>70) t1
where student.sno=t1.sno;

(6) 列出选修“数据库”课程得最高分的学生的姓名、性别和所在系。

create view T5_1_6 as
select sname,ssex,sdno
from student
where sno=( select sno
from sc c1
from sc c2,course
where c2.cno=course.cno
and c1.cno=c2.cno
and cname='数据库'
)
);

create view T5_1_6_1 as
select sname,ssex,sdno,dname
from student,dept
where student.sdno=dept.dno
and sno=( select sno
from sc c1
from sc c2,course
where c2.cno=course.cno
and c1.cno=c2.cno
and cname='数据库'
)
);

create view T5_1_6_2 as
select sname,ssex,sdno,dname
from student,sc c1,dept
where student.sdno=dept.dno
and student.sno=c1.sno
from sc c2,course
where c2.cno=course.cno
and c1.cno=c2.cno
and cname='数据库'
);

(7) 列出每门课程成绩最高的学生的姓名。

create view T5_1_7 as
select sname
from student
where sno in( select sno
from sc c1
from sc c2
where c1.cno=c2.cno
group by c2.cno
)
);

create view T5_1_7_1 as
from student,course,sc c1
where student.sno=c1.sno
and c1.cno=course.cno
and student.sno=( select sno
from sc c2
where c1.cno=c2.cno
from sc c3
where c2.cno=c3.cno
group by cno
)
)
order by c1.cno;

create view T5_1_7_2 as
from student,course,sc c1
where student.sno=c1.sno
and c1.cno=course.cno
from sc c2
where c1.cno=c2.cno
group by cno
)
order by c1.cno;

(8) 列出和“李勇”同学选修了相同课程的学生的姓名。

create view T5_1_8 as
select sname
from student
where sname!='李勇'
and sno in( select sno
from sc
where cno in( select cno
from sc,student
where student.sno=sc.sno
and sname='李勇'
)
);

create view T5_1_8_1 as
select distinct sname
from student,sc
where student.sno=sc.sno
and sname!='李勇'
and cno in( select cno
from student,sc
where student.sno=sc.sno
and sname='李勇'
);

create view T5_1_8_2 as
select distinct sname
from student,sc,(select cno from student,sc where student.sno=sc.sno and sname='李勇') t1
where student.sno=sc.sno
and sc.cno=t1.cno
and sname!='李勇';

2. EXISTS 查询 （查询结果存储为 T5_2_题目序号）

(1) 查询没有选修‘2002’号课程的学生的姓名。

create view T5_2_1 as
select sname
from student
where sno not in( select sno
from sc c1
where exists( select *
from sc c2
where c1.sno=c2.sno
and  cno='2002'
)
);

create view T5_2_1_1 as
select sname
from student
where sno in( select sno
from sc c1
where not exists( select *
from sc c2
where c1.sno=c2.sno
and  cno='2002'
)
);

create view T5_2_1_2 as
select distinct sname
from student,sc c1
where student.sno=c1.sno
and not exists( select *
from sc c2
where c1.sno=c2.sno
and c2.cno='2002'
);

create view T5_2_1_3 as
select sname
from student
where sno in( select sno
from student
minus select student.sno
from student,sc
where student.sno=sc.sno
and cno='2002'
);

(2) 查询选修了所有课程的学生的姓名。

create view T5_2_2 as
select sname
from student
where not exists( select *
from course
where not exists( select *
from sc
where sc.sno=student.sno
and sc.cno=course.cno
)
); 

create view T5_2_2_1 as
select sname
from student
where not exists( select *
from sc
where not exists( select *
from course
where student.sno=sc.sno
and sc.cno=course.cno
)
);

# 作业一         作业二          作业三           作业四           作业五

07-10 479
11-23
07-10 656
07-09 594
05-13 7510
07-08 1006
11-07
09-30
12-18
12-10
04-21 7259
04-08 104
05-07 8438
11-13
06-20
04-12 2334
02-14 3万+
03-13 2402
06-19 2953

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

AN_drew

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。