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