Write a SQL to list “no of employees” & “name of employees” reporting to each person

select empno,ename,no_of_rep,name_of_rep
from emp,
    (select mgr,listagg(ename,',') name_of_rep,
           count(*) no_of_rep
     from emp
     group by mgr) r
where emp.empno=r.mgr(+)
order by empno;

---

select empno,ename,
      (select listagg(ename,',')
      from emp where mgr = e.empno) name_of_rep,
      (select count(1)
      from emp where mgr = e.empno) no_of_rep
from emp e
order by empno;

---

select m.empno,m.ename,
       listagg(e.ename,',') name_of_rep,
       count(e.ename) count_of_rep
from emp m, emp e
where m.empno=e.mgr(+)
group by m.empno,m.ename
order by 1

---

Compute Student Result Dashboard

Data Setup Scripts:


CREATE TABLE student (
    sno     NUMBER,
    name   VARCHAR2(30),
    mark   NUMBER,
    result varchar2(1),
dept   varchar2(5),
year     number
);

BEGIN
    FOR i IN 1..1000 LOOP
        INSERT INTO student (sno,name,mark,dept,year) 
        VALUES (i,'STUDENT_'|| i,trunc(dbms_random.value(1,100)),trunc(dbms_random.value(1,6)),trunc(dbms_random.value(1,5)));
    END LOOP;
    COMMIT;
END;
/

update student set result='P' 
where mark >=35;
update student set result='F' 
where mark <35;

update student set dept='CSE' where dept='1';
update student set dept='ECE' where dept='2';
update student set dept='EEE' where dept='3';
update student set dept='MECH' where dept='4';
update student set dept='CVE' where dept='5';


commit;

SQLs to get result dashboard

select * from (

select dept,

       'YEAR : '||year year,

       'P = '||count(decode(result,'P','P',null)) ||', F = '||count(decode(result,'F','F',null)) R_count

from student

group by dept,year)

PIVOT ( max ( R_count)

            FOR dept

            IN ( 'CSE' CSE,'ECE' ECE,'MECH' MECH,'EEE' EEE,'CVE' CVE)

        )

        order by year;

select * from (

select dept,

       year,

       'P = '||count(decode(result,'P','P',null)) ||', F = '||count(decode(result,'F','F',null)) R_count

from student

group by dept,year)

PIVOT ( max ( R_count)

            FOR year

            IN ( 1 as I,2 II,3 III,4 IV)

        );

select dept, max(case when year=1 then F_count end),

              max(case when year=2 then F_count end),

              max(case when year=3 then F_count end),

              max(case when year=4 then F_count end)

from (

select dept,year,'P = '||count(decode(result,'P','P',null)) ||', F = '||count(decode(result,'F','F',null)) F_count

from student

group by dept,year

order by 1,2)

group by dept;

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

create or replace function get_result_count (pin_dept varchar2, pin_year number)

                  return varchar2 as

   lv_cnt varchar2(100);

begin

    select 'P = '||count(decode(result,'P','P',null)) 

       ||', F = '||count(decode(result,'F','F',null)) 

    into lv_cnt

    from student

    where dept=pin_dept

    and year = pin_year;

return lv_cnt;

exception when others then

   return null;

end ;

/

select dept,get_result_count(dept,1) Y_I,

            get_result_count(dept,2) Y_II,

            get_result_count(dept,3) Y_III,

            get_result_count(dept,4) Y_IV

from(

select distinct dept

from student);

select year,get_result_count('ECE',year) ECE,

            get_result_count('CSE',year) CSE ,

            get_result_count('MECH',year) MECH,

            get_result_count('CVE',year) CVE,

            get_result_count('EEE',year) EEE

from(

select distinct year

from student);

Compute Group Salary

---

-- DATA SETUP --

create table emp_t(empno number,
                   ename varchar2(100),
                   mgr   number,
                   sal   number);

Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7839,'KING',null,1000);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7698,'BLAKE',7839,700);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7782,'CLARK',7839,500);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7566,'JONES',7839,800);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7788,'SCOTT',7566,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7902,'FORD',7566,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7369,'SMITH',7902,75);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7499,'ALLEN',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7521,'WARD',7698,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7654,'MARTIN',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7844,'TURNER',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7876,'ADAMS',7788,50);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7900,'JAMES',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7934,'MILLER',7782,300);

commit;

---

-- Query 1 --

select sum(sal)
from EMP_T
start with ENAME = 'FORD'
connect by PRIOR empno=mgr;

select EMPNO, ENAME, MGR, SAL,(select sum(sal)
                                from EMP_T
                                start with ENAME = a.ENAME
                                connect by PRIOR empno=mgr) group_sal
from emp_t a;

---

-- Query 2 --

CREATE OR REPLACE FUNCTION get_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
/
select EMPNO, ENAME, MGR, SAL,get_group_sal(a.ename) group_sal
from emp_t a;

---

-- Query 3 --

with FUNCTION fn_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
select EMPNO, ENAME, MGR, SAL,fn_group_sal(ename) group_sal
from emp_t a;

---

Query to Extract Names from mail id

---

---

create table tab1(mail_id varchar2(50));

insert into tab1 values('siva.k.academy@gmail.com');
insert into tab1 values('jeevan.anand@yahoo.com');
insert into tab1 values('ravi@outlook.com');

commit;

select * from tab1;


with d1 as (select mail_id m, 
       substr(mail_id,1, instr(mail_id,'@')-1) n,
       substr(mail_id, instr(mail_id,'@')+1) d
from tab1),
d2 as (select m,d,n,
       instr(n,'.',1,1) f_dot ,
       instr(n,'.',1,2) s_dot 
from d1),
d3 as (select m,d,n,f_dot,s_dot, 
       substr(n,1,decode(f_dot,0,length(n),f_dot-1)) f_name,
       decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1)) m_name,
       decode(f_dot+s_dot,0,null,substr(n,decode(s_dot,0,f_dot,s_dot)+1)) l_name
from d2)
select m,f_name,m_name,l_name
from d3;

---

Two dimensional array in PLSQL using VARRAY

Example to understand "Two dimensional array in PLSQL using VARRAY"

---

SET SERVEROUTPUT ON
DECLARE
    TYPE matrix_elem_type IS  VARRAY ( 3 ) OF NUMBER;
    TYPE matrix_type IS       VARRAY ( 3 ) OF matrix_elem_type;
    lv_matrix1       matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix2       matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix3_total matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix_elem   matrix_elem_type := matrix_elem_type(NULL,NULL,NULL);
    -- Local Procedure to print the array
    procedure print_array(pin_array matrix_type,pin_desc varchar2) as
    begin
    dbms_output.put_line('Printing the '||pin_desc||' ....');
    for i in pin_array.first..pin_array.last loop
        for j in pin_array(i).first..pin_array(i).last loop
           dbms_output.put(pin_array(i)(j)||'   ');
        end loop;
        dbms_output.put_line('');
    end loop;
    end;
BEGIN
    -- Assing First Array
    lv_matrix_elem := matrix_elem_type(1,2,3);
    lv_matrix1(1) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(4,5,6);
    lv_matrix1(2) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(7,8,9);
    lv_matrix1(3) := lv_matrix_elem;
    
    -- Printing the first array
    print_array(lv_matrix1,'First Array');
    
    -- Assing Second Array
    lv_matrix_elem := matrix_elem_type(11,12,13);
    lv_matrix2(1) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(14,15,16);
    lv_matrix2(2) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(17,18,19);
    lv_matrix2(3) := lv_matrix_elem;
    
    -- Printing the Second array
    print_array(lv_matrix2,'Second Array');    
    
    -- Add Array1+Array2
    
    for i in lv_matrix1.first..lv_matrix1.last loop
        for j in lv_matrix1(i).first..lv_matrix1(i).last loop
        lv_matrix_elem(j) := lv_matrix1(i)(j) + lv_matrix2(i)(j);
        end loop;
        lv_matrix3_total(i) := lv_matrix_elem;
    end loop;
    
    --Print Total Array
    print_array(lv_matrix3_total,'Total Array');    
END;

How to get unique records without using “DISTINCT” in oracle

drop table Student_list;
create table Student_list(roll_no number, stu_name varchar2(30));

Insert into Student_list (ROLL_NO,stu_name) values (1,'KING');
Insert into Student_list (ROLL_NO,stu_name) values (2,'BLAKE');
Insert into Student_list (ROLL_NO,stu_name) values (3,'CLARK');
Insert into Student_list (ROLL_NO,stu_name) values (4,'JONES');
Insert into Student_list (ROLL_NO,stu_name) values (5,'SCOTT');
Insert into Student_list (ROLL_NO,stu_name) values (6,'RAGHU');
Insert into Student_list (ROLL_NO,stu_name) values (7,'FORD');
Insert into Student_list (ROLL_NO,stu_name) values (8,'SMITH');
Insert into Student_list (ROLL_NO,stu_name) values (9,'ALLEN');
Insert into Student_list (ROLL_NO,stu_name) values (10,'WARD');
Insert into Student_list (ROLL_NO,stu_name) values (1,'KING');
Insert into Student_list (ROLL_NO,stu_name) values (5,'SCOTT');
Insert into Student_list (ROLL_NO,stu_name) values (7,'FORD');

COMMIT;

---

select * from Student_list
order by roll_no,stu_name;

---

SELECT DISTINCT ROLL_NO,STU_NAME
FROM STUDENT_LIST
ORDER BY ROLL_NO,STU_NAME;

---

SELECT UNIQUE ROLL_NO,STU_NAME
FROM STUDENT_LIST
ORDER BY ROLL_NO,STU_NAME;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
GROUP BY ROLL_NO,STU_NAME
ORDER BY ROLL_NO,STU_NAME;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
UNION 
SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
UNION 
SELECT NULL,NULL
FROM DUAL WHERE 1=2;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
INTERSECT
SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
MINUS
SELECT NULL,NULL
FROM DUAL;

---

SELECT ROLL_NO,STU_NAME FROM (
SELECT ROLL_NO,STU_NAME,
    ROW_NUMBER() over(partition by ROLL_NO,STU_NAME 
                      ORDER BY ROLL_NO,STU_NAME) R
FROM STUDENT_LIST)
where R=1;

---

SELECT ROLL_NO,STU_NAME FROM (
SELECT ROLL_NO,STU_NAME,
    RANK() over(partition by ROLL_NO,STU_NAME 
                ORDER BY ROWNUM) R
FROM STUDENT_LIST)
where R=1;

---

SELECT * FROM STUDENT_LIST
WHERE ROWID IN (
SELECT MIN(ROWID)
FROM STUDENT_LIST
GROUP BY ROLL_NO,STU_NAME)
ORDER BY ROLL_NO,STU_NAME;

---

SELECt * FROM STUDENT_LIST A
WHERE 1 = ( SELECT COUNT(1) 
            FROM STUDENT_LIST B 
            WHERE A.ROLL_NO = B.ROLL_NO
            AND A.STU_NAME = B.STU_NAME
            AND A.ROWID >= B.ROWID);

---

"Accessible by" clause

1. "Accessible by" clause is used to declare a particular unit may be referenced only by given list of units.
2. Anonymous block cannot be listed under "Accessible by" Clause, hence the units declared as "Accessible by" cannot be called dynamically.
3. "Accessible by" declared units cannot be invoked from outside of database

CREATE OR replace PROCEDURE proc_for_sal_increment ( pin_empno NUMBER,
                                                    pin_pct    NUMBER ) 
accessible BY (update_sal_wrapper)
AS
BEGIN
  UPDATE emp
  SET    sal = ( sal ) + ( sal * pin_pct / 100 )
  WHERE  empno = pin_empno;
  
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Unable to Update sal : '
  || SQLCODE);
END;

---

BEGIN
    Proc_for_sal_increment(7698, 10);
END;
/


exec proc_for_sal_increment(7698, 10); 

CREATE OR replace PROCEDURE Update_sal_wrapper
AS
BEGIN
    Proc_for_sal_increment(7698, 10);
END;
/

exec update_sal_wrapper;