The objective of this course is to:
• Employ SQL functions to generate and retrieve customized data
• Display data from multiple tables using the ANSI SQL 99 JOIN syntax
• Identify the major structural components of the Oracle Database 11g
• Create reports of aggregated data
• Write SELECT statements that include queries
• Retrieve row and column data from tables with the SELECT statement
• Run data manipulation statements (DML) to update data in the Oracle Database 11g
• Create tables to store data
• Utilize views to display and retrieve data
• Control database access to specific objects
• Manage schema objects
• Manage objects with data dictionary views
• Write multiple-column sub-queries
• Use scalar and correlated sub-queries
• Use the regular expression support in SQL
• Create reports of sorted and restricted data
Introduction to Oracle Database 11g
- Features of Oracle Database 11g
- Basic design, theoretical and physical aspects of a relational database
- Different types of SQL statements
- Describe the data set used by the course
- Logging 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
