Oracle SQL Fundamentals

Version1.2

The world’s largest global training provider

info@theknowledgeacademy.com

theknowledgeacademy.com
Oracle SQL Fundamentals

© 2018 The Knowledge Academy Ltd 1

About The Knowledge Academy

• World Class Training Solutions
• Subject Matter Experts
• Highest Quality Training Material
• Accelerated Learning Techniques
• Project, Programme, and Change
Management
• Bespoke Tailor Made Training Solutions
• PRINCE2®, MSP®, ITIL®, Soft Skills, and
More

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 2

1
Administration
• Trainer
• Fire Procedures
• Facilities
• Days/Times
• Breaks
• Special Needs
• Delegate ID Check
• Phones and Mobile Devices

© 2018 The Knowledge Academy Ltd 3

Outlines
• Session 1: Relational Database Concepts
• Session 2: Using SQL*Plus
• Session 3: Using SQL Developer
• Session 4: Retrieving Data with the
Select Statement
• Session 5: Aggregate Functions
• Session 6: Joining Tables
• Session 7: Numeric Character and Date
Functions
• Session 8: Conversion and
Miscellaneous

© 2018 The Knowledge Academy Ltd 4

2
Outlines
• Session 9: SQL Parameters
• Session 10: Using Subqueries
• Session 11: Managing Data
• Session 12: Managing Tables
• Session 13: Managing Indexes and
Views
• Session 14: Managing Sequences and
Synonyms
• Session 15: Retrieve Data Using
Subqueries
• Session 16: Hierarchical Queries

© 2018 The Knowledge Academy Ltd 5

Outlines
• Session 17: Enhanced Grouping Features
• Session 18: Manage Large Data Sets
• Session 19: Flashback Technology
• Session 20: Date, Time, And Time Zone
Support
• Session 21: Regular Expression Support
• Session 22: Manage Objects with the
Data Dictionary
• Session 23: Manage Schema Objects
• Session 24: Access Control

© 2018 The Knowledge Academy Ltd 6

3
Session 1: Relational Database Concepts

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 7

Basic Concepts
Databases

• A Database is a systematic collection of data that is stored in a centralised location

• Many types of databases exist such as Hierarchical, Network, Relational, and

Object‐Oriented

• Oracle is both a Relational as well as an Object‐Oriented Database

• A relational database consists of tables that are related to each other logically

• A table in a database consists of rows and columns

© 2018 The Knowledge Academy Ltd 8

4
Basic Concepts
Databases
• Each column in the table is referred to as a field and each row is known as a tuple or
a record

• The number of columns in a row is known as its degree

EmpId EName Salary

1001 Scott 7500
Table
2328 Michael 4545
Relational 7242 John 3455
Database
Table

© 2018 The Knowledge Academy Ltd 9

Basic Concepts
Databases

• Every row that exists in the table is unique and contains data about a separate
entity

• Each column in a table, also known as an attribute, has a unique name as well

• Every column in the table has its own data type and all entries for that column can
contain data of that type only

• Every cell (i.e. the intersection of a row and column) must contain atomic values

• No lists or sub‐tables are allowed

© 2018 The Knowledge Academy Ltd 10

5
Different Keys and Their Functions
Keys and Their Types

• A Table in a Relational Database can have a number of keys, as follows:

Keys in SQL

Primary Composite Candidate Alternate

Unique Key Foreign Key
Key Key Key Key

© 2018 The Knowledge Academy Ltd 11

Different Keys and Their Functions

Keys and Their Types

• Primary Key: a Primary Key is a column that is unique and cannot be left
blank. This key is used to identify a specific record or row. An example of a
Primary Key can be the EmployeeId in an Employee table

Primary Key

EmpId EName Salary

1001 Scott 7500
2328 Michael 4545
7242 John 3455

© 2018 The Knowledge Academy Ltd 12

6
Different Keys and Their Functions
Keys and Their Types

Syntax

Create Table <Table Name>

(Col1 DataType Primary Key,
Col2 DataType
)
Or
Create Table <Table Name>
(Col1 DataType,
Col2 DataType,
Primary Key (Column)
)

© 2018 The Knowledge Academy Ltd 13

Different Keys and Their Functions

Keys and Their Types

Example

Create Table Employee

(EmpId Number Primary Key,
EmpName Varchar2(20),
Salary Number
)
Or
Create Table Employee
(EmpId Number,
EmpName Varchar2(20),
Salary Number,
Primary Key (Empid)
)
© 2018 The Knowledge Academy Ltd 14

7
Different Keys and Their Functions
Keys and Their Types

• Unique Key: a Unique Key is a column that cannot be duplicated in the entire table
and can carry a value of NULL just once in the whole table
• Instead of a Primary Key, a Unique Key can also be used to identify a record
Unique Key

DeptId DeptName Location

10 IT London
Null Once
Production Buckingham
20 R&D Canterbury

© 2018 The Knowledge Academy Ltd 15

Different Keys and Their Functions

Keys and Their Types

Syntax

Create Table <Table Name>

(Col1 DataType Unique Key,
Col2 DataType
)
Or
Create Table <Table Name>
(Col1 DataType,
Col2 DataType,
Unique Key (Column)
)

© 2018 The Knowledge Academy Ltd 16

8
Different Keys and Their Functions
Keys and Their Types

Example

Create Table Employee

(EmpId Number Unique Key,
EmpName Varchar2(20),
Salary Number
)
Or
Create Table Employee
(EmpId Number,
EmpName Varchar2(20),
Salary Number,
Unique Key (Empid)
)

© 2018 The Knowledge Academy Ltd 17

Different Keys and Their Functions

Keys and Their Types

• Composite Key: a Composite Key is a Primary Key that is comprised of more than one
column or attribute such as StuClass and RollNo, together identifying a single student
of a class

StuClass RollNo StuName

7 10 Mike
See This
7 12 Adam
9 10 Charles

• In a Composite Key, it is mandatory that all the columns never match in order to
avoid ambiguity, as in the table above

© 2018 The Knowledge Academy Ltd 18

9
Different Keys and Their Functions
Keys and Their Types

Syntax Example

Create Table <Table Name> Create Table Student

(Col1 DataType, ( StuClass Number,
Col2 DataType, RollNo Number,
Col3 DataType, StuName Varchar2(20),
Primary Key (Col1,Col2) Primary Key
(StuClass,RollNo)

© 2018 The Knowledge Academy Ltd 19

Different Keys and Their Functions

Keys and Their Types

• Candidate Key: a single attribute or group of attributes in a table can

serve as a Candidate Key. A Candidate Key is a column that can act as a
Unique Key

• As an example, the SecurityID of an Employee could be the Candidate

Key in an Employee table. The Composite Key FirstName and LastName
in a Student Table could also serve as a Candidate Key

• A Composite Key can also be called an Alternate Key

© 2018 The Knowledge Academy Ltd 20

10
Different Keys and Their Functions
Keys and Their Types

• Foreign Key: a Foreign Key is the Primary Key of one table referenced in another
table, such as DeptNo being referenced in Employee table

Primary Key Primary Key Foreign Key

DeptNo DeptName Location EmpNo EmpName DeptNo

10 IT London 1001 Scott 10
20 R&D Canterbury 2328 Michael 20
30 Production Buckingham 7242 John 30
Department Table Employee Table

© 2018 The Knowledge Academy Ltd 21

Different Keys and Their Functions

Keys and Their Types

Syntax

Create Table <Table Name>

(Col1 DataType references
<mainTable>(ColumnName)
Col2 DataType,
Col3 DataType,

© 2018 The Knowledge Academy Ltd 22

11
Different Keys and Their Functions
Keys and Their Types

Example

Create Table Dept

(DeptId Number Primary Key,
DeptName Varchar2(20),
Location Varchar2(20)
)

Create Table Employee

(EmpId Number Primary Key,
EmpName Varchar2(20),
DeptNo Number References Dept(Deptid)
)

© 2018 The Knowledge Academy Ltd 23

Session 2: Using SQL*Plus

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 24

12
Overview of SQL*Plus
SQL*Plus – An Overview

• SQL*Plus is a querying tool that comes with every installation of the

Oracle database

• This tool is available in three interfaces: the Command Line Interface,

the Graphical Interface and the Web‐Based Interface

• SQL*Plus can be used for generating reports interactively or as batch

processes and sending the result to a text file, HTML, or to the screen

© 2018 The Knowledge Academy Ltd 25

Overview of SQL*Plus
SQL*Plus – An Overview

• SQL*Plus has an environment of its own, and users can query and
access the Oracle database using SQL*Plus statements

• The SQL*Plus tool in Oracle also provides its users with ways to create
and execute procedures and functions using the PL/SQL language

• For formatting the results obtained through the SQL query language,
SQL*Plus provides special commands

© 2018 The Knowledge Academy Ltd 26

13
Overview of SQL*Plus
SQL*Plus – An Overview

• Using SQL*Plus users, can perform the following tasks:

Query Tables and Format the Results Returned by the Query

Describe the Structure of Objects in the Database
Perform Administration Tasks
Create and Execute Batch Scripts

© 2018 The Knowledge Academy Ltd 27

Overview of SQL*Plus
Working with SQL*Plus

• In order to start working with SQL*Plus, the user needs to log in to the
database

• To do so, invoke the SQL*Plus application either by typing sqlplus at the

command prompt or by double‐clicking the application icon in your operating
system

• The user will be prompted to enter their username and password

• In case the user does not possess one, the most common one provided by
Oracle itself can be used, e.g. username: SCOTT and password: tiger

© 2018 The Knowledge Academy Ltd 28

14
Overview of SQL*Plus
Working with SQL*Plus

• The SCOTT user provides limited access to the users

• To get the access you need, you can consult the DBA (Database
Administrator)

• Once logged in, you are provided with the SQL> prompt as follows:

SQL>_
• From the SQL prompt you can now start issuing valid SQL commands to get
the results you desire

© 2018 The Knowledge Academy Ltd 29

Overview of SQL*Plus
Working with SQL*Plus

• The user is now ready to issue commands and queries to the database

• SQL*Plus commands can be divided into the following categories:

DDL DML DCL TCL

• Data • Data • Data Control • Transaction
Definition Manipulation Language Control
Language Language Language

© 2018 The Knowledge Academy Ltd 30

15
Overview of SQL*Plus
Working with SQL*Plus

• DDL: also known as the Data Definition Language, it contains statements that can be
used to Create, Modify, or Delete Object Structures

• Examples of DDL commands are:

Create • Used for Creating Objects

Alter • Used for Modifying the Object Structures

Drop • Used for Deleting the Object Structures

Truncate • Used for Marking a Table for Deallocation

© 2018 The Knowledge Academy Ltd 31

Overview of SQL*Plus
Working with SQL*Plus

• DML: the Data Manipulation Language consists of statements that include

Insert, Update, Delete, and Select

• Each of these statements has a different purpose to perform:

Insert Update Delete Select

• To Add • To Modify • To Remove • To Query

Records to Records in Records the Table
a Table a Table

© 2018 The Knowledge Academy Ltd 32

16
Overview of SQL*Plus
Working with SQL*Plus

• DCL stands for Data Control Language

• It consists of statement that are used to control access to some object – be it a table,
view, or even a schema

• The statements included in this set are GRANT and REVOKE

GRANT REVOKE

Used to give permissions

Takes the permissions back
to a user or a role on a
from the user/schema
specific object

© 2018 The Knowledge Academy Ltd 33

Overview of SQL*Plus
Working with SQL*Plus

• TCL: also known as Transaction Control Language, the set of statements that
fall under this group helps the users to treat one or more statements as a
single group

• If one of the statements fails, the remaining set of statements also fail unless
all the statements execute successfully

• The TCL statements help these statements to be treated as an atomic unit

• These statements include Committ, RollBack, and SavePoint

© 2018 The Knowledge Academy Ltd 34

17
Overview of SQL*Plus
Working with SQL*Plus

Committ Rollback SavePoint

The effect of Used to create

All data up to the
statements since markers so that
last transaction is
the last Committ to Rollback can be
saved to the
the last transaction performed to a
database
is cancelled specific point

© 2018 The Knowledge Academy Ltd 35

Session 3: Using SQL Developer

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 36

18
SQL Developer Concepts
Introducing the SQL Developer

• One of the many tools provided by Oracle is the SQL Developer

• SQL Developer can be used for performing operations such as Insert, Update,
and Delete using a graphical interface

• SQL Developer provides Form Fields such as Command Button, Check Box,
Option Button, Text Field etc. that can be laid out on a form to develop the
user interface

• SQL Developer cannot use SQL alone to perform all the required actions such
as validations

© 2018 The Knowledge Academy Ltd 37

SQL Developer Concepts

Introducing the SQL Developer

• SQL Developer uses the procedural language PL/SQL to make validations, fetch data
from the database, and display it on the user screen

• It also makes use of Event Driven programming through Triggers

• A Trigger is a piece of code that executes when some action takes place, such as
when a record gets inserted or deleted, or before a record is inserted, deleted, or
updated

• A user can specify in this piece of code when it will execute, which table will be
affected, and whether the code executes on satisfying some condition or not

© 2018 The Knowledge Academy Ltd 38

19
SQL Developer Concepts
Introducing the SQL Developer

• The developer can also change the properties of some form fields as and when
required

• This is done by bringing up the Properties Box and selecting the property that needs
to be changed

• SQL Developer also allows users to create forms using a Wizard

• The user just needs to answer some questions and the form is ready

• There are also different types of forms available in SQL Developer

© 2018 The Knowledge Academy Ltd 39

SQL Developer Concepts

Introducing the SQL Developer

• At times, a user may want to see all the records listed in the form of a table

• In this case, the user can build a Tabular Form to suit their needs

• In another scenario, the user might have to validate some data in one table against
the data in another table

• In this case, the user can resort to a Master‐Detail Form

• An example of this could be the DeptNo being referenced in the Employee table and
referenced from the Dept table

© 2018 The Knowledge Academy Ltd 40

20
SQL Developer Concepts
Introducing the SQL Developer

Example of Master Detail Form

Master: Dept

DeptNo 20 DeptName Location Canterbury

Detail: Employee

EmployeeID EmployeeName DeptNo Salary

2328 Michael 20 4545
4096 Sam 20 6734

© 2018 The Knowledge Academy Ltd 41

SQL Developer Concepts

Introducing the SQL Developer

• SQL Developer has two tabs: the Connection Navigator and the Report
Navigator

• The Connection Navigator allows the user to browse and access the
database objects from the schemas to which they have access

• The Reports Navigator, on the other hand, allows the developers to

either use the pre‐defined reports or develop new reports as per their
requirements

© 2018 The Knowledge Academy Ltd 42

21
SQL Developer Concepts
Some Other Components of SQL Developer

Data Modeller Data Miner

Unit Test Navigator
Browser Connections

• Provides access to • Allows users to • Provides a

all elements that browse and navigator for
make up a data review their Data browsing, creating
model design Mining and running unit
components tests

© 2018 The Knowledge Academy Ltd 43

SQL Developer Concepts

Some Other Components of SQL Developer

File Navigator DBA Navigator

• Allows users to browse the • Allows users to browse and

file system and open files review all aspects of their
directly in SQL Developer database

© 2018 The Knowledge Academy Ltd 44

22
Session 4:
Retrieving Data with the Select Statement

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 45

Using the Select Statement

The SQL Statement

• One of the most important statements used in an Oracle database is the Select
statement

• Generally, the Select statement is used to query tables and views to retrieve data,
but can also be used to create one table from another or insert data from one table
into another

• The Select statement is able to retrieve data individually or as a group

• This statement can also be used to query records and display them in an ascending or
descending order

© 2018 The Knowledge Academy Ltd 46

23
Using the Select Statement
The SQL Statement

Syntax

Select *, [<column list>] from <table name(s)>

[where <condition>]
[Group By <Column Names>] [Having <Condition>]
[Order By <Column List>]

© 2018 The Knowledge Academy Ltd 47

Using the Select Statement

The SQL Statement
• Some uses of the Select statement are listed in the form of examples here:

Select * from emp;

• The above statement selects all the fields and all the rows from the table emp

Select EmpNo, EmpName, salary from emp;

• The statement selects only the three specified fields but all rows from the table
emp

Select EmpNo, EmpName, salary from emp where salary > 7000;

• The above statement retrieves empno, empname, and salary of all those records
whose salary is greater than 7000
© 2018 The Knowledge Academy Ltd 48

24
Using the Select Statement
The SQL Statement
• Some more uses of the Select statement are listed in the form of examples here:

Select * from emp where salary >7000;

• The above statement retrieves the records of all the employees whose salary is
greater than 7000

Select * from emp where salary >7000 and salary < 8000;

• The above statement retrieves the records of all the employees whose salary is
greater than 7000 but less than 8000

Select * from emp where salary between 7000 and 8000;

• The above statement retrieves the records of all the employees whose salary is
lies between 7000 and 8000 and both 7000 and 8000 inclusive

© 2018 The Knowledge Academy Ltd 49

Using the Select Statement

The SQL Statement
• Some more uses of the Select statement are listed in the form of examples here:
Select DeptNo,Sum(salary) from emp Group By DeptNo;
• The above statement displays the total salary earned by each dept. Remember,
personal details can not be included with a Group By clause
Select DeptNo,Sum(salary) from emp Group By DeptNo Having Sum(salary) >
25000;
• The above statement displays the total salary earned by each dept if the total
salary is above 25000. This applies only with Group By
Select DeptNo,Sum(Salary) from emp where DeptNo <> 20 Group by DeptNo
Having Sum(Salary) > 25000;
• The above statement first omits DeptNo 20 from the list and the remaining
departments are grouped. Only those are displayed whose total salary > 25000

© 2018 The Knowledge Academy Ltd 50

25
Using the Select Statement
The SQL Statement
• Some more uses of the Select statement are listed in the form of examples here:

Select * from emp order by EmpName;

• The above statement displays all the records from the emp table sorted in
ascending order of empname

Select * from emp order by EmpName desc;

• The above statement displays all the records from the emp table sorted in
descending order of empname

Select * from emp order by salary desc EmpName asc;

• The above statement displays records with the highest salary at the top. Those
having the same salary are sorted according to ascending order of empname

© 2018 The Knowledge Academy Ltd 51

Using the Select Statement

The SQL Statement

• As SQL stands for Structured Query Language, it is important to note here that
the SQL statement can be used only in the order in which it has been presented

• It is not possible to use Order By before Group By or Having after Order By

• Also note that whenever Where, Group By, Having, or Order By are used then
the table name can not appear at the end of the Select statement

• The order of specifying the options must be strictly followed if those options
are being used

© 2018 The Knowledge Academy Ltd 52

26
Session 5: Aggregate Functions

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 53

Using the Aggregate Functions

Aggregate Functions
• The Select statement, as discussed earlier, can be used to retrieve and
display records from tables and views

• Besides just using Single Row functions, it can also use Aggregate
Functions

• Examples of the Aggregate Functions are the SUM functions we used

while discussing the Select statement

• This session will explain some more aggregate functions that can be
used along with the Select statement

© 2018 The Knowledge Academy Ltd 54

27
Using the Aggregate Functions
Aggregate Functions
• Aggregate Functions are used to compute some numeric results such as
counting how many employees earn a salary of above 10,000, or the average
salary of each department

Function Name Description

MIN Returns the smallest value in a given column
MAX Returns the largest value in a given column
SUM Returns the sum of the numeric values in a given column
AVG Returns the average value of a given column
COUNT Returns the total number of values in a given column
COUNT(*) Returns the number of rows in a table

© 2018 The Knowledge Academy Ltd 55

Using the Aggregate Functions

Aggregate Functions
• Aggregate Functions are always used in conjunction with the Group By clause:

Min Select DeptNo, Min(salary) from emp Group By DeptNo

Max Select DeptNo, Max(salary) from emp Group By DeptNo
Avg Select DeptNo, Avg(salary) from emp Group By DeptNo
Sum Select DeptNo, Sum(salary) from emp Group By DeptNo
Count Select DeptNo, Count(salary) from emp Group By DeptNo
Count(*) Select DeptNo, Count(*) from emp Group by DeptNo

© 2018 The Knowledge Academy Ltd 56

28
Session 6: Joining Tables

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 57

Joining Tables
Joins in SQL
• In SQL, Joins have various kinds and each one of them is used to retrieve data
differently. However, they all retrieve data from multiple tables

• Joins in SQL can be classified into:

INNER LEFT

OUTER RIGHT
JOINS
SELF FULL

CARTESIAN

© 2018 The Knowledge Academy Ltd 58

29
Joining Tables
Joins in SQL
• Inner Join: an Inner Join, also known as an Equi Join, is used to get records from more
than one table if the column value in one table matches a column’s value in another
table

• An example could be:

DeptNo DeptName Location

10 IT London The Dept Table
20 R&D Canterbury
30 Production Buckingham

© 2018 The Knowledge Academy Ltd 59

Joining Tables
Joins in SQL
• Inner Join: an Inner Join, also known as an Equi Join, is used to get records from more
than one table if the column value in one table matches a column’s value in another
table

• Another example could be:

EmpNo EmpName DeptNo

1002 John 10 The Employee Table
1137 Kevin 20
1187 Abraham 20

© 2018 The Knowledge Academy Ltd 60

30
Joining Tables
Joins in SQL

Select EmpName, DeptName from Employee

Inner Join Dept
On Employee.DeptNo = Dept.DeptNo

EmpName DeptName
John IT The Result
Kevin R&D
Abraham R&D

© 2018 The Knowledge Academy Ltd 61

Joining Tables
Joins in SQL
• The user can see from the result that there is no mention of Department Number 30
(Production) as there is no entry for said department in the Employee table

• Only the matching rows get a mention in the result set that is output to the user:

DeptNo DeptName Location

10 IT London
20 R&D Canterbury
30 Production Buckingham

© 2018 The Knowledge Academy Ltd 62

31
Joining Tables
Joins in SQL
• Outer Join: this join is utilised when the user needs to obtain all the records from one
table and only matching records from the second table

• There are three kinds of Outer Joins:

Left Outer Join Right Outer Join Full Outer Join

© 2018 The Knowledge Academy Ltd 63

Joining Tables
Joins in SQL
• Left Outer Join: while using this kind of a join, the user is able to
retrieve all records from the table on the left side of the ON and only
matching records from the right side of the ON

DeptNo DeptName Location EmpNo EmpName DeptNo

10 IT London 1002 John 10
20 R&D Canterbury 1137 Kevin 20
30 Production Buckingham 1187 Abraham 20

© 2018 The Knowledge Academy Ltd 64

32
Joining Tables
Joins in SQL
Select Dept.DeptNo, Dept.DeptName, Employee.EmpName
from Dept
LEFT OUTER JOIN Employee
ON
Dept.DeptNo = Employee.DeptNo

DeptNo DeptName EmpName

10 IT John See This
20 R&D Kevin
20 R&D Abraham
30 Production <NULL>

© 2018 The Knowledge Academy Ltd 65

Joining Tables
Joins in SQL
• Right Outer Join: while using this kind of a join, the user is able to
retrieve all the records from the table on the right side of the ON and
only matching records from the left side of the ON

DeptNo DeptName Location EmpNo EmpName DeptNo

10 IT London 1002 John 10
20 R&D Canterbury 1137 Kevin 20
30 Production Buckingham 1187 Abraham 20
1762 Betty 40

© 2018 The Knowledge Academy Ltd 66

33
Joining Tables
Joins in SQL
Select EmpNo, EmpName, Dept.DeptNo from Employee
RIGHT OUTER JOIN Dept
ON
Dept.DeptNo = Emp.DeptNo

EmpNo EmpName DeptNo

1002 John 10 See This
1137 Kevin 20
1187 Abraham 20
1762 Betty <NULL>

© 2018 The Knowledge Academy Ltd 67

Joining Tables
Joins in SQL
• Full Outer Join: this kind of join retrieves all records from both the
tables and outputs NULL where the records do not meet the specified
condition

DeptNo DeptName Location EmpNo EmpName DeptNo

10 IT London 1002 John 10
20 R&D Canterbury 1137 Kevin 20
30 Production Buckingham 1187 Abraham 20
1762 Betty 40

© 2018 The Knowledge Academy Ltd 68

34
Joining Tables
Joins in SQL
Select EmpNo, EmpName, Dept.DeptNo from Employee
FULL OUTER JOIN Dept
ON
Dept.DeptNo = Emp.DeptNo

EmpNo EmpName DeptNo

1002 John 10 See These
1137 Kevin 20
1187 Abraham 20
<NULL> <NULL> 30
1762 Betty <NULL>

© 2018 The Knowledge Academy Ltd 69

Joining Tables
Joins in SQL
• Self Join: a Self Join is an inner join in which the same table is treated
as two different tables

Employee as EMP Employee as MGR

EmpNo EmpName MgrNo EmpNo EmpName MgrNo

1002 John 1002 John
1137 Kevin 1002 1137 Kevin 1002
1187 Abraham 1002 1187 Abraham 1002
1762 Betty 1187 1762 Betty 1187

© 2018 The Knowledge Academy Ltd 70

35
Joining Tables
Joins in SQL
Select E.EmpNo, E.EmpName, M.EmpName as Manager from Employee E
INNER JOIN
Employee M
On
E.EmpNo = M.EmpNo

EmpNo EmpName Manager

1137 Kevin John
1187 Abraham John
1762 Betty Abraham

© 2018 The Knowledge Academy Ltd 71

Joining Tables
Joins in SQL
• Cartesian Join: a Cartesian Join, also known as a Cartesian Product or Cross Join, is a
join in which the result set is the product of the number of rows in Table 1 by the
number of rows in Table 2

• If an EMP has 10 records and Dept has 3 records, the result set would contain 10 x 3
records i.e. 30 records

• Generally, the use of such a join is for testing purposes

Select a.col1,a.col2,b.col1 from

Syntax
TableA a
of a
CROSS JOIN
Cross Join
TABLEB b

© 2018 The Knowledge Academy Ltd 72

36
Session 7: Numeric, Character,
and Date Functions

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 73

Numeric, Character, and Date Functions

Functions in SQL

• Functions in any language, package, or application tend to perform the task at

hand in a short period and without any hassles

• SQL supports various types of functions such as Numeric, Character, and Date

123 Numeric

ABC Character

mm/dd/yy Date

© 2018 The Knowledge Academy Ltd 74

37
Numeric, Character, and Date Functions
Functions in SQL

• Numeric Functions: functions that return numeric values fall under this category.
Some of the functions in this category are listed below, with an explanation for each

Function Description
ABS Calculates the absolute value of a given number, e.g. ABS(‐5) is 5
ACOS Returns the arc cosine of the given value
CEIL Return the next highest value for a given number
POS Returns the next lowest value for a given number
EXP Return the Exponent of a given number
POW Returns a number when it is raised to some power, e.g. POW(5,2) returns 25

© 2018 The Knowledge Academy Ltd 75

Numeric, Character, and Date Functions

Functions in SQL

• Character Functions: these functions return character values and are listed below

Function Description
CHR Returns the Character for a numeric value, e.g. CHR(65) returns ‘A’
CONCAT Returns a string after joining two or more
INITCAP Returns a string after converting the first letter of each word in the string to
a capital
ASCII Returns the ASCII value for a given character
LPAD Fills the empty spaces at the beginning of a string with a specific character
LTRIM Removes any leading spaces from a string

© 2018 The Knowledge Academy Ltd 76

38
Numeric, Character, and Date Functions
Functions in SQL

• Date Functions: these functions return character values and are listed below

Function Description
ADD_MONTHS Adds a specified Number of Months to a given date and returns the date
CURRENT_DATE Returns today’s date in the YYYY‐MM‐DD format
LAST_DAY Returns the Last Day of the month in the specified date
NEXT_DAY Returns the First Occurrence of the specified Day after the specified date
MONTHS_BETWEEN Returns the number of Months between two specified dates
DBTIMEZONE Returns the Database Time Zone
EXTRACT Returns a specified INTERVAL from a date time value

© 2018 The Knowledge Academy Ltd 77

Session 8: Conversions and Miscellaneous

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 78

39
Conversion Functions
Conversion Functions in SQL

• Conversion functions are used in SQL when a value in one data type
needs to be converted to another

• An example of such a conversion could be when the user needs to find

the Month Name of a date that is specified as a string

• These are single row functions capable of typecasting value, literal, or

an expression

© 2018 The Knowledge Academy Ltd 79

Conversion Functions
Conversion Functions in SQL

• These functions are:

Conversion Functions

TO_CHAR TO_NUMBER TO_DATE

© 2018 The Knowledge Academy Ltd 80

40
Conversion Functions
Conversion Functions in SQL

• TO_CHAR: this function is used when the user needs to convert a date type or a
numeric value to a character type value

Syntax

TO_CHAR(Value To Convert, Format)

Example

Select SYSDATE, TO_CHAR(SysDate, “MMMM”) from Dual;

Output

March

© 2018 The Knowledge Academy Ltd 81

Conversion Functions
Conversion Functions in SQL

• TO_NUMBER: this function is used when the user needs to convert a character type
value to a numeric type value
Syntax

TO_NUMBER(Value To Convert, Format)

Example

Select TO_NUMBER(‘938.49’,999D99) from Dual

Output

938.49

© 2018 The Knowledge Academy Ltd 82

41
Conversion Functions
Conversion Functions in SQL

• TO_DATE: this function is used when the user needs to format a character type value
as a date type value

Syntax

TO_DATE(ValueTo Convert, Format)

Example

Select TO_DATE(‘March 2 2018, 11:45 AM’,’DD‐MMMM‐YY’) from Dual

Output

02‐March‐2018

© 2018 The Knowledge Academy Ltd 83

Conversion Functions
Conversion Functions in SQL

• Some other conversions are listed below:

• ASCIISTR • DECOMPOSE
• BIN_TO_NUM • HEXTORAW
• CAST • NUMTODSINTERVAL
• CHARTOROWID • NUMTOYMINTERVAL
• COMPOSE • RAWTOHEX
• CONVERT • RAWTONHEX
• ROWIDTOCHAR

© 2018 The Knowledge Academy Ltd 84

42
Session 9: SQL Parameters

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 85

Parameter Declaration
A parameter declaration can take any of the following forms:

• Function Declaration & Definition

• Procedure Declaration & Definition

• CREATE FUNCTION Statement

• CREATE PROCEDURE Statement

© 2018 The Knowledge Academy Ltd 86

43
Keyword and Parameter Descriptions
Datatype

• Refers to the data type of the parameter

being declared

• This data type can not be constrained

IN, OUT, IN OUT

• These are the parameter modes

• They define the action of formal parameters

© 2018 The Knowledge Academy Ltd 87

Keyword and Parameter Descriptions

NOCOPY

• NOCOPY is used to induce the database to pass an argument quickly

• This clause improves the performance when passing a large value such as:

o A record

o An array to an OUT or IN OUT parameter

o An index‐by table

© 2018 The Knowledge Academy Ltd 88

44
Keyword and Parameter Descriptions
parameter_name

• This is the name of the formal

parameter declared, and one can
reference it in the body of the
subprogram

{ := | DEFAULT } expression

• Specifies a default value for an IN

parameter

© 2018 The Knowledge Academy Ltd 89

Session 10: Using Subqueries

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 90

45
Using Subqueries
Subqueries

• A query within another query can be called a Subquery

• Also known as an INNER or a NESTED query, it is embedded within the

WHERE clause of the main query

• A Subquery returns data to the main query so as to further limit the data that
is returned by the main query:

Select * from bonus where DeptNo in (Select distinct DeptNo from Emp)

© 2018 The Knowledge Academy Ltd 91

Using Subqueries
Subqueries

• The level of nesting of a subquery, as specified by Oracle, can be up to 255

• Whenever a column of a subquery has the same name as that of the column
in a main query, it should be prefixed by an alias name so as to make it
distinct

• When a subquery references a column from the table referenced in the main
query, Oracle goes on to treat it as a correlated subquery

• A subquery can be used to display results or insert the returned values into
an existing or a new table

© 2018 The Knowledge Academy Ltd 92

46
Using Subqueries
Subqueries

• Subqueries can make use of the mathematical operators as well as the

keywords IN, ANY, ALL etc.

• IN: this keyword is used when the main query wants to compare a
value with one of the values returned by the subquery

Select EmpName from emp where DeptNo IN (Select DeptNo from Dept)

© 2018 The Knowledge Academy Ltd 93

Using Subqueries
Subqueries

• ANY: can be used in place of IN, as ANY also selects a matching value
from the list, but must use the “=“, “<“,”>” operators for making
comparison
Select EmpName from emp where DeptNo =ANY(Select DeptNo from Dept)

• ALL: can be used to make a comparison with all the values instead of
just a single value

Select EmpName from emp where sal > ALL(Select sal from DeptSal)

© 2018 The Knowledge Academy Ltd 94

47
Using Subqueries
Subqueries
• A subquery needs to follow certain rules, as seen here:

Subqueries must be enclosed within parentheses

A subquery can not return more than one column if there is only one column in the
main query to compare with

Subqueries returning greater than one row must use IN, ANY, or ALL

An ORDER BY and a BETWEEN cannot be used in a subquery, although the main query
can use both of them

© 2018 The Knowledge Academy Ltd 95

Subqueries with the Select Statement

EmpNo EmpName Salary DeptNo

1008 Johnathan 8373 10
1009 Malcolm 9374 20 Employee Table
1010 Adam 8239 10
1011 Bertrand 9747 30

Empno DeptNo Bonus

1008 10 8373
Bonus Table
1009 20 9374
1010 10 8239

© 2018 The Knowledge Academy Ltd 96

48
Subqueries with the Select Statement
Subqueries

Select EmpName, Salary from Emp where Emp.DeptNo IN

(Select Distinct Bonus.DeptNo from Bonus)

EmpName Salary
Johnathan 8373
Malcolm 9374
Adam 8239

• The user can see here that Bertrand’s record is missing as his DeptNo is not present
in the Bonus table

© 2018 The Knowledge Academy Ltd 97

Subqueries with the Select Statement

Subqueries

• The Subquery, when used with the Select statement, has the following syntax:

SELECT column_name [, column_name ]

FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

© 2018 The Knowledge Academy Ltd 98

49
Subqueries with Insert and Update
Subqueries

• Besides using the subquery in a select statement, it can also be used with Insert, as
seen here:

Insert into <tablename> [column list] Select *[|column

list] from <Table1[,Table2…..Tablen]>
[where <condition>

Insert into emp Select * from bk_emp

• The above statement selects records from the bk_emp table and inserts them into
the emp table

© 2018 The Knowledge Academy Ltd 99

Subqueries with Insert and Update

Subqueries

• Besides using the subquery in a Select statement, it can also be used

with Update, as seen here:

Update <table name>

SET column_name = new_value
[Where OPERATOR [value]
(Select COLUMN_NAME from TABLE_NAME)
[WHERE <Condition>])

© 2018 The Knowledge Academy Ltd 100

50
Session 11: Managing Data

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 101

About Database Management

• Database management
includes monitoring,
administrating, and
maintaining a database and
its groups in the enterprise

• Enterprise manager is a
famous tool used for
managing the environment of
a database

© 2018 The Knowledge Academy Ltd 102

51
About Database Management
• Enterprise Manager enables us to:

o Manage Oracle Database with comprehensive and integrated

features

o Manage a single database or thousands of instances with

unparralled scalability

o Make full use of an intuitive management product leads the

business in ease of implementation and use

© 2018 The Knowledge Academy Ltd 103

About Database Management

Database Control versus Grid Control

• There are two consoles provided by Enterprise manager in order to

monitor a database:

Database Control Grid Control

© 2018 The Knowledge Academy Ltd 104

52
About Database Management
Database Control

• This control is the Enterprise Manager Web‐based application that is

used for Oracle Database Management

• It is available with every installation of Oracle Database

• With Database Control, you can administer and control a single

instance as well as clustered databases

© 2018 The Knowledge Academy Ltd 105

About Database Management

Grid Control

• This is the Enterprise Manager console, which is used to manage the

entire Oracle environment centrally

• The Targets tab is used to access multiple database targets with Grid
Control

• This tab is then followed by the Databases

© 2018 The Knowledge Academy Ltd 106

53
About Database Management
Database Home Page as the Management Hub

• The home page of the Enterprise Manager Database depicts status and
performance information regarding an instance of your single‐sourced
database, including:

o Fundamental database information

o Quick overview of database status

o CPU utilisation of the Oracle host

© 2018 The Knowledge Academy Ltd 107

About Database Management

Continued:

o The amount of time the instance consumed using CPU and I/O

o The reference collection response versus the current response of the SQL tracked
set

o Quick view of security of the database

o Recent backup time and its status

o Recommendations for enhanced performance and storage‐related issues

© 2018 The Knowledge Academy Ltd 108

54
Monitoring Databases
• Database monitoring allows you to identify the problem areas which
impact the performance of the database

• Once the problem area is identified, the next step is to tune the
database with Enterprise Manager administration capabilities

• The data from AWR (Automatic Workload Repository) is used in

Enterprise Manager for displaying performance information and
initiate database alerts

© 2018 The Knowledge Academy Ltd 109

Monitoring Databases
• Various real‐time performance charts are provided by the user
interface in order to manage the given targets

• Color‐coded charts display statistics for both aggregate and instance‐

specific performance

• You can click a legend link to display comprehensive information

regarding a page, useful for identifying and resolving the problem at its
source

© 2018 The Knowledge Academy Ltd 110

55
Monitoring Databases
It includes:

Assessing Database Diagnosing Problems Diagnosing Problems

Performance Effectively Automatically

Using Additional Using Other

Diagnostic Pages Performance Tools

© 2018 The Knowledge Academy Ltd 111

Administering Databases
• Oracle Enterprise Manager ensures that the Oracle database is running
effectively

• Enterprise Manager allows the database administrators to perform

day‐to‐day operations

• It provides a graphical user interface to manage the database storage

structure and schemas

• The Oracle database opens with the Oracle Database Home Page

© 2018 The Knowledge Academy Ltd 112

56
Administering Databases
• From here, the overview of database properties and performance can
be displayed

• However, the administration section of the page can be used to

perform the following administration tasks:

o Create and manage primary database storage structure

o Allocate the system storage requirements of the database

© 2018 The Knowledge Academy Ltd 113

Administering Databases
Continued

o Create and manage primary

objects like tables, views,
indexes, etc.

o Backup and restore the database

o Control user access for

maintaining system security

© 2018 The Knowledge Academy Ltd 114

57
Administering Databases
It includes:

• Storage Object Management

• Use of Database Configuration Features

• Managing Automatic Storage

• Conversion of Single Instances to Oracle

Real Application Clusters

• Converting to a Locally‐Managed
Tablespace

© 2018 The Knowledge Academy Ltd 115

Administering Databases
It includes:

• Controlling Resources with Resource

Manager

• Tracking Statistics for Improving Database

Performance

• Use of Oracle Scheduler

• Work With Database Schemas

• Manage Database Objects

© 2018 The Knowledge Academy Ltd 116

58
Administering Databases
It includes:

• Database Features of XML in Oracle

Enterprise Manager

• Users and Privileges Management

• Materialised Views Management

• Change Management

• Use of Oracle Enterprise Manager’s

Advisor

© 2018 The Knowledge Academy Ltd 117

Session 11: Managing Tables

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 118

59
Managing Tables
Guidelines for Table Management:
Following these guidelines can make management of tables a lot easier and help improve
performance when creating and updating tables or loading and querying table data:

1 2
• Before creating • Consider own options
tables, design them for creating the
first required type of table

3 4
• Specify every table • Consider parallelizing
location table creation

© 2018 The Knowledge Academy Ltd 119

Managing Tables
Continued

5 6 7
• Consider Using • Estimate size of • Limitations to
NOLOGGING when table and plan consider when
creating tables according to it creating tables

© 2018 The Knowledge Academy Ltd 120

60
Managing Tables
Creating Tables:
• The syntax of CREATE TABLE is as follows:

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
);

© 2018 The Knowledge Academy Ltd 121

Managing Tables
Example of Create Table:

• The CREATE TABLE statement is used for creating

the structure of a table CREATE TABLE Employees
(
EmployeeID int,
• Integrity constraints like the primary key, unique LastName varchar (200),
key, foreign key, etc. can be defined for the FirstName varchar (200),
columns during creation Address varchar (200)
);

• Integrity constraints can be defined at the level

of column creation or at table level

© 2018 The Knowledge Academy Ltd 122

61
Managing Tables
Creating a Temporary Table:

• The CREATE GLOBAL TEMPORARY TABLE statement is used for creating a temporary table

• To judge whether the data is transaction‐specific or session‐specific, used the ON

COMMIT clause

• These are useful for applications where it is necessary to buffer the result set because it
is created by executing multiple DML queries

• Itinerary data is confidential. At session end, optional itineraries are removed or

neglected

© 2018 The Knowledge Academy Ltd 123

Managing Tables
For creating a Transaction‐specific temporary table, the following statement
is used:

CREATE GLOBAL TEMPORARY TABLE

admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;

© 2018 The Knowledge Academy Ltd 124

62
Managing Tables
Loading Tables:
There are numerous methods for inserting or loading data into tables. The
most widely used methods are specified below:

Method Description
SQL* Loader This utility program of Oracle loads
data from external files into oracle
database tables
CREATE TABLE....AS SELECT Using SQL statement, a table can be
STATEMENT (CTAS) created and loaded with selected data
from another present table

© 2018 The Knowledge Academy Ltd 125

Managing Tables
Continued

Method Description
INSERT Statement INSERT Statement allows you to
add rows by specifying values of
column or specifying subquery that
select data from another table
MERGE Statement It allows to insert rows into or
update the table rows by selecting
another table rows

© 2018 The Knowledge Academy Ltd 126

63
Managing Tables
Automatically Collecting Statistics on Tables:

• The PL/SQL package DBMS STATS lets us generate and manage stats for
optimising on the basis of cost

• This package can be used to collect, modify, view, export, import, and delete
stats

• Using this package, the collected statistics can be named or identified

• You can enable DBMS_STATS to automatically collect table statistics by entering

the keyword MONITORING in the CREATE TABLE statement

© 2018 The Knowledge Academy Ltd 127

Managing Tables
Altering Tables:

• Modify the structure of the table using the ALTER TABLE statement

• The table must be in schema and you should have the privileges to ALTER ANY TABLE

• Using the ALTER TABLE statement, you can add, modify, or delete columns in the present
table

• It can also be used to drop or add various constraints on existing table

ALTER TABLE table_name

ADD column_name datatype;

© 2018 The Knowledge Academy Ltd 128

64
Managing Tables
Dropping/Truncating Tables:

• Using the DROP statement, the structure of the table can be removed

SQL> DROP TABLE <table name> [CASCADE CONSTRAINTS];

• To keep the table structure intact but remove all the rows and thus release
table space, a user can utilise the TRUNCATE statement

SQL> TRUNCATE TABLE employee;

© 2018 The Knowledge Academy Ltd 129

Managing Tables
The ALTER TABLE statement can be used in the following ways:

Moving a Table to a
Altering Physical Manually Allocating
New Segment or
Attributes of a Table Storage for a Table
Tablespace

© 2018 The Knowledge Academy Ltd 130

65
Managing Tables
Continued

Modifying an Existing
Adding Table Columns
Column Definition

Renaming Table Dropping Table

Columns Columns

© 2018 The Knowledge Academy Ltd 131

Managing Tables
Managing External Tables:

• Oracle includes the functionality to access data from external tables

• External tables are those which are not located in the database of an enterprise

• They can be in any format and an access driver is used to read them

• By giving metadata for explaining an external table, the data of the external table can be
used by a database like it is the data present in regular table of database

• By direct querying or in parallel by making use of SQL, external data can be queried

© 2018 The Knowledge Academy Ltd 132

66
Managing Tables
External tables support DDL statements. Only DDL statements discussed are
supported and not every clause of the standards are supported:

Creating External Altering External

Tables Tables

System and Object

Dropping External
Privileges for External
Tables
Tables

© 2018 The Knowledge Academy Ltd 133

Managing Tables
Creating External Tables:

• Using ORGANISATION
EXTERNAL, external tables
can be created

• You are not creating a table –

instead, metadata is created
in the data dictionary that
allows access to the data of
external table

© 2018 The Knowledge Academy Ltd 134

67
Managing Tables
Altering External Tables
The following clauses can be used to
change the table characteristics of
external tables:

• REJECT LIMIT

• PROJECT COLUMN

• DEFAULT DIRECTORY

• ACCESS PARAMETERS

• LOCATION
© 2018 The Knowledge Academy Ltd 135

Managing Tables
Continued

• PARALLEL

• ADD COLUMN

• MODIFY COLUMN

• DROP COLUMN

• RENAME TO

© 2018 The Knowledge Academy Ltd 136

68
Managing Tables
Dropping External Tables:

• The DROP TABLE statement is used for this, and it does not affect the actual data that is
located outside database

System and Object Privileges for External Tables:

• System and object privileges for external tables are subclasses of those for a regular table

Only the following system privileges can be applied to external tables:

• CREATE
• ALTER
• DROP
• SELECT

© 2018 The Knowledge Academy Ltd 137

Managing Tables
System and Object Privileges for
External Tables:

The following object privileges can

be applied to external tables:
• ALTER
• SELECT

Object privileges linked with the

directory are:
• READ
• WRITE
© 2018 The Knowledge Academy Ltd 138

69
Managing Tables
Viewing Information About Tables:
The following views allow you to
access information regarding tables:
• DBA_TABLES
• ALL_TABLES
• USER_TABLES

• DBA_TAB_COLUMNS
• ALL_TAB_COLUMNS
• USER_TAB_COLUMNS

© 2018 The Knowledge Academy Ltd 139

Managing Tables
Viewing Information About Tables:
The following views allow you to
access information regarding tables:
• DBA_ALL_TABLES
• ALL_ALL_TABLES
• USER_ALL_TABLES

• DBA_TAB_COMMENTS
• ALL_TAB_COMMENTS
• USER_TAB_COMMENTS

© 2018 The Knowledge Academy Ltd 140

70
Managing Tables
Viewing Information About Tables:
The following views allow you to
access information regarding tables:
• DBA_COL_COMMENTS
• ALL_COL_COMMENTS
• USER_COL_COMMENTS

• DBA_EXTERNAL_TABLES
• ALL_EXTERNAL_TABLES
• USER_EXTERNAL_TABLES and
many more views

© 2018 The Knowledge Academy Ltd 141

Session 13: Managing Indexes and Views

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 142

71
About Indexes
• Indexes are the structures that are not compulsory

• They are linked with tables that permit SQL queries to execute more speedily
against a table

• An Oracle Database index offers a faster access route to the table data

• Indexes can be used without redrafting any queries

• Indexes do not depend logically or physically on the data in the concerned

table

© 2018 The Knowledge Academy Ltd 143

Index Management
• Index Management is an essential part of database management and performance
tuning strategy

• Important necessities for an Index Management strategy are:

1. 2.
• Filtering out • Creating missing
unused indexes indexes

3. 4.
• Little user • Rebuilding used
intervention indexes

© 2018 The Knowledge Academy Ltd 144

72
About Views

• A logical depiction of some other table or combination is referred to as

a view

• The data of a view originates from the base tables – simply tables on
which the view is based

• Views are very useful as they enable us to tailor the demonstration of

data to various types of users

© 2018 The Knowledge Academy Ltd 145

Creating Views
In order to create a view, the following requirements should be fulfilled:

1. 2.
• To create a view in our schema, we • If the owner of the view intends to
must have the privilege CREATE give other users access to the view,
VIEW the owner must have received the
object privileges to the base
• For view creation in another user's objects with the GRANT OPTION or
schema, we must have the CREATE the system privileges with the
ANY VIEW system privilege ADMIN OPTION

© 2018 The Knowledge Academy Ltd 146

73
Creating Views
In order to create a view, the following requirements should be fulfilled:

3.
• The owner of the view, whether us or
another user, must have been
explicitly granted privileges to access
all objects referenced in the view
definition

© 2018 The Knowledge Academy Ltd 147

Creating Views
The following statement creates a view on a subset of data in the emp table:

CREATE VIEW sales_staff AS

SELECT empno, ename, DeptNo
FROM emp
WHERE DeptNo = 10
WITH CHECK OPTION CONSTRAINT
sales_staff_cnst;

• The query that defines the sales_staff view references only rows in department 10

• The CHECK OPTION creates the view with the constraint named sales_staff_cnst that
INSERT and UPDATE statements issued against the view cannot result in rows that
the view cannot select

© 2018 The Knowledge Academy Ltd 148

74
Session 14: Managing Sequences and
Synonyms

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 149

About Sequences
• Sequences are the database objects

• Multiple users can generate unique integers from sequences

• Sequential numbers are produced by the sequence generator. which

can be used to generate unique primary keys automatically and to
coordinate keys across multiple rows or tables

• If sequential values have to be produced without sequences, they can

only be produced programmatically

© 2018 The Knowledge Academy Ltd 150

75
Creating Sequences
• It is mandatory to have the CREATE SEQUENCE system privilege in order to create a
sequence in our Schema

• The CREATE ANY SEQUENCE privilege is required to create a sequence in some other
schema

• Example, the following statement creates a sequence to generate employee numbers

for the column empno of the emp table:

CREATE SEQUENCE emp_sequence

INCREMENT BY 1
START WITH 10
NOMAXVALUE
NOCYCLE
CACHE 10;

© 2018 The Knowledge Academy Ltd 151

Altering Sequences
In order to alter a sequence:

• A user needs the privilege of ALTER ANY SEQUENCE

• The schema should contain this sequence:

ALTER SEQUENCE emp_sequence

INCREMENT BY 5
MAXVALUE 1000
CYCLE
CACHE 10;

© 2018 The Knowledge Academy Ltd 152

76
Dropping Sequences
• A sequence in the schema can be dropped

• When a sequence is no longer required, it can be dropped using the

DROP SEQUENCE statement

• To do so, the DROP ANY SEQUENCE system privilege is required

DROP SEQUENCE order_seq;

© 2018 The Knowledge Academy Ltd 153

About Synonyms
• For any schema object, a synonym is an
alias

• Synonyms can offer a level of security by:

o Covering the object’s name and owner

o By offering location transparency for

remote objects of a distributed
database

• Synonyms can be public or private

© 2018 The Knowledge Academy Ltd 154

77
Creating Synonyms
• If we want to create a private synonym in our schema, a CREATE SYNONYM privilege
is needed

• The CREATE ANY SYNONYM privilege is required for creating the private synonym for
some other user’s schema

• The below statement creates a public synonym with name public_emp on the emp
table contained in the schema of pward:

CREATE PUBLIC SYNONYM public_emp

FOR pward.emp

© 2018 The Knowledge Academy Ltd 155

Dropping Synonyms
• Any private synonym in our own schema can be dropped

• In order to drop a private synonym in another user’s schema, one should

have the system privilege DROP ANY SYNONYM

• If a synonym is no longer required, it can be dropped using the DROP

SYNONYM statement

• The following statement drops the private synonym with the name emp:

DROP SYNONYM emp;

© 2018 The Knowledge Academy Ltd 156

78
Session 15: Retrieve Data Using
Subqueries

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 157

Types of Subqueries
1. Single Row Sub Query

• This subquery returns a single row output

• When used in WHERE conditions, they mark

the use of single row comparison operators

2. Multiple row sub query

• This subquery returns a multiple row output

• It uses multiple row comparison operators

such as IN, ANY, ALL

© 2018 The Knowledge Academy Ltd 158

79
Types of Subqueries
3. Correlated Sub Query

• These Subqueries are dependent upon data given by the outer query

• This type of subquery also carries Subqueries which uses EXISTS operator for
testing the occurrence of data rows thereby satisfying the specified criteria

The following operators perform relative value comparisons:

• = • >=
• <> • <
• > • <=

© 2018 The Knowledge Academy Ltd 159

IN and NOT IN Subqueries

• When a subquery returns multiple rows to be evaluated in comparison to
the outer query, the IN and NOT IN operators can be used

• They verify whether a comparison value is present in a set of values

• IN operator is applicable for rows in the outer query which matches any
row returned by the subquery

• NOT IN operator is applicable for rows in the outer query that match no
rows returned by the subquery

© 2018 The Knowledge Academy Ltd 160

80
Session 16: Hierarchical Queries

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 161

Hierarchical Queries
• A hierarchy is a parent‐child relationship within the same view or table

• A hierarchical query functions on data having a tree relationship

• The hierarchy can be traversed by both bottom‐up and top‐down

approaches

• The hierarchical query clause enables us to select rows in a hierarchical

order from a table that contains the hierarchical data

© 2018 The Knowledge Academy Ltd 162

81
Hierarchical Queries

START WITH Condition NOCYCLE

CONNECT BY Condition

Hierarchical Query Clause

© 2018 The Knowledge Academy Ltd 163

Hierarchical Queries
• START WITH ‐ Root rows of hierarchy are
depicted by START WITH

• CONNECT BY – The relationship among

child rows and parent rows of the
hierarchy is depicted by CONNECT BY

• NOCYCLE parameter ‐ Rows are returned

from a query on the instruction of
NOCYCLE parameter to Oracle Database

© 2018 The Knowledge Academy Ltd 164

82
Hierarchical Queries
• In order to refer to the parent row, one expression in a condition should
be fitted with the PRIOR operator in a hierarchical query

… PRIOR expr = expr

or
… expr = PRIOR expr

© 2018 The Knowledge Academy Ltd 165

Hierarchical Queries
• A condition requires the PRIOR operator if the CONNECT BY condition is compounded.
More than one PRIOR condition can exist

• For example:

CONNECT BY last_Name != ‘King’ AND PRIOR employee_Id =

manager_Id …
CONNECT BY PRIOR employee_Id = manager_Id and
PRIOR account_Mgr_Id = customer_Id …

• While comparing the column values with the equality operator, PRIOR is used

© 2018 The Knowledge Academy Ltd 166

83
Hierarchical Queries
Oracle processes hierarchical queries in the following way:

• At first a join is assessed (if present)

• Then, Oracle evaluates the CONNECT BY condition

• Finally, it evaluates the remaining WHERE clause predicates

The information from these evaluations is then used by Oracle for creating
the hierarchy via the steps mentioned in next slide

© 2018 The Knowledge Academy Ltd 167

Hierarchical Queries

1) Oracle selects the rows that satisfy the START WITH condition

2) Oracle then selects the rows that satisfy the CONNECT BY condition
with respect to one of the root rows

3) It selects the children of the rows obtained in step 2 and then selects
children of those children by evaluating the CONNECT BY condition
with respect to a present parent row

© 2018 The Knowledge Academy Ltd 168

84
Hierarchical Queries
4) If the query contains a WHERE
clause without a join, Oracle
eliminates all those rows from
the hierarchy that do not
satisfy the WHERE clause
condition

5) Oracle returns the rows in the

order where children appear
below their parents

© 2018 The Knowledge Academy Ltd 169

Hierarchical Queries

2 9
7

3 4 10 12
8

11
5 6

Hierarchical Queries

© 2018 The Knowledge Academy Ltd 170

85
Hierarchical Queries
• For the parent row, the PRIOR expression of the CONNECT BY condition, and for every
row in the table, the other expressions are evaluated by Oracle in order to find the
children of a parent row

• The rows are a parent’s children if the condition is true for them

• Rows selected by the query can be filtered further if the CONNECT BY condition has
other conditions

• There cannot be a subquery in a CONNECT BY condition

• Oracle returns an error if the CONNECT BY condition results in a loop; that is, if one
row is both the parent and a child of another row in the hierarchy

© 2018 The Knowledge Academy Ltd 171

Hierarchical Queries
• GROUP BY or ORDER BY destroy
the hierarchical order of the
CONNECT BY results, so it is
recommended not to specify
them in a hierarchical query

• The ORDER SIBLINGS BY clause

can be used for the purpose of
ordering sibling rows of the
same parent

© 2018 The Knowledge Academy Ltd 172

86
Hierarchical Queries
Example for CONNECT BY:

SELECT employee_Id, last_Name, manager_Id

FROM employee
CONNECT BY PRIOR employee_Id = manager_Id;

EMPLOYEE_ID LAST_NAME MANAGER_ID

220 Watson 102

221 Holmes 104

© 2018 The Knowledge Academy Ltd 173

Hierarchical Queries
Example for LEVEL:

SELECT employee_Id, last_Name, manager_Id, LEVEL

FROM employee
CONNECT BY PRIOR employee_Id = manager_Id;

EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL

220 Watson 102 1

221 Holmes 104 2

© 2018 The Knowledge Academy Ltd 174

87
Hierarchical Queries
Example for START WITH:

SELECT last_Name, employee_Id, manager_Id, LEVEL

FROM employee
START WITH employee_Id = 102
CONNECT BY PRIOR employee_Id = manager_Id
ORDER SIBLINGS BY last_Name;

LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL

Lorentz 102 1

Austin 108 108 2

© 2018 The Knowledge Academy Ltd 175

Session 17: Enhanced Grouping Features

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 176

88
Enhanced Grouping Features
Grouping

• GROUPING differentiates regular grouped rows and super aggregate rows

• ROLLUP and CUBE are two of the GROUP BY extensions which generate super
aggregate rows in which null depicts the set of values

• The GROUPING function is used to differentiate a null in a regular row and a null
showing the set of all values in a super aggregate row

GROUPING ( expr )

© 2018 The Knowledge Academy Ltd 177

Enhanced Grouping Features

Aggregate Query with GROUPING SETS Equivalent Aggregate Query with GROUP BY

SELECT a, b, SUM(c) FROM tab1 GROUP BY a, SELECT a, b, SUM(c) FROM tab1 GROUP BY a,
b GROUPING SETS ( ( a, b) ) b
SELECT a, b, SUM( c ) FROM tab1 GROUP BY SELECT a, b, SUM( c ) FROM tab1 GROUP BY
a, b GROUPING SETS ( (a, b), a) a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP
BY a
SELECT a, b, SUM( c ) FROM tab1 GROUP BY SELECT a, null, SUM( c ) FROM tab1 GROUP
a, b GROUPING SETS ( a, b) BY a
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP
BY b

© 2018 The Knowledge Academy Ltd 178

89
Enhanced Grouping Features
Aggregate Query with GROUPING SETS Equivalent Aggregate Query with GROUP BY

SELECT a, b, SUM( c ) FROM tab1 GROUP BY SELECT a, b, SUM( c ) FROM tab1 GROUP BY
a, b GROUPING SETS ( (a, b), a, b, ( ) ) a, b
UNION
SELECT a, null, SUM( c ) FROM tab1 GROUP
BY a, null
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP
BY null, b
UNION
SELECT null, null, SUM( c ) FROM tab1

© 2018 The Knowledge Academy Ltd 179

Enhanced Grouping Features

For Example:

Column1 (key) Column2 (value)

1 NULL
1 1
2 2
3 3
3 NULL
4 5

© 2018 The Knowledge Academy Ltd 180

90
Enhanced Grouping Features
SELECT key, value, GROUPING_ID, count(*)
FROM T1
GROUP BY key, value WITH ROLLUP;

The results of the query above are shown in the table in the next slide.
In the table you will observe the following points:

• You will see that the column GROUPING_ID is a bitvector of selected columns

• Both the columns are selected for the second row, which explains the value 0

• Only the first column is selected for the third row; this explains the value 1

© 2018 The Knowledge Academy Ltd 181

Enhanced Grouping Features

Column1 (key) Column2 (value) GROUPING_ID Count(*)
NULL NULL 3 6
1 NULL 0 2
1 NULL 1 1
1 1 0 1
2 NULL 1 1
2 2 0 1
3 NULL 0 2
3 NULL 1 1
3 3 0 1
4 NULL 1 1
4 5 0 1

© 2018 The Knowledge Academy Ltd 182

91
Enhanced Grouping Features
Grouping Functions

• This function displays whether, for

a given row, an expression in a
GROUP BY clause is aggregated or
not

• If a column is a part of the

grouping set, then it is
represented by value “0”, or else
by value “1”

© 2018 The Knowledge Academy Ltd 183

Enhanced Grouping Features

Grouping Functions

Consider the following query:

SELECT key, value, GROUPING_ID, grouping (key, value), grouping

(value, key), grouping (key), grouping (value), count (*)
FROM T1
GROUP BY key, vale WITH ROLLUP;

The results of this query are shown on the next slide.

© 2018 The Knowledge Academy Ltd 184

92
Enhanced Grouping Features
COLUMN COLUMN GROUPING_ID Grouping Grouping Grouping Grouping count(*)
1 (key) 2(value) (key,value) (value,key) (key) (value)

NULL NULL 3 3 3 1 1 6
1 NULL 0 0 0 0 0 2
1 NULL 1 1 2 0 1 1
1 1 0 0 0 0 0 1
2 NULL 1 1 2 0 1 1
2 2 0 0 0 0 0 1
3 NULL 0 0 0 0 0 2
3 NULL 1 1 2 0 1 1
3 3 0 0 0 0 0 1
4 NULL 1 1 2 0 1 1
4 5 0 0 0 0 0 1

© 2018 The Knowledge Academy Ltd 185

Enhanced Grouping Features

Cubes and Rollups

• General syntax: WITH CUBE/ROLLUP

• Used with GROUP BY only

GROUP BY a, b, c WITH CUBE is equivalent to

GROUP BY a, b, c GROUPING SETS (
(a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )
)

© 2018 The Knowledge Academy Ltd 186

93
Enhanced Grouping Features
Continued

• ROLLUP calculates the aggregate at the dimensional hierarchy levels

• ROLLUP is used with GROUPBY
• GROUP BY a, b, c with ROLLUP thinks that hierarchy is as follows:
o “a” drilling down to “b” drilling down to “c”

GROUP BY a, b, c, WITH ROLLUP is equivalent to GROUP BY a, b, c

GROUPINGSETS (
(a, b, c), (a, b), (a), ( )
)

© 2018 The Knowledge Academy Ltd 187

Session 18: Manage Large Data Sets

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 188

94
Managing Very Large Databases

• Mission‐critical‐databases are frequently run by modern enterprises.

They contains several gigabytes and often terabytes of data

• In order to meet the support and maintenance requirements of large

databases, we must find some alternatives

• One such alternative is Partitioning, which not only maintain very large
databases but also save query time in complex databases

© 2018 The Knowledge Academy Ltd 189

Managing Very Large Databases

Sales Sales Sales

JAN

JAN

FEB
FEB

EMEA APAC

© 2018 The Knowledge Academy Ltd 190

95
Managing Very Large Databases
• Data Manipulation Language and SQL queries can access partitioned tables without
being modified

• Data Definition Language can access and manipulate partitions, after partitions are
defined

• Each partition of a index or table can have different physical attributes but each
partition must have the same logical attributes

• Partitioning is widely used for applications that manage large data. Data Warehousing
systems advantage from manageability and performance while Online Transaction
Processing systems advantage from improvements in availability and manageability

© 2018 The Knowledge Academy Ltd 191

Managing Very Large Databases

Benefits of Partitioning

PARALLELISM AVAILABILITY

Partition
PERFORMANCE MANAGEABILITY
Benefits

© 2018 The Knowledge Academy Ltd 192

96
Managing Very Large Databases
Advantages of Partitioning

• Partitioning enables various data management operations, including

creating and rebuilding indexes, data loads, recovery, and backup at the
partition level to reduce the time needed for management operations

• By accessing a subset of partitions, the results of a query can be

obtained and improve the performance of the query

• Maintain operations by reducing the impact of scheduled downtime

© 2018 The Knowledge Academy Ltd 193

Managing Very Large Databases

Advantages of Partitioning (continued)

• If critical indexes and tables are disintegrated into partitions to reduce

recovery times, maintenance windows, and impact of failures, there will
be an increase in the availability of mission‐critical databases

• Parallel execution against partitioned objects is important for scalability

in a clustered environment

© 2018 The Knowledge Academy Ltd 194

97
Managing Very Large Databases
Partitioning and Very Large Databases

• The following factors are responsible for database growth:

o For enabling cross‐departmental analysis, companies merge the

systems by reducing system maintenance costs. Amalgamation of
applications and databases is a prime factor in the growth of
database size

o Organisations progress organically and by mergers and acquisitions,

which cause the extent of produced and processed data to rise

© 2018 The Knowledge Academy Ltd 195

Managing Very Large Databases

Partitioning and Very Large Databases
(continued)

o Several companies experience

regulations for storing data for a
minimum amount of time which
results in more data being stored
for longer periods of time

• Partitioning reports growth is a basic

challenge for very large databases

© 2018 The Knowledge Academy Ltd 196

98
Managing Very Large Databases
• This feature helps to manage indexes
and tables by enabling a divide and
rule technique

• Partitioning helps the database in

scaling large data sets and
maintaining consistency in its
performance. This also saves on any
extra costs that may be incurred on
hardware resources

© 2018 The Knowledge Academy Ltd 197

Session 19: Flashback Technology

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 198

99
Overview of Oracle Flashback Technology
• Oracle Flashback Technology is a collection
of Oracle Database features that enable us
to view a database object’s past states

• One can do the following with the help of

flashback features:

o Execute queries that return previous

data

o Execute queries that return metadata,

showing a comprehensive history of
changes made to the database
© 2018 The Knowledge Academy Ltd 199

Overview of Oracle Flashback Technology

Continued

o Record and archive

transactional data changes
automatically

o Restore tables/rows to a prior

point in time

o When the database remains

online, rollback any transaction
and its dependent transactions

© 2018 The Knowledge Academy Ltd 200

100
Using Flashback Features in Application
Development
The following flashback features can be used in application development in
order to report historical data:

Oracle Flashback Oracle Flashback Flashback

Query Version Query Transaction Query

© 2018 The Knowledge Academy Ltd 201

Using Flashback Features in Application

Development
Continued

DBMS_FLASHBACK Flashback Flashback Data

Package Transaction Archive

© 2018 The Knowledge Academy Ltd 202

101
Using Flashback Features in Database
Administration
The following flashback features are used for
data recovery.

Only a Database Administrator can make use

of these features:

• Oracle Flashback Table

• Oracle Flashback Drop

• Oracle Flashback Database

© 2018 The Knowledge Academy Ltd 203

Configuration of Database for Flashback

Technology
Below are the configuration tasks that needs to be performed before using flashback
features in the application:

• Configuring the Database for Automatic Undo Management

• Configuring the Database for Oracle Flashback Transaction Query

• Configuring the Database for Flashback Transaction

• Allowing Oracle Flashback Operations on Specific LOB Columns

• Granting Necessary Privileges

© 2018 The Knowledge Academy Ltd 204

102
Session 20: Date, Time, and Time Zone
Support

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 205

Introduction to Time Zone Support

• Business transactions are conducted across different time zones

• Datetime, interval data types, and Oracle’s time zone support allow users to store
reliable information associated with the events and transactions time

Datetime and Interval Datatypes

• The datetime datatypes are:

o DATE o TIMESTAMP WITH TIME

ZONE
o TIMESTAMP
o TIMESTAMP WITH LOCAL
TIME ZONE

© 2018 The Knowledge Academy Ltd 206

103
Introduction to Time Zone Support
• Below are the interval datatypes:

o INTERVAL YEAR TO MONTH

o INTERVAL DAY TO SECOND

• Datetimes and intervals are composed of fields. These fields in Oracle are:

YEAR MONTH DAY HOUR

MINUTE SECOND

© 2018 The Knowledge Academy Ltd 207

Introduction to Time Zone Support

TIMESTAMP WITH TIMEZONE includes the following fields:

TIMEZONE_HOUR TIMEZONE_MINUTE

TIMEZONE_REGION TIMEZONE_ABBR

© 2018 The Knowledge Academy Ltd 208

104
Introduction to Time Zone Support
Datetime Datatypes

1. DATE Datatype

• Stores information about date and time

• For every DATE value, Oracle stores the

following information:

o Century
o Year
o Month
o Date
o Hour
o Minute
o Second
© 2018 The Knowledge Academy Ltd 209

Introduction to Time Zone Support

Continued

• A date value can be specified by:

o Mentioning the date value as a

literal

o Transforming a
character/numeric value to a
date value with the TO_DATE
function

© 2018 The Knowledge Academy Ltd 210

105
Introduction to Time Zone Support
2. TIMESTAMP Datatype

• This is the DATE datatype extension

• This datatype stores year, month, day,

hour, minute, and second values

• In the TIMESTAMP datatype, fractional

seconds are also stored which are not
stored by the DATE datatype

TIMESTAMP
[(fractional_seconds_precision)]

© 2018 The Knowledge Academy Ltd 211

Introduction to Time Zone Support

3. TIMESTAMP WITH TIME ZONE
Datatype

TIMESTAMP WITH TIME ZONE is an option

of TIMESTAMP that comprises a time zone
offset or time zone region name in its value

Syntax:

TIMESTAMP
[(fractional_seconds_precision)] WITH
TIME ZONE

© 2018 The Knowledge Academy Ltd 212

106
Introduction to Time Zone Support
4. TIMESTAMP WITH LOCAL TIME ZONE
Datatype

• TIMESTAMP WITH LOCAL TIME ZONE is

another variant of TIMESTAMP

Syntax

TIMESTAMP [(fractional_seconds_precision)]
WITH LOCAL TIME ZONE

fractional_seconds_precision is non‐compulsory
and specifies the number of digits in the
fractional part of the SECOND datetime field.

© 2018 The Knowledge Academy Ltd 213

Introduction to Time Zone Support

Interval Datatypes

• Time durations should be stored in Interval

datatypes

• Mainly, they are used with analytic

functions

1. INTERVAL YEAR TO MONTH

• It stores a time period with the help of

YEAR and MONTH datetime fields

INTERVAL YEAR [(year_precision)] TO MONTH

© 2018 The Knowledge Academy Ltd 214

107
Introduction to Time Zone Support

Continued

2. INTERVAL DAY TO SECOND Datatype

• It stores a period of time in terms of

days, hours, minutes and seconds

INTERVAL DAY [(day_precision)] TO

SECOND [(fractional_seconds_precision)]

© 2018 The Knowledge Academy Ltd 215

Introduction to Time Zone Support

Continued

3. Inserting Values into Interval Datatypes

• Values can be inserted into an interval

column as follows

o Interval can be inserted as a literal

o SQL functions NUMTODSINTERVAL,

NUMTOYMINTERVAL,
TO_DSINTERVAL, and
TO_YMINTERVAL can be used

© 2018 The Knowledge Academy Ltd 216

108
Session 21: Regular Expression Support

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 217

Introduction to Regular Expressions

• Regular expressions allow us to for
patterns in string data by making use
of standardised syntax conventions

• A regular expression is specified with

the help of following characters:

o Metacharacters

o Literals

© 2018 The Knowledge Academy Ltd 218

109
Implementation of Oracle Database Regular
Expressions
• Regular Expression support is implemented
by an Oracle Database with a set of Oracle
Database SQL functions and conditions that
enable us to hunt and manipulate string
data

• These functions can be used on a text

literal, bind variable, and any column that
grips character data, such as:

o VARCHAR2
o CLOB
o CHAR
o NCLOB
© 2018 The Knowledge Academy Ltd 219

Implementation of Oracle Database Regular

Expressions
SQL Regular Expression Functions and Conditions
SQL Element Category Description

REGEXP_LIKE Condition • Looks in a character column for a

pattern
• Returns rows matching a regular
expression, make use of this function
in the WHERE clause of a query

Example: the following clause filters employees with the last name Steven or Stephen

WHERE REGEXP_LIKE(last_name, '^Ste(v|ph)en$')

© 2018 The Knowledge Academy Ltd 220

110
Implementation of Oracle Database Regular
Expressions
SQL Regular Expression Functions and Conditions
SQL Element Category Description

REGEXP_REPLACE Function • Looks for a pattern in a character

column and substitutes every
incidence of that pattern with the
mentioned string

This function puts a space after each character in the state_name column:

REGEXP_REPLACE(state_name, '(.)', '\1 ')

© 2018 The Knowledge Academy Ltd 221

Implementation of Oracle Database Regular

Expressions
SQL Regular Expression Functions and Conditions
SQL Element Category Description

REGEXP_INSTR Function • Looks for a given occurrence of a

regular expression pattern and
returns an integer that shows the
position in the string where the match
is found

© 2018 The Knowledge Academy Ltd 222

111
Implementation of Oracle Database Regular
Expressions
SQL Regular Expression Functions and Conditions
SQL Element Category Description

REGEXP_SUBSTR Function • Returns the substring that matches

the specified regular expression
pattern

© 2018 The Knowledge Academy Ltd 223

Session 22: Manage Objects with the

Data Dictionary

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 224

112
Introduction to Data Dictionary
• A Data Dictionary is a read‐only table set

• It provides administrative metadata about the database

• A Data Dictionary is comprised of the following:

o Description of Schema objects of the database

o Space allotted/used by schema objects

o Oracle user names

o Default values for columns

© 2018 The Knowledge Academy Ltd 225

Introduction to Data Dictionary

Continued

o Auditing information

o Integrity constraint information

o Privileges and roles granted to each

user

o General Database information

© 2018 The Knowledge Academy Ltd 226

113
Structure of Data Dictionary
A Data Dictionary consists of the following:

1. Base Tables

• These are the fundamental tables that store

information about the related database

• These tables should be written and read by

Oracle

• Users infrequently access them directly as

they are normalised, and data is mostly
stored in an encrypted format
© 2018 The Knowledge Academy Ltd 227

Structure of Data Dictionary

Continued

2. User‐Accessible Views

• These are the views that sum up and show the

information stored in the base tables of the data
dictionary

• The base table data is decoded into valuable

information, such as user or table names, using
joins and WHERE clauses to interpret the
information

• Most users are granted access to the views instead

of the base tables

© 2018 The Knowledge Academy Ltd 228

114
Structure of Data Dictionary
3. SYS, Owner of the Data
Dictionary

• SYS, the Oracle user, is the owner

of all base tables & user‐accessible
views of the data dictionary

• The rows or schema objects

contained in the SYS schema
should never be changed as such
action can compromise data
integrity
© 2018 The Knowledge Academy Ltd 229

How the Data Dictionary Is Used

The primary uses of a Data Dictionary are:

• A Data Dictionary is accessed by Oracle to find information about

storage structures, users, and schema objects

• Each time a data definition language (DDL) statement is issued, Oracle

modifies the data dictionary

• Oracle users can make use of a data dictionary as a read only reference
of information about the database

© 2018 The Knowledge Academy Ltd 230

115
How the Data Dictionary Is Used
Data Dictionary View Prefixes

Prefix Scope
USER User's view
ALL Expanded user's view
DBA Database administrator's view

© 2018 The Knowledge Academy Ltd 231

Session 23: Manage Schema Objects

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 232

116
Introduction to Schema Objects

• A schema is a pool of database objects

• A schema is possessed by a database user and shares the same name

as the user

• These are the logical structures created by users

• Some objects, such as tables or indexes, hold data while other objects,
such as views or synonyms, consist only of a definition

© 2018 The Knowledge Academy Ltd 233

Creating Multiple Tables and Views in a

Single Operation
• A number of tables and views can be created and privileges can be
granted with only one operation using the statement CREATE SCHEMA

• This statement is useful if we want to guarantee the creation of a

number of table views and grants in a single operation

• If a view or grant fails in an individual table, the whole statement is

rolled back

• No object gets created and no privilege is granted

© 2018 The Knowledge Academy Ltd 234

117
Analysing Tables, Indexes, and Clusters
• A schema object is analysed in order to:

o Collect and manage its statistics

o Confirm the validity of its storage

format

o Recognise migrated and chained rows

of a table/cluster

© 2018 The Knowledge Academy Ltd 235

Schema Object Management Privileges

• A power to execute a specific type of SQL statement or to access another user's
object is called a privilege

• Privileges are granted to users so that they can fulfil tasks required for their job

• Privileges can be given to users in the following ways:

o Privileges can be granted to users explicitly

o Privileges can be granted to a role and then that role is granted to one/more
users

© 2018 The Knowledge Academy Ltd 236

118
Schema Object Management Privileges
A schema object privilege is a right to perform a
particular action on a particular schema object:

• Sequence
• Function
• Package
• View
• Table
• Procedure

For different types of schema objects, different

object privileges are available.

© 2018 The Knowledge Academy Ltd 237

Schema Object Management Privileges

Grant and Revoke Schema Object Privileges

• Privileges can be granted to users and roles, and can also be revoked

• Privileges can be granted/revoked by making use of the following:

o The SQL statements GRANT and REVOKE

o By adding privilege to Role/User dialog box

© 2018 The Knowledge Academy Ltd 238

119
Session 24: Access Control

Project Management, Business

Architecture & Technical & Business & Web, Marketing, Legal, Finance, & Workplace
Management, Computer Software
Systems Programming Corporate PR, & Social Accounting Requirements
& Leadership Design

© 2018 The Knowledge Academy Ltd 239

Access Control

• Access‐control within a database is significant for the security of data

• Access control adheres to the principle that application users must

have privileges to view, alter, or remove only the data in the live
database that is appropriate to their profession

• Developing a secure database is relevantly easy for Database

Administrators (DBAs) and developers if they use an account that
provides enough privileges for the role they are opting for

© 2018 The Knowledge Academy Ltd 240

120
Access Control
Access Mediation
Users

A user should have the authorisations based on

the labels that are defined for the policy in
order to access protected data

Relationships between Users,

Data & Labels Labels Data Sensitivity Data

© 2018 The Knowledge Academy Ltd 241

Access Control
Understanding User Authentications

Two types of authorisations are:

• Authorizations Set by the Administrator

• Computed Session Labels

1) Authorisations Set by the Administrator:

• Authorized Levels

• Authorized Compartments

• Authorized Groups

© 2018 The Knowledge Academy Ltd 242

121
Access Control
Continued

2) Computed Session Labels

Computed Label Definition

Maximum Read Label The maximum level of a user along with
any combination of groups and
compartments for which the user is
authorised
Minimum Write Label The user's minimum level
Maximum Write Label The maximum level of a user along with
any combination of groups and
compartments for which the user is
granted write access

© 2018 The Knowledge Academy Ltd 243

Access Control
Continued

2) Computed Session Labels

Computed Label Definition

Default Read Label The single default level along with any
compartments and groups designated as
default for any user
Default Write Label A subset of the default read label
Default Row Label The blend of components between the
user's minimum write label and the
maximum write label

© 2018 The Knowledge Academy Ltd 244

122
Access Control
Securable, Principals, and Permissions

There are three different levels in the

Principals hierarchy

• Windows level

• Server level

• Database level

© 2018 The Knowledge Academy Ltd 245

Access Control
Securable, Principals, and Permissions

1. Windows level

• Windows domain, or local logins: if users are windows

domain group logins, then all members of that group
get the permissions that are assigned to the login

2. Server level

• SQL Server userid, password logins, or server roles

3. Database level

• Either users or roles

© 2018 The Knowledge Academy Ltd 246

123
Congratulations

Congratulations on completing this course!

Keep in touch
info@theknowledgeacademy.com
Thank you

© 2018 The Knowledge Academy Ltd 247

124
/The.Knowledge.Academy.Ltd /TKA_Training /the-knowledge-academy

/+TheKnowledgeAcademyWinkfield /TheKnowledgeAcademy

The world’s largest global training provider

info@theknowledgeacademy.com

theknowledgeacademy.com

To the best of our knowledge, the information contained herein is accurate and reliable as of the date of publication; however, we do not assume any liability whatsoever for the accuracy and completeness of the

above information.