Apr 26, 2010

Pivoting INSERT - Oracle


Pivoting is an operation in which you need to build a transformation such that each record from any  input stream, such as, a  nonrelational database table, must be converted into multiple records for a more relational database table environment.


Suppose you receive a set of  sales records from a nonrelational database table, SALES_SOURCE_DATA in the following format:
EMPLOYEE_ID,WEEK_ID,SALES_MON,SALES_TUE,SALES_WED, SALES_THUR,SALES_FRI
You would want to store these records in the SALES_INFO table in a more typical relational format:
EMPLOYEE_ID, WEEK, SALES



Using a pivoting INSERT, convert the set of  sales records from the  nonrelational database table to relational format.





INSERT ALL   INTO sales_info VALUES (employee_id,week_id,sales_MON)   INTO sales_info VALUES (employee_id,week_id,sales_TUE)   INTO sales_info VALUES (employee_id,week_id,sales_WED)   INTO sales_info VALUES (employee_id,week_id,sales_THUR)   INTO sales_info VALUES (employee_id,week_id, sales_FRI)   SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,          sales_WED, sales_THUR,sales_FRI   FROM sales_source_data;
5 rows created.



In the example in the slide, the sales data is received from the nonrelational database table SALES_SOURCE_DATA, which is the details of the sales performed by a sales representative on each day of a week, for a week with a particular week ID.
    DESC SALES_SOURCE_DATA


Name                      Null?                Type
EMPLOYEE_ID                             NUMBER(6)
WEEK_ID                                      NUMBER(2)
SALES_MON                                 NUMBER(8,2)
SALES_TUE                                  NUMBER(8,2)
SALES_WED                                 NUMBER(8,2)
SALES_THUR                               NUMBER(8,2)
SALES_FRI                                    NUMBER(8,2)





SQL> SELECT * FROM SALES_SOURCE_DATA;

EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI
176             6      2000     3000      4000        5000     6000

SQL> DESC SALES_INFO 

Name                      Null?                Type
EMPLOYEE_ID                             NUMBER(6)
WEEK                                            NUMBER(2)
SALES                                            NUMBER(8,2)

SQL> 
SELECT * FROM sales_info;

EMPLOYEE_ID       WEEK      SALES
176                6        2000
176                6        3000
176                6        4000
176                6        5000
176                6        6000


Observe in the preceding example that using a pivoting INSERT, one row from the SALES_SOURCE_DATA table is converted into five records for the relational table, SALES_INFO.

Apr 19, 2010

SQL * Plus File commands - Oracle

SQL * Plus File commands - Oracle


1. SAVE filename
2. GET filename
3. START filename
4. @ filename
5. EDIT filename
6. SPOOL filename
7. EXIT


SQL> L
1. Select lastname, manager_id, dept_no
2* from emp;


SQL > SAVE my_query
File created my_query


SQL > START my_query
lastname          manager_id      dept_id
-----------------------------------------
King                                                   1
Kochaar                         80                1
..........
10 rows updated

Apr 18, 2010

Using LIST, n, APPEND commands in SQL * plus - Oracle

In earlier post http://angeldeeps.blogspot.com/2010/04/sql-plus-oracle-editing-commands.html I discussed about list of editing commands used in SQL * plus - Oracle.

Here, I am going to discuss few of the commands from that list.

//Here I have created a table called emp having empno and empname fields.

SQL> create table emp (empno number, empname varchar(20));


Table created.

//Inserting few records into emp table
SQL> insert into emp values(1,'Deepika');


1 row created.


SQL> insert into emp values(2, 'Aarti');


1 row created.


SQL> insert into emp values(3, 'Rupal');


1 row created.


SQL> insert into emp values(4, 'Ratna');


1 row created.


SQL> insert into emp values(5, 'Sonal');


1 row created.


//List command will show your last command executed
SQL> list
  1* insert into emp values(5, 'Sonal')


//Retrieving values to view

SQL> select * from emp;


     EMPNO EMPNAME
---------- --------------------
         1 Deepika
         2 Aarti
         3 Rupal
         4 Ratna
         5 Sonal

//Retrieving particular field records
SQL> select empno
  2  , empname from emp;


     EMPNO EMPNAME
---------- --------------------
         1 Deepika
         2 Aarti
         3 Rupal
         4 Ratna
         5 Sonal

//n - here n is 1 means it will show me statement at that line no
SQL> 1
  1* select empno

//Added jobcode field to emp table

SQL> alter table emp add (jobcode varchar(10));


Table altered.


SQL> select * from emp;


     EMPNO EMPNAME              JOBCODE
---------- -------------------- ----------
         1 Deepika
         2 Aarti
         3 Rupal
         4 Ratna
         5 Sonal

//Updating jobocde 
SQL> update emp set jobcode = 'A001' where empno =1;


1 row updated.


SQL> update emp set jobcode = 'B001' where empno = 2;


1 row updated.


SQL> update emp set jobcode = 'C001' where empno = 3;


1 row updated.


SQL> update emp set jobcode = 'D001' where empno = 4;


1 row updated.


SQL> update emp set jobcode = 'E001' where empno = 5;


1 row updated.

//retrieving particular field records

SQL> select empname
  2  from emp;


EMPNAME
--------------------
Deepika
Aarti
Rupal
Ratna
Sonal

// A - it will append jobcode to my last statement executed
SQL> A, jobcode;
  2* from emp, jobcode


//L will show last statement 
SQL> L
  1  select empname
  2* from emp, jobcode



SQL> select * from emp;


     EMPNO EMPNAME              JOBCODE
---------- -------------------- ----------
         1 Deepika              A001
         2 Aarti                B001
         3 Rupal                C001
         4 Ratna                D001
         5 Sonal                E001


SQL> L
  1* select * from emp


//C - will change. Here I am changing my last statement's emp table with dept table. So my query will now be select * from dept;
SQL> c/emp/dept
  1* select * from dept


SQL> L
  1* select * from dept

SQL * Plus - oracle Editing commands

SQL * Plus editing commands -

1. A[PPEND] text
2. C[HANGE] / old / new
3. C[HANGE] / text
4. CL[EAR] BUFF[ER]
5. DEL
6. DEL n
7. DEL m n
8. I[NPUT]
9. I[NPUT] text
10. L[IST]
11. L[IST] n
12. L[IST] m n
13. R[UN]
14. n
15. n text
16. 0 text

Note :- Will be discussed in detail in future posts..

Difference between SQL and SQL * plus commands

The Differences between SQL and SQL * plus commands are as follows :


SQL -

1. A language
2. ANSI standard
3. Keywords cannot be abbreviated
4. Statements manipulate data and table definitions in the database

SQL * Plus -

1. An environment
2. Oracle proprietary
3. Keywords can be abbreviated
4. Commands do not allow manipulation of values in database