Oracle Illustrated

Feb 21 2010   8:53AM GMT

Migrating from 9i to 11g – Table Creation scripts

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Drop sequence dummy_seq
/

create sequence dummy_seq start with 1
/

create table emp
as
select
dummy_seq.nextval empno,
object_name empname,
object_id sal,
CASE WHEN ROWNUM BETWEEN 1 and 28000 then ‘CLERK’
WHEN ROWNUM BETWEEN 28001 and 30000 then ‘SALESMAN’
WHEN ROWNUM BETWEEN 30001 and 30150 then ‘PRESIDENT’
WHEN ROWNUM BETWEEN 30151 and 35000 then ‘MANAGER’
ELSE ‘ANALYST’ end Job,
round(
dbms_random.value(1000,100000)) comm,
CASE WHEN ROWNUM BETWEEN 1 and 10000 then 320
WHEN ROWNUM BETWEEN 10001 and 13051 then 120
WHEN ROWNUM BETWEEN 13052 and 26001 then 380
WHEN ROWNUM BETWEEN 26002 and 27002 then 630
ELSE 550 end deptno
from all_objects
/

DELETE FROM emp tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY empname ORDER BY empno, empname ) duplicate FROM emp ) qry
WHERE qry.duplicate > 1)

(The above delete is to run the examples selecting from various blocks with gaps)

create unique index emp_idx on emp (empno )
/

exec dbms_stats.gather_table_stats(ownname => ‘SYSTEM’, tabname => ‘emp’, cascade => TRUE)

CREATE TABLE DEPT AS
SELECT distinct deptno ,
CASE WHEN deptno = 380 then ‘ACCOUNTING’
WHEN deptno = 120 then ‘RESEARCH’
WHEN deptno = 550 then ‘SALES’
WHEN deptno = 320 then ‘OPERATIONS’
ELSE ‘IT’ end dname, ‘SINGAPORE’ Loc
FROM emp

create unique index dept_idx on dept (deptno )
/

CREATE TABLE BONUS
AS select empname, job, sal, comm from emp
/

create index bonus_idx1 on bonus (empname, job )
/

create table salgrade as
select distinct job job,
min(sal) losal, max(sal) hisal from bonus
group by job
/

create index sal_idx1 on salgrade (job );

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: