SQL-Ice Cream Assignment

The creation of a database, the creation of three tables, inserting so some records into the tables, an inner join to get the brands of a flavor from the tables using a cross reference, and another inner join to get the flavors of a brand from the tables using a cross reference.

 CREATE DATABASE ColdFusion  
 GO  
   
 USE ColdFusion  
 CREATE table iceCreamBrand(  
      brandId int Primary Key Identity(1,1),  
      brandName varchar(50)  
 )  
 CREATE table iceCreamFlavor(  
      flavorId int Primary Key Identity(1,1),  
      flavorName varchar(50)  
 )  
 CREATE table xrefIceCream(  
      flavorId int,  
      brandId int  
 )  
 Go  
   
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (1, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (2, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (3, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (4, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (5, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (6, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (7, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (8, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (9, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (10, 4)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (11, 4)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (12, 4)  
 Go  
   
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (1, N'Marshmallow Candy')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (2, N'Jelly Bean')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (3, N'Vanilla')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (4, N'Chocolate')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (5, N'Butter Pecan')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (6, N'Strawberry')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (7, N'Cookies and Cream')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (8, N'Coffee')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (9, N'Rocky Road')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (10, N'Chocolate Marshmallow')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (11, N'Cherry')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (12, N'Chocolate Almond')  
 Go  
   
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (1, N'Baskin-Robbins')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (2, N'Ben and Jerry''s')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (3, N'Gay Lea')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (4, N'Healthy Choice')  
 Go  
   
 declare @flavorName varchar(50);  
 set @flavorName = 'Vanilla';  
   
 Select brandName, flavName  
 From iceCreamBrand B  
      Inner Join xrefIceCream X  
      on B.brandId=X.brandId  
           Inner join iceCreamFlavor F  
           on F.flavID=X.flavorID  
 Where flavName=@flavorName  
 Go  
   
 declare @brandName varchar(50);  
 set @brandName = 'Baskin-Robbins';  
   
 Select flavName, brandName  
 From iceCreamFlavor F  
      Inner Join xrefIceCream X  
      on F.flavID=x.flavorId  
           inner join iceCreamBrand B  
           on B.brandID = x.brandID   
 where brandName=@brandName  
 Go  

More to come, CA.