Oracle Database 11g: SQL Fundamentals I Training

This course is a common starting point in our database curriculum for administrators, developers and business users. The objective of this course is to provide an introduction to the SQL database language within the context of an Oracle database, based upon the latest features available.

What you will learn

Among the specific subjects discussed in this course are:

• A summation of relational database principles and an introduction to the Oracle relational database server.
• Using SQL Developer and other available SQL interfaces.
• Writing simple SQL queries which isolate exactly the data desired, and then format and sort the data as needed.
• Writing moderately complex SQL queries using various join techniques.
• Supplement SQL code with references to pseudo columns and system functions.
• Summarizing, grouping and combining data to obtain more meaningful query results and to allow one to draw conclusions and make business decisions based upon the data processed.
• Advanced query techniques such as set operations, sub-queries and summary functions, allowing one to solve some of the more complex theoretical query challenges.
• Creating and maintaining database tables using the SQL Data Definition Language (DDL).
• Managing data within tables using the SQL Data Manipulation Language (DML).
• Taking advantage of the latest features found within the database such as the use of regular expressions, support for international data, time zones and other topics. 

Audience

The target audience for this course is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this course will be helpful are:

    Application designers and developers
    Database administrators
    Business users and non-technical senior end users

Outline of WA1705 Oracle Database 11g: SQL Fundamentals I Training
1. Relational databases & SQL
    About relational databases
    Elements of SQL

2. Choosing a SQL & PL/SQL interface

    About database connections
    About bind variables
    Using SQL developer
    Using SQL*plus
    Using application express

3. Building a select statement

    About the select statement
    Using alias names

4. Restricting data with the where clause

    About logical operators
    Equality operator
    Boolean operators
    Null & between operators
    Is [not] null operator
    [not] between operator
    Finding text strings
    [not] like operator
    Regexp_like()
    In operator

5. Sorting data with the order by clause

    About the order by clause
    Multiple column sorts
    Specifying the sort sequence
    About null values within sorts
    Using column aliases

6. Pseudo columns & functions

    Using rowed
    Using rownum
    Using the functions
    Sysdate
    User & uid
    Using the dual table
    Sessiontimezone function

7. Joining tables

    About joins
    Inner join
    Reflexive join
    Non-key join
    Outer join

8. Using the set operators

    About the set operators
    Set operator examples

9. Summary functions

    About summary functions
    Summary functions with distinct

10. Using sub-queries

    About sub-queries
    Standard sub-queries
    Correlated sub-queries

11. Aggregating data within groups

    About summary groups
    Finding groups within the base tables
    Selecting data from the base tables
    Selecting groups from the result table

12. Build simple SQL*plus reports

    Format column output with column
    Defining report breaks with break
    Produce subtotals with compute

13. Use data definition language to create & manage tables

    Create table statement
    Not null
    Default
    Alter table statement
    Drop table statement
    Supportive statements
    Describe
    Rename

15. Use data manipulation language to manipulate data

    About the insert statement
    About the delete statement
    About the update statement
    About transactions
    Rollback
    Commit
    Savepoint
    Set transaction
    Truncate table
    Complex table references

Appendix A

    Reference information
    SQL ddl statement reference
    SQL dml statement reference
    SQL transaction control statement reference
    SQL operators reference