Demonstrate understanding of fundamental SQL concepts needed to undertake any database project. Provide depth of knowledge of SQL and its use when working with the Oracle Database server. Gain a working knowledge of queries , insert, update and delete SQL statements as well as some Data Definition language and Data Control Language, the optimizer, tales and indexes, data modeling and normalization. By taking this course, a individual proves fluency in and a solid understanding of SQL language, data modeling and using SQL to create and manipulate tables in an Oracle Database.

At the end of this course you will be able 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

Who Should do this course?

·         Application Developers

·         Data Warehouse Developer

·         Database Administrators

·         Developer

Prepare to pass exam: 1Z0-071

Course Outline

Introduction to Oracle Database

          ·         List the features of Oracle Database 12c

·         Discuss the basic design, theoretical, and physical aspects of a relational database

·         Categorize the different types of SQL statements

·         Describe the data set used by the course

·         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

·         List the capabilities of SQL SELECT statements

·         Generate a report of data from the output of a basic SELECT statement

·         Select All Columns

·         Select Specific Columns

·         Use Column Heading Defaults

·         Use Arithmetic Operators

·         Understand Operator Precedence

·         Learn the DESCRIBE command to display the table structure

Learn to Restrict and Sort Data

·         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

·         Describe the rules of precedence for comparison and logical operators

·         Use character string literals in the WHERE clause

·         Write queries that contain an ORDER BY clause to sort the output of a SELECT statement

·         Sort output in descending and ascending order

Usage of Single-Row Functions to Customize Output

·         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

Invoke Conversion Functions and Conditional Expressions

·         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

Aggregate Data Using the Group Functions

·         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

·         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

·         Join a table to itself by using a self join

Use Sub-queries to Solve Queries

·         Describe the types of problem that sub-queries can solve

·         Define sub-queries

·         List the types of sub-queries

·         Write single-row and multiple-row sub-queries

The SET Operators

·         Describe the SET operators

·         Use a SET operator to combine multiple queries into a single query

·         Control the order of rows returned

Data Manipulation Statements

·         Describe each DML statement

·         Insert rows into a table

·         Change rows in a table by the UPDATE statement

·         Delete rows from a table with the DELETE statement

·         Save and discard changes with the COMMIT and ROLLBACK statements

·         Explain read consistency

Use of DDL Statements to Create and Manage Tables

·         Categorize the main database objects

·         Review the table structure

·         List the data types available for columns

·         Create a simple table

·         Decipher how constraints can be created at table creation

·         Describe how schema objects work

Other Schema Objects

·         Create a simple and complex view

·         Retrieve data from views

·         Create, maintain, and use sequences

·         Create and maintain indexes

·         Create private and public synonyms

Control User Access

·         Differentiate system privileges from object privileges

·         Create Users

·         Grant System Privileges

·         Create and Grant Privileges to a Role

·         Change Your Password

·         Grant Object Privileges

·         How to pass on privileges?

·         Revoke Object Privileges

Management of Schema Objects

·         Add, Modify, and Drop a Column

·         Add, Drop, and Defer a Constraint

·         How to enable and Disable a Constraint?

·         Create and Remove Indexes

·         Create a Function-Based Index

·         Perform Flashback Operations

·         Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP

·         Query External Tables

Manage Objects with Data Dictionary Views

·         Explain the data dictionary

·         Use the Dictionary Views

·         USER_OBJECTS and ALL_OBJECTS Views

·         Table and Column Information

·         Query the dictionary views for constraint information

·         Query the dictionary views for view, sequence, index and synonym information

·         Add a comment to a table

·         Query the dictionary views for comment information

Manipulate Large Data Sets

·         Use Subqueries to Manipulate Data

·         Retrieve Data Using a Subquery as Source

·         Insert Using a Subquery as a Target

·         Usage of the WITH CHECK OPTION Keyword on DML Statements

·         List the types of Multitable INSERT Statements

·         Use Multitable INSERT Statements

·         Merge rows in a table

·         Track Changes in Data over a period of time

Data Management in different Time Zones

·         Time Zones

·         CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP

·         Compare Date and Time in a Session’s Time Zone

·         DBTIMEZONE and SESSIONTIMEZONE

·         Difference between DATE and TIMESTAMP

·         INTERVAL Data Types

·         Use EXTRACT, TZ_OFFSET and FROM_TZ

·         Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries

·         Multiple-Column Subqueries

·         Pairwise and Nonpairwise Comparison

·         Scalar Subquery Expressions

·         Solve problems with Correlated Subqueries

·         Update and Delete Rows Using Correlated Subqueries

·         The EXISTS and NOT EXISTS operators

·         Invoke the WITH clause

·         The Recursive WITH clause

Preparation of 1z0-071