Oracle

Oracle Database: Introduction to SQL

Oracle Database: Introduction to SQL

Share This Learning Path

Need more info? Contact us

This Oracle Database: Introduction to SQL training helps you write subqueries, combine multiple queries into a single query using SET operators and report aggregated data using group functions. This course is a combination of Oracle Database: SQL Workshop I and Oracle Database: SQL Workshop II courses, also this course will help you understand the advanced features of SQL. Learning these features will help you query and manipulate data within the database, use the dictionary views to retrieve metadata and create reports about their schema objects. Some of the date-time functions available in the Oracle Database are also covered. This course also discusses how to use the regular expression support in SQL through expert instruction. The main development tool used in this training is Oracle SQL Developer. SQL*Plus is available as an optional development tool. This is appropriate for a 10g, 11g and 12c audience.

Audience Profile

This course is intended for administrators, analysts, developers, systems administrators who are interested in using Oracle Database.

Prerequisites

  • Familiarity with data processing concepts and techniques.
  • Data processing.

Course outline

Module 1: Introduction

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Course Objectives, Course Agenda and Appendixes Used in this Course
Overview of Oracle Database 12c and Related Products
Overview of relational database management concepts and terminologies
Introduction to SQL and its development environments
What is Oracle SQL Developer ?
Starting SQL*Plus from Oracle SQL Developer
The Human Resource (HR) Schema
Tables used in the Course

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Identify the major structural components of the Oracle Database 12c.

Module 2: Retrieving Data using the SQL SELECT Statement

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Capabilities of the SELECT statement
Arithmetic expressions and NULL values in the SELECT statement
Column aliases
Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
Use of the DESCRIBE command

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

List the capabilities of SQL SELECT statements.
Execute a basic SELECT statement.
Differentiate between SQL statements and iSQL*Plus commands.

Module 3: Restricting and Sorting Data

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Limiting the Rows
Rules of precedence for operators in an expression
Substitution Variables
Using the DEFINE and VERIFY command

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Limit the rows that are retrieved by a query.
Sort the rows that are retrieved by a query.
Use ampersand substitution in iSQL*Plus to restrict and sort output at run time.

Module 4: Using Single-Row Functions to Customize Output

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Describe the differences between single row and multiple row functions
Manipulate strings with character function in the SELECT and WHERE clauses
Manipulate numbers with the ROUND, TRUNC and MOD functions
Perform arithmetic with date data
Manipulate dates with the date functions

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Describe various types of functions that are available in SQL.
Use character, number, and date functions in SELECT statements.
Describe the use of conversion functions.

Module 5: Using Conversion Functions and Conditional Expressions

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Describe implicit and explicit data type conversion
Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nest multiple functions
Apply the NVL, NULLIF, and COALESCE functions to data
Use conditional IF THEN ELSE logic in a SELECT statement

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Describe implicit and explicit data type conversion.
Describe TO_CHAR, TO_NUMBER, and TO_DATE conversion functions.
Apply the NVL, NULLIF, and COALESCE functions to data.
Use conditional IF THEN ELSE logic in a SELECT statement.

Module 6: Reporting Aggregated Data Using the Group Functions

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Group Functions
Creating Groups of Data
Restricting Group Results

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Identify the available group functions.
Describe the use of group functions.
Group data by using the GROUP BY clause.
Include or exclude grouped rows by using the HAVING clause.

Module 7: Displaying Data from Multiple Tables Using Joins

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Introduction to JOINS
Types of Joins
Natural join
Self-join
Non equijoins
OUTER join

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Write SELECT statements to access data from more than one table using equijoins and nonequijoins.
Join a table to itself by using a self-join.
View data that generally does not meet a join condition by using outer joins.
Generate a Cartesian product of all rows from two or more tables.

Module 8: Using Subqueries to Solve Queries

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Introduction to Subqueries
Single Row Subqueries
Multiple Row Subqueries

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Define subqueries.
Describe the types of problems that subqueries can solve.
List the types of subqueries.
Write single-row and multiple-row subqueries.

Module 9: Using the SET Operators

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Set Operators
UNION and UNION ALL operator
INTERSECT operator
MINUS operator
Matching the SELECT statements
Using ORDER BY clause in set operations

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Describe set operators.
Use a set operator to combine multiple queries into a single query.
Control the order of rows returned.

Module 10: Managing Tables using DML statements

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Data Manipulation Language
Database Transactions

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Run data manipulation statements (DML).

Module 11: Introduction to Data Definition Language

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Data Definition Language

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Describe Data Definition Language.

Module 12: Introduction to Data Dictionary Views

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Introduction to Data Dictionary
Describe the Data Dictionary Structure
Using the Data Dictionary views
Querying the Data Dictionary Views

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Describe the Data Dictionary Structure.
Using the Data Dictionary views.

Module 13: Creating Sequences, Synonyms, Indexes

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Overview of sequences
Overview of synonyms
Overview of indexes

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Use a sequence.
Use a synonym.
Use a indexe.

Module 14: Creating Views

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Overview of views

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Creating views.

Module 15: Managing Schema Objects

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Managing constraints
Creating and using temporary tables
Creating and using external tables

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Understand how to creating and using temporary tables.
Understand how to creating and using external tables.

Module 16: Retrieving Data by Using Subqueries

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Retrieving Data by Using a Subquery as Source
Working with Multiple-Column subqueries
Using Scalar subqueries in SQL
Correlated Subqueries
Working with the WITH clause

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Understand how to retrieving data by using subqueries.

Module 17: Manipulating Data by Using Subqueries

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Using Subqueries to Manipulate Data
Inserting by Using a Subquery as a Target
Using the WITH CHECK OPTION Keyword on DML Statements
Using Correlated Subqueries to Update and Delete rows

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Understand how to manipulating data by using subqueries.

Module 18: Controlling User Access

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

System privileges
Creating a role
Object privileges
Revoking object privileges

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Describe how to controlling user access.

Module 19: Manipulating Data

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Overview of the Explicit Default Feature
Using multitable INSERTs
Using the MERGE statement
Performing flashback operations
Tracking Changes in Data

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

Describe each data manipulation language (DML) statement.
Insert rows into a table.
Update rows in a table.
Delete rows from a table.
Control transactions.

Module 20: Managing Data in Different Time Zones

Module Overview

This module gives you the knowledge and skills needed to identify the major structural components of the Oracle Database 12c, create reports of aggregated data, write SELECT statements that include queries, retrieve row and column data from tables, run data manipulation statements (DML) in Oracle Database 12c, create tables to store data, utilize views to display data, control database access to specific objects, manage schema objects, display data from multiple tables using the ANSI SQL 99 JOIN syntax, manage objects with data dictionary views, write multiple-column sub-queries, employ SQL functions to retrieve customized data, use scalar and correlated sub-queries, create reports of sorted and restricted data.

Lessons

Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
Working with INTERVAL data types

Lab Sessions

Not available for this module

Lab Lessons

Lab lessons not available

After completing this module, students will be able to:

How to use CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP.
How to use INTERVAL data types.

Book Your Seat​

Find Learning Paths​

  • Search Paths

  • Vendors

Latest Learning Paths​

Join our community of certified professionals

Sign Up to our newsletter, and stay always up to date with latest IT certifications

About Us

ICTC is the leader in technical certification courses and exams. Our labs consist of a latest tech PCs and our instructors are certified from each vendor

Facebook Linkedin

Learn

View all the provided certifications and there relevant courses. Book online for a certification exam.

Explore

Contact Us

International Computer Training Center

Copyright reserved to ICTC. Proudly Crafted by GTP Works

Choose how to get more info...

Give as a call

211 500 2 900

Let us, call you

Send us an email