sql functions

SQL FUNCTIONS
EMP TABLE

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- -----
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

DEPT TABLE

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SALGRADE TABLE

SQL> select * from salgrade;

GRADE LOSAL HISAL DEPTNO
---------- ---------- ---------- ----------
1 700 1200 10
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
10

Types of SQL Functions:

I ) Single-row Functions:
The functions operate on single row which in turn result only one row.

Character functions.
Numeric functions.
Date functions.
Conversion functions.

II ) Multiple-row Functions:
Functions take an input of multiple rows and return only a subset of row. They are called as Group functions.

Case-conversion Functions:

Convert case for character strings.


S.NO Function Result
1 LOWER(‘SQL Course’) sql course
2 UPPER(‘SQL Course’) SQL COURSE
3 INITCAP(‘SQL Course’) Sql Course


SQL> select lower ('SQL Course'), upper('SQL Course'), initcap('SQL Course') from dual;

LOWER('SQL UPPER('SQL INITCAP('S
---------- ---------- ----------
sql course SQL COURSE Sql Course

1. Display the employee number, name and deptno for employee ‘blake’.

SQL> select empno,ename,deptno from emp where lower(ename)='blake';

EMPNO ENAME DEPTNO
---------- ---------- ----------
7698 BLAKE 30

Note: Here, ename are stored in upper case strings.

2. Display the names of all employees in lower case strings.

SQL> select lower(ename),initcap(ename) from emp;

LOWER(ENAM INITCAP(EN
---------- ----------
smith Smith
allen Allen
ward Ward
jones Jones
martin Martin
blake Blake
clark Clark
scott Scott
king King
turner Turner
adams Adams
james James
ford Ford
miller Miller

14 rows selected.

Similarly it works for UPPER Function.

Character Functions:


S.NO FUNCTION RESULT REMARKS
1 CONCAT(‘Good’,’String’) GoodString Concatenates two strings and adds together
2 SUBSTR(‘String’,1,3) Str Extracts a string of determined length
3 LENGTH(‘String’) 6 Return the number of character in the string
4 INSTR(‘String’,’r’) 3 Returns the numeric position of the character
5 LPAD(sal,10,’*’) ******5000 Pads the character specified to the left
6 RPAD(sal,10,’*’) 5000****** Pads the character specified to the right

SQL> select concat('Good','String'),substr('String',1,3), length('string'), instr('String','r') from dual;

CONCAT('GO SUB LENGTH('STRING') INSTR('STRING','R')
---------- --- ---------------- -------------------
GoodString Str 6 3


SQL> select ename,sal,lpad(sal,10,'*'),rpad(sal,10,'*') from emp;

ENAME SAL LPAD(SAL,1 RPAD(SAL,1
---------- ---------- ---------- ----------
SMITH 800 *******800 800*******
ALLEN 1600 ******1600 1600******
WARD 1250 ******1250 1250******
JONES 2975 ******2975 2975******
MARTIN 1250 ******1250 1250******
BLAKE 2850 ******2850 2850******
CLARK 2450 ******2450 2450******
SCOTT 3000 ******3000 3000******
KING 5000 ******5000 5000******
TURNER 1500 ******1500 1500******
ADAMS 1100 ******1100 1100******
JAMES 950 *******950 950*******
FORD 3000 ******3000 3000******
MILLER 1300 ******1300 1300******

14 rows selected.

3. Display name of the employees, length of the name, concatenate job with name, position of the character ‘A’ in the name for employees whose job contains ‘SALES’ as prefix.

SQL> select ename,concat(ename,job),length(ename),instr(ename,'A') from emp where substr(job,1,5)='SALES';

ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')
---------- ------------------- ------------- ----------------
ALLEN ALLENSALESMAN 5 1
WARD WARDSALESMAN 4 2
MARTIN MARTINSALESMAN 6 2
TURNER TURNERSALESMAN 6 0

Here ename and job are added together. Length is followed for the ename, INSTR returns the position of ‘A’ in ename. Where clause retrives the first five characters and checks with ‘SALES’ literal.


Number Functions:


S.NO FUNCTION QUERY OUTPUT
1 ABS Select abs(-15) from dual; 15
2 CEIL Select ceil(44.778) from dual; 45
3 POWER Select power(4,2) from dual; 16
4 FLOOR Select floor(100.2) from dual; 100
5 MOD Select mod(10,3) from dual; 1
6 ROUND Select round(100.256,2) from dual; 100.26
7 TRUNC Select trunk(100.256,2) from dual; 100.25
8 SQRT Select sqrt(4) from dual; 2


SQL> select abs(-15),ceil(44.778),power(4,2),floor(100.2) from dual;

ABS(-15) CEIL(44.778) POWER(4,2) FLOOR(100.2)
---------- ------------ ---------- ------------
15 45 16 100

SQL> select mod(10,3),round(100.256,2),trunc(100.256,2),sqrt(4) from dual;
MOD(10,3) ROUND(100.256,2) TRUNC(100.256,2) SQRT(4)
---------- ---------------- ---------------- ----------
1 100.26 100.25 2



Date Functions:

Stores date with century.
SYSDATE is a function which return the system date and time.
DUAL is a dummy table used to view SYSDATE.
DUAL table is owned but the sysuser. It contains one column called dummy and the only one row with the value X.


SQL> select *from dual;

D
-
X


Arithmetic with Dates:

4. Find the number of weeks worked by the employees belong to dept. number is 10.

SQL> select sysdate from dual;

SYSDATE
---------
05-MAR-09


SQL> select ename,(sysdate-hiredate)/7"WEEKS"from emp where deptno=10;

ENAME WEEKS
---------- ----------
CLARK 1447.36604
KING 1424.36604
MILLER 1414.79462







Date functions:


S.NO FUNCTION RESULT
1 MONTHS_BETWEEN(date1,date2) Number of months between two dates.
2 ADD_MONTHS(date,n) Add calendar months to date.
3 NEXT_DAY(date,’char’) Return the date of next character day of the week with the date.
4 LAST_DAY(date) Returns the last day of the month of the date specified.
5 ROUND(date,format) Round specified date in specified format.



SQL> select months_between(sysdate,hiredate)"NO.OF MONTHS",months_between(sysdate,hiredate)/12"NO.OF YEARS" from emp;

NO.OF MONTHS NO.OF YEARS
------------ -----------
338.631047 28.2192539
336.534272 28.0445227
336.469756 28.0391463
335.114917 27.9262431
329.276208 27.439684
334.147176 27.845598
332.889111 27.7407593
262.56653 21.8805442
327.631047 27.3025872
329.921369 27.4934474
261.437498 21.7864582
327.082659 27.2568883
327.082659 27.2568883
325.437498 27.1197915

14 rows selected.

SQL> select add_months(sysdate,3) from dual;

ADD_MONTH
---------
05-JUN-09


SQL> select next_day(sysdate,'mon') from dual;

NEXT_DAY(
---------
09-MAR-09



SQL> select next_day(sysdate,'tue')from dual;

NEXT_DAY(
---------
10-MAR-09

SQL> select next_day(sysdate,'sun') from dual;

NEXT_DAY(
---------
08-MAR-09


SQL> select last_day(sysdate)from dual;

LAST_DAY(
---------
31-MAR-09


SQL> select last_day(sysdate)from dual;

LAST_DAY(
---------
31-MAR-09


SQL> select round(sysdate,'MONTH')from dual;

ROUND(SYS
---------
01-MAR-09


SQL> select round(sysdate,'year')from dual;

ROUND(SYS
---------
01-JAN-09


SQL> select trunc(sysdate,'MONTH')from dual;

TRUNC(SYS
---------
01-MAR-09

SQL> select trunc(sysdate,'year')from dual;

TRUNC(SYS
---------
01-JAN-09
Explicit Date Type Conversions:

TO_NUMBER TO_DATE


NUMBER CHARACTER DATE



TO_CHAR TO_CHAR

TO CHAR With Dates:

TO_CHAR(date, ’format’)

The format mode enclosed in single quotation marks and includes any valid date format.

DATE FORMATS:


YYYY Full year in months.
YEAR Year spelled out.
MM 2- digit value for month.
MONTH Full name of the month.
DY 3-Letter abbreviation.
DAY Full name of the day.


SQL> select to_char(sysdate,'year')from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
two thousand nine


SQL> select to_char(sysdate,'month')from dual;

TO_CHAR(S
---------
march


SQL> select to_char(sysdate,'day')from dual;

TO_CHAR(S
---------
thursday


SQL> select to_char(sysdate,'dy')from dual;

TO_
---
thu


SQL> select to_char(sysdate,'yyyy')from dual;

TO_C
----
2009


SQL> select to_char(sysdate,'year')from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
two thousand nine


SQL> select to_char(sysdate,'MM')from dual;

TO
--
03


SQL> select to_char(sysdate,'dd')from dual;

TO
--
05


SQL> select to_char(hiredate,'year')from emp;

TO_CHAR(HIREDATE,'YEAR')
------------------------------------------
nineteen eighty
nineteen eighty-one
nineteen eighty-one
nineteen eighty-one
nineteen eighty-one
nineteen eighty-one
nineteen eighty-one
nineteen eighty-seven nineteen eighty-one nineteen eighty-one nineteen eighty-seven
nineteen eighty-one
nineteen eighty-one
nineteen eighty-two

14 rows selected.


SQL> select length(to_char(sysdate,'year'))from dual;

LENGTH(TO_CHAR(SYSDATE,'YEAR'))
-------------------------------
17


TO_CHAR with Number:

TO_CHAR(number, format)

To display a number value as a character.


9 Represents a number
0 Forces a zero to be displayed
$ Places a $ sign
L Uses the local currency symbol
. Prints a decimal point

SQL> select to_char(8,'$9')from dual;

TO_
---
$8


SQL> select to_char(234,'999')from dual;

TO_C
----
234


SQL> select length(to_char(234,'999'))from dual;

LENGTH(TO_CHAR(234,'999'))
--------------------------
4


SQL> select to_char(678,'$999')from dual;

TO_CH -----
$678



SQL> select length(to_char(678,'$999'))from dual;

LENGTH(TO_CHAR(678,'$999'))
---------------------------
5


SQL> select to_char(678,'999000')from dual;

TO_CHAR
-------
678


SQL> select to_char(678,'000999')from dual;

TO_CHAR
-------
000678


SQL> select to_char(678,'00099999')from dual;

TO_CHAR(6
---------
00000678


SQL> select to_char(678,'099999')from dual;

TO_CHAR
-------
000678


SQL> select to_char(678,'L999')from dual;

TO_CHAR(678,'L
--------------
$678




SQL> select to_char(678,'999.')from dual;

TO_CH
-----
678.


SQL> select to_char(678,'999.999')from dual;

TO_CHAR(
--------
678.000


SQL> select to_char(678,'099999999.999')from dual;

TO_CHAR(678,'0
--------------
000000678.000


SQL> select to_char(hiredate,'dd mm yyyy day HH:MM:SS') from emp;

TO_CHAR(HIREDATE,'DDMMYYYYDAY
-----------------------------
17 12 1980 wednesday 12:12:00
20 02 1981 friday 12:02:00
22 02 1981 sunday 12:02:00
02 04 1981 thursday 12:04:00
28 09 1981 monday 12:09:00
01 05 1981 friday 12:05:00
09 06 1981 tuesday 12:06:00
19 04 1987 sunday 12:04:00
17 11 1981 tuesday 12:11:00
08 09 1981 tuesday 12:09:00
23 05 1987 saturday 12:05:00
03 12 1981 thursday 12:12:00
03 12 1981 thursday 12:12:00
23 01 1982 saturday 12:01:00

14 rows selected.


SQL> select to_char(hiredate,'ddspth')from emp;

TO_CHAR(HIREDA
--------------
seventeenth
twentieth
twenty-second
second
twenty-eighth
first
ninth
nineteenth
seventeenth
eighth
twenty-third
third
third
twenty-third

14 rows selected.


SQL> select to_char(hiredate,'ddspth')||'of'||to_char (hiredate,'MONTH')from emp;

TO_CHAR(HIREDATE,'DDSPTH'
-------------------------
seventeenthofDECEMBER
twentiethofFEBRUARY
twenty-secondofFEBRUARY
secondofAPRIL
twenty-eighthofSEPTEMBER
firstofMAY
ninthofJUNE
nineteenthofAPRIL
seventeenthofNOVEMBER
eighthofSEPTEMBER
twenty-thirdofMAY
thirdofDECEMBER
thirdofDECEMBER
twenty-thirdofJANUARY

14 rows selected.


SQL> select to_char(hiredate,'DDSPTH')||'of'||to_char (hiredate,'MONTH')from emp;

TO_CHAR(HIREDATE,'DDSPTH'
-------------------------
SEVENTEENTHofDECEMBER
TWENTIETHofFEBRUARY
TWENTY-SECONDofFEBRUARY
SECONDofAPRIL
TWENTY-EIGHTHofSEPTEMBER
FIRSTofMAY
NINTHofJUNE
NINETEENTHofAPRIL
SEVENTEENTHofNOVEMBER
EIGHTHofSEPTEMBER
TWENTY-THIRDofMAY THIRDofDECEMBER
THIRDofDECEMBER
TWENTY-THIRDofJANUARY

14 rows selected.


SQL> select to_char(hiredate,'DDSPTH')||'OF'||to_char (hiredate,'MONTH')||'IN THE YEAR OF'||to_char(hiredate,'YEAR') from emp;

TO_CHAR(HIREDATE,'DDSPTH')||'OF'||TO_CHAR(HIREDATE,'MONTH')||'INTHEYEAROF'||TO_C
--------------------------------------------------------------------------------
SEVENTEENTHOFDECEMBER IN THE YEAR OFNINETEEN EIGHTY
TWENTIETHOFFEBRUARY IN THE YEAR OFNINETEEN EIGHTY-ONE
TWENTY-SECONDOFFEBRUARY IN THE YEAR OFNINETEEN EIGHTY-ONE
SECONDOFAPRIL IN THE YEAR OFNINETEEN EIGHTY-ONE
TWENTY-EIGHTHOFSEPTEMBERIN THE YEAR OFNINETEEN EIGHTY-ONE
FIRSTOFMAY IN THE YEAR OFNINETEEN EIGHTY-ONE
NINTHOFJUNE IN THE YEAR OFNINETEEN EIGHTY-ONE
NINETEENTHOFAPRIL IN THE YEAR OFNINETEEN EIGHTY-SEVEN
SEVENTEENTHOFNOVEMBER IN THE YEAR OFNINETEEN EIGHTY-ONE
EIGHTHOFSEPTEMBERIN THE YEAR OFNINETEEN EIGHTY-ONE
TWENTY-THIRDOFMAY IN THE YEAR OFNINETEEN EIGHTY-SEVEN
THIRDOFDECEMBER IN THE YEAR OFNINETEEN EIGHTY-ONE
THIRDOFDECEMBER IN THE YEAR OFNINETEEN EIGHTY-ONE
TWENTY-THIRDOFJANUARY IN THE YEAR OFNINETEEN EIGHTY-TWO

14 rows selected.


TO_NUMBER and TO_DATE:

A character string to a number format using the TO_NUMBER function

TO_NUMBER (char);

A character sting to date format using ht TO_DATE function

TO_DATE ( char,[‘format’] );

SQL> select to_number('9')+to_number('8')from dual;

TO_NUMBER('9')+TO_NUMBER('8')
-----------------------------
17

SQL> select to_number('25')/to_number('5')from dual;

TO_NUMBER('25')/TO_NUMBER('5')
------------------------------
5


SQL> select to_date('26-feb-07')from dual;

TO_DATE('
---------
26-FEB-07


SQL> select add_months(to_date('12-oct-99','DD-MM-YY'),5)from dual;

ADD_MONTH
---------
12-MAR-00

Question----------------

Using The NVL Function:

5. Display name, salary, commission, and their annual salary with commission for all employees.

SQL> select ename,sal,comm,(sal*12)+nvl(comm,0)from emp;

Substitutes zero where the commission is NULL.

ENAME SAL COMM (SAL*12)+NVL(COMM,0)
---------- ---------- ---------- --------------------
SMITH 800 9600
ALLEN 1600 300 19500
WARD 1250 500 15500
JONES 2975 35700
MARTIN 1250 1400 16400
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 0 18000
ADAMS 1100 13200
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600

14 rows selected.


Using DECODE function:

Case or if – then – else statement

DECODE (column | Expression, search 1, result 1, Search 2, result 2,…,Search n, result n, default);

6. Write a query that hikes the salary of employees whose job is ‘ANALYST’ by 10% ‘CLERK’ by 15%,’MANAGER’ by 20%.

SQL> select job,sal,decode(job,'ANALYST',sal*1.1,
'CLERK',sal*1.5,'MANAGER',sal*1.20,sal)"REVISAED SALARY"from emp;

JOB SAL REVISAED SALARY
--------- ---------- ---------------
CLERK 800 1200
SALESMAN 1600 1600
SALESMAN 1250 1250
MANAGER 2975 3570
SALESMAN 1250 1250
MANAGER 2850 3420
MANAGER 2450 2940
ANALYST 3000 3300
PRESIDENT 5000 5000
SALESMAN 1500 1500
CLERK 1100 1650
CLERK 950 1425
ANALYST 3000 3300
CLERK 1300 1950

14 rows selected.

JOIN

A join basically involves more than one table to interact with,

SYNTAX:

SELECT table1.column,tablle2.column FROM table1,table2 WHERE table1.column=table2.column;

WHERE Clause specifies the join conditions.

CARTESIAN PRODUCT:

A Cartesian product is formed when

A join condition is completely omitted
All rows in the first table are joined to all rows in the second table.

7. Display all possible combination of tuples for emp with dept.

Types of Join:

I) Inner Join
i) Equi join
ii) Non-Equi join
iii) Self join

Retrives rows that matches the condition of the WHERE Clause.

II) Outer Join

Retrives rows that does not match the conditions of the WHERE Clause along with the matched rows.


Equi Join:

8. Display all employees name,number,dept number and their dept location.

SQL> select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno;

EMPNO ENAME DEPTNO DEPTNO LOC
------- ---------- ---------- -------- -------------
7782 CLARK 10 10 NEW YORK
7839 KING 10 10 NEW YORK
7934 MILLER 10 10 NEW YORK
7369 SMITH 20 20 DALLAS
7876 ADAMS 20 20 DALLAS
7902 FORD 20 20 DALLAS
7788 SCOTT 20 20 DALLAS
7566 JONES 20 20 DALLAS
7499 ALLEN 30 30 CHICAGO
7698 BLAKE 30 30 CHICAGO
7654 MARTIN 30 30 CHICAGO
7900 JAMES 30 30 CHICAGO
7844 TURNER 30 30 CHICAGO
7521 WARD 30 30 CHICAGO

14 rows selected.


Table Aliases:

SQL> select e.empno,e.ename,e.deptno,d.deptno,d.loc from emp e,dept d where e.deptno=d.deptno;


EMPNO ENAME DEPTNO DEPTNO LOC
---------- ---------- ---------- ---------- -------------
7782 CLARK 10 10 NEW YORK
7839 KING 10 10 NEW YORK
7934 MILLER 10 10 NEW YORK
7369 SMITH 20 20 DALLAS
7876 ADAMS 20 20 DALLAS
7902 FORD 20 20 DALLAS
7788 SCOTT 20 20 DALLAS
7566 JONES 20 20 DALLAS
7499 ALLEN 30 30 CHICAGO
7698 BLAKE 30 30 CHICAGO
7654 MARTIN 30 30 CHICAGO
7900 JAMES 30 30 CHICAGO
7844 TURNER 30 30 CHICAGO
7521 WARD 30 30 CHICAGO

14 rows selected.


Non-Equi Join:

9. Display all grades for employees whose salary grades are defined in the SALGRADE table.

SQL> select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
ADAMS 1100 1
JAMES 950 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
ALLEN 1600 3
TURNER 1500 3
JONES 2975 4
BLAKE 2850 4
CLARK 2450 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5

14 rows selected.



Outer Join:

The (+) operator is used on the side where the date is deficient. Assume a new dept is added to the dept table. There can be no employees in the department for the said dept So, here the deficient records are in the emp table. Add a outer join operator as the employee side of the join condition.

SQL> select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno order by e.deptno;

ENAME DEPTNO DNAME
---------- ---------- --------------
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 10 ACCOUNTING
SMITH 20 RESEARCH
ADAMS 20 RESEARCH
FORD 20 RESEARCH
SCOTT 20 RESEARCH
JONES 20 RESEARCH
ALLEN 30 SALES
BLAKE 30 SALES
MARTIN 30 SALES
JAMES 30 SALES
TURNER 30 SALES
WARD 30 SALES

14 rows selected.


SQL> select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno order by e.deptno;

ENAME DEPTNO DNAME
---------- ---------- --------------
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 10 ACCOUNTING
SMITH 20 RESEARCH
ADAMS 20 RESEARCH
FORD 20 RESEARCH
SCOTT 20 RESEARCH
JONES 20 RESEARCH
ALLEN 30 SALES
BLAKE 30 SALES
MARTIN 30 SALES
JAMES 30 SALES
TURNER 30 SALES
WARD 30 SALES
OPERATIONS

15 rows selected.



Self join (joining a table to itself):

10. To find the name of the manager for every employee.

SQL> select worker.ename||'works for'||manager.ename from emp worker,emp manager where worker.mgr=manager.empno;

WORKER.ENAME||'WORKSFOR'||MAN
-----------------------------
SCOTT works for JONES
FORD works for JONES
ALLEN works for BLAKE
WARD works for BLAKE
JAMES works for BLAKE
TURNER works for BLAKE
MARTIN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
JONES works for KING
CLARK works for KING


BLAKE works for KING
SMITH works for FORD 13 rows selected.