Oracle sample tables for SQLServer

 

EDIT 3/21/2012:

I have put a revised version of the Oracle sample tables post on my other blog about SQL Server and technical stuff. You might want to have a look at the URL below.

http://aalamrangi.wordpress.com/2012/03/21/oracle-sample-table-script-for-sql-server/

Thanks for visiting!

————————————————–

Oracle has some sample tables viz. EMP, DEPT, BONUS, SALGRADE, DUMMY etc. which are helpful while practicing SQL commands. Oracle provides the scripts to create these tables in the name of DEMOBLD.SQL which can be found in the <OracleHome>\sqlplus\demo folder of your Oracle installation or online at this link. (EDIT 3/21/2012: This link is dead now!)

I have made some changes to the script to run it on MS SQL Server. It might prove useful if you don’t have access to an Oracle installation but want want to practice SQL commands. 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 is in the datatypes and the datetime convert function.

/* 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. */

DROP TABLE EMP
DROP TABLE DEPT
DROP TABLE BONUS
DROP TABLE SALGRADE
DROP TABLE DUMMY

CREATE TABLE 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))

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

CREATE TABLE DEPT
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) )

INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’)
INSERT INTO DEPT VALUES (20, ‘RESEARCH’, ‘DALLAS’)
INSERT INTO DEPT VALUES (30, ‘SALES’, ‘CHICAGO’)
INSERT INTO DEPT VALUES (40, ‘OPERATIONS’, ‘BOSTON’)

CREATE TABLE BONUS
(ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL NUMERIC,
COMM NUMERIC)

CREATE TABLE SALGRADE
(GRADE NUMERIC,
LOSAL NUMERIC,
HISAL NUMERIC)

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

CREATE TABLE DUMMY
(DUMMY NUMERIC)

INSERT INTO DUMMY VALUES (0)

About these ads

44 Responses to “Oracle sample tables for SQLServer”

  1. santosh Says:

    thank you very much

  2. demon Says:

    thanks calvan….

  3. SrinathKN Says:

    This is what exactly I was loking for.
    The script does not contain constraints?

  4. SrinathKN Says:

    First of all Thanks for the valuable script.

  5. A.Rahim Says:

    Thanx Calvin..
    Your scrip save my time..
    thnx a lot

  6. Siva Says:

    Hi

    Thanks a lot… :)

  7. sudhir Says:

    It was immensely helpful.

    Thanks!

    sudhir

  8. dee Says:

    splendid!

  9. krupa Says:

    Thanks a lot buddy…the article was indeed helpful

  10. Yogesh Says:

    Thanks a lot, you made my job easy! :)

  11. Rakesh Says:

    Thank you Very much ……….It Certainly helps me dude

  12. Kiran Kumar Reddy M Says:

    thank u very much ……it really helped

  13. Imtiyaz Husain Sayyed Says:

    I really like to thank for teaching in such a beautiful way.

  14. Sumit Says:

    Good Job!! :)

  15. pratima Says:

    thanks Calvin, I reuse this info every time I format my DB

  16. alok Says:

    thanks a lot.

  17. Pavan Says:

    Nice Work… Definitely helpful to Everyone…Thank you so much..:)

  18. madhu Says:

    THANKYOU DUDE…………

  19. laxmi Says:

    tooooooooooo baddddddddddd

  20. June Says:

    Good work. Helped me. Thanks.

  21. jagan Says:

    Thank you soooooooooooooooo much

  22. Jamil Akhtar Says:

    Thanks Yaar……………………..

  23. vijay patel Says:

    very nice

  24. dharmender Says:

    ==>insert into emp values(&empno,’&ename’,’&job’,’&hiredate’,&sal,&comm,&deptno);
    //the above query results an error
    //it will returns error at hire date,what is the format for hiredate statement
    and how will take multiple statements using one insert statement pleaz explain me in detail
    im waiting for urs explanations(anybody)

    • Arnold Says:

      coz hiredate is in date format so you have to enter a proper date format or convert it into date type;the initial date format is:DD-MON-YYY (eg:03-jan-2012) if you puts for instance 01-02-1981 oracle can’t understand that format yo have to do the following operation to convert: TO_DATE(’01-02-1981′,’DD-MM-YYYY’)

  25. azey Says:

    Awesome. Thank you sir. Topey

  26. kapil Says:

    Thank you all its really so helpful

  27. Lokesh Says:

    Thanks

  28. egon Says:

    Dzieki wielkie

  29. uddhav Says:

    thanks a lot bro for emp table

  30. uddhav Says:

    create table locations
    (LocaTION_ID number(4)constraint loc_location_id_nn not null ,STREET_ADDRESS varchar2(40),POSTAL_CODE varchar2(12),CITY varchar2(30)constraint loc_locaton_id_nn not null,STATE_PROVINCE varchar2(25),COUNTRY_ID char(2));

    insert into locations values(1400,’2014jabberwoky rd’,26192,’southlake’,’texas’,’us’)
    insert into locations values(1500,’2011 interiors blvd’,99236,’southsanfrancisco’,’california’,’us’)
    insert into locations values(1700,’2004charade rd’,98199,’seattle’,’washington’,’us’)
    insert into locations values(1800,’460 blovr st w’,’onmss 1xb’, ‘toronto’,’ontario’,’ca’)
    insert into locations values(2500,’2500 macdalen centre’,’ox 99zb’,’oxford’,’oxford’,’uk’)

  31. uddhav Says:

    create table jobs
    (job_id varchar2(10) constraint jo_job_id_nn not null,job_title varchar2(35) constraint jo_job_title_nn not null,
    min_salary number (6),max_salary number(6))
    insert into jobs values(‘ad_pres’,’president’,20000,40000)
    insert into jobs values(‘ad_vp’,’administration vice president’,15000,30000)

  32. Oracle Sample Table Script for SQL Server « Learnings as a SQL Server DBA and Developer Says:

    [...] with Oracle to be useful for beginners to practice SQL commands. I did the following post on my other blog for fun in 2007 and that post has been surprisingly popular among [...]

  33. saad Says:

    Thanks alot bro.. :)

  34. Swati Says:

    Thanks a lot…….

  35. sanjay gupta Says:

    thank you

  36. Giridhar Says:

    Thankyou very much

  37. PANKAJ Says:

    THANK U

  38. Bilal Says:

    Thank u very very much… :))

  39. IIT11 Says:

    INSERT INTO EMP VALUES(7369,”SMITH”,800,”1980-12-17”,”CLERK”,20);
    INSERT INTO EMP VALUES(7499,”ALLEN”,1600,“1981-02-20”,“SALESMAN”,30);
    INSERT INTO EMP VALUES(7521,”WARD”,500,”1981-02—22”,”SALESMAN”,30);
    INSERT INTO EMP VALUES(7566,”JONES”,2975,”1981-04-02”,“MANAGER”,20);
    INSERT INTO EMP VALUES(7654,“MARTIN”,1250,“1981-09-28”,“SALESMAN”,30);
    INSERT INTO EMP VALUES(7698,“BLAKE”,2850,“1981-05-01”,“MANAGER”,30);
    INSERT INTO EMP VALUES(7782,“CLARK”,2450,“1981-06-09”,“MANAGER”,10);
    INSERT INTO EMP VALUES(7788,“SCOTT”,3000,“1982-10-09”,“ANALYST”,20);
    INSERT INTO EMP VALUES(7839,“KING”,5000,“1981-11-17”,“PRESIDENT”,10);
    INSERT INTO EMP VALUES(7844,“TURNER”,1500,“1981-09-08” ,“SALESMAN”,30);
    INSERT INTO EMP VALUES(7876,“ADAMS”,1100,“1983-01-12”, “CLERK”,20);
    INSERT INTO EMP VALUES(7900,“JAMES”,950,“1981-12-03”,“CLERK”,30);
    INSERT INTO EMP VALUES(7902,“FORD”,3000,“1981-12-03”,“ANALYST”,20);
    INSERT INTO EMP VALUES(7934,“MILLER”,1300,”1982-23-01”,“CLERK”,10);
    insert into dept values (1,”ACCOUNTING”,”ST LOUIS”);
    insert into dept values (2,”RESEARCH”,”NEW YORK”);
    insert into dept values (3,”SALES”,”ATLANTA”);
    insert into dept values (4, “OPERATIONS”,”SEATTLE”);

  40. malik Says:

    good job


Leave a Reply

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: