1.

Basic DATABASE Concept and SQL

 Basic history of database concept: DBMS, RDBMS, ORDBMS
 Advantage of ORACLE database and version information
 Interface tools usage: sqlplus, isqlplus, sqldeveloper, Toad
 SQL Language overview : DQL, DML, DDL, DCL, TCL
 What is the usage of ANSI standard.
 SELECT Command - Column Alias Rules, String data,
 Concatenations with various data
 Null Value handling with number and characters,
 Arithmetic Operator
 Concatenation Operator,
 Eliminating Duplicate Rows

DBMS
A database management system (DBMS) is a collection of programs that
enables you to store, modify, and extract information from a database. It’s a
single table with no relation on other table.
RDBMS
Relational databases are sets of tables. It must have relation between two or
more tables. One table have a primary key value and another table have a
Foreign Keys – which point (reference) the first table primary key value.

ORDBMS
An object relational database is also called an object relational database
management system (ORDBMS). This system simply puts an object oriented
front end on a relational database (RDBMS).
The system will convert the object information into data tables with rows and
colums and handle the data the same as a relational database
Advantages of choosing Oracle Database.

Portability

Oracle is ported to more platforms running on more than 100 hardware

platforms and 20 networking protocols

Backup and Recovery

Oracle provides industrial strength support for on-line backup and recovery
and good software fault tolerance to disk failure.

Performance

Speed of a Oracle database and application is quite good, even with large
databases

Cursor Support
A cursor basically lets you do row-by-row processing.

Multiple Database Support

Oracle has a superior ability to manage multiple databases within the same
transaction using a two-phase commit protocol.

Oracle version

Oracle Database 10g Release 1-- of February 2006

Oracle Database 10g Release 2 -- April 2010

Oracle Database 11g Release 1-- September 2008

Oracle Database 11g Release 2-- of August 2013

Oracle Database 12c Release 1 --June 2013

Oracle Database 12c Release 1: 12.1.0.2 -- July 2014

Oracle Database 12c Release 2: 12.2.0.1 -- March 2017

Data Query Language (DQL)

DQL commands are basically SELECT statements. SELECT statements let you
query the database to find information in one or more tables, and return the
query as a result set.

 SELECT : Query the database.

Data Manipulation Language (DML)

Data manipulation language (DML) statements query and manipulate data in

existing schema objects.

 INSERT :Use to Add Rows to existing table.

 UPDATE :Use to Edit Existing Rows in tables.
 DELETE :Use to Delete Rows from tables.
 MERGE :Use to Update or Insert Rows depending on condition

Data Definition Language (DDL)

DDL (Data Definition Language) is a language used by a database

management system that allows users to define the database and specify data
types, structures and constraints on the data.

CREATE :Use to create objects like CREATE TABLE, CREATE FUNCTION,

CREATE SYNONYM, CREATE VIEW. Etc.

ALTER :Use to Alter Objects like ALTER TABLE, ALTER USER, ALTER
TABLESPACE, ALTER DATABASE. Etc.

DROP :Use to Drop Objects like DROP TABLE, DROP USER, DROP
TABLESPACE, DROP FUNCTION. Etc.

REPLACE :Use to Rename table names.

TRUNCATE :Use to truncate (delete all rows) a table.

Data Control Language (DCL)

Data Control Language Statements are used to grant privileges on tables,

views, sequences, synonyms, procedures to other users or roles.

GRANT :Use to grant privileges to other users or roles.

REVOKE :Use to take back privileges granted to other users and roles.

Privileges are of two types :

 System Privileges
 Object privileges

System Privileges are normally granted by a DBA to users. Examples of system

privileges are CREATE SESSION, CREATE TABLE, CREATE USER etc.

Object privileges means privileges on objects such as tables, views,

synonyms, procedure. These are granted by owner of the object.

Transaction Control Language (TCL)

Transaction Control Language(TCL) commands are used to manage

transactions in database. These are used to manage the changes made by
DML statements.

COMMIT : Make changes done in transaction permanent.

ROLLBACK : Rollbacks the state of database to the last commit point.
SAVEPOINT : Use to specify a point in transaction to which later you can rollback.
Column alias rules in oracle

 If the alias_name contains spaces, you must enclose the alias_name in

quotes.
 It is acceptable to use spaces when you are aliasing a column name.
However, it is not generally good practice to use spaces when you are
aliasing a table name.
 The alias_name is only valid within the scope of the SQL statement.

Strings variables

Strings can be literals or variables. A string literal begins and ends with a
single quotation mark:
'This is a string literal'

If you need to embed a single quote inside a string literal, you can type
in two single quotes right next to one another, as in:

'This isn''t a date'

select 'This isn''t a date' from dual

'THISI SN''TADATE'
This isn't a date

You can also use the “q” character to indicate an alternative terminating
character for the literal:
q'[This isn't a date]'

select q'[This isn't a date]' from dual

Q'[THISI SN' TADATE]'

This isn't a date
Concatenation operator
The concatenation operator manipulates character strings and CLOB data.
Operator ||

- Null Value handling with number and characters

- Null is unknown value or undefined value or un assigned value
- Null not equal to zero
- Null not equal to space
- Null not equal to null

select NULL + 5 from dual;

Will return null.

- IS NULL – return true if a column has null value

- Is not null – return true if a column has other than null value

- Nulls first – to make the position of null in first ( Order by ASC)

- Nulls last – to make the position of null in last( order by DESC)
Arithmetic Operators
Arithmetic operators can perform arithmetical operations on numeric
operands involved.

Operator Meaning

+ (Add) Addition
select first_name ,salary, salary +1000 from
employees

FIRST_NAME SALARY SALARY+1000

Steven 24000 25000
Neena 17000 18000

- (Subtract) Subtraction
select first_name ,salary, salary -1000 from
employees

FIRST_NAME SALARY SALARY-1000

Steven 24000 23000
Neena 17000 16000

* (Multiply) Multiplication
select first_name ,salary, salary * commission_pct
from employees
FIRST_NAME SALARY salary * commission_pct
John 14000 5600
Karen 13500 4050
Alberto 12000 3600

/ (Divide) Division
select first_name ,salary, salary / 2 from employees
FIRST_NAME SALARY SALARY/2
Steven 24000 12000
Neena 17000 8500
Concatenation Operator
The Oracle/PLSQL || operator allows you to concatenate 2 or more strings
together.

syntax string_expression1 || string_expression2 || …

NULL value in any expression is treated as ''

NULL Values (empty string)

But || returns NULL if all expressions are NULL

Expression Number and datetime expressions are implicitly converted to string

Conversion before concatenation

Alternatives CONCAT function Accept 2 parameters only

|| Operator

SELECT 'The city' || ' is ' || 'Paris' FROM dual;

-- Result: The city is Paris

NULL Is Empty String

NULL value in any string expression is treated as '' (empty string).

SELECT 'The city' || ' is ' || NULL FROM dual;

-- Result: The city is

But if all expressions evaluate to NULL, || operator returns NULL, not empty string.

SELECT NVL(NULL || NULL || NULL, 'Null value') FROM dual;

-- Result: Null value

Non-String Parameters Implicit Conversion

|| operator implicitly converts numbers and datetime values to string before concatenation.

SELECT 1 || 'st' FROM dual;

-- Result: 1st

SELECT 'Today is ' || SYSDATE FROM dual;

-- Result: Today is 28-MAY-12

Eliminating Duplicate Rows

Sample query Display records after Eliminating Duplicate rows

Using Distinct
Select distinct sal from emp

Using Unique
Select unique sal from emp

Using group by
select first_name from employees
group by first_name

Using rowid
select sal from emp
where rowid in
(select max(rowid) from emp group by sal);
Using join
select sal from emp e1
where rowid in
(select max(rowid) from emp e2
where e1.sal= e2.sal);
2. Restricting and Sorting Data
 WHERE Clause - Character Strings and Dates, number
 General Comparison Conditions = > >= < <= <>
 Other Comparison BETWEEN , IN , LIKE , NULL
 Logical Conditions AND OR NOT
 ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

WHERE Clause - Character Strings and Dates, number

- The WHERE clause is used to filter records.

- The WHERE clause is used to extract only those records that fulfill a specified condition.
- The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE
statement

select first_name, salary from employees where first_name='Steven'

FIRST_NAME SALARY
Steven 24000
Steven 2200

select first_name, salary from employees where department_id =90

FIRST_NAME SALARY
Steven 24000
Neena 17000
Lex 17000

select first_name, salary, hire_date from employees where hire_date = to_date('03-01-

1990', 'dd-mm-yyyy')

FIRST_NAME SALARY HIRE_DATE

Alexander 9000 03-JAN-90
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:

Operator Description

= Equal
select first_name, salary from employees where employee_id = 130

FIRST_NAME SALARY
Mozhe 2800

<> or != Not equal.

or ^= select first_name, salary, employee_id from employees where employee_id
<> 130

FIRST_NAME SALARY EMPLOYEE_ID

Steven 24000 100
Neena 17000 101
Lex 17000 102

> Greater than

select first_name, salary, employee_id from employees where salary >
18000
FIRST_NAME SALARY EMPLOYEE_ID
Steven 24000 100

< Less than

select first_name, salary, employee_id from employees where salary <
2500
FIRST_NAME SALARY EMPLOYEE_ID
James 2400 127
Steven 2200 128

>= Greater than or equal

select first_name, salary, employee_id from employees where salary

>=17000
FIRST_NAME SALARY EMPLOYEE_ID
Steven 24000 100
Neena 17000 101
Lex 17000 102
<= Less than or equal

select first_name, salary, employee_id from employees where salary < =

2400

FIRST_NAME SALARY EMPLOYEE_ID

James 2400 127
Steven 2200 128
TJ 2100 132

BETWEEN Between an inclusive range

select first_name, salary, hire_date from employees where hire_date

between to_date('03-01-1990', 'dd-mm-yyyy') and to_date('03-01-1996',
Not 'dd-mm-yyyy')
BETWEEN
FIRST_NAME SALARY HIRE_DATE
Lex 17000 13-JAN-93
Alexander 9000 03-JAN-90
Bruce 6000 21-MAY-91

select first_name, salary from employees where salary not between 15000
and 20000
FIRST_NAME SALARY
Neena 14000
Lex 22000

Search for a pattern

LIKE select first_name from employees where first_name like 'T%';

FIRST_NAME
NOT LIKE
TJ
Tayler

WHERE First_name LIKE 'a%' Finds any values that starts with "a"

WHERE First_name LIKE '%a' Finds any values that ends with "a"
 WHERE First_name LIKE '%or%' Finds any values that have "or" in any
position

WHERE First_name LIKE '_r%' Finds any values that have "r" in the
second position

WHERE First_name LIKE 'a_%_%' Finds any values that starts with "a"
and are at least 3 characters in length

WHERE First_name LIKE 'a%o' Finds any values that starts with "a"
and ends with "o"

WHERE First_name LIKE ‘_ _e’ Data with 3rd char is e

WHERE First_name LIKE ‘_ _’ Data contain only 2 leters

WHERE First_name LIKE ‘_ _ _ _%’ Data contain 4 letters or above

WHERE First_name LIKE %e%e%’ Data contain 2 e

WHERE First_name LIKE ‘N’ Data is N (First_name =’N’)

IN To specify multiple possible values for a column

NOT IN select first_name from employees where Manager_id in (100,101,102)

FIRST_NAME
Hermann
Gerald
Lex
 select first_name , job_id from employees where job_id not in ('IT_PROG',
'AD_VP', 'ST_MAN')

FIRST_NAME JOB_ID
Steven AD_PRES
Nancy FI_MGR
Daniel FI_ACCOUNT

SELECT * FROM departments

WHERE Location_id IN (SELECT location_id FROM locations where
country_id='US');

DEPARTMENT_ DEPARTMENT_NA MANAGER_I LOCATION_I

ID ME D D
60 IT 103 1400
50 Shipping 121 1500
10 Administration 200 1700

AND, OR, NOT

Using AND
select location_id from locations where country_id = 'IT' and city= 'Roma'
LOCATION_ID
1000

Using OR
select location_id from locations where country_id = 'IT' or city= 'Roma'
LOCATION_ID
1000
1100
Using AND OR
select first_name, salary from employees where salary=17000 and
(department_id= 90 or department_id=100)

FIRST_NAME SALARY
Neena 17000
Lex 17000

Using NOT
select first_name, salary, department_id from employees where not
department_id=100 and not department_id =90

FIRST_NAME SALARY DEPARTMENT_ID

Alexander 9000 60
Bruce 6000 60
Raju 4800 60
ORDER BY
Using ASC
select first_name from employees order by first_name asc
FIRST_NAME
Adam
Alana
Alberto
Alexander

Using DESC
select first_name from employees order by first_name desc
FIRST_NAME
Winston
William
William
Vance

Using Multiple column

select first_name , salary from employees order by first_name desc , salary
asc

FIRST_NAME SALARY
Winston 3200
William 7400
William 8300
Vance 2800
Valli 4800
Using alias
select first_name f , salary from employees order by f desc
F SALARY
Winston 3200
William 7400
William 8300

Using column Number

select first_name , salary, department_id from employees order by 2 desc

FIRST_NAME SALARY DEPARTMENT_ID

Steven 24000 90
Neena 17000 90
Lex 17000 90
Single-Row Functions
 Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, CONCAT,
LTRIM, RTRIM, TRIM, REPLACE, TRANSLATE, REVERSE
 Number Functions: ROUND, TRUNC, MOD, POWER, CEIL , FLOOR, ABS
 Dates Functions: SYSDATE, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ADD_MONTHS,
ROUND, TRUNC, Arithmetic on Date
 Conversion Functions: Implicit Data-Type Conversion & Explicit Data-Type Conversion, TO_CHAR
,TO_NUMBER ,TO_DATE
 General Functions: NVL , NVL2 , NULLIF, COALESCE
 CASE Expression, DECODE
 Nested function with real-time usage

Character or Text Functions:

Character or text functions are used to manipulate text strings. They accept strings
or characters as input and can return both character and number values as output.

Character Functions Example

1. LOWER

The Lower function converts the character values into lowercase letters.

SELECT lower('ORACLE') FROM DUAL;

oracle

2. UPPER

The Upper function converts the character values into uppercase letters.

SELECT upper('oracle') FROM DUAL;

ORACLE
3. INITCAP

The Initcap function coverts the first character of each word into uppercase and the remaining
characters into lowercase.

SELECT initcap('LEARN ORACLE') FROM DUAL;

Learn Oracle

4. CONCAT

The Concat function join the first string with the second string.

SELECT concat('Oracle',' Backup’) FROM DUAL;

Oracle Backup

5. SUBSTR

The Substr function returns specified characters from character value starting at position m and n
characters long. If you omit n, all characters starting from position m to the end are returned.

Syntax: substr(string [,m,n])

SELECT substr('ORACLE DATA RECOVERY',8,4) FROM DUAL;

O/p - > Data

SELECT substr('ORACLE DATA PUMP',8) FROM DUAL;

o/p - > DATA PUMP

You can specify m value as negative. In this case the count starts from the end of the string.

SELECT substr('ORACLE BACKUP',-6) FROM DUAL;

BACKUP
6. LENGTH

The Length function is used to find the number of characters in a string.

SELECT length('Oracle Data Guard') FROM DUAL;

17
7. INSTR

- The Instr function is used to find the position of a string in another string.
- Optionally you can provide position m to start searching for the string and the occurrence n
of the string.
- By default m and n are 1 which means to start the search at the beginning of the search and
the first occurrence.

Syntax: instr('Main String', 'substring', [m], [n])

SELECT instr('oralce apps','app') FROM DUAL;

SELECT instr('oralce apps is a great application','app',1,2) FROM DUAL;

24

8. LPAD

The Lpad function pads the character value right-justified to a total width of n character positions.

Syntax: lpad(column, n, 'string');

SELECT lpad('100',5,'x') FROM DUAL;

xx100

9. RPAD

The Rpad function pads the character value left-justified to a total width of n character positions.

Syntax: rpad(column, n, 'string');

SELECT rpad('100',5,'x') FROM DUAL;

100xx
10. TRIM

The Trim function removes the leading or trailing or both the characters from a string.

Syntax: trim(leading|trailing|both, trim_char from trim_source)

SELECT trim('O' FROM 'ORACLE') FROM DUAL;

RACLE

11. REPLACE

The Replace function is used to replace a character with another character in a string.

Syntax: replace(column, old_char,new_char)

SELECT replace('ORACLE DATA BACKUP', 'DATA','DATABASE') FROM DUAL;

ORACLE DATABASE BACKUP

12 Translate
- Translate replaces by position, the first character of the list to match is
replaced by the first character of the replacement list.
- The second character with the second, and if there are characters in the list
to match that do not have positional equivalents in the replacements list they
are dropped.

Translate ah to e

SELECT TRANSLATE('So What', 'ah', 'e') FROM DUAL;

So Wet

Remove double quotes

SELECT TRANSLATE('"Darn double quotes "', 'A"', 'A') FROM DUAL;

Darn double quotes

Make secret key
SELECT TRANSLATE('this is a secret', 'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')

FROM DUAL;

'p78o 8o 0 o42i4p

Secret to original

SELECT TRANSLATE('p78o 8o 0 o42i4p', '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')

FROM DUAL;

this is a secret

Length of vowels

SELECT length('this is a secret') -length(TRANSLATE('this is a secret','xaeiou','x')) from dual

13. Reverse
- used to reverse the string
SELECT REVERSE('Dan Morgan') FROM DUAL;

nagroM naD
Number Functions Example

1. ROUND

 The Round function rounds the value to the n decimal values.

 If n is not specified, there won't be any decimal places.
 If n is negative, numbers to the left of the decimal point are rounded.

Syntax: round(number,n)

SELECT round(123.67,1) FROM DUAL;

123.7

SELECT round(123.67) FROM DUAL;

124

SELECT round(123.67,-1) FROM DUAL;

120

2. TRUNC

The Trunc function truncates the value to the n decimal places. If n is omitted, then n defaults to zero.

Syntax: trunc(number,n)

SELECT trunc(123.67,1) FROM DUAL;

123.6

SELECT trunc(123.67) FROM DUAL;

123
3. MOD

 The Mod function returns the remainder of m divided by n.

Syntax: mod(m,n)

SELECT mod(8,5) FROM DUAL;

3
4. Ceil

 The ceil function returns round to next value .

Select ceil(1.9)from dual;

Select ceil(1.1)from dual;

5. Sign

Select sign(5)from dual;

Select sign(-5)from dual;

-1

Select sign(null)from dual;

-
6. abs

Remove ‘ – ‘ sign

Select abs(-5)from dual;

Select sign(-6)from dual;

6
7. power

Select power(3,2)from dual;

Select sign(2,3)from dual;

6
Date Functions Example

Date Format – > DD-MON-YYYY

19- OCT -2017

1. SYSDATE

 The Sysdate function returns the current oracle database server date and time.

SELECT sysdate FROM DUAL;

2. Arithmetic with Dates

 You can add or subtract the number of days or hours to the dates. You can also subtract the
dates

Add days, hours, min to date

SELECT sysdate+2 "add_days" FROM DUAL;

SELECT sysdate-3 "sub_days" FROM DUAL;

SELECT sysdate+3/24 "add_hours" FROM DUAL;

SELECT sysdate-2/24 "sub_hours" FROM DUAL;

SELECT sysdate-hire_date "sub_dates" FROM EMPLOYEES; -- returns number of days between

the two dates.

3. MONTHS_BETWEEN

The Months_Between function returns the number of months between the two given dates.

Syntax: months_between(date1,date2)

SELECT months_between(sysdate,hire_date) FROM EMPLOYEES;

SELECT months_between('01-JUL-2000', '23-JAN-2000') FROM DUAL;

4. ADD_MONTHS

The Add_Months is used to add or subtract the number of calendar months to the given date.

Syntax: add_months(date,n)

SELECT add_months(sysdate,3) FROM DUAL;

SELECT add_months(sysdate,-3) FROM DUAL;

SELECT add_months('01-JUL-2000', 3) FROM DUAL;

5. NEXT_DAY

The Next_Day function finds the date of the next specified day of the week. The syntax is

NEXT_DAY(date,'char')

The char can be a character string or a number representing the day.

SELECT next_day(sysdate,'FRIDAY') FROM DUAL;

SELECT next_day(sysdate,5) FROM DUAL;

SELECT next_day('01-JUL-2000', 'FRIDAY') FROM DUAL;

6. LAST_DAY

The Last_Day function returns the last day of the month.

SELECT last_day(sysdate) FROM DUAL;

SELECT last_day('01-JUL-2000') FROM DUAL;

7. ROUND

The Round function returns the date rounded to the specified format. The Syntax is
Round(date [,'fmt'])

Round to nearest start date or year

SELECT round(sysdate,'MONTH') FROM DUAL;

01-NOV-17

SELECT round(sysdate,'YEAR') FROM DUAL;

01-JAN-18

8. TRUNC

The Trunc function returns the date truncated to the specified format. The Syntax is
Trunc(date [,'fmt'])

SELECT trunc(sysdate,'MONTH') FROM DUAL;

01-Nov-2017

SELECT trunc(sysdate,'YEAR') FROM DUAL;

01-Jan-2017

9.To_date
Convert string date into oracle date format
SELECT to_date('10-12-06','MM-DD-YY') from dual;

12-OCT-06

SELECT to_date('jan 2007','MON YYYY') from dual;

01-Jan-2007

SELECT to_date('10-12-06','MM-DD-YY') from dual;

12-OCT-06

SELECT to_date('2007/05/31','YYYY/MM/DD') from dual;

31-MAY-07

SELECT to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI') from dual;

31-DEC-07
 SELECT to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS') from dual;

01-APR-06

SELECT to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') from dual;

15-MAY-06

SELECT to_date('022002','mmyyyy') from dual;

01-FEB-02

SELECT to_date('12319999','MMDDYYYY')from dual;

31-DEC-99

10. To_Char
Used to change date format

SELECT to_char(sysdate,’mm yy ') from dual;

11 17

SELECT to_char(sysdate,’day YYYY') from dual;

wednesday 2017

SELECT to_char(sysdate,’DDTH MONTH YYYY') from dual;

19TH SEPTHEMBER 2017

T0_char date format – 19-sep-2017

DD 19
MM 09
MON SEP
MONTH SEPTEMBER
YY 17
YYYY 2017
YEAR TWENTY SEVENTEEN
 DAY TUESDAY
DY TUE
D 3
HH 11 (HOUR 12H)
HH24 13 (HOUR 24H)
MI 06 (MIN)
SS 59
AM/PM AM
DDTH 19TH
DDSP NINTEEN
DDSPTH NINTEENTH

Implicit Data Type Conversion

 A VARCHAR2 or CHAR value can be implicitly converted to NUMBER or DATE type value by
Oracle.
 Similarly, a NUMBER or DATA type value can be automatically converted to character data by
Oracle server.
 Note that the impicit interconversion happens only when the character represents the a valid
number or date type value respectively.

For example, examine the below SELECT queries. Both the queries will give the same result because
Oracle internally treats 15000 and '15000' as same.
 Query-1

SELECT employee_id,first_name,salary

FROM employees

WHERE salary > 15000;

Query-2

SELECT employee_id,first_name,salary

FROM employees

WHERE salary > '15000';

Explicit Data Type Conversion

SQL Conversion functions are single row functions which are capable of typecasting column value, literal
or an expression .

TO_CHAR, TO_NUMBER and TO_DATE are the three functions which perform cross modification of data
types.

TO_CHAR function
TO_CHAR function is used to typecast a numeric or date input to character type with a format model
(optional).

To_char - date function already mention above

TO_NUMBER function
The TO_NUMBER function converts a character value to a numeric datatype. If the string being
converted contains nonnumeric characters, the function returns an error.

Syntax
TO_NUMBER (string1, [format], [nls_parameter])

Format Description
Model

CC Century

SCC Century BC prefixed with -

WW Week number (i.e. 1)

W Week number of the month (i.e. 5)

IW Week number of the year in ISO standard.

DDD Day of year in numbers (i.e. 365)

TZD Abbreviated time zone name. ie PST.

TZR Time zone region

SYEAR Year in characters, BC prefixed with -

BC BC/AD Indicator

TO_DATE function
 The function takes character values as input and returns formatted date equivalent of the same.
 The TO_DATE function allows users to enter a date in any format,

format Model Description

YEAR Year, spelled out

YYYY 4-digit year

YYY,YY,Y Last 3, 2, or 1 digit(s) of year.

IYY,IY,I Last 3, 2, or 1 digit(s) of ISO year.

Null function

NVL
NVL( exp1, exp2 )

NVL( string/number, replace_with )

if exp1 is null return exp2

if exp1 is not null return exp1

SELECT nvl(salary, 'Sorry, no pay!') FROM employees;

NVL2
NVL2(input_value, return_if_not_null, return_if_null)

NVL2( exp1, exp2, exp3)

If exp1 is not null return exp2

If exp1 is null return exp3

SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;

NULLIF

 The NULLIF function compares two arguments expr1 and expr2.

 If expr1 and expr2 are equal, it returns NULL;
 If expr1 and expr2 are not equal, it returns exp1

NULLIF (expr1, expr2)

SELECT NULLIF (12, 12)
FROM DUAL;
Null

SELECT NULLIF ('SUN', 'MOON')

FROM DUAL;
SUN

COALESCE
 COALESCE function, returns the first non-null expression in the argument list. It takes
minimum two mandatory parameters but maximum arguments has no limit.

COALESCE (expr1, expr2, ... expr_n )

SELECT COALESCE (address1, address2, address3) Address

FROM employees;

I WILL WORK LIKE THIS

 IF address1 is not null THEN
result := address1;
ELSIF address2 is not null THEN
result := address2;
ELSIF address3 is not null THEN
result := address3;
ELSE
result := null;
END IF;

Decode and Case

The DECODE function

The function is the SQL equivalence of IF..THEN..ELSE conditional procedural
statement.
Select decode
( region,'N','North','S','South','E','East', 'W','West', 'UNKNOWN' )
From customer;

Here if N – print North, S- South,… else Unknown

CASE expression
CASE expressions works on the same concept as DECODE but differs in
syntax and usage.
SELECT ename, empno,
(CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END) department
FROM emp
Nesting Functions

 Combining functions into the same formula is referred to as nesting functions.

 The output of one function is used as an input argument for another function in the formula.

4. JOINS
 EQUI JOIN / SIMPLE JOIN / NORMAL JOIN
 ANSI JOIN, LEFT OUTER, RIGHT OUTER, FULL OUTER
 NATURAL JOIN, NATURAL OUTER JOINS
 INNER JOIN, JOIN ... USING clause, JOIN ... ON clause,
 CROSS JOIN, NON-EQUI JOIN, SELF JOIN
 ORACLE STANDARD OUTER JOINS.
 Multi table Joins, Complex Joins How to simplified complex joins.

JOINS
A join is the process of combining data from two or more tables using matching columns.

Equi join / Inner join

Outer join
Left outer join
Right outer join
Full outer join
Cross join /Cartesian join
Self join
select * from course
CID CNAME
10 sql
20 C++
30 Java

Select * from student

SID SNAME CID
1 steven 20
2 lex 10
3 neena -

Equi join / Inner join

Only select matched record from both table
Select s.sname, c.cname
From students s, course c
Where s.cide=c.cid

SNAME CNAME
steven C++
lex sql

Left outer join

Select matched record from both table and un matched record from left
table
Select s.sname, c.cname
From student s, course c
Where s.cid=c.cid(+)
SNAME CNAME
steven C++
lex sql
neena -
Right outer join
Select matched record from both table and unmatched record from Right
table
Select s.sname, c.cname
From student s, course c
Where s.cid (+)=c.cid
SNAME CNAME
steven C++
lex sql
- Java

Full outer join

Return matched and un matched record from both table

Select s.sname, c.cname

From student s
full outer join
course c
on s.cid =c.cid

SNAME CNAME
steven C++
lex sql
neena -
- Java

Cross join
Cross join is a Cartesian product
No of rows in first table joined with no of rows in second table
Cartesian product is formed when user ignores where clause or a valied join
condition
It is used by developers for performance testing
If one table has 3 record and another has 0 record then cross join =0 rows
If 3 in both table then output will be 9 rows

Rule ->
If need to get n tables record then there must be n-1 join conditions
If 5 tables then 4 join condition

Select s.sname, c.cname

From student s , course c
SNAME CNAME
steven sql
steven C++
steven Java
lex sql
lex C++
lex Java
neena sql
neena C++
neena Java

Self join
Join table by itself

select e1.first_name as Ename , e2.first_name as Manager

from employees e1, employees e2
where e1.manager_id=e2.employee_id
ENAME MANAGER
Neena Steven
 Lex Steven
Alexander Lex
Bruce Alexander
Raju Alexander
Valli Alexander
Diana Alexander
Nancy Neena

Join Multiple table

select * from faculty

FID FNAME CID
1 Dinesh 10
2 kumar 30
3 sai 20

select s.sname, c.cname, f.fname

from
student s, course c, faculty f
where s.cid=c.cid and c.cid=f.cid
SNAME CNAME FNAME
lex sql Dinesh
steven C++ sai
5. Multi-row Functions
 Group Functions Rules, SUM, MIN, MAX, COUNT, AVG
 Creating Groups of Data: GROUP BY Clause
 Filtering Group Results: The HAVING Clause

Group Functions Rules

1. Accept one argument

2. Ignore null values
3. In select statement if there is any other colum other than group
function that should be mention in group by
4. Use having instead of where
5. Min max support all data types
6. Avg, sum support only number
7. Count(*) include null value

Select max(salary), min (salary), avg(salary), sum(salary), count( salary) from employees

MAX(SALARY) MIN(SALARY) AVG( SALARY) SUM( SALARY) COUNT( SALARY)

24000 2100 6461.68224299 691400 107

Select sum(1,2) from dual;

Error
---It accept one argument

Select * from t1;

A
-----
1000
-
1000
1000
4 rows selectd

Select count(a) from t1;

Count(A)
----------
3

--Ignore null values

But
Select count(a) from t1;

Count(*)
----------
4
-- count(*) include null value

If we add any 1000 to null result null

Select 1000 +1000+null from dual;
Error

GROUP BY Clause

select department_id, count(first_name)

from employees
group by department_id

DEPARTMENT_ID COUNT(FI RST_NAME)

100 6
30 6
90 3
20 2
70 1
110 2

 The HAVING Clause

select department_id, count(first_name)

from employees
group by department_id
having count(first_name) >2

DEPARTMENT_ID COUNT(FI RST_NAME)

100 6
30 6
90 3
6. Sub-queries

- Query embedded with another query

Types
1.single row sub query
2.multiple row sub query
3.scalar sub query
4.Inline view
5.Nested sub query
6.correlated sub query
7.Multiple column sub query

1.single row sub query

- Inner query execute first
- output of sub query will be single row single column
select first_name, salary from employees
where salary = (select max(salary) from employees);
FIRST_NAME SALARY
Steven 24000

Next example
select first_name, salary from employees
where salary = (select max(salary) from employees)

UNION ALL

select first_name, salary from employees

where salary = (select min(salary) from employees);

FIRST_NAME SALARY
Steven 24000
TJ 2100

scalar sub query

- Sub query written in select clause called scalar sub query
Show how many employees in each department
Select department_name,
(select count(*)
from employees emp
where emp.department_id= dept.department_id) cnt
 from departments dept

DEPARTMENT_NAME CNT
Administration 1
Marketing 2
Purchasing 6
Human Resources 1
Shipping 45
IT 5
Public Relations 1
Sales 34
Executive 3
Finance 6
Accounting 2

Another example
select 1+2 + (select 2+3 from dual)
from dual;
1+2+(SELECT2 +3FROMDUAL)
8

Inline views
-if query written in from clause is called inline views
select max(salary)
from
(select first_name, salary from employees where department_id=60);

MAX(SALARY)
9000

Nested Sub query

If outer query depend the output of inner query is called nested sub query

select first_name, salary from employees

where department_id in (select department_id from departments)
FIRST_NAME SALARY
Steven 24000
Neena 17000
Lex 17000
Alexander 9000
Bruce 6000
Raju 4800
Valli 4800
Diana 4200
Nancy 12000

Correlated sub query

 If inner query depends on outer query is called correlated sub query
select e.first_name, e.salary from employees e
where
e.department_id in (select d.department_id from departments d where
e.department_id= d.department_id)

FIRST_NAME SALARY
Steven 24000
Neena 17000
Lex 17000

Multiple row sub query

 Produce more than one row as output
It use ( in , all, any)

select first_name, salary from employees

where salary > all (select salary from employees where first_name='Peter');
 FIRST_NAME SALARY
Steven 24000
Neena 17000
Lex 17000
John 14000
Karen 13500

< any Less than the highest

=any Equivalent to in
>all More than the highest
<all Less than the lowest

Multiple column sub query

 If inner query produce more than one column is called multiple
column sub query
 Here column in outer and inner query remain same

select first_name, salary, department_id from employees

where (department_id, salary) in (select department_id, max(salary) from
employees group by department_id);

FIRST_NAME SALARY DEPARTMENT_ID

Nancy 12000 100
Den 11000 30
Steven 24000 90
Michael 13000 20
Hermann 10000 70
Shelley 12000 110
Adam 8200 50
 John 14000 80
Susan 6500 40
Alexander 9000 60
Jennifer 4400 10

7. Reporting data using interface commands

 pagesize, linesize , column heading , column format , colsep

 tTitle , bTitle , break on column, spool , CSV file generation, Text file generation
The first step is to configure some SQL*PLus system settings using the SETstatement.

For this example, we’ll be manually altering these settings one time prior to our query and
file generation, but if desired, you can change the defaults of various settings in your User
Profile, located in the login.sql file.

set colsep ,

set headsep off

set pagesize 0

set trimspool on

colsep is the separator character used to split your columns. For a .csv file, this is a simple
comma.

headsep is the separator character for the header row (if you require one). In this example
we’re not outputting the header row, so we’ll leave this off.

pagesize is the number of lines “per page.” This is a slightly archaic setting that is intended
for printing without having too many lines per page. With a value of 0, we don’t use pages
since we’re outputting to a file. If you elect to show the header row, set pagesize to a very
large number (larger than the expected number of record results in the query), so your
header row will only show up one time rather than once “per page.”

trimspool set to on simply removes trailing whitespace.

Now the final two settings will need to be altered depending on your query.

set linesize #

set numwidth #

For linesize, the # value should be the total number of output columns in your resulting
query.

numwidth is the column width (number of character spaces) used when outputting
numeric values.

With our settings taken care of, now we must tell SQL*Plus to output a file. This is
accomplished using the SPOOL statement.

While SPOOL is active, SQL*PLus will store the output of any query to the specified file.

Therefore, the next command to enter is spool:

spool file_path

Skipping ahead slightly, after your query is inserted, you also need to halt spoolso the file
output is closed by using the spool off command:

spool off

INSERT THE QUERY

The last step after the settings are modified and spool is running is to insert your query. For
our simple example, we’re outputting all books from our books table.

SELECT
title, primary_author
FROM books;

Don’t forget the semi-colon to close out your query statement, then enter the
aforementioned spool off command.

Create a new script file with the EDIT statement:

EDIT file_name

Now paste the entire script command list into your new file and save.

set colsep ,

set headsep off

set pagesize 0

set trimspool on

set linesize 2
set numwidth 5

spool books.csv

SELECT title, primary_author FROM books;

spool off

To execute the script, simply use the @ symbol followed by the file name:

@file_name

Your script should be executed and the .csv file created as expected.

8. Data Manipulation Language DML and Transaction Control Language TCL

 DML : INSERT, UPDATE, DELETE, MERGE
 TCL : COMMIT, ROLLBACK, SAVEPOINT

Insert
Insert a single row into a table:

INSERT INTO dept VALUES (10, 'pre-sales', 'New York');

Insert rows from one table into another table:

INSERT INTO emp2 SELECT * FROM emp;

CREATE TABLE emp3 AS SELECT * FROM emp;

Insert multiple rows into a table with a single statement:

INSERT ALL
INTO dept (deptno, dname, loc) VALUES (11, 'Sales', 'New York')
INTO dept (deptno, dname, loc) VALUES (12, 'Pre-sales', 'Dallas')
SELECT 1 FROM dual;
INSERT INTO dept
SELECT 13, 'Sales', 'New York' FROM dual UNION
SELECT 14, 'Pre-sales', 'Dallas' FROM dual;

Insert multiple rows into different tables with a single statement:

INSERT ALL
WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)
WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)
WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)
ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)
SELECT type, val FROM source_tab;

Update
Update is a SQL DML command used to change data in a table.

Note that it's important to specify a WHERE-clause, if not ALL rows in the table will be updated.

UPDATE emp SET comm = 100 WHERE empno = 7844;

UPDATE emp SET comm = 100

This will update all row in the column

Delete
Delete is a SQL DML command used to remove one or more rows from a table. Note that it's important to
specify a WHERE-clause, if not ALL rows in the table will be removed.

Remove selective rows from a table - in this case only one:

DELETE FROM emp WHERE empno = 7844;

COMMIT;

Remove all rows from a table (also compare to TRUNCATE):

DELETE FROM emp;
ROLLBACK;

Remove records in a specific partition:

DELETE FROM emp_part PARTITION p1;

Remove rows from a SELECT statement:

DELETE FROM (SELECT * FROM emp WHERE deptno = 10);

Merge
MERGE (sometimes also called UPSERT) is a SQL command that performs a series of
conditional update and/or insert operations. A row is updated if it exists, inserted if it doesn't.

Merge into t2

Using t1

On (t1.a=t2.a)

When matched then

Update set t2.b=t1.b

When not matched then

Insert (t2.a,t2.b) values (t1.a,t1.b);

Transaction Control Language (TCL)

Transaction control statements manage changes made by DML statements.

COMMIT : Make changes done in transaction permanent.

(To save all pending changes)

ROLLBACK : Rollbacks the state of database to the last commit point.

( To discard all pending changes)
SAVEPOINT : Use to specify a point in transaction to which later you can rollback.

(Is a marker- partial roll back)

Commit

insert into emp (empno,ename,sal) values (101,’Abid’,2300);

commit;

saved in database

Rollback
delete from emp;

rollback; /* undo the changes */

Save point

Table t1
A
----
1
2
Save point x;
3
4
Rollback to x
Now 3 and 4 will rollback

Select * from t1

A
----
1
2
Commit;
The value will store in database

9. Data Definition Language - DDL

 DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE
 DEFAULT OPTION.
 Constrain table copy

Data Definition Language (DDL) Statements

CREATE :Use to create objects like CREATE TABLE, CREATE FUNCTION,

CREATE SYNONYM, CREATE VIEW. Etc.

ALTER :Use to Alter Objects like ALTER TABLE, ALTER USER, ALTER
TABLESPACE, ALTER DATABASE. Etc.

DROP :Use to Drop Objects like DROP TABLE, DROP USER, DROP
TABLESPACE, DROP FUNCTION. Etc.

Rename :Use to Rename table names.

TRUNCATE :Use to truncate (delete all rows) a table.

Create table
create table course
(
cid number(3),
cname varchar(20) not null,
constraint c1 primary key(cid)
);

user_tab_columns
select * from user_tab_columns where table_name='EMPLOYEES';
user_tables
select * from user_tables;
 Used to find how many table in the user

USER_TAB_COLUMNS
select * from USER_TAB_COLUMNS where table_name='STUDENTS'
 Used to find all column and detail in the table

Sample table
create table students
(
sid number(4),
sname varchar(30) not null,
cid number(3),
phno number(10),
email varchar(30),
gender char(1) not null,
dob date not null,
doj timestamp,
address varchar(100),
resume long,
constraint c2 primary key(sid),
constraint c3 unique (phno),
constraint c4 unique(email),
constraint c5 check (gender in ('m','f')),
constraint c6 foreign key (cid) references course(cid)
);

Alter

Drop column
Alter table students drop column resume

Add column
Alter table students add (qual varchar(20));

Rename column
Alter table students rename email to mailid

Modify data type

Alter table students modify (doj date);

Drop constraint
Alter table students drop constraint c2;

Add constraint
Alter table students add constraint c2 primary key(sid);
Rename a table
Rename students to newstud

Drop a table
Drop table newstud

Truncate
Truncate table students;

Add multiple column

Alter table students add( fathername varchar(30), mothername
varchar(30));

Constrain table copy

Copy the table or clone the table with all constraints

select dbms_metadata.get_ddl('TABLE', ‘source table name’, '<schemaname'>) from dual

This will return the table code with metadata.

Now change table name and constraint name which want to be unique

select dbms_metadata.get_ddl('TABLE', ‘EMP1’, 'HR’) from dual

select dbms_metadata.get_dependent_ddl('TABLE', ‘EMP1') from dual;

10. Constraints
 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
 Column Level Constraint, Table Level Constraint Naming constraints and usage
 Adding a Constraint, Dropping a Constraint,
 Disabling Constraints, Enabling Constraints
 Validating Constraints

Constraint
A constraint is a data rule or restriction that is enforced within the database rather than at
application or object level.

 NOT NULL - Ensures that a column cannot have a NULL value

 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 DATE NOT NULL);

 UNIQUE - Ensures that all values in a column are different, allow null value,
automatically generate unique index, not accept duplicate value.
 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30) UNIQUE);

 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row
in a table

CREATE TABLE t1 (id NUMBER PRIMARY KEY, val1 VARCHAR2(30));

(Or)

CREATE TABLE t1 (

c1 NUMBER,

c2 VARCHAR2(30),

c3 VARCHAR2(30),

CONSTRAINT t1_pk PRIMARY KEY (c1,c2));

 FOREIGN KEY - Uniquely identifies a row/record in another table, reference key, refer
the primary key or unique of another table.
 Define a table with primary key to reference:
CREATE TABLE t1 (c1 NUMBER PRIMARY KEY);

CREATE TABLE t3 (

c1 NUMBER,

c2 NUMBER,

CONSTRAINT t1_fk FOREIGN KEY (c1) REFERENCES t1);

  CHECK - Ensures that all values in a column satisfies a specific condition

CREATE TABLE emp

(empno NUMBER PRIMARY KEY,
ename VARCHAR2(20),
sal NUMBER(10,2),
CONSTRAINT salcheck
CHECK (sal between 1000 and 20000)
);

 DEFAULT - Sets a default value for a column when no value is specified

 INDEX - Used to create and retrieve data from the database very quickly

Enable and disable constraint

alter table table_name ENABLE constraint constraint_name;

alter table table_name DISABLE constraint constraint_name;

11. Views
 Simple Views and Complex Views , Create, Drop, Source Code
 Rules for Performing DML Operations on a View
 WITH CHECK OPTION , WITH READ ONLY
 Inline Views
 Materialized View , Create, Refresh, Drop - Usage

Views
- Is a virtual table
- One view can create for one select statement
- Logically represent a subset of data from one or more table
- To make complex query look simple

View types

Simple view - Data manipulation operation can be done

Complex view – Data manipulation not possible

Simple view
Create or replace view v1
 As
Select first_name, department_id from employees
Where department_id = 60;

User_views
- Check how many views created

Select * from user_views

Run the views
- Select * from v1;
Drop the views
- Drop view v1;

Read only views

- Cant insert data
- Can only execute select statement

Create or replace view v2

As
Select * from t1
Where a = 2 with read only

Check option views

- Allow to insert values only in the where condition

Create or replace view v2

As
Select * from t1
Where a in (2,4,5) with check option;
Force views
- Create view without base table.

Create or replace force view v3

As
Select * from emp;

User_error
- Used to find error in views
Select *from user_error where type = ‘view’;
Complex view

Create or replace view v3

As
Select distinct a from t1;

Complex view contain these key words

Distinct /unique
Pseudo column
Data dictionary table
Function
Expression
Operators
Group by
Joins
Having
Sub query
Rules For Performing DML Operations On a View

 can always perfrorm DML operations om simple views

 cannot remove a row, if view contains:
o Group Function
o Group By
o Distinct
o RowNum
 cannot add data thru view, if view contains:
o Group Function
o Group By
o Distinct
o RowNum
o Parent table that has columns - not null
 cannot modify data in view, if view contains
o Group Function
o Group By
o Distinct
o RowNum
o Expressions (case when, decode)
Materialized Views

- Store the out put of query

- Is a database object that store the result of the query
- Snapshot of a remote table data
- After create a materialized view , if table update that wont refelect the
materialized view
- If need to refresh materialized view use dbms_mview.refresh

Create

Create materialized view mv10

As
Select first_name, department_id from employees
Where department_id =60;
Run
Select * from mv10;
User_mviews
- Check howmany materialized view created
- Select * from user_mviews
Update or Refresh materialized view
 begin
dbms_mview.refresh(‘mv10’, ‘c’);
end;
The following refresh types are available.

 FAST : A fast refresh is attempted. If materialized view logs are not present
against the source tables in advance, the creation fails.
 COMPLETE : The table segment supporting the materialized view is truncated
and repopulated completely using the associated query.
 FORCE : A fast refresh is attempted. If one is not possible a complete refresh is
performed.
Refresh groups of views

A refresh group is a collection of one or more materialized views that Oracle refreshes
in an atomic transaction, guaranteeing that relationships among the master tables are
preserved.
To create a refresh group:

exec DBMS_REFRESH.MAKE(name=>'my_grp', -
list=>'my_mv1,my_mv2', -
next_date => sysdate, -
interval => 'null');

Drop Materialized view

- Drop materialized view mv10;
12. Other Database Objects
 Sequence- NEXTVAL and CURRVAL
 Index - When to Create an Index, When Not to Create an Index.
 Synonyms

Index
- Used to improve performance
- Need to create in where clause
- Oracle create separate copy of a column in asc order to fetch result
fast.
- Can create multiple index for single table

Types
1. B* tree index
2. Bitmap index
3. Composite index
4. Unique index
5. Function based index
6. Indexed virtual column
7. Index organized table(IOT)

High cardinality column – column contain more unique record

 Low cardinality column – column contain more duplicate record

B* tree index
- B – Balanced index
- Create for high cardinality column ( more unique record)

Create index idx1 on emp(first_name);

Bitmap Index
- Create for low cardinality index ( more duplicate index)
Create index idx2 on emp( gender );
Composite index
- Index created for multiple column

Create index idx3 on emp(first_name, salary);

Function based index
- Index created based on function
Create index idx4 on emp (upper(first_name));
Unique Index
- Will not allow duplicate and null value

Create unique index idx5 on emp(empid);

Index organization table
- Make the data in ascending order

Create table t1(a number primary key) organization index;

Indexed virtual column
Used in auto generated virtual column
 Create index idx7 on emp (bonus);

This index is created for the virtual table below

Create table employees
(
Ename varchar (30),
Sal number,
Bonus number generated always as (sal/2) virtual
);

User_indexes
Check how many index created
Select * from user_indexes where table_name =’emp’;
Drop index
Drop index idx2;

Hint to choose index

We have to index idx1 , idx2
Create index idx1 on emp(name);
Create index idx2 on emp(sal);

Here choose idx2 as hints

Select /* + index (emp idx2) */
Name, salary from emp
Where name=’david’ and sal=1000;
Pseudo-Column
Is an Oracle assigned value (pseudo-field) used in the same context as an Oracle
Database column

1. SYSDATE and SYSTIMESTAMP

2. UID and USER
3. CURRVAL and NEXTVAL
4. LEVEL
5. ROWID
6. ROWNUM
7. ORA_ROWSCN

SYSDATE and SYSTIMESTAMP

Return the current DATE and TIMESTAMP:

SQL> SELECT sysdate, systimestamp FROM dual;

SYSDATE SYSTIMESTAMP

--------- ----------------------------------------

13-DEC-07 13-DEC-07 10.02.31.956842 AM +02:00

UID and USER

Return the User ID and Name of a database user:

SQL> SELECT uid, user FROM dual;

UID USER
 ---------- ------------------------------

50 MICHEL

LEVEL

The term LEVEL refers to a pseudocolumn in Oracle which is used in a

hierarchical query to identify the hierarchy level in numeric format.
select employee_id, first_name, manager_id, level
from employees
start with manager_id is null
connect by prior employee_id =manager_id

EMPLOYEE_ID FIRST_NAME MANAGER_ID LEVEL

100 Steven - 1
101 Neena 100 2
108 Nancy 101 3
109 Daniel 108 4
110 John 108 4
111 Ismael 108 4

Second example start with lex

select employee_id, first_name, manager_id, level
from employees
start with first_name='Lex'
connect by prior employee_id =manager_id
 EMPLOYEE_ID FIRST_NAME MANAGER_ID LEVEL
102 Lex 100 1
103 Alexander 102 2
104 Bruce 103 3
105 Raju 103 3
106 Valli 103 3
107 Diana 103 3

ROWID

ROWID returns the rowid (binary address) of a row in a database table.

ROWNUM

- ROWNUM returns a number indicating the order in which a row was selected
from a table.
- The first row selected has a ROWNUM of 1, the second row has a ROWNUM of
2, and so on.
- If a SELECT statement includes an ORDER BY clause, ROWNUMs are
assigned to the retrieved rows before the sort is done.

Example for rownum and rowid

select employee_id, first_name, manager_id, ROWNUM, ROWID
from employees;

EMPLOYEE_ID FIRST_NAME MANAGER_ID ROWNUM ROWID

100 Steven - 1 AAAC9EAAEAAAABXAAA
101 Neena 100 2 AAAC9EAAEAAAABXAAB
102 Lex 100 3 AAAC9EAAEAAAABXAAC
103 Alexander 102 4 AAAC9EAAEAAAABXAAD
104 Bruce 103 5 AAAC9EAAEAAAABXAAE
105 Raju 103 6 AAAC9EAAEAAAABXAAF
 - If use order by clause for particular column rownum will change order
not in sequence
- Because
- A SELECT statement includes an ORDER BY clause, ROWNUMs are assigned
to the retrieved rows before the sort is done.

select employee_id, first_name, manager_id, ROWNUM, ROWID

from employees order by first_name;
EMPLOYEE_ID FIRST_NAME MANAGER_ID ROWNUM ROWID
121 Adam 100 22 AAAC9EAAEAAAABXAAV
196 Alana 124 97 AAAC9EAAEAAAABXABg
147 Alberto 100 48 AAAC9EAAEAAAABXAAv
103 Alexander 102 4 AAAC9EAAEAAAABXAAD

CURRVAL and NEXTVAL

The current sequence number store in CURRVAL. NEXTVAL increments the

sequence and returns the next value.

sequence_name.CURRVAL
sequence_name.NEXTVAL

INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename)

Data dictionary table

- Dual
-
- V$database -> dispalay the database name
Select * from v$database;
 - V$version- > to find oracle version
Select * from v$version

- All_users -> to find how many user in database

Select * from all_users
Sequence
- Schema object , automatically generate unique number.

Create sequence s1
Start with 5
Increment by 1
Max value 10;

Call sequence number

Select s1.nextval from dual;
Select s1.currval from dual;
INSERT INTO emp VALUES (s1.NEXTVAL, my_ename)
Alter sequence
Alter sequence s1
Increment by 2
Max val 20;
Drop sequence
Drop sequence s1;
User_sequence
- Find how many sequence created

Select * from user_sequence;

 Data type in oracle

Where p is the precision and s is the

Precision can range from scale.
1 to 38.
number(p,s)
Scale can range from -84 For example, number(7,2) is a number
to 127. that has 5 digits before the decimal and
2 digits after the decimal.

Where size is the number of characters to

store. Fixed-length strings. Space padded.

char(size) Maximum size of 2000 bytes. If no bracket it take 1 char

Char(20) -> block 20, so length is 20 will

not release unused block

Maximum size of 4000 Where size is the number of characters to

bytes. store. Variable-length string.
varchar2(size)
Maximum size of 32KB in Must need to give size.
PLSQL. It will release unused block

Variable-length strings. (backward

long Maximum size of 2GB.
compatible)
 In one table max of one long datatype can
be used.

Clob Store up to (4 gigabytes -1) *

(the value of the CHUNK Stores single-byte and multi-byte character
(character parameter of LOB storage) data.
large object) of character data.

File locators that point to a binary file on

Maximum file size of 232-1
bfile the server file system (outside the
bytes. (4 gigabytes -1)
database).(accept path of a file)

Store up to (4 gigabytes -1) *

blob (the value of the CHUNK Stores unstructured binary large objects.
parameter of LOB storage).

A date between Jan 1, 4712

date Accept date
BC and Dec 31, 9999 AD.

timestamp fractional seconds Includes year, month, day, hour,

(fractional precision must be a number minute, and seconds.
seconds between 0 and 9. (default is
precision) 6) For example:
timestamp(6)

xmltype to store the original XML data

External Tables : Querying Data From Flat Files in Oracle

- Used to read data from to txt files.

- Data will not store in database.
- Table will be in oracle and data will be in file

Step1
Login to sys /admin
>Sqlplus sys/admin as sysdba
Step 2
Create directory in oracle
>create directory test as ‘D:\new’;
Step 3
Permission to hr user
>Grant Read, Write on directory test to hr;
Step 4
Login to hr user
>connect hr/admin
 Step 5
Create 2 text files, with data in comma separator
Step 6
Create the external table as column like in the file

CREATE TABLE countries_ext (

country_code VARCHAR2(5),
country_name VARCHAR2(50),
country_language VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
country_code CHAR(5),
country_name CHAR(50),
country_language CHAR(50)
)
)
LOCATION ('Countries1.txt','Countries2.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

SQL> SELECT *
2 FROM countries_ext
3 ORDER BY country_name;
COUNT COUNTRY_NAME COUNTRY_LANGUAGE
----- ---------------------------- -----------------------------
ENG England English
FRA France French
GER Germany German
IRE Ireland English
SCO Scotland English
USA Unites States of America English
WAL Wales Welsh

7 rows selected.

Sql Loader
Is a bulk loader utlity to load data from a flat file to an oracle table
It is used to high performance data loads

Step1
Create table
CREATE TABLE emp (empno number(5),
name varchar2(50),
sal number(10,2),
jdate date);
step 2
move csv file from remote to local

a. For example:-

C:\> ftp 200.200.100.111

Name: oracle

Password:oracle
FTP>

b. Now give PUT command to transfer file from current Windows machine to
Linux machine
 FTP>put
Local file:C:\>emp.csv
remote-file:/u01/oracle/emp.csv

File transferred in 0.29 Seconds

FTP>

c. Now after the file is transferred quit the FTP utility by typing bye command.

FTP>bye
Good-Bye

Step 3

After creating the table, you have to write a control file describing the actions which
SQL Loader should do. You can use any text editor to write the control file.

$ vi emp.ctl

1. LOAD DATA
2. INFILE ‘/u01/oracle/emp.csv’
3. APPEND OR TRUNCATE OR INSERT OR REPLACE
4. INSERT INTO TABLE emp
5. FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”’
TRAILING NULLCOLS
6. (empno,name,sal,jdate date ‘mm/dd/yyyy’)

Line1. The LOAD DATA statement is required at the beginning of the control file.
Line2. The INFILE option specifies where the input file is located

Line3. You can use any of the following loading option

INSERT : Loads rows only if the target table is empty

APPEND : Load rows if the target table is empty or not.
REPLACE : First deletes all the rows in the existing table and then, load rows.
TRUNCATE : First truncates the table and then load rows.
Line4. Insert command to insert data
Line 5.
Indicates how the fields are separated in input file.
Here the fields are separated by “,” so specified “,” as the terminating char for fields.
 We can replace this by any char which is used to terminate fields.
Some of the popularly use terminating characters are semicolon “;”, colon “:”, pipe “|”
etc.
TRAILING NULLCOLS means if the last column is null then treat this as null value,
otherwise, SQL LOADER will treat the record as bad if the last column is null.
Line 6.
In this line specify the columns of the target table. Note how do you specify format for
Date columns

Step 4

After you have wrote the control file save it as ‘emp .ctl file’ and then, call SQL
Loader utility by typing the following command

$sqlldr userid=scott/tiger control=path/emp.ctl

log=path/emp.log skip =1 or 2 or …

In windows

Sqlldr hr/admin control=D:\new\emp.ctl

The LOG option of sqlldr specifies where the log file of this sql loader
session should be created.

The log file contains all actions which SQL loader has performed i.e. how
many rows were loaded, how many were rejected and how much time is
taken to load the rows and etc.

Skip is used to skip how many lines in the file before load.

Step 5

Select * from emp;

SQL loader
----------------

1.
create table course
(
cid number(3),
cname varchar(20)
);

2.
insert into course values(30, 'Java');

3.
select * from course;

CID CNAME
10 sql
20 C++
30 Java

4.
create a file 1.csv with the following data and store in d:/new

cid,cname
40,sos
50,R
60,python
70,pega

5.

create a script and save as 2.ctl in d:/new

Load data infile 'd:\new\1.csv'

append
into table course
fields terminated by ','
(cid,cname)

6.

open cmd prompt type

SQLLDR HR/ADMIN CONTROL =D:/NEW/2.CTL SKIP=1

7.

NOW CHECK SELECT

select * from course;

CID CNAME
40 sos
50 R
60 python
70 pega
10 sql
20 C++
30 Java
Global temporary table

- Temp table insert value and can use till give commit
- If commit data will delete
- Many large applications make extensive use of Temporary data storage.
Oracle implements the feature in Temporary tables.
- Global temporary tables are types of database tables which can privately
store data, persistently for a session or transaction.
- The data flushes out at defined instant automatically.
- Default is on commit delete rows

On Commit delete rows

- Data will delete when give commit or exit
Create global temporary table
Gt1 (A number)
On Commit delete rows;

Insert into gt1 values (1);

Insert into gt1 values (2);
Select * from gt1;
 1
2
Commit;
Select * from gt1;
No rows selected

On commit preserve rows

- Commit data will be in table
- Exit data will delete from table

Create global temporary table

Gt1 (A number)
On commit preserve rows;

Insert into gt1 values (1);

Insert into gt1 values (2);
Select * from gt1;
1
2
Commit;
Select * from gt1;
1
2
>exit;
 Data delete

Analytical function

RANK Use over(partition by order by)

DENSE_RANK
LAG and LEAD
ROW_COUNT
LISTAGG Use within group (order by partition
by)
FIRST and LAST MIN(salary) KEEP (DENSE_RANK
FIRST ORDER BY salary) OVER
(PARTITION BY department_id)

FIRST_VALUE and LAST_VALUE Use over(partition by order by)

RANK

- If duplicate value skip to next number

Select first_name ,salary, rank() over (order by salary desc) from

employees
 FIRST_NAME SALARY RANK()OVER(ORDERBYSALARYDESC)
Steven 24000 1
Neena 17000 2
Lex 17000 2
John 14000 4
Karen 13500 5
Michael 13000 6

Here 3 is skip to next value

DENSE_RANK

- If duplicate value will not skip to next number

FIRST_NAME SALARY DENSE_ RANK()OVER(ORDERBYSALARYDESC)
Steven 24000 1
Neena 17000 2
Lex 17000 2
John 14000 3
Karen 13500 4
Michael 13000 5
Nancy 12000 6
Alberto 12000 6

FIRST and LAST

- The FIRST and LAST functions can be used to return the first or last value from an
ordered sequence.
- Say we want to display the salary of each employee, along with the lowest and
highest within their department we may use like.
-
SELECT employee_id, department_id, salary,

MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) OVER (PARTITION BY

department_id) "Lowest" ,
 MIN(salary) KEEP (DENSE_RANK LAST ORDER BY salary) OVER (PARTITION BY
department_id) "Highest"

FROM employees

ORDER BY department_id, salary;

EMPLOYEE_ID DEPARTMENT_ID SALARY Lowest Highest

200 10 4400 4400 4400
202 20 6000 6000 13000
201 20 13000 6000 13000
119 30 2500 2500 11000
118 30 2600 2500 11000
117 30 2800 2500 11000
116 30 2900 2500 11000

FIRST_VALUE
- The FIRST_VALUE analytic function is similar to the FIRST analytic function,
allowing you to return the first result from an ordered set.

SELECT employee_id, department_id, salary , FIRST_VALUE(salary) OVER

(PARTITION BY department_id ORDER BY salary) FROM employees;

EMPLOYE DEPARTME SALA FIRST_ VALUE(SALARY) OVER( PARTITIONBYDEPARTME

E_ID NT_ID RY NT_IDORDERBYSALARY)
200 10 4400 4400
202 20 6000 6000
201 20 13000 6000
119 30 2500 2500
118 30 2600 2500

LAST_VALUE
- The LAST_VALUE analytic function is similar to the LAST analytic function, allowing
you to return the last result from an ordered set.

Lead()
 - To fetch next record
Select first_name , lead(first_name,1,'X') over (order by first_name asc) from employees

FIRST_NAME LEAD(FIRST_NAME,1,' X')OVER(ORDERBYFIRST_NAMEASC)

Adam Alana
Alana Alberto
Alberto Alexander
Alexander Alexander
Alexander X

Lag()
- Fetch previous Record

Select first_name , lag(first_name,1,'X') over (order by first_name asc) from

employees

FIRST_NAME LAG( FIRST_NAME,1,' X')OVER( ORDERBYFIRST_NAMEASC)

Adam X
Alana Adam
Alberto Alana
Alexander Alberto
Alexander Alexander
Alexis Alexander
Allan Alexis

Row_number()
- It will create row number for each row according to the order specified
by the column

Select first_name , salary, row_number()over (order by salary asc) from employees

FIRST_NAME SALARY ROW_NUMBER()OVER(ORDERBYSALARYASC)

 TJ 2100 1
Steven 2200 2
Hazel 2200 3
James 2400 4
Ki 2400 5
Karen 2500 6
James 2500 7

Listagg()
- Use keyword within group
- Convert row data into column data

select listagg (employee_id, ‘,’) within group (order by employee_id asc) as

row_to_column from employees;

ROW_TO_COLUMN

101,102,103,104,105,106,107

Next example order by

Select * from dept;

DEPTID EMPID
10 200
10 201
10 304
20 301
20 456
20 321
20 978
30 122
30 222
40 444
select deptid, listagg(empid,',') within group(order by empid) from deptid group by deptid;

DEPTID LISTAGG (EMPID,',')WITHINGROUP(ORDERBY EMPID)

10 200,201,304
20 301,321,456,978
30 122,222
40 444

Example – find dept wise rank wise

Select first_name , salary, Department_id , rank() over (partition by department_id order
by salary desc) as rank from employees

FIRST_NAME SALARY DEPARTMENT_ID RANK

Jennifer 4400 10 1
Michael 13000 20 1
Pat 6000 20 2
Den 11000 30 1
Alexander 3100 30 2
Shelli 2900 30 3
Sigal 2800 30 4
Guy 2600 30 5
Karen 2500 30 6
Susan 6500 40 1
Adam 8200 50 1

Example – with clause

with e as (Select first_name , salary, Department_id , rank() over (partition by

department_id order by salary desc) as rank from employees)

select * from e where rank =1;

FIRST_NAME SALARY DEPARTMENT_ID RANK

 Jennifer 4400 10 1
Michael 13000 20 1
Den 11000 30 1
Susan 6500 40 1

Random Number

Generating a random number

>SELECT DBMS_RANDOM.random FROM dual;

RANDOM

==========

-748908608

Generating a random number between year 1999 and 2007

The function VALUE generate random numbers from the range provided. The range will be taken as 0-1
if none is provided.

>SELECT ROUND(DBMS_RANDOM.VALUE(0000,2007)) num FROM dual;

NUM

==========

2001

Generating a random string of 10 characters

The function STRING generate strings in upper case, lower case or alphanumeric format.

>SELECT dbms_random.string('A', 10) str FROM dual;

STR

==========

eYksMvGhTT

Random - Date
Oracle stores dates as integer offsets from a key date in the past (January 1, 4712 B.C., in case you were
curious). This means that you can generate random dates in a given range by finding the integer that
corresponds to your desired start date, and then adding a random integer to it.

You can generate the internal date number for today's date by using the TO_CHAR function with the 'J'
format code:

SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;

Generating a random date during the year 2008

For example, to generate random dates during the year 2003, you would first determine the date
integer for January 1, 2003:

>SELECT TO_CHAR(TO_DATE('01/01/08','mm/dd/yy'),'J') FROM DUAL;

TO_CHAR

=======

2454467

The system responds with 2454467. So, to generate a random date within the year, we use
DBMS_RANDOM.VALUE with :
a low_value of 2454467

and a high_value of 2454467 + 364 day by year,

and convert it to a date.

>SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2454467,2454467+364)),'J') FROM DUAL;

TO_DATE(T

=========

04-JUN-08

Random Number - Examples:

E.g.: Generating a random number (positive or negative)

SQL> select dbms_random.random from dual;

RANDOM
_____________
1393936551

E.g.: Generating a random number between 0 and 1.

SQL> select dbms_random.value from dual;

VALUE
_____________
1

E.g.: Generating a random number from a range, between 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;

NUM
_____________
611
E.g.: Generating a 12 digit random number.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

NUM
_____________
175055628780

E.g.: Generating an upper case string of 20 characters

SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD

E.g.: Generating a lower case string of 20 characters

SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
Xpoovuspmehvcptdtzcz

E.g.: Generating an alphanumeric string of 20 characters.

SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB

E.g.: Generating an upper case alphanumeric string of 20 characters

SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

E.g.: Generating a string of printable 20 characters. This will output a string of all
characters that could possibly be printed.

SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
*Yw>IKzsj\uI8K[IQPag

E.g.: Example for calling the dbms_random package and setting the seed for generating the
same set of random numbers in different sessions. Please note that the same random
numbers are generated in different sessions. Though I have found this to work on most
accounts, in some cases, the first number generated was different in different sessions and
the remaining were same. I recommend not using this option in any of production code until
it is properly document by Oracle.

jaJA>declare
2 l_num number;
3 begin
4 l_num := dbms_random.random;
5 dbms_output.put_line(l_num);
6 dbms_random.seed('amar testing 67890');
7 l_num := dbms_random.random;
8 dbms_output.put_line(l_num);
9 end;
10 /
483791552
478774329

PL/SQL procedure successfully completed.

DCL command
Data Control Language(DCL) is used to control privilege in Database.

To check which database login

>select name from V$database;

To check how many user in database

>select * from all_users;

To create new user

>create user lex identified by welcome123;

Grant connect permission to user

>grant connect , resource to lex;

Change password to lex

>alter user lex identified tiger123
Grant select permission
>grant select on emp to les;

Revoke select
>revoke select on emp from lex;

Grant select,insert, update,delete permission

>Grant select,insert, update,delete on emp to lex

Revoke all permission

>revoke all on emp from lex;

Grant select permission to all user

>grant select on emp to public;

Grant create table to lex

>grant create table to lex;

Grant all privilege to lex

>grant sysdba to lex;

Revoke select from all user

>revoke select on emp from public;

Create a group
>create role r1;
Add user to group(role)
>grant r1 to lex, steven;

Grant select permission to group

>grant select on emp to r1;
Drop user
>drop user lex;
Drop group
>drop group r1;
Synonyms
- Is an alternative name for an object

Select * from emp;

Create synonyms e1 for emp;

To view no of synonyms created

Select * from user_synonyms;

Select * from e1 and select * from emp both are same.

Can create synonym from another synonym

Create synonym e2 for e1;

Insert values in synonym will insert into table.

Oracle Set Operators

- Returns all row from two different select statement

- Must have same number of column with same data type from both select statement.

UNION Example

The following statement combines the results with the UNION operator, which
eliminates duplicate selected rows.

select empno,ename,sal from emp

UNION
select empno,ename,salary from oldemp

UNION ALL Example

- UNION ALL operator does not eliminate duplicate selected rows
select empno,ename from emp
union all
select empno,ename from oldemp;

INTERSECT Example
- It returns the common records from all the select statement
SELECT empno FROM emp
INTERSECT
SELECT empno FROM oldemp;
MINUS Example

Returns all the rows in the first select statement that are not returned by
second select statement.
SELECT empno FROM emp
MINUS
SELECT empno FROM oldemp;

SQL PLUS – Commands

Work in cmd promt only

Clear screen
Set feedback on
Set feedback off

Set pagesize
Set line size

Ttitle
Btitle

Set heading on
Set heading off

& substitution variable

&& constant

Define
Undefine

Set verify on
Set verify off
Show user

Set timing on
Set timing off

Spool
Ed
@
/
Exit
quit

Clear screen

- Clear the screen in the terminal window

Set feedback on
Set feedback off

Select * from course

cid cname
10 sql
1 row selected

If on -> ‘1 row selected’ will display

If off -> ‘1 row selected’ will not display

Set pagesize

>set page size 40

-The output of query will print 40 rows per page
- by default 10 rows will print

Set line size

 >set line size 80
-The output of query will print 80 char in a line

Ttitle

>ttitle Reports
-Create a title on top of a query output page
-Header part

Btitle

>btitle confidential
-Create a title on bottom of a query output page
-footer part
Example

Select * from course

Reports
cid cname
10 sql
Confidential
1 row selected

Remove the title

>btitle off
>ttitle off

Set heading on
Set heading off

-The column or field title will display or not dispaly

Example

Select * from course

cid cname
10 sql
 1 row selected

& substitution variable

-When executed each time it will ask value for A

SQL> select &A+1 from dual;

Enter value for a: 4
old 1: select &A+1 from dual
new 1: select 4+1 from dual

4+1
----------
5

&& constant
-When executed next time it will not ask value for B ( constant)
-The value cant change

SQL> select &&B+1 from dual;

Enter value for b: 4
old 1: select &&B+1 from dual
new 1: select 4+1 from dual

4+1
----------
5

Define

>define B=10
-Used to change the constant value
- Now the value of B is 10

Undefine
>undefine B
-Used to remove the value of B
- Now it will ask value of B from user

Set verify on
Set verify off

>set verify on
-The highlighted part will display
>set verify off
-The highlighted part will not display

SQL> select &&B+1 from dual;

Enter value for b: 4
old 1: select &&B+1 from dual
new 1: select 4+1 from dual

4+1
----------
5
Show user

>show user;
User is ‘HR’
-Show the current user

Set timing on
Set timing off
>set timing on
-used to display time taken to execute a query in the bottom of
the page

Spool
-used to store query and output of query in a txt file

>spool d:\new\2.txt
>select * from course;
cid cname
10 sql

1 row selected
>spool off
-spool stop
 -Save and exit to write in a file
-Now check 2.txt file in d:\new

>select * from course;

cid cname
10 sql

1 row selected

Ed

-used to edit last executed query

>select * from course
>ed

-The last query open in note pad, then edit and save, quit
-After quit from notepad , the edited query will display in prompt

@
>@d:\new\1.txt

-used to call or execute a file contain query.

-need to mention path of the file

/
-used to execute last executed query.

Exit
quit

-Exit from database user.

Backup – Using Data Pump

Table Exports/Imports
The TABLES parameter is used to specify the tables that are to be exported. The following is an
example of the table export and import syntax.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp

logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp

logfile=impdpEMP_DEPT.log

For example output files see expdpEMP_DEPT.log and impdpEMP_DEPT.log.

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to
specify the schemas to be exported. The following is an example of the schema export and
import syntax.

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp

logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp

logfile=impdpSCOTT.log
 For example output files see expdpSCOTT.log and impdpSCOTT.log.

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an
example of the full database export and import syntax.

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp

logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp

logfile=impdpDB10G.log

For an example output file see expdpDB10G.log.

What is migration?

Migrate data from one database to another