Master in SQL in 10 min
It is required to hands-on on SQL for multiple reason .Below query designed in such a way that go to start as there no dependency on table or data .Queries cover in depth join, hierarchical, window function and pivot/unpivot .
Please try online with below link for practice .
https://livesql.oracle.com/apex/f?p=590:1:17166089709093::NO:RP::
JOIN :
with A as (select level as c from dual connect by level<10)
,
B as (select level+3 as c from dual connect by level<10 )
--- A is [1...9] and B is [4...12]
---NOTE : A is left table and B is right table when we say A join B
--INNER JOIN
-- it will find record where a.id =b.id otherwords common element in both
select A.c as a,B.c as B from A
inner join B on B.c=A.c
--or by deafult it is inner join
select A.c as a,B.c as B from A
join B on B.c=A.c
--inner join O/P
A B
4 4
5 5
6 6
7 7
8 8
9 9
--LEFT JOIN
--it will find record get all record of A and matched rcord of B with A
select A.c as a,B.c as B from A
left join B on B.c=A.c
--Left join O/P
A B
4 4
5 5
6 6
7 7
8 8
9 9
1 -
2 -
3 -
--Only A
select A.c as a,B.c as B from A
left join B on B.c=A.c where b.c is null
A B
1 -
2 -
3 -
--RIGHT JOIN
--it will find record get all record of A and matched rcord of B with A
select A.c as a,B.c as B from A
right join B on B.c=A.c
--Right join output
A B
4 4
5 5
6 6
7 7
8 8
9 9
- 11
- 12
- 10
--only B
select A.c as a,B.c as B from A
right join B on B.c=A.c where a.c is null
A B
- 11
- 12
- 10
--FULL JOIN --all a and all b
select A.c as a,B.c as B from A
full join B on B.c=A.c
--O/P
A B
4 4
5 5
6 6
7 7
8 8
9 9
- 10
- 11
- 12
1 -
2 -
3 -
--only A and only B
select A.c as a,B.c as B from A
full join B on B.c=A.c where a.c is null or b.c is null
A B
- 10
- 11
- 12
1 -
2 -
3 -
---------End of join -------------------------------
--Cartesian product AXB
select A.c as a,B.c as B from A
, B
-O/P
A B
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12
2 4
2 5
2 6
2 7
2 8
2 9
2 10
2 11
2 12
3 4
3 5
3 6
3 7
3 8
3 9
3 10
3 11
3 12
4 4
4 5
4 6
4 7
4 8
4 9
4 10
4 11
4 12
5 4
5 5
5 6
5 7
5 8
5 9
5 10
5 11
5 12
6 4
6 5
6 6
6 7
6 8
6 9
6 10
6 11
6 12
7 4
7 5
7 6
7 7
7 8
7 9
7 10
7 11
7 12
8 4
8 5
8 6
8 7
8 8
8 9
8 10
8 11
8 12
9 4
9 5
9 6
9 7
9 8
9 9
9 10
9 11
9 12
Hierarchical :
with a as (
select 'CEO' role, 'None' report from dual
union all
select 'M1' role, 'CEO' report from dual
union all
select 'M2' role, 'CEO' report from dual
union all
select 'R1' role, 'M1' report from dual
union all
select 'R2' role, 'M1' report from dual
union all
select 'R3' role, 'M2' report from dual
union all
select 'R4' role, 'M2' report from dual
union all
select 'R5' role, 'M2' report from dual
)
select sys_connect_by_path(role,'/') path ,role,report from a start with role='R1' connect by prior report=role
PATH ROLE REPORT
/R1 R1 M1
/R1/M1 M1 CEO
/R1/M1/CEO CEO None
with k as (select 'w1' as word,'s1,s2,s3' as syn from dual union all select 'w2' as word , 'e1,e2,e3' as syn from dual )
select word,regexp_substr(syn,'[^,]+',1,level) from k connect by regexp_instr(syn,'[^,]+',1,level)>0 and prior sys_guid() is not null and prior word=word
WORD REGEXP_SUBSTR(SYN,'[^,]+',1,LEVEL)
w1 s1
w1 s2
w1 s3
w2 e1
w2 e2
w2 e3
Window function
-- Get the table?
with T as (select * from (select level as c1 from dual connect by level <4) , (select level as c2 from dual connect by level<4))
select * from T
O/P
C1 C2
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
--OVER () means on total table?
select c1, sum(C2) over () as total? from T
C1 TOTAL
1 18
1 18
1 18
2 18
2 18
2 18
3 18
3 18
3 18
--Over (partition by c1 ) , it will partiton by each value of C1?
select c1, sum(C2) over (partition by c1 ) as total? from T
C1 TOTAL
1 6
1 6
1 6
2 6
2 6
2 6
3 6
3 6
3 6
--Over (partition by C1 order by c2 ) , it will partition by c1 of each value of c1 and order by each value of c2?
with T as (select * from (select level as c1 from dual connect by level <4) , (select level as c2 from dual connect by level<5))
select c1, sum(C2) over (partition by c1 order by c2) as total? from T
C1 TOTAL
1 1
1 3
1 6
1 10
2 1
2 3
2 6
2 10
3 1
3 3
3 6
3 10
--Over (partition by C1 order by C2 rows between unbounded preceding and current row ) --it partiton by C1 for each value and order by C2 for each value?
select c1, sum(C2) over (partition by c1 order by c2 rows between? unbounded preceding and current row ) as total? from T
-- rows/range
--ex: here for partition? 1 of C2? , 1st row unbounded preceding -null current row? -1 sum(null? +1)
?--1 of C2? , 2st row unbounded preceding -1 current row? -2? ? ? sum(null+1 +2)=3?
?--1 of C2? , 3rd row unbounded preceding -1,2 current row? -3? ? sum(null+1+2? +3)
?--1 of C2? , 4th row unbounded preceding -1,2,3 current row? -4
ROWNUM C1 TOTAL
1 1 1
2 1 3
3 1 6
4 1 10
5 2 1
6 2 3
7 2 6
8 2 10
9 3 1
10 3 3
11 3 6
12 3 10
--Over(partiton by C1 order by C1 rows between 1 preceding and current row )?
select rownum, c1, sum(C2) over (partition by c1 order by c2 rows between? 1 preceding and current row ) as total? from T
--ex: here for partition? 1 of C2? , 1st row unbounded preceding -null current row? -1? ?sum(null+1)=1
?--1 of C2? , 2st row? 1 preceding -1 current row? -2? ?sum(1+2)=3
?--1 of C2? , 3rd row? 1 preceding -2 current row? -3? ?sum(2+3)=5
?--1 of C2? , 4th row? 1 preceding -3 current row? -4? ?sum(3+4)=7
?
-- Like wise we can have?
--Over(partiton by C1 order by C1 rows between unbounded preceding and unbounded following? )?
--Over(partiton by C1 order by C1 rows between current row? and 1 following? )?
--Over(partiton by C1 order by C1 rows between current row? and unbounded following? )?
ROWNUM C1 TOTAL
1 1 1
2 1 3
3 1 5
4 1 7
5 2 1
6 2 3
7 2 5
8 2 7
9 3 1
10 3 3
11 3 5
12 3 7
-- WITHIN GROUP ()
---It is used by aggrate value?
with T as (select * from (select level as c1 from dual connect by level <4) , (select level as c2 from dual connect by level<5))
select? c1, listagg(c2) within group (order by c2) from T GROUP BY C1?
--Some other agg function?
with T as (select * from (select level as c1 from dual connect by level <4) , (select level as c2 from dual connect by level<5))
select?
listagg(c2,',') within group(order by c2) listagg, Rtrim(Xmlagg (Xmlelement (e, c2||',')).extract? ( '//../text()' ), ',') xmlagg ,json_arrayagg(json_object(c2) order by c2),xmlagg(xmlelement(x,c2) ) jsonagg, json_objectagg(key 'P' value c2) jsonagg, json_query(json_objectagg(key 'P'||c2? value c2),'$.P2' with CONDITIONAL? wrapper ) jsqonquery?
,json_value(json_objectagg(key 'P'||c2? value c2),'$.P3' ) jsonvalue
?--xmlelement(k,c1), json_object(c1,c2)?
?
?from T
group by c1
LISTAGG XMLAGG JSON_ARRAYAGG(JSON_OBJECT(C2)ORDERBYC2) JSONAGG JSONAGG JSQONQUERY JSONVALUE
1,2,3,4 1,4,3,2 [{"c2":1},{"c2":2},{"c2":3},{"c2":4}] <X>1</X><X>4</X><X>3</X><X>2</X> {"P":1,"P":4,"P":3,"P":2} [2] 3
1,2,3,4 1,4,3,2 [{"c2":1},{"c2":2},{"c2":3},{"c2":4}] <X>1</X><X>4</X><X>3</X><X>2</X> {"P":1,"P":4,"P":3,"P":2} [2] 3
1,2,3,4 1,4,3,2 [{"c2":1},{"c2":2},{"c2":3},{"c2":4}] <X>1</X><X>4</X><X>3</X><X>2</X> {"P":1,"P":4,"P":3,"P":2} [2] 3
PIVOT /UNPIVOT
--- Print the table value?
with T as (select * from (select level as c1 from dual connect by level <4) , (select level as c2 from dual connect by level<4))
select * from (select 'Co'||c1 as col1 , 'Ct'||C2 col2, rownum num from T)
COL1 COL2 NUM
Co1 Ct1 1
Co1 Ct2 2
Co1 Ct3 3
Co2 Ct1 4
Co2 Ct2 5
Co2 Ct3 6
Co3 Ct1 7
Co3 Ct2 8
Co3 Ct3 9
pivot (max(col2) for col1 in ('Co1' co1,'Co2' co2,'Co3' co3))
NUM CO1 CO2 CO3
6 - Ct3 -
1 Ct1 - -
7 - - Ct1
2 Ct2 - -
8 - - Ct2
4 - Ct1 -
5 - Ct2 -
3 Ct3 - -
9 - - Ct3
unpivot (unpivotelement for unpivotcolumn in ( co1 as 'Co1',co2 as 'Co2',co3 as 'Co3'))
NUM UNPIVOTCOLUMN UNPIVOTELEMENT
6 Co2 Ct3
1 Co1 Ct1
7 Co3 Ct1
2 Co1 Ct2
8 Co3 Ct2
4 Co2 Ct1
5 Co2 Ct2
3 Co1 Ct3
9 Co3 Ct3
Magic SQL
Reverse a String ,
select listagg(substr(name,level,1),'') within group (order by rownum desc) lett from (select 'brahmanandakar' name from dual) connect by level<length(name)+1
Expand value ,
领英推荐
select regexp_substr(k,'[^,]+',1,level) ,level from (select 'KHU,BFJ,KHJJ' as k from dual) connect by regexp_instr(k||',',',',1,level)<>0
Regexp_substr
SELECT regexp_substr('a[name]bc[desfsdfsdf]ghfsdfdsdi', '\[(.*?)\]',10,1,null,1) from dual
SELECT regexp_substr('a[name]bc[desfsdfsdf]ghfsdfdsdi', '\[(.*?)\]',10,1,null,1) from dual
Parameter :
1.String,
2.regexp
3.Start of index of string to consider?
4.Occurance (first match ,second match)
5.match charactor? (i-case insensitive ,c-case sensitive,n-new line char, m-mutiline char ,x-ingore whitespace )
6.Match group?l
Thanks for reading .