Oracle's 1Z0-047

1Z0-047 Course/Exam Objectives

• Chapter 1: Retrieving Data Using the SQL SELECT Statement
  • List the capabilities of SQL SELECT statements
  • Execute a basic SELECT statement
  • Describe how schema objects work
• Chapter 2: Restricting and Sorting Data
  • Limit the rows that are retrieved by a query
  • Sort the rows that are retrieved by a query
• Chapter 3: 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
• Chapter 4: 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
• Chapter 5: Displaying Data from Multiple Tables
  • View data that generally does not meet a join condition by using outer joins
  • 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
  • Generate a Cartesian product of all rows from two or more tables
• Chapter 6: 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
• Chapter 7: Using the Set Operators
  • Describe set operators
  • Use a set operator to combine multiple queries into a single query
  • Control the order of rows returned
• Chapter 8: 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
• Chapter 9: 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
• Chapter 10: 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
• Chapter 11: Managing Objects with Data Dictionary Views
  • Use data dictionary views to research data on your objects
  • Query various data dictionary views
• Chapter 12: 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
• Chapter 13: 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
• Chapter 14: Manipulating Large Data Sets
  • Track the changes to data over a period of time
  • 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
• Chapter 15: 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
• Chapter 16: Managing Data in Different Time Zones
  • Use various datetime functions
• Chapter 17: 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
• Chapter 18: Hierarchical Retrieval
  • Interpret the concept of a hierarchical query
  • Create a tree-structured report
  • Format hierarchical data
  • Exclude branches from the tree structure
• Chapter 19: Regular Expression Support
  • Using Metacharacters
  • Regular Expression Functions
  • Replacing Patterns
  • Regular Expressions and Check Constraints

Career Prospects

An Oracle Database: SQL Certified Expert professional has several career opportunities open up to them, including:

• Application Developers
• PL/SQL Developer
• Forms Developer
• Database Administrators
• System Analysts
• Data Warehouse Administrators

1Z0-047 Exam FAQ's