oracle实验三创建视图,实验1——Oracle数据查询

一、实验题目

Oracle数据查询设计

二、实验目的

熟悉统计查询、子查询、外连接查询、递归查询等高级查询设计。

三、实验内容

1、编写SQL创建视图view_getTeacherbyDeptID，获取信息技术学院及其下属部门的所有教师信息。

create or replace view view_getTeacherbyDeptID

as

select * from teacher

where dept_id in (

select dept_id from dept

start with dept_name='信息技术学院'

connect by prior dept_id=parent_id

);

2、编写SQL创建视图view_getTeacherCountbyProfess，统计各个职称级别的教师人数。

create or replace view view_getTeacherCountbyProfess

as

select count(*) "教师人数",

decode(profess,'教授','1-教授','副教授','2-副教授','讲师','3-讲师','助教','4-助教') "职称级别"

from teacher

group by profess

order by "职称级别";

3、编写SQL创建视图view_getCourseCountForTearch，统计任课表中各教师的授课数。

create or replace view view_getCourseCountForTearch

as

select

count(*) "课程数",

teacher_id "教师编号" ,

(select tname from teacher where teacher_id=tc.teacher_id) "教师姓名"

from tc

group by teacher_id;

4、编写SQL创建视图view_getCourseCountForAllTearch，统计所有教师的授课数。

create or replace view view_getCourseCountForAllTearch

as

select a.*,b.c "授课数" from teacher a,

(select count(*) c,teacher_id from tc group by teacher_id) b

where a.teacher_id= b.teacher_id(+);