Oracle 1Z0-047

Free Questions in OTE format

About Oracle 1Z0-047 dump

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