Character inception test, Oracle

Language: Layout:

DROP TABLE Character_data\\
DROP TABLE Character_Types\\
DROP TABLE guess_who\\

CREATE TABLE Character_data 
(
  Introduced NUMBER NOT NULL 
, Name VARCHAR2(50) NOT NULL 
, Species VARCHAR2(50) 
, Age NUMBER 
, Role VARCHAR2(50) 
)\\

INSERT ALL
   INTO Character_data (Introduced, Name, Species, Age, Role) VALUES (1, 'Sam', 'human', 33, 'hacker')
   INTO Character_data (Introduced, Name, Species, Age, Role) VALUES (2, 'Axel', 'human', 23, 'mail carrier')
   INTO Character_data (Introduced, Name, Species, Age, Role) VALUES (3, 'Pumpkin', 'mimic', 0, 'nice zombie')
   INTO Character_data (Introduced, Name, Species, Age, Role) VALUES (4, 'Kuro', 'human', 25, 'operations manager')
   INTO Character_data (Introduced, Name, Species, Age, Role) VALUES (5, 'Lavender', 'human', 26, 'operations support')
   INTO Character_data (Introduced, Name, Species, Age, Role) VALUES (6, 'Disco Inferno', 'sentinel', 245, 'fighting champion')
SELECT 1 FROM DUAL\\

SELECT * FROM Character_data\\

CREATE TABLE Character_Types 
(
  Name VARCHAR2(20) NOT NULL 
, JCF VARCHAR2(20) NOT NULL 
, Etype VARCHAR2(20) 
, Variant VARCHAR2(20)  
)\\

INSERT ALL
   INTO Character_Types (Name, JCF, Etype, Variant) VALUES ('Sam', 'INTP', '529', 'sx/sp')
   INTO Character_Types (Name, JCF, Etype, Variant) VALUES ('Axel', 'ESFJ', '268', 'so/sx')
   INTO Character_Types (Name, JCF, Etype, Variant) VALUES ('Pumpkin', 'ESFP', '729', 'sx/sp')
   INTO Character_Types (Name, JCF, Etype, Variant) VALUES ('Kuro', 'ENTJ', '378', 'sx/so')
   INTO Character_Types (Name, JCF, Etype, Variant) VALUES ('Lavender', 'INFJ', '945', 'sx/sp')
   INTO Character_Types (Name, JCF, Etype, Variant) VALUES ('Disco Inferno', 'ISTP', '827', 'sp/sx')
SELECT 1 FROM DUAL\\

SELECT * FROM Character_Types\\

CREATE TABLE guess_who 
(
  age NUMBER NOT NULL 
, JCF VARCHAR2(20) NOT NULL 
, most_admires VARCHAR2(20) 
, death_count NUMBER 
, hates VARCHAR2(368) 
)\\

INSERT ALL
   INTO guess_who (age, JCF, most_admires, death_count, hates) VALUES (33, 'INTP', 'Axel', 1, 'himself, also dry heat')
   INTO guess_who (age, JCF, most_admires, death_count, hates) VALUES (23, 'ESFJ', 'Red', 1, 'Kuro')
   INTO guess_who (age, JCF, most_admires, death_count, hates) VALUES (0, 'ESFP', 'Disco', 1, 'running out of microwave videos')
   INTO guess_who (age, JCF, most_admires, death_count, hates) VALUES (25, 'ENTJ', 'Iro', 2, 'willfully weak people')
   INTO guess_who (age, JCF, most_admires, death_count, hates) VALUES (26, 'INFJ', 'Red', 0, 'Kuro')
   INTO guess_who (age, JCF, most_admires, death_count, hates) VALUES (245, 'ISTP', 'Pumpkin', 1, 'unarmored earth vehicles')
SELECT 1 FROM DUAL\\

SELECT * FROM guess_who\\

with deaths as (
    select a.name as person
    , b.death_count as dies
    , a.age
    from Character_data a
    join guess_who b on a.age = b.age
    group by a.name
    , b.death_count
    ,a.age
    )

, locations as (select a.person
,b.age
,case
    when a.dies = 0 then 'our universe'
    when a.dies = 1 then 'Pumpkin verse'
    else 'dead'
    end as location
from deaths a
join Character_data b on a.age = b.age
               )

select person
, location 
from locations
group by person
, location


absolute service time: 0,92 sec