Archive

Archive for August, 2012

Hierarchical Query in ORACLE SQL

Hierarchical queries are suitable for tables which is having self foreign key references.

SQL: create table hierarchy(Person_id number primary key, name varchar2(20),ancestor number references hierarchy(person_id));

INSERT SQL:

insert into hierarchy values(1,’Great Grand Father’,”);

insert into hierarchy values(2,’Grand Father’,1);

insert into hierarchy values(3,’Father’,2);
insert into hierarchy values(4,’Son1′,3);
insert into hierarchy values(5,’Son2′,3);
insert into hierarchy values(6,’Son3′,3);
insert into hierarchy values(7,’Grand Son1′,4);
insert into hierarchy values(8,’Grand Son1′,5);
SELECT * from hierarchy;

PERSON_ID NAME ANCESTOR
2 Grand Father 1
3 Father 2
4 Son1 3
5 Son2 3
6 Son3 3
7 Grand Son1 4
8 Grand Son1 5
1 Great Grand Father

Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 7 Grand Son1
4 5 Son2
5 8 Grand Son1
4 6 Son3

For better understanding, hierarchical query in tree structure

SELECT LEVEL,PERSON_ID,LPAD(‘ ‘,LEVEL*2,’.’) || NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID;

LEVEL PERSON_ID LPAD(”,LEVEL..
1 1 . Great Grand Father
2 2 … Grand Father
3 3 ….. Father
4 4 ……. Son1
5 7 ……… Grand Son1
5 9 ……… Grand Son2
5 10 ……… Gra Son3
4 5 ……. Son2
5 8 ……… Grand Son1
4 6 ……. Son3
5 12 ……… grand son2
5 11 ……… grand son1

ORDER SIBLINGS BY:-

SELECT LEVEL,PERSON_ID,LPAD(‘ ‘,LEVEL*2,’.’) || NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID LPAD(”,LEVEL..
1 1 . Great Grand Father
2 2 … Grand Father
3 3 ….. Father
4 4 ……. Son1
5 10 ……… Gra Son3
5 7 ……… Grand Son1
5 9 ……… Grand Son2
4 5 ……. Son2
5 8 ……… Grand Son1
4 6 ……. Son3
5 11 ……… grand son1
5 12 ……… grand son2

SYS_CONNECT_BY_PATH in Hierarchical Query:-

SQL:SELECT LEVEL,PERSON_ID,SYS_CONNECT_BY_PATH(NAME,’/’)
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID SYS_CONNECT_B..
1 1 /Great Grand Father
2 2 /Great Grand Father/Grand Father
3 3 /Great Grand Father/Grand Father/Father
4 4 /Great Grand Father/Grand Father/Father/Son1
5 10 /Great Grand Father/Grand Father/Father/Son1/Gra Son3
5 7 /Great Grand Father/Grand Father/Father/Son1/Grand Son1
5 9 /Great Grand Father/Grand Father/Father/Son1/Grand Son2
4 5 /Great Grand Father/Grand Father/Father/Son2
5 8 /Great Grand Father/Grand Father/Father/Son2/Grand Son1
4 6 /Great Grand Father/Grand Father/Father/Son3
5 11 /Great Grand Father/Grand Father/Father/Son3/grand son1
5 12 /Great Grand Father/Grand Father/Father/Son3/grand son2

CONNECT_BY_ROOT in Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME,CONNECT_BY_ROOT NAME AS ANCE
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME ANCE
1 1 Great Grand Father Great Grand Father
2 2 Grand Father Great Grand Father
3 3 Father Great Grand Father
4 4 Son1 Great Grand Father
5 10 Gra Son3 Great Grand Father
5 7 Grand Son1 Great Grand Father
5 9 Grand Son2 Great Grand Father
4 5 Son2 Great Grand Father
5 8 Grand Son1 Great Grand Father
4 6 Son3 Great Grand Father
5 11 grand son1 Great Grand Father
5 12 grand son2 Great Grand Father

SQL:SELECT LEVEL,PERSON_ID,NAME,CONNECT_BY_ROOT NAME AS ANCE
FROM
HIERARCHY
START WITH PERSON_ID=3
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME ANCE
1 3 Father Father
2 4 Son1 Father
3 10 Gra Son3 Father
3 7 Grand Son1 Father
3 9 Grand Son2 Father
2 5 Son2 Father
3 8 Grand Son1 Father
2 6 Son3 Father
3 11 grand son1 Father
3 12 grand son2 Father

Exclude some branches from the tree structure:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
AND PERSON_ID != 5
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 10 Gra Son3
5 7 Grand Son1
5 9 Grand Son2
4 6 Son3
5 11 grand son1
5 12 grand son2

WHERE CLAUSE IN Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
WHERE PERSON_ID != 5
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 10 Gra Son3
5 7 Grand Son1
5 9 Grand Son2
5 8 Grand Son1
4 6 Son3
5 11 grand son1
5 12 grand son2

 

Advertisements
Categories: SQL, Technical

CUBE, ROLLUP, GROUPING in ORACLE SQL

Basic table used in the Examples

SQL:create table dining(dining_id number primary key,food varchar2(20),food_type varchar2(20),qty number);

DINING_ID FOOD FOOD_TYPE QTY

SQL:

insert into dining values(1,’Dosa’,’Masala Dosa’,20);
insert into dining values(2,’Dosa’,’Tomato Dosa’,20);
insert into dining values(3,’Dosa’,’Onion Dosa’,30);
insert into dining values(4,’Dosa’,’Plain Dosa’,40);
insert into dining values(5,’Idli’,’Rava Idli’,50);
insert into dining values(6,’Idli’,’Rice Idli’,60);
insert into dining values(7,’Idli’,’Aval Idli’,70);

DINING_ID FOOD FOOD_TYPE QTY
1 Dosa Masala Dosa 10
2 Dosa Tomato Dosa 20
3 Dosa Onion Dosa 30
4 Dosa Plain Dosa 40
5 Idli Rava Idli 50
6 Idli Rice Idli 60
7 Idli Aval Idli 70

Using Group By:

SELECT

FOOD,FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY FOOD, FOOD_TYPE

ORDER BY FOOD, FOOD_TYPE;

OUTPUT:-

FOOD FOOD_TYPE SUM(QTY)
Dosa Masala Dosa 10
Dosa Onion Dosa 30
Dosa Plain Dosa 40
Dosa Tomato Dosa 20
Idli Aval Idli 70
Idli Rava Idli 50
Idli Rice Idli 60

USING ROLLUP WITH GROUP BY:-

SELECT

FOOD,FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY ROLLUP(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

FOOD FOOD_TYPE SUM(QTY)
Dosa Masala Dosa 10
Dosa Onion Dosa 30
Dosa Plain Dosa 40
Dosa Tomato Dosa 20
Dosa 100
Idli Aval Idli 70
Idli Rava Idli 50
Idli Rice Idli 60
Idli 180
280

When we use the rollup function with group by, output will have aggregate of the rows.
USING CUBE WITH GROUP BY:-

SELECT

FOOD,FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY CUBE(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

FOOD FOOD_TYPE SUM(QTY)
Dosa Masala Dosa 10
Dosa Onion Dosa 30
Dosa Plain Dosa 40
Dosa Tomato Dosa 20
Dosa 100
Idli Aval Idli 70
Idli Rava Idli 50
Idli Rice Idli 60
Idli 180
Aval Idli 70
Masala Dosa 10
Onion Dosa 30
Plain Dosa 40
Rava Idli 50
Rice Idli 60
Tomato Dosa 20
280

Cube will result aggregate values along with super aggregate values
USING GROUPING WITH GROUP BY:-

SELECT

GROUPING(FOOD),FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY CUBE(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

GROUPING(FOOD) FOOD_TYPE SUM(QTY)
0 Masala Dosa 10
0 Onion Dosa 30
0 Plain Dosa 40
0 Tomato Dosa 20
0 100
0 Aval Idli 70
0 Rava Idli 50
0 Rice Idli 60
0 180
1 Aval Idli 70
1 Masala Dosa 10
1 Onion Dosa 30
1 Plain Dosa 40
1 Rava Idli 50
1 Rice Idli 60
1 Tomato Dosa 20
1 280

SELECT

grouping(FOOD),FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY ROLLUP(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

GROUPING(FOOD) FOOD_TYPE SUM(QTY)
0 Masala Dosa 10
0 Onion Dosa 30
0 Plain Dosa 40
0 Tomato Dosa 20
0 100
0 Aval Idli 70
0 Rava Idli 50
0 Rice Idli 60
0 180
1 280

GROUPING function will let us know which is aggregate rows and which is not.

(If there is a value available for that field means zero otherwise one)

Categories: SQL, Technical

Views in ORACLE SQL

August 3, 2012 Comments off

View: its a SELECT statement with a name for a table.kind of virtual table…allows you to access a part of original table.

1.CREATE VIEW

============

Syntax: CREATE OR REPLACE VIEW  view_name AS SELECT STATEMENT

Example: CREATE OR REPLACE VIEW KARU_ORDER_VIEW AS SELECT * FROM ORDERS_KARU WHERE ORDER_ID=1;

you can check the view structure using below command

DESC KARU_ORDER_VIEW;

2.INSERT INTO VIEW

=================

Note 1: If the view is omitted ‘not null’ fields of real tables, then we cannot insert a record using view but we can update/delete

Note 2:If the view has a distinct, group function or hierarchical queries then we cannot insert a row

Note 3: From clause references more than one table ..i mean joins then we cannot insert a row

INSERT INTO KARU_ORDER_VIEW(ORDER_ID,ORDER_NAME,ORDER_DATE,QUANTITY)

values(6,’CUPS’,sysdate,60);

3.UPDATE VIEW

=============

Unless the data conflict the constraints of the real table, we can update the view

UPDATE

KARU_ORDER_VIEW

SET

ORDER_NAME=’SPOONS’

WHERE ORDER_ID=6;

4.DELETING IN VIEW

==================

DELETE  FROM KARU_ORDER_VIEW WHERE ORDER_ID=6;

5.DROP VIEW

============

Syntax: DROP VIEW View_Name;

Example: DROP VIEW KARU_ORDER_VIEW;

 

Categories: SQL, Technical

Joins In Oracle SQL

August 2, 2012 Comments off

Joins in SQL is used to combine two or more tables and retrieve the data to result a set of ouput.

Types of Joins in SQL:

=================

1.Equijoins

2.Non-Equijoins

3.Inner joins

4.Outer joins

5.Natural joins

6.Cross joins

7.Self joins

I am using the below tables to explain the joins

1.CREATE TABLE

ORDERS_KARU

(ORDER_ID NUMBER PRIMARY KEY,
ORDER_NAME VARCHAR2(20),
ORDER_DATE DATE,
QUANTITY NUMBER);

2.CREATE TABLE

CUSTOMERS_KARU

(CUSTOMER_ID NUMBER PRIMARY KEY,
CUSTOMER_NAME VARCHAR2(20),
CUSTOMER_ADDRESS VARCHAR2(50),
ORDER_ID NUMBER CONSTRAINT CUS_FR_KEY REFERENCES ORDERS_KARU(ORDER_ID));

3.INSERT INTO ORDERS_KARU VALUES(1,’KEYBOARDS’,SYSDATE,10);

INSERT INTO ORDERS_KARU VALUES(2,’MOUSE’,SYSDATE,20);

INSERT INTO ORDERS_KARU VALUES(3,’CELL PHONE’,SYSDATE,30);

INSERT INTO ORDERS_KARU VALUES(4,’BOOKS’,SYSDATE,40);

INSERT INTO ORDERS_KARU VALUES(5,’MONITOS’,SYSDATE,50);

ORDER_ID ORDER_NAME ORDER_DATE QUANTITY
1 KEYBOARDS 2012-08-02 04:51:04 10
2 MOUSE 2012-08-02 04:52:39 20
3 CELL PHONE 2012-08-02 04:52:39 30
4 BOOKS 2012-08-02 04:52:39 40
5 MONITOS 2012-08-02 04:52:39 50

4.INSERT INTO CUSTOMERS_KARU VALUES(1,’HP’,’UK’,1);

INSERT INTO CUSTOMERS_KARU VALUES(2,’DELL’,’USA’,1);

INSERT INTO CUSTOMERS_KARU VALUES(3,’ALLEGRO’,’AUS’,3);

INSERT INTO CUSTOMERS_KARU VALUES(4,’IPHONE’,’CHN’,5);

INSERT INTO CUSTOMERS_KARU(CUSTOMER_ID,CUSTOMER_NAME,

CUSTOMER_ADDRESS) VALUES (5,’NOKIA’,’IND’);

CUSTOMER_ID CUSTOMER_NAME CUSTOMER_ADDRESS ORDER_ID
1 HP UK 1
2 DELL USA 1
3 ALLEGRO AUS 3
4 IPHONE CHN 5
5 NOKIA IND

Inner Joins

==========

Inner joins will give the results if and only if the conditions are matched between the joined tables.

1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK INNER JOIN CUSTOMERS_KARU CK
ON

OK.ORDER_ID = CK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK INNER JOIN CUSTOMERS_KARU CK
ON

OK.ORDER_ID = CK.ORDER_ID
WHERE

CUSTOMER_NAME LIKE ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

Older Inner Join Syntax:

====================

1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK,CUSTOMERS_KARU CK
WHERE

OK.ORDER_ID = CK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.Outer Joins :

===========

Outer join is same as inner joins but also adds data from rows that dont necessarily matches in joined other tables.

Types of outer joins : Left,Right,Full

2.1.Left Outer Join

2.1.1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK LEFT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2

2.1.2 SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK LEFT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.1.3.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK left outer join CUSTOMERS_KARU CK
on

CK.ORDER_ID = OK.ORDER_ID
and

                         CK.customer_name like ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2

2.1.4.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK LEFT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID
WHERE

                          CK.customer_name like ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

2.2.Right outer join:

2.2.1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK RIGHT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
5 NOKIA

2.2.2.SELECT

CUSTOMER_ID,CUSTOMER_NAME,CK.ORDER_ID
FROM

ORDERS_KARU OK RIGHT OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
5 NOKIA

2.3.Full outer join:

2.3.1SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK FULL OUTER JOIN CUSTOMERS_KARU CK
ON

CK.ORDER_ID = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2
5 NOKIA

Older outer join syntax:

2.4.Older Right outer join

SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK, CUSTOMERS_KARU CK
WHERE

CK.ORDER_ID = OK.ORDER_ID(+);

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
5 NOKIA

2.5. Older Left outer join

2.5.1.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK, CUSTOMERS_KARU CK
WHERE

CK.ORDER_ID(+) = OK.ORDER_ID;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
1 HP 1
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5
4
2

2.5.2.SELECT

CUSTOMER_ID,CUSTOMER_NAME,OK.ORDER_ID
FROM

ORDERS_KARU OK, CUSTOMERS_KARU CK
WHERE

CK.ORDER_ID(+) = OK.ORDER_ID
and CK.customer_name like ‘%E%’;

CUSTOMER_ID CUSTOMER_NAME ORDER_ID
2 DELL 1
3 ALLEGRO 3
4 IPHONE 5

3.Natural Join :
============

If the columns in two tables have identical names, we can do the natural join.

3.1. SELECT * FROM ORDERS_KARU NATURAL JOIN CUSTOMERS_KARU;

O_ID O_NAME O_DATE QUANTITY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN

USING –this kiyword is similar to natural join.

3.2SELECT *

FROM

ORDERS_KARU

JOIN

CUSTOMERS_KARU

USING(ORDER_ID);

O_ID O_NAME O_DATE QTY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN

3.3 SELECT *

FROM

ORDERS_KARU

LEFT OUTER JOIN

CUSTOMERS_KARU

USING(ORDER_ID);

O_ID O_NAME O_DATE QUANTITY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN
4 BOOKS 2012-08-02 04:52:39 40
2 MOUSE 2012-08-02 04:52:39 20

3.4. SELECT *

FROM

ORDERS_KARU

RIGHT OUTER JOIN

CUSTOMERS_KARU

USING(ORDER_ID);

O_ID O_NAME O_DATE QTY C_ID C_NAME C_ADDRESS
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN
5 NOKIA IND

4.Non-EquiJoin

=============

Other than equal s criteria, like >=,<=,!=, like, between….what ever used to join the tables….these joins are called non-equijoin.

4.1 SELECT *

FROM

ORDERS_KARU OK

JOIN

CUSTOMERS_KARU CK

ON OK.ORDER_ID != CK.ORDER_ID;

O_ID O_NAME O_DATE QTY C_ID C_N C_ADR O_ID
1 KEYBOARDS 2012-08-02 04:51:04 10 3 ALLEGRO AUS 3
1 KEYBOARDS 2012-08-02 04:51:04 10 4 IPHONE CHN 5
2 MOUSE 2012-08-02 04:52:39 20 1 HP UK 1
2 MOUSE 2012-08-02 04:52:39 20 2 DELL USA 1
2 MOUSE 2012-08-02 04:52:39 20 3 ALLEGRO AUS 3
2 MOUSE 2012-08-02 04:52:39 20 4 IPHONE CHN 5
3 CELL PHONE 2012-08-02 04:52:39 30 1 HP UK 1
3 CELL PHONE 2012-08-02 04:52:39 30 2 DELL USA 1
3 CELL PHONE 2012-08-02 04:52:39 30 4 IPHONE CHN 5
4 BOOKS 2012-08-02 04:52:39 40 1 HP UK 1
4 BOOKS 2012-08-02 04:52:39 40 2 DELL USA 1
4 BOOKS 2012-08-02 04:52:39 40 3 ALLEGRO AUS 3
4 BOOKS 2012-08-02 04:52:39 40 4 IPHONE CHN 5
5 MONITOS 2012-08-02 04:52:39 50 1 HP UK 1
5 MONITOS 2012-08-02 04:52:39 50 2 DELL USA 1
5 MONITOS 2012-08-02 04:52:39 50 3 ALLEGRO AUS

5.CROSS JOIN or Cartesian product

============================

This join will produce n1*n2 times of rows. n1-number of rows in first table,n2-number of rows in second table

5.1. SELECT * FROM ORDERS_KARU, CUSTOMERS_KARU;

O_ID O_NAME O_DATE QTY C_ID C_NAME C_ADR O_ID
1 KEYBOARDS 2012-08-02 04:51:04 10 1 HP UK 1
1 KEYBOARDS 2012-08-02 04:51:04 10 2 DELL USA 1
1 KEYBOARDS 2012-08-02 04:51:04 10 3 ALLEGRO AUS 3
1 KEYBOARDS 2012-08-02 04:51:04 10 4 IPHONE CHN 5
1 KEYBOARDS 2012-08-02 04:51:04 10 5 NOKIA IND
2 MOUSE 2012-08-02 04:52:39 20 1 HP UK 1
2 MOUSE 2012-08-02 04:52:39 20 2 DELL USA 1
2 MOUSE 2012-08-02 04:52:39 20 3 ALLEGRO AUS 3
2 MOUSE 2012-08-02 04:52:39 20 4 IPHONE CHN 5
2 MOUSE 2012-08-02 04:52:39 20 5 NOKIA IND
3 CELL PHONE 2012-08-02 04:52:39 30 1 HP UK 1
3 CELL PHONE 2012-08-02 04:52:39 30 2 DELL USA 1
3 CELL PHONE 2012-08-02 04:52:39 30 3 ALLEGRO AUS 3
3 CELL PHONE 2012-08-02 04:52:39 30 4 IPHONE CHN 5
3 CELL PHONE 2012-08-02 04:52:39 30 5 NOKIA IND
4 BOOKS 2012-08-02 04:52:39 40 1 HP UK 1
4 BOOKS 2012-08-02 04:52:39 40 2 DELL USA 1
4 BOOKS 2012-08-02 04:52:39 40 3 ALLEGRO AUS 3
4 BOOKS 2012-08-02 04:52:39 40 4 IPHONE CHN 5
4 BOOKS 2012-08-02 04:52:39 40 5 NOKIA IND
5 MONITOS 2012-08-02 04:52:39 50 1 HP UK 1
5 MONITOS 2012-08-02 04:52:39 50 2 DELL USA 1
5 MONITOS 2012-08-02 04:52:39 50 3 ALLEGRO AUS 3
5 MONITOS 2012-08-02 04:52:39 50 4 IPHONE CHN 5
5 MONITOS 2012-08-02 04:52:39 50 5 NOKIA IND

6.Self-join:

=========

Joining the table to itself is self-join

6.1SELECT *

FROM

ORDERS_KARU OK1

JOIN ORDERS_KARU OK2

ON OK1.ORDER_ID =OK2.ORDER_ID;

O_ID O_NAME O_DATE QTY O_ID OR_NAME O_DATE QY
1 KEYBOARDS 2012-08-02 04:51:04 10 1 KEYBOARDS 2012-08-02 04:51:04 10
2 MOUSE 2012-08-02 04:52:39 20 2 MOUSE 2012-08-02 04:52:39 20
3 CELL PHONE 2012-08-02 04:52:39 30 3 CELL PHONE 2012-08-02 04:52:39 30
4 BOOKS 2012-08-02 04:52:39 40 4 BOOKS 2012-08-02 04:52:39 40
5 MONITOS 2012-08-02 04:52:39 50 5 MONITOS 2012-08-02 04:52:39 50
Categories: Technical

simple but weird SQL i came across today

August 1, 2012 Comments off

I really feel shame that i didnt know the answer for this simple sql.

1.select to_char(add_months(trunc(to_date(’10-05-10′,’RR-MM-DD’),’YY’),-1),’Q’) from dual;

2.update (select * from orders_karu) set order_date=sysdatewhere order_id=1

Categories: SQL, Technical