MIDTERM EXAMINATION

-- JOHN MICHAEL GONZALES
-- BSIT          CUBAO2
-- MIDTERM EXAMINATION


-- Create the infoman database
CREATE DATABASE ONEPIECE;
USE ONEPIECE;

-- Create the Characters table
CREATE TABLE Characters (
  character_id INT PRIMARY KEY,
  name VARCHAR(255),
  age INT,
  occupation VARCHAR(255),
  affiliation VARCHAR(255)
);

-- Create the Hobbies table
CREATE TABLE Hobbies (
  hobby_id INT PRIMARY KEY,
  character_id INT,
  hobby VARCHAR(255),
  FOREIGN KEY (character_id) REFERENCES Characters (character_id)
);

-- Create the Antagonists table
CREATE TABLE Antagonists (
  antagonist_id INT PRIMARY KEY,
  name VARCHAR(255),
  rank_strength INT,
  skills VARCHAR(255)
);

-- Inserting data into the Characters table
INSERT INTO Characters (character_id, name, age, occupation, affiliation)
VALUES
  (1, 'Monkey D. Luffy', 19, 'Pirate', 'Straw Hat Pirates'),
  (2, 'Roronoa Zoro', 21, 'Swordsman', 'Straw Hat Pirates'),
  (3, 'Nami', 20, 'Navigator', 'Straw Hat Pirates'),
  (4, 'Usopp', 19, 'Sniper', 'Straw Hat Pirates'),
  (5, 'Sanji', 21, 'Chef', 'Straw Hat Pirates'),
  (6, 'Tony Tony Chopper', 17, 'Doctor', 'Straw Hat Pirates'),
  (7, 'Nico Robin', 30, 'Archaeologist', 'Straw Hat Pirates'),
  (8, 'Franky', 36, 'Shipwright', 'Straw Hat Pirates'),
  (9, 'Brook', 90, 'Musician', 'Straw Hat Pirates');

-- Inserting data into the Hobbies table
INSERT INTO Hobbies (hobby_id, character_id, hobby)
VALUES
  (1, 1, 'Eating meat and exploring'),
  (2, 2, 'Swordsmanship and training'),
  (3, 3, 'Navigating and studying maps'),
  (4, 4, 'Inventing and storytelling'),
  (5, 5, 'Cooking and flirting'),
  (6, 6, 'Healing and exploring medical advancements'),
  (7, 7, 'Reading and deciphering poneglyphs'),
  (8, 8, 'Building ships and playing cola-powered instruments'),
  (9, 9, 'Playing music and reminiscing about the past');

-- Inserting data into the Antagonists table
INSERT INTO Antagonists (antagonist_id, name, rank_strength, skills)
VALUES
  (1, 'Monkey D. Dragon', 100, 'Revolutionary leader'),
  (2, 'Blackbeard', 90, 'Yami Yami no Mi and Gura Gura no Mi user'),
  (3, 'Kaido', 95, 'Dragon Zoan form and immense physical strength'),
  (4, 'Big Mom', 92, 'Soru Soru no Mi user and powerful Haki'),
  (5, 'Akainu', 97, 'Magma Magma no Mi user and strong Busoshoku Haki');


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

-- Perform the join query
SELECT C.name, C.age, H.hobby, A.name AS antagonist_name
FROM Characters AS C
INNER JOIN Hobbies AS H ON C.character_id = H.character_id
INNER JOIN Antagonists AS A ON C.character_id = A.antagonist_id
WHERE A.rank_strength >= 95;