SQL to count number of vowels

SQL to count number of occurrences of a vowels

create table emp(empno number,ename varchar2(100));

WITH ds AS (

    SELECT empno,

           upper(ename)      ename,

           length(ename)     l

    FROM emp)

SELECT *

FROM

    (

        SELECT empno,ename,c,COUNT(*) cnt

        FROM

            (

                SELECT empno, ename,substr(ename, r, 1) c

                FROM

                    ds,

                    LATERAL ( SELECT ROWNUM r

                              FROM dual

                              CONNECT BY level <= l)

            )

        WHERE

            c IN ('A','E','I','O','U')

        GROUP BY empno,ename,c

    ) PIVOT ( MAX ( cnt ) FOR c IN ( 'A','E','I','O','U' ));

sql to summarize data

drop table t;

create table t(d date, v varchar2(1));

insert into t values ( sysdate+1, 'A');

insert into t values ( sysdate+2, 'A');

insert into t values ( sysdate+3, 'B');

insert into t values ( sysdate+4, 'A');

insert into t values ( sysdate+5, 'A');

insert into t values ( sysdate+6, 'A');

insert into t values ( sysdate+7, 'B');

insert into t values ( sysdate+8, 'B');

insert into t values ( sysdate+9, 'C');

insert into t values ( sysdate+10, 'C');

insert into t values ( sysdate+11, 'A');

insert into t values ( sysdate+12, 'A');

commit;

select distinct v, min(d) over(partition by c)d , count(c) over(PARTITION by c) cnt

from(

select d,v,sum(c) over(order by d) c

from (

select d,v,case when v <> nvl(lag(v,1) over(order by d),v) then 1 else 0 end c

from t))

order by 2;

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

drop table tab_chart;

create table tab_chart(TYPE_OF_CHART varchar(100), DATE_OF_CHART date , TITLE varchar(100), ARTIST varchar(100));

insert into tab_chart values('Albums',to_date('27-02-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('06-03-83','dd-mm-yyyy'),'WAR','U2');

insert into tab_chart values('Albums',to_date('13-03-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('20-03-83','dd-mm-yyyy'),'THE HURTING','TEARS FOR FEARS');

insert into tab_chart values('Albums',to_date('27-03-83','dd-mm-yyyy'),'THE FINAL CUT','PINK FLOYD');

insert into tab_chart values('Albums',to_date('03-04-83','dd-mm-yyyy'),'THE FINAL CUT','PINK FLOYD');

insert into tab_chart values('Albums',to_date('10-04-83','dd-mm-yyyy'),'FASTER THAN THE SPEED OF NIGHT','BONNIE TYLER');

insert into tab_chart values('Albums',to_date('17-04-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');

insert into tab_chart values('Albums',to_date('24-04-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');

insert into tab_chart values('Albums',to_date('01-05-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');

insert into tab_chart values('Albums',to_date('08-05-83','dd-mm-yyyy'),'TRUE','SPANDAU BALLET');

insert into tab_chart values('Albums',to_date('15-05-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('22-05-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('29-05-83','dd-mm-yyyy'),'THRILLER','SOME OTHER ARTIST');

insert into tab_chart values('Albums',to_date('05-06-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('12-06-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');

insert into tab_chart values('Albums',to_date('19-06-83','dd-mm-yyyy'),'SYNCHRONICITY','THE POLICE');

insert into tab_chart values('Albums',to_date('26-06-83','dd-mm-yyyy'),'SYNCHRONICITY','THE POLICE');

insert into tab_chart values('Albums',to_date('03-07-83','dd-mm-yyyy'),'FANTASTIC','WHAM!');

insert into tab_chart values('Singles',to_date('29-01-60','dd-mm-yyyy'),'STARRY EYED','MICHAEL HOLLIDAY');

insert into tab_chart values('Singles',to_date('05-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('12-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('19-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('26-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');

insert into tab_chart values('Singles',to_date('04-03-60','dd-mm-yyyy'),'POOR ME','ADAM FAITH');

insert into tab_chart values('Singles',to_date('10-03-60','dd-mm-yyyy'),'POOR ME','ADAM FAITH');

insert into tab_chart values('Singles',to_date('17-03-60','dd-mm-yyyy'),'RUNNING BEAR','JOHNNY PRESTON');

insert into tab_chart values('Singles',to_date('24-03-60','dd-mm-yyyy'),'RUNNING BEAR','JOHNNY PRESTON');

insert into tab_chart values('Singles',to_date('31-03-60','dd-mm-yyyy'),'MY OLD MAN''S A DUSTMAN','LONNIE DONEGAN');

commit;

select distinct TYPE_OF_CHART, min(DATE_OF_CHART) over(partition by TYPE_OF_CHART,T) DATE_OF_CHART

, TITLE, ARTIST, count(*) over(partition by TYPE_OF_CHART,T) cnt

from (

select TYPE_OF_CHART, DATE_OF_CHART, TITLE, ARTIST, sum(t) over(partition by TYPE_OF_CHART order by DATE_OF_CHART) T

from (

select TYPE_OF_CHART, DATE_OF_CHART, TITLE, ARTIST, 

case when TITLE||ARTIST <>  nvl(lag(TITLE||ARTIST,1) 

over(partition by TYPE_OF_CHART order by DATE_OF_CHART),TITLE||ARTIST) then 1 else 0 end T

from tab_chart))

order by 1,2;

SQL to find max value across row and column

SQL to find max value across row and column

drop table t;
create table t(c1 number, c2 number, c3 number);

insert into t values(1,2,3);
insert into t values(4,5,6);
insert into t values(7,8,9);

commit;

select
max(greatest(c1,c2,c3)) col_value
from t;

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

with d as (
select 'C1' col_name,max(c1) val from t
union
select 'C2',max(c2) from t
union
select 'C3',max(c3) from t)
select *
from d
where val = (select max(val) from d);

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

with d as (select 'C1' col_name,c1 val from t
union
select 'C2',c2 from t
union
select 'C3',c3 from t)
select * from (
select col_name,case when val =  max(val) over() then val end val
from d)
where val is not null;

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

select column_name,col_val from (
select column_name,col_val, max(col_val) over() m_val
from t
UNPIVOT (col_val for column_name in (c1,c2,c3)))
where col_val = m_val

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

SQL Group employees into teams

create table team(ename varchar2(30), city varchar2(100));

insert into team values('Veda','Pune');

insert into team values('Shruthi','Pune');

insert into team values('Apoorva','Pune');

insert into team values('Karthik','Pune');

insert into team values('Jhon','Pune');

insert into team values('Shyam','Delhi');

insert into team values('Ram','Delhi');

insert into team values('Somen','Kolkata');

commit;

WITH d AS (

SELECT ename,

   city,

   ceil(ROW_NUMBER() OVER(PARTITION BY city ORDER BY ROWNUM) / 2) t 

FROM

team

)

SELECT

city,

listagg(ename, ',') team_members,

'Team'|| ROW_NUMBER() OVER(ORDER BY city) team_name

FROM d

GROUP BY city,t

ORDER BY 3;

Hierarchical EMP data in Flat format

Oracle SQL Practical question Emp Hiearchical data in Flat format

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

select regexp_substr(EMPNO_LIST, '\w+', 1, 1) L1_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 1) L1_MGR,

   1 LV1,

   regexp_substr(EMPNO_LIST, '\w+', 1, 2) L2_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 2) L2_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 2) is not null then 2 end LV2,       

   regexp_substr(EMPNO_LIST, '\w+', 1, 3) L3_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 3) L3_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 3) is not null then 3 end LV3,

   regexp_substr(EMPNO_LIST, '\w+', 1, 4) L4_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 4) L4_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 4) is not null then 4 end LV4,

   regexp_substr(EMPNO_LIST, '\w+', 1, 5) L5_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 5) L5_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 5) is not null then 5 end LV5                            

from (

select     trim(',' from sys_connect_by_path(ename,',')) ename_list,

   trim(',' from sys_connect_by_path(empno,',')) empno_list,

CONNECT_BY_ISLEAF ISLEAF

from emp

start with mgr is null

connect by prior empno=mgr)

where ISLEAF=1;

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

select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2, L3_EMPNO, L3_ENAME,LV3, L4_EMPNO, L4_ENAME, LV4,

       l5.empno L5_EMPNO,l5.ename L5_ENAME,case when l5.empno is not null then 5 end LV5

    from (

    select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2, L3_EMPNO, L3_ENAME, LV3,

           l4.empno L4_EMPNO,l4.ename L4_ENAME,case when l4.empno is not null then 4 end LV4

    from (

        select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2,

               l3.empno L3_EMPNO,l3.ename L3_ENAME,case when l3.empno is not null then 3 end LV3

        from (

            select L1_EMPNO, L1_ENAME, LV1,

                   l2.empno L2_EMPNO, l2.ename L2_ENAME, case when l2.empno is not null then 2 end LV2

            from (

                SELECT empno l1_empno,ename l1_ename,1 lv1

                FROM emp

                where mgr IS NULL) l1,

                emp l2

            where l2.mgr = l1.l1_empno) l2,

            emp l3

        where l3.mgr = l2.L2_EMPNO ) l3,

    emp l4

    where l4.mgr(+) = l3.L3_EMPNO) L4,

    emp l5

where l5.mgr(+) = l4.L4_EMPNO 

order by 1,4;

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

Delete duplicate records in SQL

drop table emp_t;
create table emp_t(empno number,ename varchar2(100), job varchar2(100), sal number, deptno NUMBER);

insert into emp_t values(1001,'KING','PRESIDENT',100000,10);
insert into emp_t values(1002,'RAGHU','MANAGER', 80000,20);
insert into emp_t values(1003,'FORD','MANAGER',75000,10);
insert into emp_t values(1002,'RAGHU','MANAGER', 80000,20);
insert into emp_t values(1004,'JAMES','MANAGER',82000,30);
insert into emp_t values(1005,'WARD','SALESMAN',50000,10);
insert into emp_t values(1004,'JAMES','MANAGER',82000,30);
insert into emp_t values(1006,'FORD','SALESMAN',40000,10);
insert into emp_t values(1004,'JAMES','MANAGER',82000,30);
insert into emp_t values(1007,'SMITH','SALESMAN',45000,10);

commit;

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

Method 1:
delete from emp_t
where rowid not in (
select max(rowid)
from emp_t
group by EMPNO, ENAME, JOB, SAL, DEPTNO);

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

Method 2:
delete
from emp_t a
where rowid > ( select min(rowid) from emp_t b
                where a.empno = b.empno and a.ename = b.ename and a.job=b.job and a.sal= b.sal and a.deptno = b.deptno);

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

Method 3:
delete from emp_t where rowid in (     
select rowid from (
select rowid,
       row_number() over(partition by EMPNO, ENAME, JOB, SAL, DEPTNO order by rowid) r
from emp_t)
where r >1);

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

oracle compound trigger to avoid mutating trigger

drop trigger trig_validate_sal;
drop trigger trig_before_up_sal;
drop trigger comp_tr_log_changes;
drop package pkg1;

drop table emp_t;
drop table emp_sal_log;

create table emp_t(empno number primary key, ename varchar2(100),deptno number, job varchar2(10),sal number);
create table emp_sal_log(empno number, update_log varchar2(1000));


insert into emp_t values(1000, 'KING',10,'CEO',150000);

insert into emp_t values(1001, 'RAVI',10,'DIRECTOR',80000);
insert into emp_t values(1002, 'SURYA',10,'SR MANAGER',70000);
insert into emp_t values(1003, 'RAGHU',10,'MANAGER',60000);
insert into emp_t values(1004, 'SCOTT',10,'LEAD',50000);
insert into emp_t values(1005, 'SMITH',10,'DEVELOPER',40000);
insert into emp_t values(1006, 'VARUN',10,'QA',40000);

commit;

----------------
-- Trigger to check if the sal greater than 100000, if yes, log the error message, else update sucessfully


create or replace trigger trig_validate_sal
before update of sal on emp_t
for each row
declare
lv_max_sal number := 100000;
begin

if :new.sal < lv_max_sal then
insert into emp_sal_log values(:new.empno,
    'Salary Updates successfully : '
    ||' OLD SAL = '||:old.sal||', NEW SAL = '||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
    'Salary NOT UPDATED : Employee salary cannot be more than '
    ||lv_max_sal);
end if;
end;
/

update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;

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

-- Trigger to check if the sal greater than sal of CEO, if yes, log the error message, else update successfully., but for CEO update should go through.

create or replace trigger trig_validate_sal
before update of sal on emp_t
for each row
declare
lv_ceo_sal number;
begin

select sal into lv_ceo_sal
from emp_t
where job = 'CEO'
and deptno= :new.deptno;

if (:new.sal < lv_ceo_sal and :old.job <> 'CEO') or (:old.job = 'CEO') then
insert into emp_sal_log values(:new.empno,
       'Salary Updates successfully : '
   ||' OLD SAL = '||:old.sal||', NEW SAL = '||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
     'Salary NOT UPDATED : Employee salary cannot be more than '
||lv_ceo_sal);
end if;

end;
/


update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- Lets see how to avoid mutating error using packaged global variable


drop trigger trig_validate_sal;

create or replace package pkg1 as
lv_ceo_sal number;
end;
/

create or replace trigger trig_before_up_sal
before update of sal on emp_t
declare
lv_max_sal number;
begin

select sal into pkg1.lv_ceo_sal
from emp_t
where job = 'CEO';

end;
/

create or replace trigger trig_validate_sal
before update of sal on emp_t
for each row
declare
begin

if (:new.sal < pkg1.lv_ceo_sal and :old.job <> 'CEO')
   or (:old.job = 'CEO') then
insert into emp_sal_log values(:new.empno,
   'Salary Updates successfully : '
   ||' OLD SAL = '||:old.sal||', NEW SAL = '||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
   'Salary NOT UPDATED : Employee salary cannot be more than '
   ||pkg1.lv_ceo_sal);  
end if;
end;
/

update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;

update emp_t set sal = 160000 where empno = 1000;

select * from emp_sal_log;

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

-- Lets see how to avoid mutating error using compound trigger

drop trigger trig_validate_sal;
drop trigger trig_before_up_sal;
drop package pkg1;

CREATE OR REPLACE TRIGGER comp_tr_log_changes
  FOR UPDATE ON EMP_T COMPOUND TRIGGER
lv_ceo_sal number;
  BEFORE STATEMENT IS
  BEGIN
select sal
into lv_ceo_sal
from emp_t
where job = 'CEO';
  END BEFORE STATEMENT;


  BEFORE EACH ROW IS
  BEGIN
if (:new.sal < lv_ceo_sal and :old.job <> 'CEO') or (:old.job = 'CEO')  then
insert into emp_sal_log values(:new.empno,
'Salary Updates successfully : '||' OLD SAL = '||:old.sal||', NEW SAL = '||:new.sal);
else
:new.sal := :old.sal;
insert into emp_sal_log values(:new.empno,
'Salary NOT UPDATED : Employee salary cannot be more than '||lv_ceo_sal);           
end if;
  END BEFORE EACH ROW;

END comp_tr_log_changes;
/


select * from emp_t;

update emp_t set sal = 45000 where empno = 1005;

select * from emp_sal_log;


update emp_t set sal = 150000 where empno = 1006;

select * from emp_sal_log;

update emp_t set sal = 160000 where empno = 1000;

select * from emp_sal_log;