Archive

Archive for the ‘SQL’ Category

Transform from vertical rows to horizontal columns in oracle sql

Syntax:

————

select
Primary column,
max((case vertical_column_name when  ‘val1’
THEN  ‘display value’
else  null
end )) col1,

max((case vertical_column_name when  ‘val2’
THEN  ‘display value’
else  null
end )) col2,

max((case vertical_column_name when  ‘val3’
THEN  ‘display value’
else  null
end )) col3
from

table1 t1

where  conditions
group by primary_column;

Example:

—————

SELECT

st.roll_no,

max((case Phone_number_type when  ‘1’
THEN  Phone_number
else  null
end )) Land_Line_Number,

max((case Phone_number_type when  ‘2’
THEN  Phone_number
else  null
end )) Hand_Phone_Number

FROM

STUDENTS st

WHERE

st.rollno between 100 and 200

GROUP BY st.rollno;

 

 

 

 

Advertisements
Categories: SQL, Technical

Table not listed under connections in SQLDeveloper

When I created a connection with an existing user name, i was able to connect to the sql server but in the Tables tree i cannot see any tables list.

Solution: Use another user who has access to the table list(i.e. user sa)the user which i tried previously is not having access to the table listing, thats why the table tree was empty

Categories: PL/SQL, SQL

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

 

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

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

Remove constraints for existing columns in Oracle

July 27, 2012 Comments off

Syntax: ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME

Example1: ALTER TABLE ORDERS DROP CONSTRAINT PK_NEW1;

Categories: SQL, Technical