Inner Join Practice 2

 --drop table Brand
  
 --drop table Flavor
  
 --drop table xrefFlavorBrand
  
 --drop table sales
  
 --drop table distributors
  
 --drop table xrefDistFlavorBrand
  
 --drop table xrefDistSales
  
 use Yummys
  
 go
  
 create table Brand
  
 (
  
    BrandID int identity(1,1) primary key
  
    ,BrandName varchar(50)
  
    ,BrandLocation varchar(50)
  
 )
  
 insert into Brand (BrandName, BrandLocation) values ('BlueBunny', 'San Francisco')
  
 insert into Brand (BrandName, BrandLocation) values ('PurpleMonkey', 'Detroit')
  
 insert into Brand (BrandName, BrandLocation) values ('PinkHippo', 'Malibu')
  
 insert into Brand (BrandName, BrandLocation) values ('GreenRabbit', 'Tucson')
  
 insert into Brand (BrandName, BrandLocation) values ('RainbowLamb', 'Minneapolis')
  
 select * 
  
 from Brand
  
 create table flavor
  
    (
  
    Flavor_ID int identity (1,1) PRIMARY KEY,
  
    flavor_name varchar (40),
  
    descraption varchar (100),
  
    quantity int
  
    )
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    values
  
    ('choclate', 'classic choclate', 2)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    values
  
    ('Vanilla', 'Plain Vanilla', 4)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Neopolatin', '1/3 Choclate, 1/3 Vanilla. 1/3 Strawberry', 1)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Coffee', 'Fresh Cround Coffee Flavor', 0)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Sardine', 'Self-Explanatory', 7)
  
 select *
  
 from flavor
  
 create table xrefFlavorBrand
  
 (
  
    xrefID int identity (1,1) primary key
  
    , BrandID int
  
    , FlavorID int
  
 )
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (1,1)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (2,2)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (3,3)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (4,4)
  
 insert into xrefFlavorBrand (BrandID, FlavorID) values (5,5)
  
 create table sales
  
 (
  
    SalesID int identity (1,1) primary key
  
    , xrefID int
  
    , ScoopNo int
  
    , Price decimal(4,2)
  
    , SalesDate int
  
    )
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (1, 2, 2.00, 1/2/10)
  
 insert into sales 
  
    (xrefID, ScoopNo, Price, SalesDate) values (2, 3, 3.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (3, 2, 2.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (2,3,3.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (4,2,2.00, 1/4/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (5, 4, 4.00, 1/4/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (1, 5, 5.00, 1/5/10)  
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (4, 3, 3.00, 1/5/10)  
  
 select * from sales
  
 create table xrefDistFlavorBrand
  
 (
  
       xrefDistSalesID int identity (1,1) primary key
  
       ,BrandID int
  
       ,FlavorID int
  
       ,distributor_ID int
  
 )
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(1,1,1)
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(2,2,2)
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(3,3,3)
  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(4,4,4)
  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values (5,5,5)
  
 select * 
  
 from xrefDistFlavorBrand
  
 --here is the second table
  
 create table xrefDistSales
  
 (
  
       xrefDistSalesID int identity (1,1) primary key
  
       ,BrandID int
  
       ,SalesID int      
  
 )
  
 insert into xrefDistSales(BrandID,SalesID) values (1,1)
  
 insert into xrefDistSales(BrandID,SalesID) values (2,2)
  
 insert into xrefDistSales(BrandID,SalesID) values (3,3)
  
 insert into xrefDistSales(BrandID,SalesID) values (4,4)
  
 insert into xrefDistSales(BrandID,SalesID) values (5,5)
  
 select *
  
 from xrefDistSales
  
 create table distributors
  
    (
  
       distributor_ID int identity (1,1) PRIMARY KEY,
  
       DistributorName varchar (30)
  
    );
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('ND Icecream')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('GF Grocery')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Henreys Foods')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Mayville Grocery')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Red River Valley Icecream')
  
 select *
  
 from distributors
  
 --keep track of the number of tubs of ice cream on hand
  
 Select * 
  
 from flavor FL
  
 inner join xrefDistFlavorBrand xDFB
  
 on FL.Flavor_ID = xDFB.FlavorID
  
 where quantity <=1
  
 --search distributors by flavor and brand, get a list of distributors
  
 select * 
  
 from distributors D
  
 inner join xrefDistFlavorBrand xDFB
  
 on d.Distributor_ID = xDFB.Distributor_ID
  
 inner join flavor F
  
 on f.Flavor_ID = xDFB.FlavorID
  
 inner join Brand B
  
 on B.BrandID = xDFB.BrandID
  
 where flavor_name = 'choclate'
  
 and
  
 BrandName = 'BlueBunny'
  
 --Keep track of which flavors and brands were sold
  
 select * 
  
 from sales S
  
 inner join xrefDistSales xDS
  
 on s.SalesID = xDS.SalesID
  
 inner join xrefDistFlavorBrand xDFB
  
 on xDS.xrefDistSalesID = xDFB.xrefDistSalesID
  
 inner join flavor F
  
 on f.Flavor_ID = xDFB.FlavorID
  
 where f.flavor_name = 'vanilla'