Joe Celko’s SQL Boot Camp Agenda

• The History of SQL and theANSI/ISOStandards
  • Dr. Codd and the Relational model (Bachman Versus Codd)
  • How Close are the Actual Products?
• Change your Mindset
  • Sets Versus Record Sequences
    • Tables are not Files – Interrelated in a Schema, has a Key
    • Rows are not Records – No Ordering in the Table, Self-contained
    • Columns are not Fields – Constraints, Defaults, REFERENCES
  • Declarative Versus Procedural Programming
    • Tell the Computer WHAT to Do and not HOW to Do it
    • Optimizer – “Trust the Force, Luke!”
      • Dynamically Considers more Than a Human Can – Stats, Predicates, Hints
      • Same Program Can Execute Differently Each Time; Compiled Language Don’t
      • Statements are Done “All at Once”
        • How SELECT Works
        • How INSERT Works
        • How UPDATE Works
        • How DELETE Works
  • Transactions, not Batches
    • Multiple Sessions at the Same Time
    • ACID Properties – Atomic, Consistent, Independent, Durable
  • Tiered Versus Monolithic Architectures
    • Do not Write COBOL and Tape Files in SQL
    • No Formatting in the Database – Report Servers and Front End Apps
• Q&A

Lunch 

• The Three Sub-Languages Overview
  • DDL (Data Definition Language) – What We Will Talk about This Session
  • DML (Data Manipulation Language)
  • DCL (Data Control Language)
• DDL
  • Bad DDL leads to Convoluted DML
    • Constantly Building Normalized Table From Scratch
    • Constantly Doing Extra Work
  • Data Types in SQL
    • DATE, TIME and TIMESTAMP
    • Numerics – DECIMAL(s, P) Versus NUMERIC(s, P)
    • Strings – VARCHAR(n) Vs CHAR(n), Unicode Versus ASCII
  • NOT NULL and DEFAULT Options
    • Prefer Encodings with Missing Values
    • DEFAULT is Under-used
  • CHECK () Constraints
    • You Can name Them for Error Messages
    • Saves Application Side Programming
    • Optimizer uses the Search Conditions
  • PRIMARY KEY and UNIQUE
    • Single or multiple Columns
    • PRIMARY KEY – One Per Table
      • Why IDENTITY, GUID, etc Cannot be a key
      • Implies UNIQUE NOT NULL
      • A bit of history as to Why it is in SQL
      • UNIQUE – multiple allowed
        • Can have NULL
        • Can Overlap each Other
  • REFERENCES Clause
    • Must Reference a PRIMARY KEY or UNIQUE in the Referenced Table(s)
    • The Referenced and Referencing Table Can be the Same.
    • Cycles and Trees in Referencing
      • Watch Out for Too much Depth (A → B, B → C) Vs (A → B, A → C)
      • Avoid Cycles (A → B, B → C, C→ A)or (A → B, B → C, A → C)
• Q&A

 

Demonstration: Kuznetsov’s temporal constraints: no gaps, no overlaps in event sequence.

Exercise: Create a table for scheduling teachers and Classes in Classrooms.

1. Teacher’s name
2. Class Room number (three with Floor as First Digit)
3. Class Period (1 to 6)
4. Course name (four letter abbreviation and Three Digit number)

Day 2 | Basic Queries, Table Operations and Manipulations

• Basic SELECT Statement Overview
  • SELECT Clause – happens all at Once
    • Expressions and Alias Names
    • Function Calls and Constants
    • Scalar Subquery (do not use!)
  • FROM Clause Options
    • Original CROSS JOIN Syntax
    • Infixed Join Syntax
      • CROSS JOIN
      • INNER JOIN
      • [LEFT| RIGHT | FULL] OUTER JOIN
      • Other Things like OUTER UNION; Do not use
      • on Clause – Lots of Options, but use the basics
      • Anything That Returns a Table Will Do
  • WHERE Clause
    • It is a Search Condition and not a Predicate
    • Three-valued logic – DML Versus DDL
    • All Kinds of Predicates
      • AND, or, NOT
      • BETWEEN, IN(), LIKE, IS [NOT] NULL,
      • All kinds of expressions
        • Math Operations & Functions
        • String Operations & Functions
        • Temporal Operations & Functions
        • Generics: CASE expression Family, CAST()
• Subqueries or Why the “S”in SQL Stood for Structured
  • Derived Table in FROM Clause
  • CTE (Common Table Expression)
    • Simple Version is a “local View”
    • Recursive CTE – Do not use
  • Table Constructors with VALUES()
    • Usually for Constant Tables
    • It Can Take expressions
• INSERT INTO
  • VALUES() Option
  • SELECT .. FROM.. Option
• DELETE FROM in a Set Oriented Language
• UPDATE in a Set Oriented Language

 Lunch

• Set Operations in SQL
  • Union Compatible Tables
  • Result Set has no Table or Column names – use AS
  • Multi-set (bag) Versions of True Set Operations
    • Removes Redundant Duplicates
    • ALL Preserves Duplicates
• SQL Table Operators
  • UNION [ALL]- the all is Cheaper
  • INTERSECT [ALL]
  • EXCEPT [ALL]
• Table Predicates
  • [NOT] EXISTS ()
  • <expr> <comp Op> [ALL|ANY|SOME] (<query>)
• Relational Division – Dr. Codd and History
  • Exact Division
  • Division with Remainder
• GROUP BY and its Options
  • Simple aggregate Functions
    • SUM(), AVG(), COUNT(), MIN(), MAX()
    • COUNT(*) Cardinality Versus COUNT()
    • DISTINCT Option
    • Tricks with CASE expressions
    • HAVING Clause
  • GROUPING SET() and its Shorthand
    • GROUPING() Function for generated NULLs
    • ROLLUP
    • CUBE

 

Demonstration: Romley’s Division

Exercise: Given a Data set, Produce a simple report with totals and averages at Different levels of aggregation. The Data set has to be built From several tables with messy rules.

 

Day Three | SQL Idioms and Advanced Programming Techniques

• Auxiliary Tables
  • Calendar
    • Basic Calendar
    • Report Periods
  • Series as a Replacement for loops
  • Basic Look-up Tables
• Nested Set Model for Hierarchies
  • Trees are not Hierarchies
  • Basic Operations on Trees
• Window Clauses
  • PARTITION BY Subclauses
  • ORDER BY Subclauses
  • [ROW | RANGE] Subclauses
• Simple Aggregates with Window Clause
• Ordinal Functions
  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()

Lunch

• Procedural Code in SQL
  • Vendor 4GL and SQL/PSM, PL/SQL
    • Heresy and needs to be Avoided
    • Watch out for CURSORs
• Sequences – newer Feature
• Triggers
  • Worst of Procedural Code
  • No Optimization Possible
  • Very Vendor Specific
• Stored Procedures
  • Software Engineering Rules Still Apply
    • Coupling
    • Cohesion
  • SQL Injection
  • Avoid Local Variables
  • Avoid Control Flow Programming
  • Long Parameter List
  • Recursion is Possible; avoid it
• Q&A and Wrap-up

Demonstration: Using ROW_NUMBER() for Finding Contiguous groupings (islands), gaps and other Subsets based on Sequencing

Exercise: Write a Procedure to insert a one-level sub-tree into a Nested Sets model tree table. One of the Parameters is the Parent node of the inserted, and the new tree is roots as the new right-most subordinate.