Oracle Database: Introduction to SQL NEW
What you will learn
This course is available in Training On
Demand format
Audience
Application
Developers Business Analysts
Data
Warehouse Administrator Developer
Forms
Developer PL/SQL Developer System Analysts
Related Training
Required
Prerequisites
Familiarity
with data processing concepts and techniques Data processing
Course Objectives
o Identify the major structural components of the Oracle
Database 12c Create reports of aggregated data
o Write SELECT statements that include queries Retrieve row
and column data from tables
o Run data manipulation statements (DML) in Oracle Database
12c Create tables to store data
o
Utilize views to
display data
o Control database access to specific objects Manage schema objects
o Display data from multiple tables
using the ANSI SQL 99 JOIN syntax Manage objects
with data dictionary views
o
Write multiple-column sub-queries
o
Employ
SQL functions to retrieve customized data
o
Use
scalar and correlated sub-queries
o
Create reports of
sorted and restricted data
Course Topics Introduction to Oracle Database
o
List the features of
Oracle Database 12c
o Discuss the basic design, theoretical, and physical
aspects of a relational database
o Categorize the different types of SQL statements Describe
the data set used by the course
o Log on to the database using SQL Developer environment
Save queries to files and use script files in SQL Developer
Retrieve Data using the SQL SELECT Statement
o
List the capabilities
of SQL SELECT statements
o Generate a report of data from the output of a basic
SELECT statement Select All Columns
o
Select Specific
Columns
o Use Column Heading Defaults Use Arithmetic Operators
Understand Operator Precedence
o
Learn the DESCRIBE
command to display the table structure
Learn to Restrict and Sort Data
o Write queries that contain a WHERE clause to limit the
output retrieved List the comparison operators and logical operators that are
used in a WHERE clause
o Describe the rules
of precedence for
comparison and logical
operators Use character string literals in the WHERE clause
o Write queries that contain an ORDER BY clause to sort the
output of a SELECT statement
o
Sort output in
descending and ascending order
Usage of Single-Row Functions to Customize Output
o Describe the differences between single row and multiple
row functions Manipulate strings with character function in the SELECT and
WHERE clauses
o Manipulate numbers with the ROUND, TRUNC, and MOD
functions Perform arithmetic with date data
o
Manipulate dates with
the DATE functions
Invoke Conversion
Functions and Conditional Expressions
o
Describe implicit and
explicit data type conversion
o Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion
functions Nest multiple functions
o Apply the NVL, NULLIF, and COALESCE functions to data Use
conditional IF THEN ELSE logic in a SELECT statement
Aggregate Data Using the
Group Functions
o Use the aggregation functions to produce meaningful
reports Divide the retrieved data in groups by using the GROUP BY clause
Exclude groups of data by using the HAVING
clause
Display Data From
Multiple Tables Using Joins
o Write SELECT statements to access data from more than one
table View data that generally does not meet a join condition by using outer joins
o
Join a table to itself
by using a self join
Use Sub-queries to Solve
Queries
o Describe the types of problem that sub-queries can solve
Define sub-queries
o List the types of
sub-queries
o
Write single-row and
multiple-row sub-queries
The SET Operators
o
Describe the SET
operators
o Use a SET operator to combine multiple queries into a
single query Control the order of rows
returned
Data Manipulation Statements
o Describe each DML statement Insert rows into a table
o Change rows in a table by the UPDATE statement Delete rows
from a table with the DELETE statement
o Save and discard changes with the COMMIT and ROLLBACK
statements Explain read consistency
Use of DDL Statements to
Create and Manage Tables
o Categorize the main database objects Review the table
structure
o List the data types available for columns Create a simple
table
o Decipher how constraints can be created
at table creation Describe how schema
objects work
Other Schema Objects
o Create a simple and complex view Retrieve data from views
o Create, maintain, and use sequences Create and maintain
indexes
o
Create private and
public synonyms
Control User Access
o Differentiate system privileges from object privileges Create Users
o
Grant System
Privileges
o Create and Grant Privileges to a Role Change Your Password
o Grant Object Privileges How to pass on privileges? Revoke
Object Privileges
Management of Schema Objects
o Add, Modify, and Drop a Column Add, Drop, and Defer a
Constraint
o How to enable and Disable a Constraint? Create and Remove
Indexes
o Create a Function-Based Index Perform Flashback Operations
o Create an External Table by Using ORACLE_LOADER and by
Using ORACLE_DATAPUMP
o
Query External Tables
Manage Objects with Data Dictionary Views
o Explain the data dictionary Use the Dictionary Views
o
USER_OBJECTS and
ALL_OBJECTS Views
o
Table and Column
Information
o
Query the dictionary
views for constraint information
o Query the dictionary views for view, sequence, index
and synonym information
o
Add a comment to a table
o
Query the dictionary
views for comment information
Manipulate Large Data Sets
o Use Subqueries to Manipulate Data Retrieve Data Using a
Subquery as Source Insert Using a Subquery as a Target
o Usage of the WITH CHECK OPTION Keyword on DML Statements
List the types of Multitable INSERT Statements
o Use Multitable INSERT Statements Merge rows in a table
o
Track Changes in Data
over a period of time
Data Management in different Time Zones
o
Time Zones
o
CURRENT_DATE,
CURRENT_TIMESTAMP, and LOCALTIMESTAMP
o Compare Date and Time in a Session’s Time Zone DBTIMEZONE and
SESSIONTIMEZONE
o Difference between DATE and TIMESTAMP INTERVAL Data Types
o
Use EXTRACT, TZ_OFFSET
and FROM_TZ
o
Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
Retrieve Data Using
Sub-queries
o
Multiple-Column
Subqueries
o Pairwise and Nonpairwise Comparison Scalar Subquery
Expressions
o
Solve problems with
Correlated Subqueries
o Update and Delete Rows Using Correlated Subqueries The
EXISTS and NOT EXISTS operators
o Invoke the WITH clause The Recursive WITH clause
Regular Expression
Support
o Use the Regular Expressions Functions and Conditions in
SQL Use Meta Characters with Regular Expressions
o Perform a Basic Search using the REGEXP_LIKE function Find
patterns using the
REGEXP_INSTR function
o
Extract Substrings
using the REGEXP_SUBSTR function Replace Patterns Using the REGEXP_REPLACE
function Usage of Sub-Expressions with Regular Expression Support
Implement the REGEXP_COUNT
function
المشاركات
0 التعليقات:
إرسال تعليق