Sunday 17 February 2019

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

Sunday 13 January 2019

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

Thursday 20 December 2018

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;

Friday 14 December 2018

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;

Saturday 8 December 2018

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;

Saturday 7 July 2018

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


Wednesday 6 June 2018

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

About Me

I am Siva Jeevan.LK [LEARN | CODE | TRAIN | SHARE] Passionate to answer oracle SQL & PLSQL related questions. In my day to day job, I work with technologies including Oracle, Java, Python, MongoDB, talend and UNIX. I am passionate about "DATA", “SQL” , coding & training. In my spare time, I teach database technologies , ETL etc. I am very glad that you are reading my Profile, and I value your time as well as mine. Looking forward to see you in my videos at Siva Academy Passionate to answer oracle SQL & PLSQL related questions. You can contact me via My youtube channel (Siva Academy) or drop an email at siva.k.academy@gmail.com Happy to Learn & Help :-)

Total Pageviews