This course is a common starting point in the Oracle database curriculum for administrators, developers and business users. The objective of this course is to provide an introduction to the SQL database language within the context of an Oracle database, based upon the latest features available. The primary objective of this course is to consider advanced subjects and techniques pertaining to the SQL database language, based upon the latest features available with the Oracle release. Even professional experienced in other implementations of the industry-standard SQL language will benefit from the advanced and Oracle-specific features of SQL discussed in this course.
Course Objectives
This course will cover the following elements:
- Retrieving data using the SQL select statement
- Restricting and sorting data
- Using single row functions to customize output
- Using conversion functions and conditional expression
- Reporting aggregated data using the group functions
- Displaying data from multiple tables
- Using sub-queries to solve queries
- Using the set operators
- Manipulating data
- Using DDL statements to create and manage tables
- Creating other schema objects
Course Benefits
Upon successful completion of this course students will be able to:
- List the capabilities of SQL select statements
- Execute a basic select statement
- Limit the rows that are retrieved by a query
- Sort the rows that are retrieved by a query
- Use ampersand substitution to restrict and sort output at runtime
- Describe various types of functions available in SQL
- use character, number and date functions in select statements
- Describe various types of conversion functions that are available in SQL
- Use the to_char, to_number and to_date conversion functions
- Apply conditional expressions in a select statement
- Identify the available group functions
- Describe the use of group functions
- Describe the use of group functions
- Group data by using the group by clause
- Include or exclude grouped rows by using the having clause
- Write select statements to access data from more than one table using equijoins and nonequiijoins
- Join a table to itself by using a self-join
- View data that generally does not meet a join condition by using outer joins
- Generate a cartesian product of rows from two or more tables
- Define sub-queries
- Describe the types of problems that the sub-queries can solve
- List the types of sub-queries
- Write single-row and multiple-row sub-queries
- Describe set operators
- Use a set operator to combine multiple queries into a single query
- Control the order of rows returned
- Describe each data manipulation
- Language (DML) statement
- Insert rows into a table
- Update rows in a table
- Delete rows from a table
- Control transactions
- Categorize the main database objects
- Review the table structure
- List the data types that are available for columns
- Create a simple table
- Explain how constraints are created at the time of table creation
- Describe how schema objects work
- Create simple and complex views
- Create, maintain and use sequences
- Create and maintain indexes
- Create private and public synonyms