ToySales MySQL Database

ToySales.sql

drop table if exists Sales;
drop table if exists SalesRep;
drop table if exists Product;
drop table if exists Category;
drop table if exists Manufacturer;
create table Manufacturer (
  id INT NOT NULL AUTO_INCREMENT
, name VARCHAR(50)
, PRIMARY KEY (id)
) ENGINE=INNODB;
create table Category (
  id INT NOT NULL AUTO_INCREMENT
, description VARCHAR(50)
, PRIMARY KEY (id)
) ENGINE=INNODB;
create table Product (
  id INT NOT NULL AUTO_INCREMENT
, name VARCHAR(50)
, nominal_cost DECIMAL(6,2)
, nominal_price DECIMAL(6,2)
, category_id INT NOT NULL
, manufacturer_id INT NOT NULL
, sku INT NOT NULL
, PRIMARY KEY (id) 
, CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES Category(id) 
, CONSTRAINT fk_manufacturer FOREIGN KEY (manufacturer_id) REFERENCES Manufacturer(id) 
) ENGINE=INNODB;
create table SalesRep (
  id INTEGER NOT NULL AUTO_INCREMENT
, name VARCHAR(50)
, PRIMARY KEY (id)
) ENGINE=INNODB;
create table Sales (
  id INTEGER NOT NULL AUTO_INCREMENT
, salesRep_id INTEGER 
, saleDate DATE
, units INT
, unitAmount DECIMAL(6,2)
, saleAmount DECIMAL(6,2)
, PRIMARY KEY (id) 
, CONSTRAINT fk_salesRep FOREIGN KEY (salesRep_id) REFERENCES SalesRep(id)
) ENGINE=INNODB;
insert into Manufacturer (name) values('Hasbro');
insert into Manufacturer (name) values('Mattel');
insert into Manufacturer (name) values('Fischer-Price');
insert into Category (description) values('Boys 4-6');
insert into Category (description) values('Boys 7-10');
insert into Category (description) values('Girls 4-6');
insert into Category (description) values('Girls 6-8');
insert into Category (description) values('All Children 5-10');
insert into Category (description) values('All Children 3-5');
insert into SalesRep (name) values ('George');
insert into SalesRep (name) values ('Julie');
insert into SalesRep (name) values ('Johnny');
insert into SalesRep (name) values ('Jimmy');
SELECT * FROM SalesRep; 
insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku) 
	(SELECT 'Uno Card Game', 7.00, 9.00, Category.id, Manufacturer.id, 1
	from Category JOIN Manufacturer where Category.description = 'All Children 5-10' and Manufacturer.name = 'Hasbro');
insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku) 
	(SELECT 'My Little Pony', 7.00, 11.00, Category.id, Manufacturer.id, 2
	from Category JOIN Manufacturer where Category.description = 'Girls 6-8' and Manufacturer.name = 'Hasbro');
insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku)  
	(SELECT 'Play-Doh', 3.00, 4.00, Category.id, Manufacturer.id, 2
	from Category JOIN Manufacturer where Category.description = 'All Children 5-10' and Manufacturer.name = 'Mattel');
insert into Product (name, nominal_cost, nominal_price, category_id, manufacturer_id, sku) 
	(SELECT 'Laugh and Learn Baby Blocks', 3.00, 4.00, Category.id, Manufacturer.id, 2
	from Category JOIN Manufacturer where Category.description = 'All Children 3-5' and Manufacturer.name = 'Fischer-Price');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-31', 2, 5.00, 10.00 from SalesRep where name='George');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-01', 2, 5.00, 10.00 from SalesRep where name='George');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-21', 2, 5.00, 10.00 from SalesRep where name='George');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-11', 2, 5.00, 10.00 from SalesRep where name='George');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-21', 2, 5.00, 10.00 from SalesRep where name='Julie');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-31', 2, 5.00, 10.00 from SalesRep where name='Julie');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-21', 2, 5.00, 10.00 from SalesRep where name='Julie');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-21', 2, 5.00, 10.00 from SalesRep where name='Julie');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-11', 2, 5.00, 10.00 from SalesRep where name='Jimmy');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-04-21', 2, 5.00, 10.00 from SalesRep where name='Jimmy');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-31', 2, 5.00, 10.00 from SalesRep where name='Jimmy');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-03-01', 2, 5.00, 10.00 from SalesRep where name='Johnny');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-11', 2, 5.00, 10.00 from SalesRep where name='Johnny');
insert into Sales (SalesRep_id, saleDate, units, unitAmount, saleAmount) (select id, '2012-05-21', 2, 5.00, 10.00 from SalesRep where name='Johnny');

select SalesRep.name, Sales.units, Sales.unitAmount, Sales.saleAmount, Sales.saleDate from Sales join SalesRep on SalesRep.id = Sales.SalesRep_id ;
select SalesRep.name, sum(units), sum(Sales.saleAmount), MONTH(Sales.saleDate) AS 'Sales-Month' 
	from Sales join SalesRep on SalesRep.id = Sales.SalesRep_id 
	GROUP BY SalesRep.name, MONTH(Sales.saleDate);