East West University
Department of Computer Science and Engineering
CSE 302: LAB 06
Course Instructor: Dr. Mohammad Rezwanul Huq
Writing SQL Statements involving Outer Joins, Creating Views and
Granting and Revoking Authorization
Lab Objective
Familiarize students with Outer joins, Views and Authorization in Oracle.
Lab Outcome
After completing this lab successfully, students will be able to:
1. Construct SQL Statements involving outer joins.
2. Understand, create and use views in a SQL Statement.
3. Understand and execute authorization statement.
Psychomotor Learning Levels
This lab involves activities that encompass the following learning levels in psychomotor domain.
Level Category Meaning Keywords
P1 Imitation Copy action of Relate, Repeat, Choose, Copy,
another; observe and Follow, Show, Identify, Isolate.
replicate.
P2 Manipulation Reproduce activity Copy, response, trace, Show,
from instruction or Start, Perform, Execute,
memory Recreate.
Lab Activities
Activity 1: Outer Joins
➔ Find both account holder and non-account holder customers.
select * from customer natural left join depositor;
Activity 2: Views
➔ Create a view named CustomerAtStamford that contains customer name and street and those who
live in Stamford.
create view CustomerAtStamford as
select customer_name, customer_street
from customer
where customer_city = ‘Stamford’;
➔ Check the user-defined views by executing the following SQL statement.
select * from user_views;
➔ Show the content of CustomerAtStamford view.
select * from CustomerAtStamford;
➔ Create another view named CustomerAtStamford_Putnam that contains name of customers who live
in ‘Putnam’ street based on CustomerAtStamford view.
create view CustomerAtStamford_Putnam as
Select customer_name
From CustomerAtStamford_yourname
Where customer_street = ‘Putnam’;
➔ Check the content of the CustomerAtStamford_Putnam view.
Activity 3: Authorization
➔ Create a user c##alice with minimum privileges.
create user c##alice identified by cse301;
grant resource, connect, create session, unlimited tablespace,
create table, create view to c##alice;
➔ Grant the privilege to alice so that she can access (only select and insert) CustomerAtStamford
view created by you.
Grant select, insert on CustomerAtStamford to c##alice;
➔ Now, connect as alice to the database and execute the following SQL statement.
Select * from <owner_name>.CustomerAtStamford;
➔ Insert a tuple into the view CustomerAtStamford.
Insert into <owner_name>.CustomerAtStamford values (‘Peter’,
‘Bricklane’);
➔ Is this view updateable?
➔ Check the granted authorizations to/from alice by executing the following SQL statement.
Select * from user_tab_privs;
➔ Revoke insert privilege from alice. (executed by your account)
Revoke insert on CustomerAtStamford_yourname from c##alice;
➔ Check the granted authorizations to/from alice again. Can you see any change?
