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

    Esse Blog é reconhecido pela
    Certificações
    Sou articulista
    Sou articulista

    Clique para seguir este blog e receber notificações via email de novos posts.

    Tércio Costa

    Tércio Costa

    Formado em Ciências da Computação pela UFPB com experiência em Servidores Windows Server e Linux e banco de dados Oracle desde 2008 juntamente com os seus serviços. Desenvolvimento de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado Oracle Certified SQL Expert, mantendo o blog https://oraclepress.wordpress.com/ reconhecido pela OTN, articulista na OTN, portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine.

    Links Pessoais

    Serviços verificados

    Visualizar Perfil Completo →

    Total de Visualizações da Página
    • 47,032 Visualizações
    %d blogueiros gostam disto: