Learning Path: Beginner Oracle Developer

Learning Path: Beginner Oracle Developer

by Dan Hotka
Released June 2016
Publisher(s): Addison-Wesley Professional
ISBN: 0134664035

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

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 language

Overview

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.

Table of contents

  1. Introduction
    1. Oracle SQL: Introduction
  2. Lesson 1: Oracle SQL Course Overview
    1. Learning objectives
    2. 1.1 Understand the course pre-requisites
    3. 1.2 Utilize the course download
    4. 1.3 Understand the course database objects
    5. 1.4 Introduce the Database Relational Model
    6. 1.5 Introduce the Oracle Architecture
    7. Put it all together
  3. Lesson 2: Introduction to the Course Tools
    1. Learning objectives
    2. 2.1 Work with SQL*Plus
    3. 2.2 Utilize SQL*Developer
    4. 2.3 Explore Toad
    5. Put it all together
  4. Lesson 3: Introduction to SQL
    1. Learning objectives
    2. 3.1 Understand Simple SQL queries
    3. 3.2 Work with Where clauses
    4. 3.3 Hands-on Lab
    5. Put it all together
  5. Lesson 4: Table Joins
    1. Learning objectives
    2. 4.1 Discover available tables/columns
    3. 4.2 Use the tools to create SQL without typing
    4. 4.3 Understand multiple table joins
    5. 4.4 Work with ANSI syntax
    6. 4.5 Use Oracle12 new ANSI join syntax
    7. 4.6 Explore sorting options
    8. 4.7 Hands-on Lab
    9. Put it all together
  6. Lesson 5: Functions and Pseudocolumns
    1. Learning objectives
    2. 5.1 Utilize pseudocolumns
    3. 5.2 Review character/string functions
    4. 5.3 Explore date functions
    5. 5.4 Understand number functions
    6. 5.5 Work with Decode/Case/regular expressions/pattern matching functions
    7. 5.6 Work with Group By functions
    8. 5.7 Use multiple functions
    9. 5.8 Use the functions from the tools
    10. 5.9 Hands-on Lab
    11. Put it all together
  7. Lesson 6: Advanced Queries Using Sub-Queries
    1. Learning objectives
    2. 6.1 Understand sub-queries
    3. 6.2 Work with in-line views
    4. 6.3 Utilize SQL coding tips and techniques
    5. 6.4 Hands-on Lab
    6. Put it all together
  8. Lesson 7: Oracle Data Manipulation and Definition Language
    1. Learning objectives
    2. 7.1 Understand how to create tables
    3. 7.2 Utilize subsets of tables
    4. 7.3 Work with inserting data
    5. 7.4 Explore updating data
    6. 7.5 Utilize deleting data
    7. 7.6 Use the MERGE command
    8. 7.7 Utilize the tools to perform DML and DDL operations
    9. 7.8 Hands-on Lab
    10. Put it all together
  9. Lesson 8: Database Management
    1. Learning objectives
    2. 8.1 Work with sequences
    3. 8.2 Understand identity columns
    4. 8.3 Explore creating views
    5. 8.4 Understand permissions
    6. 8.5 Use indexes to increase SQL performance
    7. 8.6 Introduce SQL performance tuning
    8. 8.7 Hands-on Lab
    9. Put it all together
  10. Lesson 9: Data Relationships
    1. Learning objectives
    2. 9.1 Learn what referential integrity is
    3. 9.2 Explore your data relationships
    4. 9.3 Use entity-relationship diagrams
    5. 9.4 View related data
    6. 9.5 Save related data (useful for test data/reports)
    7. 9.6 Live Demonstration
    8. Put it all together
  11. Lesson 10: Analytical SQL
    1. Learning objectives
    2. 10.1 Introduce analytical SQL
    3. 10.2 Use analytical functions
    4. 10.3 Explore partitioning functions
    5. 10.4 Utilize windowing functions
    6. Put it all together
  12. Lesson 11: Simple Reports Using SQL*Plus
    1. Learning objectives
    2. 11.1 Introduce SQL*Plus formatting commands
    3. 11.2 Build simple reports
    4. 11.3 Utilize HTML reports
    5. 11.4 Explore creating pivot tables
    6. 11.5 Introduce Oracle scripting
    7. 11.6 Hands-on Lab
    8. Put it all together
  13. Lesson 12: Toad and SQL Developer Reports
    1. Learning objectives
    2. 12.1 Use the Toad Report Writer
    3. 12.2 Explore creating reports using SQL Developer
    4. 12.3 Hands-on Lab
    5. Put it all together
  14. Lesson 13: Spreadsheets and Saving the Data Using the Tools
    1. Learning objectives
    2. 13.1 Explore saving data/creating spreadsheets using SQL*Plus
    3. 13.2 Utilize saving data/creating spreadsheets using SQL Developer
    4. 13.3 Work with saving data/creating spreadsheets/Access databases using Toad
    5. 13.4 Hands-on Lab
    6. Put it all together
  15. Lesson 14: Microsoft Office Programs with Oracle SQL
    1. Learning objectives
    2. 14.1 Introduce ODBC drivers
    3. 14.2 Implement Oracle connections using ODBC
    4. 14.3 Explore SQL from Excel
    5. 14.4 Understand SQL from Access
    6. Put it all together
  16. Summary
    1. Oracle SQL: Summary
  17. Introduction
    1. Oracle PL/SQL Programming Fundamentals LiveLessons: Introduction
  18. Lesson 1: PL/SQL Course Overview
    1. Learning Objectives
    2. 1.1 Understand the course pre-requisites
    3. 1.2 Learn the Oracle database objects to be used in this course
    4. 1.3 Review the command-line SQL*Plus tool
    5. 1.4 Review SQL Developer tool
    6. 1.5 Review TOAD tool
    7. Put it all together
  19. Lesson 2: Oracle PL/SQL Overview
    1. Learning Objectives
    2. 2.1 Review Oracle architecture
    3. 2.2 Review PL/SQL overview
    4. Put it all together
  20. Lesson 3: PL/SQL Basics and Variables
    1. Learning Objectives
    2. 3.1 Explore PL/SQL basics
    3. 3.2 Use PL/SQL variables
    4. Put it all together
  21. Lesson 4: Logic Flow
    1. Learning Objectives
    2. 4.1 Understand IF/THEN/ELSE logic
    3. 4.2 Explore the CASE statement
    4. 4.3 Utilize looping syntax
    5. Put it all together
  22. Lesson 5: PL/SQL Cursors
    1. Learning Objectives
    2. 5.1 Introduce PL/SQL cursors and the implicit cursor
    3. 5.2 Explore explicit cursors
    4. Put it all together
  23. Lesson 6: PL/SQL Exception Handling
    1. Learning Objectives
    2. 6.1 Introduce PL/SQL exceptions
    3. 6.2 Explore PL/SQL exception basic syntax
    4. 6.3 Discover implicit vs explicit exceptions
    5. 6.4 Work with SQLCODE and SQLERRM features
    6. 6.5 Understand user-defined exceptions
    7. 6.6 Use application-defined exceptions
    8. Put it all together
  24. Lesson 7: PL/SQL Procedures and Functions
    1. Learning Objectives
    2. 7.1 Introduce PL/SQL procedures and functions
    3. 7.2 Explore PL/SQL procedures
    4. 7.3 Understand PL/SQL functions
    5. 7.4 Work with parameter passing
    6. Put it all together
  25. Lesson 8: PL/SQL Packages
    1. Learning Objectives
    2. 8.1 Introduce PL/SQL packages
    3. 8.2 Explore the package syntax
    4. 8.3 Review executing code in packages
    5. Put it all together
  26. Lesson 9: PL/SQL Triggers
    1. Learning Objectives
    2. 9.1 Introduce PL/SQL triggers
    3. 9.2 Explore the trigger syntax
    4. 9.3 Review the order of trigger processing and other restrictions
    5. 9.4 Work with useful examples
    6. Put it all together
  27. Lesson 10: PL/SQL New Features
    1. Learning Objectives
    2. 10.1 Introduce PL/SQL new features
    3. 10.2 Explore the new features with tips and techniques
    4. Put it all together
  28. Lesson 11: PL/SQL Collections
    1. Learning Objectives
    2. 11.1 Introduce PL/SQL collections
    3. 11.2 Explore the collection syntax
    4. 11.3 Review code examples
    5. 11.4 Explore bulk binding techniques
    6. Put it all together
  29. Lesson 12: PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips
    1. Learning Objectives
    2. 12.1 Introduce PL/SQL debugging, PL/SQL profiling, PL/SQL coding tips
    3. 12.2 Explore the TOAD debugger
    4. 12.3 Explore the SQL developer debugger
    5. 12.4 Review the PL/SQL profiler
    6. 12.5 Use the PL/SQL profiler with SQL*Plus
    7. 12.6 Use the TOAD PL/SQL profiler
    8. 12.7 Review PL/SQL coding tips
    9. Put it all together
  30. Oracle PL/SQL Programming Fundamentals LiveLessons: Summary
    1. Summary
  31. Introduction
    1. Toad for Oracle: Introduction
  32. Lesson 1: Toad for Oracle Course Overview
    1. Learning objectives
    2. 1.1 Understand the course pre-requisites
    3. 1.2 Understand the course database objects and the course download
    4. 1.3 Introduce Toad for Oracle Unleashed book
    5. Put it all together
  33. Lesson 2: Toad Configuration and Setup Option
    1. Learning objectives
    2. 2.1 Install Toad
    3. 2.2 Understand login panel options and configuration
    4. 2.3 Explore useful Toad options panels
    5. 2.4 Customize the Tool bar
    6. Put it all together
  34. Lesson 3: SQL and the Editor Window
    1. Learning objectives
    2. 3.1 Use the editor window
    3. 3.2 Work with the describe panel
    4. 3.3 Discover useful SQL code
    5. 3.4 Work with the Toad SQL history feature
    6. 3.5 Discover useful user enhancements
    7. 3.6 Introduce Toad's Code Review
    8. 3.7 Hands-on Lab
    9. Put it all together
  35. Lesson 4: The Schema Browser
    1. Learning objectives
    2. 4.1 Use the Toad schema browser panel to configure the interface LHS and RHS options
    3. 4.2 Manage vast numbers of objects
    4. 4.3 Create object wizards from navigator tree
    5. 4.4 Explore additional data grid features
    6. 4.5 Export data from the data grid
    7. 4.6 Create reports from data grids using the Fast Reports interface
    8. 4.7 Hands-on Lab
    9. Put it all together
  36. Lesson 5: PL/SQL Tips and Techniques
    1. Learning objectives
    2. 5.1 Explore PL/SQL
    3. 5.2 Work with code templates
    4. 5.3 Use the PL/SQL symbolic debugger
    5. 5.4 Hands-on Lab
    6. Put it all together
  37. Lesson 6: Toad as a SQL Tuning Tool
    1. Learning objectives
    2. 6.1 Utilize Toad Explain Plan features
    3. 6.2 Use the query viewer
    4. 6.3 Work with auto trace
    5. 6.4 Use Auto Optimize SQL/SQL Tuning Advisor
    6. 6.5 Utilize Session Browser to show SQL, wait events and locking
    7. 6.6 Work with SQL Trace (10046 trace)
    8. 6.7 Work with the PL/SQL profiler
    9. 6.8 Hands-on Lab
    10. Put it all together
  38. Lesson 7: Toad for the Database Administrator
    1. Learning objectives
    2. 7.1 Utilize Toad monitoring features for the DBA
    3. 7.2 Review Toad’s tablespace management options
    4. 7.3 Work with statistics and useful monitoring assistance
    5. 7.4 Review additional DBA topics in Toad
    6. Put it all together
  39. Lesson 8: Other Useful Features of Toad
    1. Learning objectives
    2. 8.1 Explore Automation Designer
    3. 8.2 Utilize Finding Objects
    4. 8.3 Work with HTML doc generator
    5. 8.4 Use Toad’s compare features
    6. 8.5 Create objects and scripts using Toad
    7. 8.6 Explore various Toad reports
    8. 8.7 Review useful keystrokes in Toad
    9. 8.8 Hands-on Lab
    10. Put it all together
  40. Lesson 9: Object and Data Relationship Features, Query Builder Features
    1. Learning objectives
    2. 9.1 Discover master/detail data browsing
    3. 9.2 Use Entity Relationship Diagram
    4. 9.3 Use Query Builder
    5. 9.4 Hands-on Lab
    6. Put it all together
  41. Summary
    1. Toad for Oracle: Summary

Product information

  • Title: Learning Path: Beginner Oracle Developer
  • Author(s): Dan Hotka
  • Release date: June 2016
  • Publisher(s): Addison-Wesley Professional
  • ISBN: 0134664035