Introducing Oracle Database 11g
· 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 onto the database using the SQL Developer environment
· Save queries to files and use script files in SQL Developer
Retrieving 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
Restricting and Sorting 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 sort the output of a SELECT statement
· Sort output in descending and ascending order
Using 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
Using 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
Reporting Aggregated Data Using the Group Functions
· Use the aggregation functions in SELECT statements to produce meaningful reports
· Create queries that divide the data in groups by using the GROUP BY clause
· Create queries that exclude groups of date by using the HAVING clause
Displaying Data From Multiple Tables
· 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
Using 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
Using 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 when using the SET operators
Manipulating Data
· Describe each DML statement
· Insert rows into a table with the INSERT statement
· Use the UPDATE statement to change rows in a table
· Delete rows from a table with the DELETE statement
· Save and discard changes with the COMMIT and ROLLBACK statements
· Explain read consistency
Using 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
Creating 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
Controlling User Access
· Differentiate system privileges from object privileges
· Grant privileges on tables
· View privileges in the data dictionary
· Grant roles
· Distinguish between privileges and roles
Managing Schema Objects
· Add constraints
· Create indexes
· Create indexes using the CREATE TABLE statement
· Create function-based indexes
· Drop columns and set column UNUSED
· Perform FLASHBACK operations
· Create and use external tables
Managing Objects with Data Dictionary Views
· Explain the data dictionary
· Find table information
· Report on column information
· View constraint information
· Find view information
· Verify sequence information
· Understand synonyms
· Add comments
Manipulating Large Data Sets
· Manipulate data using sub-queries
· Describe the features of multi-table inserts
· Use the different types of multi-table inserts
· Merge rows in a table
· Track the changes to data over a period of time
Managing Data in Different Time Zones
· Use data types similar to DATE that store fractional seconds and track time zones
· Use data types that store the difference between two date-time values
· Practice using the multiple data-time functions for globalize applications
Retrieving Data Using Sub-queries
· Write a multiple-column sub-query
· Use scalar sub-queries in SQL
· Solve problems with correlated sub-queries
· Update and delete rows using correlated sub-queries
· Use the EXISTS and NOT EXISTS operators
· Use the WITH clause
Regular Expression Support
· List the benefits of using regular expressions
· Use regular expressions to search for, match, and replace strings.
