Oracle Database SQL | Introduction to SQL

OCA Oracle Certified Associate 11g / 12c - Exam number: 1Z0-071

This course will take place in 3-4 Kirby Street, London, EC1N 8TS. Find on map
15 %
Discount

when you buy 3 or more courses

The discount will be automatically
applied at the checkout
Looking for a
2-12 months
training?
Choose Database Administrator
Career Programme

Looking for a 2-12 months training?

Choose

Database Administrator career programme

Upcoming dates

There are no open public schedule dates for Oracle Database SQL | Introduction to SQL course. We can offer you a private training or arrange a public course for you. Contact us here and we can make arrangements for your course.

Course Outline

Oracle Database SQL | Introduction to SQL course overview

This Intro to SQL training teaches basic concepts of relational databases and the SQL programming language. You'll gain essential SQL skills to write queries against single & multiple tables, manipulate data in tables & create database objects.


Prerequisites for Oracle Database SQL | Introduction to SQL course

  • Familiarity with data processing concepts and techniques.


Oracle Database SQL | Introduction to SQL Course Contents

1 Oracle and Structured Query Language (SQL)

The Exam: An Overview

What to Do and What to Expect

Oracle SQL vs. ANSI SQL

Oracle SQL vs. Oracle SQL*Plus

SQL Fundamentals I vs. SQL Certified Associate

Confirm Appropriate Materials for Study

Identify the Connection Between an ERD and a Relational Database

Entity-Relationship Diagrams and Data Modeling

Relational Databases

Many-to-Many Relationships

Database Normalization

Explain the Relationship Between a Database and SQL

Describe the Purpose of DDL

Describe the Purpose of DML

Transaction Control Language

Build a SELECT Statement to Retrieve Data from an Oracle Database Table

2 Using DDL Statements to Create and Manage Tables

Categorize the Main Database Objects

What Are Database Objects?

Schemas

Create a Simple Table

Naming a Table or Other Object

The SQL Statement CREATE TABLE

Review the Table Structure

List the Data Types That Are Available for Columns

Character

Numeric

Date

Large Objects

Explain How Constraints Are Created at the Time of Table Creation

Creating CONSTRAINTS in the CREATE TABLE Statement

The Types of CONSTRAINTS

Drop Columns and Set Column UNUSED

Dropping Columns

UNUSED

Create and Use External Tables

Benefits

Creating External Tables

3 Manipulating Data

Truncate Data

Recursively Truncate Child Tables

Insert Rows into a Table

Default Column List

Enumerated Column List

Update Rows in a Table

Expressions

Constraints

The WHERE Clause

Delete Rows from a Table

Control Transactions

COMMIT

ROLLBACK

SAVEPOINT

ROLLBACK Revisited

4 Restricting and Sorting Data

Sort the Rows That Are Retrieved by a Query

Reference by Name

Expressions

Reference by Position

Combinations

ORDER BY and NULL

Limit the Rows That Are Retrieved by a Query

The WHERE Clause

Boolean Logic

Additional WHERE Clause Features

Additional Concepts

Use Ampersand Substitution to Restrict and Sort Output at Run Time

&

DEFINE and UNDEFINE Commands

The SET and SHOW Commands

ACCEPT and PROMPT

Use the SQL Row Limiting Clause

FETCH

WITH TIES

OFFSET

5 Using Single-Row Functions to Customize Output

Use Various Types of Functions That Are Available in SQL

Character Functions

Number Functions

Date Functions

Other Functions

Use Character, Number, Date, and Analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) Functions in SELECT Statements

The DUAL Table

Character Functions

Numerical Functions

Date Functions

Analytical Functions

Nesting Functions

6 Using Conversion Functions and Conditional Expressions

Describe Various Types of Conversion Functions

Explicit and Implicit Conversion

Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions

Conversion Functions

Additional Conversion Functions

Apply General Functions and Conditional Expressions in a SELECT Statement

CASE

DECODE

NVL

NULLIF

7 Reporting Aggregated Data Using the Group Functions

Describe the Use of Group Functions

COUNT

SUM

MIN, MAX

AVG

MEDIAN

RANK

DENSE_RANK

FIRST, LAST

Others

Group Data by Using the GROUP BY Clause

Multiple Columns

ORDER BY Revisited

Nesting Functions

Include or Exclude Grouped Rows by Using the HAVING Clause

8 Displaying Data from Multiple Tables

Describe the Different Types of Joins and Their Features

Types of Joins

Use SELECT Statements to Access Data from More Than One Table Using Equijoins and Non-Equijoins

Inner Joins

Using Table Aliases

Natural Joins

USING

Multitable Joins

Non-Equijoins

Join a Table to Itself by Using a Self-Join

Self-Referencing Foreign Keys

Self-Join Syntax

View Data That Generally Does Not Meet a Join Condition by Using Outer Joins

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

For the Record: Oracle Outer Join Syntax: (+)

9 Using Subqueries to Solve Queries

Define Subqueries

Describe the Types of Problems Subqueries Can Solve

Describe the Types of Subqueries

Query Data Using Correlated Subqueries

Update and Delete Rows Using Correlated Subqueries

UPDATE with a Correlated Subquery

DELETE with a Correlated Subquery

Use the EXISTS and NOT EXISTS Operators

Use the WITH Clause

Write Single-Row and Multiple-Row Subqueries

Single-Row Subqueries

Multiple-Row Subqueries

10 Managing Schema Objects

Describe How Schema Objects Work

Tables

Constraints

Views

Indexes

Sequences

Create Simple and Complex Views with Visible/Invisible Columns

Creating Views

Updatable Views

Inline Views

ALTER VIEW

Visible/Invisible Columns

Create, Maintain, and Use Sequences

Creating and Dropping Sequences

Using Sequences

Create and Maintain Indexes Including Invisible Indexes and Multiple Indexes on the Same Columns

The Oracle Database Optimizer

Implicit Index Creation

Single Column

Composite

Unique

Dropping

Visible and Invisible Indexes

Index Alternatives on the Same Column Set

Perform Flashback Operations

Overview

Recover Dropped Tables

Recovering Data Within Existing Tables over Time

Marking Time

11 Using the Set Operators

Describe Set Operators

Use a Set Operator to Combine Multiple Queries into a Single Query

UNION

UNION ALL

INTERSECT

MINUS

Combinations

Control the Order of Rows Returned

ORDER BY—By Position

ORDER BY—By Reference

12 Managing Objects with Data Dictionary Views

Query Various Data Dictionary Views

Structure

Dynamic Performance Views

Reading Comments

Adding Comments

DICTIONARY

Identifying a User’s Owned Objects

Inspecting Tables and Columns

Compiling Views

Checking Privileges

Inspecting Constraints

Finding Columns

13 Manipulating Large Data Sets

Describe the Features of Multitable INSERTs

Use the Following Types of Multitable INSERTS: Unconditional and Conditional

Merge Rows into a Table

14 Controlling User Access

Differentiate System Privileges from Object Privileges

System Privileges

Prerequisites

GRANT and REVOKE

ANY

ADMIN OPTION

ALL PRIVILEGES

PUBLIC

Grant Privileges on Tables and on a User

Schema Prefixes

WITH GRANT OPTION

REVOKE

ALL PRIVILEGES

Dependent Privileges

View Privileges in the Data Dictionary

Grant Roles

Distinguish Between Privileges and Roles

View Complete Course outline

Daily schedule

The scheme below shows what a typical day at Training Dragon looks like.

10:00 11:30 11:45 13:00 14:00 15:30 15:45
Classroom Activity
This course explains how to create web pages from scratch using different techniques, such as
Tea
Break
Classroom Activity
This course explains how to create web pages from scratch using different techniques, such as
Lunch
Break
Classroom Activity
This course explains how to create web pages from scratch using different techniques, such as
Tea
Break
Classroom Activity
This course explains how to create web pages from scratch using different techniques, such as
Video Placeholder

Looking for a
2-12 months
training?

Choose

Database Administrator career programme

Our Trainers

Emiliano
Emiliano
Leads our teaching team with many years of experience in teaching web development.
Ross
Ross
Brings many years of Python and Java software development experience to classrooms.
Olu
Olu
Microsoft and Oracle certified developer with years teaching experience in Android, .NET and databases.

Latest Reviews

Frequently Asked Questions

Have a question?
We are here to answer them