Oracle SQL

Oracle SQL

Released August 2015
Publisher(s): Pearson
ISBN: 013427704X

Watch 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

Oracle SQL LiveLessons 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.

Description

In this LiveLessons course, Oracle ACE Director Dan Hotka will teach you how to use the SQL query language, interpret business questions into SQL code, and how to use Where clauses sub-queries, complex table join conditions, and how to create table objects and populate tables with data. Students will also learn how to access Oracle from Microsoft products such as Excel. In addition, they will learn how analytical SQL can be used for business analysis. This course will use both Toad and SQL Developer for SQL examples.

Related Files

The companion files for this LiveLesson can be downloaded from http://www.informit.com/title/9780134275741.

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.

Skill Level

  • Beginner

Learn How To

  • Use the SQL query language
  • Interpret business questions into SQL code
  • Understand Oracle standard SQL and ANSI SQL
  • Work with Where clauses, sub-queries, and complex table join conditions
  • Create table objects and populate these tables with data
  • Access Oracle from Microsoft products such as Excel
  • Use analytical SQL, useful for business analysts

Who Should Take This Course

  • Students who are new to SQL and want to learn Oracle SQL

Course Requirements

  • Basic knowledge of what is a database, especially RDBMS and what is a computer programming language

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

Product information

  • Title: Oracle SQL
  • Author(s):
  • Release date: August 2015
  • Publisher(s): Pearson
  • ISBN: 013427704X