BEGIN TRY
DROP TABLE [dbo].[EMPLOYEE];
DROP TABLE [dbo].[DEPARTMENT];
DROP TABLE [dbo].[SALARY_GRADE];
DROP TABLE [dbo].[EMPLOYEE_XML];
END TRY
BEGIN CATCH
END CATCH
GO
CREATE TABLE [dbo].[DEPARTMENT] (
DEPARTMENT_ID INTEGER PRIMARY KEY
,DNAME VARCHAR(14)
,LOC VARCHAR(13)
)
GO
INSERT INTO [dbo].[DEPARTMENT]
VALUES (
10
,'ACCOUNTING'
,'NEW YORK'
)
GO
INSERT INTO [dbo].[DEPARTMENT]
VALUES (
20
,'RESEARCH'
,'DALLAS'
)
GO
INSERT INTO [dbo].[DEPARTMENT]
VALUES (
30
,'SALES'
,'CHICAGO'
)
GO
INSERT INTO [dbo].[DEPARTMENT]
VALUES (
40
,'OPERATIONS'
,'BOSTON'
)
GO
CREATE TABLE [dbo].[EMPLOYEE] (
EMPLOYEE_ID INTEGER NOT NULL PRIMARY KEY
,EMPLOYEE_NAME VARCHAR(25)
,JOB VARCHAR(15)
,MGR INTEGER
,HIREDATE DATE
,SALARY DECIMAL(7, 2)
,BENEFIT DECIMAL(7, 2)
,DEPARTMENT_ID INTEGER FOREIGN KEY REFERENCES [dbo].[DEPARTMENT](DEPARTMENT_ID)
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7369
,'SMITH'
,'CLERK'
,7902
,'1980-12-17'
,2555
,NULL
,20
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7499
,'ALLEN'
,'SALESMAN'
,7698
,'1981-02-21'
,1600
,300
,30
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7521
,'WARD'
,'SALESMAN'
,7698
,'1981-03-17'
,1250
,500
,30
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7566
,'JONES'
,'MANAGER'
,7839
,'1981-04-02'
,2975
,NULL
,20
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7654
,'MARTIN'
,'SALESMAN'
,7698
,'1981-09-28'
,1250
,1400
,30
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7698
,'BLAKE'
,'MANAGER'
,7839
,'1981-05-01'
,2850
,NULL
,30
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7782
,'CLARK'
,'MANAGER'
,7839
,'1981-06-09'
,2450
,NULL
,10
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7788
,'SCOTT'
,'ANALYST'
,7566
,'1982-12-09'
,3000
,NULL
,20
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7839
,'KING'
,'PRESIDENT'
,NULL
,'1981-11-17'
,5000
,NULL
,10
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7844
,'TURNER'
,'SALESMAN'
,7698
,'1981-09-08'
,1500
,0
,30
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7876
,'ADAMS'
,'CLERK'
,7788
,'1983-01-12'
,1100
,NULL
,20
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7900
,'JAMES'
,'CLERK'
,7698
,'1981-12-03'
,950
,NULL
,30
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7902
,'FORD'
,'ANALYST'
,7566
,'1981-12-03'
,3000
,NULL
,20
)
GO
INSERT INTO [dbo].[EMPLOYEE]
VALUES (
7934
,'MILLER'
,'CLERK'
,7782
,'1982-01-23'
,1300
,NULL
,10
)
GO
CREATE TABLE [dbo].[SALARY_GRADE] (
GRADE INTEGER
,LOSAL INTEGER
,HISAL INTEGER
)
GO
INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
1
,700
,1200
)
GO
INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
2
,1201
,1400
)
GO
INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
3
,1401
,2000
)
GO
INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
4
,2001
,3000
)
GO
INSERT INTO [dbo].[SALARY_GRADE]
VALUES (
5
,3001
,9999
)
GO
CREATE TABLE [dbo].[EMPLOYEE_XML] ([XML_Value] XML)
GO
DECLARE @XML_STRING NVARCHAR(MAX);
SET @XML_STRING = ' ' + (
SELECT E.EMPLOYEE_ID
,E.EMPLOYEE_NAME
,DE.DNAME AS EMPLOYEE_DEPARTMENT
,M.EMPLOYEE_NAME AS MANAGER_NAME
,DM.DNAME AS MANAGER_DEPARTMENT
FROM [EMPLOYEE] E
INNER JOIN [EMPLOYEE] M ON E.MGR = M.EMPLOYEE_ID
INNER JOIN [DEPARTMENT] DE ON E.DEPARTMENT_ID = DE.DEPARTMENT_ID
INNER JOIN [DEPARTMENT] DM ON M.DEPARTMENT_ID = DM.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID = M.DEPARTMENT_ID
FOR XML AUTO
) + ' ';
INSERT INTO [dbo].[EMPLOYEE_XML] ([XML_Value])
VALUES (@XML_STRING);
SET @XML_STRING = ' ' + (
SELECT E.EMPLOYEE_ID
,E.EMPLOYEE_NAME
,DE.DNAME AS EMPLOYEE_DEPARTMENT
,M.EMPLOYEE_NAME AS MANAGER_NAME
,DM.DNAME AS MANAGER_DEPARTMENT
FROM [EMPLOYEE] E
INNER JOIN [EMPLOYEE] M ON E.MGR = M.EMPLOYEE_ID
INNER JOIN [DEPARTMENT] DE ON E.DEPARTMENT_ID = DE.DEPARTMENT_ID
INNER JOIN [DEPARTMENT] DM ON M.DEPARTMENT_ID = DM.DEPARTMENT_ID
WHERE E.DEPARTMENT_ID <> M.DEPARTMENT_ID
FOR XML AUTO
) + ' ';
INSERT INTO [dbo].[EMPLOYEE_XML] ([XML_Value])
VALUES (@XML_STRING);
SET @XML_STRING = ' ' + (
SELECT E.EMPLOYEE_ID
,E.EMPLOYEE_NAME
,DE.DNAME AS EMPLOYEE_DEPARTMENT
,M.EMPLOYEE_NAME AS MANAGER_NAME
,DM.DNAME AS MANAGER_DEPARTMENT
FROM [EMPLOYEE] E
INNER JOIN [EMPLOYEE] M ON E.MGR = M.EMPLOYEE_ID
INNER JOIN [DEPARTMENT] DE ON E.DEPARTMENT_ID = DE.DEPARTMENT_ID
INNER JOIN [DEPARTMENT] DM ON M.DEPARTMENT_ID = DM.DEPARTMENT_ID
FOR XML AUTO
) + ' ';
INSERT INTO [dbo].[EMPLOYEE_XML] ([XML_Value])
VALUES (@XML_STRING);