Oracle Database SQL Expert Course

Restricting and Sorting Data

• Limit the rows that are retrieved by a query
• Sort the rows that are retrieved by a query
• Use substitution variables
• Use the SQL row limiting clause
• Create queries using the PIVOT and UNPIVOT clause
• Use pattern matching to recognize patterns across multiple rows in a table

Using Single-Row Functions to Customize Output

• Describe various types of functions that are available in SQL
• Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements
• Use conversion functions

Reporting Aggregated Data Using the Group Functions

• Identify the available group functions
• Use group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

• Use equijoins and nonequijoins
• Use a self-join
• Use outer joins
• Generate a Cartesian product of all rows from two or more tables
• Use the cross_outer_apply_clause

Using Subqueries to Solve Queries

• Use subqueries
• List the types of subqueries
• Use single-row and multiple-row subqueries
• Create a lateral inline view in a query

EXTRACT Managing Schema Objects

• Manage constraints
• Create and maintain indexes including invisible indexes and multiple indexes on the same columns
• 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

Manipulating Large Data Sets

• Manipulate data using subqueries
• Describe the features of multitable INSERTs
• Use multitable inserts
• Unconditional INSERT
• Pivoting INSERT
• Conditional ALL INSERT
• Conditional FIRST INSERT
• Merge rows in a table
• Track the changes to data over a period of time
• Use explicit default values in INSERT and UPDATE statements

Generating Reports by Grouping Related Data

• Use the ROLLUP operation to produce subtotal values
• Use the CUBE operation to produce crosstabulation values
• Use the GROUPING function to identify the row values created by ROLLUP or CUBE
• Use GROUPING SETS to produce a single result set