TAknPi1p

1drop table if exists Sales;
2drop table if exists SalesRep;
3drop table if exists Product;
4drop table if exists Category;
5drop table if exists Manufacturer;
6create table Manufacturer (
7  id INT NOT NULL AUTO_INCREMENT
8, name VARCHAR(50)
9, PRIMARY KEY (id)
10) ENGINE=INNODB;
11create table Category (
12  id INT NOT NULL AUTO_INCREMENT
13, description VARCHAR(50)
14, PRIMARY KEY (id)
15) ENGINE=INNODB;
16create table Product (
17  id INT NOT NULL AUTO_INCREMENT
18, name VARCHAR(50)
19, nominal_cost DECIMAL(6,2)
20, nominal_price DECIMAL(6,2)
21, category_id INT NOT NULL
22, manufacturer_id INT NOT NULL
23, sku INT NOT NULL
24, PRIMARY KEY (id) 
25, CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES Category(id) 
26, CONSTRAINT fk_manufacturer FOREIGN KEY (manufacturer_id) REFERENCES Manufacturer(id) 
27) ENGINE=INNODB;
28create table SalesRep (
29  id INTEGER NOT NULL AUTO_INCREMENT
30, name VARCHAR(50)
31, PRIMARY KEY (id)
32) ENGINE=INNODB;
33create table Sales (
34  id INTEGER NOT NULL AUTO_INCREMENT
35, salesRep_id INTEGER 
36, saleDate DATE
37, units INT
38, unitAmount DECIMAL(6,2)
39, saleAmount DECIMAL(6,2)
40, PRIMARY KEY (id) 
41, CONSTRAINT fk_salesRep FOREIGN KEY (salesRep_id) REFERENCES SalesRep(id)
42) ENGINE=INNODB;
43insert into Manufacturer (name) values('Hasbro');
44insert into Manufacturer (name) values('Mattel');
45insert into Manufacturer (name) values('Fischer-Price');
46insert into Category (description) values('Boys 4-6');
47insert into Category (description) values('Boys 7-10');
48insert into Category (description) values('Girls 4-6');
49insert into Category (description) values('Girls 6-8');
50insert into Category (description) values('All Children 5-10');
51insert into Category (description) values('All Children 3-5');
52insert into SalesRep (name) values ('George');
53insert into SalesRep (name) values ('Julie');
54insert into SalesRep (name) values ('Johnny');
55insert into SalesRep (name) values ('Jimmy');
56SELECT * FROM SalesRep; 
57insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku) 
58	(SELECT 'Uno Card Game', 7.00, 9.00, Category.id, Manufacturer.id, 1
59	from Category JOIN Manufacturer where Category.description = 'All Children 5-10' and Manufacturer.name = 'Hasbro');
60insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku) 
61	(SELECT 'My Little Pony', 7.00, 11.00, Category.id, Manufacturer.id, 2
62	from Category JOIN Manufacturer where Category.description = 'Girls 6-8' and Manufacturer.name = 'Hasbro');
63insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku)  
64	(SELECT 'Play-Doh', 3.00, 4.00, Category.id, Manufacturer.id, 2
65	from Category JOIN Manufacturer where Category.description = 'All Children 5-10' and Manufacturer.name = 'Mattel');
66insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku) 
67	(SELECT 'Laugh and Learn Baby Blocks', 3.00, 4.00, Category.id, Manufacturer.id, 2
68	from Category JOIN Manufacturer where Category.description = 'All Children 3-5' and Manufacturer.name = 'Fischer-Price');
69insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-31', 2, 5.00, 10.00 from SalesRep where name='George');
70insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-01', 2, 5.00, 10.00 from SalesRep where name='George');
71insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-21', 2, 5.00, 10.00 from SalesRep where name='George');
72insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-11', 2, 5.00, 10.00 from SalesRep where name='George');
73insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-21', 2, 5.00, 10.00 from SalesRep where name='Julie');
74insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-31', 2, 5.00, 10.00 from SalesRep where name='Julie');
75insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-21', 2, 5.00, 10.00 from SalesRep where name='Julie');
76insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-21', 2, 5.00, 10.00 from SalesRep where name='Julie');
77insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-11', 2, 5.00, 10.00 from SalesRep where name='Jimmy');
78insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-21', 2, 5.00, 10.00 from SalesRep where name='Jimmy');
79insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-31', 2, 5.00, 10.00 from SalesRep where name='Jimmy');
80insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-01', 2, 5.00, 10.00 from SalesRep where name='Johnny');
81insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-11', 2, 5.00, 10.00 from SalesRep where name='Johnny');
82insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-21', 2, 5.00, 10.00 from SalesRep where name='Johnny');
83
84select SalesRep.name, Sales.units, Sales.unitAmount, Sales.saleAmount, Sales.saleDate from Sales join SalesRep on SalesRep.id = Sales.SalesRep_id ;
85select SalesRep.name, sum(units), sum(Sales.saleAmount), MONTH(Sales.saleDate) AS 'Sales-Month' 
86	from Sales join SalesRep on SalesRep.id = Sales.SalesRep_id 
87	GROUP BY SalesRep.name, MONTH(Sales.saleDate);