CREATE DATABASE [CUSTOMER_SALES]
 CONTAINMENT = NONE
 ON  PRIMARY 
( 
	NAME = N'CUSTOMER_SALES', 
	FILENAME = N'D:\SQL2019DATAI1\CUSTOMER_SALES\CUSTOMER_SALES.mdf' , 
	SIZE = 500MB ,
	 MAXSIZE = 1GB, 
	 FILEGROWTH = 100MB 
),
 FILEGROUP [CUSTOMER_SALES_DATA_FG] 
( 
	NAME = N'CUSTOMER_SALES_DATA1', 
	FILENAME = N'D:\SQL2019DATAI1\CUSTOMER_SALES\CUSTOMER_SALES_DATA.ndf' , 
	SIZE = 2000MB, 
	MAXSIZE = 10000MB, 
	FILEGROWTH = 500MB 
)
 LOG ON 
( 
	NAME = N'CUSTOMER_SALES_log', 
	FILENAME = N'D:\SQL2019DATAI1\CUSTOMER_SALES\CUSTOMER_SALES_log.ldf' , 
	SIZE = 1GB,
	MAXSIZE = 5GB, 
	FILEGROWTH = 1GB 
)
GO

/*******************/
/* Creating Tables */
/*******************/
USE  [CUSTOMER]
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

DROP TABLE [dbo].CUSTOMER
GO

DROP TABLE [stage].CUSTOMER
GO

DROP SCHEMA [stage] 
GO

CREATE SCHEMA [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 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

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

/********************************************************/
/* BEGINNING TSQL MODULE 1 - SECTION 3.4 Creating VIEWS */
/********************************************************/

USE  [CUSTOMER]
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].[DIM].[Store]
GO

SELECT * FROM [TERRITORY].[Store]
GO

UPDATE [TERRITORY].[Store]
SET StoreCity = 'Torino'
WHERE [StoreName] = '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

CREATE VIEW [TERRITORY].[STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
GO

CREATE VIEW [TERRITORY].[SALES TERRITORY STORES]
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].[STORES]
GO

SELECT * FROM [TERRITORY].[SALES TERRITORY STORES]
GO

INSERT INTO [TERRITORY].[STORES] 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].[STORES]
ORDER BY StoreCity ASC
GO


CREATE VIEW [TERRITORY].[ITALY_STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Italia'
GO

CREATE VIEW [TERRITORY].[FRANCIA_STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Francia'
GO
CREATE VIEW [TERRITORY].[SVIZZERA_STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Svizzera'
GO

CREATE VIEW [TERRITORY].[AUSTRIA_STORES2]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Italia'
GO

-- fix views

ALTER VIEW [TERRITORY].[ITALY_STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Italia'
GO

SELECT * FROM [TERRITORY].[ITALY_STORES]
GO

SELECT * FROM [TERRITORY].[AUSTRIA_STORES]
GO

SELECT * FROM [TERRITORY].[FRANCIA_STORES]
GO

SELECT * FROM [TERRITORY].[SVIZZERA_STORES]
GO



ALTER VIEW [TERRITORY].[FRANCIA_STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Francia'
GO

ALTER VIEW [TERRITORY].[SVIZZERA_STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Svizzera'
GO

ALTER VIEW [TERRITORY].[AUSTRIA_STORES]
AS
SELECT [StoreKey]
      ,[StoreNo]
      ,[StoreName]
	  ,[StoreCity]
      ,[StoreTerritory]
FROM  [TERRITORY].[Store]
WHERE [StoreTerritory] = 'Austria'
GO

SELECT * FROM [TERRITORY].[ITALY_STORES]
GO

SELECT * FROM [TERRITORY].[AUSTRIA_STORES]
GO

SELECT * FROM [TERRITORY].[FRANCIA_STORES]
GO

SELECT * FROM [TERRITORY].[SVIZZERA_STORES]
GO