Notes

(Register for a free Oracle Web account) Request Training United States
Training Search
Training Search
SearchTraining



Certification

Community

Oracle CloudHelp
Cart
Home1.800.529.0165
Oracle Database SQL Expert New & Upcoming Releases Print this Exam
Exam Number: 1Z0-047
Associated Certifications: Oracle Database SQL Certified Expert
Exam Product Version: SQL and PL/SQL
Exam Price: US$ 245 More on exam pricing
Duration: 90 minutes
Number of Questions: 60

Passing Score: 66% View passing score policy
Validated Against:
This exam has been validated against Oracle Database 10g, Oracle Database 11g, Oracle Database 11g Release 2, and Oracle Database 12c Release 1.

Exam Format: Multiple Choice
Registration:
Purchase exam voucher from Oracle University
Register for exam at PearsonVue or locate a test center near you





Certification Value Packages: Save up to 20% on training and exams with this all-inclusive package.


Exam PreparationExam Topics
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
Using the Set Operators

Explain set operators
Use a set operator to combine multiple queries into a single query
Control the order of rows returned
Manipulating Data

Describe the DML statements
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
Describe the data types that are available for columns
Create tables
Create constraints for tables
Describe how schema objects work
Truncate tables, and recursively truncate child tables
Use 12c enhancements to the DEFAULT clause, invisible columns, virtual columns and identity columns in table creation/alteration
Creating Other Schema Objects

Create simple and complex views with visible/invisible columns
Retrieve data from views
Create, maintain and use sequences
Create private and public synonyms
Managing Objects with Data Dictionary Views

Query various data dictionary views
Controlling User Access

Differentiate system privileges from object privileges
Grant privileges on tables and on a user
View privileges in the data dictionary
Grant roles
Distinguish between privileges and roles
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
Managing Data in Different Time Zones

Use various datetime functions
TZ_OFFSET
FROM_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_DSINTERVAL
CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
DBTIMEZONE
SESSIONTIMEZONE
EXTRACT
Retrieving Data Using Subqueries

Use multiple-column subqueries
Use scalar subqueries
Use correlated subqueries
Update and delete rows using correlated subqueries
Use the EXISTS and NOT EXISTS operators
Use the WITH clause
Hierarchical Retrieval

Interpret the concept of a hierarchical query
Create a tree-structured report
Format hierarchical data
Exclude branches from the tree structure
Regular Expression Support

Use meta Characters
Use regular expression functions to search, match and replace
Use replacing patterns
Use regular expressions and check constraints



WHY CHOOSE ORACLE UNIVERSITY
100% Student Satisfaction
Why Get Oracle Certified
Certification Benefits
TRAINING & CERTIFICATION
Training By Product
Certification By Product
Training By Format
Training By Job Role
Practice Exams
Discount Packages
Learning Paths
CONTACTS & COMMUNITIES
Contact Oracle University
Contact Oracle Certification Support
Certification Help Centre
Community
TERMS & POLICIES
Frequently Asked Questions
Certification Polices & Agreements
Request Training
CONTACT AND CHAT
Global Contacts
Oracle Support
Phone: 1.800.529.0165
Hardware and Software, Engineered to Work Together
SubscribeCareers Contact Us Site Maps Legal Notices Terms of Use Cookie PreferencesOracle MobileTerms and Conditions |