Oracle Database 11g: SQL Fundamentals

What you will learn:

Audience:

• Application Developers
• Forms Developer
• PL/SQL Developer
• Reports Developer
• Technical Consultant
• Portal Developer
• Functional Implementer
• End Users

Prerequisites:

Required Prerequisites:

• Familiarity with data processing concepts and techniques

Course Objectives:

• Retrieve row and column data from tables with the SELECT statement
• Create reports of sorted and restricted data
• Employ SQL functions to generate and retrieve customized data
• Display data from multiple tables using the ANSI SQL 99 JOIN syntax
• Create reports of aggregated data
• Use the SET operators to create subsets of data
• Run data manipulation statements (DML) to update data in the Oracle Database 11g
• Run data definition language (DDL) statements to create and manage schema objects
• Identify the major structural components of the Oracle Database 11g
• Control database access to specific objects
• Manage schema objects
• Manage objects with data dictionary views
• Manipulate large data sets in the Oracle database by using subqueries
• Manage data in different time zones
• Write multiple-column subqueries
• Use scalar and correlated subqueries
• Use the regular expression support in SQL
• Add new users with different levels of access privileges

Course Topics:

Introduction

• Listing the features of Oracle Database 11g
• Discussing the basic design, theoretical and physical aspects of a relational database
• Describing the development environments for SQL
• Describing Oracle SQL Developer
• Describing the data set used by the course
• The Human Resources (HR) Schema
• Review of Using Oracle SQL Developer
• Review of some basic concepts of SQL

Retrieving Data Using the SQL SELECT Statement

• Listing the capabilities of SQL SELECT statements.
• Generating a report of data from the output of a basic SELECT statement
• Using arithmetic expressions and NULL values in the SELECT statement
• Using Column aliases
• Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Displaying the table structure using the DESCRIBE command

Restricting and Sorting Data

• Writing queries with a WHERE clause to limit the output retrieved
• Using the comparison operators and logical operators
• Describing the rules of precedence for comparison and logical operators
• Using character string literals in the WHERE clause
• Writing queries with an ORDER BY clause to sort the output
• Sorting output in descending and ascending order
• Using the Substitution Variables

Using Single-Row Functions to Customize Output

• Differentiating between single row and multiple row functions
• Manipulating strings using character functions
• Manipulating numbers with the ROUND, TRUNC and MOD functions
• Performing arithmetic with date data
• Manipulating dates with the date functions

Using Conversion Functions and Conditional Expressions

• Describing implicit and explicit data type conversion
• Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nesting multiple functions
• Applying the NVL, NULLIF, and COALESCE functions to data
• Using conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Using the aggregation functions in SELECT statements to produce meaningful reports
• Using AVG, SUM, MIN, and MAX function
• Handling Null Values in a group function
• Creating queries that divide the data in groups by using the GROUP BY clause
• Creating queries that exclude groups of date by using the HAVING clause

Displaying Data From Multiple Tables

• Writing SELECT statements to access data from more than one table
• Joining Tables Using SQL:1999 Syntax
• Viewing data that does not meet a join condition by using outer joins
• Joining a table by using a self join
• Creating Cross Joins

Using Sub-queries to Solve Queries

• Using a Subquery to Solve a Problem
• Executing Single-Row Sub-queries
• Using Group Functions in a Sub-query
• Using Multiple-Row Subqueries
• Using the ANY and ALL Operator in Multiple-Row Sub-queries

Using the SET Operators

• Describing the SET operators
• Using a SET operator to combine multiple queries into a single query
• Using UNION, UNION ALL, INTERSECT, and MINUS Operator
• Using the ORDER BY Clause in Set Operations

Manipulating Data

• Adding New Rows to a Table Using the INSERT statement
• Changing Data in a Table Using the UPDATE Statement
• Using DELETE and TRUNCATE Statements
• Saving and discarding changes with the COMMIT and ROLLBACK statements
• Implementing Read Consistency
• Using the FOR UPDATE Clause

Using DDL Statements to Create and Manage Tables

• Categorizing Database Objects
• Creating Tables using the CREATE TABLE Statement
• Describing the data types
• Describing Constraints
• Creating a table using a subquery
• Altering and Dropping a table

Creating Other Schema Objects

• Creating, modifying, and retrieving data from a view
• Performing Data manipulation language (DML) operations on a view
• Dropping a view
• Creating, using, and modifying a sequence
• Creating and dropping indexes
• Creating and dropping synonyms

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

• Adding, Modifying and Dropping a Column
• Adding, Dropping and Deferring a Constraint
• Enabling and Disabling a Constraint
• Creating and Removing Indexes
• Creating a Function-Based Index
• Performing Flashback Operations
• Creating an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
• Querying External Tables

Managing Objects with Data Dictionary Views

• The Data Dictionary
• How to Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Querying the dictionary views for constraint information
• Querying the dictionary views for view, sequence, index and synonym information
• Adding a comment to a table
• Querying the dictionary views for comment information

Manipulating Large Data Sets

• Using Subqueries to Manipulate Data
• Retrieving Data Using a Subquery as Source
• Inserting Using a Subquery as a Target
• Using the WITH CHECK OPTION Keyword on DML Statements
• Types of Multitable INSERT Statements
• Using Multitable INSERT Statements
• Merging rows in a table
• Tracking Changes in Data over a period of time

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

Retrieving 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

Regular Expression Support

• Using the Regular Expressions Functions and Conditions in SQL
• Using Meta Characters with Regular Expressions
• Performing a Basic Search using the REGEXP_LIKE function
• Finding patterns using the REGEXP_INSTR function
• Extracting Substrings using the REGEXP_SUBSTR function
• Replacing Patterns Using the REGEXP_REPLACE function
• Using Sub-Expressions with Regular Expression Support
• Using the REGEXP_COUNT function