Oracle – Joins

A JOIN clause is used to combine rows from two or more tables to retrieve data, based on a related column between them.

  • Equi Join
  • Outer Join
    • Right Outer Join
    • Left Outer Join
    • Full Outer Join
  • Self Join
  • Cross Join
  • Natural Join
  • Non-Inner Join

Equi Join

It is a join with a join condition containing an equality operator. An equi join returns only the rows that have equivalent values for the specified columns.

Example 1

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees   e,
       departments d
 WHERE e.department_id = d.department_id;

Example 2

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name,
       d.location_id,
       l.country_id
  FROM employees   e,
       departments d,
       locations l
 WHERE e.department_id = d.department_id
   AND d.location_id = l.location_id;

Example 3

SELECT e.department_id,
       d.department_name,
	   MIN(salary) "MIN Salary",
	   MAX(salary) "MAX Salary",
	   SUM(salary) "SUM Salary",
	   ROUND(AVG(salary)) "AVG Salary"
  FROM employees e,
       departments d
 WHERE e.department_id = d.department_id
 GROUP BY e.department_id, d.department_name
 ORDER BY 1;

Example 4

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name,
       l.street_address,
	   c.country_name
  FROM employees e JOIN departments d ON (e.department_id = d.department_id)
                   JOIN locations   l ON (d.location_id = l.location_id)
				   JOIN countries   c ON (l.country_id = c.country_id);

Outer Join

Right Outer Join

Records = Matched Record + Un Matched Record From Right Side

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e,
       departments d
  WHERE e.department_id(+) = d.department_id;
-- ===============================  
SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e RIGHT JOIN departments d
  ON e.department_id = d.department_id;

Left Outer Join

Records = Matched Record + Un Matched Record From Left Side

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e,
       departments d
  WHERE e.department_id = d.department_id(+);
-- ===========================================
SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e LEFT JOIN departments d
  ON e.department_id = d.department_id;

Full Outer Join

Records = Matched Record + Un Matched Record From Both Sides

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e FULL JOIN departments d
  ON e.department_id = d.department_id;

Self Join

A self JOIN is a regular join, but the table is joined with itself.

Example 1

SELECT e.employee_id,
       e.first_name,
	   e.hire_date,
	   e.salary,
	   e.manager_id,
	   m.first_name AS "Manager Name",
	   m.salary     AS "Manager Salary",
	   m.hire_date  AS "Manager Hire Date"
  FROM employees e, employees m
  WHERE e.manager_id = m.employee_id;

Example 2

Employee works for Manager

SELECT e1.first_name ||' works for ' ||  e2.first_name "Employee with their managers"
  FROM employees e1,
       employees e2
 WHERE e1.manager_id = e2.employee_id;

Example 3

Employees who are getting more salary than their managers.

SELECT e.employee_id,
       e.first_name,
	   e.hire_date,
	   e.salary,
	   e.manager_id,
	   m.first_name AS "Manager Name",
	   m.salary     AS "Manager Salary",
	   m.hire_date  AS "Manager Hire Date"
  FROM employees e, employees m
  WHERE e.manager_id = m.employee_id
  AND e.salary > m.salary;

Example 4

Employees who are experienced than their managers.

  SELECT e.employee_id,
       e.first_name,
	   e.hire_date,
	   e.salary,
	   e.manager_id,
	   m.first_name AS "Manager Name",
	   m.salary     AS "Manager Salary",
	   m.hire_date  AS  "Manager Hire Date"
  FROM employees e, employees m
  WHERE e.manager_id = m.employee_id
  AND e.hire_date < m.hire_date;

Cross Join

Cross Join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.

SELECT e.employee_id,
       e.first_name,
       m.first_name "Manager Name"
  FROM employees e CROSS JOIN employees m;
  
-- Result Count:
SELECT 107*107 FROM dual;

Natural Join

JOIN matched on one columns, If matched on more than one column, result is out of control.

SELECT e.employee_id,
       e.first_name,
	   department_id,
	   d.department_name
  FROM employees e NATURAL JOIN departments d;

Join By Using

SELECT e.employee_id,
       e.first_name,
	   department_id,
	   d.department_name
  FROM employees e JOIN departments d
  USING (department_id);

Non-Inner Join

If an ‘=’ is not used to get data from more than one table then it is called Non-Inner Join.

Creating Environment

CREATE TABLE student(
 name	VARCHAR2(25)
,mark 	NUMBER(3)
);

CREATE TABLE grade(
 grade		VARCHAR2(25)
,l_limit 	NUMBER(3)
,h_limit	NUMBER(3)
);

INSERT INTO student VALUES('Vijay' , 386);
INSERT INTO student VALUES('Fisher', 405);
INSERT INTO student VALUES('Riya'  , 451);
INSERT INTO student VALUES('Harini', 399);
INSERT INTO student VALUES('Star'  , 350);
INSERT INTO student VALUES('Pepper', 300);
INSERT INTO student VALUES('Bobby' , 249);

INSERT INTO grade VALUES('O',  451, 500);
INSERT INTO grade VALUES('A+', 401, 450);
INSERT INTO grade VALUES('A',  351, 400);
INSERT INTO grade VALUES('B+', 301, 350);
INSERT INTO grade VALUES('B',  251, 300);
INSERT INTO grade VALUES('S/W',  0, 250);

Example

SELECT s.name, 
       s.mark, 
	   g.grade
  FROM student s,
       grade   g
 WHERE s.mark BETWEEN g.l_limit AND g.h_limit 
 ORDER BY s.name;

Oracle – SQL Functions

SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements.

Prerequisite

DROP TABLE emps;
SELECT employee_id FROM employees;
CREATE TABLE emps AS(SELECT * FROM employees WHERE 1=1 AND employee_id BETWEEN 100 AND 105);
ALTER TABLE emps ADD temp VARCHAR2(20);
UPDATE emps SET temp = NULL;

Single Row Functions

  • Character Functions
  • Number Functions
  • Date Functions
  • Type Conversions
  • General Functions

Character Functions

Case Manipulation

  • UPPER( )
  • LOWER( )
  • INITCAP( )
-- UPPER( )
-- =========
SELECT first_name, UPPER(first_name) FROM emps;

-- LOWER( )
-- =========
SELECT first_name, LOWER(first_name) FROM emps;

-- INITCAP( )
-- ===========
SELECT job_id, INITCAP(job_id) FROM emps;

Character Manipulation

  • LENGTH( )
  • CONCAT( )
  • REVERSE( )
  • SUBSTR( ), INSTR( )
  • LPAD( ), RPAD( )
  • LTRIM( ), RTRIM( ), TRIM( )
  • REPLACE( )
  • TRANSLATE( )
-- LENGTH() 
-- =========
-- Number of characters returned
-- LENGTH('string')

SELECT first_name, LENGTH(first_name) FROM emps;

-- CONCAT()
-- =========
-- CONCAT(char1, char2)
/*
CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes 
CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1.
*/
SELECT first_name, CONCAT(first_name, last_name) FROM emps;

-- REVERSE()
-- ==========
-- REVERSE(string)
SELECT job_id, REVERSE(job_id) FROM emps;

-- SUBSTR()
-- =========
-- SUBSTR(string, start_position, end_position)
SELECT first_name, SUBSTR(first_name, 1, 3) FROM emps;
SELECT first_name, SUBSTR(first_name, 1, 3.7) FROM emps;

-- INSTR()
-- ========
-- INSTR(string, sub_string, start_position, occurance)
SELECT first_name, INSTR(first_name, 'a', 1, 1) FROM emps;
SELECT first_name, INSTR(first_name, 'e', 1, 2) FROM emps;

-- LPAD()
-- =======
-- LPAD(string, return_length, pad_value)
SELECT first_name, LPAD(first_name, 10, '$') FROM emps;

-- RPAD()
-- =======
-- RPAD(string, return_length, pad_value)
SELECT first_name, RPAD(first_name, 10, '*') FROM emps;

-- LTRIM()
-- ========
-- LTRIM(string, trim_value)
UPDATE emps SET temp = LPAD(first_name, 10, '$');
SELECT temp, LTRIM(temp, '$') FROM emps;

-- RTRIM()
-- ========
-- RTRIM(string, trim_value)
UPDATE emps SET temp = RPAD(first_name, 10, '$');
SELECT temp, RTRIM(temp, '$') FROM emps;

-- TRIM()
-- =======
-- TRIM(trim_value FROM string)
UPDATE emps SET temp = LPAD(RPAD(first_name, 10, '$'), 20, '$');
SELECT temp, TRIM('$' FROM temp) FROM emps;

-- REPLACE()
-- ==========
-- REPLACE(string, set_of_string_to_replace, replacement_string) 
UPDATE emps SET temp = NULL;
UPDATE emps SET temp = 'Sathiyan S' WHERE employee_id = 100;
SELECT temp, REPLACE(temp, 'Sa', 'Ab') FROM emps;

-- TRANSLATE()
-- ============
-- TRANSLATE(string, string_to_replace, replacement_string)
UPDATE emps SET temp = NULL;
UPDATE emps SET temp = 'Sathiyan S' WHERE employee_id = 100;
SELECT temp, TRANSLATE(temp, 'Sa', 'Ab') FROM emps;

-- REPLACE() & TRANSLATE()
-- ========================
SELECT 'Oracle PLSQL - System Analyst' AS sample_text,
REPLACE('Oracle PLSQL - System Analyst', 'ly', 'az') AS replace_test,
TRANSLATE('Oracle PLSQL - System Analyst', 'ly', 'az') AS translate_test
FROM dual;

Number Function

  • ROUND( )
  • TRUNC( ) – Number, Date
  • MOD( )
  • POWER( )
  • SIGN( )
  • ABS( )
  • FLOOR( )
  • CEIL( )
/*
Natural Value: 
Rounding Poisition  | Value	
		0  	 	  	|  0
		10 	 	  	| -1
		100	 	  	| -2
		1000 	  	| -3

Decimal Value:
Rounding Poisition  | Value	
		1  	 	  	| 1
		2 	 	  	| 2
		3	 	  	| 3
		4	 	  	| 4
*/

-- ROUND()
-- =======
-- Syntax: ROUND(number, position_value)
SELECT ROUND(234.789,  1) FROM dual; -- 234.8
SELECT ROUND(234.729,  1) FROM dual; -- 234.7
SELECT ROUND(234.729,  2) FROM dual; -- 234.73
SELECT ROUND(234.755,  2) FROM dual; -- 234.76
SELECT ROUND(234.7553, 3) FROM dual; -- 234.755
SELECT ROUND(234.7558, 3) FROM dual; -- 234.756
SELECT ROUND(234.7558, 0) FROM dual; -- 235
SELECT ROUND(234.789, -1) FROM dual; -- 230
SELECT ROUND(236.789, -1) FROM dual; -- 240
SELECT ROUND(234.729, -2) FROM dual; -- 200
SELECT ROUND(254.729, -2) FROM dual; -- 300
SELECT ROUND(534.7553,-3) FROM dual; -- 1000
SELECT ROUND(234.7553,-3) FROM dual; -- 0

-- TRUNC()
-- =======
-- Syntax: TRUNC(number, position_value) or TRUNC(date, options)
SELECT TRUNC(7865.3452332, 4) FROM dual; -- 7865.3452
SELECT TRUNC(7865.3452332, 3) FROM dual; -- 7865.345
SELECT TRUNC(7865.3452332, 2) FROM dual; -- 7865.34
SELECT TRUNC(7865.3452332, 1) FROM dual; -- 7865.3
SELECT TRUNC(7865.3452332, 0) FROM dual; -- 7865
SELECT TRUNC(7865.3452332,-1) FROM dual; -- 7860
SELECT TRUNC(7865.3452332,-2) FROM dual; -- 7800
SELECT TRUNC(7865.3452332,-3) FROM dual; -- 7000
SELECT TRUNC(7865.3452332,-4) FROM dual; -- 0

-- Current Month Starting
SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;
-- Current Quarter Starting 
SELECT TRUNC(SYSDATE, 'Q') FROM dual;
-- Year Starting
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;
-- First Day Of Current Week(Sunday)
SELECT TRUNC(SYSDATE, 'DAY') FROM dual;
-- Current Date
SELECT TRUNC(SYSDATE, 'DDD') FROM dual;

-- MOD()
-- ======
-- Syntax: MOD(dividend_number, divisor_number), Returns Reminder
SELECT MOD(16,5), MOD(16,4) FROM dual;

-- POWER()
-- ========
-- Syntax: POWER(number, power_value)
SELECT POWER(2,2), POWER(6,2), POWER(3,3)  FROM dual;

-- SIGN()
-- =======	
-- Syntax: SIGN(number), Returns 1 if number is +ve or -1 if number is -ve
SELECT SIGN(-5), SIGN(0), SIGN(5) FROM dual;

-- ABS()
-- ======
-- Syntax: ABS(number), Returns Always +ve value of given number
SELECT ABS(-10), ABS(10) FROM dual;

-- FLOOR()	
-- ========
-- Syntax: FLOOR(number), Returns near lowest integer value
SELECT FLOOR(17.9) FROM dual; -- 17
SELECT FLOOR(17.0) FROM dual; -- 17

-- CEIL()
-- =======
-- Syntax: CEIL(number), Returns near greatest integer value
SELECT CEIL(17.1) FROM dual;  -- 18
SELECT CEIL(17.0) FROM dual;  -- 17

Date Function

  • ADD_MONTHS( )
  • MONTHS_BETWEEN( )
  • LAST_DAY( )
  • NEXT_DAY( )
-- Arithmetic Operations 
-- ======================
-- Note: SYSDATE is not a Date Function

SELECT SYSDATE    FROM dual;
SELECT SYSDATE+13 FROM dual;
SELECT SYSDATE-13 FROM dual;
SELECT TO_DATE('22-APR-19') - TO_DATE('01-APR-19') FROM dual; 
SELECT SYSDATE+13 - SYSDATE FROM dual; 

-- ADD_MONTHS()
-- =============
-- Syntax: ADD_MONTHS(date, number_months), Returns Date
SELECT ADD_MONTHS(SYSDATE,  3) FROM dual;
SELECT ADD_MONTHS(SYSDATE, -3) FROM dual;

-- MONTHS_BETWEEN()
-- =================
-- Syntax: MONTHS_BETWEEN(date_1, date_2), Returns Difference 
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, '22-DEC-16')) "MONTHS BETWEEN" FROM dual;
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, '01-DEC-87')/12) "AGE" FROM dual;

-- LAST_DAY()
-- ===========
-- Syntax: LAST_DAY(date), Returns Last Day Of Current Month
SELECT LAST_DAY(SYSDATE)   FROM dual;
SELECT LAST_DAY(SYSDATE-30)FROM dual;
SELECT LAST_DAY(SYSDATE+30)FROM dual;

-- NEXT_DAY()
-- ===========
-- Syntax: NEXT_DAY(date, weekday or weekday in number), Returns the first weekday that is greater than a date.
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;
SELECT NEXT_DAY('02-Aug-03', 'TUESDAY') FROM dual;
SELECT NEXT_DAY('24-DEC-03', 'TUESDAY') FROM dual;
SELECT NEXT_DAY(SYSDATE + (50*7), 'TUESDAY') FROM dual;
SELECT NEXT_DAY(SYSDATE, 4) FROM dual;
SELECT NEXT_DAY(SYSDATE, 7) FROM dual;
SELECT NEXT_DAY(SYSDATE, 8) FROM dual;
--ORA-01846: not a valid day of the week

Type Conversion

  • TO_CHAR( )
  • TO_DATE( )
  • TO_NUMBER( )
-- TO_CHAR()
-- ==========
-- Syntax: TO_CHAR(date, format), Return string
SELECT TO_CHAR(SYSDATE, 'DD') AS "DD" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DDD') AS "DDD" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DDTH MONTH YYYY') AS "DDTH MONTH YYYY" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DDSPTH') AS "DDSPTH" FROM dual;
SELECT TO_CHAR(SYSDATE, 'HH12:MI:SS') AS "12 Hrs" FROM dual;
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') AS "24 Hrs" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DAY') AS "DAY" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY') AS "DD/MM/YYYY" FROM dual;

-- TO_DATE()
-- ==========
-- Syntax: TO_DATE(string, format), Return date
SELECT TO_DATE('12/04/2019', 'DD/MM/YYYY') FROM dual;
SELECT TO_DATE('04-12-2019', 'MM-DD-YYYY') FROM dual;
SELECT TO_DATE('2019/02/09', 'YYYY/MM/DD') FROM dual;
SELECT TO_DATE('01/04/2019 18:30:25', 'DD-MM-YYYY HH24:MI:SS') FROM dual;

-- TO_NUMBER()
-- ============
-- Syntax: TO_NUMBER(string), Return number.
SELECT TO_NUMBER('10') FROM dual;

General Functions

  • NVL( )
  • NVL2( )
  • NULLIF( )
  • COALESCE( )
-- NVL()
-- ======
SELECT employee_id, commission_pct, NVL(commission_pct, 0) "commission_pct_nvl"
  FROM employees;

-- NVL2()
-- =======
SELECT employee_id,
       commission_pct,
       NVL(commission_pct, 0) "NVL",
       NVL2(commission_pct, 3, 1) "NVL2"
  FROM employees;

-- NVL TO CHAR
-- ============
SELECT employee_id,
       commission_pct,
       NVL(commission_pct, 0) "NVL",
       NVL2(commission_pct, 1, 0) "NVL2",
       NVL(TO_CHAR(commission_pct), 'No commission') "NVL TO_CHAR"
  FROM employees;

-- NULLIF()
-- =========
SELECT NULLIF(1,2)                    FROM dual; -- 1
SELECT NULLIF(12, 12)                 FROM dual; -- NULL
SELECT NULLIF('Sathiyan', 'SATHIYAN') FROM dual; -- Sathiyan
SELECT NULLIF('Hello', 'Hello')       FROM dual; -- NULL

-- COALESCE()
-- ===========
SELECT COALESCE(100, NULL , NULL, 87, NULL ,98)  FROM dual;
SELECT COALESCE(NULL, NULL , NULL, 87, NULL ,98) FROM dual;

Multiple Row Functions

Group Function/ Aggregate Function

  • MIN( )
  • MAX( )
  • SUM( )
  • AVG( )
  • COUNT( )
SELECT COUNT(employee_id) "COUNT",
       MIN(salary) "Minimum",
       MAX(salary) "Maximum",
       SUM(salary) "Salary Sum",
       ROUND(AVG(salary)) "Average"
  FROM employees;

GROUP BY, HAVING

SELECT department_id, 
	   SUM(salary)
  FROM employees
 WHERE department_id  > 50
 GROUP BY department_id
HAVING SUM(salary) > 35000;

Analytical Functions

  • RANK() OVER( ), DENSE_RANK( ) OVER( )
  • AVG( ) OVER( )
  • LEAD( ), LAG( )
-- RANK() OVER( ), DENSE_RANK( ) OVER( )
-- ======================================
SELECT employee_id,
       first_name,
       salary,
       RANK() OVER(ORDER BY salary DESC) "RANK",
       DENSE_RANK() OVER(ORDER BY salary DESC) "D_RANK"
  FROM emps;

-- AVG( ) OVER( )
-- ===============
SELECT employee_id,
       first_name,
       salary,
       AVG(salary) OVER(ORDER BY salary DESC) "AVG"
  FROM emps;

-- LEAD( ) OVER( )
-- ================
-- Syntax: LEAD ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
SELECT first_name,
       hire_date,
       LEAD(hire_date, 1) OVER(ORDER BY hire_date) AS Next_Hired
  FROM emps;


-- LAG( ) OVER( )
-- ===============
-- Syntax: LAG ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
SELECT first_name,
       hire_date,
       salary,
       LAG(salary, 1, 0) OVER(ORDER BY hire_date) AS Prev_Salary
  FROM emps;

Reference

Oracle – CASE & DECODE

DECODE and CASE both provides IF-THEN-ELSE functionality in Oracle SQL. Decode Function and Case Statement is used to transform data values at retrieval time.
Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through some examples in this post.

DECODECASE
FunctionExpression
Performs an equality check onlyLogical comparisons
SQL onlyBoth PL/SQL and SQL
Works with scalar value aloneWorks with sub-queries also
NULLNULL IS NULL
Does not expects datatype consistency Expects datatype consistency
Oracle StandardANSI SQL Standard

Decode

Syntax

DECODE(VALUE, search_1, result_1 
           [, search_2, result_2]
           [, search_N, result_N]
           [, default]
           )

Example 1

SELECT department_id, department_name,
DECODE(department_id, 10, 'Admin'
                    , 20, 'Marketing'
                    , 30, 'Purchasing'
                    , 40, 'HR'
                    , 50, 'Logistics'
                    ,     'Others') decoded_name
  FROM departments;

Example 2 – Using Decode In Update

UPDATE table_x SET gender =  DECODE(gender, 'M', 'F', 'M') WHERE gender IS NOT NULL;

Example 3 – Order By Using a List of Values

-- Create Table
-- =============
CREATE TABLE emp_job_map(
    emp_name VARCHAR(64),
    job_name VARCHAR(64)
);

-- Inserting Values
-- =================
INSERT INTO emp_job_map VALUES('King','Engineer');
INSERT INTO emp_job_map VALUES('Bishop','Policeman');
INSERT INTO emp_job_map VALUES('Annamalai','Lawyer');
INSERT INTO emp_job_map VALUES('Louisa','Doctor');
INSERT INTO emp_job_map VALUES('Krishna','Engineer');
INSERT INTO emp_job_map VALUES('Jack','Doctor');
INSERT INTO emp_job_map VALUES('Daniel','Nurse');

-- Order By Using a List of Values
-- ================================
SELECT emp_name, job_name
  FROM emp_job_map
 ORDER BY DECODE(job_name,
                'Policeman',1,
                'Lawyer',2) ASC, 
                job_name ASC;

Case

Syntax

CASE expression WHEN this1 THEN that1
                WHEN this2 THEN that2
                [ ELSE that]
 END

Example 1

SELECT employee_id, first_name, 
 (CASE department_id
 WHEN 10 THEN 'Admin'
 WHEN 20 THEN 'Marketing'
 WHEN 30 THEN 'Purchasing'
 WHEN 40 THEN 'HR'
 WHEN 50 THEN 'Logistics'
 ELSE 'Unknown'
  END) department_case_name
FROM employees
ORDER BY department_case_name;

Example 2 – Searchable Sub-Query

SELECT CASE
       -- Predicate with "IN"
       WHEN salary IN (9000,10000) THEN '9K-10K'
       -- Searchable Subquery
       WHEN employee_id IN (SELECT manager_id FROM departments) THEN 'Dept_Mgr'
       ELSE 'Unknown'
       END case_category
  FROM employees;

Example 3 – Logical Operations

SELECT employee_id, salary,
      (CASE
       WHEN salary < 3000 THEN 'Low'
       WHEN salary BETWEEN 3000 AND 6000 THEN 'Medium'
       WHEN salary > 6000 THEN 'High'
       ELSE 'N/A'
        END) case_salary
FROM employees
ORDER BY employee_id;

Reference

Oracle – Synonyms

A synonym is an alias for a database object. The object can be a table, view, synonym, sequence, PL/SQL stored procedure, PL/SQL function, PL/SQL package, materialized view.

Synonyms can be created as PRIVATE (by default) or PUBLIC.

  • Private – It exist only in specific user schema.
  • Public – Available to all users in the database.

The name of the object for which you are creating the synonym. It can be one of the following:

  • Table
  • View
  • Sequence
  • Stored Procedure
  • Function
  • Package
  • Materialized View
  • User-defined Object
  • Synonym

You can refer to synonyms in the following statements:

  • Select
  • Insert
  • Update
  • Delete
  • Explain Plan
  • Lock Table

Syntax

Create Synonym (Replace for Altering Synonym)

-- General
-- ========
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name FOR [schema .] object_name [@ dblink];

-- Private
-- ========
CREATE OR REPLACE SYNONYM synonym_name FOR object_name;

-- Public
-- =======
CREATE OR REPLACE PUBLIC SYNONYM synonym_name FOR object_name;

-- Public Access On Object For Public Synonym
-- ===========================================
GRANT SELECT ON object_name TO PUBLIC;

Drop Synonym

-- Private
-- ========
DROP SYNONYM synonym_name;

-- Public
-- ========
DROP PUBLIC SYNONYM synonym_name;

Data Dictionary Table

-- Synonyms Owned 
-- ===============
SELECT * FROM USER_SYNONYMS;
SELECT * FROM USER_SYNONYMS WHERE table_name= UPPER('synonym_name');

-- Synonyms Accessible
-- ====================
SELECT * FROM ALL_SYNONYMS;
SELECT * FROM ALL_SYNONYMS WHERE table_name= UPPER('synonym_name');

-- All Synonyms
-- =============
SELECT * FROM DBA_SYNONYMS;
SELECT * FROM DBA_SYNONYMS WHERE table_name= UPPER('synonym_name');

Examples

Prerequisite

DROP TABLE super_heroes_marvel_dc;
CREATE TABLE super_heroes_marvel_dc AS (SELECT employee_id AS id, first_name AS name, salary FROM employees WHERE 1=2);

INSERT INTO emps VALUES(1 , 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(2 , 'Thor'       , 45000);
INSERT INTO emps VALUES(3 , 'Jane Foster', 40000);
INSERT INTO emps VALUES(4 , 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(5 , 'Spider man' , 30000);
INSERT INTO emps VALUES(6 , 'Stark'      , 25000);
INSERT INTO emps VALUES(7 , 'Pepper'     , 20000);
INSERT INTO emps VALUES(8 , 'Oliver'     , 15000);
INSERT INTO emps VALUES(9 , 'Overwatch'  , 10000);
INSERT INTO emps VALUES(10, 'Ethan Hunt' , 5000);

Example 1 – Public

-- Select From Table
-- ==================
SELECT * FROM hr.super_heroes_marvel_dc;

-- Grant Permission to Public
-- ===========================
GRANT SELECT ON hr.super_heroes_marvel_dc TO PUBLIC;

-- Creating Synonym
-- =================
CREATE OR REPLACE PUBLIC SYNONYM heroes FOR hr.super_heroes_marvel_dc;

-- Select From Synonym
-- ====================
SELECT * FROM heroes;

Example 2 – Synonyms for Synonyms

-- Synonym
-- ========
CREATE OR REPLACE SYNONYM heroes_sal FOR super_heroes_marvel_dc;

-- Synonyms for Synonyms
-- ======================
CREATE OR REPLACE SYNONYM heroes FOR heroes_sal;

-- Select From Synonyms for Synonyms
-- ==================================
SELECT * FROM heroes;

Example 3 – DB Link Synonym Usage

-- Creating DB Link
-- =================
CREATE DATABASE LINK my_db_link CONNECT TO user IDENTIFIED BY passwd USING 'alias';

-- Creating Synonym For DB Link Table
-- ===================================
CREATE SYNONYM my_table FOR remote_table@my_db_link;

-- Select From DB Link Table Synonym
-- ==================================
SELECT * FROM my_table;

Reference

Oracle – Indexes

An index is an object in the database that allows you to find specific data in a table faster. You don’t need to ask Oracle to use an index if it’s already there. Most of the time, Oracle will work out that it needs to use it.

Index Types

  • Unique Index
    • Primary Key
    • Unique Key
  • B-Tree
  • Bitmap
  • Function-Based

Unique

Oracle create unique index for Primary key and unique key constraints.

B-Tree Indexes vs Bitmap Indexes

B-Tree IndexBitmap Index
Good for high-cardinality dataGood for low-cardinality data
Good for OLTP databases (lots of updating)Good for data warehousing applications
Use a large amount of spaceUse relatively little space
Easy to updateDifficult to update

B-Tree Indexes

The B*Tree index has several sub types. Some of them are,

  • Index Organized Tables – A table stored in a B*Tree structure
  • Reverse Key Indexes – The bytes in the key are reversed. This is used to stop sequence keys being on the same block like 999001, 999002, 999003 would be reversed to 100999, 200999, 300999 thus these would be located on different blocks.

There are only a few things you need to specify to create a b-tree index:

  • index_name: The name of the new index you’re creating, which must be a unique name.
  • table_name: The name of the table you want to create the index for.
  • columns: A single column name, or a comma-separated list of the columns you want to create the index on.
-- Syntax
-- =======
CREATE INDEX index_name
ON table_name (columns);

-- Example
-- ========
CREATE INDEX emp_id_idx
ON emps(employee_id);

-- Syntax - Reverse Index 
-- =======================
CREATE INDEX index_name
ON table_name (column_name)
REVERSE;

-- Example - Reverse Index 
-- ========================
CREATE INDEX emp_fn_ln_ridx 
ON emps (firstname, lastname) 
REVERSE;

-- Syntax - Composite Index
-- =========================
CREATE INDEX index_name
ON table_name (col1, col2);

-- Example - Composite Index
-- ==========================
CREATE INDEX emp_fn_ln_cidx 
ON emps (firstname, lastname);

-- Syntax - Index Organized Tables
-- ================================
CREATE TABLE table_name (id INTEGER PRIMARY KEY, value VARCHAR2(50)) 
SEGMENT CREATION IMMEDIATE -- This Line Must from version 11g. 
ORGANIZATION INDEX;

Bitmap Indexes

With a bitmap index , a single index entry uses a bitmap to point to many rows simultaneously, they are used with low data that is mostly read-only. They should not be used with OLTP systems.

-- Syntax
-- =======
CREATE BITMAP INDEX index_name
ON table_name (columns);

-- Example
-- ========
CREATE BITMAP INDEX emp_id_bm_idx
ON emps(employee_id);

Function Based Indexes

These are B*Tree or bitmap indexes that store the computed result of a function on a row(s) – not the column data itself.

-- Syntax
-- =======
CREATE INDEX index_name
ON table_name(function(column_name));

-- Example
-- ========
CREATE INDEX emp_sal_fn_idx
ON employee(salary * 12);

Alter – Rename, Rebuild Indexes

-- Rename
-- =======

ALTER INDEX emp_sal_fn_idx
RENAME TO employee_sal_fn_idx;

-- Rebuild
-- ========
ALTER INDEX employee_sal_fn_idx
REBUILD COMPUTE STATISTICS;

Disable, Drop Indexes

-- Disable
-- ========
ALTER INDEX index_name ON table_name DISABLE;

-- Rebuild (Enable)
-- =================
ALTER TABLE index_name ON table_name REBUILD;

-- Drop
-- =====
DROP INDEX index_name;

Cardinality vs Selectivity

Cardinality and selectivity are two terms that are often used in the SQL world when working with indexes.

  • Cardinality is the number of distinct values in a particular column.
  • Selectivity is the ratio of cardinality on an indexed column to the total number of rows in a table.

Selectivity = Cardinality/Total_Rows. A selectivity value of a query closer to 1 is said to have high selectivity, and closer to 0 is called low selectivity. If a query has a high selectivity, then it is normally fast to select using an index. If a query has low selectivity, it is often not worth the time to use the index in the query, and Oracle may not use it.

References

Oracle – Sequence

In Oracle, you can create an auto-number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax

Create Sequence

CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE | NOCYCLE
CACHE value | NOCACHE
ORDER value | NOORDER;
  • sequence_name: Name of the sequence.
  • initial_value: starting value from where the sequence starts. Initial_value should be greater than or equal to minimum value and less than equal to maximum value.
  • increment_value: Value by which sequence will increment itself. Increment_value can be positive or negative.
  • minimum_value: Minimum value of the sequence.
  • maximum_value: Maximum value of the sequence.
  • cycle: When sequence reaches its set_limit it starts from beginning.
  • nocycle: An exception will be thrown if sequence exceeds its max_value.
  • cache: This clause directs Oracle server to cache ’n’ values of a sequence. Its value is 20 by default.
  • nocache: It is the default behavior of the sequence.
  • order: It ensures that sequence values are generated in order. Generally, it is least usable clause in sequence definition but they are helpful in applications which use sequence as timestamp value.
  • noorder: It is the default behavior of the sequence.

Alter Sequence

ALTER SEQUENCE sequence_name -- You Can Alter any (one) of the following.. Or all...
INCREMENT BY n
MAXVALUE n
MINVALUE n
CYCLE | NOCYCLE
CACHE n | NOCACHE
ORDER n | NOORDER;

Drop Sequence

DROP SEQUENCE sequence_name;

Sequence Usage

-- Next Value (Sequence incremented to next value)
-- ===============================================
-- sequence_name.NEXTVAL
SELECT sequence_name.NEXTVAL FROM dual;

-- Current Value (Showing current incremented value of sequence)
-- =============================================================
-- sequence_name.CURRVAL
SELECT sequence_name.CURRVAL FROM dual;

Sequence Table

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

Examples

Prerequisite

DROP TABLE emps;
CREATE TABLE emps AS (SELECT employee_id AS id, first_name AS name, salary FROM employees WHERE 1=2);

Example 1

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
NOCYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spider man' , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Overwatch'  , 10000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);
-- ORA-08004: sequence EMPS_ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Altering Max Value
-- ===================
ALTER SEQUENCE emps_id_seq
MAXVALUE 10;

-- Inserting Again
-- ================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;

Example 2

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 21 -- This Value should be greater than 20 to use cycle.
CYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spidarman'  , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain America', 10001);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain Marvel' , 10002);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Wonder Women'   , 10003);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Flash'          , 10004);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Batman'         , 10005);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Deadpool'       , 10006);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Superman'       , 10007);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ant Man'        , 10008);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Daredevil'      , 10009);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Electra'        , 10010);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Watchmen'       , 10011);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Catwomen'       , 10012);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Vibe'           , 10013);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'killer Frost'   , 5000); -- Starting Again From 1
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt'     , 5000);

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;
DROP TABLE emps;

Example 3 – Primary Key Auto Increment (Up to 11g)

-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Without Primary Key
-- ==============================
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000001');
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000002');

-- Checking 
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

Example 4 – Unique, Sequential Code

  • Making “inv_name” auto-generated.
    • Eg: “INV/01042019/000001”
  • Making primary key(id) also auto-generated.
-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Function
-- ==================
CREATE OR REPLACE FUNCTION next_inv_name_fn 
  RETURN VARCHAR2
  AS
  BEGIN
  RETURN('INV/' || TO_CHAR(SYSDATE, 'DDMMYYYY') || '/' || LPAD(invoices_id_seq.NEXTVAL, 6, 0));
  END;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.CURRVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Values
-- =================
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);

-- Checking
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

References

MySQL – Connect DB with Python mysql.connector

MySQL Connector/Python enables Python programs to access MySQL databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249). It is written in pure Python and does not have any dependencies except for the Python Standard Library.

Syntax

>>> import mysql.connector
>>> connection = mysql.connector.connect(host="localhost", user="myusername", password="mypassword", database="mydatabase")

Example

>>> import mysql.connector
>>> 
>>> connection = mysql.connector.connect(host="localhost", user="sathiyan", password="mysql", database="mysql_db")
>>> cursor = connection.cursor()
>>> query = ("SELECT name FROM res_partner")
>>> query_execute = cursor.execute(query)
>>> rows = cursor.fetchall()
>>> for row in rows:
...     print(row[0])
...
"ABC Pvt. Ltd."
"A-Z Pvt. Ltd."
"Y! Ltd."
>>> cursor.close()
>>> connection.close()

Reference

Oracle – PIVOT & UNPIVOT

  • Pivot – Converting Rows Into Columns
  • Unpivot – Converting Columns Into Rows

PIVOT

Multiple rows are aggregated and transposed into columns, with each column representing a different range of aggregate data.

Syntax

SELECT ...
FROM   ...
PIVOT [XML]
   ( pivot_clause
     pivot_for_clause
     pivot_in_clause )
WHERE  ...
  • pivot_clause: defines the columns to be aggregated (pivot is an aggregate operation);
  • pivot_for_clause: defines the columns to be grouped and pivoted;
  • pivot_in_clause: defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).

Examples

-- Simple Pivot
-- ============== 
SELECT * FROM(SELECT department_id, TO_CHAR(hire_date, 'YYYY') AS YEAR FROM employees) 
 PIVOT (COUNT(*) FOR YEAR IN (2004, 2005))
 ORDER BY department_id;

-- Alias for the aggregate function "EMPLOYEES_HIRED" 
-- ===================================================
SELECT * FROM(SELECT department_id, TO_CHAR(hire_date,'YYYY') AS YEAR FROM employees)
 PIVOT (COUNT(*) AS EMPLOYEES_HIRED FOR YEAR IN (2004, 2005))
 ORDER BY department_id;

-- Alias For Aggregate function, Values Inside Parentheses
-- ===================================================
SELECT * FROM(SELECT department_id AS dept_id, TO_CHAR(hire_date,'YYYY') AS YEAR FROM employees)
 PIVOT (COUNT(*) AS EMP_HIRED FOR YEAR IN (2004 AS YEAR2004 , 2005 AS YEAR2005))
 ORDER BY department_id;

-- More than one aggregate function
-- ==================================
SELECT * FROM(SELECT department_id AS, TO_CHAR(hire_date, 'YYYY') AS YEAR, salary FROM employees)
 PIVOT (COUNT( * ) AS employees_hired,
        SUM(salary) AS salaries 
        FOR YEAR IN (2004 AS YEAR2004, 2005 AS YEAR2005))
 ORDER BY department_id;

-- Pivot Data Using WITH
-- =======================
WITH pivot_data AS (SELECT department_id, job_id, salary FROM employees)
SELECT *
  FROM pivot_data
 PIVOT(SUM(salary)
       FOR department_id
       IN (10,20,30,40)
     );

-- Pivot Data Using Pivot In Clause
-- =================================
SELECT *
  FROM (SELECT department_id, job_id, salary FROM employees)
 PIVOT (SUM(salary)             --<-- pivot_clause
        FOR department_id       --<-- pivot_for_clause
        IN (50,60,70,80,90,100) --<-- pivot_in_clause
		)
 WHERE job_id IN ('FI_ACCOUNT','SA_REP','ST_CLERK');
 
 
SELECT *
  FROM(SELECT department_id, job_id, salary FROM employees)
 PIVOT(SUM(salary)
	   FOR department_id 
	   IN (10,20,30,40));
	   
-- Alias All Elements Of Pivot Query
-- ====================================
SELECT *
  FROM(SELECT employee_id, department_id, job_id, salary FROM employees)
 PIVOT(SUM(salary) AS salary_total,
       COUNT(employee_id) AS emp_cnt
       FOR department_id IN (10 AS d10,
                             20 AS d20,
                             30 AS d30));
	
-- Doubled Number Of Pivot Columns(Doubled Number Of Aggregates)
-- ==================================				 
SELECT *
  FROM(SELECT department_id, job_id, salary FROM employees)
 PIVOT(SUM(salary) AS sum, 
       COUNT(salary) AS cnt
       FOR(department_id,job_id) IN ((30, 'PU_MAN')   AS d30_mgr,
                                     (30, 'PU_CLERK') AS d30_clrk));
									
-- Referencing Pivot Column Directly
-- ================================== 
SELECT d30_mgr_sum, d30_mgr_cnt
  FROM(SELECT department_id, job_id, salary FROM employees)
 PIVOT(SUM(salary) AS sum, 
       COUNT(salary) AS cnt
       FOR(department_id,job_id) IN ((30, 'PU_MAN')   AS d30_mgr,
                                     (30, 'PU_CLERK') AS d30_clrk));

-- Pivot XML
-- ==========
SELECT *
  FROM (SELECT department_id, job_id, salary FROM employees)
 PIVOT XML(SUM(salary) AS salaries
	          FOR department_id 
	          IN (SELECT department_id FROM departments WHERE department_id IN (10,20,30,40)));
            
SELECT job_id, 
       EXTRACT(department_id_xml, '/PivotSet/item/column') AS salary_elements
  FROM (SELECT department_id, job_id, salary FROM employees)
 PIVOT XML(SUM(salary) AS salaries
	          FOR department_id 
	          IN (SELECT department_id FROM departments WHERE department_id IN (10,20,30,40)))
 WHERE job_id = 'AD_ASST';

Outputs

-- Simple Pivot
-- ==================================
DEPARTMENT_ID       2004       2005
------------- ---------- ----------
           10          0          0
           20          1          1
           30          0          2
           40          0          0
           50          4         12
           60          0          1
           70          0          0
           80          5         10
           90          0          1
          100          0          2
          110          0          0
                       0          0

-- Alias for the aggregate function "EMPLOYEES_HIRED" 
-- ===================================================
DEPARTMENT_ID 2004_EMPLOYEES_HIRED 2005_EMPLOYEES_HIRED
------------- -------------------- --------------------
           10                    0                    0
           20                    1                    1
           30                    0                    2
           40                    0                    0
           50                    4                   12
           60                    0                    1
           70                    0                    0
           80                    5                   10
           90                    0                    1
          100                    0                    2
          110                    0                    0
                                 0                    0

-- Alias For Aggregate function, Values Inside Parentheses
-- ========================================================
DEPT_ID YEAR2004_EMP_HIRED YEAR2005_EMP_HIRED
------- ------------------ ------------------
     10                  0                  0
     20                  1                  1
     30                  0                  2
     40                  0                  0
     50                  4                 12
     60                  0                  1
     70                  0                  0
     80                  5                 10
     90                  0                  1
    100                  0                  2
    110                  0                  0
                         0                  0

-- More Than One Aggregate Function
-- ==================================
More Than One Aggregate Function
-- Pivot Data Using WITH & Pivot Data Using Pivot In Clause(Eg:2)
-- =========================================================
JOB_ID             10         20         30         40
---------- ---------- ---------- ---------- ----------
IT_PROG
AC_MGR
AC_ACCOUNT
ST_MAN
PU_MAN                                11000
AD_ASST          4400
AD_VP
SH_CLERK
FI_ACCOUNT
FI_MGR
PU_CLERK                              13900
SA_MAN
MK_MAN                     13000
PR_REP
AD_PRES
SA_REP
MK_REP                      6000
ST_CLERK
HR_REP                                            6500

-- Pivot Data Using Pivot In Clause
-- =================================
Pivot Data Using Pivot In Clause
-- Alias All Elements Of Pivot Query
-- ====================================
Alias All Elements Of Pivot Query
-- Doubled Number Of Pivot Columns(Doubled Number Of Aggregates)
-- ==================================
D30_MGR_SUM D30_MGR_CNT D30_CLRK_SUM D30_CLRK_CNT
----------- ----------- ------------ ------------
      11000           1        13900            5

-- Referencing Pivot Column Directly
-- ================================== 
D30_MGR_SUM D30_MGR_CNT
----------- -----------
      11000           1

UNPIVOT

An unpivot operation is the opposite of pivot (albeit without the ability to disaggregate the data). A simpler way of thinking about unpivot is that it turns pivoted columns into rows (one row of data for every column to be unpivoted).

Syntax

SELECT ...
FROM   ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
   ( unpivot_clause
     unpivot_for_clause
     unpivot_in_clause )
WHERE  ...
  • unpivot_clause: this clause specifies a name for a column to represent the unpivoted measure values.
  • unpivot_for_clause: the unpivot_for_clause specifies the name for the column that will result from our unpivot query. The data in this column describes the measure values in the unpivot_clause column.
  • unpivot_in_clause: this contains the list of pivoted columns (not values) to be unpivoted.

Examples

-- Simple Unpivot
-- ===============
 SELECT *
   FROM locations
UNPIVOT(column_v              --<-- unpivot_clause
        FOR column_h          --<-- unpivot_for_clause
        IN (country_id, city) --<-- unpivot_in_clause
        );

-- Unpivot Select Column
-- =======================
 SELECT location_id,
        column_h,
	column_v
   FROM locations
UNPIVOT (column_v 
         FOR column_h 
         IN (country_id, city) 
         );

 SELECT employee_id AS emp_id,
        first_name,
        property,
        property_val
   FROM employees
UNPIVOT (property_val
         FOR property
         IN(salary,commission_pct))
  WHERE department_id = 80;

-- Unpivot Exclude Nulls
-- ======================
 SELECT employee_id AS emp_id,
        first_name,
        property,
        property_val
   FROM employees
UNPIVOT EXCLUDE NULLS(property_val
                      FOR property
                      IN(salary,commission_pct))
  WHERE department_id = 30;

-- Unpivot Include Nulls
-- ======================
 SELECT employee_id,
        first_name,
        property,
        property_val
   FROM employees
UNPIVOT INCLUDE NULLS(property_val
                      FOR property
                      IN(salary,commission_pct))
  WHERE department_id = 30;

Output

-- Simple Unpivot
-- ===============
Unpivot
-- Unpivot Select Column
-- =======================
LOCATION_ID COLUMN_H   COLUMN_V
----------- ---------- ------------------------------
       1000 COUNTRY_ID IT
       1000 CITY       Roma
       1100 COUNTRY_ID IT
       1100 CITY       Venice
       1200 COUNTRY_ID JP
       1200 CITY       Tokyo
       1300 COUNTRY_ID JP
       1300 CITY       Hiroshima
       1400 COUNTRY_ID US
       1400 CITY       Southlake
       1500 COUNTRY_ID US
       1500 CITY       South San Francisco

-- Unpivot Select Column (Eg:2)
-- =======================
EMPLOYEE_ID FIRST_NAME   PROPERTY       PROPERTY_VAL
----------- ------------ -------------- ------------
        145 John         SALARY                14000
        145 John         COMMISSION_PCT           .4
        146 Karen        SALARY                13500
        146 Karen        COMMISSION_PCT           .3
        147 Alberto      SALARY                12000
        147 Alberto      COMMISSION_PCT           .3
        148 Gerald       SALARY                11000
        148 Gerald       COMMISSION_PCT           .3
        149 Eleni        SALARY                10500
        149 Eleni        COMMISSION_PCT           .2
        150 Peter        SALARY                10000
        150 Peter        COMMISSION_PCT           .3
        151 David        SALARY                 9500
        151 David        COMMISSION_PCT          .25
        152 Peter        SALARY                 9000
        152 Peter        COMMISSION_PCT          .25
        153 Christopher  SALARY                 8000
        153 Christopher  COMMISSION_PCT           .2
        154 Nanette      SALARY                 7500
        154 Nanette      COMMISSION_PCT           .2
        155 Oliver       SALARY                 7000

-- Unpivot Exclude Nulls
-- ======================
EMP_ID FIRST_NAME  PROPERTY  PROPERTY_VAL
------ ----------- --------- ------------
   114 Den         SALARY           11000
   115 Alexander   SALARY            3100
   116 Shelli      SALARY            2900
   117 Sigal       SALARY            2800
   118 Guy         SALARY            2600
   119 Karen       SALARY            2500

-- Unpivot Include Nulls
-- ======================
EMP_ID FIRST_NAME  PROPERTY       PROPERTY_VAL
------ ----------- -------------- ------------
   114 Den         SALARY                11000
   114 Den         COMMISSION_PCT
   115 Alexander   SALARY                 3100
   115 Alexander   COMMISSION_PCT
   116 Shelli      SALARY                 2900
   116 Shelli      COMMISSION_PCT
   117 Sigal       SALARY                 2800
   117 Sigal       COMMISSION_PCT
   118 Guy         SALARY                 2600
   118 Guy         COMMISSION_PCT
   119 Karen       SALARY                 2500
   119 Karen       COMMISSION_PCT

Pivot Feature Using CASE and DECODE

Prior to 11g Pivot and Unpivot were not introduced. So in order to obtain this Pivot feature in 10g we can use CASE and DECODE.

Examples

-- CASE
-- =======
SELECT department_id, COUNT(
    CASE TO_CHAR(hire_date, 'YYYY')
      WHEN '2004'
      THEN 1
    END) AS "2004", COUNT(
    CASE TO_CHAR(hire_date, 'YYYY')
      WHEN '2005'
      THEN 1
    END) AS "2005"
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- DECODE
-- =======
SELECT job_id
       SUM(DECODE(department_id,10,salary)) AS "D10_SAL",
       SUM(DECODE(department_id,20,salary)) AS "D20_SAL",
       SUM(DECODE(department_id,30,salary)) AS "D30_SAL",
       SUM(DECODE(department_id,40,salary)) AS "D40_SAL"
  FROM employees
 GROUP BY job_id;

References

Oracle – ROLLUP & CUBE

ROLLUP operators let you extend the functionality of GROUP BY clauses by calculating subtotals and grand totals for a set of columns. The CUBE operator is similar in functionality to the ROLLUP operator; however, the CUBE operator can calculate subtotals and grand totals for all permutations of the columns specified in it.

Note: The result of both the ROLLUP and the CUBE operators will be similar, if your data is grouped by only one column.

Example

Group By Single Column ROLLUP and CUBE

-- General Group By Single Column Query
-- =====================================
SELECT department_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- Group By Single Column ROLLUP
-- ==============================
SELECT department_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY ROLLUP(department_id)
ORDER BY department_id;

-- Group By Single Column CUBE
-- ============================
SELECT department_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY CUBE(department_id)
ORDER BY department_id;

-- Note: Above ROLLUP and CUBE will returns same value.

Output

-- General Group By Single Column Query
-- =====================================
DEPARTMENT_ID SALARY_SUM
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     156400
           60      28800
           70      10000
           80     304500
           90      58000
          100      51600
          110      20300
                    7000

-- Group By Single Column ROLLUP
-- ==============================
DEPARTMENT_ID SALARY_SUM
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     156400
           60      28800
           70      10000
           80     304500
           90      58000
          100      51600
          110      20300
                    7000
                  691400

-- Group By Single Column CUBE
-- ============================
DEPARTMENT_ID SALARY_SUM
------------- ----------
           10       4400
           20      19000
           30      24900
           40       6500
           50     156400
           60      28800
           70      10000
           80     304500
           90      58000
          100      51600
          110      20300
                    7000
                  691400

Group By Two Columns ROLLUP and CUBE

-- General Group By Two Columns Query
-- ===================================
SELECT department_id, job_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;

-- Group By Two Column ROLLUP
-- ===========================
SELECT department_id, job_id, SUM(salary) AS salary_sum
FROM employees
GROUP BY ROLLUP(department_id, job_id)
ORDER BY department_id, job_id;

-- Group By Two Column CUBE
-- =========================
SELECT department_id, 
       job_id, 
       SUM(salary) AS salary_sum
  FROM employees
 GROUP BY CUBE(department_id, job_id)
 ORDER BY department_id, job_id;

Output

-- General Group By Two Columns Query
-- ===================================
DEPARTMENT_ID JOB_ID     SALARY_SUM
------------- ---------- ----------
           10 AD_ASST          4400
           20 MK_MAN          13000
           20 MK_REP           6000
           30 PU_CLERK        13900
           30 PU_MAN          11000
           40 HR_REP           6500
           50 SH_CLERK        64300
           50 ST_CLERK        55700
           50 ST_MAN          36400
           60 IT_PROG         28800
           70 PR_REP          10000
           80 SA_MAN          61000
           80 SA_REP         243500
           90 AD_PRES         24000
           90 AD_VP           34000
          100 FI_ACCOUNT      39600
          100 FI_MGR          12000
          110 AC_ACCOUNT       8300
          110 AC_MGR          12000
              SA_REP           7000

-- Group By Two Column ROLLUP
-- =================================
DEPARTMENT_ID JOB_ID     SALARY_SUM
------------- ---------- ----------
           10 AD_ASST          4400
           10                  4400
           20 MK_MAN          13000
           20 MK_REP           6000
           20                 19000
           30 PU_CLERK        13900
           30 PU_MAN          11000
           30                 24900
           40 HR_REP           6500
           40                  6500
           50 SH_CLERK        64300
           50 ST_CLERK        55700
           50 ST_MAN          36400
           50                156400
           60 IT_PROG         28800
           60                 28800
           70 PR_REP          10000
           70                 10000
           80 SA_MAN          61000
           80 SA_REP         243500
           80                304500
           90 AD_PRES         24000
           90 AD_VP           34000
           90                 58000
          100 FI_ACCOUNT      39600
          100 FI_MGR          12000
          100                 51600
          110 AC_ACCOUNT       8300
          110 AC_MGR          12000
          110                 20300
              SA_REP           7000
                               7000
                             691400

-- Group By Two Column CUBE
-- ==================================
DEPARTMENT_ID JOB_ID     SALARY_SUM
------------- ---------- ----------
           10 AD_ASST          4400
           10                  4400
           20 MK_MAN          13000
           20 MK_REP           6000
           20                 19000
           30 PU_CLERK        13900
           30 PU_MAN          11000
           30                 24900
           40 HR_REP           6500
           40                  6500
           50 SH_CLERK        64300
           50 ST_CLERK        55700
           50 ST_MAN          36400
           50                156400
           60 IT_PROG         28800
           60                 28800
           70 PR_REP          10000
           70                 10000
           80 SA_MAN          61000
           80 SA_REP         243500
           80                304500
           90 AD_PRES         24000
           90 AD_VP           34000
           90                 58000
          100 FI_ACCOUNT      39600
          100 FI_MGR          12000
          100                 51600
          110 AC_ACCOUNT       8300
          110 AC_MGR          12000
          110                 20300
              AC_ACCOUNT       8300
              AC_MGR          12000
              AD_ASST          4400
              AD_PRES         24000
              AD_VP           34000
              FI_ACCOUNT      39600
              FI_MGR          12000
              HR_REP           6500
              IT_PROG         28800
              MK_MAN          13000
              MK_REP           6000
              PR_REP          10000
              PU_CLERK        13900
              PU_MAN          11000
              SA_MAN          61000
              SA_REP           7000
              SA_REP         250500
              SH_CLERK        64300
              ST_CLERK        55700
              ST_MAN          36400
                               7000
                             691400

Grouping Along With ROLLUP and CUBE

-- Grouping and Group By Two Column CUBE
-- ====================================== 
SELECT department_id, 
       job_id, 
       SUM(salary) AS salary_sum, 
       grouping_id(department_id, job_id) AS sub_total
  FROM employees
 GROUP BY CUBE(department_id, job_id)
 ORDER BY department_id, job_id;

-- Grouping and Group By Two Column CUBE (With NVL and Case Usage)
-- ====================================== 
SELECT NVL(department_id, 999), 
       NVL(job_id, 'All Jobs'), 
       SUM(salary) AS salary_sum, 
       CASE grouping_id(department_id, job_id)
       WHEN 0 THEN 'Job Sum'
       WHEN 1 THEN 'Dept Sub-Total'
       WHEN 2 THEN 'Job Sub-Total'
       WHEN 3 THEN 'Grand Total'
       END AS sub_total
  FROM employees
 GROUP BY CUBE(department_id, job_id)
 ORDER BY department_id, job_id;

Output

-- Grouping and Group By Two Column CUBE
-- ============================================
DEPARTMENT_ID JOB_ID     SALARY_SUM  SUB_TOTAL
------------- ---------- ---------- ----------
           10 AD_ASST          4400          0
           10                  4400          1
           20 MK_MAN          13000          0
           20 MK_REP           6000          0
           20                 19000          1
           30 PU_CLERK        13900          0
           30 PU_MAN          11000          0
           30                 24900          1
           40 HR_REP           6500          0
           40                  6500          1
           50 SH_CLERK        64300          0
           50 ST_CLERK        55700          0
           50 ST_MAN          36400          0
           50                156400          1
           60 IT_PROG         28800          0
           60                 28800          1
           70 PR_REP          10000          0
           70                 10000          1
           80 SA_MAN          61000          0
           80 SA_REP         243500          0
           80                304500          1
           90 AD_PRES         24000          0
           90 AD_VP           34000          0
           90                 58000          1
          100 FI_ACCOUNT      39600          0
          100 FI_MGR          12000          0
          100                 51600          1
          110 AC_ACCOUNT       8300          0
          110 AC_MGR          12000          0
          110                 20300          1
              AC_ACCOUNT       8300          2
              AC_MGR          12000          2
              AD_ASST          4400          2
              AD_PRES         24000          2
              AD_VP           34000          2
              FI_ACCOUNT      39600          2
              FI_MGR          12000          2
              HR_REP           6500          2
              IT_PROG         28800          2
              MK_MAN          13000          2
              MK_REP           6000          2
              PR_REP          10000          2
              PU_CLERK        13900          2
              PU_MAN          11000          2
              SA_MAN          61000          2
              SA_REP           7000          0
              SA_REP         250500          2
              SH_CLERK        64300          2
              ST_CLERK        55700          2
              ST_MAN          36400          2
                               7000          1
                             691400          3

-- Grouping and Group By Two Column CUBE (With NVL and Case Usage)
-- =========================================================
NVL(DEPARTMENT_ID,999) NVL(JOB_ID SALARY_SUM SUB_TOTAL
---------------------- ---------- ---------- --------------
                    10 AD_ASST          4400 Job Sum
                    10 All Jobs         4400 Dept Sub-Total
                    20 MK_MAN          13000 Job Sum
                    20 MK_REP           6000 Job Sum
                    20 All Jobs        19000 Dept Sub-Total
                    30 PU_CLERK        13900 Job Sum
                    30 PU_MAN          11000 Job Sum
                    30 All Jobs        24900 Dept Sub-Total
                    40 HR_REP           6500 Job Sum
                    40 All Jobs         6500 Dept Sub-Total
                    50 SH_CLERK        64300 Job Sum
                    50 ST_CLERK        55700 Job Sum
                    50 ST_MAN          36400 Job Sum
                    50 All Jobs       156400 Dept Sub-Total
                    60 IT_PROG         28800 Job Sum
                    60 All Jobs        28800 Dept Sub-Total
                    70 PR_REP          10000 Job Sum
                    70 All Jobs        10000 Dept Sub-Total
                    80 SA_MAN          61000 Job Sum
                    80 SA_REP         243500 Job Sum
                    80 All Jobs       304500 Dept Sub-Total
                    90 AD_PRES         24000 Job Sum
                    90 AD_VP           34000 Job Sum
                    90 All Jobs        58000 Dept Sub-Total
                   100 FI_ACCOUNT      39600 Job Sum
                   100 FI_MGR          12000 Job Sum
                   100 All Jobs        51600 Dept Sub-Total
                   110 AC_ACCOUNT       8300 Job Sum
                   110 AC_MGR          12000 Job Sum
                   110 All Jobs        20300 Dept Sub-Total
                   999 AC_ACCOUNT       8300 Job Sub-Total
                   999 AC_MGR          12000 Job Sub-Total
                   999 AD_ASST          4400 Job Sub-Total
                   999 AD_PRES         24000 Job Sub-Total
                   999 AD_VP           34000 Job Sub-Total
                   999 FI_ACCOUNT      39600 Job Sub-Total
                   999 FI_MGR          12000 Job Sub-Total
                   999 HR_REP           6500 Job Sub-Total
                   999 IT_PROG         28800 Job Sub-Total
                   999 MK_MAN          13000 Job Sub-Total
                   999 MK_REP           6000 Job Sub-Total
                   999 PR_REP          10000 Job Sub-Total
                   999 PU_CLERK        13900 Job Sub-Total
                   999 PU_MAN          11000 Job Sub-Total
                   999 SA_MAN          61000 Job Sub-Total
                   999 SA_REP           7000 Job Sum
                   999 SA_REP         250500 Job Sub-Total
                   999 SH_CLERK        64300 Job Sub-Total
                   999 ST_CLERK        55700 Job Sub-Total
                   999 ST_MAN          36400 Job Sub-Total
                   999 All Jobs         7000 Dept Sub-Total
                   999 All Jobs       691400 Grand Total

Reference

Oracle – Regular Expression Functions

Regular expressions are the search patterns which can be used to retrieve matching data and also for manipulating data.

  • The ‘source’ is a string which we want to represent in regular expression pattern.
  • The ‘regexp’ is a regular expression pattern.
  • The ‘position’ is a integer values specified the position to start search.
    • Default position is 1 mean begin of the original string.
  • The ‘occurrence’ is specifies positive integer number.
    • If occurrence value specify 0 then replace all matched.
    • And if occurrence value any positive number then replace only that number matched.
  • The ‘modes’ is a expression flag.
    • i – ignore case
    • c – case sensitive
    • n – match any character as well as match newline character
    • m – multi line
    • x – ignore whitespace

Practice Environment Creation

DROP TABLE emps;
CREATE TABLE emps AS(SELECT employee_id, first_name, last_name, hire_date, salary, phone_number  FROM employees WHERE 1=1 AND employee_id BETWEEN 100 AND 120); 
INSERT INTO emps VALUES(121, 'Sa256thi34yan23', 'Hello 45 World, Hi', '01-Jan-00', 19999, 19999.99);
INSERT INTO emps VALUES(122, 'Sa6thi34yan3', '   ', '01-Jan-00', 19999, 19999.99);

Study Link

REGEXP_LIKE

You can use it in the WHERE and HAVING clauses of a SELECT statement.
In a PL/SQL script, it returns a Boolean value. You can also use it in a CHECK constraint.
Optional Parameters – modes

Examples

-- Syntax
-- ========
-- REGEXP_LIKE(source, regexp, modes)

-- Returns first_name contains 'z'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, 'z');

-- Returns first_name contains 'tt'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, 'tt');

-- Returns first_name contains 'm' and 'M'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, 'm', 'i');

-- Returns first_name contains 'M' alone...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, 'M', 'c');

-- Returns first_name statrs with 'M'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '^M');

-- Returns first_name ends with 'A' or 'a'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, 'A$', 'i');

-- Returns first_name contains 'tt' or 'man'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, 'tt|man');

-- Returns first_name contains 'b' or 'B' or 'z' or 'Z' or 'E' or 'e'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[bzE]', 'i');

-- Returns first_name contains 'b' or 'z' or 'E'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[bzE]', 'c');

-- Returns first_name contains 'w', 'x', 'y' and 'z'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[w-z]');

-- Returns first_name contains 'w' or 'W', 'x' or 'X', 'y' or 'Y' and 'z' or 'Z'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[w-z]', 'i');

-- Returns all first_name that contain a letter in the range of 'w' and 'z', followed by the letter 'i'.
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[w-z]i');

-- Returns all first_name that contain a letter in the range of 'w' and 'z', followed by any character, followed by the letter 'n'.
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[w-z].[n]');

-- Returns all first_name that contain a letter in the range of 'w' and 'z', followed by any two character, followed by the letter 'r'.
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[w-z]..[r]');

-- Returns first_name contains 'll'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '[l]{2}');

-- Returns first_name contains string 'lle'...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '(lle)');

-- Returns first_name contains 'a' one or more times...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, 'a+');

-- Returns first_name contains numbers(digits)...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '\d');

-- Returns first_name contains non digit character...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '\D');

-- Returns first_name contains word character...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '\w');

-- Returns first_name contains non-word character(Eg: White Space)...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '\W');

-- Returns first_name contains whitespace...
SELECT first_name FROM emps WHERE REGEXP_LIKE(first_name, '\s');

-- Returns last_name contains last charater as 'i'...
SELECT first_name, last_name FROM emps WHERE REGEXP_LIKE(last_name, 'i\Z');

REGEXP_SUBSTR

It returns a string with the part of source matched by the regular expression.
If the match attempt fails, NULL is returned.
You can use REGEXP_SUBSTR with a single string or with a column.
Optional Parameters – position, occurrence, modes

Examples

-- Syntax
-- =======
-- REGEXP_SUBSTR(source, regexp, position, occurrence, modes)

-- Returns 'first_name' contains numeric digits for length of 2...
SELECT first_name, last_name, REGEXP_SUBSTR(
       last_name,
       '[[:digit:]]{2}',1,1)
 FROM emps;

-- Returns 'first_name' contains alphanumeric charater for length of 5... 
SELECT first_name, last_name, REGEXP_SUBSTR(
       first_name,
       '[[:alnum:]]{5}')
  FROM emps;

-- Returns 'first_name' contains alphabets for length of 5... 
SELECT first_name, REGEXP_SUBSTR(
       first_name,
       '[[:alpha:]]{5}')
  FROM emps;

-- Returns 'last_name' contains punctuation symbols... 
SELECT first_name, last_name, REGEXP_SUBSTR(
       last_name,
       '[[:punct:]]')
  FROM emps
 WHERE REGEXP_SUBSTR(last_name,'[[:punct:]]') 
IS NOT NULL;

-- Returns 'last_name' contains 2 space as character...
SELECT first_name, last_name, REGEXP_SUBSTR(
       last_name,
       '[[:space:]]{2}') 
  FROM emps 
 WHERE REGEXP_SUBSTR(last_name,'[[:space:]]{2}') 
IS NOT NULL;

-- Changing '11-93-34567' to '011-093-345'...
SELECT  LPAD(REGEXP_SUBSTR('11-93-34567', '[^-]+', 1, 1), 3, 0) || '-' ||
        LPAD(REGEXP_SUBSTR('11-93-34567', '[^-]+', 1, 2), 3, 0) || '-' ||
        LPAD(REGEXP_SUBSTR('11-93-34567', '[^-]+', 1, 3), 3, 0)
FROM    dual;

Combined Example

WITH strings AS ( 
  SELECT 'ABC123' AS str FROM dual UNION ALL 
  SELECT 'A1B2C3' AS str FROM dual UNION ALL 
  SELECT '123ABC' AS str FROM dual UNION ALL 
  SELECT '1A2B3C' AS str FROM dual UNION ALL
  SELECT 'LHRJFK/010315/SAXONMR'   AS str FROM dual UNION ALL 
  SELECT 'CDGLAX/050515/SMITHMRS'  AS str FROM dual UNION ALL 
  SELECT 'LAXCDG/220515/SMITHMRS'  AS str FROM dual UNION ALL 
  SELECT 'SFOJFK/010615/JONESMISS' AS str FROM dual 
) 
SELECT REGEXP_SUBSTR(str, '[0-9]') first_number,                     /* Returns the first number */ 
       REGEXP_SUBSTR(str, '[0-9].*') first_number_then_everything,   /* Returns the first number and the rest of the string */
       REGEXP_SUBSTR(str, '[A-Z][0-9]') first_letter_w_number_after, /* Returns the first letter with a following number */
       REGEXP_SUBSTR(str, '[A-Z]{6}') first_6_letters,               /* Returns the first string of 6 characters */  
       REGEXP_SUBSTR(str, '[0-9]+') first_matching_numbers,          /* Returns the first matching numbers */  
       REGEXP_SUBSTR(str, '[A-Z].*$') first_letter_then_all,         /* Returns the first letter followed by all other characters */  
       REGEXP_SUBSTR(str, '/[A-Z].*$') forward_slash_w_letter_after, /* Returns / followed by a letter then all other characters */
	   REGEXP_SUBSTR(str, '[^/]+', 1, 3) last_part_of_str		     /* Returns last part after / in a string*/   
  FROM strings;

Output

REGEXP Output

REGEXP_INSTR

It returns the beginning or ending position of a regex match in the source string.
This function takes the same parameters as REGEXP_SUBSTR, plus one more.
Set return_option to zero or omit the parameter to get the position of the first character in match.
Optional Parameters – position, occurrence, return_option, modes

Examples

-- Syntax
-- =======
-- REGEXP_INSTR(source, regexp, position, occurrence, return_option, modes)

-- Returns 'first_name' contains 'a' or 'b' or 'c' from position 2 for 2nd occurrence... 
SELECT first_name, last_name, REGEXP_INSTR(
       first_name,
       '[abc]', 2, 2)
  FROM emps
 WHERE REGEXP_INSTR(first_name, '[abc]', 2, 2) > 0;
  
-- Returns 'first_name' contains 'a' or 'b' or 'c' from position 1 for 1st occurrence... 
SELECT first_name, last_name, REGEXP_INSTR(
       first_name,
       '[abc]', 1, 1, 0)
  FROM emps 
 WHERE REGEXP_INSTR(first_name, '[abc]', 1, 1, 0) > 0;

-- Returns 'first_name' contains 'a' or 'b' or 'c' from position 1 for 1st occurrence''s next position... 
SELECT first_name, last_name, REGEXP_INSTR(
       first_name,
       '[abc]', 1, 1, 1)
  FROM emps 
 WHERE REGEXP_INSTR(first_name, '[abc]', 1, 1, 1) > 0;
 
-- Returns 'first_name' contains alphanumeric for length of 5 from position 1 for 1st occurrence... 
SELECT first_name, last_name, REGEXP_INSTR(
       first_name,
       '[[:alnum:]]{5}', 1, 1)
  FROM emps
 WHERE REGEXP_INSTR(first_name, '[[:alnum:]]{5}', 1, 1) > 0;
 
-- Returns 'first_name' contains numeric for length of 2 from position 1 for 1st occurrence... 
SELECT first_name, last_name, REGEXP_INSTR(
       first_name,
       '[[:digit:]]{2}', 1, 1)
  FROM emps
 WHERE REGEXP_INSTR(first_name, '[[:digit:]]{2}', 1, 1) > 0;

-- Returns 'first_name' contains numeric for length of 5 from poistion 1 for 1st occurrence''s next position... 
SELECT first_name, last_name, REGEXP_INSTR(
       first_name,
       '[[:digit:]]{2}', 1, 1, 1)
  FROM emps
 WHERE REGEXP_INSTR(first_name, '[[:digit:]]{2}', 1, 1, 1) > 0;

REGEXP_REPLACE

It returns the source string with one or all regex matches replaced.
If no matches can be found, the original string is replaced.
If you specify a positive number for occurrence only that match is replaced.
If you specify zero or omit the parameter, all matches are replaced.
Optional Parameters – position, occurrence, modes

Example

-- Syntax
-- =======
-- REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes)

-- Escape Character (Here '\.' Is ecaping '.')
SELECT REGEXP_REPLACE(phone_number,
	   '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
	   '(\1)-\2-\3') "REGEXP_REPLACE"
  FROM emps ORDER BY "REGEXP_REPLACE";

-- Replace '~' Symbol
SELECT REGEXP_REPLACE(REGEXP_SUBSTR('abc~dcb~dfg~123~457', '[^~]+', 1, 1), '([[:alpha:]]{3})', '(\1)'),
       REGEXP_SUBSTR('abc~dcb~dfg~123~457', '[^~]+', 1, 2), 
       REGEXP_SUBSTR('abc~dcb~dfg~123~457', '[^~]+', 1, 3),
       REGEXP_SUBSTR('abc~dcb~dfg~123~457', '[^~]+', 1, 4),
       REGEXP_SUBSTR('abc~dcb~dfg~123~457', '[^~]+', 1, 5)
FROM dual;

Combined Example

WITH strings AS (   
  SELECT 'abc123'             val FROM dual UNION ALL   
  SELECT '123abc'             val FROM dual UNION ALL   
  SELECT 'a1b2c3'             val FROM dual UNION ALL
  SELECT 'Hello  World'       val FROM dual UNION ALL   
  SELECT 'Hello        World' val FROM dual UNION ALL   
  SELECT 'Hello,   World  !'  val FROM dual UNION ALL
  SELECT 'AddressLine1'       val FROM dual UNION ALL   
  SELECT 'ZipCode'            val FROM dual UNION ALL   
  SELECT 'Country'            val FROM dual UNION ALL
  SELECT '2015-01-01'         val FROM dual UNION ALL   
  SELECT '2000-12-31'         val FROM dual UNION ALL   
  SELECT '900-01-01'          val FROM dual UNION ALL 
  SELECT 'NEW YORK'           val FROM dual UNION ALL   
  SELECT 'New York'           val FROM dual UNION ALL   
  SELECT 'new york'           val FROM dual
)
SELECT val "Value", 
     REGEXP_REPLACE(val, '[0-9]', '') col1,             /*Remove Numbers From Strings*/
     REGEXP_REPLACE(val, '[0-9]', '', 1, 1) col2,        /*Remove First Occurrence of Numbers*/
     REGEXP_REPLACE(val, ' {2,}', ' ') col3,             /*Convert multiple spaces into a single space*/
     LOWER(REGEXP_REPLACE(val, '([A-Z0-9])', '_\1', 2)) col4, /*Camel Case String to Lowercase With Underscores*/ 
     REGEXP_REPLACE(val, '([[:digit:]]+)-([[:digit:]]{2})-([[:digit:]]{2})', '\3.\2.\1') col5, /*Convert yyyy-mm-dd date formats to dd.mm.yyyy*/
     REGEXP_REPLACE(val, '[a-z]', '1', 1, 0, 'i') col6,    /*Case Insensitive*/ 
     REGEXP_REPLACE(val, '[a-z]', '1', 1, 0, 'c') col7,    /*Case Sensitive*/   
     REGEXP_REPLACE(val, '[a-zA-Z]', '1', 1, 0, 'c') col8  /*Case Sensitive Matching*/  
  FROM strings; 

REGEXP_COUNT

The REGEXP_COUNT function is used to count the specified string pattern.
The mode is same as REGEXP_LIKE function.
This will be useful for handling the email validations.
This function is only available in Oracle 11g and later.
Optional Parameters – position, modes.

Example

-- Syntax
-- =======
-- REGEXP_COUNT(source, regexp, position, modes)

SELECT REGEXP_COUNT('myaccount@gmail.com', '@') FROM dual;

-- If user wants to calculate how many time a is used in string after 2 positions.

SELECT REGEXP_COUNT('Sherlock Strange', 'e', 2, 'c') FROM dual;

References