
CREATE TABLE albums (
	id INTEGER, 
	title VARCHAR(160) NOT NULL, 
	artist_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(artist_id) REFERENCES artists (id)
)


CREATE TABLE artists (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)


CREATE TABLE customers (
	id INTEGER, 
	first_name VARCHAR(40) NOT NULL, 
	last_name VARCHAR(20) NOT NULL, 
	company VARCHAR(80), 
	address VARCHAR(70), 
	city VARCHAR(40), 
	state VARCHAR(40), 
	country VARCHAR(40), 
	postal_code VARCHAR(10), 
	phone VARCHAR(24), 
	fax VARCHAR(24), 
	email VARCHAR(60) NOT NULL, 
	support_rep_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(support_rep_id) REFERENCES employees (id)
)


CREATE TABLE employees (
	id INTEGER, 
	last_name VARCHAR(20) NOT NULL, 
	first_name VARCHAR(20) NOT NULL, 
	title VARCHAR(30), 
	reports_to INTEGER, 
	birth_date TIMESTAMP, 
	hire_date TIMESTAMP, 
	address VARCHAR(70), 
	city VARCHAR(40), 
	state VARCHAR(40), 
	country VARCHAR(40), 
	postal_code VARCHAR(10), 
	phone VARCHAR(24), 
	fax VARCHAR(24), 
	email VARCHAR(60), 
	PRIMARY KEY (id), 
	FOREIGN KEY(reports_to) REFERENCES employees (id)
)


CREATE TABLE genres (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)


CREATE TABLE invoice_lines (
	id INTEGER, 
	invoice_id INTEGER NOT NULL, 
	track_id INTEGER NOT NULL, 
	unit_price NUMERIC(10, 2) NOT NULL, 
	quantity INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(invoice_id) REFERENCES invoices (id), 
	FOREIGN KEY(track_id) REFERENCES tracks (id)
)


CREATE TABLE invoices (
	id INTEGER, 
	customer_id INTEGER NOT NULL, 
	invoice_date TIMESTAMP NOT NULL, 
	billing_address VARCHAR(70), 
	billing_city VARCHAR(40), 
	billing_state VARCHAR(40), 
	billing_country VARCHAR(40), 
	billing_postal_code VARCHAR(10), 
	total NUMERIC(10, 2) NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(customer_id) REFERENCES customers (id)
)


CREATE TABLE media_types (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)


CREATE TABLE playlist_tracks (
	playlist_id INTEGER NOT NULL, 
	track_id INTEGER NOT NULL, 
	CONSTRAINT "PK_PlaylistTrack" PRIMARY KEY (playlist_id, track_id), 
	FOREIGN KEY(playlist_id) REFERENCES playlists (id), 
	FOREIGN KEY(track_id) REFERENCES tracks (id)
)


CREATE TABLE playlists (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)


CREATE TABLE tracks (
	id INTEGER, 
	name VARCHAR(200) NOT NULL, 
	album_id INTEGER, 
	media_type_id INTEGER NOT NULL, 
	genre_id INTEGER, 
	composer VARCHAR(220), 
	milliseconds INTEGER NOT NULL, 
	bytes INTEGER, 
	unit_price NUMERIC(10, 2) NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(album_id) REFERENCES albums (id), 
	FOREIGN KEY(genre_id) REFERENCES genres (id), 
	FOREIGN KEY(media_type_id) REFERENCES media_types (id)
)

CREATE TABLE albums (
	id INTEGER, 
	title VARCHAR(160) NOT NULL, 
	artist_id INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(artist_id) REFERENCES artists (id)
)

/*
2 rows from albums table:
id	title	artist_id
1	For Those About To Rock We Salute You	1
2	Balls to the Wall	2
*/


CREATE TABLE artists (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)

/*
2 rows from artists table:
id	name
1	AC/DC
2	Accept
*/


CREATE TABLE customers (
	id INTEGER, 
	first_name VARCHAR(40) NOT NULL, 
	last_name VARCHAR(20) NOT NULL, 
	company VARCHAR(80), 
	address VARCHAR(70), 
	city VARCHAR(40), 
	state VARCHAR(40), 
	country VARCHAR(40), 
	postal_code VARCHAR(10), 
	phone VARCHAR(24), 
	fax VARCHAR(24), 
	email VARCHAR(60) NOT NULL, 
	support_rep_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(support_rep_id) REFERENCES employees (id)
)

/*
2 rows from customers table:
id	first_name	last_name	company	address	city	state	country	postal_code	phone	fax	email	support_rep_id
1	Luís	Gonçalves	Embraer - Empresa Brasileira de Aeronáutica S.A.	Av. Brigadeiro Faria Lima, 2170	São José dos Campos	SP	Brazil	12227-000	+55 (12) 3923-5555	+55 (12) 3923-5566	luisg@embraer.com.br	3
2	Leonie	Köhler	None	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	+49 0711 2842222	None	leonekohler@surfeu.de	5
*/


CREATE TABLE employees (
	id INTEGER, 
	last_name VARCHAR(20) NOT NULL, 
	first_name VARCHAR(20) NOT NULL, 
	title VARCHAR(30), 
	reports_to INTEGER, 
	birth_date TIMESTAMP, 
	hire_date TIMESTAMP, 
	address VARCHAR(70), 
	city VARCHAR(40), 
	state VARCHAR(40), 
	country VARCHAR(40), 
	postal_code VARCHAR(10), 
	phone VARCHAR(24), 
	fax VARCHAR(24), 
	email VARCHAR(60), 
	PRIMARY KEY (id), 
	FOREIGN KEY(reports_to) REFERENCES employees (id)
)

/*
2 rows from employees table:
id	last_name	first_name	title	reports_to	birth_date	hire_date	address	city	state	country	postal_code	phone	fax	email
1	Adams	Andrew	General Manager	None	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
*/


CREATE TABLE genres (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)

/*
2 rows from genres table:
id	name
1	Rock
2	Jazz
*/


CREATE TABLE invoice_lines (
	id INTEGER, 
	invoice_id INTEGER NOT NULL, 
	track_id INTEGER NOT NULL, 
	unit_price NUMERIC(10, 2) NOT NULL, 
	quantity INTEGER NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(invoice_id) REFERENCES invoices (id), 
	FOREIGN KEY(track_id) REFERENCES tracks (id)
)

/*
2 rows from invoice_lines table:
id	invoice_id	track_id	unit_price	quantity
1	1	2	0.99	1
2	1	4	0.99	1
*/


CREATE TABLE invoices (
	id INTEGER, 
	customer_id INTEGER NOT NULL, 
	invoice_date TIMESTAMP NOT NULL, 
	billing_address VARCHAR(70), 
	billing_city VARCHAR(40), 
	billing_state VARCHAR(40), 
	billing_country VARCHAR(40), 
	billing_postal_code VARCHAR(10), 
	total NUMERIC(10, 2) NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(customer_id) REFERENCES customers (id)
)

/*
2 rows from invoices table:
id	customer_id	invoice_date	billing_address	billing_city	billing_state	billing_country	billing_postal_code	total
1	2	2007-01-01 00:00:00	Theodor-Heuss-Straße 34	Stuttgart	None	Germany	70174	1.98
2	4	2007-01-02 00:00:00	Ullevålsveien 14	Oslo	None	Norway	0171	3.96
*/


CREATE TABLE media_types (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)

/*
2 rows from media_types table:
id	name
1	MPEG audio file
2	Protected AAC audio file
*/


CREATE TABLE playlist_tracks (
	playlist_id INTEGER NOT NULL, 
	track_id INTEGER NOT NULL, 
	CONSTRAINT "PK_PlaylistTrack" PRIMARY KEY (playlist_id, track_id), 
	FOREIGN KEY(playlist_id) REFERENCES playlists (id), 
	FOREIGN KEY(track_id) REFERENCES tracks (id)
)

/*
2 rows from playlist_tracks table:
playlist_id	track_id
1	3402
1	3389
*/


CREATE TABLE playlists (
	id INTEGER, 
	name VARCHAR(120), 
	PRIMARY KEY (id)
)

/*
2 rows from playlists table:
id	name
1	Music
2	Movies
*/


CREATE TABLE tracks (
	id INTEGER, 
	name VARCHAR(200) NOT NULL, 
	album_id INTEGER, 
	media_type_id INTEGER NOT NULL, 
	genre_id INTEGER, 
	composer VARCHAR(220), 
	milliseconds INTEGER NOT NULL, 
	bytes INTEGER, 
	unit_price NUMERIC(10, 2) NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(album_id) REFERENCES albums (id), 
	FOREIGN KEY(genre_id) REFERENCES genres (id), 
	FOREIGN KEY(media_type_id) REFERENCES media_types (id)
)

/*
2 rows from tracks table:
id	name	album_id	media_type_id	genre_id	composer	milliseconds	bytes	unit_price
1	For Those About To Rock (We Salute You)	1	1	1	Angus Young, Malcolm Young, Brian Johnson	343719	11170334	0.99
2	Balls to the Wall	2	2	1	None	342562	5510424	0.99
*/
List the number of invoices and the invoice total from California.
SELECT billing_state ,  COUNT(*) ,  SUM(total) FROM invoices WHERE billing_state  =  "CA";
[{'billing_state': 'CA', 'COUNT(*)': 21, 'SUM(total)': 115.85999999999997}]