1Z0-071: Oracle Database SQL

Exam Number: 1Z0-071

Exam Title: Oracle Database SQL

Associated Certification Paths

Oracle 1Z0-071 Certification Details:

Exam Name Oracle Database SQL
Exam Code 1Z0-071
Exam Product Version SQL and PL/SQL
Exam Price USD $245
Duration 100 minutes
Number of Questions 73
Passing Score 63%
Validated Against This exam was validated against 11g Release 2 version and
up to 12c Release 1 version
Format Multiple Choice
Recommended Practice 1Z0-071 Online Practice Exam
Recommended Training Oracle Database 12c: Introduction to SQL or
Oracle Database: Introduction to SQL

Oracle 1Z0-071 Certification Topics:

Oracle and Structured Query Language (SQL) 
  • Identify the connection between an ERD and a Relational Database
  • Explain the relationship between a database and SQL
  • Describe the purpose of DDL 
  • Describe the purpose of  DML 
  • Build a SELECT statement to retrieve data from an Oracle Database table
Restricting and Sorting Data 
  • Use the ORDER BY clause to sort SQL query results
  • Limit the rows that are retrieved by a query
  • Use ampersand substitution to restrict and sort output at runtime
  • Use SQL row limiting clause
Using Single-Row Functions to Customize Output
  • Use various types of functions available in SQL
  • Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements
Using Conversion Functions and Conditional Expressions 
  • Describe various types of conversion functions that are available in SQL
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Apply general functions and conditional expressions in a SELECT statement
Reporting Aggregated Data Using the 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 
  • Describe the different types of joins and their features
  • Use 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
Using Subqueries to Solve Queries 
  • Define subqueries
  • Describe the types of problems subqueries can solve
  • Describe the types of subqueries
  • Query data using correlated subqueries
  • Update and delete rows using correlated subqueries
  • Use the EXISTS and NOT EXISTS operators
  • Use the WITH clause
  • Use single-row and multiple-row subqueries
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
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
Manipulating Data 
  • Truncate data
  • 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 
  • Describe data types that are available for columns
  • Create a simple table
  • Create constraints for tables
  • Drop columns and set column UNUSED
  • Create and use external tables
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
  • Distinguish between privileges and roles
Managing Schema Objects 
  • Describe how schema objects work
  • Create simple and complex views with visible/invisible columns
  • Create, maintain and use sequences
  • Create and maintain indexes including invisible indexes and multiple indexes on the same columns
  • Perform flashback operations
Manipulating Large Data Sets
  • Describe the features of multitable INSERTs
  • Merge rows in a table

Oracle 1Z0-071 - Oracle Database SQL Exam Preparation: