Oracle9i SQL 1Z0-007 - Online Course

This series is designed to help those who have some experience with relational databases to prepare for the Oracle9i 1Z0-007 exam.

This series is for anyone who is interested in learning more about Oracle9i SQL This online course contains 10 lessons and 1 practice exams and should take approximately 24 hours to complete.

Lesson 1: Introduction to SQL (3.0) hours

Introduction to SQL teaches you how to understand basic database concepts, list new SQL features in Oracle9i, use the four available SQL Plus tools, and enter and execute basic queries using the SELECT command. This course provides an introduction to the Oracle9i relational database management system and basic database concepts. Topics include Basic database concepts, SQL Plus command line, SQL Plus window, SQL Plus worksheet, iSQL Plus, Using the SELECT statement, Writing SQL commands, and Writing queries.

Lesson 2: Intermediate Queries (2.0) hours

Intermediate Queries teaches you how to use the WHERE clause to filter out rows from query results, format query results, use multiple filters in a query, query the DUAL table, sort query results using the ORDER BY clause, and handle null values in query results. This course provides information about using the SELECT statement and using the WHERE and ORDER BY clauses in Oracle9i SQL. Topics include Using the WHERE clause, Formatting query results, Using multiple filters, Dual and pseudocolumns, Using the ORDER BY clause, and Sorting rules for null values.

Lesson 3: Queries with Single-Row Functions (2.0) hours

Queries with Single-Row Functions teaches you how to describe the most commonly-used functions available in SQL, use character, number, and date functions in SELECT statements, use conversion functions, and find online documentation for more information about functions as needed. This course examines the types and uses of functions available for various data types in Oracle9i SQL. Topics include String functions, Number functions, Date functions, Miscellaneous functions, Conversion functions, and Combining functions.

Lesson 4: Combining Tables (2.0) hours

Combining Tables teaches you how 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 using a self join, create various types of join queries, and identify the differences between Oracle proprietary format and ANSI format for joining tables. This course examines joining tables in Oracle9i SQL using the ANSI format JOIN clause and the Oracle proprietary format. Topics include Joining tables, Cross joins, Natural joins, The USING and the ON clause, Outer joins, Left outer joins, Right outer joins, Self joins, and Other types of joins.

Lesson 5: Grouping and Summarizing Data (1.0) hour

Grouping and Summarizing Data teaches you how to explain how group functions work, use aggregate functions, use DISTINCT and ALL clauses, group and sort rows using the GROUP BY clause, use the HAVING clause to restrict data, and use the ROLLUP, CUBE, and GROUPING SETS clauses. This course examines the summarizing of query results into groups using the GROUP BY clause in Oracle9i SQL. Topics include The GROUP BY clause and group functions, Aggregate functions, Grouping rows and sorting with GROUP BY, Restricting Data, and Extending the GROUP BY clause.

Lesson 6: Advanced Queries and Views (2.0) hours

Advanced Queries and Views teaches you how to list the types of subqueries, use subqueries in Oracle SQL commands, add and drop views, change views, and modify data using views. This course provides an in-depth look at using subqueries and views in Oracle9i SQL. Topics include Subqueries, Types and uses of views, Simple, constraint and complex views, Changing and dropping views, Working with views, and Inserting, updating and deleting in views.

Lesson 7: Working with Data (2.0) hours

Working with Data teaches you how to distinguish between Oracle9i and user-defined datatypes, use the four Oracle9i DML commands INSERT, UPDATE, DELETE, and MERGE, and understand transactions and the commands used to manipulate their behavior. This course examines the four main commands used in SQL to add, modify, remove, and merge rows of data in database tables. Topics include Datatypes, Data Manipulation Language, Using transactions, Adding and changing data, and Deleting and merging data.

Lesson 8: Creating Tables and Constraints (2.0) hours

Creating Tables and Constraints teaches you how to create, modify, rebuild, and remove tables, use the TRUNCATE command, create, modify and drop constraints, use primary key constraints, use foreign key contraints, and add comments to SQL scripts and schema objects. This course examines creating and changing tables and defining constraints on tables in Oracle9i SQL. Topics include Creating tables, Relational and temporary tables, Adding comments, Changing table structures, Renaming, truncating and dropping tables, Constraints, Primary key, unique and foreign key constraints, Table level and check constraints, and Adding, modifying and dropping constraints.

Lesson 9: Database Objects and User Security (4.0) hours

Database Objects and User Security teaches you how to create, modify, and delete sequences, create, modify, and delete synonyms, create, modify, and delete indexes, create, modify, and delete users, grant and revoke system and object privileges, and create, grant, revoke, enable, disable, and delete roles. This course examines how to create database objects including sequences, synonyms, and indexes in Oracle9i SQL. It also covers sharing table data with other users through privileges and roles. Topics include Working with sequences, Working with synonyms, Understanding indexes, Working with indexes, Understanding users, Working with users, Granting privileges, Revoking privileges, Creating, granting, and revoking roles, and Enabling, disabling, and dropping roles.

Lesson 10: SQL*Plus And iSQL*Plus Reporting and PL/SQL (3.0) hours

SQL*Plus And iSQL*Plus Reporting and PL/SQL teaches you how to create a well formatted report with SQL Plus, know when and how to use variables, use iSQL Plus, use Top-N Analysis, and identify the basic concepts of PL/SQL. This course examines how to use environment settings, variables, and special SQL Plus commands to generate professional looking reports in Oracle9i SQL. Topics include SQL Plus reports, Environment settings, Using environment settings, Variables and scripts, Generating reports, Reporting features, Running reports, Top-N queries, PL/SQL, Retrieving data in PL/SQL, Dynamic SQL, Control structures, and Iteration and repetition.

1 Practice Exam

This course provides questions to prepare users who plan to take exam 1Z0-007: Introduction to Oracle9i: SQL.