Single Row Functions
• Manipulate data items
• Accept arguments and return one value
• Act on each row returned
• Return one result per row
• May modify the data type
• Can be nested
Single Row Functions
• Numeric
• Character
• Date
• Conversion
• Others
Single Row Functions: Numeric
• NVL: NVL will convert the NULL value to the required value
SELECT sal+NVL(comm,0) FROM emp;
• ABS: ABS(n): Returns the absolute value of n
SELECT ABS(-10) FROM dual;
o/p: 10
• CEIL: CEIL(n): The ceil function returns the largest integer value that is greater than or equal to a number
SELECT CEIL(23.2) FROM dual;
o/p: 24
• FLOOR: FLOOR(n): The floor function returns the smallest integer value that is greater than or equal to a number
SELECT FLOOR(56.99) FROM dual; o/p: 56
• MOD: MOD(m,n): Returns remainder of division
SELECT MOD(5,2) FROM dual; o/p: 1
• POWER: POWER(m,n): The power function returns m raise to nth power
SELECT POWER(3,2) FROM dual: o/p: 9
• ROUND: ROUND(m,n): Rounds value to specified decimal
SELECT ROUND(52.5) FROM dual; o/p: 53
• SQRT: SQRT(n): Returns square root of n
SELECT SQRT(4) FROM dual; o/p: 2
• TRUNC: TRUNC(m,n): Truncates value to specified decimal
SELECT TRUNC(56.223,1) FROM dual; o/p : 56.2
• SIGN: SIGN(n): The sign function returns a value indicating the sign of a number
SELECT SIGN(-10),SIGN(0),SIGN(10) FROM dual;
o/p -1 0 1
Single Row Functions: Character
• CONCAT: CONCAT( col1,col2)
SELECT CONCAT (ename,job) FROM emp;
• UPPER, LOWER, INITCAP
SELECT UPPER(ename), LOWER(ename), INITCAP(ename) FROM emp;
• RPAD, LPAD: RPAD(col,30,’*’)
SELECT LPAD(ename,30,’*’), RPAD(ename,30,’-’) FROM emp;
• LTRIM,RTRIM
SELECT LTRIM(ename), RTRIM(ename) FROM emp;
Single Row Functions: Character (contd…)
• LENGTH: LENGTH(STRING)
SELECT LENGTH (ename) FROM emp;
• INSTR: INSTR(STRING, ‘search strng’, start position)
SELECT INSTR(ename, ‘S’,1) FROM emp;
• SUBSTR: SUBSTR(STRING, ‘start pos’, ‘no. of chs’);
SELECT SUBSTR(ename,1,3) FROM emp;
• SOUNDEX: SOUNDEX(string)
SELECT ename FROM emp WHERE SOUNDEX(ename) = SOUNDEX(‘SMEETH’);
Single Row Functions: Date
• TO_CHAR: Converts date into a required character format
SELECT TO_CHAR(hiredate, ‘day-month-year’) FROM EMP;
• ADD_MONTHS: Adds months to a date
SELECT ADD_MONTHS(hiredate,11) FROM emp;
• MONTHS_BETWEEN: Finds the number of months between two dates
SELECT MONTHS_BETWEEN(sysdate,hiredate) FROM emp;
• LAST_DAY: Finds the last date of the given date
SELECT LAST_DAY(hiredate) FROM emp;
• Next_day: Finds the date of next day after a specific date
SELECT NEXT_DAY(hiredate, 'friday') from emp;
Conversion Functions
• TO_CHAR(X): Converts the value of X to a character or converts a date to a character
SELECT TO_CHAR(1981) FROM dual;
• TO_NUMBER(X): Converts nonnumeric value X to a number
SELECT TO_NUMBER(‘1221’) FROM dual;
• TO_DATE(X,[Y]): Converts non-date value X to a date using the format specified by Y
SELECT TO_DATE(‘12-FEB-2007’) FROM dual;
Aggregate Functions
• SUM: Returns the sum of the column values provided as parameters to sum function
Select SUM(sal) “Total Sal” From Emp Where
Deptno = 20;
• AVG(n): Returns average value of n
Select AVG(Sal) “Average” From Emp ;
• COUNT: Returns the no. of rows for the specified column
– count(*)–slow Select count(*) “Tot_row” From Emp;
– count(empno)–fast Select count(ename) “Tot_row” From Emp;
• MIN: Returns minimum value of expr
Select MIN(Sal) “Minimum” From Emp;
• MAX: Returns maximum value of expr
Select MAX(Sal) “Maximum” From Emp;
Note: Group functions ignore NULL values by default.
Group by and Having Clause
• The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns
• HAVING is very similar to WHERE except the statements within it are of an aggregate nature
• SELECT deptno, count(*)
FROM emp
GROUP BY deptno;
• SELECT deptno, count(*)
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 2;
Miscellaneous Functions
• decode( )
Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement.
•syntax:
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
decode compares
SQL> Select empno, ename,
DECODE(deptno,10,’Ten’,20,’Twenty’,’Other’) FROM emp ;
SQL> SELECT empno, ename,
DECODE(sign(sal-2000), 1, ’Above target’,
-1, ‘below target’, ‘On Target’) FROM emp;
Case Statement
• CASE
– CASE constructs allow a more compact notation and some elimination of repetition with respect to what otherwise would be expressed with an IF construct .
case n
when 1 then Action1;
when 2 then Action2;
when 3 then Action3;
else ActionOther;
end case;
• SELECT COUNT (CASE WHEN sal < 2000 THEN 1 ELSE null END) count1, COUNT (CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2, COUNT (CASE WHEN sal > 4000
THEN 1 ELSE null END) count3
FROM emp;
Case Statement
• A CASE expression selects a result and returns it. To select the result, the CASE expression uses a selector, an expression whose value is used to select one of several alternatives. Consider these two semantically almost identical fragments
text := case n
when 1 then one
when 2 then two
when 3 then three
else other end case;
…and
if n = 1 then text := one;
elsif n = 2 then text := two;
elsif n = 3 then text := three;
else text := other;
end if;
OLAP(Online Analytical processing) Features in Oracle
• ROLLUP: is used to produce subtotals at any level of aggregation needed. These subtotals then “roll up” into a grand total.
e.g.
SELECT deptno, job, SUM(sal) AS salary
FROM emp
GROUP BY ROLLUP(deptno,job);
Note: NULL values in the output of rollup operations typically mean that the row contains subtotal or grand total information.
Output of the ROLLUP function
DEPTNO JOB SALARY
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
• CUBE: The cube operation calculates all levels of subtotals on horizontal lines across spreadsheets of output and create cross-tab summaries on multiple vertical columns in those spreadsheets
e.g.
SELECT deptno,job,SUM(sal) AS salary
FROM emp
GROUP BY CUBE(deptno,job);
Output of the CUBE Function
DEPTNO JOB SALARY
-----------------------------------------------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
Joins
If we require data from more than one table in the database, a join is used.
Types of Join
• EQUI
• NON-EQUI
• OUTER
• SELF
EQUI JOIN
• The columns compared by = operator are called join columns and the join operation is called an EQUI JOIN
• ANSI/ISO syntax for EQUI join
Select ename, deptno, dname
From emp natural join dept;
• For Cartesian product
SELECT col1, col2 FROM example1 CROSS JOIN example2;
SELF JOIN
• It is possible using table labels (aliases) to join a table to itself, as if they were two separate tables
• This allows rows in a table to be joined to rows in the same table
Example:
List the employees working in the same department as that of employee 7900
SELECT b.ename from emp a, emp b
WHERE a.empno=7900 and a.deptno=b.deptno;
OUTER JOIN
• If a row does not satisfy a join condition, then the row will not appear in the query result
• The missing row(s) can be returned if an outer join operator is used in the join condition
• The operator is plus sign enclosed in parentheses (+), and is placed on the side of the join(table) which is deficient in information
• ANSI/ISO syntax for outer join (9i)
SELECT e. ename, d.dname FROM emp e
LEFT OUTER JOIN dept d ON d.deptno=e.deptno;
OUTER JOIN
SELECT e. ename, d.dname FROM
Emp e RIGHT OUTER JOIN dept d
On d.deptno=e.deptno;
• Full outer join
SELECT e. ename, d.dname
FROM emp e FULL OUTER JOIN dept d
ON d.deptno=e.deptno;
Sub-queries
• A sub-query is a form of an SQL statement that appears inside another SQL statement
• also termed as nested query
• The statement containing a sub-query is called a parent statement. The parent statement uses the rows returned by the sub-query.
Types of Sub-queries
• Single-row Sub-queries
e.g. SELECT ename FROM emp
WHERE deptno=(SELECT deptno FROM dept
WHERE dname=‘ACCOUNTING’);
• Multi-row Sub-queries
e.g. SELECT ename FROM emp
WHERE deptno IN (SELECT deptno FROM dept);
Types of Sub-queries Using ANY and ALL
• SELECT * FROM emp WHERE sal >= ANY
(SELECT sal FROM emp WHERE deptno=30)
and deptno=10;
• ANY: the condition evaluates to true, if there exist at least one row selected by the sub-query for which the comparison holds.
• SELECT * FROM Emp WHERE sal >= ALL
(SELECT sal FROM emp WHERE deptno=30)
and deptno<>30;
All: the condition evaluates to true, if there exist all the rows selected by the sub-query for which the comparison holds.
Types of Sub-queries
• Inline Views: The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM clause, just as if the query was a table name
e.g. SELECT ename, sal FROM ( select * from emp ORDER BY sal desc) WHERE ROWNUM <6; • Multiple-column Sub-queries: When you want to compare more than one columns in a sub-query e.g. SELECT ename, job FROM emp WHERE (deptno, sal) IN (SELECT deptno, max(sal) FROM emp GROUP BY deptno);
Co-related Sub-queries
• A co-related query is a form of query used in Select, Update or Delete commands to force the DBMS to evaluate the query once per row of the parent query rather than once for the entire query
• A co-related query is used to answer questions whose answers depends on the values in each row of the parent query
Co-related Sub-query
• EXISTS: The EXISTS condition is considered "to be met" if the sub-query returns at least one row
• Examples
SELECT deptno, dname
FROM dept a
WHERE EXISTS (SELECT empno FROM emp e
WHERE a.deptno=e.deptno);
SELECT deptno, dname
FROM dept a
WHERE NOT EXISTS (SELECT empno FROM emp e WHERE a.deptno=e.deptno);
Co-related Sub-query (Contd…)
SELECT ename
FROM emp a
WHERE EXISTS (SELECT * FROM emp b WHERE b.mgr=a.empno);
With Clause and Sub-queries (9i)
• With clause lets us factor out the sub-query, give it a name, then reference that name multiple times within the original complex query
• This improves the performance of this query by having Oracle 9i execute the query only once, then simply reference it at the appropriate points in the main query
Example
WITH summary AS
(SELECT dname, SUM(sal) AS dept_total
FROM emp,dept
WHERE emp.deptno=dept.deptno
GROUP BY dname)
SELECT dname,dept_total
FROM summary
WHERE dept_total > (SELECT SUM(dept_total)*1/3
FROM summary)
ORDER BY dept_total desc;
