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

Generating and Updating XML Documents

• ✔️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

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

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 by using self join

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

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
• ✔️Describe how constraints can be created at table creation
• ✔️Describe how schema objects work

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

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

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

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 function
• ✔️Perform arithmetic with DATE data
• ✔️Manipulate date with the DATE function

Aggregate Data Using the Group Functions

• ✔️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

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

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

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

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

Manipulate Large Data Sets

• ✔️Use Sub queries to Manipulate Data
• ✔️Retrieve Data Using a Sub query as Source
• ✔️Insert Using Sub query as a Target
• ✔️Usage of WITH CHECK OPTION Keyword on DML Statements
• ✔️List the types of Multi table INSERT Statements
• ✔️Use Multi table INSERT Statements
• ✔️Merge rows in a table
• ✔️Track Changes in Data over a period of time

Retrieve Data Using Sub-queries

• ✔️Multiple-Column Sub-queries
• ✔️Pairwise and Non-pairwise Comparison
• ✔️Scalar Sub query Expressions
• ✔️Solve problems with Correlated Sub-queries
• ✔️Update and Delete Rows Using Correlated Sub-queries
• ✔️The EXISTS and NOT EXISTS operators
• ✔️Invoke the WITH clause
• ✔️The Recursive WITH clause