dataoriadbscript.sql

CREATE TABLE Locations (  
    LocationID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Name VARCHAR(255)  
);

CREATE TABLE Riddles (  
    RiddleID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Riddle varchar(16000),  
    Answer VARCHAR(255)  
);

CREATE TABLE Maze (  
    MazeID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Way VARCHAR(255),  
    LocationID INT,  
    Status VARCHAR(20) 
);

ALTER TABLE Maze 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE MirrorSymbols (  
    SymbolID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Symbol VARCHAR(50),  
    Correct VARCHAR(50),  
    LocationID INT  
);

ALTER TABLE MirrorSymbols 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE Scrolls (  
    ScrollID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Status varchar(16000),  
    Keyword VARCHAR(50),  
    LocationID INT  
);

ALTER TABLE Scrolls 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE BridgeParts (  
    BridgePartID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,   
    Status VARCHAR(20),  
    LocationID INT  
);

ALTER TABLE BridgeParts 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE PuzzlePieces (  
    PuzzlePieceID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Description varchar(16000),  
    Status VARCHAR(20),  
    LocationID INT  
);

ALTER TABLE PuzzlePieces 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE Books (  
    BookID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Title VARCHAR(255), 
    Position VARCHAR(50),  
    LocationID INT  
);

ALTER TABLE Books 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE SecretDoors (  
    DoorID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    KeyType varchar(16000),  
    Status VARCHAR(20),  
    LocationID INT  
);

ALTER TABLE SecretDoors 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE Statues (  
    StatueID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Description varchar(16000),  
    LocationID INT  
);

ALTER TABLE Statues 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE Crystals (  
    CrystalID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Color VARCHAR(50), 
    Sequence VARCHAR(50),  
    LocationID INT  
);

ALTER TABLE Crystals 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE CombinationLocks (  
    LockID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, 
    Combination VARCHAR(20),  
    Status VARCHAR(20),  
    LocationID INT  
);

ALTER TABLE CombinationLocks 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE TorchPositions (  
    TorchID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Position VARCHAR(20),  
    Status VARCHAR(20),  
    LocationID INT  
);

ALTER TABLE TorchPositions 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE Constellations (  
    ConstellationID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    Name VARCHAR(255),  
    Shape VARCHAR(255),  
    LocationID INT  
);

ALTER TABLE Constellations 
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

CREATE TABLE Altar (  
    AltarID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,  
    LocationID INT,  
    Arrangement VARCHAR(255), 
    Sequence VARCHAR(20) 
);

ALTER TABLE Altar  
ADD FOREIGN KEY (LocationID) REFERENCES Locations(LocationID);

Insert into Locations (name) values ('Chamber of Scripts');

Insert into Locations (name) values ('Hidden Room');

Insert into Locations (name) values ('Dark Maze');

Insert into Locations (name) values ('Broken Bridge');

Insert into Locations (name) values ('Grand Hall');

Insert into Locations (name) values ('Library of Knowledge');

Insert into Locations (name) values ('Ancient Mirror Chamber');

Insert into Locations (name) values ('Secret Room of Scrolls');

Insert into Locations (name) values ('The Abandoned Abyss');

Insert into Locations (name) values ('Throne Room');

Insert into Locations (name) values ('Crystel Cavern');

Insert into Locations (name) values ('The Oasis');

Insert into Locations (name) values ('Whispering Woods');

Insert into Locations (name) values ('Starlight passage');

Insert into Locations (name) values ('Ethereal Bridge');

Insert into Locations (name) values ('Hidden Treasure Vault');

Select * from Locations;

Insert into SecretDoors (LocationID,KeyType,Status) values (1,'Silver Key','LOCKED');

Insert into SecretDoors (LocationID,KeyType,Status) values (6,'Gold Key','LOCKED');

Insert into SecretDoors (LocationID,KeyType,Status) values (10,'Lever','LOCKED');

Select * from SecretDoors;

Insert into CombinationLocks (Status,Combination,LocationID) values ('LOCKED','1997',2);

Insert into CombinationLocks (Status,Combination,LocationID) values ('LOCKED','1234',12);

Insert into CombinationLocks (Status,Combination,LocationID) values ('LOCKED','1414',16);

Select * from CombinationLocks;

Insert into Maze (Status,Way,LocationID) values ('BLOCKED','Left',3);

Insert into Maze (Status,Way,LocationID) values ('UNBLOCKED','Right',3);

Select * from Maze;

Insert into Riddles (Riddle,Answer) values ('I speak without a mouth and hear without ears. I have no body, but I come alive with the wind. What am I?' ,'XXXX');

Insert into Riddles (Riddle,Answer) values ('What has a beginning, a middle, and an end, but is also infinite?' ,'XXXX');

Select * from Riddles;

INSERT INTO BridgeParts (Status, LocationID) VALUES ('Broken', 4);

Select * from BridgeParts;

INSERT INTO Books (Title, Position, LocationID) VALUES ('Inferno','1', 6);

INSERT INTO Books (Title, Position, LocationID) VALUES ('Origin','2', 6);

INSERT INTO Books (Title, Position, LocationID) VALUES ('The Da Vinci Code','3', 6);

INSERT INTO Books (Title, Position, LocationID) VALUES ('The Lost Symbol','4', 6);

INSERT INTO Books (Title, Position, LocationID) VALUES ('Angels & Demons','5', 6);

Select * from Books;

INSERT INTO MirrorSymbols (Symbol, Correct, LocationID) VALUES ('✻','True', 7);

INSERT INTO MirrorSymbols (Symbol, Correct, LocationID) VALUES ('❆','False', 7);

INSERT INTO MirrorSymbols (Symbol, Correct, LocationID) VALUES ('✷','False', 7);

INSERT INTO MirrorSymbols (Symbol, Correct, LocationID) VALUES ('❂','False', 7);

INSERT INTO MirrorSymbols (Symbol, Correct, LocationID) VALUES ('⁂','False', 7);

INSERT INTO MirrorSymbols (Symbol, Correct, LocationID) VALUES ('௹','False', 7);

Select * from MirrorSymbols;

INSERT INTO Scrolls (Keyword, Status, LocationID) VALUES ('SECRET','Encrypted', 8);

Select * from Scrolls;

INSERT INTO Crystals (Sequence, Color, LocationID) VALUES ('Incorrect','Blue', 11);

INSERT INTO Crystals (Sequence, Color, LocationID) VALUES ('Incorrect','Red', 11);

INSERT INTO Crystals (Sequence, Color, LocationID) VALUES ('Incorrect','Green', 11);

INSERT INTO Crystals (Sequence, Color, LocationID) VALUES ('Incorrect','Yellow', 11);

INSERT INTO Crystals (Sequence, Color, LocationID) VALUES ('Incorrect','Opal', 11);

Select * from Crystals;

INSERT INTO TorchPositions (Position,Status, LocationID) VALUES ('Correct','Unlit', 11);

Select * from TorchPositions;

INSERT INTO Statues (Description, LocationID) VALUES ('Whispering Statue', 13);

Select * from Statues;

INSERT INTO Altar (Sequence, Arrangement, LocationID) VALUES ('Water Earth Fire Air', 'Incorrect', 13);

Select * from Altar;

INSERT INTO Constellations (Name, Shape, LocationID) VALUES ('Leo', 'Connected Stars', 14);

INSERT INTO Constellations (Name, Shape, LocationID) VALUES ('Aries', 'Connected Stars', 14);

INSERT INTO Constellations (Name, Shape, LocationID) VALUES ('Orion', 'Connected Stars', 14);

INSERT INTO Constellations (Name, Shape, LocationID) VALUES ('Tauras', 'Connected Stars', 14);

INSERT INTO Constellations (Name, Shape, LocationID) VALUES ('Cancer', 'Connected Stars', 14);

Select * from Constellations;