SQL QUERY - MARS_DB

TEST_GIST.TXT

CREATE DATABASE MARS_DB;

USE MARS_DB;

CREATE TABLE base (
    base_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    base_name VARCHAR(30),
    founded DATE
);
INSERT INTO base
    (base_id, base_name, founded)
VALUES
    (DEFAULT, 'Tharsisland', '2037-06-03'),
    (DEFAULT, 'Valles Marineris 2.0', '2040-12-01'),
    (DEFAULT, 'Gale Cratertown', '2041-08-15'),
    (DEFAULT, 'New New New York', '2042-02-10'),
    (DEFAULT, 'Olympus Mons Spa & Casino', NULL);


CREATE TABLE martian (
    martian_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    base_id INT,
    super_id INT,
FOREIGN KEY base_id REFERENCES base (base_id)
);

INSERT INTO martian
    (martian_id, first_name, last_name, base_id, super_id)
VALUES
    (DEFAULT, 'Ray', 'Bradbury', 1, NULL),
    (DEFAULT, 'John', 'Black', 4, 10),
    (DEFAULT, 'Samuel', 'Hinkston', 4, 2),
    (DEFAULT, 'Jeff', 'Spender', 1, 9),
    (DEFAULT, 'Sam', 'Parkhill', 2, 12),
    (DEFAULT, 'Elma', 'Parkhill', 3, 8),
    (DEFAULT, 'Melissa', 'Lewis', 1, 1),
    (DEFAULT, 'Mark', 'Watney', 3, NULL),
    (DEFAULT, 'Beth', 'Johanssen', 1, 1),
    (DEFAULT, 'Chris', 'Beck', 4, NULL),
    (DEFAULT, 'Nathaniel', 'York', 4, 2),
    (DEFAULT, 'Elon', 'Musk', 2, NULL),
    (DEFAULT, 'John', 'Carter', NULL, 8);


CREATE TABLE visitor (
    visitor_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    host_id INT,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
FOREIGN KEY host_id REFERENCES martian (martian_id)

);
INSERT INTO visitor
    (visitor_id, host_id, first_name, last_name)
VALUES
    (DEFAULT, 7, 'George', 'Ambrose'),
    (DEFAULT, 1, 'Kris', 'Cardenas'),
    (DEFAULT, 9, 'Priscilla', 'Lane'),
    (DEFAULT, 11, 'Jane', 'Thornton'),
    (DEFAULT, NULL, 'Doug', 'Stavenger'),
    (DEFAULT, NULL, 'Jamie', 'Waterman'),
    (DEFAULT, 8, 'Martin', 'Humphries');


CREATE TABLE supply (
    supply_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(30),
    description TEXT,
    quantity INT
);
INSERT INTO supply
    (supply_id, name, description, quantity)
VALUES
    (DEFAULT, 'Solar Panel', 'Standard 1x1 meter cell', 912),
    (DEFAULT, 'Water Filter', 'This takes things out of your water so it''s drinkable.', 6),
    (DEFAULT, 'Duct Tape', 'A 10 meter roll of duct tape for ALL your repairs.', 951),
    (DEFAULT, 'Ketchup', 'It''s ketchup...', 206),
    (DEFAULT, 'Battery Cell', 'Standard 1000 kAh battery cell for power grid (heavy item).', 17),
    (DEFAULT, 'USB 6.0 Cable', 'Carbon fiber coated / 15 TBps spool', 42),
    (DEFAULT, 'Fuzzy Duster', 'It gets dusty around here. Be prepared!', 19),
    (DEFAULT, 'Mars Bars', 'The ORIGINAL nutirent bar made with the finest bioengineered ingredients.', 3801),
    (DEFAULT, 'Air Filter', 'Removes 99% of all Martian dust from your ventilation unit.', 23),
    (DEFAULT, 'Famous Ray''s Frozen Pizza', 'This Martian favorite is covered in all your favorite toppings. 1 flavor only.', 823);

CREATE TABLE inventory (
    base_id INT,
    supply_id INT,
    quantity INT,
FOREIGN KEY base_id REFERENCES base (base_id),
FOREIGN KEY supply_id REFERENCES supply (supply_id)
);
INSERT INTO inventory
    (base_id, supply_id, quantity)
VALUES
    (1, 1, 8),
    (1, 3, 5),
    (1, 5, 1),
    (1, 6, 2),
    (1, 8, 12),
    (1, 9, 1),
    (2, 4, 5),
    (2, 8, 62),
    (2, 10, 37),
    (3, 2, 11),
    (3, 7, 2),
    (4, 10, 91);

CREATE TABLE martian_confidential (
    martian_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    base_id INT,
    super_id INT,
    salary INT,
    dna_id VARCHAR(30),
FOREIGN KEY base_id REFERENCES base (base_id)
);
INSERT INTO martian_confidential
    (martian_id, first_name, last_name, base_id, super_id, salary, dna_id)
VALUES
    (DEFAULT, 'Ray', 'Bradbury', 1, NULL, 155900, 'gctaggaatgtagaatctcctgttg'),
    (DEFAULT, 'John', 'Black', 4, 10, 120100, 'cagttaatggttgaagctggggatt'),
    (DEFAULT, 'Samuel', 'Hinkston', 4, 2, 110000, 'cgaagcgctagatgctgtgttgtag'),
    (DEFAULT, 'Jeff', 'Spender', 1, 9, 10000, 'gactaatgtcttcgtggattgcaga'),
    (DEFAULT, 'Sam', 'Parkhill', 2, 12, 125000, 'gttactttgcgaaagccgtggctac'),
    (DEFAULT, 'Elma', 'Parkhill', 3, 8, 137000, 'gcaggaatggaagcaactgccatat'),
    (DEFAULT, 'Melissa', 'Lewis', 1, 1, 145250, 'cttcgatcgtcaatggagttccggac'),
    (DEFAULT, 'Mark', 'Watney', 3, NULL, 121100, 'gacacgaggcgaactatgtcgcggc'),
    (DEFAULT, 'Beth', 'Johanssen', 1, 1, 130000, 'cttagactaggtgtgaaacccgtta'),
    (DEFAULT, 'Chris', 'Beck', 4, NULL, 125000, 'gggggggttacgacgaggaatccat'),
    (DEFAULT, 'Nathaniel', 'York', 4, 2, 105000, 'ggtctccctgggcgggatattggatg'),
    (DEFAULT, 'Elon', 'Musk', 2, NULL, 155800, 'atctgcttggatcaatagcgctgcg'),
    (DEFAULT, 'John', 'Carter', NULL, 8, 129500, 'ccaatcgtgcgagtcgcgatagtct');