sales_example.sql

--***********************************************************************************
-- Title:  SuperMarket_Sales
-- Author: Lee McCraw-Leavitt + Bryan Davis
-- Description: create database and tables for sales support
-- Date created: July 3, 2017
-- Date modified: April 8, 2019
-- Change Log:  Added more data and other analysis
--***********************************************************************************

CREATE DATABASE  SuperMarket_Sales;    
GO


--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- Which database do you want to use?
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
USE SuperMarket_Sales;

GO


--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- CREATE the Customers table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
CREATE TABLE  dbo.Customers (
	CustomerID  int Primary Key,   
	CustomerFirstName  varchar(50),  
	CustomerLastName varchar(50),
	CustomerAddress1 varchar (50),
	CustomerAddress2 varchar(50) NULL,  	
	CustomerCity varchar(50),
	CustomerState char(2)
);



--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- INSERT data into the Customers table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
INSERT INTO dbo.Customers  
	VALUES  
        (1, 'Bob', 'Smith', '123 Main', ' ','Bellevue', 'WA' )
        , (2, 'Jane', 'Clark', '254 West Stewart', ' ','Seattle', 'WA' )
        , (3, 'Julia', 'Robertson', '987 North Lake', ' ','Seattle', 'WA' )
;

GO

--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- CREATE the Products table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
CREATE TABLE dbo.Products (
	ProductsID int Primary Key,
	ProductName varchar (75),
	ProductPriceCurrent varchar (50),
);

GO

--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- INSERT data into the Products table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
INSERT INTO dbo.Products 
	VALUES 
        ( 1 , 'Apples', '$0.89') 
        , ( 2 , 'Milk', '$1.59') 
        , ( 3 , 'Bread', '$2.28')
        , ( 4 , 'Coffee', '$6.00')
        , ( 5 , 'Juice', '$3.50')
        , ( 6 , 'Rice', '$1.15')
        , ( 7 , 'Candy', '$2.99')
        , ( 8 , 'Yogurt', '$4.55')
;
GO




--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- CREATE the Sales table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
CREATE TABLE Sales (
	SalesID int Primary Key,
	CustomerID int,
	SalesDate date
);

GO


--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- INSERT data into the Sales table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
INSERT INTO dbo.Sales
	VALUES 
        (1 , 1, '5/2/2019'),
        (2 , 1, '5/3/2019'),
        (3 , 2, '5/3/2019'),
        (4 , 2, '5/4/2019'),
        (5 , 3, '5/3/2019'),
        (6 , 3, '5/6/2019'),

;
GO


--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- CREATE the SalesLineItems table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
CREATE TABLE dbo.SalesLineItems (
	SalesID int,
	LineItemID int,
	ProductID int,
	SalesQuantity int
    PRIMARY KEY ( LineItemID, SalesID )
);

GO

--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- INSERT data into the SalesLineItems table
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
INSERT INTO dbo.SalesLineItems  
	VALUES  
        
        -- BOB Trip One
        ( 1 , 1 , 1 , 12 )
        , ( 1 , 2 ,  2 , 2 )
        , ( 1 , 3 , 3 , 1 )

        -- BOB Trip Two
        , ( 2 , 1 , 8 , 2 )
        , ( 2 , 2 ,  4 , 6 )
        , ( 2 , 3 , 6 , 3 )

        -- JANE Trip One
        , ( 3 , 1 , 2 , 2 )
        , ( 3 , 2 , 3 , 2 )
        , ( 3 , 3 , 7 , 2 )
        , ( 3 , 4 , 1 , 2 )

        -- JANE Trip Two
        , ( 4 , 1 , 6 , 6 )
        , ( 4 , 2 , 5 , 4 )
        , ( 4 , 3 , 2 , 2 )
        , ( 4 , 4 , 1 , 1 )
        , ( 4 , 5 , 8 , 2 )

        -- JULIA Trip One
        , ( 5 , 1 , 8 , 4 )
        , ( 5 , 2 , 1 , 4 )
        , ( 5 , 3 , 2 , 4 )
        , ( 5 , 4 , 5 , 4 )
        , ( 5 , 5 , 4 , 4 )

        -- JULIA Trip Two
        , ( 6 , 1 , 3 , 1 )
        , ( 6 , 2 , 2 , 1 )
        , ( 6 , 3 , 1 , 1 )

;

GO

--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- FINAL DATASETS
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Sales;
SELECT * FROM SalesLineItems;

GO


--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- SIMPLE ANALYSIS
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
select pds.ProductName, count(sls.SalesQuantity) as ProductSold
from 
    SalesLineItems as sli
        join Products as prd
            on sli.ProductID = prd.ProductID
group by 
    pds.ProductName



--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-- CREATE table from analysis
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
create table ProductCounts as
select pds.ProductName, count(sls.SalesQuantity) as ProductSold
from 
    SalesLineItems as sli
        join Products as prd
            on sli.ProductID = prd.ProductID
group by 
    pds.ProductName