内蒙古大学 2017-2018《数据库原理与应用A》实验指导书 作业二

（本文中蓝色字体为个人理解，非标准答案，仅供参考）

 

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

=======================================

 

作业二 数据库中数据的查询-----简单查询

 

一．实验内容及说明

在实验一和实验二的基础上，进行数据库的查询，充分体会 SQL 语言的强大功能。并将每个实验结果均存储为一张视图表。

方式如下：create view T2_* [(..)]as                

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

 

二．实验步骤 (Oracle 10g测试通过)

1. 查询全体学生的学号和姓名。（其视图名为 T2_1，后面以此类推）

create view T2_1 as 
select sno,sname 
from student;

查询结果：

 

2. 查询全体学生的详细记录。

create view T2_2 as 
select * 
from student;

查询结果：

 

3. 查询全体学生的姓名和出生年份。

方法1：直接用当前年份-sage，得到出生年份 

create view T2_3 as 
select sname,2018-sage birthday 
from student;	

方法2：用to_char格式化后的系统时间-sage，得到出生年份

create view T2_3_1 as 
select sname,to_char(sysdate,'yyyy')-sage birthday 
from student;

查询结果:

 

4. 查询选修了课程的学生的学号。

create view T2_4 as 
select sno 
from sc;

查询结果:

 

5. 查询”cs”系的学生的姓名。

create view T2_5 as 
select sname from student,dept 
where student.sdno=dept.dno 
and dname='cs';

查询结果:

 

6. 查询年龄在 20 岁以下的学生的学号和年龄。

 

create view T2_6 as 
select sno,sage 
from student 
where sage<20;

查询结果:

 

7. 查询年龄在 20—22 岁的学生的学号。

方法1：用between 20 and 22表示 20—22

create view T2_7 as 
select sno 
from student 
where sage between 20 and 22;

方法2：用>=20 和 <=22表示20—22

create view T2_7_1 as 
select sno 
from student 
where sage>=20 and sage<=22;

查询结果:

 

8. 查询职称为‘副教授’的教师的信息。

create view T2_8 as 
select * 
from teacher 
where prof='副教授';

查询结果:

 

9. 查询姓“王”的学生的信息。

create view T2_9 as 
select * 
from student 
where sname like'王%';

查询结果:

 

10. 查询选修了课程的学生的学号及成绩。

create view T2_10 as 
select sno,grade 
from sc;

查询结果:

 

11. 查询学生及详细选课信息。

方法1：把学生表和选课表连接，只查询学生姓名+选课信息

 

create view T2_11 as
select sc.sno,sname,cno,grade /*查询sc.sno和查询student.sno是一样的*/
from student,sc
where student.sno=sc.sno;

方法1.1：把学生表和选课表连接，查询学生所有信息+选课信息

create view T2_11_1 as
select sc.sno,sname,ssex,sage,sdno,cno,grade  /*查询sc.sno和查询student.sno是一样的*/
from student,sc
where student.sno=sc.sno;

 

进阶一：

方法2.1：使用left join ... on，把学生表、选课表左外连接，查询学生所有信息+选课信息

create view T2_11_2_1 as 
select student.sno,sname,ssex,sage,sdno,cno,grade   /*查询sc.sno和查询student.sno是一样的*/
from student left join sc on(student.sno=sc.sno);

方法2.2：使用left join ... using，把学生表、选课表左外连接，查询学生所有信息+选课信息

 

create view T2_11_2_2 as 
select sno,sname,ssex,sage,sdno,cno,grade 
from student left join sc using(sno);

方法2.3：使用where  某属性=某属性(+)，把学生表、选课表左外连接，查询学生所有信息+选课信息

create view T2_11_2_3 as 
select student.sno,sname,ssex,sage,sdno,cno,grade   /*查询sc.sno和查询student.sno是一样的*/
from student,sc
where student.sno=sc.sno(+);

 

进阶二：

方法3.1：使用两次left join ... on，把学生表、选课表、课程表左外连接，查询学生所有信息+选课信息+课程信息

create view T2_11_3_1 as 
select student.sno,sname,ssex,sage,sdno,course.cno,cname,ccredit,grade   /*查询sc.sno和查询student.sno是一样的，查询sc.cno和查询course.cno是一样的*/
from student left join sc on(student.sno=sc.sno) left join course on(sc.cno=course.cno);  

 

方法3.2：使用两次left join ... using，把学生表、选课表、课程表左外连接，查询学生所有信息+选课信息+课程信息

 

create view T2_11_3_2 as 
select sno,sname,ssex,sage,sdno,cno,cname,ccredit,grade 
from student left join sc using(sno) left join course using(cno);

 

方法3.3：使用where 某属性=某属性(+) and 某属性=某属性(+)，把学生表、选课表、课程表左外连接，查询学生所有信息+选课信息+课程信息

 

create view T2_11_3_3 as 
select student.sno,sname,ssex,sage,sdno,course.cno,cname,ccredit,grade   /*查询sc.sno和查询student.sno是一样的，查询sc.cno和查询course.cno是一样的*/
from student,sc,course 
where student.sno=sc.sno(+) 
and sc.cno=course.cno(+);

查询结果:

 

12. 查询选修‘1001’课程的学生的最高分。

方法1：仅查询最高分

create view T2_12 as 
select max(grade) max   /*max(grade)是聚集函数，后面的max是别名，别名可以随意取名*/
from sc 
where cno='1001'; 

方法2：不相关子查询，查询学生学号+最高分

create view T2_12_2 as
select sno,grade
from sc
where cno='1001'
and grade=( select max(grade) max   /*max(grade)是聚集函数，后面的max是别名，别名可以随意取名*/
	    from sc 
	    where cno='1001'
	  );

 

方法2.1：相关子查询，查询学生学号+最高分

create view T2_12_2_1 as
select sno,grade
from sc a   /*父查询表sc的别名叫做a*/
where grade=( select max(grade) max   /*max(grade)是聚集函数，后面的max是别名，别名可以随意取名*/
	      from sc b    /*子查询表sc的别名叫做b*/
	      where a.cno=b.cno
	      and cno='1001'
	    );		

查询结果:

 

13. 查询性别为‘女’且职称为‘副教授’的教师信息。

create view T2_13 as 
select * 
from teacher 
where tsex='女' 
and prof='副教授';

查询结果:

 

14. 查询学分低于或等于 3 的课程信息。

create view T2_14 as 
select * 
from course 
where ccredit<=3;

查询结果:

 

=======================================

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