fbpx
09864032319 , 09678176577 datacomes@gmail.com

Oracle Database SQL Expert(1Z0-047)

Course Name:Oracle Database SQL Expert(1Z0-047)

Course Duration:2 months

Course Fees:10,000

Syllabus:

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
  • 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 functions in SELECT statements (Updated to “Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements” September 15, 2014)**
  • 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
  • Use the cross_outer_apply_clause**

Using Subqueries to Solve Queries

  • Define subqueries*
  • Describe the types of problems that subqueries can solve*
  • Use subqueries**
  • List the types of subqueries
  • Write single-row and multiple-row subqueries
  • Create a lateral inline view in a query**

Using the Set Operators

  • Describe set operators
  • Use a set operator to combine multiple queries into 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
  • 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 (Updated to “Create simple and complex views with visible/invisible columns on September 15, 2014)**
  • 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 (Updated to Grant privileges on tables and on a user on September 15, 2014)**
  • View privileges in the data dictionary
  • Grant roles
  • Distinguish between privileges and roles

Managing Schema Objects

  • Add constraints
  • Create indexes (Updated to “Create and maintain indexes including invisible indexes and multiple indexes on the same columns” on September 15, 2014)**
  • 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
  • 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

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

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

  • Use Meta Characters
  • Regular Expression Functions
  • Use Replacing Patterns
  • Use Regular Expressions and Check Constraints
Back To Top