use s_t2
/*1. 查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；*/
go
select *
from course
where Cname like 'DB%s__'/*注意,第十一号课程是自己添加上去的,
老师给的原始数据库是没有的,这是为了测试查询语句是否正确特意加的*/
/*2. 查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、
课程名；*/
go
select Sname,student.sno,course.Cno,Cname
from student ,sc ,course
where Sname like '%阳%' and
(student.sno=sc .Sno and course.Cno=sc.Cno )
/*3. 列出选修了‘数学’或者‘大学英语’的学生学号、姓名、
所在院系、选修课程号及成绩；*/
go
select student .sno ,Sname,Sdept,course .Cno ,Grade
from student ,course ,sc
where (Cname like '数学' or Cname like '大学英语') and
(student.sno=sc .Sno and course.Cno=sc.Cno )
/*4. 查询缺少成绩的所有学生的详细情况；*/
go
select student .*
from student ,sc
where sc.Grade is null and (student .sno =sc.Sno)
/*5. 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；*/
go
select *
from student
where sage <> (select sage
from student
where Sname like '张力')
/*6. 查询所选课程的平均成绩大于张力的平均成绩的学生学号、
姓名及平均成绩；*/
go
select student .sno ,Sname,AVG (grade) as '平均成绩'
from student ,sc
where student .sno =sc.Sno
group by student .Sno,Sname
having AVG (grade)>(select AVG (Grade )
from student ,sc
where Sname like '张力' and
student .sno =sc.Sno )
/*查询张力的平均成绩*/
go
select AVG (Grade )
from student ,sc
where Sname like '张力' and
student .sno =sc.Sno
/*7. 按照“学号，姓名，所在院系，已修学分”的顺序列出
学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；*/
go
select student .sno as '学号',Sname as '姓名',
Sdept as '所在院系',sum(course.Ccredit ) as '已修学分'
from student ,sc,course
where student .sno=sc.Sno and course .Cno = sc.cno and
Grade>60
group by student .sno ,student .Sname ,student .Sdept
/*8. 列出只选修一门课程的学生的学号、姓名、院系及成绩；*/
go
select student .sno as '学号',Sname as '姓名',
Sdept as '所在院系',Grade
from student ,sc
where student .sno=sc.Sno and
student .sno in( select student.sno
from student ,sc
where student .sno=sc.Sno
group by student .sno
having COUNT (Cno)=1 )
/*第18号学生没有被查询出来,等下回来想是为什么*/
/*9. 查找选修了至少一门和张力选修课程一样的学生的学号、
姓名及课程号；*/
go
select student .sno ,Sname,cno
from student ,sc
where student .sno=sc.Sno and Sname not like '张力' and
Cno in (select Cno
from student ,sc
where student .sno=sc.Sno and
Sname like '张力')
/*10. 只选修“数据库”和“数据结构”两门课程的学生的基本信息；*/
/*解题思路：先找出选修了数据库的学生，再找出其中选修了数据结构的，
再找出只选修了两门的学生，综合起来就是只选修了这两门的学生
*/
go
select student.*
from student ,course ,sc
where student.sno=sc.Sno and course .Cno =sc.Cno and
Cname ='数据库' and
student.sno in (select Student.sno
from student ,course ,sc
where student.sno=sc.Sno and course .Cno =sc.Cno and
Cname ='数据结构') and
student .sno in( select student.sno
from student ,sc
where student .sno=sc.Sno
group by student .sno
having COUNT (Cno)=2 )
/*11. 至少选修“数据库”或“数据结构”课程的学生的基本信息；*/
/*本题把上题只选修两门条件去掉就可以*/
go
select student.*
from student ,course ,sc
where student.sno=sc.Sno and course .Cno =sc.Cno and
Cname ='数据库' and
student.sno in (select Student.sno
from student ,course ,sc
where student.sno=sc.Sno and course .Cno =sc.Cno and
Cname ='数据结构')
/*12. 列出所有课程被选修的详细情况，包括课程号、课程名、
学号、姓名及成绩；*/
go
select course .Cno as '课程号' ,Cname as '课程名',
student .sno as '学号',Sname as '姓名' ,
Grade as '成绩'
from student ,sc,course
where student .sno =sc.Sno and sc .Cno =course .Cno
order by course .Cno
/*13. 查询只被一名学生选修的课程的课程号、课程名；*/
go
select course .Cno ,Cname
from course
where course .Cno in (select course.Cno
from course ,sc
where course.Cno =sc.Cno
group by course .Cno
having COUNT(sno)=1)
/*14. 检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；*/
go
select Student.sno ,Sname
from student
where not exists(select student.sno
from student ,sc
where student.sno=sc.sno and
Cno in (select Cno
from student ,sc
where student.sno=sc.sno
and Sname like '张向东'))
/*15. 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；*/
go
select student .sno ,sname
from student ,sc,course
where student.sno=sc.Sno and course .Cno =sc.Cno and
Cname ='数据结构'
/*16. 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生
姓名、年龄和院系；*/
go
select sname,sage,sdept
from student
where sage<any(select sage
from student
where Sdept='cs')
and Sdept !='cs'
/*17. 使用ANY、ALL 查询，列出其他院系中比CS系所有学生
年龄小的学生；*/
go
select sno
from student
where sage<all(select sage
from student
where Sdept ='cs')
and Sdept !='cs'
/*18. 分别使用连接查询和嵌套查询，列出与‘张力’在一个院系的
学生的信息；*/
go
select *
from student
where Sdept in (select sdept
from student
where Sname like '张力')
/*19. 使用集合查询列出CS系的学生以及性别为女的学生名单；*/
go
select *
from student
where Sdept ='cs' or Ssex like '女'
/*20. 使用集合查询列出CS系的学生与年龄不大于19岁的学生的
交集、差集；*/
go
select *
from student
where Sdept ='cs' intersect (select *
from student
where sage<=19)
go
select *
from student
where Sdept ='cs' except (select *
from student
where sage<=19)
/*21. 使用集合查询列出选修课程1的学生集合与选修课程2的学生
集合的交集；*/
go
select sno
from sc
where Cno=1 and sno in (select Sno
from sc
where Cno =2)
数据库实验SQL代码
需积分: 34 108 浏览量
2018-03-14
14:08:12
上传
评论
收藏 666KB ZIP 举报
database.zip （18个子文件） 
database 
stu_log.ldf 3MB YGKQ 50MB use_sql stu_log.ldf 3MB 实验八_存储过程及触发器.sql 3KB 实验六_数据库的安全性.sql 1KB S_T2.sql 254B 增加一列的代码.sql 64B stu_data.mdf 3MB 实验七_数据库完整性约束.sql 4KB 实验五_视图.sql 3KB 实验三_查询语句实验.sql 3KB 删除一列的代码.sql 59B 修改列名_类型等.sql 282B 命令行插入_修改_删除记录的代码.sql 3KB 实验四_数据库综合查询.sql 7KB three_table.sql 396B stu.mdf 3MB YGKQ_log 2MB资源评论
