Sheet1

Topic
SQL Select

SQL Insert

SQL Examples of Joins

Page 1

Sheet1

SQL Explicit vs. Implicit Joins

SQL Group By Examples

SQL NULLs

Page 2

Sheet1

Normalization

Page 3

Sheet1

SQL SubQueries

Insert, Update, Delete Views

SQL Join Views

SQL Inline Views

SQL Nth Highest Salary

SQL Second Highest Salary
SQL Difference Truncate / Delete

Page 4

Sheet1
SQL Difference Truncate / Drop
SQL Difference HAVING / WHERE

SQL Difference CAST / CONVERT

SQL Difference NOT IN / NOT EXIST

SQL Difference IN / EXISTS
SQL Difference UNION / UNION ALL

SQL Difference Nested / Correlated Subquery

SQL REPLACE
SQL TOP

SQL SELECT INTO

SQL ALTER TABLE

Page 5

Sheet1

SQL Difference views / Materialized views

SQL Count

SQL Clustered / Non-Clustered Indexes

Page 6

Sheet1

SQL Delete Duplicate Records

SQL Difference Unique/Primary Key

SQL GETDATE()
SQL DATEDIFF()
SQL DATEADD()

SQL DATEPART()

SQL Convert()

Page 7

Sheet1
SQL Insert Into Select

SQL Insert Multiple Values In Table

SQL Referential Integrity

SQL Not Null Constraint

SQL Primary Key Constraint

SQL Foreign Key Constraint

Page 8

Sheet1

SQL Default Constraint

SQL Check Constraint

SQL ROLLUP

SQL CUBE

SQL STUFF()

SQL Count_Big

SQL Binary_Checksum

Page 9

Sheet1

SQL Checksum_AGG

SQL Index Include

SQL Covered Query

SQL Identity

SQL sp_columns

SQL Diff Local/Global Temporary Tables

SQL Stored Procedure

Page 10

Sheet1

SQL sp_who

SQL Session

SQL Dynamic SQL

SQL SQL Server Execution Plan

SQL sp_executesql

SQL Difference Execute/Execute()

Page 11

Sheet1

SQL Case Expression

SQL XML Variable Example

SQL Pivot

SQL Merge

SQL Compute / Compute by

SQL Has_perms_by_name

SQL Difference between Logins / Users

Page 12

Sheet1

SQL Diff between Table Variable/Temp Table

SQL Find Primary Key / Foreign Key in Table

SQL Columnstore Indexes

SQL Difference Primary / Candidate Key

Page 13

Sheet1

SQL Diff between Cross Join / Full Outer Join

SQL Interview Questions

PL/SQL Introduction

PL/SQL Procedures

Page 14

Sheet1

PL/SQL Functions

PL/SQL Collections

Page 15

Sheet1

PL/SQL Records

PL/SQL Table Based Records

PL/SQL Programmer Defined Records

Page 16

Sheet1

PL/SQL Cursor Based Records

PL/SQL Tables
PL/SQL Varrays
PL/SQL Nested Tables

PL/SQL Triggers

PL/SQL Cursors

Page 17

Sheet1

PL/SQL Implicit Cursors

PL/SQL Explicit Cursors

PL/SQL REF Cursors

Page 18

Sheet1

PL/SQL Cursor For Loop

PL/SQL Cursors with Parameters

Page 19

Sheet1

PL/SQL Where Current Of and For Update

Examples of PL/SQL Cursors

SQL PL/SQL Sitemap
Functions Alphabetical

Functions Category

Unique Constraints

Page 20

Sheet1

Grant/Revoke Privileges

Synonyms (create, drop)

Roles

Change Password
Tablespaces

Users

Page 21

Sheet1

Schemas

Literals (Constants)

Page 22

Sheet1

Exception Handling

Page 23

Sheet1

Loops and Conditional Statements

Sequences (Autonumber)

Page 24

Sheet1

Transactions

Comments within SQL

Oracle Error Messages

Page 25

Sheet1

Oracle System Tables

DELETE Statement

INTERSECT Operator

MINUS Operator

Page 26

Sheet1
JOIN TABLES
FROM Clause
ORDER BY Clause
HAVING Clause
SUM Function

MIN Function

MAX Function

OR Condition

AND and OR

LIKE Condition

REGEXP_LIKE Condition

IN Condition

NOT Condition

EXISTS Condition

CREATE TABLE
DROP TABLE

Page 27

Sheet1

GLOBAL TEMP Tables

LOCAL TEMP Tables

VIEW

Data Types

DELETE
DISTINCT

Page 28

Sheet1
IS NOT NULL

UNION

UNION ALL

After Delete Trigger

Page 29

Sheet1

After Insert Trigger

Page 30

Sheet1

After Update Trigger

Page 31

Sheet1

Alter Tablespace

Page 32

Sheet1

Before Delete Trigger

Page 33

Sheet1

Before Insert Trigger

Before Update Trigger

Close Cursor

Comments in SQL

Page 34

Sheet1
Commit

Create Schema Statement

Create Tablespace

Create User

Page 35

Sheet1

Cursor Attributes

Cursor CURRENT OF

Page 36

Sheet1

Cursor FOR UPDATE

Declare Cursor
Declare Variables

Defined Exception

Disable All Triggers

Disable Foreign Key
Disable Trigger
Drop Foreign Key
Drop Tablespace
Drop Trigger
Drop User
Enable All Trigger
Enable Foreign Key

Page 37

Sheet1
Enable Trigger
Error Messages
EXIT
Fetch Cursor

Find Default Tablespace

Find Users
Find Users Logged In
FOR Loop

Foreign Key

Foreign Key (cascade)

Foreign Key (set null)

Page 38

Sheet1

GOTO

IF-THEN-ELSE
Lock Table
Open Cursor

Page 39

Sheet1

REPEAT UNTIL

Rollback

Sequences

Set Default Tablespace

Set Transaction
SQLCODE

Page 40

Sheet1
SQLERRM

System Exception

System Tables

WHEN OTHERS

Page 41

Sheet1
WHILE Loop

ABS

ACOS

ADD_MONTHS

ASCII

ASCIISTR

ASIN

Page 42

Sheet1

ATAN

ATAN2

BFILENAME

BIN_TO_NUM

BITAND

Page 43

Sheet1
CARDINALITY

CAST

CEIL

CHARTOROWID

CHR

COALESCE

COMPOSE

CONCAT

Concat with ||

CORR

Page 44

Sheet1

COS

COSH

COVAR_POP

COVAR_SAMP

Page 45

Sheet1

CUME_DIST

CURRENT_DATE

CURRENT_TIMESTAMP

DBTIMEZONE

DECODE

DECOMPOSE

Page 46

Sheet1

DENSE_RANK

DUMP

EMPTY_BLOB

EMPTY_CLOB

EXP

EXTRACT

Page 47

Sheet1

FLOOR

FROM_TZ

GREATEST

GROUP_ID

HEXTORAW

INITCAP

Page 48

Sheet1

INSTR

INSTR2

INSTR4

Page 49

Sheet1

INSTRB

INSTRC

LAG

LAST_DAY

Page 50

Sheet1

LEAD

LEAST

LENGTH

Page 51

Sheet1

LENGTH2

LENGTH4

LENGTHB

Page 52

Sheet1

LENGTHC

LISTAGG

LN

LNNVL

LOCALTIMESTAMP

Page 53

Sheet1

LOG

LOWER

LPAD

LTRIM

Page 54

Sheet1

MEDIAN

MOD

MONTHS_BETWEEN

NANVL

NCHR

NEW_TIME

NEXT_DAY

Page 55

Sheet1

NULLIF

NUMTODSINTERVAL

NUMTOYMINTERVAL

NVL

NVL2

Page 56

Sheet1

POWER

RANK

RAWTOHEX

Page 57

Sheet1

REMAINDER

ROUND (dates)

Page 58

Sheet1

ROUND (numbers)

RPAD

Page 59

Sheet1

RTRIM

SESSIONTIMEZONE

SIGN

Page 60

Sheet1

SIN

SINH

SOUNDEX

SQRT

STDDEV

Page 61

Sheet1

SUBSTR

SYSDATE
SYSTIMESTAMP

SYS_CONTEXT

TAN

TANH

Page 62

Sheet1

TO_CHAR

TO_CLOB

TO_DATE

TO_DSINTERVAL

TO_LOB
TO_MULTI_BYTE
TO_NCLOB

TO_NUMBER

Page 63

Sheet1
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ

TO_YMINTERVAL

TRANSLATE

TRIM

Page 64

Sheet1

TRUNC (dates)

TRUNC (numbers)

Page 65

Sheet1

TZ_OFFSET

UID

UPPER

USER

USERENV

VARIANCE
VAR_POP
VAR_SAMP

Page 66

Sheet1

VSIZE

Advantages of PL/SQL

PL/SQL Conditional Statements

PL/SQL Iterative Statements

Parameters-Procedure, Function

PLSQL-Tutorial Home

Page 67

Sheet1
PL/SQL Home
PL/SQL Environment
PL/SQL Variables

PL/SQL Constants

PL/SQL Operators

PL/SQL Conditions

PL/SQL Strings

PL/SQL Arrays

Page 68

Sheet1

PL/SQL Packages

PL/SQL Date & Time

PL/SQL DBMS Output
PL/SQL Object Oriented

SQL Commands

SQL Logical Operators

SQL Group Functions
SQL Integrity Constraint
SQL RENAME

SQL Subquery

Page 69

Sheet1
SQL Indexes

Oracle Functions
SQL Tuning or SQL Optimization
Sql Tuning Tips, Optimize SQL
SELECT LIMIT Statement
SELECT TOP Statement
UPDATE Statement
WHERE Clause
IS NULL Condition
BETWEEN Condition
SQL ALIASES
SQL AND
SQL SELECT LIMIT
SQL CREATE TABLE AS

SQL AVG

Page 70

Sheet1
Description
SQL select is Data Retrieval Language(DRL) Command and used to get data from
database table(s).We can use projection operator (*) with select to get all
columns data from table(s).

SQL Insert is Data Manipulation Language (DML) Command and used to inset row(s) to
database tables.

SQL joins are used to get the data from multiple tables which are in relation.
SQL joins are of following types:
Equi join
Non Equi join
Natural join
Inner join
Outer join
Self join & etc

Page 71

Sheet1
Explicit Join uses 'JOIN' Keyword
whereas
Implicit joins doesnot use 'JOIN' Keyword

Used to group the result of select query.

Can handle using NVL, NVL2, COLEASE and never use not in, =!
all result tends to null

Page 72

With null because

Sheet1
Normalization is the process of efficiently organizing data in a database. There
are two goals of the normalization process:
eliminating redundant data (for example, storing the same data in more than one
table ) and
ensuring data dependencies make sense (only storing related data in a table).
Both of these are worthy goals as they reduce the amount of space a database
consumes and ensure that data is logically stored.
The Normal Forms
The database community has developed a series of guidelines for ensuring that
databases are normalized.
These are referred to as normal forms and are numbered from one (the lowest form
of normalization,
referred to as first normal form or 1NF) through five (fifth normal form or 5NF).
In practical applications, you'll often see 1NF , 2NF , and 3NF along with the
occasional 4NF.
Fifth normal form is very rarely seen and won't be discussed in this article.
First Normal Form (1NF):
First normal form (1NF) sets the very basic rules for an organized database:
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row
with a unique column or set of columns (the primary key).
Second Normal Form (2NF):
Second normal form (2NF) further addresses the concept of removing duplicative
data :
Meet all the requirements of the first normal form.
Remove subsets of data that apply to multiple rows of a table and place them
in separate tables.
Create relationships between these new tables and their predecessors through
the use of foreign keys.
Third Normal Form (3NF):
Third normal form (3NF) goes one large step further:

Page 73

Sheet1
A Subquery or Inner query or Nested query is a query within another SQL query and
embedded within the WHERE clause.
A subquery is used to return data that will be used in the main query as a
condition to further restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements
along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.
There are a few rules that subqueries must follow:
1.
Subqueries must be enclosed within parentheses.
2.
A subquery can have only one column in the SELECT clause, unless multiple
columns are in the main query for the subquery to compare its selected columns.
3.
An ORDER BY cannot be used in a subquery, although the main query can use an
ORDER BY. The GROUP BY can be used to perform the same function as the ORDER B3.Y
in a subquery.
4.
Subqueries that return more than one row can only be used with multiple
value operators, such as the IN operator.
5.
The SELECT list cannot include any references to values that evaluate to a
BLOB, ARRAY, CLOB, or NCLOB.
6.
A subquery cannot be immediately enclosed in a set function.
7.
The BETWEEN operator cannot be used with a subquery; however, the BETWEEN
operator can be used within the subquery.

When you update a view oracle will update the underlying base table.
You can use DML operations on a view if the view does not include Group by, Start
with, Connect
by, Distinct clauses or any subqueries and set operations(Union, union all,
intersect
An inline view is basically a subquery, except it is always in the FROM clause of
a SQL statement. The reason it is called a view is because it essentially
functions as a view as far as the entire query is concerned.

Interview question
Interview question
Delete is DML command Require commit to be executed, and truncate is DDL which is
autcommit in nature.
We can delete data conditionally by using delete command, where conditional data
deletion is not possible with TRUNCATE.TRUNCATE command deletes all available data

Page 74

Sheet1
Truncate deletes the data but drop delete data as well as table structure.

HAVING is used to check conditions after the aggregation takes place.

WHERE is used before the aggregation takes place.
Parameter
CAST CONVERT
ANSI standard Yes No
Data-type coverage Limited Full (Date & Date Time values supported)
Performance
No difference No difference
"NOT IN" anti-join fails because a NULL returned cannot be compared, Using Not In
it will return everything as null, but in
case
ofalways
'Not Exist'
itTRUE
can handle
value with null
EXISTS
returns
or FALSE.
IN can return TRUE, FALSE or UNKNOWN.
The UNION and UNION ALL operators allow you to combine multiple data sets, The
difference between the two union sorts
the
combined
sets
and removed
duplicates
while
UNION
ALLruns
doesfirst
not. and executes
With
a normal
nested
sub query,
the inner
SELECT
query
once, returning values to be used by the main query. A correlated sub query
however, executes once for each candidate row considered by the outer query. In
other words the inner query is driven by the outer query.
Steps of execution:
Nested sub query execution:
The inner query is executed first and finds a value
The outer query executes once, using the value from the inner query.
Correlated Sub query execution:
Get the candidate row (fetched by the outer query).
Execute the inner query using the value of the candidate row.
Use the values resulting from the inner query to test qualify or disqualify
the candidate.
It will replace character by another character
The SELECT TOP clause is used to specify the number of records to return.
Note: Not all database systems support the SELECT TOP clause.
The SELECT INTO statement selects data from one table and inserts it into a new
table.
Alter table is use to modify column name, add column, delete Column

Page 75

Sheet1
1) First difference between View and materialized view is that, In Views query
result is not stored in the disk or database but Materialized view allow to store
query result in disk or table.
2) Another difference between View vs materialized view is that, when we create
view using any table, rowid of view is same as original table but in case of
Materialized view rowid is different.
3) One more difference between View and materialized view in database is that, In
case of View we always get latest data but in case of Materialized view we need to
refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) This is continuation of first difference between View and Materialized View, In
case of view its only the logical view of table no separate copy of table but in
case of Materialized view we get physically separate copy of table
6)
difference
between
vs Materialized
View is that, In case of
to Last
get the
number of
recordView
selected
by sql Query

The difference is that, Clustered index is unique for any given table and we can
have only one clustered index on a table. The leaf level of a clustered index is
the actual data and the data is resorted in case of clustered index.
Whereas in case of non-clustered index the leaf level is actually a pointer to the
data in rows so we can have as many non-clustered indexes as we can on the db.

Page 76

Sheet1
We
1.
2.
3.
4.

can delete duplicate rows in following ways:

Using subquery
Using Rank function
Using self join
Analytic function

Unique can be null and should be unique, Primary key should be not null and
unique.
The GETDATE() function returns the current date and time from the SQL Server.
The DATEDIFF() function returns the time between two dates
The DATEADD() function adds or subtracts a specified time interval from a date.
The DATEPART() function is used to return a single part of a date/time, such as
year, month, day, hour, minute, etc.

The CONVERT() function is a general function that converts an expression of one

data type to another.
The CONVERT() function can be used to display date/time data in different formats.

Page 77

Sheet1
The INSERT INTO SELECT statement selects data from one table and inserts it into
an existing table. Any existing rows in the target table are unaffected.
We can insert multiple rows of explicit data in one SQL command in Oracle.

Referential integrity is a database concept that ensures that relationships

between tables remain consistent.
When one table has a foreign key to another table, the concept of referential
integrity states that you may not add a record to the table that contains the
foreign key unless there is a corresponding record in the linked table.
It also includes the techniques known as cascading update and cascading delete,
which ensure that changes made to the linked table are reflected in the primary
table.

The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means
that you cannot insert a new record, or update a record without adding a value to
this field.
It can be either table level or column level
Syntax (Column Level) :COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY
KEY]
Syntax (Table Level): CONSTRAINT [constraint name] PRIMARY KEY [column (s)]
It can be composite also.

Refer SQL Referential Intigrity.

Page 78

Sheet1
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is
specified.

The CHECK constraint is used to limit the value range that can be placed in a
column.
If you define a CHECK constraint on a single column it allows only certain values
for this column.
If you define a CHECK constraint on a table it can limit the values in certain
columns based on values in other columns in the row.

ROLLUP generates a result set that shows aggregates for a hierarchy of values in
the selected columns.

The CUBE operator generates a result set that is a multidimensional cube.

A multidimensional cube is an expansion of fact data, or data that records
individual events.
The expansion is based on columns that the user wants to analyze.
These columns are called dimensions.
The cube is a result set that contains a cross tabulation of all the possible
The SQL Server (Transact-SQL) STUFF function deletes a sequence of characters from
a source string and then inserts another sequence of characters into the source
string,
starting
atis
a specified
position.
COUNT_BIG
function
used to count
particular item in a group.
It returns number of items in a group.
It works same as COUNT function.
The difference between COUNT and COUNT_BIG function is COUNT function returns
integer and COUNT_BIG function returns big integer.
Returns the binary checksum value computed over a row of a table or over a list of
expressions. BINARY_CHECKSUM can be used to detect changes to a row of a table.

Page 79

Sheet1
CHECKSUM_AGG function is used to get checksum of values in a group.
It ignores null values in computation.
CHECKSUM_AGG can be used along with BINARY_CHECKSUM to detect changes in a table.
The order of the rows in the table does not affect the result of CHECKSUM_AGG.
In addition, CHECKSUM_AGG functions may be used with the DISTINCT keyword and the
GROUP BY clause.
A query is covered if all the columns it uses come from one or more indexes.
These columns include the columns you want the query to return as well as columns
in any JOIN, WHERE, HAVING, and ORDER BY clause.
A covered query typically is considered advantageous because data access through
indexes can be more efficient.
Creates an identity column in a table. This property is used with the CREATE TABLE
and ALTER TABLE Transact-SQL statements.

Returns column information for the specified objects that can be queried in the
current environment.

A local temporary table, #table_name, exists only for the duration of a user
session or the procedure that created the temporary table.
When the user logs off or when the procedure that created the table completes, the
local temporary table is lost. Multiple users can't share a local temporary table
because it is local to one user session.
You also can't grant or revoke permissions on the local temporary table.
A global temporary table, ##table_name, also exists for the duration of a user
session or the procedure that created the table.
When the last user session that references the table disconnects, the global
temporary
tableof
isprogram
lost. units you can create with PL/SQL and store in the
The main types
database are standalone procedures and functions, and packages.
Once stored in the database, these PL/SQL components, collectively known as stored
procedures, can be used as building blocks for several different applications.

Page 80

Sheet1
Provides information about current users, sessions, and processes in an instance
of the Microsoft SQL Server Database Engine.
The information can be filtered to return only those processes that are not idle,
that belong to a specific user, or that belong to a specific session.

When a user connects to a database, the SQL session is initialized.

The CONNECT command is used to establish a database connection.
With the CONNECT command, you can either invoke a connection or change connections
to the database.
For example, if you are connected as USER1, you can use the CONNECT command to
connect to the database as USER2. When this happens, the SQL session for USER1 is
You can use dynamic SQL to create applications that execute dynamic queries, whose
full text is not known until runtime.
Many types of applications need to use dynamic queries, including:
Applications that
criteria at runtime
Applications that
Applications that
constantly changing
Applications that

allow users to input or choose query search or sorting

allow users to input or choose optimizer hints at run time
query a database where the data definitions of tables are
query a database where new tables are created often

Executes a Transact-SQL statement or batch that can be reused many times, or one
that has been built dynamically.
The Transact-SQL statement or batch can contain embedded parameters.

EXECUTE command executes stored procedure where as EXECUTE() function takes

dynamic string as input and executes them.
Eg.
EXECUTE sp_help
GO
EXEC('EXEC sp_help')
GO

Page 81

Sheet1

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to i
The PIVOT operator takes data in separate rows, aggregates it and converts it into
columns

It is used to conditionally insert or update data depending on its presence, a

process also known as an "upsert".
The MERGE statement reduces table scans and can perform the operation in parallel
if required.

Generates totals that appear as additional summary columns at the end of the
result set.
When used with BY, the COMPUTE clause generates control-breaks and subtotals in
the result set. You can specify COMPUTE BY and COMPUTE in the same query.

Evaluates the effective permission of the current user on a securable

A "Login" grants the principal entry into the SERVER.

A "User" grants a login entry into a single DATABASE.
One "Login" can be associated with many users (one per database).
Each of the above objects can have permissions granted to it at its own level.

Page 82

Sheet1
Temporary Table:
1. Below is the sample example of Creating a Temporary Table, Inserting records
into it, retrieving the rows from it and then finally dropping the created
Temporary Table.
-- Create Temporary Table
CREATE TABLE #Customer
(Id INT, Name VARCHAR(50))
--Insert Two records
INSERT INTO #Customer
VALUES(1,'Basavaraj')
INSERT INTO #Customer
VALUES(2,'Kalpana')
--Reterive the records
SELECT * FROM #Customer
--DROP Temporary Table
DROP TABLE #Customer
GO
2. Temporary Table structure can be changed after its creation it implies we can
use DDL statements ALTER, CREATE, DROP.
3. Temporary Tables honor the explicit transactions defined by the user.
4. Temporary Tables are not allowed in User Defined Functions.
5. Temporary table supports adding Indexes explicitly after Temporary Table
creation and it can also have the implicit Indexes which are the result of Primary
and Unique Key constraint.
Table Variable:
1. Below is the sample example of Declaring a Table Variable, Inserting records
into it and retrieving the rows from it.
-- Create Table Variable
DECLARE @Customer TABLE
(
Id INT,
Name VARCHAR(50)
SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME =
C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'P'

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME

= C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'R'

The SQL Server in-memory columnstore index stores and manages data by using columnbased data storage and column-based query processing.
Columnstore indexes work well for data warehousing workloads that primarily
perform bulk loads and read-only queries.
Use the columnstore index to achieve up to 10x query performance gains over
Candidate Key A Candidate Key can be any column or a combination of columns that
can qualify as unique key in database. There can be multiple Candidate Keys in one
table. Each Candidate Key can qualify as Primary Key.
Primary Key A Primary Key is a column or a combination of columns that uniquely

Page 83

Sheet1
Difference between cross join and Full outer join
Cross Join :
No join conditions are specified.
Results in pairs of rows.
Results in Cartesian product of two tables.
Full Outer Join:
A combination of both left and right outer joins.
Results in every row from both of the tables , at least once.
Assigns NULL for unmatched fields.

PL/SQL is the procedural extension to SQL.

It is subprogram or database object which is perform action.

Page 84

Sheet1
A function is a named PL/SQL Block which is similar to a procedure. The major
difference between a procedure and a function is,a function must always return a
value, but a procedure may or may not return a value.

Collections are used in some of the most important performance optimization

features of PL/SQL, such as
=>BULK COLLECT:
SELECT statements that retrieve multiple rows with a single fetch, increasing the
speed of data retrieval.
=>FORALL:
Inserts, updates, and deletes that use collections to change multiple rows of data
very quickly
=>Table functions:
PL/SQL functions that return collections and can be called in the FROM clause of a
SELECT statement.
You can also use collections to work with lists of data in your program that are
not stored in database tables.

Page 85

Sheet1
A PL/SQL record is a data structure that can hold data items of different kinds.
Records consist of different fields, similar to a row of a database table.
PL/SQL can handle the following types of records:
1.Table-based
2.Cursor-based records
3.User-defined records

A record based on a table's column structure, Each field corresponds to -- and has
the same name as -- a column in a table.

PL/SQL provides a user-defined record type that allows you to define different
record structures. Records consist of different fields. Suppose you want to keep
track of your books in a library.

Page 86

Sheet1
Syntax :
CUSOR Cursor_Name IS SELECT Statement;
Variable Cursor_Name%rowtype;

Refer collection Type.

Refer collection Type.
Refer collection Type.
Oracle lets you define procedures called triggers that run implicitly when
INSERT, UPDATE, or DELETE statement is issued against the associated table
some cases, against a view, or when database system actions occur.
A procedure is explicitly run by a user, application, or trigger. Triggers
implicitly fired by Oracle when a triggering event occurs, no matter which
connected or which application is being used.

an
or, in
are
user is

Oracle creates a memory area, known as context area, for processing an SQL
statement, which contains all information needed for processing the statement, for
example, number of rows processed, etc.
A cursor is a pointer to this context area. PL/SQL controls the context area
through a cursor.

Page 87

Sheet1
Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, when there is no explicit cursor for the statement. Programmers cannot
control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor
is associated with this statement. For INSERT operations, the cursor holds the
data that needs to be inserted. For UPDATE and DELETE operations, the cursor
identifies the rows that would be affected.
In PL/SQL,
you can
refer
to the defined
most recent
implicit
cursor more
as the
SQL cursor,
Explicit
cursors
are
programmer
cursors
for gaining
control
over the
context area. An explicit cursor should be defined in the declaration section of
the PL/SQL Block. It is created on a SELECT Statement which returns more than one
row.
Working with an explicit cursor involves four steps:
Declaring the cursor for initializing in the memory
Opening the cursor for allocating memory
Fetching the cursor for retrieving data
Closing the cursor to release allocated memory

A ref cursor in Oracle PL/SQL is much like an ordinary PL/SQL cursor in that it
acts as a pointer to the result set of the cursor with which it is associated.
However, the difference is that a ref cursor can be assigned to different result
sets whereas a cursor is always associated with the same result set. Cursors and
ref cursors are not interchangeable.
The real purpose of ref cursors is to be able to share cursors and result sets
between the client and the Oracle server or between different subroutines. For
example you might open a cursor in an Oracle Forms client and then continue
working with the cursor on the server or you might open a cursor in a Java program
and then continue working with it in an Oracle PL/SQL stored procedure.
Ref cursors also come in two variants: strongly typed and weakly typed depending
on how likely you are (or want to) reuse a cursor variable. Weak ref cursor types
can be associated with any query whereas strong ref cursor types can only be

Page 88

Sheet1
You would use a CURSOR FOR LOOP when you want to fetch and process every record in
a cursor. The CURSOR FOR LOOP will terminate when all of the records in the cursor
have been fetched.
Syntax : FOR record_index in cursor_name

PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to
query.
PL/SQL Parameterized cursor define only datatype of parameter and not need to
define it's length.
Default values is assigned to the Cursor parameters. and scope of the parameters
are locally.
Parameterized cursors are also saying static cursors that can passed parameter
value when cursor are opened.

Page 89

Sheet1
PL/SQL Where current of
... WHERE CURRENT OF cursor
WHERE CURRENT is used as a reference to the current row when using a cursor to
UPDATE or DELETE the current row.
The cursor must be based on SQL that selects 'FOR UPDATE'

Refer topic 88, 89, 90

Functions are categorised into following categories:

1.String / Character Functions
2.Conversion Functions
3.Advanced Functions
4.Numeric / Mathematical Functions
5.Date / Time Functions
6.Miscellaneous Functions
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for
a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.

Page 90

Sheet1
SQL GRANT Command
SQL GRANT is a command used to provide access or privileges on the database
objects to the users.
SQL REVOKE Command:
The REVOKE command removes user access rights or privileges to the database
objects.

A synonym is an alternative name (or alias) for an object (like an table or view)
in the database. Objects can have many synonyms. Use the CREATE SYNONYM SQL
command to create synonyms and the DROP SYNONYM command to remove them. To get a
list of synonyms, query the USER_SYNONYMS view.

Roles are a collection of privileges or access rights. When there are many users
in a database it becomes difficult to grant or revoke privileges to users.
Therefore, if you define roles, you can grant or revoke privileges to users,
thereby automatically granting or revoking privileges. You can either create Roles
or use the system roles pre-defined by oracle.
Used to change/Reset the user password
A database is divided into one or more logical storage units called tablespaces.
Tablespaces are divided into logical units of storage called segments, which are
further divided into extents.
Extents
areinformation
a collection
of contiguous
blocks.
We can get
about
users by using
below tables
1. user_users.
2. dba_users.
3. all_users.

Page 91

Sheet1
A schema is a collection of logical structures of data, or schema objects.
A schema is owned by a database user and has the same name as that user.
Each user owns a single schema. Schema objects can be created and manipulated with
SQL and include the following types of objects:
Clusters
Database links
Database triggers
Dimensions
External procedure libraries
Indexes and index types
Java classes, Java resources, and Java sources
Materialized views and materialized view logs
Object tables, object types, and object views
Operators
Sequences
Stored functions, procedures, and packages
Synonyms
Tables and index-organized tables
Views
A literal is an explicit numeric, character, string, or BOOLEAN value not
represented by an identifier.
Below are literal types:
1.Text Literals
2.Numeric Literals
3.Datetime Literals
4.Interval Literals
The numeric literal 147 and the BOOLEAN literal FALSE are examples

Page 92

Sheet1
PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block
known as exception Handling. Using Exception Handling we can test the code and
avoid it from exiting abruptly.
There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
a) System exceptions are automatically raised by Oracle, when a program violates
a RDBMS rule. There are some system exceptions which are raised frequently, so
they are pre-defined and given a name in Oracle which are known as Named System
Exceptions.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
B) Those system exception for which oracle does not provide a name is known as
unamed system exception. These exception do not occur frequently. These Exceptions
have a code and an associated message.
There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.
We can assign a name to unnamed system exceptions using a Pragma called
EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a
programmer_defined exception name.
Steps to be followed to use unnamed system exceptions are
They are raised implicitly.
If they are not handled in WHEN Others they must be handled explicity.
To handle the exception explicity, they must be declared using Pragma
EXCEPTION_INIT as given above and handled referecing the user-defined exception
name in the exception section.

Page 93

Sheet1
Simple Loop, WHILE loop,
(LADDER)

For loop, If then End If; IF THEN ELSE END IF; IF ELSIF

Use the CREATE SEQUENCE statement to create a sequence, which is a database object
from which multiple users may generate unique integers. You can use sequences to
automatically generate primary key values.

Page 94

Sheet1
A transaction is a unit of work that is performed against a database.
Transactions are units or sequences of work accomplished in a logical order,
whether in a manual fashion by a user or automatically by some sort of a database
program.
A transaction is the propagation of one or more changes to the database.
For example, if you are creating a record or updating a record or deleting a
record from the table, then you are performing transaction on the table.
It is important to control transactions to ensure data integrity and to handle
database errors.
Transactions have the following four standard properties, usually referred to by
the acronym ACID:
Atomicity
Consistency
Isolation
Durability
There are following commands used to control transactions:
COMMIT: to save the changes.
ROLLBACK: to rollback the changes.
SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
Comments can make your application easier for you to read and maintain.
For example, you can include a comment in a statement that describes the purpose
of the statement within your application.
With the exception of hints, comments within SQL statements do not affect the
statement execution. Please refer to "Hints " on using this particular form of
comment.
A comment can appear between any keywords, parameters, or punctuation marks in a
statement. You can include a comment in a statement in two ways:
1. Begin the comment with a slash and an asterisk (/*).
Proceed with the text of the comment. This text can span multiple lines. End the
comment with an asterisk and a slash (*/).
The opening and terminating characters need not be separated from the text by a
space or a line break.
We can get information about errors using SQLCODE AND SQLERRM functions in oracle

Page 95

Sheet1
Below are the oracle system tables:
ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
The DELETE statement is used to delete records in a table.
The Oracle INTERSECT operator is used to return the results of 2 or more SELECT
statements. However, it only returns the rows selected by all queries. If a record
exists in one query and not in the other, it will be omitted from the INTERSECT
results.
Each SELECT statement within the Oracle INTERSECT must have the same number of
fields in the result sets with similar data types.

The SQL MINUS operator is used to return all rows in the first SELECT statement
that are not returned in the second SELECT statement.
Each SELECT statement within the MINUS query must have the same number of fields
in the result sets with similar data types.

Page 96

Sheet1
Refer Joins (topic 4)
From clause used to specify the tables or views while selecting the data from
database.
Use to sort the Result set, Default it will sort in ascending order to sort Desc
use Desc keyword
Syntax
Byaggregate
Column_name
;
Used toOrder
filter
function.
Generally used with Group by clause.
The SQL SUM function is used to return the sum of an expression in a SELECT
statement.
The SQL MIN function is used to return the minimum value of an expression in a
SELECT statement.
The SQL MAX function is used to return the maximum value of an expression in a
SELECT statement.
The SQL OR Condition is used to test multiple conditions, where the records are
returned when any one of the conditions are met.
It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

The SQL AND condition and OR condition can be combined to test for multiple
conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
When combining these conditions, it is important to use brackets so that the
database knows what order to evaluate each condition.
The SQL LIKE condition allows you to use wildcards to perform pattern matching.
The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or
DELETE statement.
The Oracle REGEXP_LIKE condition allows you to perform regular expression matching
in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement

The SQL IN condition is used to help reduce the need for multiple OR conditions in
a
SELECT,
INSERT,
UPDATE,
orknown
DELETE
The
SQL NOT
Condition
(also
asstatement
the SQL NOT Operator) is used to negate a
condition in a SELECT, INSERT, UPDATE, or DELETE statement.
The SQL EXISTS condition is used in combination with a subquery and is considered
to be met, if the subquery returns at least one row.
It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Used to create new database table.

Used to delete/erase the table from database.

Page 97

Sheet1
These tables do not reside in the system catalogs and are not persistent.
Temporary tables exist only during the connection that declared them and cannot be
referenced outside of that connection.
When the connection closes, the rows of the table are deleted, and the in-memory
description of the temporary table is dropped.
Temporary tables are useful when:
The table structure is not known before using an application.
Other users do not need the same table structure.
Data in the temporary table is needed while using the application.
TheLOCAL
tableTEMPORARY
can be declared
and distinct
dropped without
holding and
the embedded
locks on SQL
the system
Oracle
TABLES are
within modules
programs within Oracle sessions.

An Oracle VIEW, in essence, is a virtual table that does not physically exist.
Rather, it is created by a query joining one or more tables.

These data types may not be supported by all relational databases.

integer
smallint
numeric(p,s) Where p is a precision value; s is a scale value. For example,
numeric(6,2) is a number that has 4 digits before the decimal and 2 digits after
the decimal.
decimal(p,s) Where p is a precision value; s is a scale value.
real Single-precision floating point number
double precision Double-precision floating point number
float(p) Where p is a precision value.
char(x) Where x is the number of characters to store. This data type is space
padded to fill the number of characters specified.
varchar2(x) Where x is the number of characters to store. This data type does NOT
space pad.
bit(x) Where x is the number of bits to store.
bit varying(x) Where x is the number of bits to store. The length can vary up to
x.
date Stores year, month, and day values.
time Stores the hour, minute, and second values.
timestamp Stores year, month, day, hour, minute, and second values.
time with time zone Exactly the same as time, but also stores an offset from UTC
of the time specified.
timestamp with time zone Exactly the same as timestamp, but also stores an offset
from UTC of the time specified.
year-month interval Contains a year value, a month value, or both.
The SQL DELETE statement is a used to delete a one or more records from a table.
The SQL DISTINCT clause is used to remove duplicates from the result set of a
SELECT statement.

Page 98

Sheet1
The SQL IS NOT NULL Condition is used to test for a NOT NULL value in SELECT,
INSERT, UPDATE, or DELETE statement.
The SQL UNION operator is used to combine the result sets of 2 or more SELECT
statements.
It removes duplicate rows between the various SELECT statements.
Each SELECT statement within the UNION must have the same number of fields in the
result sets with similar data types.

The Oracle UNION ALL operator is used to combine the result sets of 2 or more
SELECT statements.
It returns all rows from the query (even if the row exists in more than one of the
SELECT statements).
Each SELECT statement within the Oracle UNION ALL operator must have the same
number of fields in the result sets with similar data types.
A trigger is a pl/sql block structure which is fired when a DML statements like
Insert, Delete, Update is executed on a database table. A trigger is triggered
automatically when an associated DML statement is executed.
Syntax for Creating a Trigger
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;

CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with
the given name or overwrites an existing trigger with the same name.
{BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the
trigger get fired. i.e for example: before or after updating a table. INSTEAD OF
is used to create a trigger on a view. before and after cannot be used to create a

Page 99

Sheet1
An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT
operation is executed.

Page 100

Sheet1
An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE
operation is executed

Page 101

Sheet1
The ALTER TABLESPACE statement is used to modify a tablespace or one of its data
files or temp files.
A tablespace is used to allocate space in the Oracle database where schema objects
are stored.

Page 102

Sheet1
A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE
operation is executed.

Page 103

Sheet1
A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT
operation is executed.

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE
operation is executed.

Close Cursor_name;
Comments are of two types:
1. Single line comment:
It is used to comment the entire line of a code segment.
2. Multi line comment:
It is used to comment multiple lines of code.

Page 104

Sheet1
To store the transaction permently we use commit statement; DDL are
autocommit (e.g CREATE, ALTER, DROP, TRUNCATE)
The CREATE SCHEMA statement does NOT actually create a schema in Oracle.
The CREATE SCHEMA statement is used only to create objects (ie: tables, views) in your schema in a single
SQL statement, instead of having to issue individual CREATE TABLE statements and CREATE VIEW
statements.
If an error occurs creating any of the objects in the CREATE SCHEMA statement, the Oracle database will roll
back all create statements (e: tables and view) in the CREATE SCHEMA statement.

The CREATE TABLESPACE statement is used to allocate space in the Oracle database where schema objects
are stored.
The CREATE TABLESPACE statement can be used to create the 3 kinds of tablespaces:
1. Permanent Tablespace
2. Temporary Tablespace
3. Undo Tablespace

This command is used to create database user.

To create user you should have sufficient priviledges.

Page 105

Sheet1
There are five cursor attributes: %isopen, %found, %notfound,
%rowcount and %bulk_rowcount.
%isopen
With %isopen it is possible to test whether a cursor was opened:
%found
%found returns true when the last fetch operation on the cursor
fetched a row. If %found is used on a cursor that is not open (%open
returns false), an ORA-01001: invalid cursor is raised. %notfound
returns null is returned:
%notfound
%notfound returns true when the last fetch operation on the cursor
did not fetch a row. If %notfound is used on a cursor that is not
open (%open returns false), an ORA-01001: invalid cursor is raised.
%notfound returns null is returned:
%rowcount

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement,
you can use the WHERE CURRENT OF statement.

Page 106

Sheet1

The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set.
You are not required to make changes to the records in order to use this statement.
The record locks are released when the next commit or rollback statement is issued.
A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code.

In Oracle/PLSQL, a variable allows a programmer to store data temporarily during the execution of code.

Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined
already by PL/SQL.
These are called Named Programmer-Defined Exceptions
Used to disable triggers specified on particular table.
Used to disable to foreign key in delete operations.
Used to disable trigger.
Used to remove constraint from a table.
The DROP TABLESPACE statement is used to remove a tablespace from the Oracle database.
A tablespace is used to allocate space in the Oracle database where schema objects are stored.
Used to erase trigger from database.
Used to remove database user.

Used to enable all triggers specified on a table.

Used to enable foreign key on a table

Page 107

Sheet1
Used to enable a trigger
Refer SQLCODE and SQLERRM functions
Used to gracefully terminate the user session. Changes are saved permanently before closing the session.
Used to get the value(s) from the cursor. To fetch the value from cursor, cursor should be in open state.

Used to find table space

Used to know the current users

Used to find all users logged in.
It is iteration control statement used in pl/sql

used to specify relationship between tables.

We cannot delete parent table data if table are related with normal foreign key relationship.
If we create foreign key with cascade in child table, then if we delete the data from parent table, data from child
table related to deleted record in parent table also deleted.

We cannot delete parent table data if table are related with normal foreign key relationship.
If we create foreign key with SET NULL in child table, then if we delete the data from parent table, data from
child table related to deleted record in parent table are set to null.

Page 108

Sheet1

The GOTO statement causes the code to branch to the label after the GOTO statement.
Refer conditoinal statements

The LOCK TABLE statement is used to lock tables, table partitions,

or table subpartitions.
Used to open cursor for fetching values.

Page 109

Sheet1

In MySQL, the REPEAT statement is used when you do not know how many times you want the loop body to
execute.

To restore the transaction

Use the CREATE SEQUENCE statement to create a sequence, which is a database object
from which multiple users may generate unique integers. You can use sequences to
automatically generate primary key values.

First, make sure that you have created a permanent tablespace.

Next you will need to change the Oracle database to use your permanent tablespace as the default permanent
tablespace.
In Oracle, the SET TRANSACTION statement is used to set a transaction as read-only, set a transaction as
read/write,
set a transaction's isolation level, assign a name to a transaction, or assign a rollback segment to a transaction.

SQLCODE: It returns the error number for the last encountered error.

Page 110

Sheet1
The SQLERRM function returns the error message associated with the
most recently raised error exception. This function should only be
used within the Exception Handling section of your code.
Named system exceptions are exceptions that have been given names by PL/SQL.
They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.
Below are the system exceptions:
DUP_VAL_ON_INDEX
TIMEOUT_ON_RESOURCE
TRANSACTION_BACKED_OUT
INVALID_CURSOR
NOT_LOGGED_ON
LOGIN_DENIED.
NO_DATA_FOUND
TOO_MANY_ROWS.
ZERO_DIVIDE
INVALID_NUMBER
STORAGE_ERROR
PROGRAM_ERROR
VALUE_ERROR
CURSOR_ALREADY_OPEN.
Refer Oracle system tables (topic 114)

The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named
System Exceptions and Named Programmer-Defined Exceptions.

Page 111

Sheet1
Refer looping statements

The Oracle/PLSQL ABS function returns the absolute value of a

number.

The Oracle/PLSQL ACOS function returns the arc cosine of a number.

The Oracle/PLSQL ADD_MONTHS function returns a date plus n months.

The Oracle/PLSQL ASCII function returns the NUMBER code that

represents the specified character.

The Oracle/PLSQL ASCIISTR function converts a string in any

character set to an ASCII string using the database character set.
ASCIISTR( string )

The Oracle/PLSQL ASIN function returns the arc sine of a number.

Page 112

Sheet1
The Oracle/PLSQL ATAN function returns the arc tangent of a number.

The Oracle/PLSQL ATAN2 function returns the arc tangent of n and m.

The Oracle/PLSQL BFILENAME function returns a BFILE locator for a

physical LOB binary file.

The Oracle/PLSQL BIN_TO_NUM function converts a bit vector to a

number.

The Oracle/PLSQL BITAND function returns an integer representing an

AND operation on the bits of expr1 and expr2.

Page 113

Sheet1
The Oracle/PLSQL CARDINALITY function returns the number of elements
in a nested table.
The Oracle/PLSQL CAST function converts one datatype to another.

The Oracle/PLSQL CEIL function returns the smallest integer value

that is greater than or equal to a number.

The Oracle/PLSQL CHARTOROWID function converts a char, varchar2,

nchar, or nvarchar2 to a rowid.

The Oracle/PLSQL CHR function is the opposite of the ASCII function.

It returns the character based on the NUMBER code.

COALESCE will return the first non-NULL item in its parameter list
The Oracle/PLSQL COMPOSE function returns a Unicode string.

The Oracle/PLSQL CONCAT function allows you to concatenate two

strings together.

|| is a concatenation operator used to concat twi strings.

The Oracle/PLSQL CORR function returns the coefficient of
correlation of a set of number pairs.

Page 114

Sheet1

The Oracle/PLSQL COS function returns the cosine of a number.

The Oracle/PLSQL COSH function returns the hyperbolic cosine of a

number.
COSH( number )
COSH(0.2)
Result: 1.02006675561908
COSH(0.35)
Result: 1.06187781915599
COSH(-3.15)
Result: 11.6894583539049
COSH(200)
Result: 3.61298688406287E86
COSH(0)
Result: 1
The Oracle/PLSQL COVAR_POP function returns the population covariance of a set of number pairs.

The Oracle/PLSQL COVAR_SAMP function returns the sample covariance of a set of number pairs.

Page 115

Sheet1
The Oracle/PLSQL CUME_DIST function returns the cumulative
distribution of a value in a group of values. The CUME_DIST function
will return a value that is >0 and <=1.
The CUME_DIST function can be used two ways - as an Aggregate
function or as an Analytic function.
CUME_DIST Function Syntax #1 - Used as an Aggregate Function
As an Aggregate function, the CUME_DIST function returns the
relative position of a row within a group of rows.

The MySQL CURRENT_DATE function returns the current date

The Oracle/PLSQL CURRENT_TIMESTAMP function returns the current date

and time in the time zone of the current SQL session as set by the
ALTER SESSION command. It returns a TIMESTAMP WITH TIME ZONE value.
The Oracle/PLSQL DBTIMEZONE function returns the database time zone
as a time zone offset (in the following format: '[+|-]TZH:TZM') or a
time zone region name.
The Oracle/PLSQL DECODE function has the functionality of an IF-THENELSE statement.

The Oracle/PLSQL DECOMPOSE function accepts a string and returns a Unicode string.

Page 116

Sheet1
As an Analytic function, the DENSE_RANK function returns the rank of
each row of a query with respective to the other rows.
The syntax for the DENSE_RANK function when used as an Analytic
function is:

The Oracle/PLSQL DUMP function returns a varchar2 value that

includes the datatype code, the length in bytes, and the internal
representation of the expression.

The Oracle/PLSQL EMPTY_BLOB function can be used to initialize a LOB

column to EMPTY in an INSERT statement or UPDATE statement or it can
be used initalize a LOB variable.
The Oracle/PLSQL EMPTY_CLOB function can be used to initialize a LOB
column to EMPTY in an INSERT statement or UPDATE statement or it can
be used initalize a LOB variable.

The Oracle/PLSQL EXP function returns e raised to the nth power,

where e = 2.71828183.

The Oracle/PLSQL EXTRACT function extracts a value from a date or

interval value.

Page 117

Sheet1
The Oracle/PLSQL FLOOR function returns the largest integer value
that is equal to or less than a number.

The Oracle/PLSQL FROM_TZ function converts a TIMESTAMP value (given

a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.
The Oracle/PLSQL GREATEST function returns the greatest value in a
list of expressions.
Note If the datatypes of the expressions are different, all
expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character
is considered greater than another if it has a higher character set
value.
The Oracle/PLSQL GROUP_ID function assigns a number to each group
resulting from a GROUP BY clause. The GROUP_ID function is most
commonly used to identify duplicated groups in your query results.
For each unique group, the GROUP_ID function will return 0. When a
duplicated group is found, the GROUP_ID function will return a value
>0.
You could use the HAVING clause to eliminated duplicated group.
The Oracle/PLSQL HEXTORAW function converts a hexadecimal value into
a raw value.

The Oracle/PLSQL INITCAP function sets the first character in each word to uppercase and the rest to
lowercase.

Page 118

Sheet1

The Oracle/PLSQL INSTR function returns the location of a substring

in a string.

The Oracle/PLSQL INSTR2 function returns the location of a substring

in a string, using UCS2 code points.

The Oracle/PLSQL INSTR4 function returns the location of a substring in a string, using UCS4 code points

Page 119

Sheet1

The Oracle/PLSQL INSTRB function returns the location of a substring in a string, using bytes instead of
characters.

The Oracle/PLSQL INSTRC function returns the location of a substring in a string, using Unicode complete
characters.

The Oracle/PLSQL LAG function is an analytic function that lets you

query more than one row in a table at a time without having to join
the table to itself. It returns values from a previous row in the
table. To return a value from the next row, try using the LEAD
function.

The Oracle/PLSQL LAST_DAY function returns the last day of the month
based on a date value.

Page 120

Sheet1
The Oracle/PLSQL LEAD function is an analytic function that
query more than one row in a table at a time without having
the table to itself. It returns values from the next row in
table. To return a value from a previous row, try using the
function.

lets you
to join
the
LAG

The Oracle/PLSQL LEAST function returns the smallest value in a list

of expressions.

The Oracle/PLSQL LENGTH function returns the length of the specified

string.

Page 121

Sheet1

The Oracle/PLSQL LENGTH2 function returns the length of the

specified string, using UCS2 code points.

The Oracle/PLSQL LENGTH4 function returns the length of the

specified string, using UCS4 code points.

The Oracle/PLSQL LENGTHB function returns the length of the

specified string, using bytes instead of characters.

Page 122

Sheet1

The Oracle/PLSQL LENGTHC function returns the length of the

specified string, using Unicode complete characters.

The Oracle/PLSQL LISTAGG function concatenates values of the

measure_column for each GROUP based on the order_by_clause.

The Oracle/PLSQL LN function returns the natural logarithm of a

number.

The Oracle/PLSQL LNNVL function is used in the WHERE clause of a SQL

statement to evaluate a condition when one of the operands may
contain a NULL value.
The Oracle/PLSQL LOCALTIMESTAMP function returns the current date
and time in the time zone of the current SQL session as set by the
ALTER SESSION command. It returns a TIMESTAMP value.

Page 123

Sheet1

The Oracle/PLSQL LOG function returns the logarithm of n base m.

The Oracle/PLSQL LOWER function converts all letters in the

specified string to lowercase. If there are characters in the string
that are not letters, they are unaffected by this function.

The Oracle/PLSQL LPAD function pads the left-side of a string with a

specific set of characters (when string1 is not null).

The Oracle/PLSQL LTRIM function removes all specified characters

from the left-hand side of a string.

Page 124

Sheet1
The Oracle/PLSQL MEDIAN function returns the median of an
expression.

The Oracle/PLSQL MOD function returns the remainder of m divided by

n.

The Oracle/PLSQL MONTHS_BETWEEN function returns the number of

months between date1 and date2.
The Oracle/PLSQL NANVL function lets you substitute a value for a floating point number such as
BINARY_FLOAT or BINARY_DOUBLE,
when a Nan (Not a number) value is encountered.
This is most commonly used to convert Nan (Not a number) values into either NULL or 0.

The Oracle/PLSQL NCHR function returns the character based on the number_code in the national character
set.

The Oracle/PLSQL NEW_TIME function returns a date in time zone1 to a

date in time zone2.

The Oracle/PLSQL NEXT_DAY function returns the first weekday that is

greater than a date.

Page 125

Sheet1

The Oracle/PLSQL NULLIF function compares expr1 and expr2. If expr1

and expr2 are equal, the NULLIF function returns NULL. Otherwise, it
returns expr1.

The Oracle/PLSQL NUMTODSINTERVAL function converts a number to an INTERVAL DAY TO SECOND

literal.

The Oracle/PLSQL NUMTOYMINTERVAL function converts a number to an INTERVAL YEAR TO MONTH

literal.

The Oracle/PLSQL NVL function lets you substitute a value when a

null value is encountered.

The Oracle/PLSQL NVL2 function extends the functionality found in

the NVL function. It lets you substitutes a value when a null value
is encountered as well as when a non-null value is encountered.

Page 126

Sheet1

The Oracle/PLSQL POWER function returns m raised to the nth power.

As an Analytic function, the RANK function returns the rank of each

row of a query with respective to the other rows.

The Oracle/PLSQL RAWTOHEX function converts a raw value into a hexadecimal value.
One of our viewers says that this function comes in handy to move a varchar value to a blob field.

Page 127

Sheet1

The Oracle/PLSQL REMAINDER function returns the remainder of m

divided by n.

The Oracle/PLSQL ROUND function returns a date rounded to a specific

unit of measure.

Page 128

Sheet1

The Oracle/PLSQL ROUND function returns a number rounded to a

certain number of decimal places.

The Oracle/PLSQL RPAD function pads the right-side of a string with

a specific set of characters (when string1 is not null).
RPAD( string1, padded_length, [ pad_string ] )

Page 129

Sheet1

The Oracle/PLSQL RTRIM function removes all specified characters

from the right-hand side of a string.

The Oracle/PLSQL SESSIONTIMEZONE function returns the current session's time zone as a time zone offset
(in the following format: '[+|-]TZH:TZM') or a time zone region name.

The Oracle/PLSQL SIGN function returns a value indicating the sign

of a number.

Page 130

Sheet1

The Oracle/PLSQL SIN function returns the sine of n.

The
SINH function
returnsfunction
the hyperbolicreturns
sine of n.
TheOracle/PLSQL
Oracle/PLSQL
SOUNDEX

a phonetic representation

(the way it sounds) of a string.

The SOUNDEX function algorithm is as follows:
The SOUNDEX function return value will always begin with the
first letter of string1.
The SOUNDEX function uses only the first 5 consonants to
determine the NUMERIC portion of the return value, except if the
first letter of string1 is a vowel.
The SOUNDEX function is not case-sensitive. What this means is
that both uppercase and lowercase characters will generate the same
SOUNDEX function return value.

The Oracle/PLSQL SQRT function returns the square root of n.

The Oracle/PLSQL STDDEV function returns the standard deviation of a set of numbers.
The STDDEV function can be used two ways - as an Aggregate function or as an Analytic function.

Page 131

Sheet1

The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.

The Oracle/PLSQL SYSDATE function returns the current system date

and time on your local database.

The Oracle/PLSQL SYSTIMESTAMP function returns the current system date and time (including fractional
seconds and time zone) on your local database.

The Oracle/PLSQL SYS_CONTEXT function can be used to retrieve information about the Oracle environment.

The Oracle/PLSQL TAN function returns the tangent of n.

The Oracle/PLSQL TANH function returns the hyperbolic tangent of n.

Page 132

Sheet1

The Oracle/PLSQL TO_CHAR function converts a number or date to a string.

The Oracle/PLSQL TO_CLOB function converts a LOB value from the national character set to the database
character set.

The Oracle/PLSQL TO_DATE function converts a string to a date.

The Oracle/PLSQL TO_DSINTERVAL function converts a string to an INTERVAL DAY TO SECOND type.

The Oracle/PLSQL TO_LOB

function converts
RAW
values
values.
TO_MULTI_BYTE
function LONG
returnsor
a LONG
character
value
withto
allLOB
of the
single-byte characters
converted to multibyte characters.
To use this function, your database character set contains both single-byte and multibyte characters.
The Oracle/PLSQL TO_NCLOB function converts a LOB value to a NCLOB value.

The Oracle/PLSQL TO_NUMBER function converts a string to a number.

Page 133

Sheet1

The Oracle/PLSQL TO_SINGLE_BYTE function returns a character value with all of the multibyte characters
converted to single-byte characters.
To use this function, your database character set contains both single-byte and multibyte characters.
The Oracle/PLSQL TO_TIMESTAMP function converts a string to a timestamp.
The Oracle/PLSQL TO_TIMESTAMP_TZ function converts a string to a timestamp with time zone.

The Oracle/PLSQL TO_YMINTERVAL function converts a string to an INTERVAL YEAR TO MONTH type.

The Oracle/PLSQL TRANSLATE function replaces a sequence of

characters in a string with another set of characters. However, it
replaces a single character at a time.
For example, it will replace the 1st character in the
string_to_replace with the 1st character in the replacement_string.
Then it will replace the 2nd character in the string_to_replace with
the 2nd character in the replacement_string, and so on.

The Oracle/PLSQL TRIM function removes all specified characters

either from the beginning or the ending of a string.

If you do not choose a value for the first parameter (LEADING,

TRAILING, BOTH), the TRIM function will remove trim_character from
both the front and end of string1.
See also the LTRIM and RTRIM functions.

Page 134

Sheet1

The Oracle/PLSQL TRUNC function returns a date truncated to a

specific unit of measure.

The Oracle/PLSQL TRUNC function returns a number truncated to a

certain number of decimal places.

Page 135

Sheet1

The Oracle/PLSQL TZ_OFFSET function returns the time zone offset of a value.

The Oracle/PLSQL UID function returns the id number for a user's session (the user who is currently logged in).

The Oracle/PLSQL UPPER function converts all letters in the

specified string to uppercase. If there are characters in the string
that are not letters, they are unaffected by this function.
The Oracle/PLSQL USER function returns the user_id from the current
Oracle session.
The Oracle/PLSQL USERENV function can be used to retrieve
information about the current Oracle session. Although this function
still exists in Oracle for backwards compatibility, it is
recommended that you use the sys_context function instead.

The Oracle/PLSQL VARIANCE function returns the variance of a set of numbers.

The Oracle/PLSQL VAR_POP function returns the population variance of a set of numbers.
The Oracle/PLSQL VAR_SAMP function returns the sample variance of a set of numbers.

Page 136

Sheet1

The Oracle/PLSQL VSIZE function returns the number of bytes in the internal representation of an expression.

Procedural language support : PL/SQL is a development tools not only

for data manipulation futures but also provide the conditional
checking, looping or branching operations same as like other
programming language.
Reduces network traffic : This one is great advantages of PL/SQL.
Because PL/SQL nature is entire block of SQL statements execute into
oracle engine all at once so it's main benefit is reducing the
network traffic.
Error handling : PL/SQL is dealing with error handling, It's permits
the smart way handling the errors and giving user friendly error
messages, when the errors are encountered.
Declare variable : PL/SQL gives you control to declare variables and
access them within the block. The declared variables can be used at
the time of query processing.
Intermediate Calculation : Calculations in PL/SQL done quickly and
IF THEN END IF; IF THEN ELSE END IF; IF THEN ELSIF ENDIF; CASE ,
DECODE
For Loop; Simple Loop; WHILE Loop
There are three types of parameters for procedures and functions.
They are
IN: A variable passed in this mode is of read only nature. This is
to say, the value cannot be changed and its scope is restricted
within the procedure.
The procedure receives a value from this argument when the procedure
is called.
OUT: In this mode, a variable is write only and can be passed back
to the calling program.
It cannot be read inside the procedure and needs to be assigned a
value.
Page 137

Sheet1
PL/SQL variables must be declared in the declaration section or in a
package as a global variable.
When you declare a variable, PL/SQL allocates memory for the
variable's value and the storage location is identified by the
A constant holds a value that once declared, does not change in the program.
A constant declaration specifies its name, data type, and value, and allocates storage for it.
The declaration can also impose the NOT NULL constraint.
An operator is a symbol that tells the compiler to perform specific mathematical or logical manipulation.
PL/SQL language is rich in built-in operators and provides the following types of operators:
Arithmetic operators
Relational operators
Comparison operators
Logical operators
String operators
Below are conditional statements available in PL/SQL
IF - THEN statement
IF-THEN-ELSE statement
IF-THEN-ELSIF statement
Case statement
Searched CASE statement
nested IF-THEN-ELSE
The string in PL/SQL is actually a sequence of characters with an optional size specification.
The characters could be numeric, letters, blank, special characters or a combination of all. PL/SQL offers three
kinds of strings:
1.Fixed-length strings:
In such strings, programmers specify the length while declaring the string.
The string is right-padded with spaces to the length so specified.
2.Variable-length strings:
In such strings, a maximum length up to 32,767, for the string is specified and no padding takes place.
3.Character large objects (CLOBs):
An array is a part of collection type data and it stands for variable-size arrays

Page 138

Sheet1

PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.
A package will have two mandatory parts:
1. Package specification:
The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions,
cursors, and subprograms that can be referenced from outside the package.
In other words, it contains all information about the content of the package, but excludes the code for the
subprograms.
2. Package body or definition:
The package body has the codes for various methods declared in the package specification and other private
declarations, which are hidden from code outside the package.

DBMS_OUTPUT.PUT_LINE
Below are the sql commands:
DML: INSERT, UPDATE, DELETE, MERGE
DDL: CREATE, ALTER, DROP, TRUNCATE
TCL: COMMIT, ROLLBACK, SAVEPOINT
DCL: GRANT, REVOKE
DRL: SELECT

OR, AND, NOT

AVG, COUNT, MIN, MAX, STDDEV, SUM, VARIANCE, etc.

Used to rename database objects

In Oracle, a subquery is a query within a query. You can create
subqueries within your SQL statements. These subqueries can reside
in the WHERE clause, the FROM clause, or the SELECT clause.
WHERE clause
Most often, the subquery will be found in the WHERE clause. These
subqueries are also called nested subqueries.
FROM clause
A subquery can also be found in the FROM clause. These are called
inline views.
SELECT clause

Page 139

Sheet1
An index is a performance-tuning method of allowing faster retrieval
of records. An index creates an entry for each value that appears in
the indexed columns. By default, Oracle creates B-tree indexes.

The Oracle/PLSQL AVG function returns the average value of an

expression.

Page 140

Sheet1
Syntax
SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
INSERT INTO TABLE_NAME
[ (col1, col2, col3,...colN)]
VALUES (value1, value2, value3,...valueN);
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3,...valueN);
INSERT INTO table_name
[(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM table_name [WHERE condition];
Syntax :SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;

Page 141

Sheet1
Explicit Join Syntax :
SELECT TableA.ColumnA1,
TableB.ColumnB2
FROM TableA
INNER JOIN TableB
ON TableA.ColumnA1 = TableB.ColumnB1
Implicit Join Syntax:
SELECT TableA.ColumnA1,
TableB.ColumnB2
FROM TableA,
TableB
WHERE TableA.ColumnA1 = TableB.ColumnB1
Syntax :SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

Page 142

Sheet1

Page 143

Sheet1
Subquery with Select :
SELECT column_name [, column_name ]
FROM
table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
Subquery with Insert :
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
Subquery with Update :
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Subquery with Delete :
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Subquery for column :
SELECT column_namw, (select statement) alias FROM table_Name;
Subquery in FROM clause (Inline View):
Syntax : SELECT column_name1, column_name2, alias2.column_name1 FROM Table_name1,
(SELECT Column_name1, column_name2 FROM table_name3) alias2
NA

select max(age)
from (
-- this part of the query is an inline view:
select age from table
)
NA
NA
DELETE FROM table_name [where conditions];
TRUNCATE TABLE table_name;

Page 144

Sheet1
TRUNCATE TABLE table_name;
DROP TABLE table_name;
NA

CONVERT(data_type(length),expression,style)
CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name
)

NA
NA
NA
NA

Syntax : SELECT RELPLACE(string, 'CHARACTER', 'REPLACING CHARACTER') FROM table;

SELECT TOP number|percent column_name(s)
FROM table_name;
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;
ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE table_name MODIFY column_name datatype

Page 145

Sheet1
NA

select count(column_name|*) from table_name

NA

Page 146

Sheet1
1. Using subquery
DELETE FROM table_name A
WHERE a.rowid >ANY (SELECT B.rowid FROM
B.col2 );

B WHERE A.col1 = B.col1 AND A.col2 =

2. Using Rank function

delete from $table_name where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by $primary_key order by rowid) rank_n,
rowid as "rowid"
from $table_name
where $primary_key in
(select $primary_key from $table_name
group by $all_columns
having count(*) > 1
)
)
)
where rank_n > 1
)
3. Using self join
delete from
table_name a
where
a.rowid >
any (select b.rowid
from
table_name b
where
a.col1 = b.col1
and
a.col2 = b.col2
NA
GETDATE()
DATEDIFF(datepart,startdate,enddate)
DATEADD(datepart,number,date)

DATEPART(datepart,date)
CONVERT(data_type(length),expression,style)

Page 147

Sheet1
INSERT INTO table2
SELECT * FROM table1;
INSERT
INTO
INTO
INTO
SELECT

ALL
mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
* FROM dual;

NA

NA

CREATE TABLE PersonsNotNull

(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
NA

Page 148

Sheet1
Syntax:CONSRAINT [constraint name] DEFAULT Value

Syntax : CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

STUFF( source_string, start, length, add_string )

COUNT_BIG( { [ ALL | DISTINCT ] expression ] | * } )

BINARY_CHECKSUM ( * | expression [ ,...n ] )

Page 149

Sheet1
CHECKSUM ( * | expression [ ,...n ] )

NA

IDENTITY [ (seed , increment) ]

Arguments:
seed:
Is the value that is used for the very first row loaded into the table.
increment:
Is the incremental value that is added to the identity value of the previous
row that was loaded.
You must specify both the seed and increment or neither. If neither is specified,
the default[is
(1,1).
sp_columns
@table_name
= ] object [ , [ @table_owner = ] owner ]
[ , [ @table_qualifier = ] qualifier ]
[ , [ @column_name = ] column ]
[ , [ @ODBCVer = ] ODBCVer ]
NA

CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS

BEGIN
....
END procedure_name;

Page 150

Sheet1
sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

CONNECT user@database
DISCONNECT

NA

sp_executesql [ @stmt = ] statement

[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]

Page 151

Sheet1
Syntax : CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END

SELECT *
FROM
(SELECT product_code, quantity
FROM
pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b,
'C' AS c));
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }
( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]
]
Arguments
AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM
Specifies the aggregation to be performed. These row aggregate functions are
used with the COMPUTE clause.
HAS_PERMS_BY_NAME ( securable , securable_class , permission
[ , sub-securable ] [ , sub-securable_class ] )

Page 152

Sheet1
NA

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME =

C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'P'

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME

= C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'R'

NA

NA

Page 153

Sheet1
NA

NA
NA
Syntax :

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]

IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;

Page 154

Sheet1
Syntax:CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;

Index By table :
Syntax : TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY
subscript_type;
table_name type_name;
Nested Table:
Syntax : TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;
Varray:
TYPE Type_Name IS VARRAY(SIZE) OF DATATYPE(10)

Page 155

Sheet1
Syntax : TYPE record_type_name IS RECORD
(first_col_name column_datatype,
second_col_name column_datatype, ...);

Syntax : Type Type_name IS RECORD (column_name table_name.column_name%type) ;

Record_variable Type type_Name

TYPE
type_name IS RECORD
( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION],
field_name2
datatype2
[NOT NULL] [:= DEFAULT EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION);
record-name type_name;

Page 156

Sheet1
DECLARE
CURSOR customer_cur is
SELECT id, name, address
FROM customers;
customer_rec customer_cur%rowtype;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur into customer_rec;
EXIT WHEN customer_cur%notfound;
DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
END LOOP;
END;

Syntax :
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

Syntax : CURSOR cursor_name IS select_statement;

Page 157

Sheet1
NA

E.g : DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
TYPE ref_type_name IS REF CURSOR
[RETURN {cursor_name%ROWTYPE
|ref_cursor_name%ROWTYPE
|record_name%TYPE
|record_type_name
|table_name%ROWTYPE} ];
The RETURN clause is optional, when included it causes the cursor variable to be
strongly typed.

Page 158

Sheet1
LOOP
{...statements...}
END LOOP;

DECLARE
cursor c(no number) is select * from emp_information
where emp_no = no;
tmp emp_information%rowtype;
BEGIN
OPEN c(4);
FOR tmp IN c(4) LOOP
dbms_output.put_line('EMP_No:
'||tmp.emp_no);
dbms_output.put_line('EMP_Name: '||tmp.emp_name);
dbms_output.put_line('EMP_Dept: '||tmp.emp_dept);
dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);
END Loop;
CLOSE c;
END;
/

Page 159

Sheet1
e.g.
DECLARE
CURSOR trip_cursor IS
SELECT
bt_id_pk,
bt_duration
FROM
business_trips
WHERE
bt_id_pk = 23
FOR UPDATE OF bt_id_pk, bt_duration;
BEGIN
FOR trip_record IN trip_cursor LOOP
UPDATE business_trips
SET bt_duration = 5
WHERE CURRENT OF trip_cursor;
END LOOP;
COMMIT;
END;

NA

CREATE TABLE Persons

(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Page 160

Sheet1
The Syntax for the GRANT command is:
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
The Syntax for the REVOKE command is:
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name} ;
CREATE SYNONYM emp FOR scott.employees;
CREATE PUBLIC SYNONYM dual FOR sys.dual;
CREATE PUBLIC SYNONYM emp FOR scott.employees;
DROP SYNONYM emp;
The Syntax to create a role is:
CREATE ROLE role_name
[IDENTIFIED BY password];
DROP ROLE role_name;
alter user user_name identified by new_password replace old_password
NA

Select * from all_users;

Select * from dba_users;
Select * from user_users;

Page 161

Sheet1
NA

NA

Page 162

Sheet1
NA

Page 163

Sheet1
Simple loop without condition:
LOOP
{...statements...}
END LOOP;
Simple loop with condition:
LOOP
{...statements...}
EXIT [ WHEN boolean_condition ];
END LOOP;
While loop:
WHILE condition
LOOP
{...statements...}
END LOOP;
For loop:
FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{...statements...}
END LOOP;
IF statement:
IF condition THEN
{...statements to execute when condition is TRUE...}
END IF;
IF else statement:
IF condition THEN
{...statements to execute when condition is TRUE...}
Syntax : CREATE SEQUENCE <sequence_name>
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

Page 164

Sheet1
NA

NA

Page 165

Sheet1
NA

DELETE FROM table_name

WHERE some_column=some_value;
SELECT field1, field2, ... field_n
FROM tables
INTERSECT
SELECT field1, field2, ... field_n
FROM tables;

SELECT expression1, expression2, ... expression_n

FROM tables
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables;

Page 166

Sheet1
NA
NA
NA
NA
SELECT SUM(expression)
FROM tables
WHERE conditions;
SELECT MIN(expression)
FROM tables
WHERE conditions;
SELECT MAX(expression)
FROM tables
WHERE conditions;
WHERE condition1
OR condition2
...
OR condition_n;
WHERE condition1
AND condition2
...
OR condition_n;
expression LIKE pattern [ ESCAPE 'escape_character' ]

REGEXP_LIKE ( expression, pattern [, match_parameter ] )

expression IN (value1, value2, .... value_n);

NOT condition

WHERE EXISTS ( subquery );

Create Table Table_name (Column_Name datatype(SIZE) CONSTRAINT, Column_Name

datatype(SIZE) CONSTRAINT );
Drop Table_name;

Page 167

Sheet1
DECLARE GLOBAL TEMPORARY TABLE table-Name
{ column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ]
NOT LOGGED [ON ROLLBACK DELETE ROWS]

DECLARE LOCAL TEMPORARY TABLE table_name

( column1 datatype null/not null,
column2 datatype null/not null,
...
);
Syntax : CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;
DROP VIEW view_name;
NA

DELETE FROM table

WHERE conditions;
SELECT DISTINCT expressions
FROM tables
WHERE conditions;

Page 168

Sheet1
expression IS NOT NULL

SELECT expression1, expression2, ... expression_n

FROM tables
WHERE conditions
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions;
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions;
Syntax for Creating a Trigger
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;

Page 169

Sheet1
CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;

Page 170

Sheet1
CREATE [ OR REPLACE ] TRIGGER trigger_name
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;

Page 171

Sheet1
ALTER TABLESPACE tablespace_name
{ DEFAULT
[ { COMPRESS | NOCOMPRESS } ] storage_clause
| MINIMUM EXTENT integer [ K | M | G | T | P | E ]
| RESIZE integer [ K | M | G | T | P | E ]
| COALESCE
| RENAME TO new_tablespace_name
| { BEGIN | END } BACKUP
| { ADD { DATAFILE | TEMPFILE }
[ file_specification
[, file_specification ]
]
| DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
| RENAME DATAFILE 'filename' [, 'filename' ] TO 'filename' [, 'filename' ]
| { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
}
| { logging_clause | [ NO ] FORCE LOGGING }
| TABLESPACE GROUP { tablespace_group_name | '' }
| { ONLINE
| OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
}
| READ { ONLY | WRITE }
| { PERMANENT | TEMPORARY }
| AUTOEXTEND
{ OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
}
| FLASHBACK { ON | OFF }
| RETENTION { GUARANTEE | NOGUARANTEE }
} ;

Page 172

Sheet1
CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;

Page 173

Sheet1
CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;

CREATE [ OR REPLACE ] TRIGGER trigger_name

BEFORE UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;

NA
Single line comment:
--code goes here
Multi line comment:
/* code goes here */

Page 174

Sheet1
commit;
CREATE SCHEMA AUTHORIZATION schema_name
[create_table_statement]
[create_view_statement]
[grant_statement];

CREATE
[ SMALLFILE | BIGFILE ]
TABLESPACE tablespace_name
{ DATAFILE { [ 'filename' | 'ASM_filename' ]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
[ AUTOEXTEND
{ OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
}
]
| [ 'filename | ASM_filename'
| ('filename | ASM_filename'
[, 'filename | ASM_filename' ] )
]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
}
{ MINIMUM EXTENT integer [ K | M | G | T | P | E ]
| BLOCKSIZE integer [ K ]
| { LOGGING | NOLOGGING }
| FORCE LOGGING
| DEFAULT [ { COMPRESS | NOCOMPRESS } ]
storage_clause
| { ONLINE | OFFLINE }
| EXTENT MANAGEMENT
{ LOCAL
[ AUTOALLOCATE
| UNIFORM
[ SIZE integer [ K | M | G | T | P | E ] ]
]
| DICTIONARY
}
| SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
Connect as SYSTEM.
CREATE USER username IDENTIFIED BY password;
GRANT CONNECT TO username;
GRANT EXECUTE on schema.procedure TO username;

Page 175

Sheet1

UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;

Page 176

Sheet1
CURSOR cursor_name
IS
select_statement
FOR UPDATE [OF column_list] [NOWAIT];
Cursor Cursor_Name IS SELECT Statement;
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter [,parameter]) ]
IS
[declaration_section]
exception_name EXCEPTION;
BEGIN
executable_section
RAISE exception_name;
EXCEPTION
WHEN exception_name THEN
[statements]
WHEN OTHERS THEN
[statements]
END [procedure_name];

ALTER TABLE table_name DISABLE ALL TRIGGERS;

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
ALTER TRIGGER trigger_name DISABLE;
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DROP TABLESPACE tablespace_name
[ INCLUDING CONTENTS [ {AND DATAFILES | KEEP DATAFILES ]
[ CASCADE CONSTRAINTS ] ] ;
DROP TRIGGER trigger_name;
DROP USER user_name [ CASCADE ];
CASCADE is optional. If user_name owns any objects (ie: tables or views in its
schema), you must specify CASCADE to drop all of these objects.
ALTER TABLE table_name ENABLE ALL TRIGGERS;
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Page 177

Sheet1
ALTER TRIGGER trigger_name ENABLE;
NA
EXIT;

FETCH cursor_name.attr_name into var_name

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME IN
SHOW USERS;
SHOW USER;
FOR variable_name IN lower_bound .. Upper_bound LOOP
--sql statments
END loop;
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE SET NULL

Page 178

Sheet1
GOTO label_name;
label_name must be unique within the scope of the code.
There must be at least one statement to execute after the Label Declaration.

NA
LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];
OPEN cursor_name;

Page 179

Sheet1
[ label_name: ] REPEAT
{...statements...}
UNTIL condition
END REPEAT [ label_name ];

ROLLBACK;
Syntax : CREATE SEQUENCE <sequence_name>
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
ALTER DATABASE DEFAULT TABLESPACE tbs_perm_01;
You can run the following query to see that the default permanent tablespace has,
in fact, been changed:
SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
SET TRANSACTION [ READ ONLY | READ WRITE ]
[ ISOLATION LEVEL [ SERIALIZE | READ COMMITED ]
[ USE ROLLBACK SEGMENT 'segment_name' ]
[ NAME 'transaction_name' ];
SQLCODE

Page 180

Sheet1
SQLERRM

NA

NA
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
WHEN exception_name_n THEN
[statements]
WHEN OTHERS THEN
[statements]

Page 181

Sheet1
NA
Syntax : ABS( number )

Syntax : ACOS( number )

ADD_MONTHS( date1, n )

ASCII( single_character )

ASCIISTR( string )

ASIN( number )

Page 182

Sheet1
ATAN( NUMBER )

ATAN2( n, m )

BFILENAME( 'directory', 'filename' )

BIN_TO_NUM( expr1, expr2, ... expr_n)

bitand( expr1, expr2 )

Page 183

Sheet1
CARDINALITY( nested table column )

CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

CEIL( number )

CHARTOROWID( value1 )

CHR( number_code )

NA
COMPOSE( string )

CONCAT( string1, string2 )

I like ' || t.type_desc_column || ' cake with ' || t.icing_desc_column || ' and a
'
|| t.fruit_desc_column
CORR(
n ,m ) [ over (analytic_clause) ]

Page 184

Sheet1
COS( number )

NA

COVAR_POP( expression1, expression2 )

COVAR_SAMP( expression1, expression2 )

Page 185

Sheet1
CUME_DIST( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1,
... expression_n )

CURRENT_DATE( )

CURRENT_TIMESTAMP

DBTIMEZONE

DECODE( expression , search , result [, search , result]... [, default] )

DECOMPOSE( string )

Page 186

Sheet1

DUMP( expression, [return_format], [start_position], [length] )

EMPTY_BLOB ()

EMPTY_CLOB ()

exp( number )

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

Page 187

Sheet1
FLOOR( number )

FROM_TZ( timestamp_value, time_zone_value )

GREATEST( expr1, expr2, ... expr_n )

SELECT column1, column2, ... column_n, GROUP_ID()

FROM tables
WHERE conditions
GROUP BY column1, column2, ... column_n;
SELECT SUM(salary), department, bonus, GROUP_ID()
FROM employees
WHERE bonus > 100
GROUP BY department,
ROLLUP (department, bonus);

HEXTORAW( char )

INITCAP( string1 )

Page 188

Sheet1
INSTR( string, substring [, start_position [, nth_appearance ] ] )
string is the string to search. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2,
CLOB, or NCLOB.
substring is the substring to search for in string. substring can be CHAR,
VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position is the position in string where the search will start. This
argument is optional. If omitted, it defaults to 1. The first position in the
string is 1. If the start_position is negative, the INSTR function counts back
start_position number of characters from the end of string and then searches
towards the beginning of string.
nth_appearance is the nth appearance of substring. This is optional. If omitted,
it defaults to 1.

INSTR2( string, substring [, start_position [, nth_appearance ] ] )

string is the string to search. string can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
string can not be CLOB or NCLOB.
substring is the substring to search for in string. substring can be CHAR,
VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position is the position in string where the search will start. This
argument is optional. If omitted, it defaults to 1. The first position in the
string is 1. If the start_position is negative, the INSTR2 function counts back
start_position number of characters from the end of string and then searches
towards the beginning of string.
nth_appearance is the nth appearance of substring. This is optional. If omitted,
it defaults to 1.
If substring is not found in string, then the INSTR2 function will return 0.
INSTR4( string, substring [, start_position [, nth_appearance ] ] )

Page 189

Sheet1
INSTRB( string, substring [, start_position [,

nth_appearance ] ] )

INSTRC( string, substring [, start_position [,

nth_appearance ] ] )

LAG ( expression [, offset [, default] ] )

over ( [ query_partition_clause ] order_by_clause )
expression is an expression that can contain other built-in functions, but can not
contain any analytic functions.
offset is optional. It is the physical offset from the current row in the table.
If this parameter is omitted, the default is 1.
default isdate
optional.
It is the value that is returned if the offset goes out of
LAST_DAY(
)

Page 190

Sheet1
LEAD ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )
expression is an expression that can contain other built-in functions, but can not
contain any analytic functions.
offset is optional. It is the physical offset from the current row in the table.
If this parameter is omitted, the default is 1.
LEAST( expr1, expr2, ... expr_n )

LENGTH( string1 )

Page 191

Sheet1
LENGTH2( string1 )

LENGTH4( string1 )

LENGTHB( string1 )

Page 192

Sheet1
LENGTHC( string1 )

LISTAGG (measure_column [, 'delimiter'])

LN( number )

LNNVL( condition )

LOCALTIMESTAMP

Page 193

Sheet1
LOG( m, n )

LOWER( string1 )

LPAD( string1, padded_length, [ pad_string ] )

LTRIM( string1, [ trim_string ] )

Page 194

Sheet1
MEDIAN( expression ) [ OVER ( query partition clause ) ]

MOD( m, n )

MONTHS_BETWEEN( date1, date2 )

NANVL( value, replace_with )

NCHR( number_code )

NEW_TIME( date, zone1, zone2 )

NEXT_DAY( date, weekday )

Page 195

Sheet1
NULLIF( expr1, expr2 )

NUMTODSINTERVAL( number, expression )

NUMTOYMINTERVAL( number, expression )

NVL( string1, replace_with )

NVL2( string1, value_if_NOT_null, value_if_null )

Page 196

Sheet1
POWER( m, n )
If m is negative, then n must be an integer.

The syntax for the RANK function when used as an Analytic function is:
rank() OVER ( [ query_partition_clause] ORDER BY clause )

RAWTOHEX( raw )

Page 197

Sheet1
REMAINDER( m, n )

ROUND( date, [ format ] )

Page 198

Sheet1
ROUND( number, [ decimal_places ] )

RPAD('tech', 7)

Page 199

Sheet1
RTRIM( string1, [ trim_string ] )

SESSIONTIMEZONE
SIGN( number )
If number < 0, then sign returns -1.
If number = 0, then sign returns 0.

Page 200

Sheet1
SIN( n )

SINH( n )

SOUNDEX( string1 )

SQRT( n )

stddev( [ DISTINCT | ALL ] expression )

Page 201

Sheet1
SUBSTR( string, start_position, [ length ] )

SELECT sysdate FROM Dual;

SYSTIMESTAMP
SYS_CONTEXT( namespace, parameter, [ length ] )

TAN( n )

TANH( n )

Page 202

Sheet1
TO_CHAR( value, [ format_mask ], [ nls_language ] )

TO_CLOB( expression )

TO_DATE( string1, [ format_mask ], [ nls_language ] )

TO_DSINTERVAL( character [ nls_parameter ] )

TO_LOB( long_column )

TO_MULTI_BYTE( char )
TO_NCLOB( expression )
TO_NUMBER( string1, [ format_mask ], [ nls_language ] )

Page 203

Sheet1
TO_SINGLE_BYTE( char )
TO_TIMESTAMP( string1, [ format_mask ] [ 'nlsparam' ] )
TO_TIMESTAMP_TZ( string1 , [ format_mask ] [ 'nlsparam' ] )
TO_YMINTERVAL( character )

TRANSLATE( string1, string_to_replace, replacement_string )

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )

Page 204

Sheet1
TRUNC ( date, [ format ] )

TRUNC( number, [ decimal_places ] )

Page 205

Sheet1
TZ_OFFSET( timezone )

UID

UPPER( string1 )

USER

USERENV( parameter )

VARIANCE( expression )
VAR_POP( expression )
VAR_SAMP( expression )

Page 206

Sheet1
VSIZE( expression )

NA

NA
NA
NA

Page 207

Sheet1

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

PI CONSTANT NUMBER := 3.141592654;

NA

TYPE varray_type_name IS VARRAY(n) of <element_type>

Page 208

Sheet1
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
CREATE OR REPLACE PACKAGE BODY cust_sal AS
PROCEDURE find_sal(c_id customers.id%TYPE) IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;
/

NA

alter table
table_name
rename to
new_table_name;

Page 209

Sheet1
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ... column_n)
[ COMPUTE STATISTICS ];

SELECT AVG( expression )

FROM tables
WHERE conditions;

Page 210

Sheet1
Example(s)
SELECT ENO,ENAME,SAL FROM EMP;
SELECT ENO, ENAME,SAL, DEPTNO FROM EMP WHERE SAL>1800 ;

INSERT INTO EMP(EMPNO,ENAME,DEPTNO) VALUES (1122,'Raja',30);

INSERT INTO EMP VALUES(1122,'Raj','PAINTER',7788,'12-feb-1990',2250,'',30);
INSERT INTO EMP1(ENO,EMP_NAME,SALARY,DEPT) SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;

Equi Joins:
SELECT order_id, product_name, unit_price, supplier_name, total_units
FROM product, order_items
WHERE order_items.product_id = product.product_id;
Outer Join:
SELECT p.product_id, p.product_name, o.order_id, o.total_units
FROM order_items o, product p
WHERE o.product_id (+) = p.product_id;
Self Join
SELECT E.ename, M.Ename FROM Emp E, Emp M
WHERE M.empno = E.mgr;
Non Equi Join:
SELECT E.Ename, E.Sal S.grade
FROM emp E, salgrade
WHERE E.sal between s.losal AND S.hisal;

Page 211

Sheet1
SELECT e.empno,e.ename,e.sal,d.dname,d.location from emp e inner join dept d on
e.deptno=d.deptno;
SELECT e.empno,e.ename,e.sal,d.dname,d.location from emp e,dept d where
e.deptno=d.deptno;

SELECT NAME, SUM(SALARY) FROM CUSTOMERS

GROUP BY NAME;

Page 212

Sheet1

Page 213

Sheet1
SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;
INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;
UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );

NA

Select * from (select rownum rn,sal from(select * from emp order by sal desc))
where rn=&m;
select max(sal) FROM emp
WHERe sal NOT IN (SELECT MAx(sal) FROM emp);
Delete from emp;
truncate table emp;

Page 214

Sheet1
TRUNCATE TABLE emp;
DROP TABLE emp;
NA
select CONVERT(VARCHAR(10),BusinessEntityID) FROM HumanResources.Employee
select CAST(BusinessEntityID AS VARCHAR(10)) FROM HumanResources.Employee
NA
NA
NA
NA

select replace('satish','i','ee') from

dual;

SELECT TOP 2 * FROM Customers;

SELECT TOP 50 PERCENT * FROM Customers;
SELECT CustomerName, ContactName
INTO CustomersBackup2013
FROM Customers;
ALTER TABLE emp ADD basicsal number(8,2);
ALTER TABLE emp DROP COLUMN comm;
ALTER TABLE emp MODIFY sal number(10,2)

Page 215

Sheet1
NA

select count(ename) from emp;

select count(*) from emp;
NA

Page 216

Sheet1
Example :
DELETE FROM xx_dupl_test
WHERE rowid not in
(SELECT MIN(rowid) FROM xx_dupl_test group BY SNO, name);

NA
SELECT GETDATE() AS CurrentDateTime
SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate
SELECT OrderId,DATEADD(day,45,OrderDate) AS OrderPayDate
FROM Orders
SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1
CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

Page 217

Sheet1
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';
INSERT
INTO
INTO
INTO
SELECT

ALL
suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
* FROM dual;

INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson
Construction', 'New York')
SELECT * FROM dual;

CREATE TABLE supplier

(
supplier_id
numeric(10)
not null,
supplier_name
varchar2(50)
not null,
contact_name
varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES supplier(supplier_id)
)
NA

CREATE TABLE PersonsNotNull

(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
NA

Page 218

Sheet1
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

SELECT STUFF('TechOnTheNet.com', 1, 12, 'CheckYourMath');

Result: 'CheckYourMath.com'
SELECT COUNT_BIG(*) FROM
Products

SELECT
FROM
WHERE

ProductID
TableBC
EXISTS
(
SELECT
ProductID
FROM
Products
WHERE
Products.ProductID = TableBC.ProductID
AND
BINARY_CHECKSUM(*) <> TableBC.bchecksum)

Page 219

Sheet1
SELECT

CHECKSUM_AGG(CONVERT(INT,UnitsInStock)) FROM

CREATE TABLE new_employees

(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
);

USE AdventureWorks2012;
GO
EXEC sp_columns @table_name = N'Department',
@table_owner = N'HumanResources';

CREATE OR REPLACE
PROCEDURE ADD_EVALUATION
( evaluation_id IN NUMBER
, employee_id IN NUMBER
, evaluation_date IN DATE
, job_id IN VARCHAR2
, manager_id IN NUMBER
, department_id IN NUMBER
) AS
BEGIN
NULL;
END ADD_EVALUATION;

Page 220

Products

Sheet1
USE master;
GO
EXEC sp_who;
GO

NA

CREATE OR REPLACE PROCEDURE query_invoice(

month VARCHAR2,
year VARCHAR2) IS
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(200);
inv_num NUMBER;
inv_cust VARCHAR2(20);
inv_amt NUMBER;
BEGIN
query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year
|| ' WHERE invnum = :id';
OPEN c FOR query_str USING inv_num;
LOOP
FETCH c INTO inv_num, inv_cust, inv_amt;
EXIT WHEN c%NOTFOUND;
-- process row here
END LOOP;
CLOSE c;
END;
/

EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks2012.HumanResources.Employee
WHERE BusinessEntityID = @level',
N'@level tinyint',
@level = 109;

Page 221

Sheet1
SELECT cust_last_name,
CASE credit_limit WHEN 100 THEN 'Low'
WHEN 5000 THEN 'High'
ELSE 'Medium' END
FROM customers

SELECT *
FROM
(SELECT product_code, quantity
FROM
pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b,
'C' AS c));
MERGE INTO employees e
USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
SELECT CustomerID, OrderDate, SubTotal, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 35
ORDER BY OrderDate
COMPUTE SUM(SubTotal), SUM(TotalDue);

SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE');

SELECT HAS_PERMS_BY_NAME('Ps', 'LOGIN', 'IMPERSONATE');
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY');

Page 222

Sheet1
NA

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME =

C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'P'

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME

= C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'R'

NA

NA

Page 223

Sheet1
NA

NA
NA
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/

Page 224

Sheet1
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
/

DECLARE
TYPE numbers_nt IS TABLE OF NUMBER;
l_numbers numbers_nt;
BEGIN
l_numbers := numbers_nt (1, 2, 3);
END;

Page 225

Sheet1
e.g DECLARE
TYPE employee_type IS RECORD
(employee_id number(5),
employee_first_name varchar2(25),
employee_last_name employee.last_name%type,
employee_dept employee.dept%type);
employee_salary employee.salary%type;
employee_rec employee_type;

DECLARE
customer_rec customers%rowtype;
BEGIN
SELECT * into customer_rec
FROM customers
WHERE id = 5;
dbms_output.put_line('Customer
dbms_output.put_line('Customer
dbms_output.put_line('Customer
dbms_output.put_line('Customer
END;
/

ID: ' || customer_rec.id);

Name: ' || customer_rec.name);
Address: ' || customer_rec.address);
Salary: ' || customer_rec.salary);

DECLARE
TYPE books IS RECORD
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id
number);
book1 books;
book2 books;

Page 226

Sheet1
DECLARE
CURSOR customer_cur is
SELECT id, name, address
FROM customers;
customer_rec customer_cur%rowtype;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur into customer_rec;
EXIT WHEN customer_cur%notfound;
DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
END LOOP;
END;

CREATE OR REPLACE TRIGGER orders_before_insert

BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;
-- Update create_date field to current system date
:new.create_date := sysdate;
-- Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;
END;

CURSOR C is select * from emp;

Page 227

Sheet1

E.g : DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno
IN emp.deptno%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM
emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/

Page 228

Sheet1
E.g CREATE OR REPLACE Function TotalIncome
( name_in IN varchar2 )
RETURN varchar2
IS
total_val number(6);
cursor c1 is
SELECT monthly_income
FROM employees
WHERE name = name_in;
BEGIN
total_val := 0;
FOR employee_rec in c1
LOOP
total_val := total_val + employee_rec.monthly_income;
END LOOP;
RETURN total_val;
END;

DECLARE
cursor c(no number) is select * from emp_information
where emp_no = no;
tmp emp_information%rowtype;
BEGIN
OPEN c(4);
FOR tmp IN c(4) LOOP
dbms_output.put_line('EMP_No:
'||tmp.emp_no);
dbms_output.put_line('EMP_Name: '||tmp.emp_name);
dbms_output.put_line('EMP_Dept: '||tmp.emp_dept);
dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);
END Loop;
CLOSE c;
END;
/

Page 229

Sheet1
e.g.
DECLARE
CURSOR trip_cursor IS
SELECT
bt_id_pk,
bt_duration
FROM
business_trips
WHERE
bt_id_pk = 23
FOR UPDATE OF bt_id_pk, bt_duration;
BEGIN
FOR trip_record IN trip_cursor LOOP
UPDATE business_trips
SET bt_duration = 5
WHERE CURRENT OF trip_cursor;
END LOOP;
COMMIT;
END;

NA

CREATE TABLE Persons

(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Page 230

Sheet1
GRANT CREATE TABLE TO testing;
REVOKE CREATE TABLE FROM testing;

CREATE SYNONYM emp FOR scott.employees;

CREATE PUBLIC SYNONYM dual FOR sys.dual;
CREATE PUBLIC SYNONYM emp FOR scott.employees;
DROP SYNONYM emp;
CREATE ROLE testing
[IDENTIFIED BY pwd];
DROP ROLE testing;

ALTER user scott identified by tiger;

NA

Select * from all_users;

Select * from dba_users;
Select * from user_users;

Page 231

Sheet1
NA

NA

Page 232

Sheet1
NA

Page 233

Sheet1
NA

CREATE SEQUENCE customers_seq

START WITH
1000
INCREMENT BY
1
NOCACHE
NOCYCLE;

Page 234

Sheet1
NA

NA

Page 235

Sheet1
NA

DELETE FROM Customers

WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 500
INTERSECT
SELECT company_id, company_name
FROM companies
WHERE company_name in ( 'Apple', 'Microsoft', 'Oracle' )
ORDER BY 2;
SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
MINUS
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

Page 236

Sheet1
NA
NA
NA
NA
SELECT SUM(salary) AS "Total Salary"
FROM employees
WHERE salary > 25000;
SELECT MIN(salary) AS "Lowest salary"
FROM employees;
SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;
SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'IBM'
OR city = 'New York'
OR offices > 5;
SELECT *
FROM suppliers
WHERE (city = 'New York' AND name = 'IBM')
OR (ranking >= 10);
SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE '%bob%';
SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, 'Anders(o|e|a)n');
SELECT *
FROM suppliers
WHERE supplier_name IN ('IBM', 'Hewlett Packard', 'Microsoft');
SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft' );
SELECT *
FROM suppliers
WHERE EXISTS (SELECT *
FROM orders
WHERE suppliers.supplier_id = orders.supplier_id);
create TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL,
FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes'
)
DROP TABLE EMP;

Page 237

Sheet1
declare global temporary table t2(c21 int) not logged;

DECLARE LOCAL TEMPORARY TABLE suppliers_temp

( supplier_id number(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
);
Syntax : CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;
DROP VIEW view_name;
NA

DELETE FROM suppliers

WHERE supplier_name = 'IBM';
SELECT DISTINCT city
FROM suppliers;

Page 238

Sheet1
SELECT *
FROM customers
WHERE customer_name IS NOT NULL;
SELECT supplier_id
FROM suppliers
UNION
SELECT supplier_id
FROM orders;

SELECT supplier_id
FROM suppliers
UNION ALL
SELECT supplier_id
FROM orders;

Page 239

Sheet1
CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
v_username );
END;

Page 240

Sheet1
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE into table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity_before,
quantity_after,
username )
VALUES
( :new.order_id,
:old.quantity,
:new.quantity,
v_username );
END;

Page 241

Sheet1
ALTER TABLESPACE tablespace_name
{ DEFAULT
[ { COMPRESS | NOCOMPRESS } ] storage_clause
| MINIMUM EXTENT integer [ K | M | G | T | P | E ]
| RESIZE integer [ K | M | G | T | P | E ]
| COALESCE
| RENAME TO new_tablespace_name
| { BEGIN | END } BACKUP
| { ADD { DATAFILE | TEMPFILE }
[ file_specification
[, file_specification ]
]
| DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
| RENAME DATAFILE 'filename' [, 'filename' ] TO 'filename' [, 'filename' ]
| { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
}
| { logging_clause | [ NO ] FORCE LOGGING }
| TABLESPACE GROUP { tablespace_group_name | '' }
| { ONLINE
| OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
}
| READ { ONLY | WRITE }
| { PERMANENT | TEMPORARY }
| AUTOEXTEND
{ OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
}
| FLASHBACK { ON | OFF }
| RETENTION { GUARANTEE | NOGUARANTEE }
} ;

Page 242

Sheet1
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
delete_date,
deleted_by )
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username );
END;

Page 243

Sheet1
CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;
-- Update create_date field to current system date
:new.create_date := sysdate;
-- Update created_by field to the username of the person performing the INSERT
:new.created_by := v_username;
END;

CREATE OR REPLACE TRIGGER orders_before_update

BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing UPDATE on the table
SELECT user INTO v_username
FROM dual;
-- Update updated_date field to current system date
:new.updated_date := sysdate;
-- Update updated_by field to the username of the person performing the UPDATE
:new.updated_by := v_username;
END;

Page 244

Sheet1
commit;
CREATE SCHEMA AUTHORIZATION smithj
CREATE TABLE products
( product_id number(10) not null,
product_name varchar2(50) not null,
category varchar2(50),
CONSTRAINT products_pk PRIMARY KEY (product_id)
);
CREATE TABLESPACE tbs_perm_01
DATAFILE 'tbs_perm_01.dat'
SIZE 20M
ONLINE;
CREATE TEMPORARY TABLESPACE tbs_temp_01
TEMPFILE 'tbs_temp_01.dbf'
SIZE 5M
AUTOEXTEND ON;
CREATE UNDO TABLESPACE tbs_undo_01
DATAFILE 'tbs_undo_01.f'
SIZE 5M
AUTOEXTEND ON
RETENTION GUARANTEE;

Connect as system;
create user scott identified by tiger;
grant connect,resource,dba to scott;

Page 245

Sheet1

CREATE OR REPLACE Function FindCourse

( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in
FOR UPDATE of instructor;
BEGIN
OPEN c1;
FETCH c1 INTO cnumber;
if c1%notfound then
cnumber := 9999;
else
UPDATE courses_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
end if;
CLOSE c1;
RETURN cnumber;
END;

Page 246

Sheet1
CURSOR c1
IS
SELECT course_number, instructor
FROM courses_tbl
FOR UPDATE OF instructor;
CURSOR C IS SELECT * FROM EMP;
LDescription varchar2(40);
LDescription := 'techonthenet.com Example';
CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the
order.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an
order.');
END;

NA
NA
NA
NA
DROP TABLESPACE tbs_perm_01
INCLUDING CONTENTS
CASCADE CONSTRAINTS;
NA
DROP USER SCOTT CASCADE;

ALTER TABLE EMP ENABLE ALL TRIGGERS;

NA

Page 247

Sheet1
NA
NA
EXIT;
CURSOR C IS SELECT * FROM EMP;
OPEN C;
FETCH C.empno into emp_number;
NA

NA
NA
for I in 1..10 loop
dbms_output.put_line('value of I is :||I);
end loop;
NA

NA

NA

Page 248

Sheet1
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT MAX(course_number)
FROM courses_tbl
WHERE course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
IF c1%notfound then
GOTO default_number;
ELSE
GOTO increment_number;
END IF;
<<default_number>>
cnumber := 0;
<<increment_number>>
cnumber := cnumber + 1;
close c1;
RETURN cnumber;
END;
LOCK TABLE suppliers IN SHARE MODE NOWAIT;
Cursor c is select * from emp;
OPEN C;
FETCH C.empno into emp_number;

Page 249

Sheet1
DELIMITER //
CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT
BEGIN
DECLARE income INT;
SET income = 0;
label1: REPEAT
SET income = income + starting_value;
UNTIL income >= 4000
END REPEAT label1;
RETURN income;
END; //
DELIMITER ;

ROLLBACK;
CREATE SEQUENCE customers_seq
START WITH
1000
INCREMENT BY
1
NOCACHE
NOCYCLE;

NA

SET TRANSACTION READ ONLY NAME 'RO_example';

SET TRANSACTION READ WRITE NAME 'RW_example';
SQLCODE

Page 250

Sheet1
SQLERRM

NA

NA
CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate
order_id.');
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the
order.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an
order.');
END;

Page 251

Sheet1
NA
E.g ABS(-23)
Result: 23
ABS(-23.6)
Result: 23.6
ABS(-23.65)
Result: 23.65

ACOS(0.2)
Result: 1.36943840600457
ACOS(0.35)
Result: 1.21322522314939
ACOS(-0.15)
Result: 1.72136459957158

ADD_MONTHS('01-Aug-03', 3)
Result: '01-Nov-03'
ASCII('t')
Result: 116
ASCII('T')
Result: 84
ASCII('T2')
Result: 84

ASCIISTR('A B C ')
Result: 'A B C \00C4 \00CA'
ASCIISTR('A B C ')
Result: 'A B C \00D5 \00D8'
ASCIISTR('A B C ')
Result: 'A B C \00C4 \00CA \00CD \00D5 \00D8'
ASIN(0.2)
Result: 0.201357920790331
ASIN(0.35)
Result: 0.35757110364551
ASIN(-0.15)
Result: -0.150568272776686

Page 252

Sheet1
ATAN(0.2)
Result: 0.197395559849881
ATAN(0.35)
Result: 0.336674819386727
ATAN(-0.15)
Result: -0.148889947609497
ATAN2(0.2, 0.3)
Result: 0.588002603547568
ATAN2(0.35, -0.15)
Result: 1.97568811307998
ATAN2(-0.3, -0.4)
Result: -2.49809154479651
SELECT BFILENAME('exampleDir', 'totn_logo.png')
FROM dual;
BIN_TO_NUM(1)
Result: 1
BIN_TO_NUM(1,0)
Result: 2
BIN_TO_NUM(1,1)
Result: 3
BIN_TO_NUM(1,1,1,0)
Result: 14
BIN_TO_NUM(1,1,1,1)
Result: 15
BITAND(5,3)
Result: 1
BITAND(15,7)
Result: 7
BITAND(5,2)
Result: 0
BITAND(5,0)
Result: 0
BITAND(6,2)
Result: 2

Page 253

Sheet1
select supplier_id, CARDINALITY(location)
from suppliers;

select CAST( '22-Aug-2003' AS varchar2(30) )

from dual;
CEIL(32.65)
Result: 33
CEIL(32.1)
Result: 33
CEIL(32)
Result: 32
CEIL(-32.65)
Result: -32

select * from suppliers

where rowid = CHARTOROWID('AAABoqAADAAAAwPAAA');
CHR(116)
Result: 't'
CHR(84)
Result: 'T'

COMPOSE('o' || unistr('\0308') )
Result:
COMPOSE('a' || unistr('\0302') )
Result:
COMPOSE('e' || unistr('\0301') )
Result:
CONCAT('Tech on', ' the Net')
Result: 'Tech on the Net'
CONCAT('a', 'b')
Result: 'ab'
I like ' || t.type_desc_column || ' cake with ' || t.icing_desc_column || ' and a
'
|| t.fruit_desc_column
select CORR(quantity, commission)
from sales;

Page 254

Sheet1
COS(0.2)
Result: 0.980066577841242
COS(0.35)
Result: 0.939372712847379
COS(-3.15)
Result: -0.999964658471342
COS(200)
Result: 0.487187675007006

select owner, COVAR_POP(avg_row_len, avg_space)

from all_tables
group by owner;
select owner, COVAR_SAMP(avg_row_len, avg_space)
from all_tables
group by owner;

Page 255

Sheet1
Example (as an Aggregate Function)
select CUME_DIST(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
from employees;
As Analytical Function
CUME_DIST() OVER ( [ query_partition_clause] ORDER BY clause )
E.g select employee_name, salary,
CUME_DIST() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';
mysql> SELECT CURRENT_DATE();
-> '2014-01-28'
mysql> SELECT CURRENT_DATE() + 0;
-> 20140128
mysql> SELECT CURRENT_DATE() + 1;
-> 20140129
select CURRENT_TIMESTAMP
from dual;

select DBTIMEZONE
from dual;
SELECT supplier_name,
DECODE(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
DECOMPOSE('Trs bien')
Result: 'Tre`s bien'
DECOMPOSE('')
Result: 'e'
DECOMPOSE('ol')
Result: 'ole'

Page 256

Sheet1

DUMP('Tech')
Result: 'Typ=96 Len=4: 84,101,99,104'
DUMP('Tech', 10)
Result: 'Typ=96 Len=4: 84,101,99,104'
DUMP('Tech', 16)
Result: 'Typ=96 Len=4: 54,65,63,68'
DUMP('Tech', 1016)
Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'
DUMP('Tech', 1017)
Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'
UPDATE products
SET product_photo = EMPTY_BLOB();

UPDATE customers
SET customer_photo = EMPTY_CLOB();
EXP(3)
Result: 20.0855369231877
EXP(3.1)
Result: 22.1979512814416
EXP(-3)
Result: 0.0497870683678639
EXTRACT(YEAR FROM DATE '2003-08-22')
Result: 2003
EXTRACT(MONTH FROM DATE '2003-08-22')
Result: 8
EXTRACT(DAY FROM DATE '2003-08-22')
Result: 22

Page 257

Sheet1
FLOOR(5.9)
Result: 5
FLOOR(34.29)
Result: 34
FLOOR(-5.9)
Result: -6

select FROM_TZ(TIMESTAMP '2005-09-11 01:50:42', '5:00')

from dual;
E.g
GREATEST(2, 5, 12, 3)
Result: 12
GREATEST('2', '5', '12', '3')
Result: '5'
GREATEST('apples', 'oranges', 'bananas')
Result: 'oranges'
GREATEST('apples', 'applis', 'applas')
Result: 'applis'

SELECT SUM(salary), department, bonus, GROUP_ID()

FROM employees
WHERE bonus > 100
GROUP BY department,
ROLLUP (department, bonus)
HAVING GROUP_ID() < 1;

HEXTORAW('45D')
Result: '045D' (as a raw value)
HEXTORAW('7E')
Result: '7E'

(as a raw value)

INITCAP('tech on the net');

Result: 'Tech On The Net'
INITCAP('GEORGE BURNS');
Result: 'George Burns'

Page 258

Sheet1
E. G
INSTR('Tech on the net', 'e')
Result: 2
(the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 1)
Result: 2
(the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 2)
Result: 11 (the second occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 3)
Result: 14 (the third occurrence of 'e')
INSTR('Tech on the net', 'e', -3, 2)
Result: 2

INSTR2('TechOnTheNet.com', 'e')
Result: 2
(the first occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', 1, 1)
Result: 2
(the first occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', 1, 2)
Result: 9
(the second occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', 1, 3)
Result: 11 (the third occurrence of 'e')
INSTR2('TechOnTheNet.com', 'e', -3, 2)
Result: 9
INSTR4('TechOnTheNet.com', 'e')
Result: 2
(the first occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', 1, 1)
Result: 2
(the first occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', 1, 2)
Result: 9
(the second occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', 1, 3)
Result: 11 (the third occurrence of 'e')
INSTR4('TechOnTheNet.com', 'e', -3, 2)
Result: 9

Page 259

Sheet1
INSTRB('TechOnTheNet.com', 'e')
Result: 2
(the first occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', 1, 1)
Result: 2; (the first occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', 1, 2)
Result: 9; (the second occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', 1, 3)
Result: 11; (the third occurrence of 'e' - single-byte character set)
INSTRB('TechOnTheNet.com', 'e', -3, 2)
Result: 9
(single-byte character set)
INSTRC('TechOnTheNet.com', 'e')
Result: 2
(the first occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', 1, 1)
Result: 2
(the first occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', 1, 2)
Result: 9
(the second occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', 1, 3)
Result: 11 (the third occurrence of 'e')
INSTRC('TechOnTheNet.com', 'e', -3, 2)
Result: 9

select product_id, order_date,

LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders;

LAST_DAY(TO_DATE('2003/03/15', 'yyyy/mm/dd'))
Result: Mar 31, 2003
LAST_DAY(TO_DATE('2003/02/03', 'yyyy/mm/dd'))
Result: Feb 28, 2003
LAST_DAY(TO_DATE('2004/02/03', 'yyyy/mm/dd'))
Result: Feb 29, 2004

Page 260

Sheet1
select product_id, order_date,
LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date
from orders;

LEAST(2, 5, 12, 3)
Result: 2
LEAST('2', '5', '12', '3')
Result: '12'
LEAST('apples', 'oranges', 'bananas')
Result: 'apples'
LEAST('apples', 'applis', 'applas')
Result: 'applas'
LEAST('apples', 'applis', 'applas', null)
Result: NULL

LENGTH(NULL)
Result: NULL
LENGTH('')
Result: NULL
LENGTH(' ')
Result: 1
LENGTH('Tech on the Net')
Result: 15
LENGTH('Tech on the Net ')
Result: 16

Page 261

Sheet1
LENGTH2(NULL)
Result: NULL
LENGTH2('')
Result: NULL
LENGTH2(' ')
Result: 1
LENGTH2('TechOnTheNet.com')
Result: 16
LENGTH2('TechOnTheNet.com ')
Result: 17

If string1 is NULL, then the LENGTH4 function will return NULL.

LENGTH4(NULL)
Result: NULL
LENGTH4('')
Result: NULL
LENGTH4(' ')
Result: 1
LENGTH4('TechOnTheNet.com')
Result: 16
LENGTH4('TechOnTheNet.com ')
Result: 17

If string1 is NULL, then the LENGTHB function will return NULL.

LENGTHB(NULL)
Result: NULL
(single-byte character set)
LENGTHB('')
Result: NULL

(single-byte character set)

LENGTHB(' ')
Result: 1

(single-byte character set)

LENGTHB('TechOnTheNet.com')
Result: 16
(single-byte character set)
LENGTHB('TechOnTheNet.com ')
Result: 17
(single-byte character set)

Page 262

Sheet1
If string1 is NULL, then the LENGTHC function will return NULL.
LENGTHC(NULL)
Result: NULL
LENGTHC('')
Result: NULL
LENGTHC(' ')
Result: 1
LENGTHC('TechOnTheNet.com')
Result: 16
LENGTHC('TechOnTheNet.com ')
Result: 17

WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

measure_column is the column whose values you wish to concatenate together in the
result set. Null values in the measure_column are ignored.
delimiter is optional. It is the delimiter to use when separating the
measure_column values when outputting the results.
order_by_clause determines the order that the concatenated values (ie:
measure_column) are returned.
SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name)
"Product_Listing"
FROM products;
LN(20)
Result: 2.99573227355399
LN(25)
Result: 3.2188758248682
LN(100)
Result: 4.60517018598809
LN(100.5)
Result: 4.61015772749913

select * from products

where LNNVL(qty >= reorder_level);
select LOCALTIMESTAMP
from dual;

Page 263

Sheet1
LOG(10, 20)
Result: 1.30102999566398
LOG(2, 15)
Result: 3.90689059560852
LOG(100, 1)
Result: 0
LOWER('Tech on the Net');
Result: 'tech on the net'
LOWER('GEORGE BURNS 123
');
Result: 'george burns 123
'
LPAD('tech', 7);
Result: '
tech'
LPAD('tech', 2);
Result: 'te'
LPAD('tech', 8, '0');
Result: '0000tech'
LPAD('tech on the net', 15, 'z');
Result: 'tech on the net'
LPAD('tech on the net', 16, 'z');
Result: 'ztech on the net'
LTRIM('
tech')
Result: 'tech'
LTRIM('
tech', ' ')
Result: 'tech'
LTRIM('000123', '0')
Result: '123'
LTRIM('123123Tech', '123')
Result: 'Tech'
LTRIM('123123Tech123', '123')
Result: 'Tech123'
LTRIM('xyxzyyyTech', 'xyz')
Result: 'Tech'
LTRIM('6372Tech', '0123456789')
Result: 'Tech'

Page 264

Sheet1
The MEDIAN function is NEW to Oracle 10! In older versions of Oracle, try using
the PERCENTILE_CONT function to calculate the median value.
select MEDIAN(salary)
from employees
where department = 'Marketing';
MOD(15, 4)
Result: 3
MOD(15, 0)
Result: 15
MOD(11.6, 2)
Result: 1.6
MOD(11.6, 2.1)
Result: 1.1
MOD(-15, 4)
Result: -3
MOD(-15, 0)
Result: -15

MONTHS_BETWEEN (TO_DATE ('2003/01/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14',

'yyyy/mm/dd') )
select NANVL(binary1, 0)
from test_table;
select NANVL(binary1, NULL)
from test_table;
NCHR(116)
Result: 't'
NCHR(84)
Result: 'T'
NEW_TIME (TO_DATE ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST')
NEXT_DAY('01-Aug-03', 'TUESDAY')
Result: '05-Aug-03'
NEXT_DAY('06-Aug-03', 'WEDNESDAY')
Result: '13-Aug-03'
NEXT_DAY('06-Aug-03', 'SUNDAY')
Result: '10-Aug-03'

Page 265

Sheet1
NULLIF(12, 12)
Result: NULL
NULLIF(12, 13)
Result: 12
NULLIF('apples', 'apples')
Result: NULL
NULLIF('apples', 'oranges')
Result: 'apples'
NULLIF(NULL, 12)
Result: ORA-00932 error

(because expr1 can not be the literal NULL)

NUMTODSINTERVAL(150, 'DAY')
Result: '+000000150'
NUMTODSINTERVAL(1500, 'HOUR')
Result: '+000000062'
NUMTODSINTERVAL(15000, 'MINUTE')
Result: '+000000010'
NUMTODSINTERVAL(150000, 'SECOND')
Result: '+000000001'
NUMTOYMINTERVAL(100000000, 'MONTH')
Result: '+0083'
NUMTOYMINTERVAL(100000, 'YEAR')
Result: '+0001'
SELECT NVL(supplier_city, 'n/a')
FROM suppliers;
select NVL2(supplier_city, 'Completed', 'n/a')
from suppliers;

Page 266

Sheet1
POWER(3, 2)
Result: 9
POWER(5, 3)
Result: 125
POWER(-5, 3)
Result: -125
POWER(6.2, 3)
Result: 238.328
POWER(6.2, 3.5)
Result: 593.431934277892

select employee_name, salary,

RANK() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';
DECLARE
a varchar2(8);
BEGIN
a := rawtohex('AB');
dbms_output.put_line(a);
SELECT RAWTOHEX('AB') INTO a FROM dual;
dbms_output.put_line(a);
END;

Page 267

Sheet1
REMAINDER(15, 6)
Result: 3
REMAINDER(15, 5)
Result: 0
REMAINDER(15, 4)
Result: -1
REMAINDER(11.6, 2)
Result: -0.4
REMAINDER(11.6, 2.1)
Result: -1
REMAINDER(-15, 4)
Result: 1

ROUND(TO_DATE ('22-AUG-03'),'YEAR')
Result: '01-JAN-04'
ROUND(TO_DATE ('22-AUG-03'),'Q')
Result: '01-OCT-03'
ROUND(TO_DATE ('22-AUG-03'),'MONTH')
Result: '01-SEP-03'
ROUND(TO_DATE ('22-AUG-03'),'DDD')
Result: '22-AUG-03'
ROUND(TO_DATE ('22-AUG-03'),'DAY')
Result: '24-AUG-03'

Page 268

Sheet1
ROUND(125.315)
Result: 125
ROUND(125.315, 0)
Result: 125
ROUND(125.315, 1)
Result: 125.3
ROUND(125.315, 2)
Result: 125.32
ROUND(125.315, 3)
Result: 125.315
ROUND(-125.315, 2)
Result: -125.32

Result: 'tech

'

RPAD('tech', 2)
Result: 'te'
RPAD('tech', 8, '0')
Result: 'tech0000'
RPAD('tech on the net', 15, 'z')
Result: 'tech on the net'
RPAD('tech on the net', 16, 'z')
Result: 'tech on the netz'

Page 269

Sheet1
RTRIM('tech
')
Result: 'tech'
RTRIM('tech
', ' ')
Result: 'tech'
RTRIM('123000', '0')
Result: '123'
RTRIM('Tech123123', '123')
Result: 'Tech'
RTRIM('123Tech123', '123')
Result: '123Tech'
RTRIM('Techxyxzyyy', 'xyz')
Result: 'Tech'
RTRIM('Tech6372', '0123456789')
Result: 'Tech'

SELECT SESSIONTIMEZONE
FROM dual;

If number > 0, then sign returns 1.

SIGN(-23)
Result: -1
SIGN(-0.001)
Result: -1
SIGN(0)
Result: 0
SIGN(0.001)
Result: 1
SIGN(23)
Result: 1
SIGN(23.601)
Result: 1

Page 270

Sheet1
SIN(3)
Result: 0.141120008059867
SIN(5.2)
Result: -0.883454655720153
SIN(-5.2)
Result: 0.883454655720153
SINH(3)
Result: 10.0178749274099
SINH(5.2)
Result: 90.6333626553652
SINH(-5.2)
Result: -90.6333626553652
SOUNDEX('tech on the net')
Result: 'T253'
SOUNDEX('TECH ON THE NET')
Result: 'T253'
SOUNDEX('apples')
Result: 'A142'
SOUNDEX('apples are great')
Result: 'A142'
SOUNDEX('applus')
Result: 'A142'
SQRT(9)
Result: 3
SQRT(37)
Result: 6.08276253029822
SQRT(5.617)
Result: 2.37002109695251
select STDDEV(bonus)
from employees;

Page 271

Sheet1
SUBSTR('This is a test', 6, 2)
Result: 'is'
SUBSTR('This is a test', 6)
Result: 'is a test'
SUBSTR('TechOnTheNet', 1, 4)
Result: 'Tech'
SUBSTR('TechOnTheNet', -3, 3)
Result: 'Net'
SUBSTR('TechOnTheNet', -6, 3)
Result: 'The'
SUBSTR('TechOnTheNet', -8, 2)
Result: 'On'

SELECT sysdate FROM Dual;

SELECT SYSTIMESTAMP
FROM dual;
SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT')
Result: 'RR-MM-DD'
SYS_CONTEXT('USERENV', 'NLS_SORT')
Result: 'BINARY'
TAN(3)
Result: -0.142546543074278
TAN(5.2)
Result: -1.88564187751976
TAN(-5.2)
Result: 1.88564187751976
TANH(3)
Result: 0.99505475368673
TANH(5.2)
Result: 0.999939136886199
TANH(-5.2)
Result: -0.999939136886199

Page 272

Sheet1
TO_CHAR(1210.73, '9999.9')
Result: ' 1210.7'
TO_CHAR(-1210.73, '9999.9')
Result: '-1210.7'
TO_CHAR(1210.73, '9,999.99')
Result: ' 1,210.73'
TO_CHAR(1210.73, '$9,999.00')
Result: ' $1,210.73'
TO_CHAR(21, '000099')
Result: ' 000021'
select TO_CLOB(nclob_column)
from suppliers;
TO_DATE('2003/07/09', 'yyyy/mm/dd')
Result: date value of July 9, 2003
TO_DATE('070903', 'MMDDYY')
Result: date value of July 9, 2003
TO_DATE('20020315', 'yyyymmdd')
Result: date value of Mar 15, 2002
TO_DSINTERVAL('150 08:30:00')
Result: '+000000150'
TO_DSINTERVAL('80 12:30:00')
Result: '+000000080'
TO_DSINTERVAL('95 18:30:00')
Result: '+000000095'
insert into companies (lob_column)
select TO_LOB(long_column)
from suppliers;
select TO_MULTI_BYTE('Tech on the net')
from dual;
select TO_NCLOB(lob_column)
from suppliers;
TO_NUMBER('1210.73', '9999.99')
Result: 1210.73
TO_NUMBER('546', '999')
Result: 546
TO_NUMBER('23', '99')
Result: 23

Page 273

Sheet1
select TO_SINGLE_BYTE('Tech on the net')
from dual;
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')
TO_YMINTERVAL('03-11')
Result: 3 years 11 months
(as an INTERVAL YEAR TO MONTH type)
TO_YMINTERVAL('01-05')
Result: 1 year 5 months

(as an INTERVAL YEAR TO MONTH type)

TO_YMINTERVAL('00-01')
Result: 0 years 1 month

(as an INTERVAL YEAR TO MONTH type)

TRANSLATE('1tech23', '123', '456')

Result: '4tech56'
TRANSLATE('222tech', '2ec', '3it')
Result: '333tith'

TRIM('
tech
Result: 'tech'

')

TRIM(' ' FROM

Result: 'tech'

'

tech

')

TRIM(LEADING '0' FROM '000123')

Result: '123'
TRIM(TRAILING '1' FROM 'Tech1')
Result: 'Tech'
TRIM(BOTH '1' FROM '123Tech111')
Result: '23Tech'

Page 274

Sheet1
TRUNC(TO_DATE('22-AUG-03'), 'YEAR')
Result: '01-JAN-03'
TRUNC(TO_DATE('22-AUG-03'), 'Q')
Result: '01-JUL-03'
TRUNC(TO_DATE('22-AUG-03'), 'MONTH')
Result: '01-AUG-03'
TRUNC(TO_DATE('22-AUG-03'), 'DDD')
Result: '22-AUG-03'
TRUNC(TO_DATE('22-AUG-03'), 'DAY')
Result: '17-AUG-03'

TRUNC(125.815)
Result: 125
TRUNC(125.815, 0)
Result: 125
TRUNC(125.815, 1)
Result: 125.8
TRUNC(125.815, 2)
Result: 125.81
TRUNC(125.815, 3)
Result: 125.815
TRUNC(-125.815, 2)
Result: -125.81
TRUNC(125.815, -1)
Result: 120
TRUNC(125.815, -2)
Result: 100
TRUNC(125.815, -3)
Result: 0

Page 275

Sheet1
TZ_OFFSET('US/Michigan')
Result: '-05:00'
TZ_OFFSET('-08:00')
Result: '-08:00'
TZ_OFFSET(sessiontimezone)
Result: '-07:00' (depending on your configuration)
TZ_OFFSET(dbtimezone)
Result: '-07:00' (depending on your configuration)
SELECT UID
INTO parm_uid
FROM dual;
UPPER('Tech on the Net')
Result: 'TECH ON THE NET'
UPPER('george burns 123
')
Result: 'GEORGE BURNS 123
'
select USER
into parm_user_ID
from dual;
USERENV('ENTRYID')
Result: FALSE
USERENV('LANGUAGE')
Result: 'AMERICAN_AMERICA.WE8DEC'
select VARIANCE(char_length)
from all_tab_columns;
select VAR_POP(data_length)
from all_tab_columns;
select VAR_SAMP(char_length)
from all_tab_columns;

Page 276

Sheet1
VSIZE('Tech on the net')
Result: 15
VSIZE('Tech on the net ')
Result: 16
VSIZE(null)
Result: <null>
VSIZE('')
Result: <null>
VSIZE(' ')
Result: 1

NA
NA
NA

Page 277

Sheet1

sales number(10, 2);

pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
PI CONSTANT NUMBER := 3.141592654;

NA

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);

Type grades IS VARRAY(5) OF INTEGER;

Page 278

Sheet1
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
CREATE OR REPLACE PACKAGE BODY cust_sal AS
PROCEDURE find_sal(c_id customers.id%TYPE) IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END cust_sal;
/

NA

Page 279

Sheet1

SELECT AVG(salary) AS "Avg Salary"

FROM employees
WHERE salary > 25000;

Page 280