Tera-Tom Genius Series - DB2 SQL

Author Leslie Nolander , Tom Coffing

Release Date: 2015/12/01

ISBN: 9781940540368

Topic:

Data

25
Chapters

0-1
Hours read

0k
Total Words

Start Reading Now
Add to Wishlist
View table of contents

Book Description

One of the most popular databases in the world is IBM’s DB2. This book has over 600 pages of SQL examples and explanations. This book is a must have for anyone who develops or writes SQL for an IBM DB2 system. Readers will also understand how to create tables, views and indexes. The Authors Tera-Tom Coffing, who has written over 75 successful books on Data Warehousing and Leslie Nolander, Chief Operations Officer (COO) of Coffing Data Warehousing bring a combined 40 years of experience of data warehouse knowledge to create this must have book.

Cover
The Tera-Tom Video Series
The Tera-Tom Genius Series
Tera-Tom- Author of over 50 Books
The Best Query Tool Works on all Systems
Trademarks and Copyrights
About Tom Coffing
About Leslie Nolander
Contents
Chapter 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
Chapter 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
Chapter 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
Chapter 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.
Chapter 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
Chapter 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
Chapter 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. PARTITION BY only Resets a Single OLAP not ALL of them
19. CURRENT ROW AND UNBOUNDED FOLLOWING
20. Different Windowing Options
21. Moving Sum has a Moving Window
22. How ANSI Moving SUM Handles the Sort
23. Quiz – How is that Total Calculated?
24. Answer to Quiz – How is that Total Calculated?
25. Moving SUM every 3-rows Vs a Continuous Average
26. PARTITION BY Resets an ANSI OLAP
27. The Moving Window is Current Row and Preceding
28. Moving Average
29. Moving Average Using a CAST Statement
30. Moving Average every 3-rows Vs a Continuous Average
31. PARTITION BY Resets an ANSI OLAP
32. Moving Difference
33. Moving Difference using ANSI Syntax with Partition By
34. COUNT OVER for a Sequential Number
35. COUNT OVER without Rows Unbounded Preceding
36. Quiz – What caused the COUNT OVER to Reset?
37. Answer to Quiz – What caused the COUNT OVER to Reset?
38. The MAX OVER Command
39. MAX OVER with PARTITION BY Reset
40. MAX OVER without Rows Unbounded Preceding
41. The MIN OVER Command
42. MIN OVER without Rows Unbounded Preceding
43. MIN OVER Using PARTITION BY to Reset
44. Finding a Value of a Column in the Next Row with MIN
45. The CSUM for Each Product_Id and the Next Start Date
46. Quiz – Fill in the Blank
47. Answer – Fill in the Blank
48. Using FIRST_VALUE
49. FIRST_VALUE
50. FIRST_VALUE after Sorting by the Highest Value
51. FIRST_VALUE with Partitioning
52. FIRST_VALUE Combined with Row_Number
53. FIRST_VALUE and Row_Number with Different Sort
54. Using LAST_VALUE
55. LAST_VALUE
56. Using LAG and LEAD
57. LEAD
58. LEAD
59. LEAD With Partitioning
60. LEAD to Find the First Occurrence
61. Using LEAD
62. Using LEAD with an Offset of 2
63. Using LAG
64. Using LAG with an Offset of 2
65. LAG
66. LAG with Partitioning
67. SUM (SUM(n))
Chapter 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
Chapter 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
Chapter 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
Chapter 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. Quiz – Fill in the Answers for the NULLIF Command
6. The COALESCE Command
7. The COALESCE Answer Set
8. The COALESCE Command – Fill In the Answers
9. The COALESCE Answer Set
10. The COALESCE Command – Fill In the Answers
11. The COALESCE Answer Set
12. COALESCE is Equivalent to This CASE Statement
13. The Basics of CAST (Convert and Store)
14. Some Great CAST (Convert and Store) Examples
15. A Rounding Example
16. Quiz - The Basics of the CASE Statements
17. Answer to Quiz - The Basics of the CASE Statements
18. Using an ELSE in the Case Statement
19. Using an ELSE as a Safety Net
20. Rules for a Valued Case Statement
21. Rules for a Searched Case Statement
22. Valued Case Vs. A Searched Case
23. Quiz - Valued Case Statement
24. Answer - Valued Case Statement
25. Quiz - Searched Case Statement
26. Answer - Searched Case Statement
27. The CASE Challenge
28. The CASE Challenge Answer
29. Combining Searched Case and Valued Case
30. A Trick for getting a Horizontal Case
31. Nested Case
32. Put a CASE in the ORDER BY
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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

Tera-Tom Genius Series - DB2 SQL

Book Description

Table of Contents