SQL Fundamentals Certificate for Heather Ip

Oracle Flavor Part

Data Retrieval

• SQL Developer
• SQL Developer - Connection
• Viewing Table Information
• Using SQL, Where Clause
• Using Comments
• Character Data
• Users and Schemas
• AND and OR Clause
• Using Brackets
• Date Fields
• Using Dates
• Formatting Dates
• Date Formats
• TO_DATE
• TRUNC
• Date Display
• Order By Clause
• DUAL Table
• Concatenation
• Selecting Text
• IN Operator
• BETWEEN Operator
• LIKE Operator
• Common Errors
• UPPER Function
• Single Quotes
• Finding Metacharacters
• Regular Expressions
• REGEXP_LIKE Operator
• Null Values
• IS NULL Operator
• NVL
• Accepting User Input

Using Functions

• TO_CHAR
• TO_NUMBER
• LPAD
• RPAD
• NVL
• NVL2 Function
• DISTINCT Option
• SUBSTR
• INSTR
• Date Functions
• Aggregate Functions
• COUNT
• Group By Clause
• Rollup and Cube Modifiers
• Having Clause
• Grouping By Functions
• DECODE
• CASE

Sub-Query & Union

• Single Row Sub-queries
• Union
• Union - All
• Intersect and Minus
• Multiple Row Sub-queries
• Union - Checking Data
• Outer Join

More on Joins

• Joins
• Inner Join
• Natural Join
• Equi-Join
• Outer Joins
• Left Outer Join
• RIght Outer Join
• Full Outer Join
• Using UNION
• Join Algorithms
• Nested Loop

Transact SQL Flavor Part

Basics

• Selection of certain rows/records
• Selection of values in a range
• Selection of values matching a pattern mask
• Selection of values within a list
• Selection of calculated and derived values
• How to control column headings in query results
• How to send query results to external files

Joining Tables

• Principles of joining tables:
  • Use of inner join
  • Use of non-equi join
  • Use of outer join

Joining Queries

• Union operator
• Intersect operator
• Except operator

Simple Functions

• Conversion functions
• Date functions
• Number functions
• Text functions
• Group/summary/aggregate functions

Sub-Queries

• Principles of sub-queries
• How to filter rows from main query
• Use of nested sub-query
• Use of multi-column sub-query
• Use of correlated sub-query
• Use of sub-query as an inline view and common table expression
• Use of sub-query as a column in main query

Case Statements

• Principles of case statements
• Use of case statement to derive column values
• Use of nested case statements
• Use of case statements to produce pivot tables
• Use of case statement with sub-queries

Data Definition - only temporary tables

• How to create tables
• How to remove tables and views