EX.NO:4
VIEWS
AIM:
To study and create View commands
VIEWS:
In SQL, a view is a virtual table
based on the result-set of an SQL statement.A view contains rows and columns,
just like a real table. The fields in a view are fields from one or more real
tables in the database.
SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL
statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
SQL
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SELECT column_name(s)
FROM table_name
WHERE condition
SQL Updating a View
You can update a view by using the following syntax:
SQL
CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name
AS
SELECT column_name(s)
FROM table_name
WHERE condition
SELECT column_name(s)
FROM table_name
WHERE condition
SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL
DROP VIEW Syntax
DROP VIEW view_name
************
VIEWS
************
TABLE
1 CREATION
***************
` SQL>
create table aa(name varchar2(20),book number(10),edition number(20),price
number(20),
ISBN number(20));
Table
created.
SQL>
insert into aa
values('&name',&number,&edition,&price,&ISBN);
Enter
value for name: bb
Enter
value for number: 23
Enter
value for edition: 2001
Enter
value for price: 12
Enter
value for isbn: 23435
old 1: insert into aa
values('&name',&number,&edition,&price,&ISBN)
new 1: insert into aa
values('bb',23,2001,12,23435)
1
row created.
SQL>
/
Enter
value for name: cc
Enter
value for number: 55
Enter
value for edition: 342
Enter
value for price: 76
Enter
value for isbn: 687478
old 1: insert into aa
values('&name',&number,&edition,&price,&ISBN)
new 1: insert into aa
values('cc',55,342,76,687478)
1
row created.
SQL>
/
Enter
value for name: dd
Enter
value for number: 2
Enter
value for edition: 1233
Enter
value for price: 123
Enter
value for isbn: 53616578
old 1: insert into aa
values('&name',&number,&edition,&price,&ISBN)
new 1: insert into aa
values('dd',2,1233,123,53616578)
1
row created.
SQL>
/
Enter
value for name: ee
Enter
value for number: 21
Enter
value for edition: 1111
Enter
value for price: 111
Enter
value for isbn: 12435798
old 1: insert into aa
values('&name',&number,&edition,&price,&ISBN)
new 1: insert into aa
values('ee',21,1111,111,12435798)
1
row created.
TABLE
2 CREATION
*****************
SQL>
create table qq(name varchar2(20),book number(10),author varchar(20),publisher
varchar2(20),
ISBN
number(20));
Table
created.
SQL>
select * from aa;
NAME BOOK EDITION
PRICE ISBN
--------------------
---------- ---------- ---------- ----------
bb 23 2001 12
23435
cc 55 342 76
687478
dd 2 1233 123
53616578
ee 21 1111 111
12435798
SQL>
insert into qq
values('&name','&author',&number,'&publisher',&ISBN);
Enter
value for name: bb
Enter
value for author: 21
Enter
value for number: 23
Enter
value for publisher: dfd
Enter
value for isbn: 573568
old 1: insert into qq
values('&name','&author',&number,'&publisher',&ISBN)
new 1: insert into qq
values('bb','21',23,'dfd',573568)
1
row created.
SQL>
/
Enter
value for name: cc
Enter
value for author: 43
Enter
value for number: 55
Enter
value for publisher: fg
Enter
value for isbn: 65839
old 1: insert into qq
values('&name','&author',&number,'&publisher',&ISBN)
new 1: insert into qq
values('cc','43',55,'fg',65839)
1
row created.
SQL>
/
Enter
value for name: ee
Enter
value for author: 44
Enter
value for number: 21
Enter
value for publisher: dfd
Enter
value for isbn: 1235798
old 1: insert into qq
values('&name','&author',&number,'&publisher',&ISBN)
new 1: insert into qq
values('ee','44',21,'dfd',1235798)
1
row created.
SQL>
/
Enter
value for name: oo
Enter
value for author: 87
Enter
value for number: 34
Enter
value for publisher: gfh
Enter
value for isbn: 6358379
old 1: insert into qq
values('&name','&author',&number,'&publisher',&ISBN)
new 1: insert into qq
values('oo','87',34,'gfh',6358379)
1
row created.
SQL>
select * from qq;
NAME BOOK AUTHOR PUBLISHER
--------------------
---------- -------------------- -------------------- ISBN
----------
bb 21 23 dfd
573568
cc 43 55 fg
65839
ee 44 21 dfd
1235798
NAME BOOK AUTHOR PUBLISHER ISBN
--------------------
---------- -------------------- -------------------- ----------
oo 87 34 gfh 6358379
CREATE
VIEW STATEMENT
**********************
SQL>create
view ww as select book,name,publisher from qq where ISBN=573568
View
created.
SQL>
select * from ww;
BOOK NAME PUBLISHER
- ---------
-------------------- --------------------
21 bb dfd
UPDETE
VIEW STATEMENT
**********************
SQL>
update ww set publisher='qwa'where book=21;
1
row updated.
SQL>
select * from ww;
BOOK
NAME PUBLISHER
----------
-------------------- --------------------
21 bb qwa
SQL>
create view wq as select name,ISBN,publisher from qq where book>21
View
created.
SQL>
select * from wq;
NAME ISBN PUBLISHER
--------------------
---------- --------------------
cc 65839 fg
ee 1235798 dfd
oo 6358379 gfh
SQL>
create view ss as select name,book from aa union select name,book from qq;
View
created.
SQL>
select * from ss;
NAME BOOK
--------------------
----------
bb 21
bb 23
cc 43
cc 55
dd 2
ee 21
ee 44
oo 87
8
rows selected.
COMPLEX
VIEW
*************
SQL>
create view er as select author,name,ISBN from qq where book>43;
View
created.
SQL>
select * from er;
AUTHOR NAME ISBN
--------------------
-------------------- ----------
21 ee 1235798
34 oo 6358379
SQL>select
name from(select * from qq where publisher='fg')where ISBN=65839;
NAME
--------------------
Cc
DROP VIEW
*************
SQL> drop view er;
View dropped
Result
Thus the
view creation commands are executed
successfully.
No comments:
Post a Comment