USE [ORGANIZATION]
GO

CREATE SCHEMA MASTER_DATA
GO

CREATE TABLE [MASTER_DATA].[DEPARTMENT](
	[DEPT_ID] [varchar](16) NOT NULL,
	[DEPT_NAME] [varchar](256) NOT NULL,
	[MGR_ID] [varchar](16) NOT NULL,
) ON [ORGANIZATION_DATA]
GO

INSERT INTO MASTER_DATA.DEPARTMENT VALUES
	('D001','HUMAN RESOURCE','E004'),
	('D002','PAYROLL','E008'),
	('D003','MARKETING','E012'),	
	('D004','INVENTORY','E015'),
	('D005','MARKETING','E018');
GO

CREATE TABLE [MASTER_DATA].[EMPLOYEE](
	[DEPT_ID] [varchar](16) NOT NULL,
	[EMP_ID] [varchar](16) NOT NULL,
	[EMP_FNAME] [varchar](256) NOT NULL,
	[EMP_LNAME] [varchar](256) NOT NULL,
	) ON [ORGANIZATION_DATA]
GO

INSERT INTO MASTER_DATA.EMPLOYEE VALUES
	('D001','E001','JOHN','SMITH'),
	('D001','E002','MARY','SMITH'),
	('D001','E003','SUSAN','GREEN'),	
	('D001','E004','JOHN','MARONE'),

	('D002','E005','TOM','DARBY'),
	('D002','E006','MARY','YORK'),
	('D002','E007','DEBBIE','MCDONALD'),	
	('D002','E008','GIOVANNI','BERTONE'),

	('D003','E009','LARRY','JONES'),
	('D003','E010','MIKE','MCGEE'),
	('D003','E011','BORIS','GOLD'),	
	('D003','E012','BERTHA','SMITH'),

	('D004','E013','DONALD','DILBY'),
	('D004','E014','JAKE','DORITO'),
	('D004','E015','THELMA','PINSKY');	
GO

INSERT INTO MASTER_DATA.EMPLOYEE VALUES
	('D005','E016','JOHN','BARRY'),
	('D005','E017','BARRY','SMITH'),
	('D005','E018','MARY','GREEN');
GO


DROP TABLE IF EXISTS [MASTER_DATA].[COURSE]
GO

CREATE TABLE [MASTER_DATA].[COURSE]
(
COURSE_ID VARCHAR(8) NOT NULL,
COURSE_NAME VARCHAR(32) NOT NULL
)
GO

INSERT INTO MASTER_DATA.COURSE VALUES
	('C001','MARKETING BASICS'),
	('C002','ACCOUNTING 1'),
	('C003','ACCOUNTING 2'),
	('C004','BOOK KEEPING FOR BEGGINERS');
GO

DROP TABLE IF EXISTS MASTER_DATA.EMPLOYEE_COURSE
GO

SELECT 
	E.[EMP_ID],
	C.[COURSE_ID]
INTO MASTER_DATA.EMPLOYEE_COURSE
FROM [MASTER_DATA].[EMPLOYEE] E
CROSS JOIN
[MASTER_DATA].[COURSE] C
GO