Oracle Database 11g delivers economies of scale on easily managed low-cost grids, making it easier to reduce cost of downtime with maximum availability architecture, change IT systems faster using Real Application Testing, partition and compress data to run queries faster using less disks, securely protect and audit data, and enable total recall of data, and make productive use of standby resources with Active Data Guard.

This course addresses Oracle Database 11g Programming with SQL and is focused on:

Oracle Database 11g: SQL Fundamentals I

  • Using SQL to Query Your Database
  • Conversion Functions, Group Functions, and Joins
  • Subqueries, Set Operators, and Data Manipulation
  • Using DDL, Views, Sequences, Indexes, and Synonyms

Oracle Database 11g: SQL Fundamentals II

  • Controlling User Access and Managing Schema Objects
  • Data Dictionary Views and Large Data Sets
  • Managing Data in Different Time Zones
  • Using Subqueries and Regular Expressions

What You’ll Learn

Oracle Database 11g: SQL Fundamentals I

  • Concepts and components of an Oracle Database 11g database, how to retrieve information from it using SQL, and the steps for sorting, limiting, modifying, and formatting this information
  • Steps for writing queries that convert data from one type to another, specify conditions, perform calculations on groups of rows or even tables, and return values from more than one table
  • Steps for manipulating queries to return the data you need, using subqueries and set operators, and manipulating the actual data using INSERT, UPDATE, DELETE, and other data manipulation language (DML) statements
  • Steps for creating, defining, and dropping tables, manipulating how their data can be viewed, and using schema objects to generate integers, improve queries, and rename tables

Oracle Database 11g: SQL Fundamentals II

  • Steps for controlling user access to objects and for maintaining these objects by dropping columns and adding constraints and indexes
  • Steps for querying data dictionary views to view schema objects and for performing operations on large amounts of data, such as inserting data into multiple tables and merging table rows
  • Steps for managing data in different time zones in Oracle Database 11g, for managing time intervals, and for using datetime functions
  • Steps for retrieving data using subqueries and using regular expressions to search for, match, and replace strings

Who Needs to Attend

Business and systems analysts, application developers, PL/SQL developers, forms developers, and data warehouse administrators; database administrators, database designers, developer support engineers, and technical administrators

Prerequisites

  • Familiarity with data processing concepts and techniques
  • Ability to use a graphical user interface (GUI)

Follow-On Courses

There are no follow-ons for this course.

Course Outline

Oracle Database 11g: SQL Fundamentals I

1. Oracle Database 11g: Using SQL to Query Your Database

  • Functions, capabilities, and terminology associated with Oracle Database 11g’s components and relational databases in general
  • Using Oracle SQL Developer to create a new database connection and browse tables
  • Using the SQL SELECT statement to retrieve different sets of data from a database
  • Limiting and ordering the rows returned by a SQL query
  • Ways to sort data returned by a SQL query, using substitution variables as well as the DEFINE and VERIFY commands
  • Steps for using SQL functions to modify, format, and otherwise manipulate returned data
  • Write SQL queries to display, sort, and format output in a given scenario

2. Oracle Database 11g: Conversion Functions, Group Functions, and Joins

  • Using functions to convert character, date, and numeric values and perform operations pertaining to null values
  • Using the CASE expression and the DECODE function to implement conditional processing
  • Write queries that convert data types and implement conditional processing, in a given scenario
  • Benefits of using group functions to return values for groups of rows
  • Creating, restricting, and nesting group functions
  • Using joins to return data from more than one table
  • Write queries that calculate values for groups of data and return values from multiple tables in a given scenario

3. Oracle Database 11g: Subqueries, Set Operators, and Data Manipulation

  • Using subqueries inside other queries to perform various actions
  • Using set operators to combine the results of two or more queries
  • Write queries using set operators
  • Adding new rows to a table, modifying table data, and removing rows
  • Controlling database transactions and implementing read consistency
  • Insert, update and delete rows in a table and control data transactions to it

4. Oracle Database 11g: Using DDL, Views, Sequences, Indexes, and Synonyms

  • Creating, defining, and naming a table, and specifying data types for its columns
  • Using constraints to prevent invalid data entry into tables
  • Altering or dropping a table
  • Creating and manipulating views to view or hide a table’s data
  • Using sequences to create integers, using indexes to improve the performance of queries, and using synonyms to give a table an alternative name
  • Create a new table and create and use a sequence, index, and synonym

Oracle Database 11g: SQL Fundamentals II

1. Oracle Database 11g: Controlling User Access and Managing Schema Objects

  • Granting and revoking system and object privileges
  • Grant privileges to users in a given scenario
  • Altering a table by adding, modifying, and dropping columns
  • Adding and manipulating constraints and creating indexes
  • Recovering your database using flashback and manipulating external data
  • Alter a table by dropping columns and adding constraints and indexes

2. Oracle Database 11g: Data Dictionary Views and Large Data Sets

  • Using data dictionary views to find information about tables, constraints, views, sequences, and synonyms
  • Query the appropriate dictionary views to find information about schema objects in a given scenario
  • Manipulating data using subqueries and explicit default values
  • Inserting into multiple tables, merging rows in tables, and tracking changes in data
  • Manipulate large data sets using multitable INSERT and MERGE operations

3. Oracle Database 11g: Managing Data in Different Time Zones

  • Managing time zones and time intervals
  • Using datetime functions to manage dates and time in Oracle Database 11g
  • Display time zone offsets in a given scenario
  • Extract and return the value of a datetime field in a given scenario

4. Oracle Database 11g: Using Subqueries and Regular Expressions

  • Writing multiple-column and scalar subqueries
  • Solving problems by using correlated subqueries and the WITH clause
  • Write multiple-column, correlated, and scalar subqueries in a given scenario and solve a problem using the WITH clause
  • Benefits of implementing complex match logic in the database using regular expressions
  • Using regular expressions functions and accessing subexpressions
  • Use regular expression functions to search for, replace, and manipulate data, and also add a CHECK constraint to a column