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;
