ORACLE 9i SQL CURRICULUM

SQL PROGRAMMING
Course Overview
INSTRUCTOR NOTES
This overview is designed to outline the Academy SQL course into 18 sections which approximate to 18 weeks of a typical school semester. Given that no school is typical, we suggest using this as a benchmark for scheduling and gauging whether or not you are "on schedule" to complete all the material before then end of you school year. Each Section is broken down into 5 Lessons, which, again, correlate to a 5 daily lesson plans of 45- 55 minutes each. Given the unique make-up of each instructor, each class, each school and each group of students, the lessons are designed to be used according to the time you have available to implement them.
 
Section 1
INTRODUCTION TO SQL
OVERVIEW
As we drive farther into the world of the database, the primary highway we follow is SQL (pronounced "sequel") or the Structured Query Language. This standardized language is the "highway" that anyone can drive on, thus opening the door for companies to compete in the building of storage units or databases. The database management software is a system for organizing the storage unit (or database) according to business needs and rules. These business requirements are translated through the use of data integrity constraints. This is comparable to identifying only certain roads for driving and establishing police check points to maintain security. In this section, you will gain an understanding of the relational database management system (RDBMS) and the object relational database management system (ORDBMS). You will also be introduced to SQL statements that are specific to Oracle Platform which is used for executing SQL and for formatting and reporting purposes.

COURSE OBJECTIVES
At the end of this course students will be able to:
List the features of Oracle9i
Discuss the theoretical and physical aspects of a relational database
Describe the Oracle implementation of the RDBMS and ORDBMS
List the capabilities of SQL SELECT statements

Section 2
WRITING A BASIC SQL STATEMENT
AND
RESTRICTING AND SORTING DATA
OVERVIEW
In the first semester, we covered the business rules that go into database design. The database project introduced certain concepts to access the database and create tables. We will now explore the commands necessary to make "trips" back and forth to the database. Oracle Platform is still the vehicle that will take you inside the database, however, now we'll learn how to navigate the SQL highway. The SELECT trip is the most common and easiest. Like a single trip to the grocery store or a commute to and from school/work, or, in this case, a single trip to the storage facility. Initially, the items you will be "picking up" are either numbers, dates characters of fixed or varying lengths.

COURSE OBJECTIVES
At the end of this course students will be able to:
Execute a basic SELECT statement
Differentiate between SQL statements and iSQL*Plus/Oracle Platform commands
Limit the rows retrieved by a query
Sort the rows retrieved by a query

Section 3
SINGLE ROW FUNCTIONS
OVERVIEW
This section takes the single trip one step farther. The trip analogy is now going virtual. Imagine SQL*Plus as being the window through which you take a virtual trip inside the storage closet. Single Row functions describe how you are going to display the items you grab from the storage closet on that one trip (still on the SQL highway). During these brief excursions into the database storage closet, no items are moved around or altered in any way, just how they are displayed. Functions make the basic query block more powerful and are used to manipulate data values. This is the first of two sections that explore functions, it focuses on single-row character, number, and date functions, as well as those functions that convert data from one type to another—for example, character data to numeric data.

COURSE OBJECTIVES
At the end of this course students will be able to:
Describe various types of functions available in SQL
Use character, number, and date functions in SELECT statements
Describe the use of conversion functions
Perform calculations on data
Modify individual data items
Use character, number and date functions in SELECT statements
Format data and numbers for display purposes
Convert column data types

Section 4
SINGLE ROW SUBQUERIES - con't
AND
DISPLAYING DATA FROM MULTIPLE TABLES
OVERVIEW
Trips into the database should not be wasted. Therefore gathering all items needed in one-stop can be tricky. This section will continue Single Row Functions (practice exercises) and begin to identify how to determine exacly where items are located in a storage unit and how to bring those items back in one trip. Imagine simultaneously reaching for multiple items located in different areas of the storage unit and displaying them in the database "window". Quickness is a factor in a relational database, so you must know exactly where the items are. There may be thousands of items stored in thousands of different places. SQL allows you to account for all these variations and plan accordingly. Decide what items you want to see based on their relationship to one another. This section covers how to obtain data from more than one table.

COURSE OBJECTIVES
At the end of this course students will be able to:
Write SELECT statements to access data from more than one table using equality and nonequality joins
View data that generally does not meet a join condition by using outer joins
Join a table to itself by using a self join

Section 5
DISPLAYING DATA FROM MULTIPLE TABLES - Con't
OVERVIEW
This section is a continuation of accessing data from more than one table. It covers more detail on Joins the various ways to join tables. Included is an application of the concepts through the practice exercises and an exercise on the "soft" skill associated with this Academy course: Interviewing.

COURSE OBJECTIVES
At the end of this course students will be able to:
View data that generally does not meet a join condition by using outer joins
Join a table to itself by using a self join
Retrieve records with the USING and ON clauses
Identify some interview techniques and generate a list of interview questions

Section 6
AGGREGATING DATA USING GROUP FUNCTIONS
OVERVIEW
The "trips" that are taken to and from the database are not typical vacations. These virtual excursions are more like business trips. While still cruising the SQL highway, you can make frequent, fast trips (or queries) to the database. Certain trips peform are made for specific reasons. While Single Row functions determined how you were to display data, group functions take that one step farther. Group functions allow you to perform logical operations on the items you grab. For example, you can grab a bunch of numbers and instantly display only the largest. The GROUP BY and HAVING clauses allow you to organize the results of any operations performed and display only what you want displayed. This section further addresses these and other functions. It focuses on obtaining summary information, such as averages, for groups of rows. It discusses how to group rows in a table into smaller sets, and how to specify search criteria for groups of rows.

COURSE OBJECTIVES
At the end of this course students will be able to:

Identify the available group functions
Describe the use of group functions
Group data using the GROUP BY clause
Include or exclude grouped rows by using the HAVING clause

Section 7
SUBQUERIES AND PRODUCING READABLE OUTPUT
OVERVIEW
The ability to utilize a subquery or a query within a query is the reason that the word "structured" appears in SQL or Structured Query Language.* By this section, you should have realized that our "trip" is analogous to a query. The question of what is a subquery can be addressed by looking at trip within a trip. Let's say you are going to San Francisco, CA for a vacation. While you are there, you take an excursion to the Museum of Modern Art. This is analogous to a Subquery, with one minor exception: the trip to museum is taken before the trip to San Francisco. In the database world, it is possible to take the excursion before the main trip. This way you can specify certain items you want before going. You could extract pictures that were painted by a specific artist and contained fruit (pairwise) or any pictures by that specified artist and any picture containing fruit (nonpairwise). If this function were an actual possibility on your next vacation, you could have selected your favorite pieces of art and have them waiting for you when you arrive in San Francisco. This section covers the more advanced features of the SELECT statement and some ways to display data. You can write subqueries in the WHERE clause of another SQL statement to obtain values based on an unknown conditional value. This lesson covers single-row subqueries, multiple-row subqueries and substitution variables.

COURSE OBJECTIVES
At the end of this course students will be able to:
Describe the types of problem that subqueries can solve
Define subqueries
List the types of subqueries
Write single-row and multiple-row subqueries
Produce queries that require a substitution variable
Produce more readable output
Create and execute script files

Section 8
MANIPULATING DATA
OVERVIEW
In this lesson, you will learn how to insert rows into a table, update existing rows in a table, and delete existing rows from a table. On any given SQL trip, you begin to move from the individual trip of gathering single specific data for display only, to actually making changes in a "shelving" unit. These changes take the shape of inserting, updating and/or deleting specified items on "shelves" (or in the rows). Data manipulation language (DML) is a core part of SQL. When you want to add, update, or delete data in the database, you execute a DML statement. A collection of DML statements that form a logical unit of work is called a transaction. Most applications utilize control transactions use the COMMIT, SAVEPOINT, and ROLLBACK statements.

COURSE OBJECTIVES
At the end of this course students will be able to:

Describe each DML statement
Demonstrate how to insert and rows into a table
Update and merge rows in a table
Delete rows from a table
Control transactions


Section 9
CREATING AND MANAGING TABLES
AND
INCLUDING CONSTRAINTS
OVERVIEW
The museum was mentioned in an earlier section and illustrates other factors that need to be considered on our virtual "trips". What types of artwork are housed there? How large are they? How large are the rooms? What type of security is needed? Are there certain areas of the musuem that are off-limits? Database objects allow us to begin answering some of these questions. Creating a table, for example, is like taking a trip to the musuem, creating an area/room reserved for a specific type of artwork and labeling that area so that only that particular type of art can be placed there. A room reserved for a specific purpose. Just as the items in our closet were limited to specific datatypes, objects (tables, views, sequences, indexes and/or synonyms) are identified and created to serve a specific purpose. A Data Dictionary is a record of details about these objects. It's a reference for trips that take place and make each trip easier. Finally, you will learn how to implement business rules by including integrity constraints. Remember, the primary purpose of a relational database is to retrieve specific, identified pieces of data each and every time. In order to achieve this, we have to prepare our areas of storage to ensure that only specified items will be in specified places.

COURSE OBJECTIVES
At the end of this course students will be able to:
Describe the main database objects
Create tables and alter table definitions
Describe the data types that can be used when specifying column definition
Drop, rename, and truncate tables
Describe create and maintain constraints

Section 10
CREATING VIEWS AND OTHER DATABASE OBJECTS
OVERVIEW
While tables are the foundation of a relational database, other database objects are built off of tables and rely on their accuracy. This section features commonly used database objects, such as views, sequences, indexes, and synonyms. Views provide a special trip into the database. You can logically specify which areas and which items you want to look at. You will also learn to query the relevant data dictionary object to retrieve information about views. Sequences ensure that when items or data are left inside, they are deposited in the correct location. When travelling on the SQL highway, the primary purpose of each trip is to retrieve specific, identified pieces of data each and every time. As you can tell by now, this simple process requires a great deal of preparation and maintainance. An index is reduces the amount of area inside the musuem that you have to look at when you are looking for a specific item. Synonyms rename tables inside the database to make accessing them easier. These other types of database objects make your trip faster, while still accounting for accuracy.

COURSE OBJECTIVES
At the end of this course students will be able to:
Describe a view
Create, alter the definition, and drop a view
Retrieve data through a view
Insert, update, and delete data through a view
Create and use an inline view
Perform "Top-N" analysis
Create, maintain, and use sequences, indexes, private and public synonyms

Section 11
CONTROLLING USER ACCESS
AND
SQL WORKSHOP
OVERVIEW
By stating the primary purpose of the SQL trip as being "to retrieve specific, identified pieces of data each and every time" certain measures must be taken to make that possible. The previous 2 sections, discussed aspects of SQL and the database. Now we must consider who is controlling who can (and can't) cruise the SQL highway and make pitstops in your relational database. A User is like a vehicle that allows a person to go inside the database. A password is similar to a key that starts the vehicle. Some people use their vehicle just to get them from point A to point B. Other people may prefer a larger vehicle for hauling and towing. Some people may be artists, some just spectators. A User can be customized through granting special privileges. While inside the database, these privileges determine how much freedom each User has. This is decided by what that person needs to accomplish on their trip. Just like in life, there are roles people play. The DBA is like the ultimate sheriff, policing the SQL highway through assigning privileges and roles. The DBA tells the drivers who can go where, what cargo they can carry and how they can change that cargo. The DBA, along with the principles of relational database operation, SQL and database objects all work to ensure the prime directive: to retrieve specific, identified pieces of data each and every time. This section will show you how to control database access to specific objects and add new users with different levels of access privileges, however, in practice with Oracle Platform, the Instructor is the only person will sufficient admin privileges. The majority of this section will be spent with students working through the SQL workshop.

COURSE OBJECTIVES
At the end of this course students will be able to:
Create users
Create roles to ease setup and maintenance of the security model
Use the GRANT and REVOKE statements to grant and revoke object privileges
Create and access database links
Creating tables and sequences
Modifying data in the tables
Modifying table definitions
Creating views
Writing scripts containing SQL and Oracle Platform commands
Generating a simple report

Section 12
USING SET OPERATORS
OVERVIEW
As trips to the database become a bit more complex, the need for more advanced queries come into play. The SET operators combine the results of two or more component queries into one result. All SET operators have equal precedence. If a SQL statement contains multiple SET operators, the Oracle server evaluates them from left (top) to right (bottom) if no parentheses explicitly specify another order. You should use parentheses to specify the order of evaluation explicitly in queries that use the INTERSECT operator with other SET operators. Queries containing SET operators are called compound queries.

COURSE OBJECTIVES
At the end of this course students will be able to:
Describe SET operators
Use a SET operator to combine multiple queries into a single query
Control the order of rows returned

Section 13
ORACLE 9i DATETIME FUNCTIONS
OVERVIEW
Oracle9i, lets you include the time zone in your date/time data and provides support for fractional seconds. This lesson focuses on how to manipulate the new datetime datatypes included with Oracle9i, using the new datetime functions. To understand the working of these functions, it is necessary to be familiar with the concept of time zones and the Greenwich Mean Time, or GMT.

COURSE OBJECTIVES
At the end of this course students will be able to:
Use the following datetime functions:
CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
DBTIMEZONE
SESSIONTIMEZONE
EXTRACT
FROM_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TZ_OFFSET
- top -

Section 14
ENHANCEMENTS TO THE GROUP BY CLAUSE
OVERVIEW
The GROUP BY clause can be used to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. Group functions can appear in select lists and in ORDER BY and HAVING clauses. The Oracle server applies the group functions to each group of rows and returns a single result row for each group.

COURSE OBJECTIVES
At the end of this course students will be able to:
Use the ROLLUP operation to produce subtotal values
Use the CUBE operation to produce cross-tabulation values
Use the GROUPING function to identify the row values created by ROLLUP or CUBE
Use GROUPING SETS to produce a single result set

Section 15
Advanced Subqueries
OVERVIEW
In this section, you will learn how to write multiple-column subqueries and subqueries in the FROM clause of a SELECT statement. You will also learn how to solve problems by using scalar, correlated subqueries and the WITH clause.

COURSE OBJECTIVES
Write a multiple-column subquery
Describe and explain the behavior of subqueries when null values are retrieved
Write a subquery in a FROM clause
Use scalar subqueries in SQL
Describe the types of problems that can be solved with correlated subqueries
Write correlated subqueries
Update and delete rows using correlated subqueries
Use the EXISTS and NOT EXISTS operators
Use the WITH clause

Section 16
Heirarchical Retrieval
and
Oracle 9i Extensions to DML and DDL Statements
OVERVIEW
Hierarchical queries enable you to retrieve data based on a natural hierarchical relationship between rows in a table. A relational database does not store records in a hierarchical way. However, where a hierarchical relationship exists between the rows of a single table, a process called tree walking enables the hierarchy to be constructed. A hierarchical query is a method of reporting, in order, the branches of a tree. Imagine a family tree with the eldest members of the family found close to the base or trunk of the tree and the youngest members representing branches of the tree. Branches can have their own branches, and so on. In this section, you will learn how to use hierarchical queries to create tree-structured reports. This section will also address the Oracle9i extensions to DDL and DML statements. It will focus on multitable INSERT statements, types of multitable INSERT statements, external tables, and the provision to name the index at the time of creating a primary key constraint.

COURSE OBJECTIVES
At the end of this course students will be able to:
Interpret the concept of a hierarchical query
Create a tree-structured report
Format hierarchical data
Exclude branches from the tree structure
Describe the features of multitable inserts
Use the following types of multitable inserts
Unconditional INSERT
Pivoting INSERT
Conditional ALL INSERT
Conditional FIRST INSERT

Create and use external tables
Name the index at the time of creating a primary key constraint

Section 17
REVIEW FOR FINAL EXAM
OVERVIEW
Using the review materials available, prepare students for the certification exam.

Section 18
REVIEW FOR FINAL EXAM
OVERVIEW
Using the review materials available, prepare students for the certification exam.