/*************************************************************/
/* BEGINNING TSQL MODULE 4 - SECTION 4.2 Creating a database */
/*************************************************************/
USE MASTER
GO

ALTER DATABASE [TEST2]
SET SINGLE_USER;
GO

DROP DATABASE IF EXISTS [TEST2]
GO

/********************************************/
/* Use Object Explorer to create a database */
/* called object explorer                   */
/********************************************/

/*******************************/
/* what did SQL Server create? */
/*******************************/

/*
Right click database
Click on Profiles
view General tab
*/

/*************************/
/* Now drop the database */
/*************************/

ALTER DATABASE [TEST2]
SET SINGLE_USER;
GO

DROP DATABASE IF EXISTS [TEST2]
GO

/*************************************/
/* SECTION 4.2 - Creating a Database */
/*************************************/

USE [master]
GO

ALTER DATABASE [CUSTOMER]
SET SINGLE_USER;
GO

DROP DATABASE [CUSTOMER]
GO

/******/
/* OR */
/******/

DROP DATABASE IF EXISTS [CUSTOMER]
GO

/**************************************************/
/* This is a Grumpy Old IT Guy No no...           */
/* system tables are stored in primary file group */
/* and with this setup so are the database tables */
/**************************************************/

CREATE DATABASE [CUSTOMER]
 CONTAINMENT = NONE
 ON  PRIMARY 
( 
	NAME = N'CUSTOMER_sales', 
	FILENAME = N'C:\SQL_DATA\CUSTOMER\CUSTOMER_sales.mdf' , -- this will store both system and database tables
	SIZE = 81920KB ,
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 65536KB 
)
 LOG ON 
( 
	NAME = N'CUSTOMER_sales_log', 
	FILENAME = N'C:\SQL_DATA\CUSTOMER\CUSTOMER_sales_log.ldf' , -- this is for database logs
	SIZE = 81920KB ,
	MAXSIZE = 2048GB , 
	FILEGROWTH = 65536KB 
)
GO

/*********************************/
/* Check out database properties */
/*********************************/

USE [master]
GO

DROP DATABASE IF EXISTS [CUSTOMER]
GO

CREATE DATABASE [CUSTOMER]
 CONTAINMENT = NONE
 ON  PRIMARY 
( 
	NAME = N'CUSTOMER_sales', 
	FILENAME = N'C:\SQL_DATA\CUSTOMER\CUSTOMER_SYSTEM_DATA.mdf' ,  -- this is for system tables
	SIZE = 41920KB ,
	 MAXSIZE = UNLIMITED, 
	 FILEGROWTH = 65536KB 
),
 FILEGROUP [CUSTOMER_DATA] 
( 
	NAME = N'PRODUCT_DATA1', 
	FILENAME = N'C:\SQL_DATA\INVENTORY\CUSTOMER_DATA1.ndf' , -- this is for database tables
	SIZE = 819200KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 65536KB 
),
( 
	NAME = N'PRODUCT_DATA2', 
	FILENAME = N'C:\SQL_DATA\INVENTORY\CUSTOMER_DATA2.ndf' , -- this is for database tables
	SIZE = 819200KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 65536KB 
)
 LOG ON 
( 
	NAME = N'CUSTOMER_sales_log', 
	FILENAME = N'C:\SQL_DATA\CUSTOMER\CUSTOMER_sales_log.ldf' , -- this is for database logs
	SIZE = 81920KB ,
	MAXSIZE = 2048GB , 
	FILEGROWTH = 65536KB 
)
GO

/*********************************/
/* Check out database properties */
/*********************************/


USE [master]
GO

ALTER DATABASE [INVENTORY]
SET SINGLE_USER;
GO

/*
ALTER DATABASE [INVENTORY]
SET MULTI_USER;
GO
*/

DROP DATABASE IF EXISTS [INVENTORY]
GO

CREATE DATABASE [INVENTORY]
 CONTAINMENT = NONE
 ON  PRIMARY 
( 
	NAME = N'INVENTORY', 
	FILENAME = N'C:\SQL_DATA\INVENTORY\INVENTORY.mdf' , 
	SIZE = 819200KB ,
	 MAXSIZE = UNLIMITED, 
	 FILEGROWTH = 65536KB 
), 
 FILEGROUP [INVENTORY_DATA] 
( 
	NAME = N'PRODUCT_DATA1', 
	FILENAME = N'C:\SQL_DATA\INVENTORY\PRODUCT_DATA1.ndf' , 
	SIZE = 819200KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 65536KB 
),
( 
	NAME = N'PRODUCT_DATA2', 
	FILENAME = N'C:\SQL_DATA\INVENTORY\PRODUCT_DATA2.ndf' , 
	SIZE = 819200KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 65536KB 
)
 LOG ON 
( 
	NAME = N'INVENTORY_log', 
	FILENAME = N'C:\SQL_DATA\INVENTORY\INVENTORY_log.ldf' , 
	SIZE = 81920KB , 
	MAXSIZE = 2048GB , 
	FILEGROWTH = 65536KB 
)
GO

/*********************************/
/* Check out database properties */
/*********************************/

/*************************************/
/* Let's add a file and a file group */
/*************************************/

USE master
GO

ALTER DATABASE INVENTORY
ADD FILEGROUP PRODUCT_DATA2
GO

ALTER DATABASE INVENTORY
ADD FILE
( 
	NAME = N'PRODUCT_DATA3', 
	FILENAME = N'C:\SQL_DATA\INVENTORY\PRODUCT_DATA3.ndf' , 
	SIZE = 819200KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 65536KB 
)
TO FILEGROUP PRODUCT_DATA2
GO

/*********************************/
/* Check out database properties */
/*********************************/

/*********************************/
/* SECTION 4.3 - Creating Tables */
/*********************************/

USE [CUSTOMER]
GO

/********************************/
/* NEVER, NEVER DROP dbo SCHEMA */
/********************************/

DROP TABLE IF EXISTS [CUSTOMER]
GO

DROP TABLE IF EXISTS [stage].[CUSTOMER]
GO

DROP TABLE IF EXISTS [base].[CUSTOMER]
GO

DROP SCHEMA IF EXISTS [stage]
GO

DROP SCHEMA IF EXISTS [base]
GO

CREATE TABLE [CUSTOMER] (
	CUST_KEY INTEGER IDENTITY NOT NULL,
	CUST_ID  VARCHAR(32) NOT NULL,
	CUST_NAME VARCHAR(64) NOT NULL
	)
GO

CREATE SCHEMA [stage]
GO

CREATE TABLE [stage].CUSTOMER (
	CUST_KEY INTEGER IDENTITY NOT NULL,
	CUST_ID  VARCHAR(32) NOT NULL,
	CUST_NAME VARCHAR(64) NOT NULL
	)
GO

CREATE SCHEMA [base]
GO

CREATE TABLE [base].CUSTOMER (
	CUST_KEY INTEGER IDENTITY NOT NULL,
	CUST_ID  VARCHAR(32) NOT NULL,
	CUST_NAME VARCHAR(64) NOT NULL
	)
GO

INSERT INTO [stage].CUSTOMER 
VALUES('C001','TEST')
GO

INSERT INTO [stage].CUSTOMER 
VALUES('C002','TEST')
GO

SELECT * FROM [stage].CUSTOMER 
GO

INSERT INTO [stage].CUSTOMER 
VALUES(3,'C002','TEST')
GO

SET IDENTITY_INSERT stage.CUSTOMER ON
GO

INSERT INTO [stage].CUSTOMER 
(
	CUST_KEY,
	CUST_ID,
	CUST_NAME
)
VALUES(3,'C002','TEST')
GO

SELECT * FROM [stage].CUSTOMER 
GO

SET IDENTITY_INSERT stage.CUSTOMER OFF
GO


/**************************/
/* Check out table folder */
/**************************/

DROP TABLE 
IF EXISTS [dbo].CUSTOMER
GO

DROP TABLE 
IF EXISTS [stage].CUSTOMER
GO

DROP TABLE 
IF EXISTS [base].CUSTOMER
GO

DROP SCHEMA 
IF EXISTS [stage] 
GO

DROP SCHEMA 
IF EXISTS [base] 
GO

DROP SCHEMA 
IF EXISTS [customer] 
GO

CREATE SCHEMA [customer]
GO

DROP TABLE 
IF EXISTS [customer].CUSTOMER
GO

CREATE TABLE [customer].CUSTOMER (
	CUST_KEY INTEGER IDENTITY NOT NULL,
	CUST_ID  VARCHAR(32) NOT NULL,
	CUST_NAME VARCHAR(64) NOT NULL
	)
GO

INSERT INTO [CUSTOMER].[customer].CUSTOMER 
VALUES('CUST_0001','ACME PLUMBING SUPPLY');
GO

INSERT INTO [customer].CUSTOMER VALUES('CUST_0002','XYZ HARDWARE');
GO

INSERT INTO [customer].CUSTOMER VALUES('CUST_0003','BEST ELECTRIC SUPPLY');
GO

INSERT INTO [customer].CUSTOMER VALUES('CUST_0004','FRIENDLY PAINT SUPPLY');
GO

INSERT INTO [customer].CUSTOMER VALUES('CUST_0005','HOME TILE CENTER');
GO

SELECT * FROM [customer].[CUSTOMER]
GO

TRUNCATE TABLE [customer].[CUSTOMER]
GO

INSERT INTO [customer].CUSTOMER VALUES
	('CUST_0001','ACME PLUMBING SUPPLY'),
	('CUST_0002','XYZ HARDWARE'),
	('CUST_0003','BEST ELECTRIC SUPPLY'),
	('CUST_0004','FRIENDLY PAINT SUPPLY'),
	('CUST_0005','HOME TILE CENTER')
	;

SELECT * FROM [customer].[CUSTOMER]
GO

DROP TABLE 
IF EXISTS [customer].CUSTOMER_ADDRESS
GO

CREATE TABLE [customer].CUSTOMER_ADDRESS (
	CUST_ADDR_KEY  INTEGER IDENTITY NOT NULL,
	CUST_ID  VARCHAR(32) NOT NULL,
	COUNTRY_NAME VARCHAR(64) NOT NULL,
	PROVINCE VARCHAR(64) NULL,
	STATE VARCHAR(64) NULL,
	CITY VARCHAR (64) NOT NULL,
	STREET_ADDRESS VARCHAR(128) NULL,
	POSTAL_CODE VARCHAR(8) NOT NULL)
GO

/********************/
/* INSERT SOME ROWS */
/********************/

TRUNCATE TABLE [customer].[CUSTOMER_ADDRESS]
GO

INSERT INTO  [customer].[CUSTOMER_ADDRESS] VALUES
	('CUST_0001','UNITED STATES',NULL,'NEW YORK','DOBBS FERRY','1 SOME STREET','11111'),
	('CUST_0002','UNITED STATES',NULL,'NEW YORK','ARDSLEY','2 ANOTHER STREET','2222'),
	('CUST_0003','UNITED STATES',NULL,'NEW YORK','HASTINGS ON HUDSON','3 ANY YET ANOTHER STREET','33333'),
	('CUST_0004','UNITED STATES',NULL,'NEW YORK','TARRYTOWN','4 YOU GUESSED IT, ANOTHER STREET','44444'),
	('CUST_0005','UNITED STATES',NULL,'NEW YORK','HARTSDALE','5 AN AVENUE','55555'),
	('CUST_0006','CANADA','QUEBEC',NULL,'MONTREAL','1 CANADA LANE','66666')
	;

SELECT * FROM [CUSTOMER].[customer].[CUSTOMER_ADDRESS]
GO

DROP TABLE 
IF EXISTS  [customer].CUSTOMER_ADDRESS
GO

CREATE TABLE  [customer].CUSTOMER_ADDRESS (
	CUST_ADDR_KEY  INTEGER IDENTITY NOT NULL,
	CUST_ID  VARCHAR(32) NOT NULL,
	COUNTRY_NAME VARCHAR(64) NOT NULL,
	PROVINCE VARCHAR(64) NOT NULL DEFAULT 'N/A',
	STATE VARCHAR(64) NOT NULL DEFAULT 'N/A',
	CITY VARCHAR (64) NOT NULL,
	STREET_ADDRESS VARCHAR(128) NULL,
	POSTAL_CODE VARCHAR(8) NOT NULL)
GO

/********************/
/* INSERT SOME ROWS */
/********************/

TRUNCATE TABLE [customer].[CUSTOMER_ADDRESS]
GO

INSERT INTO  [customer].[CUSTOMER_ADDRESS] VALUES
	('CUST_0001','UNITED STATES',DEFAULT,'NEW YORK','DOBBS FERRY','1 SOME STREET','11111'),
	('CUST_0002','UNITED STATES',DEFAULT,'NEW YORK','ARDSLEY','2 ANOTHER STREET','2222'),
	('CUST_0003','UNITED STATES',DEFAULT,'NEW YORK','HASTINGS ON HUDSON','3 ANY YET ANOTHER STREET','33333'),
	('CUST_0004','UNITED STATES',DEFAULT,'NEW YORK','TARRYTOWN','4 YOU GUESSED IT, ANOTHER STREET','44444'),
	('CUST_0005','UNITED STATES',DEFAULT,'NEW YORK','HARTSDALE','5 AN AVENUE','55555'),
	('CUST_0006','CANADA','QUEBEC',DEFAULT,'MONTREAL','1 CANADA LANE','66666')
	;

SELECT * FROM [CUSTOMER].[customer].[CUSTOMER_ADDRESS]
GO

/******************************/
/* SECTION 4.4 Creating viewS */
/******************************/

USE [CUSTOMER]
GO

/**************************************/
/* if a table exists in the territory */
/* schema it must be dropped before   */
/* we drop the schema...              */
/**************************************/

DROP TABLE IF EXISTS [territory].[store]
GO

DROP SCHEMA IF EXISTS [territory]
GO

CREATE SCHEMA [territory]
GO

CREATE TABLE [territory].[store](
	[storeKey] [int] IDENTITY(1,1) NOT NULL,
	[storeNo] [nvarchar](5) NOT NULL,
	[storeName] [nvarchar](64) NOT NULL,
	[storeCity] [nvarchar](64) NULL,
	[storeterritory] [nvarchar](64) NULL
) ON [PRIMARY]
GO

TRUNCATE TABLE [territory].[store]
GO

INSERT [territory].[store]
SELECT [storeNo]
      ,[storeName]
	  ,NULL AS [storeCity]
      ,[storeterritory]
FROM [sales_dw].[DIM].[store]
GO

SELECT * FROM [territory].[store]
GO

UPDATE [territory].[store]
SET storeCity = 'Torino'
WHERE [storeName] LIKE 'Modelismo Torino%'
GO

UPDATE [territory].[store]
SET storeCity = 'Geneva'
WHERE [storeName] = 'Modelismo Svizzera'
GO

UPDATE [territory].[store]
SET storeCity = 'Modane'
WHERE [storeName] = 'Modelismo Francia'
GO

UPDATE [territory].[store]
SET storeCity = 'Vienna'
WHERE [storeName] = 'Modelismo Austria'
GO

UPDATE [territory].[store]
SET storeCity = 'Munich'
WHERE [storeName] = 'Modelismo Germania'
GO

SELECT * FROM [territory].[store]
GO

DROP view IF EXISTS [territory].[store_view]
GO

--DROP view [territory].[store_view]
--GO

CREATE view [territory].[store_view]
AS
SELECT [storeKey]
      ,[storeNo]
      ,[storeName]
	  ,[storeCity]
      ,[storeterritory]
FROM  [territory].[store]
GO

SELECT * FROM [territory].[store_view]
GO

DROP view IF EXISTS [territory].[sales territory store view]
GO

CREATE view [territory].[sales territory store view]
AS
SELECT [storeKey]        AS [store Key]
      ,[storeNo]	     AS [store Number]
      ,[storeName]       AS [store Name]
      ,[storeCity]       AS [store City]
      ,[storeterritory]  AS [sales territory]
FROM  [territory].[store]
GO

SELECT * FROM [territory].[sales territory store view]
GO

DELETE FROM [territory].[store]
WHERE [storeKey] BETWEEN 6 AND 9
GO

SELECT *  FROM [territory].[store]
ORDER BY [storeKey] ASC
GO

INSERT INTO [territory].[store_view] VALUES
	('S0006','Modelismo Torino Via Po','Torino','Italia'),
	('S0007','Modelismo Torino Piazza Castello','Torino','Italia'),
	('S0008','Modelismo Torino Via Garibaldi','Torino','Italia'),
	('S0009','Modelismo Torino Piazza Solferino','Torino','Italia')
--	('S0010','Modelismo Torino Corso Re Umberto','Torino','Italia')
	;

SELECT *  FROM [territory].[store]
ORDER BY [storeKey] ASC
GO

/*************************************/
/* Notice gap in IDENTITY key vales! */
/*************************************/

SELECT *  FROM [territory].[store]
ORDER BY [storeKey] ASC
GO

SELECT *  FROM [territory].[store_view]
ORDER BY storeCity ASC
GO

/* here is a trick */

DELETE FROM [territory].[store]
WHERE [storeKey] BETWEEN 10 AND 14
GO

SET IDENTITY_INSERT [territory].[store] ON
GO

INSERT INTO [territory].[store] 
(
[storeKey], [storeNo], [storeName], [storeCity], [storeterritory]
)
VALUES
	(6,'S0006','Modelismo Moncalieri Via Po','Moncalieri','Italia'),
	(7,'S0007','Modelismo Moncalieri Piazza Castello','Moncalieri','Italia'),
	(8,'S0008','Modelismo Moncalieri Via Garibaldi','Moncalieri','Italia'),
	(9,'S0009','Modelismo Moncalieri Piazza Solferino','Moncalieri','Italia'),
	(10,'S0010','Modelismo Moncalieri Corso Re Umberto','Moncalieri','Italia')
	;

SET IDENTITY_INSERT[territory].[store] OFF
GO

/***************************/
/* Let's create some views */
/***************************/

DROP view IF EXISTS [territory].[italy_store_view]
GO

CREATE view [territory].[italy_store_view]
AS
SELECT [storeKey]
      ,[storeNo]
      ,[storeName]
	  ,[storeCity]
      ,[storeterritory]
FROM  [territory].[store]
WHERE [storeterritory] = 'Italia'
GO

SELECT * FROM [territory].[italy_store_view]
GO

DROP view IF EXISTS [territory].[france_store_view]
GO

CREATE view [territory].[france_store_view]
AS
SELECT [storeKey]
      ,[storeNo]
      ,[storeName]
	  ,[storeCity]
      ,[storeterritory]
FROM  [territory].[store]
WHERE [storeterritory] = 'Francia'
GO

SELECT * FROM [territory].[france_store_view]
GO

DROP view IF EXISTS [territory].[switzerland_store_view]
GO

CREATE view [territory].[switzerland_store_view]
AS
SELECT [storeKey]
      ,[storeNo]
      ,[storeName]
	  ,[storeCity]
      ,[storeterritory]
FROM  [territory].[store]
WHERE [storeterritory] = 'Svizzera'
GO

SELECT * FROM [territory].[switzerland_store_view]
GO

DROP view IF EXISTS [territory].[austria_store_view]
GO

CREATE view [territory].[austria_store_view]
AS
SELECT [storeKey]
      ,[storeNo]
      ,[storeName]
	  ,[storeCity]
      ,[storeterritory]
FROM  [territory].[store]
WHERE [storeterritory] = 'Italia'
GO

SELECT * FROM [territory].[austria_store_view]
GO

-- fix views

ALTER view [territory].[austria_store_view]
AS
SELECT [storeKey]
      ,[storeNo]
      ,[storeName]
	  ,[storeCity]
      ,[storeterritory]
FROM  [territory].[store]
WHERE [storeterritory] = 'Austria'
GO

SELECT * FROM [territory].[italy_store_view]
UNION ALL
SELECT * FROM [territory].[austria_store_view]
UNION ALL
SELECT * FROM [territory].[france_store_view]
UNION ALL
SELECT * FROM [territory].[switzerland_store_view]
ORDER BY 1
GO

/*****************/
/* Indexed viewS */
/*****************/

/*
3 steps:
1.) Set options to support Indexed views
2.) Create the Indexed view
3.) Create an index for the indexed view
*/

/***************/
/* set options */
/***************/

SET NUMERIC_ROUNDABORT OFF;  
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,  
    QUOTED_IDENTIFIER, ANSI_NULLS ON;  
GO 

/***************************/
/* Create the Indexed view */
/***************************/
USE [HOBBY_SHOP]
GO

DROP view IF EXISTS SALES_TRANSACTION_VIEW
GO

CREATE view SALES_TRANSACTION_VIEW
WITH SCHEMABINDING
AS
SELECT [TRANS_ID], 
	[TRANS_DATE], 
	[CUST_ID], 
	[CUST_NAME], 
	[PROD_ID], 
	[QUANTITY], 
	[WHOLE_SALE_PRICE]
  FROM [SALES].[TRANSACTIONS]
GO

/****************************************/
/* Create an INDEX for the Indexed view */
/****************************************/

/*
CREATE UNIQUE CLUSTERED INDEX PK_TRANS_DATE
ON [SALES].[TRANSACTIONS] ([TRANS_DATE]);  
GO
*/

CREATE CLUSTERED INDEX PK_TRANS_DATE
ON [SALES].[TRANSACTIONS] ([TRANS_DATE]);  
GO

SELECT [TRANS_ID]
      ,[TRANS_DATE]
      ,[CUST_ID]
      ,[CUST_NAME]
      ,[PROD_ID]
      ,[QUANTITY]
      ,[WHOLE_SALE_PRICE]
  FROM [dbo].[SALES_TRANSACTION_VIEW]
GO


/*****************************/
/* SECTION 4.5 ALTER Command */
/*****************************/

USE [CUSTOMER]
GO

/*****************************************************/
/* These need to be dropped before schema is dropped */
/*****************************************************/

DROP TABLE If EXISTS [CUS_INV].[CUSTOMER_INVOICE]
GO

DROP TABLE If EXISTS [CUS_INV].[INVOICE]
GO

DROP SCHEMA IF EXISTS [CUS_INV]
GO

CREATE SCHEMA [CUS_INV]
GO

CREATE TABLE [CUS_INV].[INVOICE]
(
[CUST_ID] VARCHAR(32) NOT NULL,
[PROD_ID] VARCHAR(32) NOT NULL,
[INV_AMT] MONEY NOT NULL
)
GO

INSERT INTO [CUS_INV].[INVOICE]
SELECT [CUST_ID],
	   'P000' + CONVERT(VARCHAR,[CUST_KEY]) AS [PROD_ID],
	   CASE [CUST_ID]
			WHEN 'CUST_0001'
				THEN CONVERT(decimal(10,2),10 * RAND(2)) 
			WHEN 'CUST_0002'
				THEN CONVERT(decimal(10,2),20 * RAND(4))
			WHEN 'CUST_0003'
				THEN CONVERT(decimal(10,2),15 * RAND(8))
			WHEN 'CUST_0004'
				THEN CONVERT(decimal(10,2),100 * RAND(7))
			WHEN 'CUST_0005'
				THEN CONVERT(decimal(10,2),75 * RAND(6))
		ELSE 47.50
		END AS [INV_AMT]

FROM [CUSTOMER].[CUSTOMER]
GO

SELECT * FROM [CUS_INV].[INVOICE]
GO

ALTER TABLE [CUS_INV].[INVOICE]
ADD INVOICE_DATE DATE NULL
GO

SELECT * FROM [CUS_INV].[INVOICE]
GO

UPDATE [CUS_INV].[INVOICE]
SET INVOICE_DATE = CONVERT(DATE,GETDATE())
GO

SELECT * FROM [CUS_INV].[INVOICE]
GO

/****************************/
/* Rename the invoice table */
/****************************/

/****************/
/* But first... */
/****************/

CREATE OR ALTER PROCEDURE usp_InvoiceReport
AS
SELECT * FROM [CUS_INV].[INVOICE]
GO

EXEC usp_InvoiceReport
GO

sp_depends 'CUS_INV.INVOICE'
GO

sp_depends 'dbo.usp_InvoiceReport'
GO

DROP PROCEDURE usp_InvoiceReport
GO

/***************************************/
/* OK, let's rename table and columns. */
/***************************************/

USE [CUSTOMER]
GO

CREATE OR ALTER view [meta_data].[usp_CustomerMetaData]
AS
select 
	A.name AS [Table name],
	B.name AS [Column Name]
from sys.objects A JOIN
sys.columns B 
ON A.object_id = B.object_id
where A.type = 'U'
--AND A.name = 'Customer_Invoice'
--AND A.name = 'INVOICE'
GO

select 
	A.NAME AS [Table name],
	B.name AS [Column Name]
from sys.objects A JOIN
sys.columns B 
ON A.object_id = B.object_id
where A.type = 'U'
--AND A.name = 'Customer_Invoice'
AND A.name = 'INVOICE'
GO

select * from sys.objects
where type = 'U'
GO

select * from sys.columns
GO


select * from sys.columns
where object_id in (
select 
	A.object_id
from sys.objects A JOIN
sys.columns B 
ON A.object_id = B.object_id
where A.type = 'U'
)
GO




USE [CUSTOMER]
GO

DROP TABLE IF EXISTS [CUS_INV].[INVOICE_BU]
GO

SELECT [CUST_ID]
      ,[PROD_ID]
      ,[INV_AMT]
      ,[INVOICE_DATE]
INTO [CUS_INV].[INVOICE_BU]
FROM [CUS_INV].[INVOICE]
GO

EXEC sp_rename 
	@objname = '[CUS_INV].[INVOICE]',
	@newname = 'Customer_Invoice'
GO

/*****************************/
/* Rename the CUST_ID column */
/*****************************/

EXEC sp_rename 
	@objname = 'CUS_INV.Customer_Invoice.CUST_ID',
	@newname = 'Customer_ID',
	@objtype = 'COLUMN'
GO

/*****************************/
/* Rename the PROD_ID column */
/*****************************/

EXEC sp_rename 
	@objname = 'CUS_INV.Customer_Invoice.PROD_ID',
	@newname = 'Product ID',
	@objtype = 'COLUMN'
GO

/*****************************/
/* Rename the INV_AMT column */
/*****************************/

EXEC sp_rename 
	@objname = 'CUS_INV.Customer_Invoice.INV_AMT',
	@newname = 'Invoice Amount',
	@objtype = 'COLUMN'
GO

/*****************************/
/* Rename the INV_AMT column */
/*****************************/

EXEC sp_rename 
	@objname = 'CUS_INV.Customer_Invoice.INVOICE_DATE',
	@newname = 'Invoice Date',
	@objtype = 'COLUMN'
GO

SELECT * FROM [CUS_INV].[CUSTOMER_INVOICE]
GO


/********************************/
/* SECTION 4.6 Creating Indexes */
/********************************/

USE [sales_dw]
GO 

/*******************************************************************/
/* Let's do a little exploration of the data warehouse we will use */
/*******************************************************************/

/***********/
/* 1296000 */
/***********/

SELECT count(*)
  FROM [sales_dw].[fact].[sales] WITH (NOLOCK)
  GO

SELECT count(*)
  FROM [fact].[sales] WITH (NOLOCK)
  GO

/*
SELECT count(*)
  FROM [sales] WITH (NOLOCK)
  GO
*/

/******************/
/* Related tables */
/******************/

SELECT TOP (1000) 
	[CustomerKey], 
	[ProductKey], 
	[CountryKey], 
	[StoreKey], 
	[CalendarKey], 
	[TransactionQuantity],
	[transactionAmount], 
	[salesTaxAmount], 
	[TotalSalesAmount]
FROM [sales_dw].[fact].[sales]
GO

SELECT [calendar_key]
      ,[calendar_date]
      ,[calendar_date_time]
      ,[calendar_year]
      ,[calendar_quarter]
      ,[calendar_quarter_abbrev]
      ,[calendar_quarter_text]
      ,[calendar_month]
      ,[calendar_month_name]
      ,[calendar_month_abbrev]
      ,[day_of_year]
      ,[month_day]
      ,[week]
      ,[week_day]
      ,[week_end_flag]
      ,[week_day_name]
      ,[week_day_abbrev]
  FROM [sales_dw].[DIM].[Calendar]
GO

SELECT distinct [CalendarKey]
FROM [sales_dw].[fact].[sales]
ORDER BY 1
GO

SELECT [CustomerKey]
      ,[ProductKey]
      ,[ProductKey]
      ,[CalendarKey]
      ,[transactionAmount]
      ,[salesTaxAmount]
  FROM [sales_dw].[fact].[sales]
  WHERE [CalendarKey] = '20101025'
  GO

/********************************************/
/* create some indexes with object explorer */
/********************************************/

/************************/
/* Free procedure cache */
/************************/

DBCC FREEPROCCACHE 
GO

/********************************/
/* Create a non-clustered index */
/********************************/

DROP INDEX  
	IF EXISTS [fact].[sales].[aktransactionDate]
GO

CREATE NONCLUSTERED INDEX [aktransactionDate] ON [fact].[sales]
(
	[CalendarKey] ASC
)
GO

SELECT [CustomerKey]
      ,[ProductKey]
      ,[ProductKey]
      ,[CalendarKey]
      ,[transactionAmount]
      ,[salesTaxAmount]
  FROM [sales_dw].[fact].[sales]
  WHERE [CalendarKey] = '20101025'
  AND [ProductKey] = 15
  GO

DROP INDEX  
	IF EXISTS [fact].[sales].[akProductTransactionDate]
GO

CREATE NONCLUSTERED INDEX [akProductTransactionDate] ON [fact].[sales]
(
	[ProductKey],[CalendarKey] ASC
)
GO

USE [sales_dw]
GO

/**********************************/
/* Check query plan and run query */
/**********************************/

DROP INDEX  
IF EXISTS [fact].[sales].[aktransactionDate]
GO

DBCC FREEPROCCACHE 
GO

SELECT [CustomerKey]
      ,[ProductKey]
      ,[ProductKey]
      ,[CalendarKey]
      ,[transactionAmount]
      ,[salesTaxAmount]
  FROM [sales_dw].[fact].[sales]
  WHERE [CalendarKey] = '20101025'
  ORDER BY [CustomerKey]
  GO

/************************/
/* Free procedure cache */
/************************/

DBCC FREEPROCCACHE 
GO

/****************************/
/* Create a clustered index */
/****************************/

DROP INDEX  
IF EXISTS [fact].[sales].[aktransactionDate]
GO


CREATE CLUSTERED INDEX [aktransactionDate] ON [fact].[sales]
(
	[CalendarKey] ASC
)
GO

/**********************************/
/* Check query plan and run query */
/**********************************/

SELECT [CustomerKey]
      ,[ProductKey]
      ,[ProductKey]
      ,[CalendarKey]
      ,[transactionAmount]
      ,[salesTaxAmount]
  FROM [sales_dw].[fact].[sales]
  WHERE [CalendarKey] = '20101025'
  GO


/******************************/
/* Generate some history data */
/******************************/

TRUNCATE TABLE [fact].[SalesHistory]
GO

INSERT INTO [fact].[SalesHistory]
SELECT [CustomerKey]
      ,[ProductKey]
      ,[CalendarKey]
	  ,[TransactionQuantity]
      ,[transactionAmount]
      ,[salesTaxAmount]
FROM [sales_dw].[fact].[sales]
GO

DROP INDEX IF EXISTS [DIM].[Calendar].[akCalendarDate]
GO

/************************/
/* Free procedure cache */
/************************/

DBCC FREEPROCCACHE 
GO

/**********************************/
/* Check query plan and run query */
/**********************************/

DROP INDEX IF EXISTS [DIM].[Calendar].[pkCalendarDate]
GO

SELECT CAL.[calendar_date],S.[TransactionAmount],S.[SalesTaxAmount]
FROM [DIM].[Calendar] CAL
JOIN
[fact].[SalesHistory]  S
ON CAL.calendar_key = S.CalendarKey
WHERE CAL.[calendar_date] = '2010-10-25'
GO

CREATE INDEX [pkCalendarDate] ON [DIM].[Calendar]
(
	[calendar_date]
)
GO

/**********************************/
/* Check query plan and run query */
/**********************************/

/************************/
/* Free procedure cache */
/************************/

DBCC FREEPROCCACHE 
GO

SELECT CAL.[calendar_date],S.[transactionAmount],S.[salesTaxAmount]
FROM [DIM].[Calendar] CAL
JOIN
[fact].[salesHistory]  S
ON CAL.calendar_key = S.CalendarKey
WHERE CAL.[calendar_date] = '2010-10-25'
GO

DROP INDEX IF EXISTS [DIM].[Calendar].[akCalendarDate]
GO

DROP INDEX IF EXISTS [DIM].[Calendar].[akCalendarDate]
GO

DROP INDEX IF EXISTS [FACT].[salesHistory].[aktransactionDate]
GO


CREATE CLUSTERED INDEX [akCalendarDate] ON [DIM].[Calendar]
(
	[calendar_date]
)
GO

DROP INDEX IF EXISTS [FACT].[salesHistory].[pktransactionDate]
GO

CREATE CLUSTERED INDEX [pktransactionDate] ON [fact].[salesHistory]
(
	[CalendarKey] ASC
)
GO

/************************/
/* Free procedure cache */
/************************/

DBCC FREEPROCCACHE 
GO

/**********************************/
/* Check query plan and run query */
/**********************************/

SELECT CAL.[calendar_date],S.[transactionAmount],S.[salesTaxAmount]
FROM [DIM].[Calendar] CAL
JOIN
[fact].[salesHistory]  S
ON CAL.calendar_key = S.CalendarKey
WHERE CAL.[calendar_date] = '2010-10-25'
GO

DROP INDEX IF EXISTS [FACT].[salesHistory].[pksalesHistoryKey]
GO

CREATE INDEX [pksalesHistoryKey] ON [fact].[salesHistory]
(
	[CustomerKey],[ProductKey],[CalendarKey]
)
GO

/************************/
/* Free procedure cache */
/************************/

DBCC FREEPROCCACHE 
GO

SELECT CAL.[calendar_date],S.[transactionAmount],S.[salesTaxAmount]
FROM [DIM].[Calendar] CAL
JOIN
[fact].[salesHistory]  S
ON CAL.calendar_key = S.CalendarKey
WHERE CAL.[calendar_date] = '2010-10-25'
GO

SELECT CAL.[calendar_date],S.[ProductKey],S.[transactionAmount],S.[salesTaxAmount]
FROM [DIM].[Calendar] CAL
JOIN
[fact].[salesHistory]  S
ON CAL.calendar_key = S.CalendarKey
WHERE CAL.[Calendar_Key] = '20101025'
AND s.[ProductKey] = 30
GO

SELECT CAL.[calendar_date],P.[ProductName],S.[transactionAmount],S.[salesTaxAmount]
FROM [DIM].[Calendar] CAL
JOIN
[fact].[salesHistory]  S
ON CAL.calendar_key = S.CalendarKey
JOIN [DIM].[Product] P
ON S.ProductKey = P.ProductKey
WHERE CAL.[Calendar_Key] = '20101025'
AND P.[ProductKey] = 30
GO

/*******************************************/
/* This cannot use index on Calendar table */
/*******************************************/

SELECT CAL.[calendar_date],S.[transactionAmount],S.[salesTaxAmount]
FROM [DIM].[Calendar] CAL
JOIN
[fact].[salesHistory]  S
ON CAL.calendar_key = S.CalendarKey
WHERE CAL.[calendar_date] BETWEEN '2010-10-25' AND '2011-10-25'
GO

/**************************/
/* SECTION 4.7 QUERY PLAN */
/**************************/

USE [SALES_DW]
GO

DBCC FREEPROCCACHE 
GO

SELECT CAL.calendar_year			AS [Year]
	  ,CAL.calendar_quarter_abbrev	AS [Quarter]
	  ,CAL.calendar_month_abbrev	AS [Month]
	  ,CAL.calendar_date			AS [Invoice Date]
	  ,C.CustomerFullName			AS [Customer]
	  ,P.[ProductCategory]			AS [Category]
	  ,P.ProductSubCategory			AS [Sub Category]
	  ,P.ProductName				AS [Product]
      ,S.[TransactionAmount]		AS [Sales Amount]
      ,S.[SalesTaxAmount]			AS [Sales Tax]
	  ,S.[TransactionQuantity]		AS [Quantity]
	  ,(S.[TransactionQuantity] * S.[TransactionAmount])  + S.[SalesTaxAmount] AS [Total Amount]
	  ,S.[TotalSalesAmount]			AS [Check Total Amount]
  FROM [fact].[Sales] S 
  JOIN [DIM].[Customer] C
  ON S.CustomerKey = C.CustomerKey
  JOIN [DIM].[Product] P
  ON S.ProductKey = P.ProductKey
  JOIN [DIM].[Calendar] CAL
  ON S.CalendarKey = CAL.calendar_key
  GO

DBCC FREEPROCCACHE 
GO

  SELECT CAL.calendar_year			AS [Year]
	  ,CAL.calendar_quarter_abbrev	AS [Quarter]
	  ,CAL.calendar_month_abbrev	AS [Month]
	  ,CAL.calendar_date			AS [Invoice Date]
	  ,C.CustomerFullName			AS [Customer]
	  ,P.[ProductCategory]			AS [Category]
	  ,P.ProductSubCategory			AS [Sub Category]
	  ,P.ProductName				AS [Product]
      ,S.[TransactionAmount]		AS [Sales Amount]
      ,S.[SalesTaxAmount]			AS [Sales Tax]
	  ,S.[TransactionQuantity]		AS [Quantity]
	  ,(S.[TransactionQuantity] * S.[TransactionAmount])  + S.[SalesTaxAmount] AS [Total Amount]
	  ,S.[TotalSalesAmount]			AS [Check Total Amount]
  FROM [fact].[Sales] S 
  JOIN [DIM].[Customer] C
  ON S.CustomerKey = C.CustomerKey
  JOIN [DIM].[Product] P
  ON S.ProductKey = P.ProductKey
  JOIN [DIM].[Calendar] CAL
  ON S.CalendarKey = CAL.calendar_key
  WHERE CAL.calendar_date = '2019-01-11'
  GO

-- 16 seconds 

DBCC FREEPROCCACHE 
GO

  SELECT CAL.calendar_year			AS [Year]
	  ,CAL.calendar_quarter_abbrev	AS [Quarter]
	  ,CAL.calendar_month_abbrev	AS [Month]
	  ,CAL.calendar_date			AS [Invoice Date]
	  ,C.CustomerFullName			AS [Customer]
	  ,P.[ProductCategory]			AS [Category]
	  ,P.ProductSubCategory			AS [Sub Category]
	  ,P.ProductName				AS [Product]
      ,S.[TransactionAmount]		AS [Sales Amount]
      ,S.[SalesTaxAmount]			AS [Sales Tax]
	  ,S.[TransactionQuantity]		AS [Quantity]
	  ,(S.[TransactionQuantity] * S.[TransactionAmount])  + S.[SalesTaxAmount] AS [Total Amount]
	  ,S.[TotalSalesAmount]			AS [Check Total Amount]
  FROM [fact].[Sales] S 
  JOIN [DIM].[Customer] C
  ON S.CustomerKey = C.CustomerKey
  JOIN [DIM].[Product] P
  ON S.ProductKey = P.ProductKey
  JOIN [DIM].[Calendar] CAL
  ON S.CalendarKey = CAL.calendar_key
  WHERE CAL.calendar_date BETWEEN '2018-01-11' AND '2019-01-11'
  GO

/*********************************/
/* BEGINNING TSQL MODULE 4 - END */
/*********************************/