Oracle University | Contact Us: Local: 1800 103 4775 Intl: +91 80 40291196

Oracle Database: Introduction to SQL NEW

Duration: 5 Days

What you will learn

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. Learn this and more through hands-on

exercises.

Learn To:

Understand the basic concepts of relational databases ensure refined code by developers.

Create reports of sorted and restricted data.

Run data manipulation statements (DML).

Control database access to specific objects.

Manage schema objects.

Manage objects with data dictionary views.

Retrieve row and column data from tables.

Control privileges at the object and system level.

Create indexes and constraints; alter existing schema objects.

Create and query external tables.

Benefits to You

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver

a higher quality of service by enabling consolidation onto database clouds.

Learn Advanced Features of SQL

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.

Use Development Tools

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 and 11g audience.

Course Bundle

Note: This course is a combination of Oracle Database: SQL Workshop I and Oracle Database: SQL Workshop II

courses.

Audience

Copyright © 2013, Oracle. All rights reserved. Page 1

Application DevelopersBusiness AnalystsData Warehouse AdministratorDeveloperForms DeveloperPL/SQL DeveloperSystem Analysts

Related Training

Required Prerequisites

Familiarity with data processing concepts and techniques

Data processing

Course ObjectivesIdentify 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

Course Topics

Copyright © 2013, Oracle. All rights reserved. Page 2

Introduction to Oracle Database List the features of Oracle Database 12cDiscuss the basic design, theoretical, and physical aspects of a relational databaseCategorize the different types of SQL statementsDescribe the data set used by the courseLog on to the database using SQL Developer environmentSave queries to files and use script files in SQL Developer

Retrieve Data using the SQL SELECT StatementList the capabilities of SQL SELECT statementsGenerate a report of data from the output of a basic SELECT statementSelect All ColumnsSelect Specific ColumnsUse Column Heading DefaultsUse Arithmetic OperatorsUnderstand Operator PrecedenceLearn the DESCRIBE command to display the table structure

Learn to Restrict and Sort DataWrite queries that contain a WHERE clause to limit the output retrievedList the comparison operators and logical operators that are used in a WHERE clauseDescribe the rules of precedence for comparison and logical operatorsUse character string literals in the WHERE clauseWrite queries that contain an ORDER BY clause to sort the output of a SELECT statementSort output in descending and ascending order

Usage of Single-Row Functions to Customize OutputDescribe the differences between single row and multiple row functionsManipulate strings with character function in the SELECT and WHERE clausesManipulate numbers with the ROUND, TRUNC, and MOD functionsPerform arithmetic with date dataManipulate dates with the DATE functions

Invoke Conversion Functions and Conditional ExpressionsDescribe implicit and explicit data type conversionUse the TO_CHAR, TO_NUMBER, and TO_DATE conversion functionsNest multiple functionsApply the NVL, NULLIF, and COALESCE functions to dataUse conditional IF THEN ELSE logic in a SELECT statement

Aggregate Data Using the Group FunctionsUse the aggregation functions to produce meaningful reportsDivide the retrieved data in groups by using the GROUP BY clauseExclude groups of data by using the HAVING clause

Display Data From Multiple Tables Using JoinsWrite SELECT statements to access data from more than one tableView data that generally does not meet a join condition by using outer joinsJoin a table to itself by using a self join

Use Sub-queries to Solve QueriesDescribe the types of problem that sub-queries can solve

Copyright © 2013, Oracle. All rights reserved. Page 3

Define sub-queriesList the types of sub-queriesWrite single-row and multiple-row sub-queries

The SET OperatorsDescribe the SET operatorsUse a SET operator to combine multiple queries into a single queryControl the order of rows returned

Data Manipulation StatementsDescribe each DML statementInsert rows into a tableChange rows in a table by the UPDATE statementDelete rows from a table with the DELETE statementSave and discard changes with the COMMIT and ROLLBACK statementsExplain read consistency

Use of DDL Statements to Create and Manage TablesCategorize the main database objectsReview the table structureList the data types available for columnsCreate a simple tableDecipher how constraints can be created at table creationDescribe how schema objects work

Other Schema ObjectsCreate a simple and complex viewRetrieve data from viewsCreate, maintain, and use sequencesCreate and maintain indexesCreate private and public synonyms

Control User AccessDifferentiate system privileges from object privilegesCreate Users Grant System PrivilegesCreate and Grant Privileges to a RoleChange Your PasswordGrant Object PrivilegesHow to pass on privileges? Revoke Object Privileges

Management of Schema ObjectsAdd, Modify, and Drop a ColumnAdd, Drop, and Defer a ConstraintHow to enable and Disable a Constraint?Create and Remove IndexesCreate a Function-Based IndexPerform Flashback OperationsCreate an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMPQuery External Tables

Copyright © 2013, Oracle. All rights reserved. Page 4

Manage Objects with Data Dictionary ViewsExplain the data dictionaryUse the Dictionary ViewsUSER_OBJECTS and ALL_OBJECTS ViewsTable and Column InformationQuery the dictionary views for constraint informationQuery the dictionary views for view, sequence, index and synonym informationAdd a comment to a table Query the dictionary views for comment information

Manipulate Large Data SetsUse Subqueries to Manipulate DataRetrieve Data Using a Subquery as SourceInsert Using a Subquery as a TargetUsage of the WITH CHECK OPTION Keyword on DML StatementsList the types of Multitable INSERT StatementsUse Multitable INSERT StatementsMerge rows in a tableTrack Changes in Data over a period of time

Data Management in different Time ZonesTime ZonesCURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMPCompare Date and Time in a Session’s Time ZoneDBTIMEZONE and SESSIONTIMEZONEDifference between DATE and TIMESTAMPINTERVAL Data TypesUse EXTRACT, TZ_OFFSET and FROM_TZInvoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queriesMultiple-Column SubqueriesPairwise and Nonpairwise ComparisonScalar Subquery ExpressionsSolve problems with Correlated SubqueriesUpdate and Delete Rows Using Correlated SubqueriesThe EXISTS and NOT EXISTS operatorsInvoke the WITH clauseThe Recursive WITH clause

Regular Expression SupportUse the Regular Expressions Functions and Conditions in SQLUse Meta Characters with Regular ExpressionsPerform a Basic Search using the REGEXP_LIKE functionFind patterns using the REGEXP_INSTR functionExtract Substrings using the REGEXP_SUBSTR functionReplace Patterns Using the REGEXP_REPLACE functionUsage of Sub-Expressions with Regular Expression SupportImplement the REGEXP_COUNT function

Copyright © 2013, Oracle. All rights reserved. Page 5