Course Code:
sqlfunbesp
Duration:
14 hours
Prerequisites:
The course is suitable for those who have some knowledge of SQL as well as those who are using ORACLE for the first time.
Previous experience with an interactive computer system is desirable but not essential.
Course Outline:
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