TruTek

• TruTek has put together a 4 day Top Gun type Advanced SQL class that will teach the advanced capabilities of SQL; where Oracle, SQL Server, DB2 and my SQL differ from eacher; and which SQL features are native to the database.  After 4 days, students will be able to do serious SQL tasks, including:
• Find the top 3 products sold this month by region and compare the sales for t hese products against sales for the past 2 months.
• Find all customers who have ordered more than 25% of their annual purchases in December (or any parameterized value).
• Find out how many sales with loss leader products only had loss leaders (customers did not by anything other than the special), and what the percentage breakdown is by sales method (cash, credit card, account, voucher).
• Keep running totals by division for monthly sales reports
• Rank top N customers by region

Day 1

String Functions (Oracle, DB2 UDB, SQL Server)
Escape delimiters
Numeric functions (Oracle, DB2 UDB, SQL Server)
Date handling and formats
Joins
Outer joins
Cartesian product
Intersect, minus, union, union all
Composite where clause
Aggregate functions

Day 2

Group by and having
Inline views
Subqueries, multi-column subqueries, using ANY, SOME & ALL operator
Correlated subqueries, using WITH
Exists, not exists, eliminating duplicates
Multi-table inserts (insert all)
Differences between Oracle, DB2 UDB and SQL Server

Day 3

CASE expressions (Oracle, SQL Server, DB2 UDB)
Inline CAST-ing
Date-Time conversions
Converting from raw (hex data)
Oracle hierarchies:  Connect by: hierarchies, NOCYCLE, pruining, circular references
Direct path inserts
Null traps

Day 4 (OLAP)

Analytical functions:  ROLLUP, CUBE, partition over, top N
Pivot queries, LEAD, LAG, FIRST_VALUE, windowing clauses
Hints (Oracle)
Performance considerations
 

Benefits of Advanced Knowledge of SQL

• A Better understanding about her power, flexibility and limitations of SQL will equip SQL developers to create solutions better and faster.
• Decreae developer times.
• Increase the power of the information that is gathered.
• Increae the confidence of developers to be able to tackle more complex information delivery tasks.
• Competitive edge as developers can deploy solutions much faster than their counterparts who only slightly know SQL.
• Less reliance on a single SQL guru within the group, especially as the skills can be absorbed within a few days of rigorous training.
• Ability to support hte complex advanced SQL of other SQL programmers.
• Full ROI to the company within the 1st month after knowledge is absorbed.