
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
*/
What are the number of votes from state 'NY' or 'CA'?
SELECT count(*) FROM votes WHERE state  =  'NY' OR state  =  'CA'
[{'count(*)': 3}]