D80190GC11 Les01

Unauthorized reproduction or distribution prohibited Copyright 2014, Oracle and/or its affiliates
BS Grupo SAC
Introduction
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
Copyright 2014,JOracle
aitssaffiliates.
uAllidrights reserved.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
s
(de
c
e
r
pa
se
u
to
BS Grupo SAC
Lesson Objectives
After completing this lesson, you should be able to do the
following:
Define the goals of the course
List the features of Oracle Database 12c
Describe the salient features of Oracle Cloud
Discuss the theoretical and physical aspects of a relational
database
Describe Oracle servers implementation of RDBMS and
object relational database management system
(ORDBMS)
A ense
Z
O be used
ic
l
N
I
Identify the development environments that
can
e
SP rabl
E
for this course
EZ ansfe
P
Describe the database and schema
-tr in this course
LO used
se
u
to
E non
G
a idrights
e reserved.
ORand/or
s
its
affiliates. All
a
O m) h t Gu
D
AN ilco tuden
M
a
In this lesson, you gain
R angunderstanding
S of the relational database management system
m
A
s
i
(RDBMS). You are also
introduced
hto Oracle SQL Developer and SQL*Plus as development
tSQL
@
e
l
environments used
for
executing
statements, and for formatting and reporting purposes.
a
o
cid
e
r
pa
a
s
de
Oracle Database 12c: SQL Workshop I 1 - 2
BS Grupo SAC
Lesson Agenda
Course objectives, agenda, and appendixes used in the

course
Overview of Oracle Database 12c and related products
Overview of relational database management concepts
and terminologies
Introduction to SQL and its development environments
Human Resource (HR) Schema and the tables used in the
course
Oracle database 12c SQL Documentation and Additional
Resources
nse
ZA
O lice
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
id
rec
ap
s
e
d
se
u
to
BS Grupo SAC
Course Objectives
After completing this course, you should be able to:
Identify the major components of Oracle Database
Retrieve row and column data from tables with the SELECT
statement
Create reports of sorted and restricted data
Employ SQL functions to generate and retrieve customized
data
Run complex queries to retrieve data from multiple tables
Run data manipulation language (DML) statements to
A ense
update data in Oracle Database
Z
O lic
N
I
Run data definition language (DDL) statements
blecreate
SP rato
E
and manage schema objects
sfe
EZ
se
u
to
OP n-tran
L
GE a no e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
a
This course offers you
an
to
R gintroduction
Sthe Oracle Database technology. In this class, you
m
A
s
i
learn the basic concepts
of relational
databases and the powerful SQL programming
th essential
@
e
l
language. This course
provides
the
SQL skills that enable you to write queries
a
o
against single
idand multiple tables, manipulate data in tables, create database objects, and
c
e
r
queryametadata.
sap
(de
BS Grupo SAC
Course Agenda
Day 1:
Introduction
Retrieving Data Using the SQL SELECT Statement
Restricting and Sorting Data
Using Single-Row Functions to Customize Output
Day 2:
Using Conversion Functions and Conditional Expressions

Reporting Aggregated Data Using the Group Functions
Displaying Data from Multiple Tables Using Joins
Using Subqueries to Solve Queries
nse
ZA
O lice
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
id
rec
ap
s
e
d
se
u
to
BS Grupo SAC
Course Agenda
Day 3:
Using the Set Operators
Managing Tables Using DML Statements
Introduction to Data Definition Language
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
c
e
r
pa
s
(de
se
u
to
BS Grupo SAC
Appendixes and Practices Used in the Course
Appendix A: Table Descriptions

Appendix B: Using SQL Developer
Appendix C: Using SQL*Plus
Appendix D: Commonly Used SQL Commands
Activity Guide
Practices and Solutions
Additional Practices and Solutions
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
c
e
r
pa
s
(de
se
u
to
BS Grupo SAC
Lesson Agenda

course
and terminologies
Human Resource (HR) Schema and the tables used in this
course
Resources
nse
ZA
O lice
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
id
rec
ap
s
e
d
se
u
to
BS Grupo SAC
Oracle Database 12c: Focus Areas
Infrastructure
Grids
Information
Management
Application
Development
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
a
Oracle Database 12c
offers
extensive
features
across the following focus areas:
R gm
S
A
s
i
Infrastructure Grids:
@ ThethInfrastructure Grid technology of Oracle enables pooling of
e
l
low-cost servers
a and storage to form systems that deliver the highest quality of service
o
d
i
in terms
c of manageability, high availability, and performance. Oracle Database 12c
e
r
consolidates
and extends the benefits of grid computing. Apart from taking full
a
p
a
des advantage of grid computing, Oracle Database 11g has unique change assurance
Oracle Cloud
features to manage changes in a controlled and cost-effective manner.

Information Management: Oracle Database 12c extends the existing information
management capabilities in content management, information integration, and
information lifecycle management areas. Oracle provides content management of
advanced data types such as Extensible Markup Language (XML), text, spatial,
multimedia, medical imaging, and semantic technologies.
Application Development: Oracle Database 12c has capabilities to use and manage
all the major application development environments such as PL/SQL, Java/JDBC, .NET
and Windows, PHP, SQL Developer, and Application Express.
Oracle Cloud: The Oracle Cloud is an enterprise cloud for business. It provides an
integrated collection of application and platform cloud services that are based on bestin-class products and open Java and SQL standards.
BS Grupo SAC
Oracle Database 12c
Manageability
High Availability
Performance
Security
Information Integration
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
Organizations needR
toM
supporta
multipleS
terabytes of information for users who demand fast
m
A
s
g
i
and secure access to @
business applications
the clock. The database systems must be
th quickly inround
leable to recover
reliable and musta
be
the event of any kind of failure. Oracle
dois designed along the following feature areas to help organizations manage
Database c
12c
i
re grids easily and deliver high-quality service:
infrastructure
a
p
a
des Manageability: By using some of the change assurance, management automation,
and fault diagnostics features, the database administrators (DBAs) can increase their
productivity, reduce costs, minimize errors, and maximize quality of service. Some of
the useful features that promote better management are Database Replay facility, the
SQL Performance Analyzer, the Automatic SQL Tuning facility, and Real-Time
Database Operations Monitoring.
Enterprise Manager Database Express 12c is a web-based tool for managing Oracle
databases. Enterprise Manager Database Express greatly simplifies database
performance diagnostics by consolidating the relevant database performance screens
into a consolidated view called Database Performance Hub. DBAs get a single,
consolidated view of the current real-time and historical view of the database
performance across multiple dimensions such as database load, monitored SQL and
PL/SQL, and Active Session History (ASH) on a single page for the selected time
period.
BS Grupo SAC
High availability: By using the high availability features, you can reduce the risk of down
time and data loss. These features improve online operations and enable faster database
upgrades.
Performance: By using capabilities such as SecureFiles, compression for online

transaction processing (OLTP), Real Application Clusters (RAC) optimizations, Result
Caches, and so on, you can greatly improve the performance of your database. Oracle
Database 12c enables organizations to manage large, scalable, transactional, and data
warehousing systems that deliver fast data access using low-cost modular storage.
Security: Oracle Database 12c helps organizations protect their information with unique
secure configurations, data encryption and masking, and sophisticated auditing
capabilities. It delivers a secure and scalable platform for reliable and fast access to all
types of information by using the industry-standard interfaces.
Information integration: Oracle Database 12c has many features to better integrate
data throughout the enterprise. It also supports advanced information lifecycle
management capabilities. This helps you manage the changing data in your database.
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
JO has Guid
O
D om) ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
c
e
r
pa
s
(de
se
u
to
BS Grupo SAC
Oracle Fusion Middleware

Portfolio of leading, standards-based, and customer-proven software products
that spans a range of tools and services from Java EE and developer tools,
through integration services, business intelligence, collaboration, and content
management
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
Oracle Fusion Middleware
and well-integrated family of products that
R gismaacomprehensive
S
A
s
i
offers complete support
development,
deployment, and management of Service-Oriented
th the development
@forfacilitates
e
l
Architecture (SOA).
SOA
of modular business services that can be
a
o
easily integrated
id and reused, thereby reducing development and maintenance costs, and
c
e
r
providing
a higher quality of services. Oracle Fusion Middlewares pluggable architecture
p
a
enables
you to leverage your investments in any existing application, system, or technology.
des
Its unbreakable core technology minimizes the disruption caused by planned or unplanned
outages.
Some of the products from the Oracle Fusion Middleware family include:
Application Server: Java EE, web services
SOA and Process Management: BPEL Process Manager, SOA Governance
Development Tools: Oracle Application Development Framework, JDeveloper, SOA
Suite
Business Intelligence: Oracle Business Activity Monitoring, Oracle Data Integrator
Enterprise Management: Enterprise Manager
Identity Management: Oracle Identity Management
Content Management: Oracle Content Database Suite
User Interaction: Portal, Rich Internet Apps
BS Grupo SAC
Oracle Enterprise Manager Cloud Control
Create and manage a complete set of cloud services.

Manage all phases of cloud life cycle.
Manage the entire cloud stack
Monitor the health of all components
Identify, understand, and resolve business problems
se
u
to
A ense
Z
O lic
N
I
ble
SP raintegration
Complete stack
Complete life cycle
Complete
E
Z nsfe
E
P
Self-Service IT I Simple and Automated
a
O In-trBusiness-Driven
L
GE a no e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
ilcis a S
ud
MA Control
t
a
Enterprise ManagerRCloud
management
tool that provides monitoring and
m
A
s
g
i
management capabilities
Oracle
components. It is a complete, integrated,
th and non-Oracle
@ formanagement
e
l
and business-driven
cloud
solution
in
a
single product, which is referred to as
a
o
Total Cloud
cidControl.
e
r
Using
paEnterprise Manager Cloud Control, you can:
a
s
de Create and manage a complete set of cloud services, including: Infrastructure-as-a-
service, Database-as-a-service, Platform-as-a-service, and others

Manage all phases of cloud life cycle
Manage the entire cloud stack: From application to disk, including engineered systems
(Exa series) and with integrated support capabilities
Monitor the health of all components, the hosts that they run on, and the key business
processes that they support
Identify, understand, and resolve business problems through the unified and correlated
management of User Experience, Business Transactions, and Business Services
across all your packaged and custom applications
BS Grupo SAC
Oracle Cloud
The Oracle Cloud is an enterprise cloud for business. It
consists of many different services that share some common
characteristics:
On-demand self-service
Resource pooling
Rapid elasticity
www.cloud.oracle.com
Measured service
Broad network access
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
Menterprise
a cloudSfor business. It provides an integrated collection of
The Oracle Cloud isRan
m
A
g
is that are based on best-in-class products and open
application and platform
cloud services
h
t
@
e
Java and SQL standards.
The top two benefits of cloud computing are speed and cost.
al
o
d applications and databases deployed in the Oracle Cloud are portable and
As a result,
cithe
e
r
you can
a easily move them to or from a private cloud or on-premise environment.
p
a
des All Cloud Services can be provisioned through a self-service interface. Users can get
their Cloud Services delivered on an integrated development and deployment platform

with tools to rapidly extend and create new services.
Oracle Cloud services are built on Oracle Exalogic Elastic Cloud and Oracle Exadata
Database Machine, together offering a platform that delivers extreme performance,
redundancy, and scalability.
The following are five essential characteristics of Oracle Cloud services:
On-demand self-service: Provisioning, monitoring, and management control
Resource pooling: Implies sharing and a level of abstraction between consumers and
services
Rapid elasticity: Ability to quickly scale up or down as needed
Measured service: Metering utilization for either internal chargeback (private cloud) or
external billing (public cloud)
Broad network access: Access through a browser on any networked device
BS Grupo SAC
Oracle Cloud Services

Oracle Cloud provides three types of services:
Software as a Service (SaaS)
Platform as a Service (PaaS)
Infrastructure as a Service (IaaS)
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
Aapplications
c thattare
l
i
uddelivered to end users over the Internet. Oracle
M
SaaS generally refers
to
a
R
S
Ais an example
gm tofhiasSaaS offering that provides both multitenant as well as
CRM On Demand
@
single-tenant options,
le depending on the customers preferences.
a
PaaS generally
dorefers to an application development and deployment platform delivered as a
i
c
e developers, enabling them to quickly build and deploy a SaaS application to end
servicerto
a
p
users.
The
platform typically includes databases, middleware, and development tools, all
a
s
e
delivered
as
a service via the Internet
d
IaaS refers to computing hardware (servers, storage, and network) delivered as a service.
This service typically includes the associated software as well as operating systems,
virtualization, clustering, and so on. Examples of IaaS in the public cloud include Amazons
Elastic Compute Cloud (EC2) and Simple Storage Service (S3).
The database cloud is built within an enterprises private cloud environment, as a PaaS
model. The database cloud provides on-demand access to database services in a selfservice, elastically scalable, and metered manner. The database cloud offers compelling
advantages in cost, quality of service, and agility. You can deploy a database within a virtual
machine in an laaS platform.
You can rapidly deploy Database clouds on Oracle Exadata which is a pre-integrated and
optimized hardware platform that supports both OLTP and DW workloads.
BS Grupo SAC
Cloud Deployment Models

Community
cloud
Public
cloud
Cloud
Deployment
Models
Private
Private
cloud
cloud
se
u
to
A ense
Z
O lic
N
I
Hybrid
P able
S
E
cloud
Z nsfer
E
OP n-tra
L
GE a no e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
ilc Suses
ud a private cloud, which it typically controls,
MA organization
t
a
Private cloud: R
A single
m data
A
is centers. However, the organization can also
manages, and hosts
ingprivate
h
t
@
e
outsource hosting
al and operation to a third-party service provider. Amazons Virtual Private
o
d example of a private cloud in an external provider setting.
Cloud c
isian
e
r
p
Public
a cloud: Multiple organizations (tenants) uses private cloud on a shared basis,
a
des hosted and managed by a third-party service provider. Example: Amazon Elastic Compute
Cloud (EC2), IBMs Blue Cloud, Sun Cloud, Google AppEngine, and so on
Community cloud: A group of related organizations, who want to make use of a common
cloud computing environment, uses the community cloud. It is managed by the
participating organizations or by a third-party managed service provider. It is hosted
internally or externally. For example, A community might consist of the different branches
of the military, all the universities in a given region, or all the suppliers to a large
manufacturer.
Hybrid cloud: A single organization that wants to adopt both private and public clouds for
a single application uses the hybrid cloud. A third model, the hybrid cloud, is maintained
by both internal and external providers. For example, an organization might use a public
cloud service, such as Amazon Simple Storage Service (Amazon S3) for archived data
but continue to maintain in-house (private cloud) storage for operational customer data.
BS Grupo SAC
Lesson Agenda

course
and terminologies
course
Resources
nse
ZA
O lice
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
id
rec
ap
s
e
d
se
u
to
BS Grupo SAC
Relational and Object Relational

Database Management Systems
Relational model and object relational model

User-defined data types and objects
Fully compatible with relational database
Supports multimedia and large objects
High-quality database server features
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
a the relational
The Oracle server supports
both
S and the object relational database models.
AR gm
s
i
The Oracle server extends
capabilities to support an object relational
th
@ the data-modeling
e
l
a
database model
that
provides
object-oriented
programming,
complex data types, complex
o
d
i
business objects,
and full compatibility with the relational world.
c
e
r
pa several features for improved performance and functionality of the OLTP
Ita
includes
s
de applications, such as better sharing of runtime data structures, larger buffer caches, and
se
u
to
deferrable constraints. Data warehouse applications benefit from enhancements such as

parallel execution of insert, update, and delete operations; partitioning; and parallel-aware
query optimization. The Oracle model supports client/server and web-based applications that
are distributed and multitiered.
For more information about the relational and object relational model, refer to Oracle
Database Concepts for 12c Database.
BS Grupo SAC
Data Storage on Different Media
se
u
to
A ense
Z
O lic
N
I
SP rable
E
Electronic
fe Database
s
Filing cabinet PEZ
n
spreadsheet
O n-tra
L
GE a no e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
Msome
a
Every organizationR
has
information
S needs. A library keeps a list of members, books,
m
A
s
g
i
due dates, and fines. A
to save information about its employees,
thneeds
@company
e
l
departments, and
salaries.
These
pieces
of information are called data.
a
o
Organizations
cidcan store data in various media and in different formats, such as a hard copy
e
r
document
pa in a filing cabinet, or data stored in electronic spreadsheets, or in databases.
a
s
de A database is an organized collection of information.
To manage databases, you need a database management system (DBMS). A DBMS is a
program that stores, retrieves, and modifies data in databases on request. There are four
main types of databases: hierarchical, network, relational, and (most recently) object
relational.
BS Grupo SAC
Relational Database Concept
Dr. E. F. Codd proposed the relational model for database

systems in 1970.
It is the basis for the relational database management
system (RDBMS).
The relational model consists of the following:
Collection of objects or relations
Set of operators to act on the relations
Data integrity for accuracy and consistency
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
The principles of the
relational
R gmamodeliswere
S first outlined by Dr. E. F. Codd in a June 1970
A
paper titled A Relational
Model ofth
Data for Large Shared Data Banks. In this paper, Dr. Codd
@model
e
l
proposed the relational
for
database
systems.
a
o
The common
cidmodels used at that time were hierarchical and network, or even simple flat-file
e
r
a
data
Relational database management systems (RDBMS) soon became very
pstructures.
a
s
popular,
especially
for their ease of use and flexibility in structure. In addition, a number of
de
innovative vendors, such as Oracle, supplemented the RDBMS with a suite of powerful,
application development and user-interface products, thereby providing a total solution.
Components of the Relational Model
Collections of objects or relations that store the data
A set of operators that can act on the relations to produce other relations
Data integrity for accuracy and consistency
BS Grupo SAC
Definition of a Relational Database

A relational database is a collection of relations or
two-dimensional tables controlled by the Oracle server.
Oracle
server
Table name: EMPLOYEES
Table name: DEPARTMENTS
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
A relational database
uses
two-dimensional tables to store information.
ma isor S
AR grelations
For example, you might
th information about all the employees in your company. In
@want to store
e
l
a relational database,
a you create several tables to store different pieces of information about
o
d
i
your employees,
c such as an employee table, a department table, and a salary table.
e
r
pa
a
s
de
BS Grupo SAC
Data Models
Model of
system
in clients
mind
Entity model of
clients model
Table model
of entity model
Oracle
server
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfeTables on disk
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
Models are the cornerstone
of a
design. Engineers
build a model of a car to work out any
R gm
S
A
s
i
details before putting it@
into production.
the same manner, system designers develop
th theInunderstanding
e
l
models to explore
ideas
and
improve
of database design.
a
o
d
Purpose of
ciModels
e
r
pa help to communicate the concepts that are in peoples minds. They can be used to do
Models
a
s
de the following:
Communicate
Categorize
Describe
Specify
Investigate
Evolve
Analyze
Imitate
The objective is to produce a model that fits a multitude of these uses, can be understood by
an end user, and contains sufficient detail for a developer to build a database system.
BS Grupo SAC
Entity Relationship Model
Create an entity relationship diagram from business

specifications or narratives:
EMPLOYEE
#*
number
*
name
o
job title
assigned to
composed of
DEPARTMENT
#*
number
*
name
o
location
Scenario:
. . . Assign one or more employees to a
department . . .
A ense
. . . Some departments do not yet have assignedZemployees
. . .
INO e lic
se
u
to
SP rabl
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
In an effective system,
data
is a
divided into
R gm
S discrete categories or entities. An entity
A
s
i
relationship (ER) model
illustration
various entities in a business and the
thmodelofis the
@is an
e
l
relationships among
them.
An
ER
derived
from business specifications or narratives
a
o
and built during
id the analysis phase of the system development life cycle. ER models separate
c
e
r
the information
required by a business from the activities performed within the business.
a
p
a
Although
businesses
can change their activities, the type of information tends to remain
s
(de constant. Therefore, the data structures also tend to be constant.
BS Grupo SAC
Benefits of ER Modeling
Documents information for the organization in a clear, precise format
Provides a clear picture of the scope of the information requirement
Provides an easily understood pictorial map for database design
Offers an effective framework for integrating multiple applications
Key Components
Entity: An aspect of significance about which information must be known. Examples are
departments, employees, and orders.
Attribute: Something that describes or qualifies an entity. For example, for the employee
entity, the attributes would be the employee number, name, job title, hire date, department
number, and so on. Each of the attributes is either required or optional. This state is called
optionality.
Relationship: A named association between entities showing optionality and degree.
Examples are employees and departments, and orders and items.
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
JO has Guid
O
D om) ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
c
e
r
pa
s
(de
se
u
to
BS Grupo SAC
Entity Relationship Modeling Conventions

Entity:
Attribute:
Singular, unique name Singular name
Lowercase
Uppercase
Mandatory marked with *
Soft box
Optional marked with o
Synonym in parentheses
EMPLOYEE
#*
number
*
name
o
job title
DEPARTMENT
#*
number
*
name
composed of o
location
assigned to
A ense
Z
O lic
N
I
Unique Identifier (UID)
SP rable
E
Primary marked with # EZ
fe
s
n
P
Secondary marked withO(#) tra
L onE
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
Entities
AR gma his S
To represent an entity
in a model,
t use the following conventions:
@
e
l
a
entity name
Singular,o
d unique
i
c
Entity
re name in uppercase
a
p
a Soft box
des
Optional synonym names in uppercase within parentheses: ( )
Attributes
To represent an attribute in a model, use the following conventions:
Singular name in lowercase
Asterisk (*) tag for mandatory attributes (that is, values that must be known)
Letter o tag for optional attributes (that is, values that may be known)
se
u
to
BS Grupo SAC
Relationships
Each direction of the relationship contains:
A label: For example, taught by or assigned to
An optionality: Either must be or maybe
A degree: Either one and only one or one or more
Symbol
Description
Dashed line
Optional element indicating maybe
Solid line
Mandatory element indicating must be
Crows foot
Degree element indicating one or more
Single line
Degree element indicating one and only one
Note: The term cardinality is a synonym for the term degree.

Each source entity {may be | must be} in relation {one and only one | one or more} with the
destination entity.
se
u
to
A ense
Z
Unique Identifiers
O lic
N
I
A unique identifier (UID) is any combination of attributes or relationships,
SP raborleboth, that serves to
E
distinguish occurrences of an entity. Each entity occurrence
be
feuniquely identifiable.
s
EZ must
n
P
Tag each attribute that is part of the UID withO
a hash -sign
tra#.
L
n
Tag secondary UIDs with a hash signGinEparentheses
no e(#).
R
a
JO has Guid
O
D om) ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
i
c
re
a
p
a
des
Note: The convention is to read clockwise.
BS Grupo SAC
Relating Multiple Tables
Each row of data in a table can be uniquely identified by a

primary key.
You can logically relate data from multiple tables using
foreign keys.
Table name: DEPARTMENTS
Table name: EMPLOYEES
se
u
to
A ense
Z
O lic
N
I
SP rable
E
Primary key
EZ ansfe
P
Primary key
Foreign key
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
ilc Sexactly
ud one entity. For example, the EMPLOYEES
MAthat describes
t
Each table containsRdata
a
m employees.
A
gabout
is
table contains information
Categories of data are listed across the top of
h
t
@
e
l
each table, and individual
cases
are
listed
below.
By using a table format, you can readily
a
o
visualize, understand,
and use information.
cid
e
r
Because
pa data about different entities is stored in different tables, you may need to combine
a
s
two
de or more tables to answer a particular question. For example, you may want to know the
location of the department where an employee works. In this scenario, you need information
from the EMPLOYEES table (which contains data about employees) and the DEPARTMENTS
table (which contains information about departments). With an RDBMS, you can relate the
data in one table to the data in another by using foreign keys. A foreign key is a column (or a
set of columns) that refers to a primary key in the same table or another table.
You can use the ability to relate data in one table to data in another to organize information in
separate, manageable units. Employee data can be kept logically distinct from the department
data by storing it in a separate table.
BS Grupo SAC
Guidelines for Primary Keys and Foreign Keys
You cannot use duplicate values in a primary key.
Primary keys generally cannot be changed.
Foreign keys are based on data values and are purely logical (not physical) pointers.
A foreign key value must match an existing primary key value or unique key value;
otherwise, it must be null.
A foreign key must reference either a primary key or a unique key column.
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
JO has Guid
O
D om) ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
c
e
r
pa
s
(de
se
u
to
BS Grupo SAC
Relational Database Terminology
3
4
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
a one orSmany tables. A table is the basic storage structure of
A relational database
can
R gcontain
m
A
is necessary about something in the real world, such as
an RDBMS. A table holds
all the tdata
h
@
e
employees, invoices,
al or customers.
o
The slide shows
cid the contents of the EMPLOYEES table or relation. The numbers indicate the
e
r
following:
pa
a
s
de 1. A single row (or tuple) representing all the data required for a particular employee. Each
1
row in a table should be identified by a primary key, which permits no duplicate rows.
The order of rows is insignificant; specify the row order when the data is retrieved.
2. A column or attribute containing the employee number. The employee number identifies
a unique employee in the EMPLOYEES table. In this example, the employee number
column is designated as the primary key. A primary key must contain a value and the
value must be unique.
3. A column that is not a key value. A column represents one kind of data in a table; in this
example, the data is the salaries of all the employees. Column order is insignificant
when storing data; specify the column order when the data is retrieved.
BS Grupo SAC
4. A column containing the department number, which is also a foreign key. A foreign key
is a column that defines how tables relate to each other. A foreign key refers to a
primary key or a unique key in the same table or in another table. In the example,
DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table.
5. A field can be found at the intersection of a row and a column. There can be only one
value in it.
6. A field may have no value in it. This is called a null value. In the EMPLOYEES table, only
those employees who have the role of sales representative have a value in the
COMMISSION_PCT (commission) field.
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
JO has Guid
O
D om) ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
c
e
r
pa
s
(de
se
u
to
BS Grupo SAC
Lesson Agenda

course
and terminologies
course
Resources
nse
ZA
O lice
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
id
rec
ap
s
e
d
se
u
to
BS Grupo SAC
Using SQL to Query Your Database

Structured query language (SQL) is:
The ANSI standard language for operating relational
databases
Efficient, easy to learn, and use
Functionally complete (With SQL, you can define, retrieve,
and manipulate data in tables.)
SELECT department_name
FROM
departments;
Oracle
server
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
anot specify
In a relational database,
do
R you
S the access route to the tables, and you do not
m
A
s
g
i
need to know how the@
data is arranged
th physically.
e
l
To access the o
database,
you execute a structured query language (SQL) statement, which is
a
d
i
the American
c National Standards Institute (ANSI) standard language for operating relational
e
r
databases.
pa SQL is also compliant to ISO Standard (SQL 1999).
a
s
de SQL is a set of statements with which all programs and users access data in an Oracle
Database. Application programs and Oracle tools often allow users access to the database
without using SQL directly, but these applications, in turn, must use SQL when executing the
users request.
SQL provides statements for a variety of tasks, including:
Querying data
Inserting, updating, and deleting rows in a table
Creating, replacing, altering, and dropping objects
Controlling access to the database and its objects
Guaranteeing database consistency and integrity
SQL unifies all of the preceding tasks in one consistent language and enables you to work
with data at a logical level.
BS Grupo SAC
SQL Statements Used in the Course

SELECT
INSERT
UPDATE
DELETE
MERGE
Data manipulation language (DML)
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT
Data definition language (DDL)
GRANT
REVOKE
Data control language (DCL)
se
u
to
A ense
Z
COMMIT
O lic
N
I
Transaction control
ROLLBACK
P able
S
E
SAVEPOINT
Z nsfer
E
OP n-tra
L
GE a no e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
SQL Statements R
ma is S
A
g
th comply with industry standards. Oracle Corporation
@ by Oracle
SQL statements supported
e
l
a
ensures futureo
compliance
with evolving standards by actively involving key personnel in SQL
d
i
standards
committees.
The
industry-accepted committees are ANSI and International
c
e
r
Standards
pa Organization (ISO). Both ANSI and ISO have accepted SQL as the standard
a
s
(de language for relational databases.
Statement
Description
SELECT
INSERT
UPDATE
DELETE
MERGE
CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT
GRANT
REVOKE
Retrieves data from the database, enters new rows, changes existing rows, and
removes unwanted rows from tables in the database, respectively. Collectively
known as data manipulation language (DML)
COMMIT
ROLLBACK
SAVEPOINT
Manages the changes made by DML statements. Changes to the data can be
grouped together into logical transactions
Sets up, changes, and removes data structures from tables. Collectively known as
data definition language (DDL)
Provides or removes access rights to both the Oracle Database and the structures
within it
BS Grupo SAC
Development Environments for SQL

There are two development environments for this course:
The primary tool is Oracle SQL Developer.
SQL*Plus command-line interface can also be used.
SQL Developer
SQL*Plus
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
SQL Developer RM
ma is S
A
g
This course is developed
th SQL Developer as the tool for running the SQL
@ using Oracle
e
l
statements discussed
a in the examples in the lessons and the practices. SQL Developer is the
o
d
i
default toolcfor this class.
re
a
p
SQL*Plus
a
desThe SQL*Plus environment can also be used to run all SQL commands covered in this
course.
Notes
See Appendix B for information about using SQL Developer, including simple instructions
on installation process.
See Appendix C for information about using SQL*Plus.
BS Grupo SAC
Lesson Agenda

course
and terminologies
course
Resources
nse
ZA
O lice
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
id
rec
ap
s
e
d
se
u
to
BS Grupo SAC
Human Resources (HR) Schema
DEPARTMENTS
LOCATIONS
department_id
department name
manager_id
location_id
location_id
street address
postal code
city
state province
Country id
JOB_HISTORY
employee_id
start_date
end_date
job_id
department_id
JOBS
job_id
job_title
min_salary
max_salary
EMPLOYEES
employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id
COUNTRIES
country_id
country_name
region_id
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
ud
MA ailcDescription
t
Human ResourcesR(HR)Schema
S
A
gmschema
is is a part of the Oracle Sample Schemas that can be
h
t
The Human Resources
(HR)
@
e
al Database. The practice sessions in this course use data from the HR
installed in an o
Oracle
schema.ecid
r
a
p
Table
Descriptions
sa REGIONS contains rows that represent a region such as America, Asia, and so on.
e
d
(
REGIONS
se
u
to
region_id
region_name
COUNTRIES contains rows for countries, each of which is associated with a region.
LOCATIONS contains the specific address of a specific office, warehouse, or production
site of a company in a particular country.
DEPARTMENTS shows details about the departments in which the employees work. Each
department may have a relationship representing the department manager in the
EMPLOYEES table.
EMPLOYEES contains details about each employee working for a department. Some
employees may not be assigned to any department.
JOBS contains the job types that can be held by each employee.
JOB_HISTORY contains the job history of the employees. If an employee changes
departments within a job or changes jobs within a department, a new row is inserted into
this table with the earlier job information of the employee.
BS Grupo SAC
Tables Used in the Course
EMPLOYEES
se
u
to
A ense
Z
O lic
N
I
SP rable
E
DEPARTMENTS
Z nsfe
E
P
JOB_GRADES
O n-tra
L
GE a no e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
d
ilcin this
ucourse:
MAare used
t
a
The following mainR
tables
S
A Gives
gmdetails
isof all the employees
h
EMPLOYEES table:
t
@
e
al table: Gives details of all the departments
DEPARTMENTS
o
cid table: Gives details of salaries for various grades
JOB_GRADES
e
r
pafrom these tables, you will also use the other tables listed in the previous slide such as
a
Apart
s
de the LOCATIONS and the JOB_HISTORY table.
Note: The structure and data for all the tables are provided in Appendix A.
BS Grupo SAC
Lesson Agenda

course
and terminologies
course
Resources
nse
ZA
O lice
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
id
rec
ap
s
e
d
se
u
to
BS Grupo SAC
Oracle Database Documentation
Oracle Database New Features Guide

Oracle Database Reference
Oracle Database SQL Language Reference
Oracle Database Concepts
Oracle Database SQL Developer Users Guide
se
u
to
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
Navigate to http://st-doc.us.oracle.com/12/121/index.htm
to access the Oracle Database 12c
R gma is S
A
documentation library.@
th
e
l
oa
d
i
c
e
r
pa
a
s
de
BS Grupo SAC
Additional Resources
For additional information about Oracle Database 12c, refer to
the following:
Oracle Database 12c: New Features eStudies
Oracle Learning Library:
http://www.oracle.com/goto/oll
Oracle Cloud:
http://cloud.oracle.com
Access the online SQL Developer Home Page, which is

available at:
http://www.oracle.com/technology/products/database/sql_developer
/index.html
se
A
Z icen
O
l
Access the SQL Developer tutorial, which isIN
available
e
P
l
online at:
ES ferab
Z
http://download.oracle.com/oll/tutorials/SQLDeveloper/index.htm
PE trans
O
L onE
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
AR gma his S
t
@
e
l
a
do
i
c
re
a
p
sa
(de
se
u
to
BS Grupo SAC
Summary
In this lesson, you should have learned:
The goals of the course
Features of Oracle Database 12c
The salient features of Oracle Cloud
The theoretical and physical aspects of a relational
database
Oracle servers implementation of RDBMS and object
relational database management system (ORDBMS)
The development environments that can be used for this
A ense
course
Z
O lic
N
I
About the database and schema used inSthis
P course
ble
se
u
to
E fera
Z
PE trans
O
L onE
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
A ilc tud
M
Relational database
management
R gma systems
S are composed of objects or relations. They are
A
s
i
managed by operations
th by data integrity constraints.
@and governed
e
l
Oracle Corporation
a produces products and services to meet your RDBMS needs. The main
o
d
i
products are
c the following:
e
r
a Database with which you store and manage information by using SQL
pOracle
a
s
de Oracle Fusion Middleware with which you develop, deploy, and manage modular
business services that can be integrated and reused
Oracle Enterprise Manager Grid Control, which you use to manage and automate
administrative tasks across sets of systems in a grid environment
SQL
The Oracle server supports ANSI-standard SQL and contains extensions. SQL is the
language that is used to communicate with the server to access, manipulate, and control data.
BS Grupo SAC
Practice 1: Overview
This practice covers the following topics:
Starting Oracle SQL Developer
Creating a new database connection
Browsing the HR tables
A ense
Z
O lic
N
I
SP rable
E
EZ ansfe
P
LO on-tr
E
G a n e
R
O and/or
aitssaffiliates.
h
O
G
)
D om ent
N
lc tud
MA theaifollowing:
In this practice, youR
perform
S
A Developer
gm tand
iscreate
h
Start Oracle SQL @
a new connection to the ora1 account.
e
l
a Developer to examine data objects in the ora1 account. The ora1
Use Oracle
oSQL
d
i
account
c contains the HR schema tables.
e
r
pathe following location for the lab files:
Note
a
s
de
se
u
to
/home/oracle/labs/sql1/labs
If you are asked to save any lab files, save them in this location.
In any practice, there may be exercises that are prefaced with the phrases If you have time
or If you want an extra challenge. Work on these exercises only if you have completed all
other exercises within the allocated time and would like a further challenge to your skills.
Perform the practices slowly and precisely. You can experiment with saving and running
command files. If you have any questions at any time, ask your instructor.
Note: All written practices use Oracle SQL Developer as the development environment.
Although it is recommended that you use Oracle SQL Developer, you can also use SQL*Plus
that is available in this course.

D80190GC11 Les01

Uploaded by

Copyright:

Available Formats

D80190GC11 Les01

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

D80190GC11 Les01

Uploaded by

Copyright:

Available Formats

Unauthorized reproduction or distribution prohibited Copyright 2014, Oracle and/or its affiliates

Oracle Database 12c: SQL Workshop I 1 - 2

Course objectives, agenda, and appendixes used in the

Oracle Database 12c: SQL Workshop I 1 - 3

Oracle Database 12c: SQL Workshop I 1 - 4

Using Conversion Functions and Conditional Expressions

Oracle Database 12c: SQL Workshop I 1 - 5

Oracle Database 12c: SQL Workshop I 1 - 6

Appendixes and Practices Used in the Course

Appendix A: Table Descriptions

Oracle Database 12c: SQL Workshop I 1 - 7

Course objectives, agenda, and appendixes used in the

Oracle Database 12c: SQL Workshop I 1 - 8

Oracle Database 12c: Focus Areas

features to manage changes in a controlled and cost-effective manner.

Oracle Database 12c: SQL Workshop I 1 - 9

Oracle Database 12c

Performance: By using capabilities such as SecureFiles, compression for online

Oracle Database 12c: SQL Workshop I 1 - 11

Oracle Fusion Middleware

Oracle Enterprise Manager Cloud Control

Create and manage a complete set of cloud services.

service, Database-as-a-service, Platform-as-a-service, and others

Oracle Database 12c: SQL Workshop I 1 - 13

their Cloud Services delivered on an integrated development and deployment platform

Oracle Cloud Services

Oracle Database 12c: SQL Workshop I 1 - 15

Cloud Deployment Models

Oracle Database 12c: SQL Workshop I 1 - 16

Course objectives, agenda, and appendixes used in the

Oracle Database 12c: SQL Workshop I 1 - 17

Relational and Object Relational

Relational model and object relational model

deferrable constraints. Data warehouse applications benefit from enhancements such as

Oracle Database 12c: SQL Workshop I 1 - 18

Data Storage on Different Media

Oracle Database 12c: SQL Workshop I 1 - 19

Relational Database Concept

Dr. E. F. Codd proposed the relational model for database

Oracle Database 12c: SQL Workshop I 1 - 20

Definition of a Relational Database

Table name: EMPLOYEES

Table name: DEPARTMENTS

Oracle Database 12c: SQL Workshop I 1 - 21

Oracle Database 12c: SQL Workshop I 1 - 22

Entity Relationship Model

Create an entity relationship diagram from business

(de constant. Therefore, the data structures also tend to be constant.

Oracle Database 12c: SQL Workshop I 1 - 23

Oracle Database 12c: SQL Workshop I 1 - 24

Entity Relationship Modeling Conventions

Optional synonym names in uppercase within parentheses: ( )

Oracle Database 12c: SQL Workshop I 1 - 25

Optional element indicating maybe

Mandatory element indicating must be

Degree element indicating one or more

Degree element indicating one and only one

Note: The term cardinality is a synonym for the term degree.

Oracle Database 12c: SQL Workshop I 1 - 26