What is “Advanced” SQL?

by

Basics

  1. SELECTing columns from a table
  2. Aggregates Part 1: COUNT, SUM, MAX/MIN
  3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

Intermediate

  1. JOINs, ANSI-89 and ANSI-92 syntax
  2. UNION vs UNION ALL
  3. NULL handling: COALESCE & Native NULL handling
  4. Subqueries: IN, EXISTS, and inline views
  5. Subqueries: Correlated
  6. WITH syntax: Subquery Factoring/CTE
  7. Views

Advanced Topics

  • Functions, Stored Procedures, Packages
  • Pivoting data: CASE & PIVOT syntax
  • Hierarchical Queries
  • Cursors: Implicit and Explicit
  • Triggers
  • Dynamic SQL
  • Materialized Views
  • Query Optimization: Indexes
  • Query Optimization: Explain Plans
  • Query Optimization: Profiling
  • Data Modelling: Normal Forms, 1 through 3
  • Data Modelling: Primary & Foreign Keys
  • Data Modelling: Table Constraints
  • Data Modelling: Link/Corrollary Tables
  • Full Text Searching
  • XML
  • Isolation Levels
  • Entity Relationship Diagrams (ERDs), Logical and Physical
  • Transactions: COMMIT, ROLLBACK, Error Handling

Leave a Comment