--
--  D1 - Create database
--

DROP DATABASE IF EXISTS [mssqltips];
CREATE DATABASE [mssqltips]
(
  EDITION = 'Premium',
  SERVICE_OBJECTIVE = 'P1'
);


--
--  D2 - Create schema
--

CREATE SCHEMA [SalesLT] AUTHORIZATION [dbo];



--
--  S1 - Create logging table
--

CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY (1, 1) NOT NULL,
    [PostTime] [datetime] NOT NULL, 
    [DatabaseUser] [sysname] NOT NULL, 
    [Event] [sysname] NOT NULL, 
    [Schema] [sysname] NULL, 
    [Object] [sysname] NULL, 
    [TSQL] [nvarchar](max) NOT NULL, 
    [XmlEvent] [xml] NOT NULL
);


--
--  S2 - Create build version table
--

CREATE TABLE [dbo].[BuildVersion](
	[Version] [nvarchar](50) NULL,
	[Description] [nvarchar](150) NULL,
	[VersionDate] [datetime] NULL
);



--
-- TR1 - Create logging trigger
--

CREATE TRIGGER [trgDatabaseLog] ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [dbo].[DatabaseLog] 
        (
        [PostTime], 
        [DatabaseUser], 
        [Event], 
        [Schema], 
        [Object], 
        [TSQL], 
        [XmlEvent]
        ) 
    VALUES 
        (
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
        @data
        );
END;


--
--  T01 - Create table - currency
--

CREATE TABLE [SalesLT].[DimCurrency](
	[CurrencyKey] [int] IDENTITY(1,1) NOT NULL,
	[CurrencyAlternateKey] [nchar](3) NOT NULL,
	[CurrencyName] [nvarchar](50) NOT NULL
);


--
--  T02 - Create table - customer
--

CREATE TABLE [SalesLT].[DimCustomer](
	[CustomerKey] [int] IDENTITY(1,1) NOT NULL,
	[GeographyKey] [int] NULL,
	[CustomerAlternateKey] [nvarchar](15) NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[NameStyle] [int] NULL,
	[BirthDate] [date] NULL,
	[MaritalStatus] [nchar](1) NULL,
	[Suffix] [nvarchar](10) NULL,
	[Gender] [nvarchar](1) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[YearlyIncome] [money] NULL,
	[TotalChildren] [tinyint] NULL,
	[NumberChildrenAtHome] [tinyint] NULL,
	[EnglishEducation] [nvarchar](40) NULL,
	[SpanishEducation] [nvarchar](40) NULL,
	[FrenchEducation] [nvarchar](40) NULL,
	[EnglishOccupation] [nvarchar](100) NULL,
	[SpanishOccupation] [nvarchar](100) NULL,
	[FrenchOccupation] [nvarchar](100) NULL,
	[HouseOwnerFlag] [nchar](1) NULL,
	[NumberCarsOwned] [tinyint] NULL,
	[AddressLine1] [nvarchar](120) NULL,
	[AddressLine2] [nvarchar](120) NULL,
	[Phone] [nvarchar](20) NULL,
	[DateFirstPurchase] [date] NULL,
	[CommuteDistance] [nvarchar](15) NULL
);


--
--  T03 - Create table - date
--

CREATE TABLE [SalesLT].[DimDate](
	[DateKey] [int] NOT NULL,
	[FullDateAlternateKey] [date] NOT NULL,
	[DayNumberOfWeek] [tinyint] NOT NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NOT NULL,
	[SpanishDayNameOfWeek] [nvarchar](10) NOT NULL,
	[FrenchDayNameOfWeek] [nvarchar](10) NOT NULL,
	[DayNumberOfMonth] [tinyint] NOT NULL,
	[DayNumberOfYear] [smallint] NOT NULL,
	[WeekNumberOfYear] [tinyint] NOT NULL,
	[EnglishMonthName] [nvarchar](10) NOT NULL,
	[SpanishMonthName] [nvarchar](10) NOT NULL,
	[FrenchMonthName] [nvarchar](10) NOT NULL,
	[MonthNumberOfYear] [tinyint] NOT NULL,
	[CalendarQuarter] [tinyint] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarSemester] [tinyint] NOT NULL,
	[FiscalQuarter] [tinyint] NOT NULL,
	[FiscalYear] [smallint] NOT NULL,
	[FiscalSemester] [tinyint] NOT NULL
);


--
--  T04 - Create table - geography
--

CREATE TABLE [SalesLT].[DimGeography](
	[GeographyKey] [int] IDENTITY(1,1) NOT NULL,
	[City] [nvarchar](30) NULL,
	[StateProvinceCode] [nvarchar](3) NULL,
	[StateProvinceName] [nvarchar](50) NULL,
	[CountryRegionCode] [nvarchar](3) NULL,
	[EnglishCountryRegionName] [nvarchar](50) NULL,
	[SpanishCountryRegionName] [nvarchar](50) NULL,
	[FrenchCountryRegionName] [nvarchar](50) NULL,
	[PostalCode] [nvarchar](15) NULL,
	[SalesTerritoryKey] [int] NULL,
	[IpAddressLocator] [nvarchar](15) NULL
);



--
--  T05 - Create table - product
--

CREATE TABLE [SalesLT].[DimProduct](
	[ProductKey] [int] IDENTITY(1,1) NOT NULL,
	[ProductAlternateKey] [nvarchar](25) NULL,
	[ProductSubcategoryKey] [int] NULL,
	[WeightUnitMeasureCode] [nchar](3) NULL,
	[SizeUnitMeasureCode] [nchar](3) NULL,
	[EnglishProductName] [nvarchar](50) NOT NULL,
	[SpanishProductName] [nvarchar](50) NULL,
	[FrenchProductName] [nvarchar](50) NULL,
	[StandardCost] [money] NULL,
	[FinishedGoodsFlag] [int] NULL,
	[Color] [nvarchar](15) NOT NULL,
	[SafetyStockLevel] [smallint] NULL,
	[ReorderPoint] [smallint] NULL,
	[ListPrice] [money] NULL,
	[Size] [nvarchar](50) NULL,
	[SizeRange] [nvarchar](50) NULL,
	[Weight] [float] NULL,
	[DaysToManufacture] [int] NULL,
	[ProductLine] [nchar](2) NULL,
	[DealerPrice] [money] NULL,
	[Class] [nchar](2) NULL,
	[Style] [nchar](2) NULL,
	[ModelName] [nvarchar](50) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[Status] [nvarchar](7) NULL
);


--
--  T06 - Create table - product category
--

CREATE TABLE [SalesLT].[DimProductCategory](
	[ProductCategoryKey] [int] IDENTITY(1,1) NOT NULL,
	[ProductCategoryAlternateKey] [int] NULL,
	[EnglishProductCategoryName] [nvarchar](50) NOT NULL,
	[SpanishProductCategoryName] [nvarchar](50) NOT NULL,
	[FrenchProductCategoryName] [nvarchar](50) NOT NULL
);


--
--  T07 - Create table - product subcategory
--

CREATE TABLE [SalesLT].[DimProductSubcategory](
	[ProductSubcategoryKey] [int] IDENTITY(1,1) NOT NULL,
	[ProductSubcategoryAlternateKey] [int] NULL,
	[EnglishProductSubcategoryName] [nvarchar](50) NOT NULL,
	[SpanishProductSubcategoryName] [nvarchar](50) NOT NULL,
	[FrenchProductSubcategoryName] [nvarchar](50) NOT NULL,
	[ProductCategoryKey] [int] NULL
);


--
--  T08 - Create table - sales reason
--


CREATE TABLE [SalesLT].[DimSalesReason](
	[SalesReasonKey] [int] IDENTITY(1,1) NOT NULL,
	[SalesReasonAlternateKey] [int] NOT NULL,
	[SalesReasonName] [nvarchar](50) NOT NULL,
	[SalesReasonReasonType] [nvarchar](50) NOT NULL
);


--
--  T09 - Create table - sales territory
--

CREATE TABLE [SalesLT].[DimSalesTerritory](
	[SalesTerritoryKey] [int] IDENTITY(1,1) NOT NULL,
	[SalesTerritoryAlternateKey] [int] NULL,
	[SalesTerritoryRegion] [nvarchar](50) NOT NULL,
	[SalesTerritoryCountry] [nvarchar](50) NOT NULL,
	[SalesTerritoryGroup] [nvarchar](50) NULL
);


--
--  T10 - Create table - internet sales
--

CREATE TABLE [SalesLT].[FactInternetSales](
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[OrderQuantity] [smallint] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[ExtendedAmount] [money] NOT NULL,
	[UnitPriceDiscountPct] [float] NOT NULL,
	[DiscountAmount] [float] NOT NULL,
	[ProductStandardCost] [money] NOT NULL,
	[TotalProductCost] [money] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL
);


--
--  T11 - Create table - internet sales reason
--

CREATE TABLE [SalesLT].[FactInternetSalesReason](
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[SalesReasonKey] [int] NOT NULL
);



--
--  DML01 - Insert data
--

INSERT INTO [dbo].[BuildVersion] 
VALUES
( 
  '01.01',
  'Script Activity - test database example',
  getdate()
);


--
--  DML02 - Select data
--

SELECT * FROM [dbo].[BuildVersion];

--
--  DML03 - Update data
--

UPDATE B SET B.VERSION = '01.02' FROM [dbo].[BuildVersion] B;


--
--  DML04 - Delete data
--

DELETE  FROM [dbo].[BuildVersion] WHERE [VERSION] = '01.02' ;



--
--  P01 - Alter table add P.K. - currency
--


ALTER TABLE [SalesLT].[DimCurrency] WITH CHECK ADD 
    CONSTRAINT [PK_DimCurrency_CurrencyKey] PRIMARY KEY CLUSTERED 
    (
       [CurrencyKey]
    ) ;


--
--  P02 - Alter table add P.K. - customer
--

ALTER TABLE [SalesLT].[DimCustomer] WITH CHECK ADD 
    CONSTRAINT [PK_DimCustomer_CustomerKey] PRIMARY KEY CLUSTERED
    (
        [CustomerKey]
    ) ;


--
--  P03 - Alter table add P.K. - date
--

ALTER TABLE [SalesLT].[DimDate] WITH CHECK ADD 
    CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED 
    (
        [DateKey]
    ) ;


--
--  P04 - Alter table add P.K. - geography
--

ALTER TABLE [SalesLT].[DimGeography] WITH CHECK ADD 
    CONSTRAINT [PK_DimGeography_GeographyKey] PRIMARY KEY CLUSTERED 
    (
       [GeographyKey]
    ) ;


--
--  P05 - Alter table add P.K. - product
--

ALTER TABLE [SalesLT].[DimProduct] WITH CHECK ADD 
    CONSTRAINT [PK_DimProduct_ProductKey] PRIMARY KEY CLUSTERED 
    (
        [ProductKey]
    ) ;



--
--  P06 - Alter table add P.K. - product category
--

ALTER TABLE [SalesLT].[DimProductCategory] WITH CHECK ADD 
    CONSTRAINT [PK_DimProductCategory_ProductCategoryKey] PRIMARY KEY CLUSTERED 
    (
        [ProductCategoryKey]
    ) ;



--
--  P07 - Alter table add P.K. - product sub category
--

ALTER TABLE [SalesLT].[DimProductSubcategory] WITH CHECK ADD 
    CONSTRAINT [PK_DimProductSubcategory_ProductSubcategoryKey] PRIMARY KEY CLUSTERED 
    (
        [ProductSubcategoryKey]
    ) ;


--
--  P08 - Alter table add P.K. - sales reason
--

ALTER TABLE [SalesLT].[DimSalesReason] WITH CHECK ADD 
    CONSTRAINT [PK_DimSalesReason_SalesReasonKey] PRIMARY KEY CLUSTERED 
    (
        [SalesReasonKey]
    ) ;


--
--  P09 - Alter table add P.K. - sales territory
--

ALTER TABLE [SalesLT].[DimSalesTerritory] WITH CHECK ADD 
    CONSTRAINT [PK_DimSalesTerritory_SalesTerritoryKey] PRIMARY KEY CLUSTERED 
    (
        [SalesTerritoryKey]
    ) ;


--
--  P10 - Alter table add P.K. - internet sales
--

ALTER TABLE [SalesLT].[FactInternetSales] WITH CHECK ADD 
    CONSTRAINT [PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber] PRIMARY KEY CLUSTERED 
    (
        [SalesOrderNumber], [SalesOrderLineNumber]
    ) ;


--
--  P11 - Alter table add P.K. - internet sales reason
--

ALTER TABLE [SalesLT].[FactInternetSalesReason] WITH CHECK ADD 
    CONSTRAINT [PK_FactInternetSalesReason_SalesOrderNumber_SalesOrderLineNumber_SalesReasonKey] PRIMARY KEY CLUSTERED 
    (
        [SalesOrderNumber], [SalesOrderLineNumber], [SalesReasonKey]
    ) ;



--
--  I01 - Create indexes - currency
--

CREATE UNIQUE NONCLUSTERED INDEX [AK_DimCurrency_CurrencyAlternateKey] ON [SalesLT].[DimCurrency]([CurrencyAlternateKey]);
CREATE UNIQUE NONCLUSTERED INDEX [IX_DimCustomer_CustomerAlternateKey] ON [SalesLT].[DimCustomer]([CustomerAlternateKey]);
CREATE UNIQUE NONCLUSTERED INDEX [AK_DimDate_FullDateAlternateKey] ON [SalesLT].[DimDate]( [FullDateAlternateKey]);


--
--  I02 - Create indexes - product
--

ALTER TABLE [SalesLT].[DimProduct] ADD  CONSTRAINT [AK_DimProduct_ProductAlternateKey_StartDate] UNIQUE NONCLUSTERED 
(
	[ProductAlternateKey] ASC,
	[StartDate] ASC
);


--
--  I03 - Create indexes - product category
--


ALTER TABLE [SalesLT].[DimProductCategory] ADD  CONSTRAINT [AK_DimProductCategory_ProductCategoryAlternateKey] UNIQUE NONCLUSTERED 
(
	[ProductCategoryAlternateKey] ASC
);


--
--  I04 - Create indexes - product subcategory
--

ALTER TABLE [SalesLT].[DimProductSubcategory] ADD  CONSTRAINT [AK_DimProductSubcategory_ProductSubcategoryAlternateKey] UNIQUE NONCLUSTERED 
(
	[ProductSubcategoryAlternateKey] ASC
);


--
--  I05 - Create indexes - sales territory
--


ALTER TABLE [SalesLT].[DimSalesTerritory] ADD  CONSTRAINT [AK_DimSalesTerritory_SalesTerritoryAlternateKey] UNIQUE NONCLUSTERED 
(
	[SalesTerritoryAlternateKey] ASC
);




--
--  F01 - Alter table add F.K. - customer
--

ALTER TABLE [SalesLT].[DimCustomer] ADD
	CONSTRAINT [FK_DimCustomer_DimGeography] FOREIGN KEY
	(
		[GeographyKey]
	)
	REFERENCES [SalesLT].[DimGeography] ([GeographyKey]);


--
--  F02 - Alter table add F.K. - geography
--

ALTER TABLE [SalesLT].[DimGeography] ADD
    CONSTRAINT [FK_DimGeography_DimSalesTerritory]  FOREIGN KEY 
    (
        [SalesTerritoryKey]
    ) REFERENCES [SalesLT].[DimSalesTerritory] ([SalesTerritoryKey]);

    
--
--  F03 - Alter table add F.K. - product
--

ALTER TABLE [SalesLT].[DimProduct] ADD 
    CONSTRAINT [FK_DimProduct_DimProductSubcategory] FOREIGN KEY 
    (
        [ProductSubcategoryKey]
    ) REFERENCES [SalesLT].[DimProductSubcategory] ([ProductSubcategoryKey]);


--
--  F04 - Alter table add F.K. - product subcategory
--

ALTER TABLE [SalesLT].[DimProductSubcategory] ADD 
    CONSTRAINT [FK_DimProductSubcategory_DimProductCategory] FOREIGN KEY 
    (
        [ProductCategoryKey]
    ) REFERENCES [SalesLT].[DimProductCategory] ([ProductCategoryKey]);


--
--  F05 - Alter table add F.K. - internet sales
--

ALTER TABLE [SalesLT].[FactInternetSales] ADD 
    CONSTRAINT [FK_FactInternetSales_DimCurrency] FOREIGN KEY 
    (
        [CurrencyKey]
    ) REFERENCES [SalesLT].[DimCurrency] ([CurrencyKey]),
	 CONSTRAINT [FK_FactInternetSales_DimCustomer] FOREIGN KEY 
    (
        [CustomerKey]
    ) REFERENCES [SalesLT].[DimCustomer] ([CustomerKey]),
	 CONSTRAINT [FK_FactInternetSales_DimDate] FOREIGN KEY 
    (
        [OrderDateKey]
    ) REFERENCES [SalesLT].[DimDate] ([DateKey]),
	 CONSTRAINT [FK_FactInternetSales_DimDate1] FOREIGN KEY 
    (
        [DueDateKey]
    ) REFERENCES [SalesLT].[DimDate] ([DateKey]),
	 CONSTRAINT [FK_FactInternetSales_DimDate2] FOREIGN KEY 
    (
        [ShipDateKey]
    ) REFERENCES [SalesLT].[DimDate] ([DateKey]),
	 CONSTRAINT [FK_FactInternetSales_DimProduct] FOREIGN KEY 
    (
        [ProductKey]
    ) REFERENCES [SalesLT].[DimProduct] ([ProductKey]),
	CONSTRAINT [FK_FactInternetSales_DimSalesTerritory] FOREIGN KEY 
    (
        [SalesTerritoryKey]
    ) REFERENCES [SalesLT].[DimSalesTerritory] ([SalesTerritoryKey]);


--
--  F06 - Alter table add F.K. - internet sales reason
--

ALTER TABLE [SalesLT].[FactInternetSalesReason] ADD 
    CONSTRAINT [FK_FactInternetSalesReason_FactInternetSales] FOREIGN KEY 
    (
        [SalesOrderNumber], [SalesOrderLineNumber]
    ) REFERENCES [SalesLT].[FactInternetSales] ([SalesOrderNumber], [SalesOrderLineNumber]),
	CONSTRAINT [FK_FactInternetSalesReason_DimSalesReason] FOREIGN KEY
	(
		[SalesReasonKey]
	) REFERENCES [SalesLT].[DimSalesReason] ([SalesReasonKey]);



--
--  V01 - Create view
--


CREATE VIEW [SalesLT].RptPreparedData
AS
SELECT
   pc.EnglishProductCategoryName
  ,Coalesce(p.ModelName, p.EnglishProductName) AS Model
  ,c.CustomerKey
  ,s.SalesTerritoryGroup AS Region
  ,CASE
    WHEN month(current_timestamp) < month(c.BirthDate) THEN 
      year(c.BirthDate) - year(current_timestamp) - 1
    WHEN month(current_timestamp) = month(c.BirthDate) AND day(current_timestamp) < day(c.BirthDate) THEN 
	  year(c.BirthDate) - year(current_timestamp) - 1
    ELSE 
	    year(c.BirthDate) - year(current_timestamp)
  END AS Age
  ,CASE
      WHEN c.YearlyIncome < 40000 THEN 'Low'
      WHEN c.YearlyIncome > 60000 THEN 'High'
      ELSE 'Moderate'
  END AS IncomeGroup
  ,d.CalendarYear
  ,d.FiscalYear
  ,d.MonthNumberOfYear AS Month
  ,f.SalesOrderNumber AS OrderNumber
  ,f.SalesOrderLineNumber AS LineNumber
  ,f.OrderQuantity AS Quantity
  ,f.ExtendedAmount AS Amount   
FROM
  [saleslt].factinternetsales as f
INNER JOIN 
  [saleslt].dimdate as d
ON 
  f.OrderDateKey = d.DateKey

INNER JOIN 
  [saleslt].dimproduct as p
ON 
  f.ProductKey = p.ProductKey
  
INNER JOIN 
  [saleslt].dimproductsubcategory as psc
ON 
  p.ProductSubcategoryKey = psc.ProductSubcategoryKey

INNER JOIN 
  [saleslt].dimproductcategory as pc
ON 
  psc.ProductCategoryKey = pc.ProductCategoryKey
  
INNER JOIN 
  [saleslt].dimcustomer as c
ON 
  f.CustomerKey = c.CustomerKey

INNER JOIN 
  [saleslt].dimgeography as g
ON 
  c.GeographyKey = g.GeographyKey

INNER JOIN 
  [saleslt].dimsalesterritory as s
ON 
  g.SalesTerritoryKey = s.SalesTerritoryKey;



--
-- U01 - Create contained user
-- 

CREATE USER [DonaldKnuth] WITH PASSWORD = 'atDaMRmTdG6yjDuc';
GO

--
-- U02 - Give read rights
-- 

ALTER ROLE db_datareader ADD MEMBER [DonaldKnuth];
GO



--
-- DML01 - Select from view
-- 

SELECT 
  CalendarYear as RptYear,
  Month as RptMonth,
  Region as RptRegion,
  Model as ModelNo,
  SUM(Quantity) as TotalQty,
  SUM(Amount) as TotalAmt
FROM 
  [saleslt].RptPreparedData 
GROUP BY
  CalendarYear,
  Month,
  Region,
  Model
ORDER BY
  CalendarYear,
  Month,
  Region
