12c OCA SQL 1Z0-071

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

  • Restricting and Sorting Data

    • Use the ORDER BY clause to sort SQL query results – POST
    Limit the rows that are retrieved by a query – POST
    Use ampersand substitution to restrict and sort output at runtime – POST I

    Use SQL row limiting clause – POST

  • Using Single-Row Functions to Customize Output

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

  • Using Conversion Functions and Conditional Expressions

    • Describe various types of conversion functions that are available in SQL – POST
    Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions – POST I, POST II, POST III, POST IV
    Apply general functions and conditional expressions in a SELECT statement – POST

  • Reporting Aggregated Data Using the Group Functions

    • Describe the use of 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

    • Describe the different types of joins and their features – POST
    Use SELECT statements to access data from more than one table using equijoins and nonequijoins – POST I, POST II
    Join a table to itself by using a self-join – POST
    View data that generally does not meet a join condition by using outer joins – POST

  • Using Subqueries to Solve Queries

    • Define subqueries – POST
    Describe the types of problems subqueries can solve – POST
    Describe the types of subqueries – POST
    Query data using correlated subqueries – POST
    Update and delete rows using correlated subqueries – POST
    Use the EXISTS and NOT EXISTS operators – POST
    Use the WITH clause – POST
    Use single-row and multiple-row subqueries – POST

  • Using the Set Operators

    • Describe 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

    • Truncate data – 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

    • Describe data types that are available for columns – POST
    Create a simple table – POST
    Create constraints for tables – POST I, POST II,
    Drop columns and set column UNUSED – POST I, POST II
    Create and use external tables – 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
    Distinguish between privileges and roles – POST

  • Managing Schema Objects

    • Describe how schema objects work – POST
    Create simple and complex views with visible/invisible columns – POST I, POST II
    Create, maintain and use sequences – POST
    Create and maintain indexes including invisible indexes and multiple indexes on the same columns – POST I, POST II
    Perform flashback operations – POST I, POST II

  • Manipulating Large Data Sets

    • Describe the features of multitable INSERTs – POST
    Merge rows in a table – POST