VIEWS
SQL> create view view4 as(select account.accno,account.balance,
depositer.custid, depositer.bname from account,depositer where
account.accno=depositer.accno);
View
created.
SQL> select * from view4;
ACCNO
BALANCE CUSTID BNAME
----------
---------- ---------- --------------------
1701
17859 111 State Bank
1702
63875 112 TMB
1703
52985 113 IOB
1704
75426 114 Syndicate
1705
42987 115 Baroda
SQL> create view view5 as select
c.custid,c.cname,c.address,d.accno,d.bname from customer c,depositer d where
c.custid=d.custid;
View
created.
SQL> select * from view5;
CUSTID CNAME ADDRESS ACCNO BNAME
----------
-------------------- -------------------- ---------- --------------------
111 Sunil 1,Nehru St,Erode 1701 State
Bank
112 Kishore 2,Gandi St,Mdu 1702 TMB
113 Harish 3,Rajaji St,Cng 1703 IOB
CUSTID CNAME ADDRESS ACCNO BNAME
----------
-------------------- -------------------- ---------- --------------------
114 Akil 4,Bose St,Vnr 1704 Syndicate
115 Angel 5,PT St,Tmm 1705 Baroda
SQL> create view view6 as select * from
empl where address='madurai';
View
created.
SQL> select * from view6;
no rows
selected
SQL> insert into view6
values(&empno,'&empname','&address',&pay, '&qualification');
Enter value
for empno: 1000
Enter value
for empname: Sunil
Enter value
for address: madurai
Enter value
for pay: 12000
Enter value
for qualification: B.Sc.
old 1: insert into view6
values(&empno,'&empname','&address',&pay,'&qualification')
new 1: insert into view6
values(1000,'Sunil','madurai',12000,'B.Sc.')
1 row
created.
SQL> select * from view6;
EMPNO EMPNAME ADDRESS PAY QUALIFICATION
----------
-------------------- -------------------- ---------- ----------------------
1000 Sunil madurai 12000 B.Sc.
SQL> select * from empl;
EMPNO EMPNAME ADDRESS PAY QUALIFICATION
----------
-------------------- --------------------
---------- ----------------------
101 Ashok 6,Lalu St,Mdu 8000 B.com
102 Sharan 7,NGO Nagar,Vnr 7500 B.Ed
103 Tharun 8,KK Nagar,Ed 12000 B.E.
EMPNO EMPNAME ADDRESS PAY QUALIFICATION
----------
-------------------- --------------------
---------- ----------------------
104 Shailesh 9,PTN St,Tmm 18000 B.E.
105 Eashan 5,Raja St,Tmm 42000 B.E.
1000 Sunil madurai 12000 B.Sc.
6 rows
selected.
SQL> alter table account add primary
key(accno);
Table
altered.
SQL> delete from view4 where accno=1704;
1 row
deleted.
SQL> select * from view4;
ACCNO
BALANCE CUSTID BNAME
----------
---------- ---------- --------------------
1701
17859 111 State Bank
1702
63875 112 TMB
1703
52985 113 IOB
1705
42987 115 Baroda
SQL> alter table depositer add constraint dp
foreign key(accno)references account(accno);
Table
altered.
SQL> alter table customer add primary key
(custid);
Table
altered.
SQL> update view5 set bname='AXIS CHN'
where cname='Angel';
1 row
updated.
SQL> select * from view5;
CUSTID CNAME ADDRESS ACCNO BNAME
----------
-------------------- -------------------- ---------- --------------------
111 Sunil 1,Nehru St,Erode 1701 State
Bank
112 Kishore 2,Gandi St,Mdu 1702 TMB
113 Harish 3,Rajaji St,Cng 1703 IOB
CUSTID CNAME ADDRESS ACCNO BNAME
----------
-------------------- -------------------- ---------- --------------------
115
Angel 5,PT St,Tmm 1705 AXIS CHN
No comments:
Post a Comment