1

Introduction

Copyright © 2017, Oracle and/or its affiliates. All rights reserved.

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
• Discuss the theoretical and physical aspects of a relational database
• Describe Oracle server’s implementation of relational database management system
(RDBMS) and object relational database management system (ORDBMS)
• Identify the development environments that can be used for this course
• Describe the database and schema used in this course

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-2
Lesson Agenda

• Course objectives, roadmap, and appendixes used in the course

• Overview of Oracle Database 12c and related products
• Overview of relational database management concepts and terminologies
• Human Resource (HR) Schema and the tables used in the course
• Introduction to SQL and its development environments
• Oracle Database 12c SQL Documentation and Additional Resources

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-3
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 update data in Oracle Database
• Run data definition language (DDL) statements to create and manage schema objects

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-4
Course Roadmap

Lesson 1: Introduction Lesson 2: Retrieving Data using SQL SELECT

Unit 1: Retrieving, Restricting,

and Sorting Data Lesson 3: Restricting and Sorting Data

Unit 2: Joins, Subqueries, and Lesson 4: Using Single-Row Functions to

Set Operators Customize Output

Lesson 5: Using Conversion Functions and

Unit 3: DML and DDL Conditional Expressions

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-5
Course Roadmap

Lesson 1: Introduction Lesson 6: Reporting Aggregated Data Using

Group Functions

Unit 1: Retrieving, Restricting, Lesson 7: Displaying Data from Multiple

and Sorting Data Tables Using Joins

Unit 2: Joins, Subqueries, and

Lesson 8: Using Subqueries to Solve Queries
Set Operators

Unit 3: DML and DDL Lesson 9: Using Set Operators

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-6
Course Roadmap

Lesson 1: Introduction

Unit 1: Retrieving, Restricting,

and Sorting Data

Unit 2: Joins, Subqueries, and Lesson 10: Managing Tables Using DML
Set Operators Statements

Lesson 11: Introduction to Data Definition

Unit 3: DML and DDL Language

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-7
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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-8
Lesson Agenda

• Course objectives, roadmap, and appendixes used in the course

• Overview of Oracle Database 12c and related products
• Overview of relational database management concepts and terminologies
• Human Resource (HR) Schema and the tables used in the course
• Introduction to SQL and its development environments
• Oracle Database 12c SQL Documentation and Additional Resources

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1-9
Oracle Database 12c: Focus Areas

Information Application
Management Development

Oracle Cloud Infrastructure

Grids

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 10

Oracle Database 12c

High Availability Manageability

Performance Information
Integration
Security
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 11
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
• Human Resource (HR) Schema and the tables used in this course
• Introduction to SQL and its development environments
• Oracle Database 12c SQL Documentation and Additional Resources

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 13

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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 14

Data Storage on Different Media

Electronic Filing cabinet Database

spreadsheet

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 16

Relational Database Concept

• Dr. E. F. Codd proposed the relational model for database systems in 1970.
• It is the basis for 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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 17

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

… …

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 18

Data Models
Model of system
in client’s mind

Entity model of
client’s model
Oracle
server
Table model
of entity model

Tables
on disk

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 19

Entity Relationship Model

• Create an entity relationship diagram from business specifications or narratives:

EMPLOYEE DEPARTMENT
assigned to
#* number #* number
* name * name
o job title composed of o location

• Scenario:
– “. . . Assign one or more employees to a department. . .”
– “. . . Some departments do not yet have assigned employees. . .”

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 20

Entity Relationship Modeling Conventions
Entity Attribute
• Singular, unique name • Singular name
• Uppercase • Lowercase
• Soft box • Mandatory marked with ‘*’
• Synonym in parentheses • Optional marked with ‘o’

EMPLOYEE DEPARTMENT
#* number assigned to
#* number
* name * name
o job title composed of o location

Unique Identifier (UID)

• Primary marked with “#”
• Secondary marked with “(#)”

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 22

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

…
Primary key
Primary key Foreign key

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 24

Relational Database Terminology

2 4

5
6

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 26

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
• Human Resource (HR) Schema and the tables used in this course
• Introduction to SQL and its development environments
• Oracle Database 12c SQL Documentation and Additional Resources

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 28

Human Resources (HR) application

HR Application
Basic Search:

Advanced Search:

First Name Emp. ID

Last Name Department

GO

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 29

Tables Used in This Course
DEPARTMENTS LOCATIONS
department_id location_id
department_name street_address
manager_id postal_code
location_id city
state_province
JOB_HISTORY country_id
employee_id
start_date
end_date EMPLOYEES
job_id employee_id COUNTRIES
department_id first_name country_id
last_name country_name
email region_id
phone_number
hire_date
JOBS job_id
job_id salary
job_title commission_pct REGIONS
min_salary manager_id region_id
max_salary department_id region_name

JOB_GRADES
grade_level
lowest_sal
highest_sal

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 30

Tables Used in the Course
EMPLOYEES

JOB_GRADES DEPARTMENTS

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 32

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
• Human Resource (HR) Schema and the tables used in this course
• Introduction to SQL and its development environments
• Oracle Database 12c SQL Documentation and Additional Resources

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 33

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

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 34

How SQL Works

• SQL is standalone and powerful.

• SQL processes groups of data.
• SQL lets you work with data at a logical level.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 35

SQL Statements Used in the Course
SELECT
Data manipulation language INSERT
UPDATE
(DML) DELETE
MERGE

CREATE
ALTER
Data definition language (DDL) DROP
RENAME
TRUNCATE
COMMENT

Data control language (DCL) GRANT

REVOKE

COMMIT
Transaction control ROLLBACK
SAVEPOINT

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 36

Development Environments for SQL

There are two development environments for this course:

• The primary tool is Oracle SQL Developer.
• The SQL*Plus command-line interface can also be used.

SQL Developer SQL*Plus

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 37

Introduction to Oracle Live SQL

• Easy way to learn, access, test, and share SQL and PL/SQL scripts on Oracle Database
• Sign up and use it free of cost.

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 38

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
• Human Resource (HR) Schema and the tables used in this course
• Introduction to SQL and its development environments
• Oracle Database 12c SQL Documentation and Additional Resources

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 39

Oracle Database Documentation

• Oracle Database New Features Guide

• Oracle Database Reference
• Oracle Database SQL Language Reference
• Oracle Database Concepts
• Oracle Database SQL Developer User’s Guide

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 40

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:
– cloud.oracle.com
• The online SQL Developer Home Page, which is available at:
– http://www.oracle.com/technology/products/database/sql_developer/index.html
• The SQL Developer tutorial, which is available online at:
– http://download.oracle.com/oll/tutorials/SQLDeveloper/index.htm

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 41

Summary

In this lesson, you should have learned about:

• The goals of the course
• Features of Oracle Database 12c
• The theoretical and physical aspects of a relational database
• Oracle server’s implementation of RDBMS and ORDBMS
• The development environments that can be used for this course
• The database and schema used in this course

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 42

Practice 1: Overview

This practice covers the following topics:

• Starting Oracle SQL Developer
• Creating a new database connection
• Browsing the HR tables

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. 1 - 43