Oracle DB 11g - Intro to SQL

Audience:

Business Analysts Developers Application Developers PL/SQL Developer Forms Developer System Analysts Data Warehouse Administrator

Prerequisites:

A basic understanding of your computer operating system and general knowledge of databases.

Description:

In this course students learn the concepts of relational databases. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects. Students learn to control privileges at the object and system level. This course covers creating indexes and constraints, and altering existing schema objects. Students also learn how to create and query external tables. Students learn to use the advanced features of SQL in order to query and manipulate data within the database, use the dictionary views to retrieve metadata and create reports about their schema objects. Students also learn some of the date-time functions available in the Oracle Database. This course discusses how to use the regular expression support in SQL. In this course, students use Oracle SQL Developer as the main development tool. SQL*Plus is introduced as an optional development tool.

I. Introducing Oracle Database 11g

List the features of Oracle Database 11g

Discuss the basic design, theoretical and physical aspects of a relational database

Categorize the different types of SQL statements

Describe the data set used by the course

Log onto the database using the SQL Developer environment

Save queries to files and use script files in SQL Developer

II. Retrieving Data Using the SQL SELECT Statement

List the capabilities of SQL SELECT statements

Generate a report of data from the output of a basic SELECT statement

Select All Columns

Select Specific Columns

Use Column Heading Defaults

Use Arithmetic Operators

Understand Operator Precedence

Learn the DESCRIBE command to display the table structure

III. Restricting and Sorting Data

Write queries that contain a WHERE clause to limit the output retrieved

List the comparison operators and logical operators that are used in a WHERE clause

Describe the rules of precedence for comparison and logical operators

Use character string literals in the WHERE clause

Write queries that contain an ORDER BY clause sort the output of a SELECT statement

Sort output in descending and ascending order

IV. Using Single-Row Functions to Customize Output

Describe the differences between single row and multiple row functions

Manipulate strings with character function in the SELECT and WHERE clauses

Manipulate numbers with the ROUND, TRUNC and MOD functions

Perform arithmetic with date data

Manipulate dates with the date functions

V. Using Conversion Functions and Conditional Expressions

Describe implicit and explicit data type conversion

Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

Nest multiple functions

Apply the NVL, NULLIF, and COALESCE functions to data

Use conditional IF THEN ELSE logic in a SELECT statement

VI. Reporting Aggregated Data Using the Group Functions

Use the aggregation functions in SELECT statements to produce meaningful reports

Create queries that divide the data in groups by using the GROUP BY clause

Create queries that exclude groups of date by using the HAVING clause

VII. Displaying Data From Multiple Tables

Write SELECT statements to access data from more than one table

View data that generally does not meet a join condition by using outer joins

Join a table by using a self join

VIII. Using Sub-queries to Solve Queries

Describe the types of problem that sub-queries can solve

Define sub-queries

List the types of sub-queries

Write single-row and multiple-row sub-queries

IX. Using the SET Operators

Describe the SET operators

Use a SET operator to combine multiple queries into a single query

Control the order of rows returned when using the SET operators

X. Manipulating Data

Describe each DML statement

Insert rows into a table with the INSERT statement

Use the UPDATE statement to change rows in a table

Delete rows from a table with the DELETE statement

Save and discard changes with the COMMIT and ROLLBACK statements

Explain read consistency

XI. Using DDL Statements to Create and Manage Tables

Categorize the main database objects

Review the table structure

List the data types available for columns

Create a simple table

Decipher how constraints can be created at table creation

Describe how schema objects work

XII. Creating Other Schema Objects

Create a simple and complex view

Retrieve data from views

Create, maintain, and use sequences

Create and maintain indexes

Create private and public synonyms

XIII. Controlling User Access

Differentiate system privileges from object privileges

Grant privileges on tables

View privileges in the data dictionary

Grant roles

Distinguish between privileges and roles

XIV. Managing Schema Objects

Add constraints

Create indexes

Create indexes using the CREATE TABLE statement

Create function-based indexes

Drop columns and set column UNUSED

Perform FLASHBACK operations

Create and use external tables

XV. Managing Objects with Data Dictionary Views

Explain the data dictionary

Find table information

Report on column information

View constraint information

Find view information

Verify sequence information

Understand synonyms

Add comments

XVI. Manipulating Large Data Sets

Manipulate data using sub-queries

Describe the features of multi-table inserts

Use the different types of multi-table inserts

Merge rows in a table

Track the changes to data over a period of time

XVII. Managing Data in Different Time Zones

Use data types similar to DATE that store fractional seconds and track time zones

Use data types that store the difference between two date-time values

Practice using the multiple data-time functions for globalize applications

XVIII. Retrieving Data Using Sub-queries

Write a multiple-column sub-query

Use scalar sub-queries in SQL

Solve problems with correlated sub-queries

Update and delete rows using correlated sub-queries

Use the EXISTS and NOT EXISTS operators

Use the WITH clause

XIX. Regular Expression Support

List the benefits of using regular expressions

Use regular expressions to search for, match, and replace strings