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)
February 6, 2007 at 4:31 PM
thank you very much
October 27, 2008 at 6:24 PM
thanks calvan….
July 4, 2009 at 12:22 PM
This is what exactly I was loking for.
The script does not contain constraints?
July 4, 2009 at 12:25 PM
First of all Thanks for the valuable script.
August 8, 2009 at 10:38 PM
Thanx Calvin..
Your scrip save my time..
thnx a lot
November 24, 2009 at 6:13 PM
Hi
Thanks a lot… 🙂
January 6, 2010 at 11:18 AM
Thanks Friend……………..
July 20, 2010 at 9:03 AM
It was immensely helpful.
Thanks!
sudhir
October 11, 2010 at 6:04 PM
splendid!
December 8, 2010 at 1:35 PM
Thanks a lot buddy…the article was indeed helpful
February 9, 2011 at 7:52 PM
Thanks a lot, you made my job easy! 🙂
February 11, 2011 at 11:50 PM
Thank you Very much ……….It Certainly helps me dude
June 4, 2011 at 5:07 PM
thnkx
June 11, 2011 at 1:06 PM
thank u very much ……it really helped
August 15, 2011 at 1:58 PM
I really like to thank for teaching in such a beautiful way.
August 26, 2011 at 11:51 PM
Good Job!! 🙂
October 18, 2011 at 7:35 PM
thanks Calvin, I reuse this info every time I format my DB
October 20, 2011 at 10:32 AM
thanks a lot.
November 27, 2011 at 2:20 PM
Nice Work… Definitely helpful to Everyone…Thank you so much..:)
December 20, 2011 at 1:05 PM
THANKYOU DUDE…………
December 29, 2011 at 3:50 AM
tooooooooooo baddddddddddd
January 10, 2012 at 3:38 AM
Good work. Helped me. Thanks.
January 17, 2012 at 1:57 AM
Thank you soooooooooooooooo much
January 19, 2012 at 1:50 AM
Thanks Yaar……………………..
January 31, 2012 at 4:07 AM
very nice
February 10, 2012 at 2:34 PM
==>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)
May 6, 2012 at 11:48 PM
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’)
February 10, 2012 at 9:20 PM
Awesome. Thank you sir. Topey
February 11, 2012 at 12:38 AM
Thank you all its really so helpful
February 28, 2012 at 11:37 AM
Thanks
March 15, 2012 at 2:52 PM
Dzieki wielkie
March 17, 2012 at 12:12 PM
thanks a lot bro for emp table
December 22, 2012 at 3:54 AM
PROSZE BARDZO EGON
March 17, 2012 at 12:15 PM
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’)
March 17, 2012 at 12:16 PM
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)
March 21, 2012 at 6:47 PM
[…] 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 […]
May 7, 2012 at 6:33 PM
Thanks alot bro.. 🙂
August 11, 2012 at 3:00 PM
Thanks a lot…….
August 21, 2012 at 8:25 AM
thank you
September 7, 2012 at 6:09 AM
Thankyou very much
September 27, 2012 at 6:13 AM
THANK U
November 16, 2012 at 2:45 AM
Thank u very very much… :))
December 4, 2012 at 11:17 AM
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”);
May 5, 2013 at 5:25 AM
good job
July 26, 2016 at 1:29 AM
Good thought to help others. Would have been still better if the constrains were included