Oracle 10g SQL

Oracle Important Vendor Questions

Exam Details : Oracle Database SQL Expert
[Download pdf for vendor exam]

Exam Number: 1Z0-047
Associated Certifications: Oracle Database SQL Certified Expert
Exam Product Version: SQL and PL/SQL
Exam Price: US$ 245
Duration: 120 minutes
Number of Questions: 70
Passing Score: 66%
Validated Against: This exam has been validated against Oracle Database 10g and Oracle Database 11g Release 2 version
Format: Multiple Choice

Exam Topics:

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 Data Sets

  • 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 
  • Track the changes to data over a period of time

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

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

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

  • Using Meta Characters
  • Regular Expression Functions 
  • Replacing Patterns 
  • Regular Expressions and Check Constraints