Oracle sql foundation

of data types:
predefined data types
constructed types
user-defined types

ANSI Predefined data types:
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
NUMERIC
DECIMAL
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE PRECISION
BOOLEAN
DATE
TIME
TIMESTAMP
INTERVAL

Oracle supports constructed
(reference, rowtype,
collection) and user-defined
types. These constructions
mostly used for PL/SQL
programming.

There is no TIME equivalents in Oracle.
BOOLEAN is allowed in PL/SQL only.

Types
ANSI, DB2, and SQL/DS Data Types
User-Defined Types
Oracle-Supplied Types

Oracle built-in data types:
CHAR[(size[ BYTE| CHAR])]
NCHAR[(size[ BYTE| CHAR])]
VARCHAR2(size[ BYTE| CHAR])
NVARCHAR2(size)
NUMBER [(precision[, scale])]
FLOAT[(precision)]
BINARY_FLOAT
BINARY_DOUBLE
DATE
TIMESTAMP[(fractional_seconds_precision)]
TIMESTAMP[(fractional_seconds)] WITH TIME ZONE
TIMESTAMP[(fractional_seconds)] WITH LOCAL TIME ZONE
INTERVAL YEAR[(year_precision)] TO MONTH
INTERVAL DAY[(day_precision)] TO SECOND[(fract_sec)]
CLOB
NCLOB
BLOB
BFILE
LONG
RAW(size)
LONG RAW
ROWID
UROWID[(size)]

Character

Numeric

Date / Timestamp / Interval

LOB

Rowid

Raw data

The LONG is legacy data type
and provided for backward
compatibility only. Only one LONG field can be in a table.

types

ANSI data types which
not contradictory to native
(except TIME and BOOLEAN)

Data Types

data types:
CHARACTER
CHARACTER VARYING
CHARACTER LARGE OBJECT
BINARY
BINARY VARYING
BINARY LARGE OBJECT
NUMERIC
DECIMAL
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE PRECISION
BOOLEAN
DATE
TIME
TIMESTAMP
INTERVAL

in Oracle

CREATE TABLE test_ansi_data_types (
CHARACTER1 CHARACTER(10),
CHAR1 CHAR(10),
CHARACTER_VARYING
CHARACTER VARYING(10),
CHAR_VARYING CHAR VARYING(10),
NATIONAL_CHARACTER
NATIONAL CHARACTER(10),
NATIONAL_CHAR NATIONAL CHAR(10),
NCHAR1 NCHAR(10),
NATIONAL_CHARACTER_VARYING
NATIONAL CHARACTER VARYING(10),
NATIONAL_CHAR_VARYING
NATIONAL CHAR VARYING(10),
NCHAR_VARYING NCHAR VARYING(10),
NUMERIC1 NUMERIC(5,2),
DECIMAL1 DECIMAL(5,2),
INTEGER1 INTEGER,
INT1 INT,
SMALLINT1 SMALLINT,
FLOAT1 FLOAT,
DOUBLE_PRECISION DOUBLE PRECISION,
REAL1 REAL
);
DESC test_ansi_data_types
DROP TABLE test_ansi_data_types;

ANSY data types have been converted to Oracle native data types

countries
WHERE country_id IN ('US', 'ZM', 'ZW');

-- TRUE (blank-padded comparison)
SELECT CASE WHEN 'a ' = 'a '
THEN 'TRUE'
ELSE 'FALSE'
END
FROM dual;
-- FALSE (empty string is NULL)
SELECT CASE WHEN '' = ' '
THEN 'TRUE'
ELSE 'FALSE'
END
FROM dual;
-- TRUE (blank-padded comparison)
SELECT CASE WHEN USER = 'HR '
THEN 'TRUE'
ELSE 'FALSE'
END
FROM dual;

2012 © EPAM Systems, RD Dep.

Character Data Type Literals and Comparisons

Character values are compared on the basis of two measures:
Binary or linguistic sorting
Blank-padded or nonpadded comparison semantics

country_id, city, state_province
FROM locations
WHERE state_province IS NULL;

2012 © EPAM Systems, RD Dep.

Empty String is NULL in Oracle

What are the results of these queries?

Which query produces this result?

Any expression containing a null always evaluates to null.

Except concatenation!

SELECT CASE WHEN '' = NULL
THEN 'TRUE'
ELSE 'FALSE'
END
FROM dual;

SELECT CASE WHEN '' IS NULL
THEN 'TRUE'
ELSE 'FALSE'
END
FROM dual;

SELECT 'ABC' || '' || 'DEF',
concat('ABC', concat('', 'DEF')),
'ABC' || NULL || 'DEF',
concat('ABC', concat(NULL, 'DEF'))
FROM dual;

employees
WHERE last_name = 'King ';
SELECT employee_id, first_name, last_name
FROM employees
WHERE
CAST(last_name AS CHAR(20)) = 'King ';
-- Blank-padded comparison (country_id is CHAR)
SELECT country_id, country_name, region_id
FROM countries
WHERE country_id = 'US ';
SELECT country_id, country_name, region_id
FROM countries
WHERE cast(country_id as varchar2(2)) = 'US ';
SELECT country_id, country_name, region_id
FROM countries
WHERE country_id = cast('US ' as varchar2(10));

2012 © EPAM Systems, RD Dep.

Blank-padded and Nonpadded Comparison Semantics

subprogram that returns a single value (or “result”) based of its arguments values.
A function is subroutine used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.
Function may operate on zero, one, two, or more arguments:
Function_name
Function_name(argument, argument, ...)
A function without any arguments is similar to a pseudocolumn.
However, a pseudocolumn typically returns a different value for each row in the result set, whereas a function without any arguments typically returns the same value for each row.

in the Oracle SQL Language Reference and cannot be modified.
User-defined functions. Allow you to define your own logic, implemented as block of PL/SQL code.

return a single value, based on the input value(s).
Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column.
Analytic Functions Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.
Object Reference Functions Object reference functions manipulate REF values (references to objects).
Model Functions Facilitate SQL for Modeling operations.
OLAP Functions OLAP functions returns data from a dimensional object in two-dimension relational format.
Data Cartridge Functions Facilitate Data Cartridge development.

Subject of this module

Values
Character Functions Returning Number Values
NLS Character Functions
Datetime Functions
General Comparison Functions
Conversion Functions
NULL-Related Functions
Large Object Functions
Collection Functions
Hierarchical Functions
Data Mining Functions
XML Functions
Encoding and Decoding Functions
Environment and Identifier Functions

Subject of this module

(number), TRUNC (number)
SIGN
SIN, SINH
SQRT
TAN, TANH
WIDTH_BUCKET

The most useful functions are marked blue

NCHR
CONCAT
INITCAP, LOWER, UPPER
NLS_INITCAP, NLS_LOWER, NLS_UPPER
LPAD, RPAD
TRIM, LTRIM, RTRIM
NLSSORT
REGEXP_REPLACE, REGEXP_SUBSTR
REPLACE
SOUNDEX
SUBSTR
TRANSLATE
Character Functions Returning Number Values
ASCII
INSTR
LENGTH
REGEXP_COUNT, REGEXP_INSTR

ORA_DST_CONVERT, ORA_DST_ERROR
ROUND (date), TRUNC (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE, SYSTIMESTAMP
TO_CHAR (datetime)
TO_DSINTERVAL, TO_YMINTERVAL
TO_TIMESTAMP, TO_TIMESTAMP_TZ
TZ_OFFSET

(datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

result row based on groups of rows, rather than on single rows.
Aggregate functions can appear in select lists, in ORDER BY and
HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups.
In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view.
Use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

all) aggregate functions that take a single argument accept these clauses:
DISTINCT and UNIQUE, which are synonymous, cause an aggregate function to consider only distinct values of the argument expression.
ALL causes an aggregate function to consider all values, including all duplicates.
DISTINCT average of 1, 1, 1, and 3 is 2.
The ALL average is 1.5.
If you specify neither, then the default is ALL.
All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null.
COUNT and REGR_COUNT never return null, but return either a number or zero.
For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

For example, the following statement calculates the average of the maximum salaries of all the departments in the sample schema HR:
SELECT AVG(MAX(salary))
FROM employees
GROUP BY department_id;
AVG(MAX(SALARY))
----------------
10926.3333

IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.

Example

SELECT first_name, last_name,
CASE trunc(salary/5000)
WHEN 0 THEN 'LOW-PAID'
WHEN 1 THEN 'MID-PAID'
WHEN 2 THEN 'WELL-PAID'
ELSE 'EXCELLENT'
END AS SALARY_CATEGORY
FROM employees;
SELECT first_name, last_name,
DECODE(trunc(salary/5000),
0, 'LOW-PAID',
1, 'MID-PAID',
2, 'WELL-PAID',
'EXCELLENT') SALARY_CATEGORY
FROM employees;

CASE
WHEN salary < 5000 THEN 'LOW-PAID'
WHEN salary >= 5000 AND salary < 10000 THEN 'MID-PAID'
WHEN salary >= 10000 AND salary < 15000 THEN 'WELL-PAID'
ELSE 'EXCELLENT'
END AS SALARY_CATEGORY
FROM employees;
SELECT first_name, last_name,
CASE
WHEN salary < 5000
THEN 'LOW-PAID'
WHEN salary < 10000
THEN 'MID-PAID'
WHEN salary < 15000
THEN 'WELL-PAID'
ELSE 'EXCELLENT'
END AS SALARY_CATEGORY
FROM employees;

that is nested within another SQL statement.
SQL statements those accept subqueries:
DML: SELECT, INSERT, UPDATE, DELETE, MERGE
DDL: CREATE TABLE and CREATE VIEW
A SQL statement that includes a subquery as part of its code is considered the parent (or outer) to the subquery (or inner query).
A parent SQL statement may include one or more subqueries in its syntax.
Subqueries may have their own subqueries.

SELECT FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE SALARY >= (SELECT MAX(SALARY)*0.7 FROM EMPLOYEES)
ORDER BY SALARY DESC;

FIRST_NAME LAST_NAME SALARY
--------------- ----------- --------
Steven King 24000
Lex De Haan 17000
Neena Kochhar 17000

EMPLOYEES
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES)
ORDER BY SALARY DESC;
SELECT *
FROM (
SELECT FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
)
WHERE ROWNUM=1;

FIRST_NAME LAST_NAME SALARY
--------------- ----------- --------
Steven King 24000

Both return identical dataset
The second causes a mistake when more than one person has the greatest salary
Both use subqueries, but the nature of subqueries is different:
In the first – subquery acts like scalar (just number – maximum salary value)
In the second – subquery acts like dataset (row source).

row in its result
Multiple-row subqueries Return zero, one, or more rows
Multiple-column subqueries Return more than one column in its result
Scalar subqueries A single-row subquery consists of only one column
Correlated subqueries Reference column(columns) from the parent query(queries)

SELECT FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
WHERE SALARY = (
SELECT MAX(SALARY)
FROM EMPLOYEES
)
ORDER BY SALARY DESC;

SELECT *
FROM (
SELECT FIRST_NAME,
LAST_NAME,
SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
)
WHERE ROWNUM=1;

EMP
WHERE
(SUBSTR(EMP.LAST_NAME,1,1), SUBSTR(EMP.LAST_NAME,2,1)) = (
SELECT CHR(ROUND(AVG(ASCII(SUBSTR(LAST_NAME,1,1))))),
CHR(MEDIAN(ASCII(SUBSTR(LAST_NAME,2,1))))
FROM EMPLOYEES E
);

SELECT EMP.FIRST_NAME,
EMP.LAST_NAME,
EMP.JOB_ID
FROM EMPLOYEES EMP
WHERE
SUBSTR(EMP.LAST_NAME,1,1) || SUBSTR(EMP.LAST_NAME,2,1) = (
SELECT CHR(ROUND(AVG(ASCII(SUBSTR(LAST_NAME,1,1))))) ||
CHR(MEDIAN(ASCII(SUBSTR(LAST_NAME,2,1))))
FROM EMPLOYEES E
);

FIRST_NAME LAST_NAME JOB_ID
------------ ----------- ----------
Alexander Khoo PU_CLERK

DEPARTMENTS D
JOIN EMPLOYEES E ON (D.MANAGER_ID = E.EMPLOYEE_ID)
INTERSECT
SELECT DISTINCT MGR.EMPLOYEE_ID, MGR.FIRST_NAME,
MGR.LAST_NAME, MGR.SALARY
FROM EMPLOYEES E
JOIN EMPLOYEES MGR ON (E.MANAGER_ID = MGR.EMPLOYEE_ID);

SELECT MANAGERS.EMPLOYEE_ID, MANAGERS.FIRST_NAME,
MANAGERS.LAST_NAME, MANAGERS.SALARY
FROM (
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.SALARY
FROM DEPARTMENTS D
JOIN EMPLOYEES E ON (D.MANAGER_ID = E.EMPLOYEE_ID)
) HEADS_OF_DEPTS JOIN (
SELECT DISTINCT MGR.EMPLOYEE_ID, MGR.FIRST_NAME,
MGR.LAST_NAME, MGR.SALARY
FROM EMPLOYEES E
JOIN EMPLOYEES MGR ON (E.MANAGER_ID = MGR.EMPLOYEE_ID)
) MANAGERS
ON (HEADS_OF_DEPTS.EMPLOYEE_ID = MANAGERS.EMPLOYEE_ID);

MGR.LAST_NAME, MGR.SALARY
FROM (
DEPARTMENTS D
JOIN EMPLOYEES DMGR ON (D.MANAGER_ID = DMGR.EMPLOYEE_ID))
JOIN (
EMPLOYEES E
JOIN EMPLOYEES MGR ON (E.MANAGER_ID = MGR.EMPLOYEE_ID))
ON DMGR.EMPLOYEE_ID = MGR.EMPLOYEE_ID
;

MPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- --------
100 Steven King 24000
145 John Russell 14000
201 Michael Hartstein 13000
108 Nancy Greenberg 12008
205 Shelley Higgins 12008
114 Den Raphaely 11000
103 Alexander Hunold 9000
121 Adam Fripp 8200

a subject value to a set of values. Returns TRUE if the subject value equals any of the values in the set. Returns FALSE if the subquery returns no rows.
NOT IN. NOT used with IN to reverse the result. Returns TRUE if the subquery returns no rows.
EXISTS. An EXISTS condition tests for existence of rows in a subquery. Returns TRUE if a subquery returns at least one row.

(ANY, IN)

-- ORA-01427: single-row subquery returns more than one row
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE
SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES GROUP BY JOB_ID)
ORDER BY SALARY DESC;
-- Invalid logic
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE
SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEES GROUP BY JOB_ID)
ORDER BY SALARY DESC;

(Correlated subquery)

SELECT EMP.FIRST_NAME,
EMP.LAST_NAME,
JOB_ID,
EMP.SALARY
FROM EMPLOYEES EMP
WHERE SALARY = (
SELECT MAX(E.SALARY)
FROM EMPLOYEES E
WHERE E.JOB_ID = EMP.JOB_ID
)
ORDER BY EMP.SALARY DESC;

FIRST_NAME LAST_NAME JOB_ID SALARY
---------- ----------- ---------- ------
Steven King AD_PRES 24000
Neena Kochhar AD_VP 17000
Lex De Haan AD_VP 17000
John Russell SA_MAN 14000
Michael Hartstein MK_MAN 13000
Nancy Greenberg FI_MGR 12008
Shelley Higgins AC_MGR 12008
Lisa Ozer SA_REP 11500
Den Raphaely PU_MAN 11000
Hermann Baer PR_REP 10000
Alexander Hunold IT_PROG 9000
Daniel Faviet FI_ACCOUNT 9000
William Gietz AC_ACCOUNT 8300
Adam Fripp ST_MAN 8200
Susan Mavris HR_REP 6500
Pat Fay MK_REP 6000
Jennifer Whalen AD_ASST 4400
Nandita Sarchand SH_CLERK 4200
Renske Ladwig ST_CLERK 3600
Alexander Khoo PU_CLERK 3100

(EXIST, ALL)

SELECT EMP.FIRST_NAME,
EMP.LAST_NAME,
EMP.JOB_ID,
EMP.SALARY
FROM EMPLOYEES EMP
WHERE NOT EXISTS (
SELECT *
FROM EMPLOYEES E
WHERE E.SALARY > EMP.SALARY
AND E.JOB_ID = EMP.JOB_ID
)
ORDER BY EMP.SALARY DESC;

FIRST_NAME LAST_NAME JOB_ID SALARY
---------- ----------- ---------- ------
Steven King AD_PRES 24000
Neena Kochhar AD_VP 17000
Lex De Haan AD_VP 17000
John Russell SA_MAN 14000
Michael Hartstein MK_MAN 13000
Nancy Greenberg FI_MGR 12008
Shelley Higgins AC_MGR 12008
Lisa Ozer SA_REP 11500
Den Raphaely PU_MAN 11000
Hermann Baer PR_REP 10000
Alexander Hunold IT_PROG 9000
Daniel Faviet FI_ACCOUNT 9000
William Gietz AC_ACCOUNT 8300
Adam Fripp ST_MAN 8200
Susan Mavris HR_REP 6500
Pat Fay MK_REP 6000
Jennifer Whalen AD_ASST 4400
Nandita Sarchand SH_CLERK 4200
Renske Ladwig ST_CLERK 3600
Alexander Khoo PU_CLERK 3100