
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 contestant numbers and names, ordered by contestant name descending.
SELECT contestant_number ,  contestant_name FROM contestants ORDER BY contestant_name DESC
[{'contestant_number': 2, 'contestant_name': 'Tabatha Gehling'}, {'contestant_number': 8, 'contestant_name': 'Nita Coster'}, {'contestant_number': 11, 'contestant_name': 'Loraine NygrenTania Mattioli'}, {'contestant_number': 9, 'contestant_name': 'Kurt Walser'}, {'contestant_number': 3, 'contestant_name': 'Kelly Clauss'}, {'contestant_number': 6, 'contestant_name': 'Jessie Eichman'}, {'contestant_number': 4, 'contestant_name': 'Jessie Alloway'}, {'contestant_number': 10, 'contestant_name': 'Ericka Dieter'}, {'contestant_number': 1, 'contestant_name': 'Edwina Burnam'}, {'contestant_number': 7, 'contestant_name': 'Allie Rogalski'}, {'contestant_number': 5, 'contestant_name': 'Alana Bregman'}]