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;

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;

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

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

• https://youtu.be/X7WFuI5eTX4
• https://youtu.be/zXxNPDDWqOs
• https://youtu.be/5WXNBwpGeUw
• https://youtu.be/917dFN2Cc6M
• https://youtu.be/g4xK03ItH8Q

To learn more on collection please check these links

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

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);

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

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;

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