Quiz Solution

/*****************************

This code created the database structure, including the schema and tables.

*****************************/

If Exists(Select name from master.dbo.sysdatabases Where Name = 'NorthwindQuiz')

Begin

       USE [master]

       ALTER DATABASE [NorthwindQuiz] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

       DROP DATABASE [NorthwindQuiz]

End

Go

Create Database NorthwindQuiz

Go

USE NorthwindQuiz

Go

CREATE TABLE [dbo].[FactOrders](

       [CustomerID] [nvarchar](5) NOT NULL,

       [CustomerName] [nvarchar](100) NULL,

       [CustomerCountry] [nvarchar](100) NULL,

       [CalendarYear] [int] NOT NULL,

       [OrderCount] [int] NULL,

 CONSTRAINT [PK_FactOrders] PRIMARY KEY CLUSTERED

(

       [CustomerID] ASC,

       [CalendarYear] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/*****************************

This is the SQL script executed through SSIS to populate the data warehouse.

*****************************/

INSERT INTO FactOrders

SELECT [CustomerID] = Northwind.dbo.Orders.CustomerID

, Northwind.dbo.Customers.CompanyName

, Northwind.dbo.Customers.Country

, [CalendarYear] =  Cast( DATEPART(year,Northwind.dbo.Orders.OrderDate) as int)

, [OrderCount] = CAST(Count(Northwind.dbo.Orders.OrderID) as int)

FROM            Northwind.dbo.Orders 

INNER JOIN

                Northwind.dbo.Customers

 ON Northwind.dbo.Orders.CustomerID = Northwind.dbo.Customers.CustomerID

GROUP BY 

Northwind.dbo.Orders.CustomerID

,Northwind.dbo.Customers.CompanyName

, Northwind.dbo.Customers.Country

, Cast( DATEPART(year,Northwind.dbo.Orders.OrderDate) as int)