-- CREATE TABLES
CREATE TABLE FAN_Range
(Rangeno INT, Rangedesc VARCHAR(25))

CREATE TABLE FAN_Item
(Item INT, Itemdesc VARCHAR(25), Rangeno INT)

CREATE TABLE FAN_WeeklySales
(Wk INT, Item INT, Sales INT)


-- INSERT ROWS
INSERT INTO FAN_Range VALUES (1, 'Toys')
INSERT INTO FAN_Range VALUES (2, 'Books')
INSERT INTO FAN_Range VALUES (3, 'DVDs')

INSERT INTO FAN_Item VALUES (1, 'Ball', 1)
INSERT INTO FAN_Item VALUES (2, 'Teddy Picnic', 2)
INSERT INTO FAN_Item VALUES (3, 'Lion King', 3)
INSERT INTO FAN_Item VALUES (4, 'Bat', 1)
INSERT INTO FAN_Item VALUES (5, 'Hamster Guide', 2)
INSERT INTO FAN_Item VALUES (6, 'Shrek', 3)

INSERT INTO FAN_WeeklySales VALUES (200901, 1, 10)
INSERT INTO FAN_WeeklySales VALUES (200901, 2, 20)
INSERT INTO FAN_WeeklySales VALUES (200901, 3, 30)
INSERT INTO FAN_WeeklySales VALUES (200902, 4, 10)
INSERT INTO FAN_WeeklySales VALUES (200902, 5, 20)
INSERT INTO FAN_WeeklySales VALUES (200902, 6, 30)
INSERT INTO FAN_WeeklySales VALUES (200903, 1, 10)
INSERT INTO FAN_WeeklySales VALUES (200903, 2, 20)
INSERT INTO FAN_WeeklySales VALUES (200903, 3, 30)
INSERT INTO FAN_WeeklySales VALUES (200904, 4, 10)
INSERT INTO FAN_WeeklySales VALUES (200904, 5, 20)
INSERT INTO FAN_WeeklySales VALUES (200904, 6, 30)


-- TABLE OUTPUTS
SELECT * FROM FAN_Range
SELECT * FROM FAN_Item
SELECT * FROM FAN_WeeklySales
	


-- QUERIES
-- Number of Items in Each Range
SELECT
	r.Rangeno,
	r.Rangedesc,
	COUNT(Item) AS ItemCount
FROM
	FAN_Range AS r
	INNER JOIN FAN_Item AS i
		ON r.Rangeno = i.Rangeno
GROUP BY
	r.Rangeno,
	r.Rangedesc
	
-- Total Sales for each Item
SELECT
	i.Item,
	i.Itemdesc,
	SUM(s.Sales) AS TotalSales
FROM
	FAN_Item AS i
	INNER JOIN FAN_WeeklySales AS s
		ON i.item = s.Item
GROUP BY
	i.item,
	i.Itemdesc
	
-- Possible Number of Items and Total Sales for each Range.
SELECT
	r.Rangeno,
	r.Rangedesc,
	COUNT(i.Item) AS ItemCount,
	SUM(s.Sales) AS TotalSales
FROM
	FAN_Range AS r
	INNER JOIN FAN_Item AS i
		ON r.Rangeno = i.Rangeno
	INNER JOIN FAN_WeeklySales AS s
		ON i.item = s.Item
GROUP BY
	r.Rangeno,
	r.Rangedesc
	
-- Correct Number of Items and Total Sales for each Range working around the Fan Trap.
SELECT
	r.Rangeno,
	r.Rangedesc,
	i.ItemCount,
	s.TotalSales
FROM
	FAN_Range AS r
	INNER JOIN (
		SELECT
			i.Rangeno,
			COUNT(i.Item) AS ItemCount
		FROM
			FAN_Item AS i
		GROUP BY
			i.Rangeno
		) AS i
		ON r.Rangeno = i.Rangeno
	INNER JOIN (
		SELECT
			i.Rangeno,
			SUM(s.Sales) AS TotalSales
		FROM
			FAN_Item AS i
			INNER JOIN FAN_WeeklySales AS s
				ON i.Item = s.Item
		GROUP BY
			i.Rangeno
		) AS s
		ON r.Rangeno = s.Rangeno
	
	
-- DROP TABLES
DROP TABLE FAN_Range
DROP TABLE FAN_Item
DROP TABLE FAN_WeeklySales