CMSC 4003 - University of Central Oklahoma
CMSC 4003
Lab Assignment 7: Views
Name:
Due: See the due date in WebCT calendar.
1. Download lab7_schema.sql and run lab7_schema.sql in SQL*Plus (@lab7_schema). The schema is based on the EER diagram used in Lab Assignment 6. In this lab, records are inserted into tables directly from the file lab7_schema.sql.
2. Create a view named OKCStudent using the following statement:
create or replace view OKCStudent(id, name)
as select sid, sname
from student
where curaddress='OKC';
(a) Run the following SQL statement to check the view.
select * from OKCStudent;
(b) A view can be considered as a virtual table with no data in it. When Oracle runs an SQL statement on a view, it first transforms the SQL statement into an equivalent SQL statement on the base tables and then runs the transformed SQL statement to retrieve the result. So, the following SQL statement
select * from OKCStudent where name='Susan';
is transformed into:
select sid from student where sname='Susan' and curaddress='OKC';
Try both of the above statements to see the results.
(c) Run the following SQL statements:
insert into OKCStudent(id, name) values('A0001', 'Scott');
commit;
Run the following SQL statement:
select * from OKCStudent;
Do you see the tuple that you have just inserted? Why?
Answer:
Run the following SQL statement:
select * from Student;
Do you see the tuple that you have just inserted? Why?
Answer:
(d) You have to be careful about the semantic of the view when inserting tuples into a view. In many situations, you would rather not allow inserting into a view.
Change the view definition of OKCStudent to make it read only as follows:
create or replace view OKCStudent(id, name)
as select sid, sname from student where curaddress='OKC'
with read only;
(e) Run the following SQL statements to check the view.
select * from OKCStudent;
insert into OKCStudent(id, name) values('A1000', 'Tony');
Can you insert the tuple this time?
Answer:
3. Create another view as follows:
create or replace view GoodStudent (sid, degree, programname, avggrade)
as select s.sid, progtype, programname, avg(grade)
from degreeprogram dp, student s, taken t
where dp.progid=s.major and s.sid=t.sid
group by s.sid, progtype, programname
having avg(grade)>3.0;
(a) Run the following SQL statement to check the view.
select * from GoodStudent;
(b) For the following SQL statement:
select * from GoodStudent where degree='BS';
provide the corresponding transformed SQL statement on the base tables.
Answer:
Run your transformed SQL statement to compare with the SQL statement directly on the view GoodStudent.
(c) Run the following insertion statement:
insert into GoodStudent (sid) values ('A1001');
Why the tuple cannot be inserted?
Answer:
4. Create the follow view:
create or replace view studentcourse (sid, sname, cno, grade)
as select s.sid, sname, cno, grade
from student s, taken t, courseoffering co
where s.sid=t.sid and t.seqid=co.seqid;
(a) For the following SQL statement:
select cno, count(*), max(grade), min(grade), avg(grade)
from studentcourse
where cno='CS480'
group by cno;
provide the corresponding transformed SQL statement on the base tables.
Answer:
Run your transformed SQL statement to compare with the SQL statement directly on the view studentcourse.
5. Create the following view:
create or replace view facultydept (facssno, facname, deptname)
as select facssno, facname, deptname
from faculty f, department d
where f.worksfor=d.deptid;
(a) Run the following SQL statement to check the view:
select * from facultydept;
(b) Try the following two insertion statements, one into studentcourse and the other into facultydept
insert into studentcourse (sid, sname) values ('A1002', 'Mary');
insert into facultydept (facssno, facname) values ('000000000', 'Jim');
commit;
Indicate why the second insertion works but the first one does not.
Answer:
6. Now you design and create the following views.
(a) Define a view OKCGoodStudent on top of the two views OKCStudent and GoodStudent.
OKCGoodStudent (id, name, degree, programname, avggrade)
Answer:
(b) A view contains only those students in CS480 whose major is offered by the CS department.
CS480CSStudent (sid, sname, seqid, semester, year, instructor, grade)
Answer:
Run your CREATE VIEW statements to test their correctness.
Note: You can also use CREATE MATERIALIZED VIEW statement to create materialized views in Oracle. Use SQL references at otn. to check the syntax of the statement.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- university of oklahoma academic calendar 2019
- university of oklahoma semester schedule
- university of oklahoma philosophy dept
- university of oklahoma calendar
- university of oklahoma salaries
- university of oklahoma football players
- university of oklahoma continuing education
- university of oklahoma printable map
- university of oklahoma enrollment numbers
- university of oklahoma outreach program
- university of oklahoma extended campus
- university of oklahoma degree