DBMS-Assignment -8

 Assignment 8 

Objectives :-To create views.

Views can be thought of as stored queries, which allow us to create a database object that functions very similarly to a table, but whose contents dynamically reflect the selected rows. Views are very flexible; you mayuse a view to address common simple queries to a single table, as well as for complicated ones which may spanacross several tables.

Creating a View : 

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] )

 ] AS query

CREATE VIEW defines a view of a query. The view has no physical existence, but is dynamically created whenever it is referenced in a query. CREATE OR REPLACE VIEW replaces an existing view of same name. The new query must use the same column names in the same order and with the same data types, but it may add additional columns to the end of the list.

Schema name ( CREATE VIEW myschema.myview ) must be specified to create a view in schema other than the current schema. The TEMPORARY or TEMP parameter is specified to create Temporary views, however If any of the tables referenced by the view are temporary, the view is created as a temporary view (whether TEMPORARY isspecified or not). Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view. View names should be distinct. 

Name: Name (optionally schema-qualified) of a view to be created.

column_name: An optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.

Query: 

A SELECT :- 

command which will provide the columns and rows of the view.

Example to create a view consisting of all comedy films

CREATE VIEW comedies AS

SELECT *

FROM films

WHERE type = 'Comedy';

ALTER VIEW :-

statement is used to change the definition of a view.

ALTER VIEW name ALTER [ COLUMN ] column SET DEFAULT expression

ALTER VIEW name ALTER [ COLUMN ] column DROP DEFAULT

DROP VIEW :-

 statement is used to remove a view

DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Set A

Using the Bank database

1. Create a view which contains the details of all customers who have applied for a loan more than Rs.100000.

2. Create a view which contains details of all loan applications from the ‘Sadashiv peth’ branch.

3. Write the following Queries, on the above created views :

a. List the details of customers who have applied for a loan of Rs. 500000.

b. List the details of loan applications fromSadashiv peth , where loan amount is > Rs 50000.

c. List the details of Loan applications, with the same loan amount.

We have already created the Bank Database so We will use it as it is .

bank4=# select * from Customer;

c_no | c_name | c_add | c_city

------+----------------------+-------------------------------------+----------------------

1 | Raj | Laxmiroad | Pune

2 | Disha | Churchgate | Mumbai

3 | Mahesh | Marketyard | Pune

4 | Jay | ABC road | Nashik

5 | Preeti | XYZ road | Nagar

6 | Kartik | Thane | Mumbai

(6 rows)

bank4=# select * from Loan_app;

l_no | l_amt_required | l_amt_approved | l_date

------+----------------+----------------+------------

121 | $50,000.00 | $45,000.00 | 2010-09-15

131 | $75,000.00 | $60,000.00 | 2005-09-07

141 | $100,000.00 | $80,000.00 | 1999-11-27

151 | $200,000.00 | $150,000.00 | 2012-04-19

161 | $500,000.00 | $450,000.00 | 2006-12-08

171 | $300,000.00 | $250,000.00 | 2005-12-04

181 | $500,000.00 | $350,000.00 | 2002-12-08

191 | $50,000.00 | $40,000.00 | 2012-06-18

bank4=# select * from B_C_L;

br_id | c_no | l_no

-------+------+------

101 | 3 | 141

101 | 1 | 161

102 | 1 | 171

102 | 6 | 151

103 | 2 | 121

103 | 5 | 161

103 | 4 | 131

104 | 2 | 131

(8 rows)

Que 1. Create a view which contains the details of all customers who have applied for a loan more than Rs.100000. 

Query :- 

create view v1 as select Customer.C_no,Customer.C_name,L_amt_required from

B_C_L,Customer,Loan_app where Customer.C_no=B_C_L.C_no and Loan_app.L_no=B_C_L.L_no and L_amt_required>'$100000';

CREATE VIEW

bank4=# select * from v1;

c_no | c_name | l_amt_required

------+----------------------+----------------

1 | Raj | $500,000.00

1 | Raj | $300,000.00

6 | Kartik | $200,000.00

5 | Preeti | $500,000.00

2 | Disha | $500,000.00

4 | Jay | $500,000.00

(6 rows)

Que 2. Create a view which contains details of all loan applications from the ‘Sadashiv peth’ branch.

Query:- 

create view v2 as select Loan_app.L_no,Loan_app.L_amt_required,Loan_app.L_amt_approved,Loan_app.L_date from Loan_app,Branch,B_C_L where Loan_app.L_no=B_C_L.L_no and Branch.Br_id=B_C_L.Br_id and Br_name='Sadashiv Peth';

CREATE VIEW

bank4=# select * from v2;

l_no | l_amt_required | l_amt_approved | l_date

------+----------------+----------------+------------

110 | $500,000.00 | $400,000.00 | 2014-09-12

191 | $50,000.00 | $40,000.00 | 2012-06-18

181 | $500,000.00 | $350,000.00 | 2002-12-08

(3 rows)

a. List the details of customers who have applied for a loan of Rs. 500000.

select C_no,C_name from v3 where L_amt_required='$500000';

b. List the details of loan applications from Sadashiv peth , where loan amount is > Rs 50000.

 

select * from v2 where L_amt_required>'$50000';

 

c. List the details of Loan applications, with the same loan amount.

select L_no,L_amt_required from v2 where L_amt_required in(select L_amt_required from v2);

 

 

SET B 
Using Project-Employee database

 1. Create a view over the employee table which contains only employee name and his qualification and it should be sorted on qualification.

 

QUERY :-

 create view v1 as select emp_name,qualification from Employee order by qualification;

 

select * from v1;

 

2. Create a view containing the project name, project type and start date of the project and should be sorted by start date of the project.

 

 QUERY :-  

create view v2 as select P_name,P_type,start_date from Proj_Emp,Project where Project.P_no=Proj_Emp.P_no order by start_date;

 
3. Write the following Queries, on the above created views :
a. List different qualifications of employees.

 QUERY :-

select qualification from v1 group by qualification;

 
b. List the name and type of projects started on 1st April 2014.

 QUERY :-

select P_name,P_type from v2 where start_date='2014-04-01';

 
c. List the names of employees who are qualified as Engineers.

 QUERY :-

  select * from v1 where qualification='Engineering ';