SQL Fundamentals II

 Click here

Course Overview

This course introduces the advanced features available in the SQL programming language. The topics covered will expand the student's knowledge of the language and enhanced their SQL programming skills.

Students will learn how to manage database objects and control user access to them.

Students will learn to manipulate large data sets using scalar and correlated subqueries.

Students learn to create and query external tables, in addition, to how data dictionary views can be used to retrieve database information.

Students learn how to write complex SQL queries using advanced querying and reporting techniques.

Date/time functions and regular expression support is also covered.

Demonstrations and hands-on practices are used to reinforce the fundamental concepts.

Course Goals

• To provide students with a solid understanding of Oracle RDBMS and SQL language.
• To assist students with developing the coding skills necessary for retrieving and/or manipulating data stored in an Oracle database.
• To complete hands-on exercises in an interactive, instructor-led environment.

Prerequisites

• Familiarity with data processing concepts and techniques.

Who Should Attend?

• Application Developers
• PL/SQL Developers
• Forms Developers
• Reports Developers
• Technical Consultants
• Portal Developers
• Functional Implementers
• End Users

Course Materials

• Student Handbook
• Reference Book

Duration

• Two (2) days

Course Outline

• Introduction
  • The Human Resources (HR) Schema
  • Review of Using Oracle SQL Developer
  • Review of some basic concepts of SQL
• Controlling User Access
  • System versus Object Privileges
  • Creating Users
  • Granting System Privileges
  • Creating and Granting Privileges to a Role
  • Changing Your Password
  • Granting Object Privileges
  • Passing On Your Privileges
  • Revoking Object Privileges
• Managing Schema Objects
  • The ALTER TABLE Statement
  • Creating and Using Table Constraint
  • Enabling and Disabling a Constraint
  • Overview of Indexes
  • Using External Tables
• Grouping Related Data
  • Review of the GROUP BY Clause
  • Review of the HAVING Clause
  • The ROLLUP Operator
  • The CUBE Operator
  • The GROUPING Function
  • GROUPING SETS
  • Composite Columns
  • Concatenated Groupings
• Manipulating Large Data Sets
  • Copying Rows from Another Table
  • Inserting Using a Subquery as a Target
  • Retrieving Data Using a Subquery as Source
  • Updating Two Columns with a Subquery
  • Manipulating Rows Based on Another Table
  • Using the WITH CHECK OPTION Keyword
  • Using Explicit Default Values
  • The MERGE Statement
  • Merging rows in a table
• Managing Data in Different Time Zones
  • Time Zones
  • CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
  • Comparing Date and Time in a Session's Time Zone
  • DBTIMEZONE and SESSIONTIMEZONE
  • Difference between DATE and TIMESTAMP
  • INTERVAL Data Types
  • Using EXTRACT, TZ_OFFSET and FROM_TZ
  • Using TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
• Retreiving Data using Subqueries
  • Multiple-Column Subqueries
  • Pairwise and Nonpairwise Comparison
  • Using Scalar Subquery Expressions
  • Solving problems with Correlated Subqueries
  • Updating and Deleting Rows Using Correlated Subqueries
  • Using the EXISTS and NOT EXISTS operators
  • Using the WITH clause
• Hierarchical Retrieval
  • Natural Tree Structure
  • Hierarchical Queries
  • Waling the Tree
  • The LEVEL Pseudocolumn
  • Pruning Branches