Database Management System lecture note.

This chapter explains what oracle functions are

1. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join and Oracle Functions
Join Concept & sub Queries, SQL Functions:
Arithmetic functions, Aggregate functions,
String functions, Date function, Conversion
Functions etc.

2. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join:
AJOINclauseisusedtocombinerowsfromtwoormoretables,basedonarelated
columnbetweenthem.
What is simple join in SQL?
INNER JOIN(Simple Join)in SQL is used to combine two or more tables together
using the matching columns from both tables. ... An inner join is also called as
simple join which is used frequently in SQL for joining tables. INNER JOIN in SQL is
possible only when there is at least one common column available in those
tables.

3. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join:
TableName:Stud3
STUD_ID SNAME CITY AGE
----------------------------------------------------------------------
1 Raju Pune 20
2 Manu Sangli 21
3 Sham Mumbai 20
4 Priti Satara 23

4. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join:
TableName:Stud4
STUD_ID SDEPT COLLEGE RANK
--------------------------------------------------------------------------
1 BCA KGD 1
2 BBA YC 2
4 MBA SUK 1
5 MCA ASC 4
6 BCA KGD 1

5. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join:

6. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join: Inner Join
INNER JOIN(Simple Join)Syntax:
SELECT table1.column1, table2.column2, etc
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

7. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join: Inner Join
INNER JOIN(Simple Join)Query:
selectstud3.sname,stud3.city,stud4.sdept,stud4.rank
fromstud3
INNERJOINstud4
ONstud3.stud_id=stud4.stud_id;

8. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join: LEFT JOIN
•SQL LEFT JOIN is used to combine the two tables together. LEFT JOIN
selects all records from left table and also selects all matching records
from the right table.
Syntax:
SELECT table1.column1, table2.column2, etc
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

9. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join: Left Join, Right Join
•SQL LEFT JOIN Query:
select stud3.sname, stud3.city, stud4.sdept, stud4.rank
from stud3
LEFT JOIN stud4
ON stud3.stud_id=stud4.stud_id;
•Right Join(Right outer join):
RIGHT JOIN selects all records from right table and also selects all
matching records from the left table.

10. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join: Right Join
Syntax:
SELECT table1.column1, table2.column2, etc
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Example:
SQL> select stud3.sname, stud3.city, stud4.sdept,stud4.college
from stud3
RIGHT JOIN stud4
ON stud3.stud_id=stud4.stud_id;

11. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join: Full Join
FULL JOIN selects and returns all records from both left and right tables.
•All records are returned even if there is no single matching record between
both tables.
•And, a NULL value is returned for all columns if no matching records found
in respective records.
•Syntax:
SELECT table1.column1, table2.column2, etc
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

12. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Join: Full Join, Self Join
Example:
SQL> select * from stud3 FULL JOIN STUD4 ON
STUD3.STUD_ID=STUD4.STUD_ID;
Self Join:
In this case logically one table is treated as two tables and recursive
connection is used.
Example:
SQL> select e1.ename, e2.ename Manager_Namefrom
e1.mgr=e2.empno;

13. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Sub Query / Nested Query / Inner Query
A sub query is a form of an SQL statement that appears inside another
SQL statement. The statement containing a sub query is called a
parent statement. The rows returned by the sub query are used by
the parent statement.
It can be used by the following command.
•To insert recorded in the target table.
•To create tables and insert records in this table.
•To update records in the target table.
•To create view.

14. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Sub Query / Nested Query / Inner Query
It can be used by the following command.
•To provide values for the condition in the where, having, in select,
update and delete statement.
Example:
1.Print the names of employee having minimum salary.
SQL> select ename, salfrom empwhere sal= ( select min(sal) from
emp);
2. Print names of employee working in ACCOUNTING department
having maximum salary.

15. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Sub Query / Nested Query / Inner Query
SQL> select ename, salfrom empwhere sal= (select max(sal)
From emp, dept where emp.deptno=dept.deptnoand dname=
‘ACCOUNTING’);
3. List names of employee reporting to ‘KING’
Sql> select enamefrom empwhere mgr=(select empnofrom emp
where ename= ‘KING’);

16. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Arithmetic Functions / Numeric Functions:
Amathematicalfunctionexecutesamathematicaloperationusually
basedoninputvaluesthatareprovidedasarguments,andreturna
numericvalueastheresultoftheoperation
Functions Description
NVL Value substitution: if the value is NULL, this function is
equal to substitute.If value is not NULL, this function is
equal to value.
Syntax: NVL(Value, substitute)
Example:
SQL>SelectNVL (Comm,100) from emp;

17. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Arithmetic Functions / Numeric Functions:
Functions Description
ABS Returns the absolute value of a number
Syntax: ABS (Value)
Example: select ABS (-30) from dual; O/P=30
CEILING Returns the smallest integer value that is >= a specific
value.
Syntax: CEIL(VALUE);
Example: select CEIL(2.6) from dual; O/P=3
FLOOR Returns the largest integer value that is <= to a specific
value.

18. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Arithmetic Functions / Numeric Functions:
Functions Description
FLOOR Syntax: FLOOR(VALUE);
Example: select floor (-2.3) from dual;O/P:--3
POWER Returns the value of a number raised to the power of
another number
SYNTAX: power(value, exponent)
Example: select power (3,2) from dual; O/P=9
SQRT Returns the square root of a number
Syntax: sqrt(value)
Example: select sqrt(64) from dual; O/P= 8

19. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Arithmetic Functions / Numeric Functions:
Functions Description
MOD Returns the value of a remainder
Syntax: MOD (value, divisor)
Example: Select MOD (10, 3) from dual; O/P=1
ROUND Rounds a number to a specified number of decimal
places
Syntax: Round(Value, Precision)
Example: select round (55.6) from dual; O/P= 56
TRUNC Truncas opposite to Round.
Syntax: TRUNC(Value,Precision)
Example: select trunc(55.57) from dual; O/P=55

20. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Arithmetic Functions / Numeric Functions:
Functions Description
SIN Returns the sine of a number
Syntax:sign(value)
Example: select SIGN(30) from dual; O/P=1/2
COS Returns the cosine of a number
Syntax:COS(value)
Example: select COS(0) from dual; O/P=1

21. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Aggregate Functions: SQL aggregate functions return a
single value, calculated from values in a column.
Useful aggregate functions:
•AVG() -Returns the average value
•COUNT() -Returns the number of rows
•MAX() -Returns the largest value
•MIN() -Returns the smallest value
•SUM() -Returns the sum

22. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Aggregate Functions :
AVG(): It returns average value after calculating from values in a numeric
column.
Syntax: SELECT AVG(column_name) FROM table_name;
Queries: Computing average marks of students.
SQL> SELECT AVG(MARKS) AS AvgMarksFROM Students;
COUNT():It is used to count the number of rows returned in a SELECT
statement.
Syntax: SELECT COUNT(column_name) FROM table_name;
Queries:
SQL> Computing total number of students.SELECTCOUNT(*) AS
NumStudentsFROM Stuents;

23. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Aggregate Functions :
MAX():The MAX() function returns the maximum value of the selected
column.
Syntax: SELECT MAX(column_name) FROM table_name;
Queries:
•Fetching maximum marks among students from the Students table.
SQL> SELECT MAX(MARKS) AS MaxMarksFROM Students;
MIN():The MIN() function returns the minimum value of the selected
column.
Syntax:SELECT MIN(column_name) FROM table_name;
Queries:
•Fetching minimum marks among students from the Students table.
SQL> SELECT MIN(MARKS) AS MinMarksFROM Students;

24. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Aggregate Functions :
SUM():The SUM() function returns the sum of all the values of the
selected column.
Syntax: SELECT SUM(column_name) FROM table_name;
Queries:
•Fetching summation of total marks among students from the
Students table.
SQL> SELECT SUM(MARKS) AS TotalMarksFROM Students;

25. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
String functions:
Functions Description
ASCII() ANSI –American National StandardsInstitute.
Returns the ASCII value for the specific character
Return the ASCII value of the first character in "CustomerName":
Queries:
SQL> SELECTASCII(CustomerName) AS NumCodeOfFirstChar
FROMCustomers;
Char() Returns the character based on the ASCII code
Return the character based on the number code 65:
Queries:
SQL> SELECTCHAR(65)ASCodeToCharacterfrom dual;

26. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
String functions:
Functions Description
CONCAT() Adds two or more strings together
Add two strings together:
Queries:
SQL>SELECTCONCAT(‘easyconcept',‘.in') from dual;

27. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
String functions:
Functions Description
LEN() Return the length of a string:
The LEN() function returns the length of a string.
Queries:
SQL>SELECTLEN(‘easyconcept.in');
LOWER() Convert the text to lower-case:
The LOWER() function converts a string to lower-case.
Queries:
SQL>SELECTLOWER('SQL Tutorial is FUN!');

28. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
String functions:
Functions Description
REVERSE() Reverse a string:
The REVERSE() function reverses a string and returns the result.
Queries:
SQL>SELECTREVERSE('SQL Tutorial');
SUBSTRING() The SUBSTRING() function extracts some characters from a string.
Extract 3 characters from a string, starting in position 1:
Queries:
SQL>SELECTSUBSTRING('SQL Tutorial',1,3)ASExtractString;

29. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
String functions:
Functions Description
UPPER() The UPPER() function converts a string to upper-case.
Convert the text to upper-case:
Queries:
SQL>SELECTUPPER('SQL Tutorial is FUN!');

30. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Date Functions:
Functions Description
Sysdate Display Current Date
Queries:
SQL>SELECTsysdatefrom dual;
O/P:11-dec-2021
Add_months()Adding months: Adds count months to date
Syntax: add_months(date,count)
Queries: Thiswill add 5 months in current date.
SQL>SELECTadd_months(sysdate,5) from dual;
O/P: 11-may-2022

31. BCA Sem-III
DBMS -II
Unit I –Introduction to Oracle and SQL Centre for Distance and Online Education
Date Functions:
Functions Description
NEXT_DAY()Displaythe next named day of the week (i.e. Sunday, Monday,….) after
the given date.
Syntax: NEXT_DAY(SYSDATE, DAY);
Queries:
SQL>SELECTNEXT_DAY(SYSDATE,‘FRIDAY’)from dual;
O/P:17-dec-2021
LAST_DAY()Givesdate of last day of month that date is in
Syntax: Last_day(sysdate)
Queries:
SQL>SELECTLast_day(sysdate) from dual;
O/P: 31-dec-2022

32. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Date Functions:
Functions Description
CURRENT_DATE DisplayCurrent date
Query:
SQL> SELECT CURRENT_DATE FROM dual
MONTHS_BETWEEN Return the number of months between two dates.
Query:
SQL> SELECT MONTHS_BETWEEN( DATE '2021-01-12', DATE '2021-
05-12') FROM DUAL;
O/P:-4

33. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Date Functions:
Functions Description
CURRENT_DATE DisplayCurrent date
Query:
SQL> SELECT CURRENT_DATE FROM dual
MONTHS_BETWEEN Return the number of months between two dates.
Query:
SQL> SELECT MONTHS_BETWEEN( DATE '2021-01-12', DATE '2021-
05-12') FROM DUAL;
O/P:-4

34. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Date Functions:
Queries:Listemployeedetailswhohavejoinedinyear81.
SQL>select*fromempwhereto_char(hiredate,’yy’)=‘81’;
Q.Listresultasspecifiedagainsttable.
i)12-jul-21as12-07-21.
SQL>selectto_char(to_date('12-jul-21'),'dd/mm/yy')fromdual;
i)Displaynext2montshiredateofallemployee.
SQL>selectadd_months(hiredate,2)fromemp;

35. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Conversion Functions:
Description
TheOracle/PLSQLCONVERTfunctionconvertsastringfromone
charactersettoanother.
Conversionfunctionsconvertavaluefromonedatatypetoanother.
Generally,theformofthefunctionnamesfollowsthe
conventiondatatypeTOdatatype.Thefirstdatatypeistheinput
datatype.Theseconddatatypeistheoutputdatatype.

36. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Conversion Functions:
TheSQLconversionfunctionsare:

37. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Conversion Functions:
TheSQLconversionfunctionsare:ImplicitData-TypeConversion:
From To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
DATE VARCHAR2
NUMBER VARCHAR2

38. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Conversion Functions:
TheSQLconversionfunctionsare:
Implicit Data-Type Conversion :
In this type of conversion the data is converted from one type to
another implicitly (by itself/automatically).
Query:
SQL> select * from empwhere sal> 2000;
Also
SQL> select * from empwhere sal> '2000';

39. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Conversion Functions:
TheSQLconversionfunctionsare:ExplicitData-TypeConversion:

40. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Conversion Functions:
TheSQLconversionfunctionsare:ExplicitData-TypeConversion:
TO_CHAR Function :
TO_CHAR function is used to typecast a numeric or date input to character type
with a format model (optional).
Using theTO_CHARFunction with Dates :
SYNTAX : TO_CHAR(date, ’format_model’)The format model:
Must be enclosed in single quotation marks and is case sensitive, Can include
any valid date format element, Has an fm element to remove padded blanks
or suppress leading zeros, Is separated from the date value by a comma
•EXAMPLE :
selectempno,ename,TO_CHAR(hiredate,'MM/YY')Month_Hiredfromempwhere
ename='SMITH';

41. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Group By Functions:
TheGROUPBYStatementinSQLisusedtoarrangeidenticaldatainto
groupswiththehelpofsomefunctions.
TheGROUPBYstatementisoftenusedwithaggregatefunctions
(COUNT(),MAX(),MIN(),SUM(),AVG())togrouptheresult-setbyoneor
morecolumns.
Syntax:
SELECTcolumn_name(s)
FROMtable_name
WHEREcondition
GROUPBYcolumn_name(s)
ORDERBYcolumn_name(s);

42. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Group By Functions:
Groupbyfunctionsandhavingclause:
Example:
1.Printdepartmentwisecountofemployees.
SQL>selectdeptno,count(*)fromempgroupbydeptno;
O/P:DEPTNOCOUNT(*)
--------------------
30 6
20 5
10 3

43. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Group By Functions:
Groupbyfunctionsandhavingclause:
Example:
1.Printjobwisetotalsalary.
SQL>selectjob,sum(sal)fromempgroupbyjob;
O/P:-
JOB SUM(SAL)
--------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000

44. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Group By Functions:
Groupbyfunctionsandhavingclause:
Example:
1.Printcountofemployeeworkingindepartment20
SQL>selectdeptno,count(*)fromempgroupbydeptnohaving
deptno=20;
O/P:-
DEPTNOCOUNT(*)
--------------------
20 5

45. BCA Sem-III
DBMS -II
Unit III –Join and Oracle Function Centre for Distance and Online Education
Thank You
E-mail ID : [email protected]