The New Stuff in SQL You Don't Know About

The New Stuff in SQL You Don't Know About

By Joe Celko
Date: Friday, June 26, 2009 09:00 AM
Duration: 480 minutes
Target audience: Any
Language:
Tags: free_book

---

A look at features in Standard SQL that have been added after the core SQL-92 Standard

Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards. He has written over 800 columns in the computer trade and academic press, mostly dealing with data and databases. He is author of eight books on SQL for Morgan-Kaufmann.

1.0. Advanced DDL
1.1. Three Valued Logic, DDL and DML
1.2. DEFAULT Values
1.3. CHECK() Constraints with fancy search conditions
1.4. Primary Keys
1.5. UNIQUE Constraints and NULLs
1.6. FOREIGN KEY .. REFERENCES
1.7. Declarative Referential Actions
1.8. Avoiding Triggers
1.9. Avoiding Proprietary Data Types

2.0. Advanced VIEWs
2.1. WITH CHECK OPTION
2.2. CTEs – regular and recursive

3.0. Infixed JOINs
3.1. LEFT and RIGHT OUTER Joins
3.2. FULL OUTER JOIN
3.4. CROSS JOIN
3.5. Other Exotic Joins

4.0. New Grouping Operators
4.1. Basic GROUP BY Clause
4.2. GROUPING SET ()
4.3. ROLLUP ()
4.4. CUBE ()

5.0. New Set Operators
5.1. UNION and UNION ALL – old stuff
5.2. INTERSECT and INTERSECT ALL
5.3. EXCEPT and EXCEPT ALL
5.4. Relational Division - Exact, With Remainder and Todd's Operator

6.0. MERGE Statement

7.0. Aggregates with the Window Clause
7.1. ORDER BY sub-clause
7.2. PARTITION BY sub-clause
7.3. RANGE sub-clause

8.0. Trees & Hierarchies in SQL
8.1. Nested Sets Model
8.2. Tricks and Tools for the Nested Sets Model

9.0. SQL Temporal Data Types
9.1. Why Time Is A Hard Problem
9.2. Time Is Made Of Durations and Not Points
9.3. Calendar Tables

10.0. Stored Procedures
10.1. Structured Programming Basics Still Apply
10.2. Coupling
10.3. Cohesion
10.4. Handling a Variable Number of Parameters
10.5. Transactions, Locking and Levels

This course includes a copy of SQL FOR SMARTIES.

Students to bring notebook computer with your favorite SQL on it.

---

Attended by: Dan Dascalescu (‎dandv‎), Todd Olson, Terry Bates, Duane Brown (‎duaneb‎), Hari Ravella, Douglas Sims, Maximilian Mohun, Paul Makepeace (‎paulm‎), Vick Khera,