This introductory class covers topics related to Oracle database technology, Oracle's SQL*Plus product and the SQL (Structured Query Language) language. This class provides a basic introduction of the critical skills required by anyone involved with Oracle in a technical capacity.
The Oracle relational database management system is introduced. Students will create SQL statements. They will learn techniques for writing simple and complex queries, making changes to existing data and controlling database transactions. Students will learn to create and administer database objects such as tables, indexes and views.
Audience
Novice Oracle developers and DBAs, intermediate Oracle developers and DBAs wishing to ‘back fill’ gaps in their expertise, technical managers needing Oracle expertise for Oracle project administration.
Prerequisites
Skill with GUI interfaces
Data processing background
Course Topics
v Database fundamentals
v Oracle's SQL tools
v SQL*Plus commands
v The PetSaver database
v SQL statements
v SQL SELECT statements
v Formatting output with SQL*Plus
v Single row SQL functions
v Aggregating data
v Multi-table SELECT statements
v Advanced subqueries ---- need to add content for this
v DML statements
v SQL transaction control
v Table creation
v Table maintenance
v Controlling user access
v Creating other database objects
Topic contents
Database fundamentals
Oracle’s SQL tools
v SQL*Plus
v iSQL*Plus
v SQL*Worksheet
SQL*Plus commands
v File commands
v Miscellaneous commands
v SQL*Plus variables
The PetSaver database
SQL statements
v ANSI SQL
SQL SELECT statements
v Selecting data
v Using expressions
v Choosing rows (WHERE clause)
v Sorting rows (ORDER BY clause)
Formatting output with SQL*Plus
v Re-naming columns (column aliases, AS clause)
v COLUMN command
v BREAK ON command
v COMPUTE command
v Adding titles and footers
Single row SQL functions
v Overview of functions
v Character functions
v Numeric functions
v Date functions
v Conversion functions
v Other functions
Aggregating data
v Composite groups (GROUP BY clause)
v Multi-row SQL functions
v Choosing groups (HAVING clause)
Multi-table SELECT statements
v Joins
v Subqueries
v Set operators
Advanced subqueries
v Multi-column subqueries
v Correlated sub queries
v In-line views (subqueries in the FROM clause)
v Scalar sub query expressions (subqueries in the SELECT list)
v Subquery factoring (using the WITH clause)
DML statements
v INSERT
v UPDATE
v DELETE
v TRUNCATE
v MERGE
SQL Transaction control
v COMMIT
v ROLLBACK
v SAVEPOINT
v SET TRANSACTION
Table creation
v Oracle datatypes
v CREATE TABLE
Table maintenance
v ALTER TABLE
v DROP TABLE
Controlling user access
v System privileges (GRANT/REVOKE statement)
v ROLEs
Creating other database objects
v Views
v Sequences
v Indexes
v Synonyms
