Useful SQL functions | Oracle DBA Tutorial pdf

Useful SQL functions

Decode – some examples have been provided in chapter “Useful scripts for
DBAs”.
Case – example
SELECT COUNTRY, CONTINENT
CASE CONTINENT WHEN 1 THEN ‘EUROPE’
WHEN 2 THEN ‘ASIA’
WHEN 3 THEN ‘AFRICA’
ELSE ‘OTHER’ END CONTINENT_NAME
FROM COUNTRIES
With clause example
WITH
x AS
(
select 'Tom' as FirstName FROM dual union
select 'Dick' FROM dual union
select 'Barney' FROM dual union
select 'Betty' FROM dual
),
z AS
(
select 'Flintstone' as LastName FROM dual union
select 'Rubble' FROM dual
)
SELECT * FROM x,z
NVL (x, y) returns y if x is null or x otherwise.
NVLS (x, y, z) returns z if x is null, y if x is not null.
INSTR (string where to search, string what to search, start position default 1, what “n-th” occurrence default 1st) is used for searching for pattern inside a string. Returns the number position where occurrence has been found or 0 if not found.
SUBSTR (string, x, y) returns portion of string that is y characters long starting from position x.
ROUND (15.2568, 2) produces 15.26.
Pivot and Un-pivot examples – can be used to transpose rows/columns.
/* PIVOT */
WITH fly_table AS (
SELECT 'John' "Customer",'Apple' "Item", 5 "Qty" FROM dual UNION
ALL
SELECT 'Jill','Orange', 2 FROM dual UNION ALL
SELECT 'Sally','Banana', 6 FROM dual UNION ALL
SELECT 'John','Orange', 2 FROM dual UNION ALL
SELECT 'Sally','Apple', 1 FROM dual UNION ALL
SELECT 'John','Orange', 1 FROM dual UNION ALL
SELECT 'Sally','Apple', 1 FROM dual
)
SELECT * FROM (
SELECT "Customer","Item", SUM("Qty") "Qty" FROM fly_table GROUP BY
"Customer", "Item"
) PIVOT ( SUM("Qty") FOR "Item" IN ('Apple','Banana','Orange'))
/* UNPIVOT */
WITH fly_table AS (
SELECT 'India' "Country",'New Delhi' "Capital" FROM dual UNION
SELECT 'UK','London' FROM dual UNION
SELECT 'USA', 'Washington DC' FROM dual UNION
SELECT 'Germany','Berlin' FROM dual
)
SELECT COL, VALUE FROM (
SELECT * FROM fly_table
)
UNPIVOT INCLUDE NULLS ( VALUE FOR COL IN ("Country","Capital"))