Inner Join Practice 2 Ice Cream table SQL

Ice cream table
Group members: Ben, Melissa, Jo

--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'


/*
select *
from xrefFlavorBrand

select FL.*, xFB.BrandID, B.BrandName
from flavor FL 
inner join xrefFlavorBrand xFB
on FL.Flavor_ID = xFB.xrefID
inner join Brand B
on xFB.BrandID = B.BrandID
where FL.flavor_name = 'Coffee'
*/