/*********************************/
/* PODCAST DEMO: NAVIGATING SSMS */
/*********************************/

/***************************/
/* TOPIC 3: OJECT EXPLORER */
/***************************/

USE MASTER
GO

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

/********************************/
/* END TOPIC 3: OBJECT EXPLORER */
/********************************/

/***********************/
/* TOPIC 4: QUERY PANE */
/***********************/

USE [ORGANIZATION_V2]
GO

DROP SCHEMA IF EXISTS HUMAN_RESOURCES
GO

CREATE SCHEMA HUMAN_RESOURCES
GO

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

TRUNCATE TABLE [HUMAN_RESOURCES].[DEPARTMENT]
GO

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

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

SELECT
	*
FROM [HUMAN_RESOURCES].[DEPARTMENT]
GO

/************/
/* CLEAN UP */
/************/

/*****************************/
/* RUN IN A SEPERATE SESSION */
/*****************************/

ALTER DATABASE ORGANIZATION_V2
SET SINGLE_USER
GO

/*************************/
/* CHECK FOR CONNECTIONS */
/*************************/

sp_who2
GO

DROP DATABASE IF EXISTS [ORGANIZATION_V2]
GO

/*
ALTER DATABASE ORGANIZATION_V2
SET MULTI_USER
GO
*/

/***********************************/
/* END OF PODCAST: NAVIGATING SSMS */
/***********************************/