Use Utility
GO
 --test for the existence of the Employee_Hierachy table and DROP if found
IF OBJECT_ID('[dbo].Employee_Hierarchy') IS NOT NULL
    DROP TABLE [dbo].[Employee_Hierarchy];

CREATE TABLE Employee_Hierarchy
(
emp_id INT						NOT NULL,
emp_First_Name VARCHAR(100)		NOT NULL,
emp_Last_Name VARCHAR(100)		NOT NULL,
position VARCHAR(100)			NOT NULL,
dept_id smallint				NOT NULL,
mgr_id int						NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (emp_id ASC) 
)

INSERT INTO Employee_Hierarchy VALUES (101,'Alice', 'Wilkins', 'General Manager', 1, NULL);
INSERT INTO Employee_Hierarchy VALUES (102, 'Rick', 'James',  'Sales Manager', 2, 101);
INSERT INTO Employee_Hierarchy VALUES (103, 'John', 'Bridges', 'Marketing Manager', 2,  101);
INSERT INTO Employee_Hierarchy VALUES (104, 'Stephen', 'Cummings', 'Marketing Manager', 2, 102);
INSERT INTO Employee_Hierarchy VALUES (105, 'Maria', 'DelRico', 'Office Manager', 3,  102);
INSERT INTO Employee_Hierarchy VALUES (106, 'James', 'Morrison', 'Staff Liasion', 4,  105);
INSERT INTO Employee_Hierarchy VALUES (107, 'John', 'Mayall', 'Employee', 5,  106);
INSERT INTO Employee_Hierarchy VALUES (108, 'Stephen', 'White', 'Employee', 5,  106);
INSERT INTO Employee_Hierarchy VALUES (109, 'Colin', 'Carpenter', 'Employee', 5,  106);
INSERT INTO Employee_Hierarchy VALUES (110, 'James', 'Cromwell', 'Employee', 5,  106);

SELECT *
FROM Employee_Hierarchy;
