Entry Level SQL Programming

Learning to program is both fun and easy by taking this brand new and exciting Introduction to Programming for Non-Programmers course! Not only will you learn the foundations of how to program, but you’ll also gain a solid foundation in the world’s most popular computer language: JavaScript.  Without having to install any special software, and using only your web browser and your text editor, you’ll journey into the exacting world of programming. Using an easy to learn training style, you’ll learn about the JavaScript language, functions, variables, making decisions, looping, and more. At the end of the course, you’ll be in a great position to take other introductory programming language course.

Module 00: Course Introduction

Module 01: Introduction to Programming

• What is a computer language?
• Why should you learn a computer language?
• What computer language should you learn first?
• Demo: Let's Make our First Statement in JavaScript!
• Lab Exercise: hello.html

Module 02: Introduction to JavaScript 

• JavaScript Advantages
• Web Browser and JavaScript
• .html File Extension
• Editing hello.html
• // and /* … */
• Demo: Hello World
• Lab Exercise: Hello World

Module 03: Functions Part 1  

• About Functions
• alert() w/o input parameter
• Semicolon
• alert() w/Input parameter
• Strings

Module 04: Functions Part 2 

• Defining Functions without inputs
• Defining Functions with inputs
• Returning values from Functions
• Demo: Using the Alert Function
• Lab Exercise: Using the Alert Function

Module 05: Variables 

• JavaScript Variables
• var keyword
• String variables
• Number variables
• Operators: +, -, *, /
• Confirm Function
• true, false
• prompt() Function
• Demo: Using Variables
• Lab Exercise: Using Variables

Module 06: Making Decisions 

• if statement
• if else statement
• confirm() with if statement
• confirm() with if-else statement
• Compare Operators: ==, <. >, <=, >=
• if-else-if…
• Demo: Making Decisions
• Lab Exercise: Making Decisions

Module 07: Repeating Steps 

• Repeating fixed number of times
• Repeating variable number of times
• while statement
• while statement with prompt()
• Checking for null
• Demo: Repeating Steps
• Lab Exercise: Repeating Steps 

Module 08: Course Summary

• Put All Skills Together To Write a Javascript App
• Review the key concepts
• Write a temperature conversion script!

The only prerequisite is a basic familiarity with computers, along with an eagerness to learn programming. You do not need any programming experience.

In this course students learn the basics of writing Oracle SQL statements. This course provides the SQL skills that allow developers, database administrators and end-users to write queries against single and multiple tables, manipulate data in tables and take advantage of Oracle supplied row functions.

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

Module 01: Writing SQL SELECT Statements

• Review the basic SQL SELECT statement syntax
• Select all columns using a wildcard notation from a table
• Valid SELECT statement expressions
• Write a query containing the arithmetic operators
• Use aliases to customize column headings
• Create a character expression with the concatenation operator
• Use the CASE Expression
• Lab Exercise

Module 02: Writing SQL SELECT Statements Demo

Module 03: Restricting Data

• Use the WHERE Clause
• WHERE clause operators
• =, <>, >, < operators
• BETWEEN, IN, LIKE, IS NULL operators
• Adding ANY or ALL to an operator
• List the Logical Conditions AND, OR, NOT
• Use Multiple Conditions in the WHERE clause
• Describe the Rules of Precedence
• Lab Exercise

Module 04: Restricting Data Demo

Module 05: Sorting Data

• Discuss default order of result sets
• Sort Rows with the ORDER BY Clause
• ORDER BY valid expressions
• DISTINCT and UNIQUE clauses
• Lab Exercise

Module 06: Sorting Data Demo

Module 07: Displaying Data from Multiple Tables

• Identify Types of Joins
• ANSI standard join operators vs. non-standard join methods
• Using the non-standard join methods
• Inner join vs. Outer join
• Generating a Cartesian Product
• ANSI standard join operators
• NATURAL JOIN, JOIN USING, JOIN ON, LEFT/RIGHT/FULL OUTER joins
• Lab Exercise

Module 08: Displaying Data from Multiple Tables Demo

Module 09: Character Row Functions

• Categories of row functions
• Statement behavior of row functions
• Useful / common character row functions
• Nest Functions to perform multiple tasks in one statement
• Lab Exercise

Module 10: Character Row Functions Demo

Module 11: Numeric Row Functions

• Where numeric row functions can and cannot be used
• Helpful numeric row functions in SQL statements
• Functionalities of common numeric row functions
• Explain Implicit and Explicit conversion
• Lab Exercise

Module 12: Numeric Row Functions Demos

Module 13: Date/Time Row Functions

• Where date/time row functions can and cannot be used
• Helpful date/time row functions in SQL statements
• Functionalities of common date/time row functions
• Lab Exercise

Module 14: Date/Time Row Functions Demo

Module 15: Miscellaneous Row Functions

• Using conversion functions
• Using conversion functions to format date/time data
• Environment and Data specific row functions
• The Decode function versus the Case Option
• Lab  Exercise

Module 16: Miscellaneous Row Functions Demo

Module 17: Aggregating Data Using Group Functions

• Categorize the Types of Group Functions
• Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
• Utilize the DISTINCT Keyword with the Group Functions
• Describe how Nulls are handled with the Group Functions
• Create Groups of Data with the GROUP BY Clause
• Group Data by more than one column
• Exclude Groups of Data with the HAVING Clause
• Differences between the HAVING clause and the WHERE clause
• Lab Exercise

Module 18: Aggregating Data Using Group Functions Demo

Module 19: Extensions to the GROUP BY Clause

• Using the CUBE option
• Using the ROLLUP option
• CUBE vs. ROLLUP output
• The GROUPING BY option
• Lab Exercise

Module 20: Extensions to the GROUP BY Clause Demo

Module 21: Manipulating Data I

• Write INSERT statements to add rows to a table
• Insert Special Values
• Copy Rows from Another Table
• Update Rows in a Table
• Use DELETE statements to remove rows from a table
• Delete Rows Based on Another Table
• The COMMIT and ROLLBACK statements
• Using the SAVEPOINT statement
• Lab Exercise

Module 22: Manipulating Data I Demo

Module 23: Manipulating Data II

• Describe the TRUNCATE Statement
• TRUNCATE vs. DELETE
• Using the Multi-table INSERT statement
• Using the MERGE statement
• Show how Read Consistency works
• Lab Exercise

Module 24: Manipulating Data II Demo

Module 25: 12c New Features

• Top-n Query
• Cascading Truncate Statement
• Native Left Outer Join

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

• Introduction to Programming and Coding for Everyone with JavaScript

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.

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

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

• Introduction to Programming for Non-Programmers
• Introduction to Programming and Coding for Everyone with JavaScript
• Oracle 10g/11g: Introduction to SQL
• Oracle 11g/12c: Introduction to SQL