oracle视图进行查询题

Question

前期代码准备：

create table s

        (xh  char(10)  primary  key,

         xm    char(8),

         age      int,

         sdept    char(20),

         csrq     date);

      create  table    sc

         (xh       char(10),

         ch        char(2),

         g         int,

       primary key(xh,ch));

        create  table   c

        (ch          char(2)   primary key,

         cm         char(20),

         xf          smallint);

// SMALLINT 数据类型是一种精确数值数据类型,其精度在算术运算后不变。它需要 2 个字节存储。

要求：创建完SC表后，给该表添加外键约束。添加完成外键约束后，用user_constraints，user_cons_columns查看添加的约束。

alter table sc add constraint fk_xh foreign key(xh) references s(xh);

select * from user_constraints;

select * from user_cons_columns;

在完成外键约束的添加后，在三个表中加入如下记录（注意记录的添加顺序，注意日期型数据的表示方法，如不正确，请改正。）

INSERT  INTO S  VALUES(0001,'a',23,'计算机','12/5/1983');

[INSERT  INTO S  VALUES(0001,'a',23,'计算机',date'1983-5-12');]

INSERT  INTO S  VALUES(0002,'b',21,'计算机','11/3/1985');

INSERT  INTO S  VALUES(0003,'c',24,'数学','2/3/1982');

INSERT  INTO S  VALUES(0004,'d',19,'外语','3/5/1987');

INSERT  INTO Sc  VALUES(0001,1,70);

INSERT  INTO Sc  VALUES(0001,2,72);

INSERT  INTO Sc  VALUES(0001,3,73);

INSERT  INTO Sc  VALUES(0002,1,65);

INSERT  INTO Sc  VALUES(0002,2,54);

INSERT  INTO Sc  VALUES(0003,3,35);

INSERT  INTO c  VALUES(1,'DB',4);

INSERT  INTO c  VALUES(2,'DS',4);

INSERT  INTO c  VALUES(3,'NET',3);

 

添加完数据后，完成以下练习：

1. 以S表为基础，把所有“计算机”系的学生建立一个名为JSJVIEW的视图，此视图中包含S表的所有字段。（在创建视图命令中不加入：WITH CHECK OPTION）

create or replace view JSJVIEW as select * from s where xm='计算机';

1. 以S表为基础，把所有“计算机”系的学生建立一个名为JSJVIEW2的视图，此视图中包含S表的所有字段。但在创建视图命令中加入：WITH CHECK OPTION

create or replace view JSJVIEW2 as select * from s where xm='计算机' with check option;

3、以SC表和C表为基础，把每门课的课程名和平均成绩建立一个视图AVGVIEW，此视图中包含两个字段分别为：课程号和平均成绩。

4、使用视图JSJVIEW查询AGE>22的学生

5、使用视图AVGVIEW查询课程平均成绩>60的课程名

Answer 1

1. 创建JSJVIEW视图：

create or replace view JSJVIEW as select * from s where sdept='计算机';

1. 创建JSJVIEW2视图：

create or replace view JSJVIEW2 as select * from s where sdept='计算机' with check option;

1. 创建AVGVIEW视图：

create or replace view AVGVIEW as 
select sc.ch, avg(sc.g) as 平均成绩, c.cm as 课程名 
from sc join c on sc.ch = c.ch 
group by sc.ch, c.cm;

1. 查询JSJVIEW视图中满足AGE>22的学生：

select * from JSJVIEW where age > 22;

1. 查询AVGVIEW视图中平均成绩>60的课程名：

select 课程名 from AVGVIEW where 平均成绩 > 60;