SQL Expert 1Z0-047

  • Restricting and Sorting Data

    • Limit the rows that are retrieved by a query – POST
    Sort the rows that are retrieved by a query – POST
    Use substitution variables – POST
    Use the SQL row limiting clause – POST
    Create queries using the PIVOT and UNPIVOT clause – POST
    Use pattern matching to recognize patterns across multiple rows in a table – POST

  • Using Single-Row Functions to Customize Output

    • Describe various types of functions that are available in SQL – POST
    Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements – POST, POST
    Use conversion functions – POST I, POST II, POST III, POST IV

  • Reporting Aggregated Data Using the Group Functions

    • Identify the available group functions – POST
    Use group functions – POST
    Group data by using the GROUP BY clause – POST
    Include or exclude grouped rows by using the HAVING clause – POST

  • Displaying Data from Multiple Tables

    • Use equijoins and nonequijoins – POST I, POST II, POST III
    Use a self-join – POST
    Use outer joins – POST
    Generate a Cartesian product of all rows from two or more tables – POST
    Use the cross_outer_apply_clause – POST

  • Using Subqueries to Solve Queries

    • Use subqueries – POST
    Use single-row and multiple-row subqueries – POST
    Create a lateral inline view in a query – POST

  • Using the Set Operators

    • Explain set operators – POST
    Use a set operator to combine multiple queries into a single query – POST
    Control the order of rows returned – POST

  • Manipulating Data

    • Describe the DML statements – POST
    Insert rows into a table – POST
    Update rows in a table – POST
    Delete rows from a table – POST
    Control transactions – POST

  • Using DDL Statements to Create and Manage Tables

    • Categorize the main database objects – POST
    Review the table structure – POST
    Describe the data types that are available for columns – POST
    Create tables – POST
    Create constraints for tables – POST I, POST II
    Describe how schema objects work – POST
    Truncate tables, and recursively truncate child tables – POST
    Use 12c enhancements to the DEFAULT clause, invisible columns, virtual columns and identity columns in table creation/alteration – POST

  • Creating Other Schema Objects

    • Create simple and complex views with visible/invisible columns – POST
    Retrieve data from views – POST
    Create, maintain and use sequences – POST
    Create private and public synonyms – POST

  • Managing Objects with Data Dictionary Views

    • Query various data dictionary views – POST

  • Controlling User Access

    • Differentiate system privileges from object privileges – POST
    Grant privileges on tables and on a user – POST
    View privileges in the data dictionary – POST
    Grant roles – POST
    Distinguish between privileges and roles – POST

  • Managing Schema Objects

    • Manage constraints – POST
    Create and maintain indexes including invisible indexes and multiple indexes on the same columns – POST
    Create indexes using the CREATE TABLE statement – POST
    Create function-based indexes – POST
    Drop columns and set column UNUSED – POST I, POST
    Perform flashback operations – POST
    Create and use external tables – POST

  • Manipulating Large Data Sets

    • Manipulate data using subqueries – POST
    Describe the features of multitable INSERTs – POST
    Use multitable inserts – POST
    Unconditional INSERT – POST
    Pivoting INSERT – POST
    Conditional ALL INSERT – POST
    Conditional FIRST INSERT – POST
    Merge rows in a table – POST
    Track the changes to data over a period of time – POST
    Use explicit default values in INSERT and UPDATE statements – POST

  • Generating Reports by Grouping Related Data

    • Use the ROLLUP operation to produce subtotal values – POST
    Use the CUBE operation to produce crosstabulation values – POST
    Use the GROUPING function to identify the row values created by ROLLUP or CUBE – POST
    Use GROUPING SETS to produce a single result set – POST

  • 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 – POST

  • Retrieving Data Using Subqueries

    • Use multiple-column subqueries – POST
    Use scalar subqueries – POST
    Use correlated subqueries – POST
    Update and delete rows using correlated subqueries – POST
    Use the EXISTS and NOT EXISTS operators – POST
    Use the WITH clause – POST

  • Hierarchical Retrieval

    • Interpret the concept of a hierarchical query – POST
    Create a tree-structured report – POST
    Format hierarchical data – POST
    Exclude branches from the tree structure – POST

  • Regular Expression Support

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