/************/
/* PRE_WORK */
/************/

USE MASTER
GO

/*

SP_WHO2
GO

ALTER DATABASE HUMAN_RESOURCES
SET SINGLE_USER
GO

DROP DATABASE IF EXISTS [HUMAN_RESOURCES]
GO

*/

/****************************/
/* HUMAN RESOURCES DATABASE */
/****************************/

CREATE DATABASE [HUMAN_RESOURCES]
CONTAINMENT = NONE
ON  PRIMARY 
( 
	NAME = N'HUMAN_RESOURCES', 
	FILENAME = N'D:\SQL2019DATAI2\HUMAN_RESOURCES\HUMAN_RESOURCES.MDF' , 
	SIZE = 520KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 64KB
),
 FILEGROUP [HUMAN_RESOURCES_DATA] 
( 
	NAME = N'HUMAN_RESOURCES_DATA', 
	FILENAME = N'D:\SQL2019DATAI2\HUMAN_RESOURCES\HUMAN_RESOURCES_DATA.NDF' , 
	SIZE = 1000KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 64KB
)
LOG ON 
( 
NAME = N'HUMAN_RESOURCES_LOG', 
FILENAME = N'D:\SQL2019DATAI2\HUMAN_RESOURCES\HUMAN_RESOURCES_LOG.LDF' , 
SIZE = 520KB , 
MAXSIZE = 2000MB , 
FILEGROWTH = 64KB
)
GO

/*

SP_WHO2
GO

ALTER DATABASE CUSTOMER
SET SINGLE_USER
GO

DROP DATABASE IF EXISTS [CUSTOMER]
GO

*/

USE [master]
GO

CREATE DATABASE [CUSTOMER]
CONTAINMENT = NONE
ON  PRIMARY 
( 
	NAME = N'CUSTOMER', 
	FILENAME = N'D:\SQL2019DATAI2\CUSTOMER\CUSTOMER.MDF' , 
	SIZE = 520KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 64KB
),
 FILEGROUP [CUSTOMER_DATA] 
( 
	NAME = N'CUSTOMER_DATA', 
	FILENAME = N'D:\SQL2019DATAI2\CUSTOMER\CUSTOMER_DATA.NDF' , 
	SIZE = 1000KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 64KB
)
LOG ON 
( 
NAME = N'CUSTOMER_LOG', 
FILENAME = N'D:\SQL2019DATAI2\CUSTOMER\CUSTOMER_LOG.LDF' , 
SIZE = 520KB , 
MAXSIZE = 2000MB , 
FILEGROWTH = 64KB
)
GO

/*************************************/
/* TOPIC 01 - CREATE TABLE WITH SSMS */
/*************************************/

USE [CUSTOMER]
GO

DROP SCHEMA IF EXISTS [MASTER_DATA]
GO

CREATE SCHEMA [MASTER_DATA]
GO

/************************/
/* Table Name: CUSTOMER */
/************************/

/***************************************************************************************/
/* Column Logical Name       Physical Column Name  Data Type  Lenght   NULL Indicator  */
/*                                                                                     */
/* Customer identifier       CUST_ID               NVARCHAR   32       NOT NULL        */
/* Customer Last Name        CUST_LAST_NAME        NVARHCAR   32       NULL            */
/* Customer First Name       CUST_FIRST_NAME       NVARHCAR   32       NULL            */
/***************************************************************************************/

/************************************/
/* Table Name: CUSTOMER_DEMOGRAPHIC */
/************************************/

/***************************************************************************************/
/* Column Logical Name       Physical Column Name  Data Type  Lenght   NULL Indicator  */
/*                                                                                     */
/* Customer identifier       CUST_ID               NVARCHAR   32       NOT NULL        */
/* Customer Credit Rating    CUST_CREDIT_RATING    SMALLINT            NULL            */
/* Customer Marital Status   CUST_MARITAL_STATUS   NVARCHAR   50       NULL            */
/***************************************************************************************/

USE [CUSTOMER]
GO

DROP SCHEMA IF EXISTS MASTER_DATA
GO

CREATE SCHEMA MASTER_DATA
GO

/******************************************************************/
/* Change schema by Table Name->Design - (right click) properties */
/******************************************************************/

/*********************************/
/* Load a few rows in each table */
/*********************************/

/****************************/
/* POPULATE TABLE WITH SSMS */
/****************************/

SELECT * FROM [MASTER_DATA].[CUSTOMER]
GO

SELECT * FROM [MASTER_DATA].[CUSTOMER_DEMOGRAPHIC]
GO

/*******************************************/
/* END - TOPIC 01 - CREATE TABLE WITH SSMS */
/*******************************************/

/******************************/
/* TOPIC 02 - CREATING TABLES */
/******************************/

USE [HUMAN_RESOURCES]
GO

DROP SCHEMA IF EXISTS HR
GO

CREATE SCHEMA HR
GO

/**************/
/* DEPARTMENT */
/**************/

CREATE TABLE HR.DEPARTMENT (
	DEPT_ID   VARCHAR(16)  PRIMARY KEY NOT NULL,
	DEPT_NAME VARCHAR(256)             NOT NULL,
	MGR_ID    VARCHAR(16)              NOT NULL
	) ON HUMAN_RESOURCES_DATA;
GO

/************/
/* EMPLOYEE */
/************/

CREATE TABLE HR.EMPLOYEE (
	EMP_ID        VARCHAR(16) PRIMARY KEY  NOT NULL,
	DEPT_ID       VARCHAR(16)              NOT NULL,
	EMP_FNAME     VARCHAR(256)             NOT NULL,
	EMP_LNAME     VARCHAR(256)             NOT NULL,
	EMP_LONG_NAME VARCHAR(256)             NOT NULL,
	MGR_ID        VARCHAR(16)              NOT NULL,
	HIRE_DATE     DATE,
	END_DATE      DATE,

	CONSTRAINT FK_DEPT  FOREIGN KEY (DEPT_ID)
		REFERENCES HR.DEPARTMENT (DEPT_ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE

	) ON HUMAN_RESOURCES_DATA;
GO

DROP SCHEMA IF EXISTS PAYROLL
GO

CREATE SCHEMA PAYROLL
GO

CREATE TABLE PAYROLL.SALARY (
	EMP_ID VARCHAR(16) NOT NULL,
	SALARY DECIMAL(10,2) NOT NULL DEFAULT 0.0,
	FROM_DATE DATE NOT NULL,
	TO_DATE DATE NULL,

	/**********************/
	/* define primary key */
	/**********************/

	CONSTRAINT PK_EMP_SALARY
        PRIMARY KEY (EMP_ID,FROM_DATE)
        WITH (IGNORE_DUP_KEY = OFF),

	/****************************************/
	/* define foreign key: link to EMPLOYEE */
	/****************************************/
		
	CONSTRAINT FK_SALARY_EMP  FOREIGN KEY (EMP_ID)
		REFERENCES HR.EMPLOYEE (EMP_ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
	) ON HUMAN_RESOURCES_DATA;
GO

/**********************************/
/* END TOPIC 02 - CREATING TABLES */
/**********************************/

/********************************/
/* TOPIC 03 - POPULATING TABLES */
/********************************/

USE [HUMAN_RESOURCES]
GO

DELETE FROM [HR].[DEPARTMENT]
GO

TRUNCATE TABLE [HR].[DEPARTMENT]
GO

INSERT INTO [HR].[DEPARTMENT]
VALUES
	('D001','HUMAN RESOURCE','E001'),
	('D002','PAYROLL','E005'),
	('D003','MARKETING','E009'),	
	('D004','INVENTORY','E013');
GO

SELECT [DEPT_ID]
      ,[DEPT_NAME]
      ,[MGR_ID]
  FROM [HR].[DEPARTMENT]
GO

-- load third

INSERT INTO [HR].[EMPLOYEE]
VALUES
	('E001','D001','JOHN','SMITH','JOHN SMITH','E001','2020-10-01',NULL),
	('E002','D002','MARY','SMITH','MARY SMITH','E005','2020-10-02',NULL),
	('E003','D003','SUSAN','GREEN','SUSAN GREEN','E009','2010-10-01',NULL),	
	('E004','D004','JOHN','MARONE','JOHN MARONE','E013','2011-09-01',NULL),

	('E005','D001','TOM','DARBY','TOM DARBY','E001','2015-11-01',NULL),
	('E006','D002','MARY','YORK','MARY YORK','E001','2017-10-20',NULL),
	('E007','D003','DEBBIE','MCDONALD','DEBBIE MCDONALD','E001','2007-08-01',NULL),	
	('E008','D004','GIOVANNI','BERTONE','GIOVANNI BERTONE','E001','2011-02-01',NULL),

	('E009','D001','LARRY','JONES','LARRY JONES','E001','2019-10-01',NULL),
	('E010','D002','MIKE','MCGEE','MIKE MCGEE','E001','2018-03-01',NULL),
	('E011','D003','BORIS','GOLD','BORIS GOLD','E001','2016-09-01',NULL),	
	('E012','D004','BERTHA','SMITH','BERTHA SMITH','E001','2009-10-28',NULL),

	('E013','D001','DONALD','DILBY','DONALD DILBY','E001','2009-10-01',NULL),
	('E014','D002','JAKE','DORITO','JAKE DORITO','E001','2009-11-28',NULL),
	('E015','D003','THELMA','PINSKY','THELMA PINSKY','E001','2010-12-01',NULL),	
	('E016','D004','SUSAN','SORRISO','SUSAN SORRISO','E001','2017-12-01',NULL);
GO

INSERT INTO [HR].[EMPLOYEE]
VALUES
	('E099','D004','JOHN','SMITH','JOHN SMITH','E001','2020-10-01',NULL);
GO

SELECT [EMP_ID]
      ,[DEPT_ID]
      ,[EMP_FNAME]
      ,[EMP_LNAME]
      ,[EMP_LONG_NAME]
      ,[MGR_ID]
      ,[HIRE_DATE]
      ,[END_DATE]
  FROM [HR].[EMPLOYEE]
GO

TRUNCATE TABLE [PAYROLL].[SALARY]
GO

INSERT INTO [PAYROLL].[SALARY]
SELECT [EMP_ID]
      ,ROUND((RAND() * 100000),2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E001'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E002'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E003'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E004'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E005'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E006'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E007'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E008'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E009'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E010'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E011'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E012'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E013'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E014'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E015'
UNION ALL
SELECT [EMP_ID]
      ,ROUND(RAND() * 100000,2) AS [EMP_SALARY],HIRE_DATE,NULL
FROM [HR].[EMPLOYEE]
WHERE EMP_ID = 'E016'
GO

SELECT [EMP_ID]
      ,[SALARY]
      ,[FROM_DATE]
      ,[TO_DATE]
  FROM [PAYROLL].[SALARY]
GO


UPDATE [PAYROLL].[SALARY]
SET [TO_DATE] = '2010-10-01'
WHERE EMP_ID = 'E012'
AND [FROM_DATE] = '2009-10-28'
GO

INSERT INTO [PAYROLL].[SALARY]
VALUES('E012',75000.00,'2010-10-02',NULL)
GO

SELECT [EMP_ID]
      ,[SALARY]
      ,[FROM_DATE]
      ,[TO_DATE]
  FROM [PAYROLL].[SALARY]
WHERE EMP_ID = 'E012'
GO

/************************************/
/* END TOPIC 03 - POPULATING TABLES */
/************************************/

/************************************/
/* TOPIC 04 - DROP TABLES WITH SSMS */
/************************************/

/****************************************/
/* END TOPIC 04 - DROP TABLES WITH SSMS */
/****************************************/

-- drop table [PAYROLL].[SALARY] which is the last
-- in the constraint chain
-- but back it up first!

SELECT *
INTO [PAYROLL].[SALARY_BU] 
FROM [PAYROLL].[SALARY]
GO

/************************************/
/* TOPIC 05 - DROP TABLES WITH TSQL */
/************************************/

USE [HUMAN_RESOURCES]
GO

/*******************************/
/* EXECUTE DROPS IN THIS ORDER */
/*******************************/

DROP VIEW IF EXISTS SALARY_BU_VIEW
GO

CREATE VIEW PAYROLL.SALARY_BU_VIEW
AS
SELECT EMP_ID, SALARY, FROM_DATE, TO_DATE
FROM [PAYROLL].[SALARY_BU]
GO

sp_depends 'PAYROLL.SALARY_BU'
GO

sp_depends 'PAYROLL.SALARY_BU_VIEW'
GO


/*

DROP TABLE [PAYROLL].[SALARY]
GO

DROP TABLE [PAYROLL].[EMPLOYEE]
GO

DROP TABLE [HR].[DEPARTMENT]
GO
*/


DROP TABLE IF EXISTS [PAYROLL].[SALARY]
GO

DROP TABLE IF EXISTS [HR].[EMPLOYEE]
GO

DROP TABLE IF EXISTS [HR].[DEPARTMENT]
GO

DROP SCHEMA HR
GO

DROP SCHEMA PAYROLL
GO


/****************************************/
/* END TOPIC 05 - DROP TABLES WITH TSQL */
/****************************************/