数据库实验四（连接）

实验报告（四）

1、实验目的

（1）    掌握自连接的形式和应用场合

（2）    掌握表连接的外连接用法和应用场合

2、实验预习与准备

（1）    了解自连接的用法

（2）    了解外连接中左连接、右连接和全连接的作用

（3）    了解内外连接的区别

3、实验内容及步骤

（1）     查询所有姓“王”的学生姓名及其所选的课程号，课程名和期末考试成绩

select student.sname,course.cno,course.cname,score.score from student inner join score on student.sno = score.sno join course on score.cno = course.cno where student.sname like '王%';

（2）     查询和“张丹丹”老师同一个院系的学生的姓名和院系名称

 

select student.sname,class.department from student join class on student.clno = class.clno where class.department = ( select department from teacher where tname = '张丹丹');

（3）     查询和“朱凡”在同一个班的所有男同学的信息。

 

select * from student where clno = ( select clno from student where sname = '朱凡') and sex = '男';

（4）        查询与“耿娇”同年出生的学生学号、姓名和电话（求出生年份：year(birth)）

 SELECT sno, sname, tel FROM student WHERE YEAR(birth) = (SELECT YEAR(birth)  FROM student WHERE sname = '耿娇' );

（5）     查询计算机学院的女生姓名，及其所选的课程名以及该课程的平时成绩，期末成绩

 

select student.sname, course.cname, score.usual, score.score from student inner join class on student.clno = class.clno inner join score on student.sno = score.sno inner join course on score.cno = course.cno where class.department = '计算机学院' and student.sex = '女';

（6）     查询邮箱不为空并且 “马克思主义基本原理”课程期末成绩在80分以下的学生学号、姓名和院系，并按学生院系升序排列，同一院系的学生按出生日期降序排列

select s.sno,s.sname,cl.department from student s inner join class cl on s.clno inner join score sc on s.sno = sc.sno join course c on sc.cno = c.cno where s.email is not null and c.cname = '马克思主义基本原理' and sc.score >= 80;

（7）     查询“100101”课程比“200101”课程成绩高的学生学号

 

select s.sno from student s inner join score sc1 on s.sno = sc1.sno inner join score sc2 on s.sno = sc2.sno where sc1.cno = '100101' and sc2.cno = '200101' and sc1.score > sc2.score;

（8）     查询选修了 “010002”和“010003”号课程的学生学号和姓名

 

 select c.cno, c.cname,c.credit from course c where not exists ( select 1 from score sc where sc.cno = c.cno);

（9）     查询没有选修任何课程的学生学号和姓名

  

 select s.sname,s.sno from student s where exists (select 1 from course_class cc where cc.clno = s.clno and cc.cno = '010002') and exists (select 1 from course_class cc where cc.clno = s.clno and cc.cno = '010003');

（10）    “朱凡”同学没有选的课程名（选做）

 

select c.cno from course c where not exists(select 1 from student,score where score.sno= student.sno and c.cno = score.cno and student.sname = '朱凡');