SQL Schema for ADP interview questions

example.sql

CREATE TABLE STATE(
  state_id INT NOT NULL,
  party VARCHAR(30) NOT NULL,
  age INT NOT NULL,
  gender VARCHAR(15) NOT NULL,
  PRIMARY KEY(state_id));

INSERT INTO STATE(state_id, PARTY, AGE, GENDER)
VALUES (123, 'D', 20, 'F');
INSERT INTO STATE(state_id, PARTY, AGE, GENDER)
VALUES (345, 'R', 80, 'M');              
INSERT INTO STATE(state_id, PARTY, AGE, GENDER)
VALUES (678, 'I', 50, 'M');  
INSERT INTO STATE(state_id, PARTY, AGE, GENDER)
VALUES (321, 'D', 20, 'F');
INSERT INTO STATE(state_id, PARTY, AGE, GENDER)
VALUES (543, 'R', 80, 'M');              
INSERT INTO STATE(state_id, PARTY, AGE, GENDER)
VALUES (876, 'I', 50, 'M');  
INSERT INTO STATE(state_id, PARTY, AGE, GENDER)
VALUES (910, 'I', 55, 'M');  

CREATE TABLE MARICOPA(
  maricopa_id INT NOT NULL,
  party VARCHAR(30) NOT NULL,
  age INT NOT NULL,
  gender VARCHAR(15) NOT NULL,
  PRIMARY KEY(maricopa_id));
  
INSERT INTO MARICOPA(Maricopa_id, PARTY, AGE, GENDER)
VALUES (123, 'D', 20, 'F');
INSERT INTO MARICOPA(Maricopa_id, PARTY, AGE, GENDER)
VALUES (345, 'R', 80, 'M');              
INSERT INTO MARICOPA(Maricopa_id, PARTY, AGE, GENDER)
VALUES (678, 'I', 50, 'M');  

CREATE TABLE Pima(
  Pima_id INT NOT NULL,
  party VARCHAR(30) NOT NULL,
  age INT NOT NULL,
  gender VARCHAR(15) NOT NULL,
  PRIMARY KEY(pima_id));
  
  
INSERT INTO PIMA(Pima_id, PARTY, AGE, GENDER)
VALUES (321, 'D', 20, 'F');
INSERT INTO PIMA(Pima_id, PARTY, AGE, GENDER)
VALUES (543, 'R', 80, 'M');              
INSERT INTO PIMA(Pima_id, PARTY, AGE, GENDER)
VALUES (876, 'I', 50, 'M');  


CREATE TABLE county_cumulative AS
SELECT maricopa_id, party as maricopa_party, age as maricopa_age, gender as maricopa_gender FROM maricopa
UNION ALL
SELECT pima_id, party, age, gender FROM pima
;