DB2 SQL Training

Customized Onsite Training

3
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors
Overview

In this training class, students will learn the DB2 SQL starting at the most basic level and going to the most advanced level with many examples.

Goals
  1. Gain a deeper knowledge and understanding of the DB2 SQL and how to write it.
Outline
  1. The Basics of SQL
    1. Introduction
    2. Finding Your Current Schema
    3. Setting Your Default SCHEMA
    4. SELECT * (All Columns) in a Table
    5. SELECT Specific Columns in a Table
    6. Commas in the Front or Back?
    7. Place your Commas in front for better Debugging Capabilities
    8. Sort the Data with the ORDER BY Keyword
    9. ORDER BY Defaults to Ascending
    10. Use the Name or the Number in your ORDER BY Statement
    11. Two Examples of ORDER BY using Different Techniques
    12. Changing the ORDER BY to Descending Order
    13. NULL Values sort First in Ascending Mode (Default)
    14. NULL Values sort First in Descending Mode (DESC)
    15. Major Sort vs. Minor Sorts
    16. Multiple Sort Keys using Names vs. Numbers
    17. Sorts are Alphabetical, NOT Logical
    18. Using A CASE Statement to Sort Logically
    19. How to ALIAS a Column Name
    20. A Missing Comma can by Mistake become an Alias
    21. Comments using Double Dashes are Single Line Comments
    22. Comments for Multi-Lines
    23. Comments for Multi-Lines as Double Dashes per Line
    24. Formatting Number Examples
    25. Formatting Date Example
  2. The WHERE Clause
    1. The WHERE Clause limits Returning Rows
    2. Double Quoted Aliases are for Reserved Words and Spaces
    3. Character Data needs Single Quotes in the WHERE Clause
    4. Character Data needs Single Quotes, but Numbers Don't
    5. Comparisons against a Null Value
    6. NULL means UNKNOWN DATA so Equal (=) won't Work
    7. Use IS NULL or IS NOT NULL when dealing with NULLs
    8. NULL is UNKNOWN DATA so NOT Equal won't Work
    9. Use IS NULL or IS NOT NULL when dealing with NULLs
    10. Using Greater Than or Equal To (>=)
    11. AND in the WHERE Clause
    12. Troubleshooting AND
    13. OR in the WHERE Clause
    14. Troubleshooting Or
    15. Troubleshooting Character Data
    16. Using Different Columns in an AND Statement
    17. Quiz - How many rows will return?
    18. Answer to Quiz - How many rows will return?
    19. What is the Order of Precedence?
    20. Using Parentheses to change the Order of Precedence
    21. Using an IN List in place of OR
    22. The IN List is an Excellent Technique
    23. IN List vs. OR brings the same Results
    24. The IN List Can Use Character Data
    25. Using a NOT IN List
    26. Null Values in a NOT IN List Bring Back No Rows
    27. A Technique for Handling Nulls with a NOT IN List
    28. BETWEEN is Inclusive
    29. NOT BETWEEN is Also Inclusive
    30. LIKE uses Wildcards Percent ‘%' and Underscore ‘_'
    31. LIKE command Underscore is Wildcard for one Character
    32. LIKE Command Works Differently on Char Vs Varchar
    33. LIKE Command on Character Data Auto Trims
    34. Quiz - What Data is Left Justified and what is Right?
    35. Numbers are Right Justified and Character Data is Left
    36. Answer - What Data is Left Justified and what is Right?
    37. An Example of Data with Left and Right Justification
    38. A Visual of CHARACTER Data vs. VARCHAR Data
    39. Use the TRIM command to remove spaces on CHAR Data
    40. Escape Character in the LIKE Command changes Wildcards
    41. Escape Characters Turn off Wildcards in the LIKE Command
    42. Quiz - Turn off that Wildcard
    43. ANSWER - To Find that Wildcard
    44. The Distinct Command
    45. Distinct vs. GROUP BY
    46. Quiz - How many rows come back from the Distinct?
    47. Answer - How many rows come back from the Distinct?
    48. The FETCH Clause
    49. The FETCH Clause with an ORDER BY Clause
  3. Aggregation
    1. Quiz - You calculate the Answer Set in your own Mind
    2. Answer - You calculate the Answer Set in your own Mind
    3. Quiz - You calculate the Answer Set in your own Mind
    4. Answer - You calculate the Answer Set in your own Mind
    5. The 3 Rules of Aggregation
    6. There are Five Aggregates
    7. Quiz - How many rows come back?
    8. Answer - How many rows come back?
    9. Troubleshooting Aggregates
    10. GROUP BY delivers one row per Group
    11. GROUP BY Dept_No Works GROUP BY 1 Fails
    12. Limiting Rows and Improving Performance with WHERE
    13. WHERE Clause in Aggregation limits unneeded Calculations
    14. Keyword HAVING tests Aggregates after they are totaled
    15. Keyword HAVING is like an Extra WHERE Clause for Totals
    16. Keyword HAVING tests Aggregates after they are totaled
    17. Getting the Average Values per Column
    18. Average Values per Column for all Columns in a Table
    19. GROUP BY GROUPING SETS Command
    20. GROUP BY Grouping Sets
    21. GROUP BY ROLLUP Command
    22. GROUP BY Rollup Result Set
    23. GROUP BY CUBE Command
    24. GROUP BY CUBE Result Set
    25. Quiz - GROUP BY GROUPING SETS Challenge
    26. Answer To Quiz - GROUP BY GROUPING SETS Challenge
  4. Join Functions
    1. A Two-Table Join Using Traditional Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. You Can Fully Qualify All Columns
    4. A two-table join using ANSI Syntax
    5. Both Queries have the same Results and Performance
    6. Quiz - Can You Finish the Join Syntax?
    7. Answer to Quiz - Can You Finish the Join Syntax?
    8. Quiz - Can You Find the Error?
    9. Answer to Quiz - Can You Find the Error?
    10. Super Quiz - Can You Find the Difficult Error?
    11. Answer to Super Quiz - Can You Find the Difficult Error?
    12. Quiz - Which rows from both tables won't return?
    13. Answer to Quiz - Which rows from both tables Won't Return?
    14. LEFT OUTER JOIN
    15. LEFT OUTER JOIN Results
    16. RIGHT OUTER JOIN
    17. RIGHT OUTER JOIN Example and Results
    18. FULL OUTER JOIN
    19. FULL OUTER JOIN Results
    20. Which Tables are the Left and which Tables are Right?
    21. Answer - Which Tables are the Left and which are the Right?
    22. INNER JOIN with Additional AND Clause
    23. ANSI INNER JOIN with Additional AND Clause
    24. ANSI INNER JOIN with Additional WHERE Clause
    25. OUTER JOIN with Additional WHERE Clause
    26. OUTER JOIN with Additional AND Clause
    27. OUTER JOIN with Additional AND Clause Results
    28. Quiz - Why is this considered an INNER JOIN?
    29. Evaluation Order for Outer Queries
    30. The DREADED Product Join
    31. The DREADED Product Join Results
    32. The Horrifying Cartesian product join
    33. The ANSI Cartesian Join will ERROR
    34. Quiz - Do these Joins Return the Same Answer Set?
    35. Answer - Do these Joins Return the Same Answer Set?
    36. The CROSS JOIN
    37. The CROSS JOIN Answer Set
    38. The Self Join
    39. The Self Join with ANSI Syntax
    40. Quiz - Will both queries bring back the same Answer Set?
    41. Answer - Will both queries bring back the same Answer Set?
    42. Quiz - Will both queries bring back the same Answer Set?
    43. Answer - Will both queries bring back the same Answer Set?
    44. How would you join these two tables?
    45. An Associative Table is a Bridge that Joins Two Tables
    46. Quiz - Can you write the 3-Table Join?
    47. Answer to quiz - Can you write the 3-Table Join?
    48. Quiz - Can you write the 3-Table Join to ANSI Syntax?
    49. Answer - Can you write the 3-Table Join to ANSI Syntax?
    50. Quiz - Can you Place the ON Clauses at the End?
    51. Answer - Can you Place the ON Clauses at the End?
    52. The 5-Table Join - Logical Insurance Model
    53. Quiz - Write a Five Table Join Using ANSI Syntax
    54. Answer - Write a Five Table Join Using ANSI Syntax
    55. Quiz - Write a Five Table Join Using Non-ANSI Syntax
    56. Answer - Write a Five Table Join Using Non-ANSI Syntax
    57. Quiz -Re-Write this putting the ON clauses at the END
    58. Answer -Re-Write this putting the ON clauses at the END
    59. The Nexus Query Chameleon Writes the SQL for Users
  5. Using Nexus for DB2
    1. Nexus is Available on the Cloud
    2. Nexus Queries Every Major System
    3. How to Use Nexus
    4. Why is Nexus Special? Visualization and Automatic SQL
    5. Why is Nexus Special? Cross-System Joins
    6. Why is Nexus Special? The Amazing Hub System
    7. Why is Nexus Special? Save Answer Sets as Tables
    8. Why is Nexus Special? Automated Data Movement
    9. Why is Nexus Special? Nexus makes the Servers Talk Directly
    10. What Makes Nexus Special? The Garden of Analysis
    11. The Garden of Analysis Grouping Sets Tab
    12. The Garden of Analysis - Grouping Sets Answer Sets
    13. The Garden of Analysis - Join Tab (1 of 4)
    14. The Garden of Analysis - Join Tab (2 of 4)
    15. The Garden of Analysis - Join Tab (3 of 4)
    16. The Garden of Analysis - Join Tab (4 of 4)
    17. The Garden of Analysis - Charts/Graphs Tab (1 of 4)
    18. The Garden of Analysis - Charts/Graphs Tab (2 of 4)
    19. The Garden of Analysis - Charts/Graphs Tab (3 of 4)
    20. The Garden of Analysis - Charts/Graphs Tab (4 of 4)
    21. The Garden of Analysis - Dynamic Charts Tab (1 of 4)
    22. The Garden of Analysis - Dynamic Charts Tab (2 of 4)
    23. The Garden of Analysis - Dynamic Charts Tab (3 of 4)
    24. The Garden of Analysis - Dynamic Charts Tab (4 of 4)
    25. The Garden of Analysis - Dashboard Tab (1 of 5)
    26. The Garden of Analysis - Dynamic Charts Tab (2 of 5)
    27. The Garden of Analysis - Dynamic Charts Tab (3 of 5)
    28. The Garden of Analysis - Dynamic Charts Tab (4 of 5)
    29. The Garden of Analysis - Dynamic Charts Tab (5 of 5)
    30. Getting to the Super Join Builder
    31. The Super Join Builder is the First Entry in the Menu
    32. The Super Join Builder Shows Tables Visually
    33. Using the Add Join Button
    34. What to Do When No Tables are Joinable?
    35. Drag a Joinable Object into the Super Join Builder
    36. You will see the Add Custom Join Window
    37. Defining the Join Columns
    38. Your Tables Will Appear Together
    39. Select the Columns You Want on the Report
    40. Check out the SQL Tab to See the SQL that has been built
    41. SQL Tab
    42. Hit Execute to get the Report inside the Super Join Builder
    43. The Report is delivered inside the Super Join Builder
    44. Let's Join Two Tables Again (1 of 6)
    45. Let's Join Two Tables Again (2 of 6)
    46. Let's Join Two Tables Again (3 of 6)
    47. Let's Join Two Tables Again (4 of 6)
    48. Let's Join Two Tables Again (5 of 6)
    49. Let's Join Two Tables Again (6 of 6)
    50. The Tabs of the Super Join Builder Philosophy - One Query
    51. The Tabs of the Super Join Builder - Objects Tab
    52. The Tabs of the Super Join Builder - Columns Tab)
    53. The Tabs of the Super Join Builder - Sorting Tab
    54. The Tabs of the Super Join Builder - Joins Tab
    55. The Tabs of the Super Join Builder - SQL Tab
    56. The Tabs of the Super Join Builder - Metadata Tab
    57. The Tabs of the Super Join Builder - Analytics Tab
    58. The Tabs of the SJB - Analytics Tab - OLAP Screen
    59. Getting a Simple CSUM in the Analytics Tab - OLAP
    60. Getting a Simple CSUM - The SQL Automatically Generated
    61. The Answer Set of the CSUM
    62. Getting all of the OLAP functions in the Analytics Tab
    63. A Five Table Join Using the Menu
    64. The First Table is placed in the Super Join Builder
    65. Using the Add Join Cascading Menu
    66. All Five Tables Are In the Super Join Builder
    67. A Five Table Join Two Steps (Cube)
    68. Choose Cube with Columns from the Left Top of the Table
    69. All Tables are Cubed (Joined Together Instantly)
    70. Choose Cube and then Choose Your Columns
    71. Create Cube - Tables Are Joined Without Columns Selected
    72. Create Cube - Select the Columns You Want on the Report
    73. How to join DB2, Oracle and SQL Server Tables
    74. The DB2 Table is now in the Super Join Builder
    75. Drag the Joining Oracle Table to the Super Join Builder
    76. Defining the Join Columns
    77. Choose the Columns You Want on Your Report
    78. Let's Add a SQL Server Table to our DB2 and Oracle Join
    79. Defining the Join Columns
    80. All Three Tables are now in the Super Join Builder
    81. Change the Hub and Run the Join on Oracle
    82. Change the Hub and Run the Join on SQL Server
    83. Simply Amazing - Change the Hub to the Garden of Analysis
    84. Have the Answer Set Saved Automatically to Any System
    85. Saving the Answer Set to an Oracle or SQL Server System
    86. Saving the Answer Set to a DB2 System
    87. Saving the Answer Set to a Teradata System
  6. Date Functions
    1. Getting the System Date
    2. Extracting From a Timestamp
    3. The EXTRACT Command
    4. Using the EXTRACT Command to Extract Month, Day, Year
    5. Extracting From a Date Column
    6. Extracting the Date and Time from the Timestamp
    7. Formatting Dates Example
    8. Formatting Date Standards
    9. Adding and Subtracting Days from a Date
    10. Adding Years, Months, Days, Hours and Seconds
    11. Using the Add_Months Command
    12. Adding Years to a Date
    13. Add Five Years to a Date
    14. Converting Character Data to a Date or Time
    15. Timestamp DAYOFWEEK, DAYNAME and MONTHNAME
    16. Finding Orders That Happened on a Friday
    17. NEXT_DAY Command Finds a Future Day of the Week
    18. Finding the Last Day of a Month
    19. Finding the Last Day of the Previous Month
    20. Getting the First Day of the Month
    21. Finding the Number of Days between Two Dates
    22. Resetting the Microseconds Back to Zero
    23. Turning Date and Time into Characters
    24. Converting Character Data to a Timestamp
    25. Finding Differences between Timestamps
    26. Differences between Timestamps Fractions of a Second
    27. Find Differences between Timestamp Seconds and Minutes
    28. Find Differences between Timestamp Hours and Days
    29. Find Differences between Timestamp Weeks and Months
    30. Find Differences between Timestamp Quarters and Years
    31. Formatting Dates
    32. Formatting Dates Example
    33. Formatting Dates Example
    34. Formatting Dates Example
    35. Formatting Timestamp Example
    36. Formatting Timestamp Example
  7. OLAP Functions
    1. The Row_Number Command
    2. Quiz - How did the Row_Number Reset?
    3. Quiz - How did the Row_Number Reset?
    4. Using a Derived Table and Row_Number
    5. Ordered Analytics OVER
    6. RANK and DENSE RANK
    7. RANK Defaults to Ascending Order
    8. Getting RANK to Sort in DESC Order
    9. RANK OVER and PARTITION BY
    10. Finding Gaps between Dates
    11. CSUM - Rows Unbounded Preceding Explained
    12. CSUM - Making Sense of the Data
    13. CSUM - Making Even More Sense of the Data
    14. CSUM - The Major and Minor Sort Key(s)
    15. The ANSI CSUM - Getting a Sequential Number
    16. Reset with a PARTITION BY Statement
    17. PARTITION BY only Resets a Single OLAP not ALL of them
    18. CURRENT ROW AND UNBOUNDED FOLLOWING
    19. Different Windowing Options
    20. Moving Sum has a Moving Window
    21. How ANSI Moving SUM Handles the Sort
    22. Quiz - How is that Total Calculated?
    23. Answer to Quiz - How is that Total Calculated?
    24. Moving SUM every 3-rows Vs a Continuous Average
    25. PARTITION BY Resets an ANSI OLAP
    26. The Moving Window is Current Row and Preceding
    27. Moving Average
    28. Moving Average Using a CAST Statement
    29. Moving Average every 3-rows Vs a Continuous Average
    30. PARTITION BY Resets an ANSI OLAP
    31. Moving Difference
    32. Moving Difference using ANSI Syntax with Partition By
    33. COUNT OVER for a Sequential Number
    34. COUNT OVER without Rows Unbounded Preceding
    35. Quiz - What caused the COUNT OVER to Reset?
    36. Answer to Quiz - What caused the COUNT OVER to Reset?
    37. The MAX OVER Command
    38. MAX OVER with PARTITION BY Reset
    39. MAX OVER without Rows Unbounded Preceding
    40. The MIN OVER Command
    41. MIN OVER without Rows Unbounded Preceding
    42. MIN OVER Using PARTITION BY to Reset
    43. Finding a Value of a Column in the Next Row with MIN
    44. The CSUM for Each Product_Id and the Next Start Date
    45. Quiz - Fill in the Blank
    46. Answer - Fill in the Blank
    47. Using FIRST_VALUE
    48. FIRST_VALUE
    49. FIRST_VALUE after Sorting by the Highest Value
    50. FIRST_VALUE with Partitioning
    51. FIRST_VALUE Combined with Row_Number
    52. FIRST_VALUE and Row_Number with Different Sort
    53. Using LAST_VALUE
    54. LAST_VALUE
    55. Using LAG and LEAD
    56. LEAD
    57. LEAD
    58. LEAD With Partitioning
    59. LEAD to Find the First Occurrence
    60. Using LEAD
    61. Using LEAD with an Offset of 2
    62. Using LAG
    63. Using LAG with an Offset of 2
    64. LAG
    65. LAG with Partitioning
    66. SUM (SUM (n))
  8. Temporary Tables
    1. There are two types of Temporary Tables
    2. CREATING A Derived Table
    3. Creating Multiple Derived Tables in the WITH Command
    4. Creating Multiple Derived Tables in the WITH Command
    5. The Same Derived Query shown Three Different Ways
    6. Most Derived Tables Are Used To Join To Other Tables
    7. The Three Components of a Derived Table
    8. Visualize This Derived Table
    9. Our Join Example with A Different Column Aliasing Style
    10. Column Aliasing Can Default For Normal Columns
    11. Our Join Example With the WITH Syntax
    12. Quiz - Answer the Questions
    13. Answer to Quiz - Answer the Questions
    14. Clever Tricks on Aliasing Columns in a Derived Table
    15. An Example of Two Derived Tables in a Single Query
    16. Example of Two Derived Tables in a Single WITH Statement
    17. WITH RECURSIVE Derived Table Hierarchy
    18. WITH RECURSIVE Derived Table Query
    19. WITH RECURSIVE Derived Table Definition
    20. WITH RECURSIVE Derived Table Seeding
    21. WITH RECURSIVE Derived Table Looping
    22. WITH RECURSIVE Derived Table Looping in Slow Motion
    23. WITH RECURSIVE Derived Table Looping Continued
    24. WITH RECURSIVE Derived Table Looping Continued
    25. WITH RECURSIVE Derived Table Ends the Looping
    26. WITH RECURSIVE Derived Table Definition
    27. WITH RECURSIVE Final Answer Set
    28. Creating and Populating a Global Temporary Table
    29. Global Temporary Table Definitions Persist
    30. ON COMMIT DELETE ROWS Example
    31. Creating and Populating a Global Temporary Table
    32. Creating a Global Temporary Table Using a CTAS
    33. Creating a Global Temporary Table Using a CTAS Join
    34. A Global Temp Table That Populates Some of the Rows
    35. A Temporary Table with Some of the Columns
  9. Sub-query Functions
    1. An IN List is much like a Subquery
    2. An IN List Never has Duplicates - Just like a Subquery
    3. The Subquery
    4. The Three Steps of How a Basic Subquery Works
    5. These are Equivalent Queries
    6. The Final Answer Set from the Subquery
    7. Quiz- Answer the Difficult Question
    8. Answer to Quiz- Answer the Difficult Question
    9. Should you use a Subquery or a Join?
    10. Quiz- Write the Subquery
    11. Answer to Quiz- Write the Subquery
    12. Quiz- Write the More Difficult Subquery
    13. Answer to Quiz- Write the More Difficult Subquery
    14. Quiz - Write the Extreme Subquery
    15. Answer to Quiz- Write the Extreme Subquery
    16. Quiz- Write the Subquery with an Aggregate
    17. Answer to Quiz- Write the Subquery with an Aggregate
    18. Quiz- Write the Correlated Subquery
    19. Answer to Quiz- Write the Correlated Subquery
    20. The Basics of a Correlated Subquery
    21. The Top Query always runs first in a Correlated Subquery
    22. Correlated Subquery Example vs. a Join with a Derived Table
    23. Quiz- A Second Chance to Write a Correlated Subquery
    24. Answer - A Second Chance to Write a Correlated Subquery
    25. Quiz- A Third Chance to Write a Correlated Subquery
    26. Answer - A Third Chance to Write a Correlated Subquery
    27. Quiz- Last Chance to Write a Correlated Subquery
    28. Answer - Last Chance to Write a Correlated Subquery
    29. Quiz - Write the Extreme Correlated Subquery
    30. Answer To Quiz - Write the Extreme Correlated Subquery
    31. Quiz- Write the NOT Subquery
    32. Answer to Quiz- Write the NOT Subquery
    33. Quiz- Write the Subquery using a WHERE Clause
    34. Answer - Write the Subquery using a WHERE Clause
    35. Quiz- Write the Subquery with Two Parameters
    36. Answer to Quiz- Write the Subquery with Two Parameters
    37. How the Double Parameter Subquery Works
    38. More on how the Double Parameter Subquery Works
    39. Quiz - Write the Triple Subquery
    40. Answer to Quiz - Write the Triple Subquery
    41. Quiz - How many rows return on a NOT IN with a NULL?
    42. Answer - How many rows return on a NOT IN with a NULL?
    43. How to handle a NOT IN with potential NULL Values
    44. IN is equivalent to =ANY
    45. Using a Correlated Exists
    46. How a Correlated Exists matches up
    47. The Correlated NOT Exists
  10. Strings
    1. The LENGTH Command Counts Characters
    2. The LENGTH Command - Spaces can Count too
    3. The LENGTH Command and Character Data
    4. The LENGTH Needs a TRIM
    5. The TRIM Command trims both Leading and Trailing Spaces
    6. A Visual of the TRIM Command Using Concatenation
    7. Trim and Trailing is Case Sensitive
    8. How to TRIM Trailing Letters
    9. The SUBSTRING Command
    10. How SUBSTRING Works with NO ENDING POSITION
    11. An Example using SUBSTRING, TRIM and CHAR Together
    12. Concatenation
    13. Concatenation and SUBSTRING
    14. Four Concatenations Together
    15. UPPER and LOWER Commands
    16. LPAD and RPAD
    17. SOUNDEX
  11. Interrogating the Data
    1. Using the LOWER Command
    2. Using the UPPER Command
    3. Non-Letters are Unaffected by UPPER and LOWER
    4. Quiz - Fill in the Answers for the NULLIF Command
    5. The COALESCE Command
    6. The COALESCE Answer Set
    7. The COALESCE Command - Fill In the Answers
    8. The COALESCE Answer Set
    9. The COALESCE Command - Fill In the Answers
    10. The COALESCE Answer Set
    11. COALESCE is Equivalent to This CASE Statement
    12. The Basics of CAST (Convert and Store)
    13. Some Great CAST (Convert and Store) Examples
    14. A Rounding Example
    15. Quiz - The Basics of the CASE Statements
    16. Answer to Quiz - The Basics of the CASE Statements
    17. Using an ELSE in the Case Statement
    18. Using an ELSE as a Safety Net
    19. Rules for a Valued Case Statement
    20. Rules for a Searched Case Statement
    21. Valued Case Vs. A Searched Case
    22. Quiz - Valued Case Statement
    23. Answer - Valued Case Statement
    24. Quiz - Searched Case Statement
    25. Answer - Searched Case Statement
    26. The CASE Challenge
    27. The CASE Challenge Answer
    28. Combining Searched Case and Valued Case
    29. A Trick for getting a Horizontal Case
    30. Nested Case
    31. Put a CASE in the ORDER BY
  12. View Functions
    1. The Fundamentals of Views
    2. Creating a Simple View to Restrict Sensitive Columns
    3. You SELECT From a View
    4. Creating a Simple View to Restrict Rows
    5. A View Provides Security for Columns and Rows
    6. Basic Rules for Views
    7. How to Modify a View
    8. An Exception to the ORDER BY Rule inside a View
    9. Views Are Sometimes CREATED for Formatting
    10. Creating a View to Join Tables Together
    11. How to Alias Columns in a View CREATE
    12. The Standard Way Most Aliasing is done
    13. What Happens When Both Aliasing Options Are Present
    14. Resolving Aliasing Problems in a View CREATE
    15. Answer to Resolving Aliasing Problems in a View CREATE
    16. Creating a View with a Local Check
    17. Aggregates on View Aggregates
    18. Altering a Table after a View Has Been Created
    19. A View that Errors after an ALTER
  13. Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION ALL Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Minus Explained Logically
    11. Minus Explained Logically
    12. An Equal Amount of Columns in both SELECT List
    13. Columns in the SELECT list should be from the same Domain
    14. The Top Query handles all Aliases
    15. The Bottom Query does the ORDER BY
    16. Great Trick:Place your Set Operator in a Derived Table
    17. UNION Vs UNION ALL
    18. A Great Example of how MINUS works
    19. USING Multiple SET Operators in a Single Request
    20. Changing the Order of Precedence with Parentheses
    21. Using UNION ALL for speed in Merging Data Sets
  14. Table Create and Data Types
    1. The Basics of Creating a Table
    2. Creating a Table with NOT NULL Constraints
    3. Creating a Table with a UNIQUE Constraint
    4. Creating a Unique Index
    5. Creating a Clustered Index
    6. Creating a Partitioned Table for a Range on a Date Column
    7. Creating a Partitioned Table for a Number
    8. Declaring a Global Temporary Table
    9. Declaring a Global Temporary Table that Persists Using Like
    10. Creating a Table with an XML Data Type
    11. Creating a Table with a CHECK Constraint
    12. Creating a Table with Default Values
    13. Creating a Table with Multiple Constraints
    14. Defining Primary Keys
    15. Defining a Primary Key after the Table Has Been Created
    16. Defining a Foreign Key after the Table Has Been Created
    17. Creating a Table with an Identity Column that is Unique
    18. Creating a Table with an Identity Column that is Non-Unique
    19. Creating a Sequence
    20. Altering a Table to Add a Column
    21. Altering a Table to Drop a Column
    22. Renaming a Table
    23. Dropping a Table
    24. Creating a Table Using a CTAS or a LIKE
    25. Creating a Table Using a CTAS Join
    26. Data Types
    27. Data Types Continued
    28. Data Types Continued
    29. Data Types Continued
    30. Data Types Continued
  15. Data Manipulation Language (DML)
    1. INSERT Syntax # 1
    2. INSERT example with Syntax 1
    3. INSERT Syntax # 2
    4. INSERT example with Syntax 2
    5. INSERT/SELECT Command
    6. INSERT/SELECT example using All Columns (*)
    7. INSERT/SELECT example with Less Columns
    8. Two UPDATE Examples
    9. Subquery UPDATE Command Syntax
    10. Example of Subquery UPDATE Command
    11. The DELETE Command Basic Syntax
    12. Example of Subquery DELETE Command
    13. Example of Subquery DELETE That Gets Rid of Null Values
  16. Statistical Aggregate Functions
    1. Numeric Manipulation Functions
    2. The Stats Table
    3. The VARIANCE Function
    4. A VARIANCE Example
    5. The CORR Function
    6. A CORR Example
    7. Another CORR Example so you can compare
    8. The REGR_INTERCEPT Function
    9. A REGR_INTERCEPT Example
    10. Another REGR_INTERCEPT Example so you can compare
    11. The REGR_SLOPE Function
    12. A REGR_SLOPE Example
    13. Another REGR_SLOPE Example so you can compare
    14. The REGR_AVGX Function
    15. A REGR_AVGX Example
    16. Another REGR_AVGX Example so you can compare
    17. The REGR_AVGY Function
    18. A REGR_AVGY Example
    19. Another REGR_AVGY Example so you can compare
    20. The REGR_COUNT Function
    21. A REGR_COUNT Example
    22. The REGR_R2 Function
    23. A REGR_R2 Example
    24. The REGR_SXX Function
    25. A REGR_SXX Example
    26. The REGR_SXY Function
    27. A REGR_SXY Example
    28. The REGR_SYY Function
    29. A REGR_SYY Example
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

74,839

Students who have taken Live Online Training

15,233

Organizations who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.41

Students rated our trainers 9.41 out of 10 based on 5,189 reviews

Contact Us or call 1-877-932-8228