SQL Expert

PREREQUISITE:

• Candidate must possess knowledge and skills in data processing.
• Candidate must be familiar with data processing concepts and techniques.

COURSE OBJECTIVES:

In this course training, participants will learn the proper way of using single row functions to customize output and applying conversion functions and conditional expressions. The application of group functions in reporting aggregated data is also covered in detail. Through our practical, hands-on exercises, participants will be reinforced in the course’s fundamental concepts.

COURSE CURRICULUM:

1. Retrieving Data Using the SQL SELECT Statement, from a single table
   • Define the capabilities of SQL SELECT statements
   • Perform a basic SELECT statement
   • Define how schema objects work
2. Restricting and Sorting Data in Various Ways
   • Limit the rows that are recovered by a query
   • Arrange the rows that are recovered by a query
3. Usage of Single-Row Functions to Customize Output
   • Distinguish the different types of functions that are available in SQL
   • Make us of character, number, and date functions in SELECT statements
   • Define the role of conversion functions in SELECT statements
   • Invoke conversion functions and conditional expressions
4. Aggregating Data Using the Group Functions
   • Identify the available group functions
   • Define the role of group functions
   • Group data by using the GROUP BY clause
   • Segregate grouped rows using the HAVING clause
5. Displaying Data from Multiple Tables Using Join Statement
   • Create SELECT statements to tap data from more than one table using equijoins and nonequijoins
   • Join a table to itself by using a self-join
   • View and select data that does not meet a join condition by using outer joins
   • Produce a Cartesian product of all rows from tables
6. Using Sub-queries to Solve Queries
   • Define subqueries
   • Define the different kinds of problems covered by sub-queries solutions
   • Differentiate the kinds of sub-queries
   • Create rows of sub-queries
7. Using the Set Operators to Restrict Different Sets of Data
   • Define set operators
   • Make use of a set operator to join multiple a single query
   • Manage the order of rows returned
8. Data Manipulation Statements to Modify Data in Oracle Database
   • Define several data manipulation language (DML) statement
   • Insert, update and delete rows into a table
   • Manage statement transactions
9. Using DDL Statements to Create and Manage Tables
   • Classify and group the main database objects
   • Study the table structure
   • Classify and distinguish the data types that are available for columns
   • Produce a simple table
10. Producing Other Schema Objects
    • Produce simple and complex views
    • Return data from views
    • Develop, manage, and execute sequences
    • Produce and sustain indexes
    • Produce private and public synonyms
11. Managing Objects with Data Dictionary Views
    • Use the data dictionary views to look for data on your objects
    • Query various data dictionary views
12. Controlling User Access
    • Classify and define system privileges from object privileges
    • Allow privileges on tables
    • Examine privileges in the data dictionary
    • Allow roles
    • Differentiate privileges and roles
13. Manipulating Large Data Sets with Oracle Database
    • Manage and operate data using sub-queries
    • Define the features of multi table INSERTs
    • Make use of the different kinds of multi table INSERTs (Unconditional, Conditional and Pivot)
    • Join rows in a table
    • Monitor changes to data over a specified period of time
14. Producing Reports by Aggregating Related Data
    • Make use of the ROLLUP operation in creating subtotal values
    • Make use of the CUBE operation in creating cross tabulation values
    • Make use of the GROUPING function to classify the row values created by ROLLUP or CUBE
    • Make use of GROUPING SETS in creating a single result set
15. Data Management in Different Time Zones
    • Make use of several date and time functions
16. Retrieving Data Using Sub-queries in Various Ways
    • Create a multiple-column sub-query
    • Make use of scalar sub-queries in SQL
    • Provide solution to problems with integrated sub-queries
    • Revise and reduce rows using integrated sub-queries
    • Make use of the EXISTS and NOT EXISTS operators
    • Make use of the WITH clause
17. Organized Retrieval (Hierarchical Retrieval)
    • Define the principle of a hierarchical query
    • Produce a structured report
    • Edit and format hierarchical data
    • Rule out branches from the structured data report
18. Regular Expression Usage and Support in Oracle
    • Make use of Meta Characters
    • Make use of regular Expression Functions
    • Make use of replacing Patterns
    • Make use of regular Expressions and Check Constraints

WHO NEEDS TO TAKE THIS COURSE:

MITS’ Oracle SQL Expert Training course is well suited for the following individuals:

• Application Developers
• Forms Developers
• PL or SQL Developers
• Reports Developers
• Technical Consultant
• End Users

Click here to request schedule and more course information.