-- -------------------------------------------------------------
-- Girlsprout Cookies
--
-- This script is useful for chapters 6 and 8 of Head First SQL.
-- Consume this script thus:
--
-- mysql> source C:\Users\russ\dev\cookies.sql
-- -------------------------------------------------------------
-- DROP   DATABASE headfirst_sql;
-- CREATE DATABASE headfirst_sql;
-- USE             headfirst_sql;

DROP   TABLE cookiesales;
CREATE TABLE cookiesales ( id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, firstname VARCHAR(16), sales FLOAT(10,2), salesdate DATE );

INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  1, 'Lindsay', 32.02, '2007-03-06' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  2, 'Paris',   26.53, '2007-03-06' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  3, 'Britney', 11.25, '2007-03-06' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  4, 'Nicole',  18.96, '2007-03-06' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  5, 'Lindsay',  9.16, '2007-03-07' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  6, 'Paris',    1.52, '2007-03-07' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  7, 'Britney', 43.21, '2007-03-07' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  8, 'Nicole',   8.05, '2007-03-07' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES (  9, 'Lindsay', 17.62, '2007-03-08' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 10, 'Paris',   24.19, '2007-03-08' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 11, 'Britney',  3.40, '2007-03-08' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 12, 'Nicole',  15.21, '2007-03-08' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 13, 'Lindsay',  0.00, '2007-03-09' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 14, 'Paris',   31.99, '2007-03-09' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 15, 'Britney',  2.58, '2007-03-09' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 16, 'Nicole',   0.00, '2007-03-09' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 17, 'Lindsay',  2.34, '2007-03-10' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 18, 'Paris',   13.44, '2007-03-10' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 19, 'Britney',  8.78, '2007-03-10' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 20, 'Nicole',  26.82, '2007-03-10' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 21, 'Lindsay',  3.71, '2007-03-11' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 22, 'Paris',    0.56, '2007-03-11' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 23, 'Britney', 34.19, '2007-03-11' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 24, 'Nicole',   7.77, '2007-03-11' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 25, 'Lindsay', 16.23, '2007-03-12' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 26, 'Paris',    0.00, '2007-03-12' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 27, 'Britney',  4.50, '2007-03-12' );
INSERT INTO cookiesales ( id, firstname, sales, salesdate ) VALUES ( 28, 'Nicole',  19.22, '2007-03-12' );


-- -------------------------------------------------------------
-- Queries...
-- -------------------------------------------------------------
SELECT * FROM cookiesales;

-- List sales, but grouped by girl:
SELECT firstname, sales FROM cookiesales ORDER BY firstname;

-- List girls' total sales from highest to lowest:
SElECT firstname, SUM(sales) FROM cookiesales GROUP BY firstname ORDER BY SUM(sales)DESC;

-- List girls' average sales from highest to lowest:
SElECT firstname, AVG(sales) FROM cookiesales GROUP BY firstname ORDER BY AVG(sales)DESC;

-- List girls' average sales from highest to lowest:
SElECT COUNT(sales) FROM cookiesales;