• This course has been retired. Users who have purchased the course will still have access to it, however it is no longer available for purchase.

Oracle 11g/12c: Advanced SQL

Course Features

  • Online Classes
  • Flexible Schedule
  • Interactive
  • High Quality
  • Certifications
  • Instructor Help

Online video courses with full instructor support,
just like in the classroom, only better. Enroll today!

Video Title
Length
00: Oracle 11g/12c: Advanced SQL Online Training - Course Introduction
8 min
01: Using the Set Operators
19 min
02: Using the Set Operators Demo
17 min
03: Flashback Query
19 min
04: Flashback Query Demo
18 min
05: Using Subqueries
18 min
06: Using Subqueries Demo
20 min
07: Hierarchical Retrieval
17 min
08: Hierarchical Retrieval Demo
16 min
09: Using the Data Dictionary Views
13 min
10: Using the Data Dictionary Views Demo
18 min
11: Creating Database Objects I (Views)
14 min
12: Creating Database Objects I (Views) Demos
15 min
13: Creating Database Objects II (Sequences)
18 min
14: Creating Database Objects II (Sequences) Demo
14 min
15: Creating Database Objects III (Synonyms)
16 min
16: Creating Database Objects III (Synonyms) Demo
14 min
17: Creating Database Objects IV (Tables)
24 min
18: Creating Database Objects IV (Tables) Demo
19 min
19: Creating Database Objects V (Indexes)
16 min
20: Creating Database Objects V (Indexes) Demo
14 min
21: Creating Database Objects VI (Constraints)
14 min
22: Creating Database Objects VI (Constraints) Demo
12 min
23: Regular Expression Support
12 min
24: Regular Expression Support Demo
10 min
25: Advanced Oracle: 12c New Features
28 min
Total:
7 hrs 3 mins
Need Help Playing Your Videos or Quiz Modules? Click Here.

What should I do if my video will not play?

There are many things that can affect the quality of your video so we have put together these instructions to help you review all the things that need to be tested in order to trouble-shoot video quality.

The first thing we want to assure you is that we have done everything possible to make sure that our videos will play well.  Our videos are hosted in the cloud by Akamai.  Akamai has servers around the world to make sure that your videos are sent from the server that is closest to you.  By hosting our videos with Akamai we make sure that you receive the highest video quality. If you are experiencing video buffering or freezing at home or at the office there are a number of things you can do to help us troubleshoot.

  • Proper Video Quality: Although the player will attempt to match the video quality with your bandwidth, you may find that setting your video quality manually will give you better performance.
  • Popup Blockers: Please check to make sure that your pop-up blocker is turned off when you go to our website. This will prevent the video window from opening.
  • Your Connection Speed: Internet speed needs to be 2.0 Mbps upload and download to successfully watch the videos.  You will want to check and see if your connection speed is 2.0 Mbps or higher.  You can do this by clicking on this link and running a speed test.
  • Jitter and Packet Loss: Your Jitter rate lets us know if your connection speed is consistent while your Packet Loss rate lets us know if your internet connection could be losing pieces of the video.
    • Go to https://www.dotcom-tools.com/ping-test.aspx to determine if there is any Jitter or Packet Loss on your connection.
    • This is measured by a letter scale with A being the best.
      • If your Packet Loss is below a B, your connection is losing pieces of the video and causing the video to skip.
      • If your Jitter is below a B, your connection speed is very inconsistent and may be changing while you are watching the video which will result in buffering issues.
      • If either of these is the case you will need to contact your internet provider.
  • Shared Internet Connection: If you are on a shared internet connection, test playing videos during non-peak hours when usage by others on your network is low.
  • Reset Your Browser: If you've tried all the previous steps, your browser may be retaining data that needs to be deleted, and this requires a reset. If you are using Chrome, use the instructions in this link to reset your browser. If you are using Internet Explorer (IE) you may need to reset it. You can do this by clicking on the cog icon on the top right of your page. Then click on tools, internet options, advanced and reset. If you are using Firefox, use the instructions in this link to reset your browser.
  • Other things to check:
    • At home or at work, test using different browsers and computers to see if you get the same result.
      • Make sure you have the latest version of the browser you are using.
    • If you are using Internet Explorer (IE) you may need to reset it. You can do this by clicking on the cog icon on the top right of your page. Then click on tools, internet options, advanced and reset.
    • Try from a different location using a different network.
    • If you are on a wireless connection try a wired solution.
    • If you are on a home network:
      • Test playing the videos when you are the only user on the network.
      • Try by-passing your router to eliminate it as the point of failure. A direct plug from your ISP to your computer will take care of this and will make sure that neither your router nor other users are affecting your connection.
    • If you are running Firefox, you can check your plugins here: https://www.mozilla.org/en-US/plugincheck/
  • Make sure you do not have any other programs open. File sharing and peer to peer sharing programs, even when limited to low bandwidth, can make it almost impossible to stream properly.
  • Tracking Progress on Mobile Devices: The GogoTraining site is designed to use the native player found in your browser. If you are taking your course on a mobile device and want to make sure your progress is tracked, then you will need to play the videos from the native player that is in your browser. Do not download any third party apps to play the videos as they do not allow your progress to be recorded. If you have installed a third party app and want to receive a Letter of Course Attendance, then you will need to uninstall the 3rd party app or see if the app has the ability for you to disable it for certain sites.
  • Submitting Debugging Information If you have tried everything else and still are having trouble viewing the videos, you can visit this page where you can help us debug your particular setup by following the instructions on the page and submit the debugging information to us.
Meet Your Instructor

John Mullins

John is an Oracle Instructor, DBA, and Developer with more than 25 years of Oracle experience. His credentials include the Oracle Certified Professional DBA and Certified Technical Trainer certifications.

Setting up Your Computer

System Requirements

Hardware:

  • Operating system supported by Oracle 10g R1 or higher - Windows XP, Solaris, HP/UX, AIX, Linux
  • 1 GB RAM minimum, 2 GB RAM preferred
  • 7-10 GB free disk space

Software:

This course is designed to use Oracle 11g editions below but will also support 12c. Set up guides for both versions are included with the course.

  • Oracle 11g Express Edition (R1+) or Oracle 12c Express Edition (free versions)
Course Links

Course Links

These links are activated when you purchase this course.

  • Course Survey
Course Transcript

Transcript

This course comes with a Transcript that tracks your progress. You can use your transcript to view and monitor your progress and when you complete the course, you can print or email the transcript.

Certificate of Completion

Certificate of Completion

You will be awarded a Letter of Course Attendance when you finish this course. You will be able to print or email the Letter of Attendance and share your accomplishment with everyone.

How to Take Your Exam

How to Take Your Exam

Oracle 11g/12c Introduction to SQL combined with Oracle 11g/12c Advanced SQL helps you to prepare for any of the following exams: 1Z0-047 Oracle Database SQL Expert or 1Z0-051 Oracle Database 11g: SQL Fundamentals I or 1Z0-061 Oracle Database 12c: SQL Fundamentals. When you pass one of the 1Z0-051 or 1Z0-061 exams, you have completed a portion of the requirements towards one of several Oracle’s Certified Associate (OCA) certifications. Passing the 1Z0-047 exam gives you the Oracle Database SQL Certified Expert certification.

For more information about certification, requirements and fees, click on the link found in Related Links.

The following certification prep books provide further help including valuable practice exams. Combining a hands-on course with practice exams is highly recommended to prepare for Oracle’s various certifications.

 

OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

by Steve O'Hearn   ISBN-13: 978-0071614214    ISBN-10: 9780071614214

OCA Oracle Database 11g SQL Fundamentals I Exam Guide Exam (1Z0-051)

by John Watson  ISBN-13: 978-0071597869    ISBN-10: 9780071597869

OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061)

by Roopesh Ramklass  ISBN-13: 978-0071820288    ISBN-10: 0071820280

 

Course Description

In this course, students learn how to use the advanced features of SQL in order to query and manipulate data within the database. Advanced querying and reporting techniques including using SET operators, writing hierarchical queries and using the Flashback Query feature are explained. Schema objects such as Tables, Indexes and Constraints are discussed in detail. 

In order to take this course you need to have taken Oracle 11g/12c: Introduction to SQL and have experience in writing Oracle SQL statements.

In This Course You Will Learn:

  • Combine SQL result sets into a single result set using SET operators
  • Access data from a previous point in time using the flashback query feature
  • Combine multiple SQL statements into one statement
  • Create various database objects

Prerequisites

You need to have equivalent experience or have taken the following GogoTraining courses:

Outline

Module 00: Oracle 11g/12c: Advanced SQL Online Training - Course Introduction

Module 01: Using the Set Operators
•    Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
•    Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
•    Describe and use the INTERSECT operator
•    Explain and use the MINUS operator
•    List the SET operator guidelines
•    Order results when using the UNION operator
•    Using the ORDER BY clause with SET operators
•    Lab Exercise

Module 02: Using the Set Operators Demo

Module 03: Flashback Query
•    Prerequisites for using the Flashback Query feature
•    Uses for the Flashback Query feature
•    Limitations of the Flashback Query feature
•    Statement level vs. session level usage
•    Using statement level Flashback Query
•    The AS OF TIMESTAMP option
•    Using session level Flashback Query
•    The DBMS_FLASHBACK Oracle supplied package
•    Lab Exercise

Module 04: Flashback Query Demo

Module 05: Using Subqueries
•    Use a subquery to solve a problem
•    Identify where subqueries can be placed in a SELECT statement
•    Describe the types of subqueries (single row, multiple row)
•    The single row subquery operators
•    The multiple row subquery operators
•    Using multi-column subqueries
•    Writing a correlated subquery
•    Lab Exercise

Module 06: Using Subqueries Demo

Module 07: Hierarchical Retrieval
•    Hierarchical Queries
•    Ranking Rows with LEVEL
•    Formatting Hierarchical Reports Using LEVEL and LPAD
•    Pruning Branches with the WHERE and CONNECT BY clauses
•    Lab Exercise

Module 08: Hierarchical Retrieval Demo

Module 09: Using the Data Dictionary Views
•    Types of Data Dictionary Views
•    Privileges needed to access the Data Dictionary Views
•    Using the Data Dictionary Views to view database metadata information
•    Helpful Data Dictionary Views
•    Lab Exercise

Module 10: Using the Data Dictionary Views Demo

Module 11: Creating Database Objects I (Views)
•    Creating a view syntax
•    View options
•    Privileges needed to create a view
•    Benefits of views
•    Limitations of views
•    Using views in SQL statements
•    Retrieving View definitions from the Data Dictionary
•    Lab Exercise

Module 12: Creating Database Objects I (Views) Demos

Module 13: Creating Database Objects II (Sequences)
•    Creating a sequence syntax
•    Privileges needed to create a sequence
•    Sequence usage
•    Sequence options
•    Using sequences in SQL statements
•    Retrieving Sequence definitions from the Data Dictionary
•    Lab Exercise

Module 14: Creating Database Objects II (Sequences) Demo

Module 15: Creating Database Objects III (Synonyms)
•    Types of synonyms
•    Private vs. Public synonyms
•    Creating a synonym syntax
•    Privileges needed to create a synonym
•    Synonym usage
•    Using synonyms in SQL statements
•    Retrieving Synonym definitions from the Data Dictionary
•    Lab Exercise

Module 16: Creating Database Objects III (Synonyms) Demo

Module 17: Creating Database Objects IV (Tables)
•    Types of Tables
•    The Basic Heap Table
•    Creating a table syntax
•    Valid column datatypes
•    Privileges needed to create a table
•    Retrieving Table definitions from the Data Dictionary
•    Lab Exercise

Module 18: Creating Database Objects IV (Tables) Demo

Module 19: Creating Database Objects V (Indexes)
•    Types of Indexes
•    Benefits of Indexes
•    The B-Tree Index
•    Creating an index syntax
•    Privileges needed to create a index
•    Retrieving Index definitions from the Data Dictionary
•    Lab Exercise

Module 20: Creating Database Objects V (Indexes) Demo

Module 21: Creating Database Objects VI (Constraints)
•    Types of Constraints
•    Primary Key, Foreign Key, Unique, NOT NULL, Check constraints
•    Review constraint purposes
•    Creating a constraint syntax
•    CREATE vs. ALTER syntax
•    Retrieving Constraint definitions from the Data Dictionary
•    Lab Exercise

Module 22: Creating Database Objects VI (Constraints) Demo

Module 23: Regular Expression Support
•    Regular Expression Support Overview
•    Describing simple patterns for searching and manipulating data
•    Describing complex patterns for searching and manipulating data
•    Lab Exercise

Module 24: Regular Expression Support Demo

Module 25: Advanced Oracle: 12c New Features

•    Row Pattern Matching

•    A Sequence Change

•    Invisible Columns

•    Data Type Changes

•    UNION/UNION All Changes

Certification

Oracle 11g/12c Introduction to SQL combined with Oracle 11g/12c Advanced SQL helps you to prepare for any of the following exams: 1Z0-047 Oracle Database SQL Expert or 1Z0-051 Oracle Database 11g: SQL Fundamentals I or 1Z0-061 Oracle Database 12c: SQL Fundamentals. When you pass one of the 1Z0-051 or 1Z0-061 exams, you have completed a portion of the requirements towards one of several Oracle’s Certified Associate (OCA) certifications. Passing the 1Z0-047 exam gives you the Oracle Database SQL Certified Expert certification.

For more information about certification, requirements and fees, click on the link found in Related Links.

The following certification prep books provide further help including valuable practice exams. Combining a hands-on course with practice exams is highly recommended to prepare for Oracle’s various certifications.

 

OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

by Steve O'Hearn   ISBN-13: 978-0071614214    ISBN-10: 9780071614214

OCA Oracle Database 11g SQL Fundamentals I Exam Guide Exam (1Z0-051)

by John Watson  ISBN-13: 978-0071597869    ISBN-10: 9780071597869

OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061)

by Roopesh Ramklass  ISBN-13: 978-0071820288    ISBN-10: 0071820280

 

GogoTraining, GogoTraining Designs, and miscellaneous designs are the trademarks of GogoTraining, Inc.

The ITIL® courses on this site are offered by GogoTraining, an ATO of AXELOS Limited

ITIL® is a registered trade mark of AXELOS Limited, used under permission of AXELOS Limited. All rights reserved.

The Swirl logo® is a registered trade mark of AXELOS Limited, used under permission of AXELOS Limited. All rights reserved.

OBASHI® is a registered trademark of OBASHI Ltd. All rights reserved.

COBIT® is a trademark of ISACA® registered in the United States and other countries.

The International Organization for Standardization (ISO) is a non-governmental organization which is a network of the national standards institutes of 156 countries.

ISO is the owner of the ISO/IEC 20000 standard.

BRMP® is a Registered Trade Mark of Business Relationship Management Institute, Inc.

SIAM® is a registered trademark

VeriSM™ is a trade mark of IFDC

PMI®, PMP®, CAPM®, and PMBOK® are registered marks of the Project Management Institute, Inc.

AgilePM® is a registered trademark of Agile Business Consortium Limited. All rights reserved

All other trademarks on this site are the trademarks of their respective owners.

GogoTraining - Leader in IT & Management Training

©2009-2024 GogoTraining, Inc. All rights reserved.

Sign up

  • All GogoTraining Members receive the first 3 course modules for free so they can meet the instructor, understand what they will learn by taking the course and review the content. Sign Up today and get access to the best trainers and online self-paced video training content in the world.