Oracle -Syllabus

  1. Introduction to Oracle Database
    1. 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
  2. Retrieve Data using the SQL SELECT Statement
    1. List the capabilities of SQL SELECT statements
      Generate a report of data from the output of a basic
    SELECT statement
      Select Specific Columns
      Use Column Heading Defaults
      Use Arithmetic Operators
      Understand Operator Precedence
      Learn the DESCRIBE command to display the table structure
  3. Learn to Restrict and Sort Data
    1. 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
  4. Usage of Single-Row Functions to Customize Output
    1. 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
  5. Invoke Conversion Functions and Conditional Expressions
    1. 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
  6. Aggregate Data Using the Group Functions
    1. Use the aggregation functions in SELECT statements to produce meaningful reports
      Divide the data in groups by using the GROUP BY clause
      Exclude groups of date by using the HAVING clause
  7. Display Data From Multiple Tables Using Joins
    1. 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 by using a self join
  8. Use Sub-queries to Solve Queries
    1. 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
  9. The SET Operators
    1. Describe the SET operators
      Use a SET operator to combine multiple queries into a single query
      Control the order of rows returned
  10. Data Manipulation Statements
    1. 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
  11. Use of DDL Statements to Create and Manage Tables
    1. 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
  12. Other Schema Objects
    1. Create a simple and complex view
      Retrieve data from views
      Create, maintain, and use sequences
      Create and maintain indexes
      Create private and public synonyms
  13. Control User Access
    1. 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
  14. Management of Schema Objects
    1. 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
  15. Manage Objects with Data Dictionary Views
    1. Use the Dictionary 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
  16. Manipulate Large Data Sets
    1. 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
  17. Data Management in Different Time Zones
    1. Time Zones
      Compare Date and Time in a Session's Time Zone
      Difference between DATE and TIMESTAMP
      INTERVAL Data Types
  18. Retrieve Data Using Sub-queries
    1. 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
  19. Regular Expression Support
    1. 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