OraclePLSQL--codes-concepts of FUNCTIONS

concepts of FUNCTIONS 

---extract-----

select HIRE_DATE

,      extract(year from HIRE_DATE) as year_of_HIRE_DATE

,      extract(month from HIRE_DATE) as month_of_HIRE_DATE

,      extract(day from HIRE_DATE) as day_of_HIRE_DATE

from   employees

where  FIRST_NAME = 'Adam';

HIRE_DATE	YEAR_OF_HIRE_DATE	MONTH_OF_HIRE_DATE	DAY_OF_HIRE_DATE
4/10/2005 0:00	2005	4	10

------Using the MONTHS_BETWEEN Function------

select FIRST_NAME,HIRE_Date,sysdate,months_between(sysdate,HIRE_Date)

from   employees

where  FIRST_NAME = 'Adam';

FIRST_NAME	HIRE_DATE	SYSDATE	MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
Adam	4/10/2005 0:00	7/17/2015 15:24	123.2465263

-------Using the ADD_MONTHS Function ------------

select sysdate,add_months(sysdate,1),

      add_months(sysdate,2),

      add_months(sysdate,-1)

from dual;  

SYSDATE	ADD_MONTHS(SYSDATE,1)	ADD_MONTHS(SYSDATE,2)	ADD_MONTHS(SYSDATE,-1)
7/17/2015 15:26	8/17/2015 15:26	9/17/2015 15:26	6/17/2015 15:26

-----Using the NEXT_DAY, LAST_DAY, ROUND, and TRUNC Functions -----

select sysdate

,      next_day(sysdate,'SAT') as next_sat

,      last_day(sysdate)       as last_day

,      round(sysdate,'YY')     as round_yy

,      trunc(sysdate,'CC')     as trunc_cc

from   dual;

SYSDATE	NEXT_SAT	LAST_DAY	ROUND_YY	TRUNC_CC
7/17/2015 15:28	7/18/2015 15:28	7/31/2015 15:28	1/1/2016 0:00	1/1/2001 0:00

-----Using the NVL Function -------

select PAYTERMS_ID_N, PAYTERMS_DISCPCT_N, PAYTERMS_DAYSTOPAY_N

    , 14*PAYTERMS_DISCPCT_N*nvl(PAYTERMS_DAYSTOPAY_N,0) as new

from   PAYMENT_TERMS

where  PAYTERMS_ID_N IN (26,27);

PAYTERMS_ID_N	PAYTERMS_DISCPCT_N	PAYTERMS_DAYSTOPAY_N	NEW
26	0	30	0
27	0.02	31	8.68

------DECODE vs CASE---------

 /*

  DECODE function expressions are quite difficult to read.

  CASE expressions are much more powerful.

  */

 

select job, ename

,      case greatest(msal,2500)

           when 2500 then 'cheap'

           else 'expensive'

           end as class

from   employee1

where  bdate < date '1964-01-01'

order  by decode(job,'DIRECTOR',1,'MANAGER',2,3);

JOB	ENAME	CLASS
DIRECTOR	KING	expensive
SALESREP	WARD	cheap
SALESREP	MARTIN	cheap
ADMIN	MILLER	cheap
TRAINER	FORD	expensive
SALESREP	ALLEN	cheap
TRAINER	SCOTT	expensive

----------Using the TO_CHAR and TO_NUMBER Functions -----

select 123

,      to_char(123)

,      to_char(123,'$0999.99')

,      to_char(123,'$9999.99')

,      to_char(123,'$09999.9')

,      to_number('123')

from   dual;

123	TO_CHAR(123)	TO_CHAR(123,'$0999.99')	TO_CHAR(123,'$9999.99')	TO_CHAR(123,'$09999.9')	TO_NUMBER('123')
123	123	$123.00	$123.00	$123.00	123

----Nesting the TO_CHAR and TO_DATE Functions ------

select sysdate  as today

,      to_char(sysdate,'hh24:mi:ss') as time

,      to_char(to_date('01/01/2015','dd/mm/yyyy')

              ,'Day') as new_year_2015_day

from dual;

TODAY	TIME	NEW_YEAR_2015_DAY
17-Jul-15	15:34:41	Thursday

-------TO_CHAR Formats and Case-Sensitivity -----

select to_char(sysdate,'DAY dy Dy') as day

,      to_char(sysdate,'MONTH mon') as month

from dual;

DAY	MONTH
FRIDAY    fri Fri	JULY      jul

----Relying on Implicit Datatype Conversion -----

select ename,bdate, substr(bdate,8)+18

from   employee1

where  deptno = 10;

ENAME	BDATE	SUBSTR(BDATE,8)+18
KING	17-Nov-52	70
MILLER	23-Jan-62	80
CLARK	9-Jun-65	83

-------Using the DISTINCT Option for Group Functions -----

select count(deptno), count(distinct deptno)

,      avg(comm), avg(coalesce(comm,0))

from   employee1;

COUNT(DEPTNO)	COUNT(DISTINCTDEPTNO)	AVG(COMM)	AVG(COALESCE(COMM,0))
13	3	550	169.2308

-----GROUP BY and DISTINCT ------

select   CUST_FIRST_NAME

,        count(distinct CUST_MARITAL_STATUS)

,        count(*)

from     CUSTOMERS

where CUST_STATE_PROVINCE_ID = 52610 and CUST_CITY = 'Ede' and  upper(substr(CUST_FIRST_NAME,1,1)) between 'A' and 'E'

group by CUST_FIRST_NAME

order by CUST_FIRST_NAME

CUST_FIRST_NAME	COUNT(DISTINCTCUST_MARITAL_STATUS)	COUNT(*)
Abel	0	1
Abigail	1	1
Adel	1	2
Antony	1	1
Arnold	1	1
Augustus	1	1
Babs	0	1
Beatrice	1	2
Becky	0	1
Bertilde	0	1
Bertram	1	1
Brant	0	1
Brenda	0	1
Brendon	1	1
Bryant	0	1
Bud	2	2
Buick	0	1
Byron	1	1
Calandra	1	1
Candace	2	2
Carey	1	1
Carter	1	1
Charles	0	1
Cody	1	1
Emmet	0	1
Emmett	1	1
Enos	0	1
Eva	0	1
Evan	0	1
Ezra	1	1

------HAVING Clause Example -------

select deptno, count(empno)

from   employee1

group  by deptno

having count(empno) >= 4;

DEPTNO	COUNT(EMPNO)
30	5
20	5

-----HAVING vs. WHERE -----

select   deptno, count(empno)

from     employee1

where    bdate > date '1960-01-01'

group by deptno

having   count(empno) >= 4;

DEPTNO	COUNT(EMPNO)
30	4

-----HAVING Clause Without a Group Function ----

select deptno, count(*)

from   employee1

group  by deptno

having deptno <= 20;

select deptno, count(*)

from   employee1

where  deptno <= 20

group  by deptno;

DEPTNO	COUNT(*)
20	5
10	3

-------One Way to Find Who Earns More Than the Average Salary ----

select e.empno

from   employee1 e

where  e.msal > (select avg(x.msal)

                 from employee1 x );

                

EMPNO

7566

7788

7839

7902

7782

               

------Advanced GROUP BY Features------

select  MANAGER_ID ,JOB_ID,count(EMPLOYEE_ID)

from EMPLOYEES

where manager_id is not null

group by  MANAGER_ID,JOB_ID

order by MANAGER_ID;

MANAGER_ID	JOB_ID	COUNT(EMPLOYEE_ID)
100	AD_VP	2
100	MK_MAN	1
100	PU_MAN	1
100	SA_MAN	5
100	ST_MAN	5
101	AC_MGR	1
101	AD_ASST	1
101	FI_MGR	1
101	HR_REP	1
101	PR_REP	1
102	IT_PROG	1
103	IT_PROG	4
108	FI_ACCOUNT	5
114	PU_CLERK	5
120	SH_CLERK	4
120	ST_CLERK	4
121	SH_CLERK	4
121	ST_CLERK	4
122	SH_CLERK	4
122	ST_CLERK	4
123	SH_CLERK	4
123	ST_CLERK	4
124	SH_CLERK	4
124	ST_CLERK	4
145	SA_REP	6
146	SA_REP	6
147	SA_REP	6
148	SA_REP	6
149	SA_REP	6
201	MK_REP	1
205	AC_ACCOUNT	1

--------GROUP BY ROLLUP------

select  MANAGER_ID ,JOB_ID,count(EMPLOYEE_ID)

from EMPLOYEES

where manager_id is not null

group by rollup (MANAGER_ID,JOB_ID);

MANAGER_ID	JOB_ID	COUNT(EMPLOYEE_ID)
100	AD_VP	2
100	MK_MAN	1
100	PU_MAN	1
100	SA_MAN	5
100	ST_MAN	5
100		14
101	AC_MGR	1
101	FI_MGR	1
101	HR_REP	1
101	PR_REP	1
101	AD_ASST	1
101		5
102	IT_PROG	1
102		1
103	IT_PROG	4
103		4
108	FI_ACCOUNT	5
108		5
114	PU_CLERK	5
114		5
120	SH_CLERK	4
120	ST_CLERK	4
120		8
121	SH_CLERK	4
121	ST_CLERK	4
121		8
122	SH_CLERK	4
122	ST_CLERK	4
122		8
123	SH_CLERK	4
123	ST_CLERK	4
123		8
124	SH_CLERK	4
124	ST_CLERK	4
124		8
145	SA_REP	6
145		6
146	SA_REP	6
146		6
147	SA_REP	6
147		6
148	SA_REP	6
148		6
149	SA_REP	6
149		6
201	MK_REP	1
201		1
205	AC_ACCOUNT	1
205		1
		106

-----GROUP BY CUBE-----------

select  MANAGER_ID ,JOB_ID,count(EMPLOYEE_ID)

from EMPLOYEES

where manager_id is not null

group by CUBE (MANAGER_ID,JOB_ID);

MANAGER_ID	JOB_ID	COUNT(EMPLOYEE_ID)
		106
	AD_VP	2
	AC_MGR	1
	FI_MGR	1
	HR_REP	1
	MK_MAN	1
	MK_REP	1
	PR_REP	1
	PU_MAN	1
	SA_MAN	5
	SA_REP	30
	ST_MAN	5
	AD_ASST	1
	IT_PROG	5
	PU_CLERK	5
	SH_CLERK	20
	ST_CLERK	20
	AC_ACCOUNT	1
	FI_ACCOUNT	5
100		14
100	AD_VP	2
100	MK_MAN	1
100	PU_MAN	1
100	SA_MAN	5
100	ST_MAN	5
101		5
101	AC_MGR	1
101	FI_MGR	1
101	HR_REP	1
101	PR_REP	1
101	AD_ASST	1
102		1
102	IT_PROG	1
103		4
103	IT_PROG	4
108		5
108	FI_ACCOUNT	5
114		5
114	PU_CLERK	5
120		8
120	SH_CLERK	4
120	ST_CLERK	4
121		8
121	SH_CLERK	4
121	ST_CLERK	4
122		8
122	SH_CLERK	4
122	ST_CLERK	4
123		8
123	SH_CLERK	4
123	ST_CLERK	4
124		8
124	SH_CLERK	4
124	ST_CLERK	4
145		6
145	SA_REP	6
146		6
146	SA_REP	6
147		6
147	SA_REP	6
148		6
148	SA_REP	6
149		6
149	SA_REP	6
201		1
201	MK_REP	1
205		1
205	AC_ACCOUNT	1

-------GROUPING_ID Function Example with ROLLUP-------

select  MANAGER_ID,

        case GROUPING_ID(MANAGER_ID, JOB_ID)

              when 0 then   JOB_ID

              when 1 then '**dept **'

              when 3 then '**total**'

         end   JOB_ID,

         count(EMPLOYEE_ID)

from EMPLOYEES

where manager_id is not null

group by rollup (MANAGER_ID,JOB_ID);

MANAGER_ID	JOB_ID	COUNT(EMPLOYEE_ID)
100	AD_VP	2
100	MK_MAN	1
100	PU_MAN	1
100	SA_MAN	5
100	ST_MAN	5
100	**dept **	14
101	AC_MGR	1
101	FI_MGR	1
101	HR_REP	1
101	PR_REP	1
101	AD_ASST	1
101	**dept **	5
102	IT_PROG	1
102	**dept **	1
103	IT_PROG	4
103	**dept **	4
108	FI_ACCOUNT	5
108	**dept **	5
114	PU_CLERK	5
114	**dept **	5
120	SH_CLERK	4
120	ST_CLERK	4
120	**dept **	8
121	SH_CLERK	4
121	ST_CLERK	4
121	**dept **	8
122	SH_CLERK	4
122	ST_CLERK	4
122	**dept **	8
123	SH_CLERK	4
123	ST_CLERK	4
123	**dept **	8
124	SH_CLERK	4
124	ST_CLERK	4
124	**dept **	8
145	SA_REP	6
145	**dept **	6
146	SA_REP	6
146	**dept **	6
147	SA_REP	6
147	**dept **	6
148	SA_REP	6
148	**dept **	6
149	SA_REP	6
149	**dept **	6
201	MK_REP	1
201	**dept **	1
205	AC_ACCOUNT	1
205	**dept **	1
	**total**	106

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

/*

Produce a matrix report (one column per department, one row for each job) where each cell

shows the number of employees for a specific department and a specific job. In a single

SQL statement, it is impossible to dynamically derive the number of columns needed,

so you may assume you have three departments only: 10, 20, and 30.

*/

select   job

    ,        count(case

                   when deptno <> 10

                   then null

                   else deptno

                   end                ) as dept_10

    ,        sum(case deptno

               when 20

               then 1

                else 0

                end                  ) as dept_20

   ,        sum(decode(deptno,30,1,0)) as dept_30

 from      employee1

  group by  job;

JOB	DEPT_10	DEPT_20	DEPT_30
TRAINER	0	4	0
ADMIN	1	0	1
SALESREP	0	0	4
MANAGER	1	1	0
DIRECTOR	1	0	0

 

 

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

/*

 we have table of customers so filters the tbale a.c to thier gender and as well as count

 their MARITAL_STATUS

*/

 ---1st method-----

SELECT CUST_GENDER,

SUM(CASE WHEN CUST_MARITAL_STATUS = 'single' THEN 1

ELSE 0 END )AS SINGLE_COUNT,

SUM(CASE WHEN CUST_MARITAL_STATUS = 'married' THEN 1

ELSE 0 END )AS MARRIED_COUNT

,SUM(CASE WHEN CUST_MARITAL_STATUS IS NULL THEN 1

ELSE 0 END )AS UNKNOWN_COUNT

FROM CUSTOMERS

GROUP BY CUST_GENDER;

---2nd way------

SELECT * FROM (

   SELECT CUST_GENDER,

   CASE

      WHEN CUST_MARITAL_STATUS = 'single' THEN 'single'

      WHEN CUST_MARITAL_STATUS = 'married' THEN 'married'

      ELSE 'NULL' END AS CUST_MARITAL_STATUS

   FROM CUSTOMERS

)

PIVOT

(

   COUNT(CUST_MARITAL_STATUS)

   FOR CUST_MARITAL_STATUS IN ('single' AS SINGLE ,'married' AS MARRIED,'NULL' AS UNKNOWN)

);

CUST_GENDER	SINGLE_COUNT	MARRIED_COUNT	UNKNOWN_COUNT
M	13035	9439	11600
F	6059	4814	5828