7 short labs in Database

GEIT 3341 DATABASE I LAB 6

GEIT 3341 Database I

Lab 6

Creating & Manipulating Views

Due Date:

Objective(s) Target CLO(s) Reference 1) To practice creating views

using the CREATE VIEW command of SQL DDL.

2) To practice granting and revoking privileges using the GRANT and REVOKE privileges.

6 Chapter 7

Name ID Section

Instructions:

1. Create a view called V1 to show the employee first name, last name,

project name he/she works on but only for employees who spend more

than 10 hours on the project. That is, your view should show the

following content: (2 points)

FNAME LNAME PNAME ------------------------------------------------------------------------------ John Smith ProductX Jennifer Wallace Newbenefits Jennifer Wallace Reorganization Alicia Zelaya Newbenefits Ramesh Narayan ProductZ Joyce English ProductY Joyce English ProductX

GEIT 3341 DATABASE I LAB 6 Ahmad Jabbar Computerization

Write your view definition here.

Screen shot of the content of the view.

GEIT 3341 DATABASE I LAB 6

2. Create another view called V2 to show the employee first name, last

name, dependent name, and relationship. Customize the column names

as shown below: (2 points)

First Name Last Name Dependent Name Relationship

--------------------------------------------------------------------------------------------------------------

John Smith Elizabeth Spouse

John Smith Michael Son

John Smith Alice Daughter

Franklin Wong Alice Daughter

Franklin Wong Theodore Son

Franklin Wong Joy Spouse

Jennifer Wallace Abner Spouse

Write your view definition here.

Screen shot of the content of the view.

GEIT 3341 DATABASE I LAB 6

3. Create another view called V3 to show the employee first and last name

of all employees who have a dependent whose name starts with A . That

is, your view should display the following: (2 points)

FNAME LNAME

------------------------------------------------------------

John Smith

Franklin Wong

Jennifer Wallace

Write your view definition here.

Screen shot of the content of the view.

GEIT 3341 DATABASE I LAB 6

4. Write a query based on the view V1 in step 1 to retrieve all rows with

project name equals ProductX. (1 point)

Write your query definition here.

Screen shot of the content of the query.

GEIT 3341 DATABASE I LAB 6

5. Write a query based on the view V2 in step 2 to retrieve all rows with

relationship as Spouse. (1 point)

Write your query definition here.

Screen shot of the content of the query.

GEIT 3341 DATABASE I LAB 6

The next two steps must be completed in the lab.

6. Using the GRANT command, give your instructor (whose username is

instructor) a SELECT privilege on either V1, V2 or V3. Check with your

instructor that he can access your view. (1 point)

7. Finally, use the REVOKE command to revoke the SELECT privilege

granted in the previous step from your instructor. Check with him again

that he no longer can access your view. (1 point)