I created this 2 tables: CREATE TABLE Battles ( battleName VARCHAR2(20), battleY

Question

I created this 2 tables:

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)
);

and this added this values:

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 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');

I want to Delete from the battle table where outcome = 'ok' and ' damaged'

so when i use Select * from battle;

I get this result:

BATTLENAME             BATTLEYR
--------------------               ----------
Guadalcanal           1942

I want to use the delete statment.

Explanation / Answer

-- need to disable FK constraint first

ALTER TABLE outcomes DISABLE CONSTRAINT fkBattles ;

DELETE FROM battles WHERE battlename IN (SELECT battle FROM outcomes WHERE outcome = 'ok' OR outcome = 'damaged');

ALTER TABLE outcomes ENABLE CONSTRAINT fkBattles ;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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