
CREATE TABLE "AREA_CODE_STATE" (
	area_code INTEGER NOT NULL, 
	state VARCHAR(2) NOT NULL, 
	PRIMARY KEY (area_code)
)


CREATE TABLE "CONTESTANTS" (
	contestant_number INTEGER, 
	contestant_name VARCHAR(50) NOT NULL, 
	PRIMARY KEY (contestant_number)
)


CREATE TABLE "VOTES" (
	vote_id INTEGER NOT NULL, 
	phone_number INTEGER NOT NULL, 
	state VARCHAR(2) NOT NULL, 
	contestant_number INTEGER NOT NULL, 
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
	PRIMARY KEY (vote_id), 
	FOREIGN KEY(contestant_number) REFERENCES "CONTESTANTS" (contestant_number), 
	FOREIGN KEY(state) REFERENCES "AREA_CODE_STATE" (state)
)

CREATE TABLE "AREA_CODE_STATE" (
	area_code INTEGER NOT NULL, 
	state VARCHAR(2) NOT NULL, 
	PRIMARY KEY (area_code)
)

/*
2 rows from AREA_CODE_STATE table:
area_code	state
201	NJ
202	DC
*/


CREATE TABLE "CONTESTANTS" (
	contestant_number INTEGER, 
	contestant_name VARCHAR(50) NOT NULL, 
	PRIMARY KEY (contestant_number)
)

/*
2 rows from CONTESTANTS table:
contestant_number	contestant_name
1	Edwina Burnam
2	Tabatha Gehling
*/


CREATE TABLE "VOTES" (
	vote_id INTEGER NOT NULL, 
	phone_number INTEGER NOT NULL, 
	state VARCHAR(2) NOT NULL, 
	contestant_number INTEGER NOT NULL, 
	created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
	PRIMARY KEY (vote_id), 
	FOREIGN KEY(contestant_number) REFERENCES "CONTESTANTS" (contestant_number), 
	FOREIGN KEY(state) REFERENCES "AREA_CODE_STATE" (state)
)

/*
2 rows from VOTES table:
vote_id	phone_number	state	contestant_number	created
1	7182887233	NY	2	2018-03-09 19:03:21
2	7148407040	NY	3	2018-03-09 19:03:36
*/
List the area codes in which voters voted both for the contestant 'Tabatha Gehling' and the contestant 'Kelly Clauss'.
SELECT T3.area_code FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number JOIN area_code_state AS T3 ON T2.state  =  T3.state WHERE T1.contestant_name  =  'Tabatha Gehling' INTERSECT SELECT T3.area_code FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number JOIN area_code_state AS T3 ON T2.state  =  T3.state WHERE T1.contestant_name  =  'Kelly Clauss'
[{'area_code': 212}, {'area_code': 315}, {'area_code': 347}, {'area_code': 516}, {'area_code': 518}, {'area_code': 585}, {'area_code': 607}, {'area_code': 631}, {'area_code': 646}, {'area_code': 716}, {'area_code': 718}, {'area_code': 845}, {'area_code': 914}, {'area_code': 917}]