Oracle SQL Certification

SQL Syllabus Content

Retrieving Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
• Describe how schema objects work

Restricting and Sorting Data

•Limit the rows that are retrieved by a query
•Sort the rows that are retrieved by a query

Using Single-Row Functions to Customize Output

• Describe various types of functions that are available in SQL
• Use character, number, and date functions in SELECT statements
• Describe the use of conversion functions

Displaying Data from Multiple Tables

• Write SELECT statements to access data from more than one table using equijoins and nonequijoins
• 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 all rows from two or more tables

Manipulating Data

• Describe each data manipulation language (DML) statement
• Insert rows into a table
Update rows in a table
Delete rows from a table
• Control transactions

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

Using the Set Operators

• Describe set operators
• Use a set operator to combine multiple a single query

• Control the order of rows returned

Hierarchical Retrieval

• Interpret the concept of a hierarchical query
• Create a tree-structured report

• Format hierarchical data
• Exclude branches from the tree structure

Managing Objects with Data Dictionary Views

• Use the data dictionary views to research data on your objects
• Query various data dictionary views

Creating Other Schema Objects

• Create simple and complex views
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Generating Reports by Grouping Related Data

• Use the ROLLUP operation to produce subtotal values
• Use the CUBE operation to produce cross tabulation values

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

Retrieving Data Using Subqueries

• Write a multiple-column subquery
• Use scalar subqueries in SQL

• Solve problems with correlated subqueries

• Update and delete rows using correlated subqueries

• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause

Manipulating Large Data Sets

• Manipulate data using subqueries
• Describe the features of multitable INSERTs
Use the following types of multitable INSERTs (Unconditional, • Conditional and Pivot)
• Merge rows in a table
• Track the changes to data over a period of time

Using Subqueries to Solve Queries

• Define subqueries
• Describe the types of problems that subqueries can solve
• List the types of subqueries
• Write single-row and multiple-row subqueries

Managing Data in Different Time Zones

• Use Various datetime functions

Reporting Aggregated Data Using the Group Functions

• Identify the available 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

Managing Schema Objects

• Add constraints
• Create indexes
• Create indexes using the CREATE TABLE statement
•Creating function-based indexes
• Drop columns and set column UNUSED
• Perform FLASHBACK operations
• Create and use external tables

Using DDL Statements to Create and Manage Tables

• 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

Share This Post: