Overview
This Learning Path includes Oracle SQL LiveLessons, Oracle PL/SQL Programming: Fundamentals to Advanced LiveLessons, and Toad for Oracle LiveLessons.
Prerequisites
Basic knowledge of what is a database, especially RDBMS and what is a computer programming languageOverview
Oracle SQL LiveLessons Video Training covers the basics of the Oracle SQL programming language. This course covers both Oracle standard SQL and the ANSI …Overview
This Learning Path includes Oracle SQL LiveLessons, Oracle PL/SQL Programming: Fundamentals to Advanced LiveLessons, and Toad for Oracle LiveLessons.
Prerequisites
Basic knowledge of what is a database, especially RDBMS and what is a computer programming languageOverview
Oracle SQL LiveLessons Video Training covers the basics of the Oracle SQL programming language. This course covers both Oracle standard SQL and the ANSI approved SQL that Oracle supports. The focus of the course is to give students working knowledge of the Oracle SQL language and at the conclusion of this course, students should be able to query the Oracle database.Oracle PL/SQL Programming: Fundamentals to Advanced LiveLessons begins with a fundamentals module that covers the basics of Oracle’s language extension for SQL and the Oracle relational database. The focus is to give students with a working knowledge of SQL the ability to develop Oracle procedures, functions, and packages using the PL/SQL language.
The advanced module teaches developers with PL/SQL experience the new and advanced features of the PL/SQL language along with performance tuning techniques.
Toad for Oracle LiveLessons video training covers the latest features of TOAD necessary to view, extract, and manipulate data within the Oracle database. This course is designed for both the developer using Toad to aid in programming Oracle objects, as well as for the business analyst using Toad as a query tool for data extraction and analysis. There is even a complete lesson for the database administrator.
Downloads
Oracle_SQL_LiveLessons_File_Download.zip
About the Instructor
Dan Hotka is a training specialist and an Oracle ACE director who has more than 37 years in the computer industry and more than 31 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well published with 12 Oracle books and well over 200 published articles. He is also the video author for Oracle SQL Performance Tuning for Developers LiveLessons and Oracle PL/SQL Programming Fundamentals. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world. Visit his website at www.DanHotka.com.
About LiveLessons Video Training
The LiveLessons Video Training series publishes hundreds of hands-on, expert-led video tutorials covering a wide selection of technology topics designed to teach you the skills you need to succeed. This professional and personal technology video series features world-leading author instructors published by your trusted technology brands: Addison-Wesley, Cisco Press, IBM Press, Pearson IT Certification, Prentice Hall, Sams, and Que. Topics include: IT Certification, Programming, Web Development, Mobile Development, Home and Office Technologies, Business and Management, and more. View all LiveLessons on InformIT at: http://www.informit.com/livelessons.
Introduction
Oracle SQL: Introduction
Lesson 1: Oracle SQL Course Overview
Learning objectives
1.1 Understand the course pre-requisites
1.2 Utilize the course download
1.3 Understand the course database objects
1.4 Introduce the Database Relational Model
1.5 Introduce the Oracle Architecture
Put it all together
Lesson 2: Introduction to the Course Tools
Learning objectives
2.1 Work with SQL*Plus
2.2 Utilize SQL*Developer
2.3 Explore Toad
Put it all together
Lesson 3: Introduction to SQL
Learning objectives
3.1 Understand Simple SQL queries
3.2 Work with Where clauses
3.3 Hands-on Lab
Put it all together
Lesson 4: Table Joins
Learning objectives
4.1 Discover available tables/columns
4.2 Use the tools to create SQL without typing
4.3 Understand multiple table joins
4.4 Work with ANSI syntax
4.5 Use Oracle12 new ANSI join syntax
4.6 Explore sorting options
4.7 Hands-on Lab
Put it all together
Lesson 5: Functions and Pseudocolumns
Learning objectives
5.1 Utilize pseudocolumns
5.2 Review character/string functions
5.3 Explore date functions
5.4 Understand number functions
5.5 Work with Decode/Case/regular expressions/pattern matching functions
5.6 Work with Group By functions
5.7 Use multiple functions
5.8 Use the functions from the tools
5.9 Hands-on Lab
Put it all together
Lesson 6: Advanced Queries Using Sub-Queries
Learning objectives
6.1 Understand sub-queries
6.2 Work with in-line views
6.3 Utilize SQL coding tips and techniques
6.4 Hands-on Lab
Put it all together
Lesson 7: Oracle Data Manipulation and Definition Language
Learning objectives
7.1 Understand how to create tables
7.2 Utilize subsets of tables
7.3 Work with inserting data
7.4 Explore updating data
7.5 Utilize deleting data
7.6 Use the MERGE command
7.7 Utilize the tools to perform DML and DDL operations
7.8 Hands-on Lab
Put it all together
Lesson 8: Database Management
Learning objectives
8.1 Work with sequences
8.2 Understand identity columns
8.3 Explore creating views
8.4 Understand permissions
8.5 Use indexes to increase SQL performance
8.6 Introduce SQL performance tuning
8.7 Hands-on Lab
Put it all together
Lesson 9: Data Relationships
Learning objectives
9.1 Learn what referential integrity is
9.2 Explore your data relationships
9.3 Use entity-relationship diagrams
9.4 View related data
9.5 Save related data (useful for test data/reports)
9.6 Live Demonstration
Put it all together
Lesson 10: Analytical SQL
Learning objectives
10.1 Introduce analytical SQL
10.2 Use analytical functions
10.3 Explore partitioning functions
10.4 Utilize windowing functions
Put it all together
Lesson 11: Simple Reports Using SQL*Plus
Learning objectives
11.1 Introduce SQL*Plus formatting commands
11.2 Build simple reports
11.3 Utilize HTML reports
11.4 Explore creating pivot tables
11.5 Introduce Oracle scripting
11.6 Hands-on Lab
Put it all together
Lesson 12: Toad and SQL Developer Reports
Learning objectives
12.1 Use the Toad Report Writer
12.2 Explore creating reports using SQL Developer
12.3 Hands-on Lab
Put it all together
Lesson 13: Spreadsheets and Saving the Data Using the Tools
Learning objectives
13.1 Explore saving data/creating spreadsheets using SQL*Plus
13.2 Utilize saving data/creating spreadsheets using SQL Developer
13.3 Work with saving data/creating spreadsheets/Access databases using Toad
13.4 Hands-on Lab
Put it all together
Lesson 14: Microsoft Office Programs with Oracle SQL
Learning objectives
14.1 Introduce ODBC drivers
14.2 Implement Oracle connections using ODBC
14.3 Explore SQL from Excel
14.4 Understand SQL from Access
Put it all together
Summary
Oracle SQL: Summary
Introduction
Oracle PL/SQL Programming Fundamentals LiveLessons: Introduction
Lesson 1: PL/SQL Course Overview
Learning Objectives
1.1 Understand the course pre-requisites
1.2 Learn the Oracle database objects to be used in this course
1.3 Review the command-line SQL*Plus tool
1.4 Review SQL Developer tool
1.5 Review TOAD tool
Put it all together
Lesson 2: Oracle PL/SQL Overview
Learning Objectives
2.1 Review Oracle architecture
2.2 Review PL/SQL overview
Put it all together
Lesson 3: PL/SQL Basics and Variables
Learning Objectives
3.1 Explore PL/SQL basics
3.2 Use PL/SQL variables
Put it all together
Lesson 4: Logic Flow
Learning Objectives
4.1 Understand IF/THEN/ELSE logic
4.2 Explore the CASE statement
4.3 Utilize looping syntax
Put it all together
Lesson 5: PL/SQL Cursors
Learning Objectives
5.1 Introduce PL/SQL cursors and the implicit cursor
5.2 Explore explicit cursors
Put it all together
Lesson 6: PL/SQL Exception Handling
Learning Objectives
6.1 Introduce PL/SQL exceptions
6.2 Explore PL/SQL exception basic syntax
6.3 Discover implicit vs explicit exceptions
6.4 Work with SQLCODE and SQLERRM features
6.5 Understand user-defined exceptions
6.6 Use application-defined exceptions
Put it all together
Lesson 7: PL/SQL Procedures and Functions
Learning Objectives
7.1 Introduce PL/SQL procedures and functions
7.2 Explore PL/SQL procedures
7.3 Understand PL/SQL functions
7.4 Work with parameter passing
Put it all together
Lesson 8: PL/SQL Packages
Learning Objectives
8.1 Introduce PL/SQL packages
8.2 Explore the package syntax
8.3 Review executing code in packages
Put it all together
Lesson 9: PL/SQL Triggers
Learning Objectives
9.1 Introduce PL/SQL triggers
9.2 Explore the trigger syntax
9.3 Review the order of trigger processing and other restrictions
9.4 Work with useful examples
Put it all together
Lesson 10: PL/SQL New Features
Learning Objectives
10.1 Introduce PL/SQL new features
10.2 Explore the new features with tips and techniques
Put it all together
Lesson 11: PL/SQL Collections
Learning Objectives
11.1 Introduce PL/SQL collections
11.2 Explore the collection syntax
11.3 Review code examples
11.4 Explore bulk binding techniques
Put it all together
Lesson 12: PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips
Learning Objectives
12.1 Introduce PL/SQL debugging, PL/SQL profiling, PL/SQL coding tips
12.2 Explore the TOAD debugger
12.3 Explore the SQL developer debugger
12.4 Review the PL/SQL profiler
12.5 Use the PL/SQL profiler with SQL*Plus
12.6 Use the TOAD PL/SQL profiler
12.7 Review PL/SQL coding tips
Put it all together
Oracle PL/SQL Programming Fundamentals LiveLessons: Summary
Summary
Introduction
Toad for Oracle: Introduction
Lesson 1: Toad for Oracle Course Overview
Learning objectives
1.1 Understand the course pre-requisites
1.2 Understand the course database objects and the course download
1.3 Introduce Toad for Oracle Unleashed book
Put it all together
Lesson 2: Toad Configuration and Setup Option
Learning objectives
2.1 Install Toad
2.2 Understand login panel options and configuration
2.3 Explore useful Toad options panels
2.4 Customize the Tool bar
Put it all together
Lesson 3: SQL and the Editor Window
Learning objectives
3.1 Use the editor window
3.2 Work with the describe panel
3.3 Discover useful SQL code
3.4 Work with the Toad SQL history feature
3.5 Discover useful user enhancements
3.6 Introduce Toad’s Code Review
3.7 Hands-on Lab
Put it all together
Lesson 4: The Schema Browser
Learning objectives
4.1 Use the Toad schema browser panel to configure the interface LHS and RHS options
4.2 Manage vast numbers of objects
4.3 Create object wizards from navigator tree
4.4 Explore additional data grid features
4.5 Export data from the data grid
4.6 Create reports from data grids using the Fast Reports interface
4.7 Hands-on Lab
Put it all together
Lesson 5: PL/SQL Tips and Techniques
Learning objectives
5.1 Explore PL/SQL
5.2 Work with code templates
5.3 Use the PL/SQL symbolic debugger
5.4 Hands-on Lab
Put it all together
Lesson 6: Toad as a SQL Tuning Tool
Learning objectives
6.1 Utilize Toad Explain Plan features
6.2 Use the query viewer
6.3 Work with auto trace
6.4 Use Auto Optimize SQL/SQL Tuning Advisor
6.5 Utilize Session Browser to show SQL, wait events and locking
6.6 Work with SQL Trace (10046 trace)
6.7 Work with the PL/SQL profiler
6.8 Hands-on Lab
Put it all together
Lesson 7: Toad for the Database Administrator
Learning objectives
7.1 Utilize Toad monitoring features for the DBA
7.2 Review Toad’s tablespace management options
7.3 Work with statistics and useful monitoring assistance
7.4 Review additional DBA topics in Toad
Put it all together
Lesson 8: Other Useful Features of Toad
Learning objectives
8.1 Explore Automation Designer
8.2 Utilize Finding Objects
8.3 Work with HTML doc generator
8.4 Use Toad’s compare features
8.5 Create objects and scripts using Toad
8.6 Explore various Toad reports
8.7 Review useful keystrokes in Toad
8.8 Hands-on Lab
Put it all together
Lesson 9: Object and Data Relationship Features, Query Builder Features
Learning objectives
9.1 Discover master/detail data browsing
9.2 Use Entity Relationship Diagram
9.3 Use Query Builder
9.4 Hands-on Lab
Put it all together
Summary
Toad for Oracle: Summary