DDL DML

use blankey

DROP TABLE IF EXISTS [dbo].[Orders_Fact]
DROP TABLE IF EXISTS [dbo].[Times]
DROP TABLE IF EXISTS [dbo].[Customers]
DROP TABLE IF EXISTS [dbo].[Products]
DROP TABLE IF EXISTS [dbo].[Employees]


CREATE TABLE [dbo].[Employees](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [nvarchar](20) NOT NULL,
    [FirstName] [nvarchar](10) NOT NULL,
    [Region] [nvarchar](15) NULL,
     CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
    (
        [EmployeeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE [dbo].[Products] (
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [nvarchar](40) NOT NULL,
    [CategoryID] [int] NULL,
    [UnitPrice] [money] NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
    (
        [ProductID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE [dbo].[Customers] (
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
     CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
    (
        [CustomerID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE Times (
    TimeID INT IDENTITY(1,1) PRIMARY KEY,
    Year INT,
    Quarter INT,
    Month INT,
    Day INT
)

CREATE TABLE Orders_Fact (
    OrdersFactID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
    EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID),
    TimeID INT FOREIGN KEY REFERENCES Times(TimeID),
    CustomerID [nchar](5) FOREIGN KEY REFERENCES Customers(CustomerID),
    TotalPrice MONEY,
    Quantity INT
)


IF EXISTS ( SELECT * FROM sys.external_tables WHERE object_id = OBJECT_ID('dbo.ExtEmployees') )
   DROP EXTERNAL TABLE [dbo].[ExtEmployees]

IF EXISTS ( SELECT * FROM sys.external_tables WHERE object_id = OBJECT_ID('dbo.ExtProducts') )
DROP EXTERNAL TABLE [dbo].[ExtProducts]

IF EXISTS ( SELECT * FROM sys.external_tables WHERE object_id = OBJECT_ID('dbo.ExtCustomers') )
   DROP EXTERNAL TABLE [dbo].[ExtCustomers]

IF EXISTS ( SELECT * FROM sys.external_data_sources WHERE name = 'NorthwindAccess')
    DROP EXTERNAL DATA SOURCE NorthwindAccess

DROP DATABASE SCOPED CREDENTIAL northwindCredential

CREATE DATABASE SCOPED CREDENTIAL northwindCredential
WITH IDENTITY = 'dpomeroy',
SECRET = 'abcd123$';

CREATE EXTERNAL DATA SOURCE NorthwindAccess
WITH (
    TYPE=RDBMS,
    LOCATION='blankey.database.windows.net',
    DATABASE_NAME='northwind',
    CREDENTIAL= northwindCredential
);




CREATE EXTERNAL TABLE [dbo].[ExtEmployees] (
    [EmployeeID] [int] NOT NULL,
    [LastName] [nvarchar](20) NOT NULL,
    [FirstName] [nvarchar](10) NOT NULL,
    [Region] [nvarchar](15) NULL,
)WITH
(
    DATA_SOURCE = NorthwindAccess,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Employees'
);



CREATE EXTERNAL TABLE [dbo].[ExtProducts] (
    [ProductID] [int] NOT NULL,
    [ProductName] [nvarchar](40) NOT NULL,
    [CategoryID] [int] NULL,
    [UnitPrice] [money] NULL,
)WITH
(
    DATA_SOURCE = NorthwindAccess,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Products'
);


CREATE EXTERNAL TABLE [dbo].[ExtCustomers] (
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
)WITH
(
    DATA_SOURCE = NorthwindAccess,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'Customers'
);


SET IDENTITY_INSERT [dbo].[Employees] ON
INSERT INTO [dbo].[Employees](EmployeeID, LastName, FirstName, Region)
    SELECT EmployeeID, LastName, FirstName, Region
     FROM dbo.ExtEmployees;
SET IDENTITY_INSERT [dbo].[Employees] OFF

SET IDENTITY_INSERT [dbo].[Products] ON
INSERT INTO [dbo].[Products](ProductID, ProductName, CategoryID, UnitPrice)
    SELECT ProductID, ProductName, CategoryID, UnitPrice
     FROM dbo.ExtProducts;
SET IDENTITY_INSERT [dbo].[Products] OFF

INSERT INTO [dbo].[Customers](CustomerID, CompanyName)
    SELECT CustomerID, CompanyName
     FROM dbo.[ExtCustomers];



INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2015,2,4,9);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2015,2,6,19);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2015,3,7,25);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2015,3,7,26);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2015,3,9,5);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2015,4,11,9);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2015,4,12,15);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2016,1,2,17);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2016,1,3,19);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2016,1,3,24);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2016,2,4,4);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2016,2,5,1);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2016,3,7,14);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2017,1,2,7);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2017,1,2,19);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2017,2,4,10);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2017,2,6,30);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2017,3,7,30);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2017,3,9,23);
INSERT INTO Times (Year, Quarter, Month, Day) VALUES (2017,4,10,27);

INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (5,1,1,23.93,7);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (1,7,2,97.1,10);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (2,6,3,48.76,15);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (14,1,4,40.02,5);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (4,7,5,53.97,6);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (5,2,6,100.01,3);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (12,3,7,77.95,10);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (1,3,8,44.57,8);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (5,1,9,91.99,10);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (6,9,10,40.54,1);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (8,4,11,100.21,3);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (8,6,12,38.78,8);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (3,8,13,71.46,7);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (14,3,14,60.41,15);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (7,7,15,76.16,6);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (14,8,16,11.08,14);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (13,9,17,79.86,12);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (7,4,18,56.6,10);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (3,1,19,89.78,12);
INSERT INTO Orders_Fact (ProductId, EmployeeId, TimeId, TotalPrice, Quantity) VALUES (4,9,20,43.03,10);

UPDATE
  Orders_Fact
SET
  TotalPrice = Quantity * UnitPrice
FROM
  Orders_Fact
INNER JOIN
  Products
    ON Orders_Fact.ProductId = Products.ProductId