DBMS VIEW

SQL> @ e:/assignment4.sql;

SQL> REM 1:

SQL>REM***********************************************************

SQL> REM VIEW 1:Create a view named British_Class that gives REM for each class of Great Britain its class, type,number of REM guns bore and displacement.

SQL>

SQL> CREATE OR REPLACE view British_Class as

2 SELECT class,type,numGuns,bore,country

3 FROM classes

4 WHERE country='Gt. Britain'

5 WITH CHECK OPTION CONSTRAINT ck_country;

View created.

SQL>

SQL> SELECT * FROM British_Class;

CLASS TYPE NUMGUNS BORE COUNTRY

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

Renown bc 6 15 Gt. Britain

Revenge bb 8 15 Gt. Britain

SQL>

SQL> REM British_Class(class,type,numGuns,bore,country)

SQL> REM OPERATIONS ON VIEW:

SQL>

SQL> SELECT COLUMN_NAME,UPDATABLE

2 FROM USER_UPDATABLE_COLUMNS

3 WHERE TABLE_NAME='BRITISH_CLASS';

COLUMN_NAME UPD

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

CLASS YES

TYPE YES

NUMGUNS YES

BORE YES

COUNTRY YES

SQL>

SQL> SAVEPOINT A1;

Savepoint created.

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO British_Class VALUES('TITAN','bb',7,20,'Gt. Britain');

1 row created.

SQL> SELECT * FROM Classes WHERE class='TITAN';

CLASS TYPE COUNTRY NUMGUNS BORE DISPLACEMENT

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

TITAN bb Gt. Britain 7 20

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE British_Class

2 SET type='bc',numGuns=12,bore=20,class='TITANIC'

3 WHERE class='TITAN';

1 row updated.

SQL> SELECT * FROM Classes WHERE class='TITANIC';

CLASS TYPE COUNTRY NUMGUNS BORE DISPLACEMENT

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

TITANIC bc Gt. Britain 12 20

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM British_Class WHERE class='TITANIC';

1 row deleted.

SQL> SELECT * FROM Classes WHERE class='TITANIC';

no rows selected

SQL>

SQL> REM ------------------------------------------

> REM CLASSES(class,type,country,NumGuns,bore,displacement);

SQL> REM OPERATIONS ON BASE TABLE:

SQL>

SQL> SELECT COLUMN_NAME,UPDATABLE

2 FROM USER_UPDATABLE_COLUMNS

3 WHERE TABLE_NAME='BRITISH_CLASS';

COLUMN_NAME UPD

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

CLASS YES

TYPE YES

NUMGUNS YES

BORE YES

COUNTRY YES

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO Classes VALUES

2 ('TITANIC','bb','Gt. Britain',7,20,26000);

1 row created.

SQL> SELECT * FROM British_Class WHERE class='TITANIC';

CLASS TYPE NUMGUNS BORE COUNTRY

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

TITANIC bb 7 20 Gt. Britain

SQL> SELECT * FROM Classes WHERE class='TITANIC';

CLASS TYPE COUNTRY NUMGUNS BORE DISPLACEMENT

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

TITANIC bb Gt. Britain 7 20 26000

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE Classes

2 SET type='bc',class='TITANIC1',NumGuns=10,bore=11,displacement=20000

3 WHERE class='TITANIC';

1 row updated.

SQL> SELECT * FROM British_Class WHERE class='TITANIC1';

CLASS TYPE NUMGUNS BORE COUNTRY

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

TITANIC1 bc 10 11 Gt. Britain

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM Classes WHERE class='TITANIC1';

1 row deleted.

SQL> SELECT * FROM British_Class WHERE class='TITANIC1';

no rows selected

SQL>

SQL> ROLL BACK TO A1;

Rollback complete.

SQL>

SQL> REM 2:

SQL>REM***********************************************************

SQL> REM VIEW 2:Define a view Kongo_Yamato that gives for each ship of Kongo and Yamato class its ship

SQL> REM name (from outcomes), year of launch, battle involved and the result.

SQL>

SQL> CREATE OR REPLACE view Kongo_Yamato as

2 SELECT o.ship,s.launched,o.battle,o.result,s.class

3 FROM ships s JOIN outcomes o ON(s.name=o.ship)

4 WHERE s.class in('Kongo','Yamato');

View created.

SQL>

SQL> SELECT * FROM Kongo_Yamato;

SHIP LAUNCHED BATTLE RESULT CLASS

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

Kongo 1913 North Atlantic sunk Kongo

Musashi 1942 North Cape ok Yamato

Haruna 1915 North Atlantic sunk Kongo

Kirishima 1915 Guadalcanal sunk Kongo

Yamato 1941 Surigao Strait ok Yamato

SQL>

SQL> REM Kongo_Yamato(SHIP,LAUNCHED,BATTLE,RESULT,CLASS);

SQL> REM OPERATIONS ON VIEW:

SQL>

SQL> SELECT COLUMN_NAME,UPDATABLE

2 FROM USER_UPDATABLE_COLUMNS

3 WHERE TABLE_NAME='KONGO_YAMATO';

COLUMN_NAME UPD

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

SHIP YES

LAUNCHED NO

BATTLE YES

RESULT YES

CLASS NO

SQL>

SQL> SAVEPOINT A2;

Savepoint created.

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO Kongo_Yamato(SHIP,BATTLE,RESULT)VALUES('Hiei','North Cape','ok');

1 row created.

SQL> SELECT * FROM OUTCOMES WHERE ship='Hiei';

SHIP BATTLE RESULT

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

Hiei North Cape ok

SQL> SELECT * FROM SHIPS WHERE name='Hiei';

NAME CLASS LAUNCHED

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

Hiei Kongo 1914

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE Kongo_Yamato

2 SET ship='Kongo',battle='Surigao Strait',result='damaged'

3 WHERE ship='Hiei';

1 row updated.

SQL>

SQL> SELECT * FROM OUTCOMES WHERE ship='Kongo' and battle='Surigao Strait';

SHIP BATTLE RESULT

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

Kongo Surigao Strait damaged

SQL> SELECT * FROM SHIPS WHERE name='Kongo';

NAME CLASS LAUNCHED

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

Kongo Kongo 1913

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM Kongo_Yamato WHERE ship='Kongo'and battle='Surigao Strait';

1 row deleted.

SQL> SELECT * FROM OUTCOMES WHERE ship='Kongo' and battle='Surigao Strait';

no rows selected

SQL> SELECT * FROM SHIPS WHERE name='Kongo';

NAME CLASS LAUNCHED

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

Kongo Kongo 1913

SQL>

SQL> REM ------------------------------------------

> REM OPERATIONS ON BASE TABLE:

SQL>

SQL> REM OUTCOMES('SHIP','BATTLE','RESULT');

SQL>

SQL> REM SHIPS(NAME,CLASS,LAUNCHED);

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO SHIPS VALUES('vishak','Kongo',1966);

1 row created.

SQL> INSERT INTO OUTCOMES VALUES('vishak','Surigao Strait','sunk');

1 row created.

SQL> SELECT * FROM Kongo_Yamato WHERE ship='vishak' and battle='Surigao Strait';

SHIP LAUNCHED BATTLE RESULT CLASS

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

vishak 1966 Surigao Strait sunk Kongo

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE SHIPS

2 SET name='shea',class='Yamato',launched=1999

3 WHERE name='Bismark';

1 row updated.

SQL>

SQL> UPDATE OUTCOMES

2 SET ship='shea',battle='North Cape',result='ok'

3 WHERE ship='vishak' and battle='Surigao Strait';

1 row updated.

SQL>

SQL> SELECT * FROM Kongo_Yamato WHERE ship='shea' AND battle='North Cape';

SHIP LAUNCHED BATTLE RESULT CLASS

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

shea 1999 North Cape ok Yamato

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM OUTCOMES WHERE ship='shea' AND battle='North Cape';

1 row deleted.

SQL> SELECT * FROM Kongo_Yamato WHERE ship='shea' AND battle='North Cape';

no rows selected

SQL>

SQL> ROLL BACK TO A2;

Rollback complete.

SQL>

SQL> REM *********************************************************

SQL> REM 3:

SQL> REM VIEW 3:Create a view named Revenge_Ships that displays the ships (ship name, class, year of

SQL> REM launch) from the Revenge Class. Label the view columns. Do not allow a ship to be

SQL> REM reassigned to another class through the view

SQL>

SQL> CREATE OR REPLACE view Revenge_Ships (view_shipname,view_class,view_launched) as

2 SELECT name,class ,launched

3 FROM ships

4 WHERE class='Revenge'

5 WITH CHECK OPTION CONSTRAINT ck_class;

View created.

SQL>

SQL> SELECT * FROM Revenge_Ships;

VIEW_SHIPNAME VIEW_CLASS VIEW_LAUNCHED

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

Ramillies Revenge 1917

Resolution Revenge 1916

Revenge Revenge 1916

Royal Oak Revenge 1916

Royal Sovereign Revenge 1916

SQL> REM Revenge_Ships(VIEW_SHIPNAME,VIEW_CLASS, VIEW_LAUNCHED)

SQL> REM OPERATIONS ON VIEW:

SQL>

SQL> SELECT COLUMN_NAME,UPDATABLE

2 FROM USER_UPDATABLE_COLUMNS

3 WHERE TABLE_NAME='REVENGE_SHIPS';

COLUMN_NAME UPD

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

VIEW_SHIPNAME YES

VIEW_CLASS YES

VIEW_LAUNCHED YES

SQL>

SQL> SAVEPOINT A3;

Savepoint created.

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO Revenge_Ships VALUES('Apollo','Revenge',1990);

1 row created.

SQL> SELECT * FROM ships WHERE name='Apollo';

NAME CLASS LAUNCHED

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

Apollo Revenge 1990

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE Revenge_Ships

2 SET VIEW_SHIPNAME='NEW Apollo',VIEW_CLASS='Revenge',VIEW_LAUNCHED=1978

3 WHERE VIEW_SHIPNAME='Apollo';

1 row updated.

SQL>

SQL> SELECT * FROM ships WHERE name='NEW Apollo';

NAME CLASS LAUNCHED

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

NEW Apollo Revenge 1978

SQL>

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM Revenge_Ships WHERE VIEW_SHIPNAME='NEW Apollo';

1 row deleted.

SQL> SELECT * FROM ships WHERE name='NEW Apollo';

no rows selected

SQL>

SQL> REM ------------------------------------------

> REM OPERATIONS ON BASE TABLE:

SQL> REM SHIPS(NAME,CLASS,LAUNCHED);

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO Ships VALUES('Akira','Revenge',1990);

1 row created.

SQL> SELECT * FROM Revenge_Ships WHERE VIEW_SHIPNAME='Akira';

VIEW_SHIPNAME VIEW_CLASS VIEW_LAUNCHED

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

Akira Revenge 1990

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE Ships

2 SET name='NEW Akira'

3 WHERE name='Akira';

1 row updated.

SQL>

SQL> SELECT * FROM Revenge_Ships WHERE VIEW_SHIPNAME='NEW Akira';

VIEW_SHIPNAME VIEW_CLASS VIEW_LAUNCHED

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

NEW Akira Revenge 1990

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM ships WHERE name='NEW Akira';

1 row deleted.

SQL> SELECT * FROM Revenge_Ships WHERE VIEW_SHIPNAME='NEW Akira';

no rows selected

SQL>

SQL> ROLL BACK TO A3;

Rollback complete.

SQL>

SQL> REM *********************************************************

SQL> REM 5:

SQL> REM VIEW 5:Create a view named Class_Ships that displays the number of ships in each class. Label the view columns.

SQL>

SQL> CREATE OR REPLACE view Class_Ships(class,No_of_ships) as

2 SELECT class,count(name)

3 FROM ships

4 GROUP BY class;

View created.

SQL>

SQL> SELECT * FROM Class_Ships;

CLASS NO_OF_SHIPS

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

Iowa 4

Yamato 2

North Carolina 2

Bismark 1

Revenge 5

Renown 2

Kongo 4

Tennessee 2

8 rows selected.

SQL>

SQL> REM Class_Ships(CLASS,NO_OF_SHIPS)

SQL> REM OPERATIONS ON VIEW:

SQL>

SQL> SELECT COLUMN_NAME,UPDATABLE

2 FROM USER_UPDATABLE_COLUMNS

3 WHERE TABLE_NAME='CLASS_SHIPS';

COLUMN_NAME UPD

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

CLASS NO

NO_OF_SHIPS NO

SQL>

SQL> SAVEPOINT A5;

Savepoint created.

SQL> REM INSERT:

SQL>

SQL> INSERT INTO Class_Ships VALUES('Revolver',25);

INSERT INTO Class_Ships VALUES('Revolver',25)

*

ERROR at line 1:

ORA-01733: virtual column not allowed here

SQL> SELECT * FROM Ships WHERE class='Revolver';

no rows selected

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE Class_Ships

2 SET class='Haruna'

3 WHERE class='Revolver';

UPDATE Class_Ships

*

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL>

SQL> SELECT * FROM ships WHERE class='Haruna';

no rows selected

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM ships WHERE class='Haruna';

0 rows deleted.

SQL> SELECT * FROM Class_Ships WHERE class='Haruna';

no rows selected

SQL>

SQL> REM ------------------------------------------

> REM OPERATIONS ON BASE TABLE:

SQL> REM SHIPS(NAME,CLASS,LAUNCHED);

SQL>

SQL> INSERT INTO Ships VALUES('Mogador1','Iowa',1990);

1 row created.

SQL> SELECT * FROM Class_Ships WHERE class='Iowa';

CLASS NO_OF_SHIPS

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

Iowa 5

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE Ships

2 SET class='Bismark',name='new'

3 WHERE class='Iowa' AND name='Mogador1';

1 row updated.

SQL>

SQL> SELECT * FROM Class_Ships WHERE class='Bismark';

CLASS NO_OF_SHIPS

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

Bismark 2

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM ships WHERE class='Bismark' and name='new';

1 row deleted.

SQL> SELECT * FROM Class_Ships WHERE class='Bismark';

CLASS NO_OF_SHIPS

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

Bismark 1

SQL>

SQL> ROLL BACK TO A5;

Rollback complete.

SQL>

SQL> REM *********************************************************

SQL> REM 6:

SQL>

SQL> REM VIEW 6:Create a view called Kongo_Battle on Kongo_Yamato (as defined in 2) that gives for each

SQL> REM ship of Kongo class its ship name, battle involved and the result.

SQL>

SQL> CREATE OR REPLACE view Kongo_Battle as

2 SELECT ship,battle,result,class FROM Kongo_Yamato

3 WHERE class ='Kongo';

View created.

SQL>

SQL> SELECT * FROM Kongo_Battle;

SHIP BATTLE RESULT CLASS

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

Kongo North Atlantic sunk Kongo

Haruna North Atlantic sunk Kongo

Kirishima Guadalcanal sunk Kongo

SQL>

SQL>

SQL> REM Kongo_Battle(SHIP,BATTLE,RESULT,CLASS)

SQL> REM OPERATIONS ON VIEW:

SQL>

SQL> SELECT COLUMN_NAME,UPDATABLE

2 FROM USER_UPDATABLE_COLUMNS

3 WHERE TABLE_NAME='KONGO_BATTLE';

COLUMN_NAME UPD

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

SHIP YES

BATTLE YES

RESULT YES

CLASS NO

SQL>

SQL> SAVEPOINT A6;

Savepoint created.

SQL>

SQL> REM INSERT FOR VALID CASES:

SQL>

SQL> INSERT INTO Kongo_Battle(SHIP,BATTLE,RESULT) VALUES('Kongo','Guadalcanal','ok');

1 row created.

SQL> SELECT * FROM OUTCOMES WHERE ship='Kongo' AND battle='Guadalcanal';

SHIP BATTLE RESULT

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

Kongo Guadalcanal ok

SQL> SELECT * FROM SHIPS WHERE name='Kongo';

NAME CLASS LAUNCHED

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

Kongo Kongo 1913

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE Kongo_Battle

2 SET ship='Hiei',battle='North Atlantic',result='damaged'

3 WHERE ship='Kongo' AND battle='Guadalcanal';

1 row updated.

SQL>

SQL> SELECT * FROM OUTCOMES WHERE ship='Hiei' AND battle='North Atlantic';

SHIP BATTLE RESULT

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

Hiei North Atlantic damaged

SQL> SELECT * FROM SHIPS WHERE name='Hiei';

NAME CLASS LAUNCHED

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

Hiei Kongo 1914

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM Kongo_Battle WHERE ship='Hiei';

1 row deleted.

SQL> SELECT * FROM OUTCOMES WHERE ship='Hiei' AND battle='North Atlantic';

no rows selected

SQL> SELECT * FROM SHIPS WHERE name='Hiei';

NAME CLASS LAUNCHED

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

Hiei Kongo 1914

SQL>

SQL>

SQL> REM ------------------------------------------

> REM OPERATIONS ON BASE TABLE 1:

SQL> REM SHIPS(NAME,CLASS,LAUNCHED);

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO SHIPS VALUES('Admiral','Kongo',1987);

1 row created.

SQL> SELECT * FROM Kongo_Battle WHERE ship='Admiral';

no rows selected

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE SHIPS

2 SET name='new Admiral'

3 WHERE name='Admiral';

1 row updated.

SQL> SELECT * FROM Kongo_Battle WHERE ship='new Admiral';

no rows selected

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM ships WHERE name='new Admiral';

1 row deleted.

SQL> SELECT * FROM Kongo_Battle WHERE ship='new Admiral';

no rows selected

SQL>

SQL> REM ------------------------------------------

> REM OPERATIONS ON BASE TABLE 2:

SQL> REM OUTCOMES('SHIP','BATTLE','RESULT');

SQL>

SQL> REM INSERT:

SQL>

SQL> INSERT INTO OUTCOMES VALUES('Kirishima','North Cape','ok');

1 row created.

SQL> SELECT * FROM Kongo_Battle WHERE ship='Kirishima' and battle='North Cape';

SHIP BATTLE RESULT CLASS

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

Kirishima North Cape ok Kongo

SQL>

SQL> REM UPDATE:

SQL>

SQL> UPDATE OUTCOMES

2 SET ship='Kongo'

3 WHERE ship='Kirishima' and battle='North Cape';

1 row updated.

SQL> SELECT * FROM Kongo_Battle WHERE ship='Kongo' and battle='North Cape';

SHIP BATTLE RESULT CLASS

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

Kongo North Cape ok Kongo

SQL>

SQL> REM DELETE:

SQL>

SQL> DELETE FROM OUTCOMES WHERE ship='Kongo' and battle='North Cape';

1 row deleted.

SQL> SELECT * FROM Kongo_Battle WHERE ship='Kongo'and battle='North Cape';

no rows selected

SQL> ROLLBACK TO A6;

Rollback complete.

SQL> spool off;