Oracle Database 11g: Introduction to SQL

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.
Why Oracle 11 g training from Mapple Edusoft :

• Official Course-ware from Oracle University.
  
• Authorized Structured Oracle Training in Jaipur.
• Highly Qualified Java Industry Experts as Trainer.
• Hands on experience on Oracle Software.
  
• Course Completion Certificate From Oracle University.
  
• Discount Vouchers for International Exams.
• 10 Full-length Online Mock Tests (Real-life Oracle® Exam) per Module.
  

Prerequisites :

• Familiarity with Data Processing Concepts and Techniques
• Ability to use a graphical user interface (GUI)

Course Topics :
Introduction to Oracle Database

• List the features of Oracle Database 11g
  
• 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
  

Regular Expression Support

• Use the Regular Expressions Functions and Conditions in SQL
  
• Use Meta Characters with Regular Expressions
  
• Perform a Basic Search using the REGEXP_LIKE function
  
• Find patterns using the REGEXP_INSTR function
  
• 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
  

Certificate From Oracle University :

Oracle Training in Jaipur - DBA, Developer - OCA -OCP

Authorized Oracle 11g Training in Jaipur @ Mapple Edusoft