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