About Exam

Exam number/code: 1Z0-047
Exam name: Oracle Database SQL Expert
Exam no. of questions: 70 Questions
Duration: 120 minutes
Passing score: 66%
Question types: Multiple choice questions (MCQ's), Multiple choice single answer, Multiple choice multiple answer, Drag and drop & Simulations (optional)

• Retrieving Data Using the SQL SELECT Statement
• Restricting and Sorting Data
• Using Single-Row Functions to Customize Output
• Reporting Aggregated Data Using the Group Functions
• Displaying Data from Multiple Tables
• Using Subqueries to Solve Queries
• Using the Set Operators
• Manipulating Data
• Using DDL Statements to Create and Manage Tables
• Creating Other Schema Objects
• Managing Objects with Data Dictionary Views
• Controlling User Access
• Managing Schema Objects
• Manipulating Large Data Sets
• Generating Reports by Grouping Related Data
• Managing Data in Different Time Zones
• Retrieving Data Using Subqueries
• Hierarchical Retrieval
• Regular Expression Support

Exam Objectives

• 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
• 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
• 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
  
• 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
• Using the Set Operators
• Describe set operators
• Use a set operator to combine multiple a single query
• Control the order of rows returned
• 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
• 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
• 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
• Managing Objects with Data Dictionary Views
• Use the data dictionary views to research data on your objects
• Query various data dictionary views
• 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
• Creating function-based indexes
• Drop columns and set column UNUSED
• Perform FLASHBACK operations
• Create and use external tables
• Manipulating Large Da