Siva Academy

Thursday, March 5, 2020

SQL to Convert Column to Rows

create table stu_table(Student varchar2(20),Subject varchar2(20));
insert into stu_table values('A','Science');
insert into stu_table values('A','math');
insert into stu_table values('A','eng');
insert into stu_table values('B','Science');
insert into stu_table values('B','math');
insert into stu_table values('B','eng');
insert into stu_table values('C','math');
commit;

select * from stu_table;

select STUDENT, SUBJECT,max(student) over(partition by student) stu_GRP
from stu_table;

select SCIENCE, MATH, ENG
from (
select STUDENT, SUBJECT,STUDENT stu_GRP
from stu_table)
pivot(max(student) for subject in ('Science' as Science,'math' as math,'eng' as eng))
order by stu_GRP;

select STUDENT, max(decode(SUBJECT,'Science',STUDENT)) Science,
max(decode(SUBJECT,'math',STUDENT)) math,
max(decode(SUBJECT,'eng',STUDENT)) eng
select STUDENT, SUBJECT
from stu_table
group by STUDENT
order by student;

-------------------------------------------------------------------------------------------------

drop table t;
create table t(id number, val varchar2(30));

insert into t values(1,'amit');
insert into t values(1,'kumar');
insert into t values(1,'sharma');

insert into t values(2,'kishor');
insert into t values(2,'kumar');
insert into t values(2,'prasad');
insert into t values(2,'dev');

insert into t values(3,'siva');
insert into t values(3,'kumar');

commit;

select * from (select id,val,row_number() over(partition by id order by rownum) n_id
from t)
pivot(max(val) for n_id in (1,2,3,4));

SELECT
id,max(decode(n_id,1,val)) v1,max(decode(n_id,2,val)) v2,max(decode(n_id,3,val)) v3,max(decode(n_id,4,val)) v4
FROM
(
SELECT
id,
val,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY ROWNUM) n_id
FROM
t
)
GROUP BY ID;

-------------------------------------------------------------------------------------------------

Saturday, February 29, 2020

PLSQL Block to print list of employee name

1. Oracle PLSQL Practical Interview Questions

Write a PLSQL block to print the list of employee names

set serveroutput on
DECLARE
lv_dept_no number :=10;
BEGIN
FOR i IN (
SELECT ename
FROM emp
WHERE deptno=lv_dept_no
) LOOP
dbms_output.put_line(i.ename);
END LOOP;
END;
/

-------------------------------------------------------------

set serveroutput on
DECLARE
lv_dept_no number :=10;
lv_ename emp.ename%type;
cursor c1 is SELECT ename
FROM emp WHERE deptno=lv_dept_no;
BEGIN
OPEN c1;
loop
fetch c1 into lv_ename;
exit when c1%notfound;
dbms_output.put_line(lv_ename);
END LOOP;
END;
/

-------------------------------------------------------------

set serveroutput on
DECLARE
type lv_name_list_type is table of emp.ename%type;
lv_emp_name_list lv_name_list_type := lv_name_list_type();
BEGIN
select ename
bulk collect into lv_emp_name_list
from emp
where deptno=10;

for i in lv_emp_name_list.first..lv_emp_name_list.last loop
dbms_output.put_line(lv_emp_name_list(i));
end loop;
END;
/

-------------------------------------------------------------

To learn more on cursor please check these links

To learn more on collection please check these links

https://www.youtube.com/playlist?list=PLb1qVSx1k1VpAFTXopXvAh_D3PLcTntdm

Saturday, February 15, 2020

SQL to convert list of numbers into table format

SQL to convert list of numbers into table format

create table t (d number);

insert into t
select level
from dual
connect by level <=25;

commit;

select C1, C2, C3, C4, C5
from (
select d,ceil(d/5) r, row_number() over(partition by ceil(d/5) order by d) c
from t)
pivot ( max(d) for c in (1 c1,2 c2,3 c3,4 c4,5 c5))
order by 1;

select C1, C2, C3, C4, C5
from (
select d,ceil(d/5) r, row_number() over(partition by ceil(d/5) order by d) c
from t)
pivot ( max(d) for d in (1 c1,2 c2,3 c3,4 c4,5 c5))
order by 1;

select max(case when mod(d,5)=1 then d end) c1,
max(case when mod(d,5)=2 then d end) c2,
max(case when mod(d,5)=3 then d end) c3,
max(case when mod(d,5)=4 then d end) c4,
max(case when mod(d,5)=0 then d end) c5
from t
group by ceil(d/5)
order by ceil(d/5);

Saturday, February 1, 2020

SQL query to check sudoku

SQL query to check sudoku

CREATE TABLE sudoku (
row_no VARCHAR2(3),
c1 NUMBER,
c2 NUMBER,
c3 NUMBER,
c4 NUMBER,
c5 NUMBER,
c6 NUMBER,
c7 NUMBER,
c8 NUMBER,
c9 NUMBER
);

insert into sudoku values('R1',8,2,3,4,9,6,1,5,7);
insert into sudoku values('R2',7,5,4,2,1,8,3,9,6);
insert into sudoku values('R3',1,6,9,7,5,3,2,8,4);
insert into sudoku values('R4',3,8,7,9,6,2,4,1,5);
insert into sudoku values('R5',4,9,2,1,8,5,7,6,3);
insert into sudoku values('R6',5,1,6,3,7,4,9,2,8);
insert into sudoku values('R7',6,3,1,8,2,7,5,4,9);
insert into sudoku values('R8',9,4,8,5,3,1,6,7,2);
insert into sudoku values('R9',2,7,5,6,4,9,8,3,1);

commit;

select sum(c1) c1,sum(c2) c2,sum(c3) c3,sum(c4) c4,sum(c5) c5,sum(c6) c6,sum(c7) c7,sum(c8) c8,sum(c9) c9
from sudoku;

select row_no,c1+c2+c3+c4+c5+c6+c7+c8+c9
from sudoku;

select 'G'||ceil(rownum/3) G,
sum(C1+ C2+ C3) G1,
sum(C4+ C5+ C6) G2,
sum(C7+ C8+ C9) G3
from sudoku
group by ceil(rownum/3);

--------------

select * from (
select sum(c1) c1,sum(c2) c2,sum(c3) c3,sum(c4) c4,sum(c5) c5,sum(c6) c6,sum(c7) c7,sum(c8) c8,sum(c9) c9
from sudoku)
UNPIVOT(column_val for column_name in (c1,c2,c3,c4,c5,c6,c7,c8,c9));

select G||column_name group_name, column_val group_sum
from (
select 'G'||ceil(rownum/3) G,
sum(C1+ C2+ C3) G1,
sum(C4+ C5+ C6) G2,
sum(C7+ C8+ C9) G3
from sudoku
group by ceil(rownum/3))
UNPIVOT(column_val for column_name in (g1,g2,g3));

--------------

select case when sum(distinct column_val)=45 then 'RESOLVED' else 'NOT RESOLVED' end from (
select * from (
select sum(c1) c1,sum(c2) c2,sum(c3) c3,sum(c4) c4,
sum(c5) c5,sum(c6) c6,sum(c7) c7,sum(c8) c8,sum(c9) c9
from sudoku)
UNPIVOT(column_val for column_name in (c1,c2,c3,c4,c5,c6,c7,c8,c9))
union
select row_no,c1+c2+c3+c4+c5+c6+c7+c8+c9
from sudoku
union
select G||column_name group_name, column_val group_sum
from (
select 'G'||ceil(rownum/3) G,
sum(C1+ C2+ C3) G1,
sum(C4+ C5+ C6) G2,
sum(C7+ C8+ C9) G3
from sudoku
group by ceil(rownum/3))
UNPIVOT(column_val for column_name in (g1,g2,g3)));

update sudoku set c3=5 where row_no='R3';

select column_val, LISTAGG(column_name,',') within group (order by column_name),
case when column_val=45 then 'CORRECT' else 'INCORRECT' end result
from(
select * from (
select sum(c1) c1,sum(c2) c2,sum(c3) c3,sum(c4) c4,
sum(c5) c5,sum(c6) c6,sum(c7) c7,sum(c8) c8,sum(c9) c9
from sudoku)
UNPIVOT(column_val for column_name in (c1,c2,c3,c4,c5,c6,c7,c8,c9))
union
select row_no,c1+c2+c3+c4+c5+c6+c7+c8+c9
from sudoku
union
select G||column_name group_name, column_val group_sum
from (
select 'G'||ceil(rownum/3) G,
sum(C1+ C2+ C3) G1,
sum(C4+ C5+ C6) G2,
sum(C7+ C8+ C9) G3
from sudoku
group by ceil(rownum/3))
UNPIVOT(column_val for column_name in (g1,g2,g3)))
group by column_val

Saturday, December 14, 2019

query to find the consecutive numbers

-------------------------------------------------------------

Data Setup

create table t(c number);

insert into t values(6);

insert into t values(10);

insert into t values(11);

insert into t values(12);

insert into t values(57);

insert into t values(77);

insert into t values(98);

insert into t values(99);

insert into t values(100);

insert into t values(120);

insert into t values(125);

commit;

-------------------------------------------------------------

Method 1

select c

from (

select c,

lag(c,1) over(order by c) lag,

lead(c,1) over(order by c) lead

from t)

where c=lag+1 or c=lead-1;

-------------------------------------------------------------

Method 2

select c from (

select c,count(1) over(partition by c-rownum) g

from t ) where g > 1;

-------------------------------------------------------------

Method 3

select c.c

from t c, t prev

where c.c = prev.c +1

union

select c.c

from t c, t next

where c.c = next.c -1;

-------------------------------------------------------------

Saturday, November 16, 2019

Examples on SQL to find group range of Sequence Numbers

drop table t;

create table t(c number);

insert into t values(1);

insert into t values(2);

insert into t values(3);

insert into t values(5);

insert into t values(7);

insert into t values(8);

insert into t values(9);

insert into t values(11);

insert into t values(12);

insert into t values(15);

insert into t values(16);

insert into t values(20);

commit;

select min(c) start_range,max(c) end_range

from t

group by c-rownum

order by 1;

create table t(START_RANGE number, end_range number);

Insert into t (START_RANGE,END_RANGE) values (1,3);

Insert into T (START_RANGE,END_RANGE) values (5,5);

Insert into T (START_RANGE,END_RANGE) values (7,9);

Insert into T (START_RANGE,END_RANGE) values (11,12);

Insert into T (START_RANGE,END_RANGE) values (15,16);

Insert into T (START_RANGE,END_RANGE) values (20,20);

SELECT start_range,

end_range,

r-1 seq,

start_range+r-1 result

FROM t,

lateral (select rownum r

from dual

connect by level <= (end_range-start_range)+1);

select start_range,end_range,r

from t,

(select rownum r

from

dual connect by level <=(

select max(end_range)

from t)) d

where start_range <= r and end_range >=r;

Below Answers Received from subscribers.... Thanks for answering :-)

SELECT DISTINCT (LEVEL + START_RANGE)-1 NUM

FROM T

CONNECT BY (LEVEL +START_RANGE ) <= END_RANGE+1

ORDER BY NUM;

---------------------------------------------------------

create table cars (
name varchar2(100),
start_year number,
end_year number);

insert into cars values('Swift',2007,2009);
insert into cars values('Swift',2009,2011);
insert into cars values('Swift',2011,2013);
insert into cars values('Maruti',2009,2011);
insert into cars values('Maruti',2013,2015);

commit;

with r as (select distinct name,start_year+r year
from cars,
lateral(select rownum-1 r
from dual connect by level <=end_year-start_year+1)
order by 1,2)
select name,min(year) s_year,max(year) e_year
from r
group by name,year-rownum;

Saturday, September 14, 2019

Expand and Fill the missing dates with last not null value

Input and Expected Output:

Data Setup:

create table tx_data(dt date, amnt number);

insert into tx_data values(to_date('20-JUL-2019','DD-MON-YYYY'),100);
insert into tx_data values(to_date('25-JUL-2019','DD-MON-YYYY'),200);
insert into tx_data values(to_date('30-JUL-2019','DD-MON-YYYY'),300);

commit;

Query:

with d1 as (
select min(dt) mi,max(dt) mx, max(dt)-min(dt) num
from tx_data),
dt_range as (select level-1+mi dt
from d1
connect by level <=num+1)
select dt_range.dt,
last_value(amnt ignore nulls) over(order by dt_range.dt) amnt
from dt_range left outer join tx_data
on (dt_range.dt = tx_data.dt)

order by 1;