内蒙古大学 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;
查询结果: