DROP TABLE Outcomes CASCADE CONSTRAINTS; DROP TABLE Battles CASCADE CONSTRAINTS;

Question

DROP TABLE Outcomes CASCADE CONSTRAINTS;
DROP TABLE Battles CASCADE CONSTRAINTS;
DROP TABLE Ships CASCADE CONSTRAINTS;
DROP TABLE Classes CASCADE CONSTRAINTS;

CREATE TABLE Classes (
className VARCHAR2(20),
typeClass CHAR(2),
country VARCHAR2(15),
numGuns INT,
bore INT,
displacement INT,
CONSTRAINT pkClasses PRIMARY KEY (className),
CHECK (typeClass IN ('bb', 'bc'))
);

CREATE TABLE Ships
(
shipName VARCHAR2(20),
shipClass VARCHAR2(20),
launchYr INT,
CONSTRAINT pkShips PRIMARY KEY (shipName),
CONSTRAINT fkClasses FOREIGN KEY (shipClass) REFERENCES Classes (className)
);

CREATE TABLE Battles
(
battleName VARCHAR2(20),
battleYr INT,
CONSTRAINT pkBattles PRIMARY KEY (battleName)
);

CREATE TABLE Outcomes
(
ship VARCHAR2(20),
battle VARCHAR2(20),
outcome VARCHAR2(10),
CONSTRAINT pkOutcomes PRIMARY KEY (ship, battle),
CHECK (outcome IN ('sunk', 'ok', 'damaged')),
CONSTRAINT fkShips FOREIGN KEY (ship) REFERENCES Ships (shipName),
CONSTRAINT fkBattles FOREIGN KEY (battle) REFERENCES Battles (battleName)
);

INSERT INTO Classes VALUES ('Bismarck', 'bb', 'Germany', 8, 15, 42000);
INSERT INTO Classes VALUES ('Iowa', 'bb', 'USA', 9, 16, 46000);
INSERT INTO Classes VALUES ('Kongo', 'bc', 'Japan', 8, 14, 32000);
INSERT INTO Classes VALUES ('North Carolina', 'bb', 'USA', 9, 16, 37000);
INSERT INTO Classes VALUES ('Renown', 'bc', 'Gt. Britain', 6, 15, 32000);
INSERT INTO Classes VALUES ('Revenge', 'bb', 'Gt. Britain', 8, 15, 29000);
INSERT INTO Classes VALUES ('Tennessee', 'bb', 'USA', 12, 14, 32000);
INSERT INTO Classes VALUES ('Yamato', 'bb', 'Japan', 9, 18, 65000);

INSERT INTO Battles VALUES ('Denmark Strait', 1941);
INSERT INTO Battles VALUES ('Guadalcanal', 1942);
INSERT INTO Battles VALUES ('North Cape', 1943);
INSERT INTO Battles VALUES ('Surigao Strait', 1944);

INSERT INTO Ships VALUES ('California', 'Tennessee', 1921);
INSERT INTO Ships VALUES ('Haruna', 'Kongo', 1915);
INSERT INTO Ships VALUES ('Hiei', 'Kongo', 1914);
INSERT INTO Ships VALUES ('Iowa', 'Iowa', 1943);
INSERT INTO Ships VALUES ('Kirishima', 'Kongo', 1915);
INSERT INTO Ships VALUES ('Kongo', 'Kongo', 1913);
INSERT INTO Ships VALUES ('Missouri', 'Iowa', 1944);
INSERT INTO Ships VALUES ('Musashi', 'Yamato', 1942);
INSERT INTO Ships VALUES ('New Jersey', 'Iowa', 1943);
INSERT INTO Ships VALUES ('North Carolina', 'North Carolina', 1941);
INSERT INTO Ships VALUES ('Ramillies', 'Revenge', 1917);
INSERT INTO Ships VALUES ('Renown', 'Renown', 1916);
INSERT INTO Ships VALUES ('Repulse', 'Renown', 1916);
INSERT INTO Ships VALUES ('Resolution', 'Revenge', 1916);
INSERT INTO Ships VALUES ('Revenge', 'Revenge', 1916);
INSERT INTO Ships VALUES ('Royal Oak', 'Revenge', 1916);
INSERT INTO Ships VALUES ('Royal Sovereign', 'Revenge', 1916);
INSERT INTO Ships VALUES ('Tennessee', 'Tennessee', 1920);
INSERT INTO Ships VALUES ('Washington', 'North Carolina', 1941);
INSERT INTO Ships VALUES ('Wisconsin', 'Iowa', 1944);
INSERT INTO Ships VALUES ('Yamato', 'Yamato', 1941);

INSERT INTO Outcomes VALUES ('California', 'Surigao Strait', 'ok');
INSERT INTO Outcomes VALUES ('Kirishima', 'Guadalcanal', 'sunk');
INSERT INTO Outcomes VALUES ('North Carolina', 'Guadalcanal', 'damaged');
INSERT INTO Outcomes VALUES ('Tennessee', 'Surigao Strait', 'ok');
INSERT INTO Outcomes VALUES ('Washington', 'Guadalcanal', 'ok');
INSERT INTO Outcomes VALUES ('North Carolina', 'Surigao Strait', 'ok');

commit;

**************************************

/*
1. Delete the class North Carolina. Note that before we delete the row from the Classes table, we need to delete the rows in the Ships table that refer to this class. Before deleting from the
Ships table, we need to delete the rows from Outcomes table that refer to these ships.
Again, issue a rollback so that the changes made by these delete statements are rolled back.
*/

-- Here, we will delete 3 rows from Outcomes, 2 rows from Ships, and 1 row from Classes.
delete from outcomes where

/* 2:
Write a stored procedure that takes a ship name as input and prints the battles that the ship was involved with.

Suppose you call the procedure as printBattlesForShip, then you can call the procedure as follows:
SET SERVEROUTPUT ON;
CALL printBattlesForShip('North Carolina');

You should get the following results for the the above call (based on our data) -- do not mind the last , -- you can try to get rid of it if you want to:
printbattlesforship 'NORTH CAROLINA') succeeded.
The battles for ship North Carolina are: Surigao Strait, Guadalcanal,
*/

Comment

Explanation / Answer

1. Delete the class North Carolina. Note that before we delete the row from the Classes table, we need to delete the rows in the Ships table that refer to this class.
Before deleting from the Ships table, we need to delete the rows from Outcomes table that refer to these ships.
Again, issue a rollback so that the changes made by these delete statements are rolled back.
*/
-- Here, we will delete 3 rows from Outcomes, 2 rows from Ships, and 1 row from Classes.

Solution:
delete from Outcomes where ship='North Carolina';
delete from Ships where shipClass='North Carolina';
delete from Classes where className ='North Carolina';
rollback;

2. Write a stored procedure that takes a ship name as input and prints the battles that the ship was involved with.
Suppose you call the procedure as printBattlesForShip, then you can call the procedure as follows:
SET SERVEROUTPUT ON;
CALL printBattlesForShip('North Carolina');

Solution:
DELIMITER //
CREATE PROCEDURE printBattlesForShip(IN shipName VARCHAR(255))
BEGIN
SELECT battleName
FROM Battles
WHERE battleName in (select battle from Outcomes where ship=shipName);
END //
DELIMITER ;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.