---------------------------------

Oracle official guidelines

---------------------------------

Exam Title: Oracle Database SQL

Exam Number: 1Z0-071

Format: Multiple Choice

Duration: 120 minutes

Number of Questions: 78

Passing Score: 63%

Validated Against: This exam is validated against 11g Release 2 version 11.2.0.1.0 and up to 19c

---------------------------------------

Note: Questions are prepared by an Oracle Certified Professional (OCP) having 17 years IT experience in SQL. All questions are verified/tested thoroughly. Questions covers below syllabus as prescribed by Oracle Corp.

----------------------------------------

ORACLE UNIVERSITY COURSE (1Z0-071)

Relational Database concepts

• Explaining the theoretical and physical aspects of a relational database

• Relating clauses in SQL Select Statement to Components of an ERD

• Explaining the relationship between a database and SQL

Restricting and Sorting Data

• Applying Rules of precedence for operators in an expression

• Limiting Rows Returned in a SQL Statement

• Using Substitution Variables

• Using the DEFINE and VERIFY commands

• Sorting Data

Using Conversion Functions and Conditional Expressions

• Applying the NVL, NULLIF, and COALESCE functions to data

• Understanding implicit and explicit data type conversion

• Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

• Nesting multiple functions

Displaying Data from Multiple Tables

• Using Self-joins

• Using Various Types of Joins

• Using Non equijoins

• Using OUTER joins

• Understanding and Using Cartesian Products

Using SET Operators

• Matching the SELECT statements

• Using the ORDER BY clause in set operations

• Using The INTERSECT operator

• Using The MINUS operator

• Using The UNION and UNION ALL operators

Managing Indexes Synonyms and Sequences

• Managing Indexes

• Managing Synonyms

• Managing Sequences

Managing Views

• Managing Views

Managing Objects with Data Dictionary Views

• Using data dictionary views

Retrieving Data using the SQL SELECT Statement

• Using Column aliases

• Using The SQL SELECT statement

• Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

• Using Arithmetic expressions and NULL values in the SELECT statement

Using Single-Row Functions to Customize Output

• Manipulating strings with character functions in SQL SELECT and WHERE clauses

• Performing arithmetic with date data

• Manipulating numbers with the ROUND, TRUNC and MOD functions

• Manipulating dates with the date function

Reporting Aggregated Data Using Group Functions

• Restricting Group Results

• Creating Groups of Data

• Using Group Functions

Using Subqueries to Solve Queries

• Using Single Row Subqueries

• Using Multiple Row Subqueries

• Update and delete rows using correlated subqueries

Managing Tables using DML statements

• Managing Database Transactions

• Controlling transactions

• Perform Insert, Update and Delete operations

• Performing multi table Inserts

• Performing Merge statements

Use DDL to manage tables and their relationships

• Describing and Working with Tables

• Describing and Working with Columns and Data Types

• Creating tables

• Dropping columns and setting column UNUSED

• Truncating tables

• Creating and using Temporary Tables

• Creating and using external tables

• Managing Constraints

• Controlling User Access

• Differentiating system privileges from object privileges

• Granting privileges on tables

• Distinguishing between granting privileges and roles

Managing Data in Different Time Zones

• Working with CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP

• Working with INTERVAL data types