Oracle Sample Table Script for SQL Server

I work primarily on SQL Server these days but I had originally started learning SQL on Oracle. I find the sample tables EMP, DEPT, BONUS, SALGRADE and DUMMY provided with Oracle to be useful for beginners to practice SQL commands. Oracle provides the script by the name of DEMOBLD.SQL to create these tables which can be found in the <OracleHome>\sqlplus\demo folder of your Oracle installation or at this URL. Some more useful scripts for Oracle users can be found at this OraFAQ page.

I thought that few folks might like to have those tables on SQL Server so I made some changes to the script so that it can run on SQL Server too. It might prove useful if you don’t have access to an Oracle installation but want want to practice SQL commands with these tables. Of course, MS SQL Server does not support all the SQL syntax as supported by Oracle, but the tables can be handy in some cases.

The SQL Server compatible script is below. Copy and paste in Query Analyzer to run. The basic difference from the original script is in the datatypes and the datetime convert function. The script will drop any tables with the same names in the current database so be sure that existing tables do not have any critical data.

/* DESCRIPTION
-- This script creates the sample tables in the current
-- MS SQL Server database and inserts corresponding data.
-- The data is same as the demonstration tables of Oracle.
--
-- 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].[EMP]
(EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2));
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].[DEPT]
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) );
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].[BONUS]
(ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL NUMERIC,
COMM NUMERIC);
GO

CREATE TABLE [dbo].[SALGRADE]
(GRADE NUMERIC,
LOSAL NUMERIC,
HISAL NUMERIC);
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 NUMERIC);
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];
About these ads

3 Responses to Oracle Sample Table Script for SQL Server

  1. abodeqa says:

    This post is really very helpful…great reading this post

  2. Desh says:

    Nice…Very useful post…………

  3. Tarun says:

    Thanks a lot. Very useful.
    just one confusion : Doesn’t BONUS table have any values ????

Have a question or a suggestion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: