SQL Advanced Techniques

Topics

• Database concepts
• Alias and Data Dictionary
• Field security in Visual Designer
• Call an Oracle function in the select and where clause: upper, to_date, to_char, string, sysdate, between
• Call a Banner function in the SELECT and WHERE clause
• Use the Object Source DataBlock to find a function and use it correctly
• Identify when to use a case statement
• Case and decode
  • Use a case in the SELECT, WHERE and ORDER BY clause
  • Identify when to use a decode statement
  • Use a decode
  • List the differences between a case and a decode
• Use a case/decode order in a drop down box and required expression in the group band
• Use a column number in the order by clause
• Distinct – when to use.
• Max, min, average, count, sum
• Group by: Describe and use
• Create SQL for chart
• Describe and use the pivot function
• Unions
  • Describe the 4 types of unions: union, union all, intersect and minus
  • Describe when to use them
  • Create and use a union operator
• Having
• Datasets in Banded and Extract reports
• Joins: inner, outer right, outer left
  • Define
  • Create in the visual designer
  • Add to data dictionary
  • Create with free type
• Subqueries
  • Scalar
  • Noncorrelated
  • Correlated
  • In line view
• Subqueries with in, not in, exist, not exist
• With clause
• Insert, update and delete
  • How to write in Argos using SQL Variables
  • How to use as a Banner letter generation replacement option
• SQL Tuning
• SQL Best Practices
• Change table order in Argos
• Explain Plan
• What is PL/SQL
• Creating functions on the database
• Calling PL/SQL functions within Argos
• Create tables and views
• Create cursors in anonymous blocks in Argos SQL variables
• Understand concepts of Oracle collections