Emp and Dept table script for SQL Server

/* DESCRIPTION
-- This script creates the sample tables in the current
-- MS SQL Server database and inserts corresponding data.
--
-- USAGE
-- Copy/Paste this script in Query Analyzer and select the database
-- from the drop down list where you want to create these tables.
-- Select EXECUTE from the QUERY menu or simply press F5 to run the script.
--
-- WARNING
-- The script drops any tables with the same name so be sure that
-- any existing tables with same names do not have any critical data.
*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMP]') AND type in (N'U'))
DROP TABLE [dbo].[EMP]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPT]') AND type in (N'U'))
DROP TABLE [dbo].[DEPT]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BONUS]') AND type in (N'U'))
DROP TABLE [dbo].[BONUS]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SALGRADE]') AND type in (N'U'))
DROP TABLE [dbo].[SALGRADE]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DUMMY]') AND type in (N'U'))
DROP TABLE [dbo].[DUMMY]
GO


CREATE TABLE [dbo].[DEPT]
(DEPTNO INT PRIMARY KEY ,
DNAME VARCHAR(20),
LOC VARCHAR(20) );
GO

INSERT INTO [dbo].[DEPT] VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO [dbo].[DEPT] VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO [dbo].[DEPT] VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO [dbo].[DEPT] VALUES (40, 'OPERATIONS', 'BOSTON');


CREATE TABLE [dbo].[EMP]
(EMPNO INT PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR INT,
HIREDATE DATE,
SAL MONEY,
COMM MONEY,
DEPTNO INT FOREIGN KEY REFERENCES DEPT(DEPTNO) );
GO

INSERT INTO [dbo].[EMP] VALUES
(7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);
INSERT INTO [dbo].[EMP] VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);
INSERT INTO [dbo].[EMP] VALUES
(7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);
INSERT INTO [dbo].[EMP] VALUES
(7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);
INSERT INTO [dbo].[EMP] VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);
INSERT INTO [dbo].[EMP] VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);
INSERT INTO [dbo].[EMP] VALUES
(7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);
INSERT INTO [dbo].[EMP] VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);
INSERT INTO [dbo].[EMP] VALUES
(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);
INSERT INTO [dbo].[EMP] VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);
INSERT INTO [dbo].[EMP] VALUES
(7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);
INSERT INTO [dbo].[EMP] VALUES
(7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);
INSERT INTO [dbo].[EMP] VALUES
(7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);
INSERT INTO [dbo].[EMP] VALUES
(7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);

CREATE TABLE [dbo].[BONUS]
(ENAME VARCHAR(20),
JOB VARCHAR(20),
SAL MONEY,
COMM MONEY);
GO

CREATE TABLE [dbo].[SALGRADE]
(GRADE INT NOT NULL,
LOSAL MONEY,
HISAL MONEY );
GO

INSERT INTO [dbo].[SALGRADE] VALUES (1, 700, 1200);
INSERT INTO [dbo].[SALGRADE] VALUES (2, 1201, 1400);
INSERT INTO [dbo].[SALGRADE] VALUES (3, 1401, 2000);
INSERT INTO [dbo].[SALGRADE] VALUES (4, 2001, 3000);
INSERT INTO [dbo].[SALGRADE] VALUES (5, 3001, 9999);

CREATE TABLE [dbo].[DUMMY]
(DUMMY INT);
GO

INSERT INTO [dbo].[DUMMY] VALUES (0);

-- Verify the data
SELECT * FROM [dbo].[EMP];
SELECT * FROM [dbo].[DEPT];
SELECT * FROM [dbo].[BONUS];
SELECT * FROM [dbo].[SALGRADE];
SELECT * FROM [dbo].[DUMMY];
















http://mssqldevelopersaran.blogspot.com