Chapter 12 - Interrogating the Data
"The difference between genius and stupidity is that genius has its limits"
- Albert Einstein
Quiz – What would the Answer be?
Can you guess what would return in the Answer Set?
Using the Student_Table above, and try and predict what the answer will be if this query was running on the system.
Answer to Quiz – What would the Answer be?
Error – Division by zero
You get an error when you DIVIDE by ZERO! Let’s turn the page and fix it!
The NULLIF Command
SELECT Class_Code
,Grade_Pt / ( NULLIF (Grade_pt,0) * 2 ) AS Math1
FROM Student_Table;
SELECT Class_Code
,Grade_Pt / ( NULLIF ( (Grade_pt) * 2, 0) ) AS Math1
FROM Student_Table;
If you have a calculation where a ZERO could kill the operation, and you don’t want that, you can use the NULLIF command to convert any zero value to a null value. Both queries above bring back the same result.
Quiz – Fill in the Answers for the NULLIF Command
What would the above Answer Set produce from your analysis?
Answer– Fill in the Answers for the NULLIF Command
Look at the answers above, and if it doesn’t make sense, go over it again until it does.
The COALESCE Command – Fill In the Answers
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null
The COALESCE Answer Set
SELECT
Last_Name
,Grade_Pt
,Class_Code
,COALESCE (Grade_Pt, Class_Code) as ValidStudents
FROM Student_Table
WHERE Last_Name IN ('Johnson', 'Larkins', 'Thomas')
ORDER BY 1 ;
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.
COALESCE is Equivalent to This CASE Statement
SELECT
Last_Name
,Grade_Pt
,Class_Code
,COALESCE (Grade_Pt, Class_Code) as ValidStudents
FROM Student_Table ;
SELECT
Last_Name
,Grade_Pt
,Class_Code
, CASE
WHEN Grade_Pt IS NOT NULL THEN Grade_Pt
WHEN Class_Code IS NOT NULL THEN Class_Code
ELSE NULL
END as ValidStudents
FROM Student_Table ;
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null. Above are two queries that return the exact same answer set. These examples are designed to give you a better idea of how Coalesce works
The Basics of CAST (Convert and Store)
CAST will convert a column or value’s data type
temporarily into another data type. Below is the syntax:
Data can be converted from one type to another by using the CAST function. As long as the data involved does not break any data rules (i.e. placing alphabetic or special characters into a numeric data type), the conversion works. The name of the CAST function comes from the Convert And STore operation that it performs.
Some Great CAST (Convert and Store) Examples
SELECT CAST('ABCDE' AS CHAR(1) ) AS Trunc
,CAST(128 AS CHAR(3) ) AS This_Is_OK
,CAST(127 AS INTEGER ) AS Bigger ;
Trunc |
This_Is_OK |
Bigger |
A |
128 |
127 |
The first CAST truncates the five characters (left to right) to form the single character ‘A’. In the second CAST, the integer 128 is converted to three characters and left justified in the output. The 127 was initially stored in a SMALLINT (5 digits - up to 32767) and then converted to an INTEGER. Hence, it uses 11 character positions for its display, ten numeric digits and a sign (positive assumed) and right justified as numeric.
Some Great CAST (Convert and Store) Examples
SELECT CAST('ABCDE' AS CHAR(1) ) AS Trunc
,CAST(128 AS CHAR(3) ) AS This_Is_OK
,CAST(127 AS INTEGER ) AS Bigger ;
Trunc |
This_Is_OK |
Bigger |
A |
128 |
127 |
The value of 121.53 was initially stored as a DECIMAL as 5 total digits with 2 of them to the right of the decimal point. Then, it is converted to a SMALLINT using CAST to remove the decimal positions. Therefore, it truncates data by stripping off the decimal portion. It does not round data using this data type. On the other hand, the CAST in the fifth column called Rounder is converted to a DECIMAL as 3 digits with no digits (3,0) to the right of the decimal, so it will round data values instead of truncating. Since .53 is greater than .5, it is rounded up to 122.
A Rounding Example
SELECT
CAST(.014 AS Decimal(3,2)) AS ".014"
,CAST(.016 AS Decimal(3,2)) AS ".016"
,CAST(.015 AS Decimal(3,2)) AS ".015"
,CAST(.0150 AS Decimal(3,2)) AS ".0150"
,CAST(.0250 AS Decimal(3,2)) AS ".0250"
,CAST(.0159 AS Decimal(3,2)) AS ".0159"
Above is an example of what you might expect to see in similar rounding examples.
Quiz - CAST Examples
SELECT Order_Number as OrdNo
,Customer_Number as CustNo
,Order_Date
,Order_Total
,CAST(Order_Total as integer) as Chopped
,CAST(Order_Total as Decimal(5,0)) as Rounded
FROM Order_Table
ORDER BY 1 ;
Fill in the Answer
Set below after
looking at the data
and the query.
The Column Chopped takes Order_Total (a Decimal (10,2) and CASTs it as an integer which chops off the decimals. Rounded CASTs Order_Total as a Decimal (5,0), which takes the decimals and rounds up if the decimal is .50 or above.
Answer to Quiz - CAST Examples
SELECT Order_Number as OrdNo
,Customer_Number as CustNo
,Order_Date
,Order_Total
,CAST(Order_Total as integer) as Chopped
,CAST(Order_Total as Decimal(5,0)) as Rounded
FROM Order_Table
ORDER BY 1 ;
The Column Chopped takes Order_Total (a Decimal (10,2) and CASTs it as an integer which chops off the decimals. Rounded CASTs Order_Total as a Decimal (5,0), which takes the decimals and rounds up if the decimal is .50 or above.
Quiz - The Basics of the CASE Statements
SELECT Course_Name
,CASE Credits
WHEN 1 THEN 'One Credit'
WHEN 2 THEN 'Two Credits'
WHEN 3 THEN 'Three Credits'
END AS CreditAlias
FROM Course_Table
WHERE Course_ID IN (220, 300) ;
This is a CASE STATEMENT which allows you to evaluate a column in your table, and from that, come up with a new answer for your report. Every CASE begins with a CASE, and they all must end with a corresponding END. What would the answer be?
Answer to Quiz - The Basics of the CASE Statements
SELECT Course_Name
,CASE Credits
WHEN 1 THEN 'One Credit'
WHEN 2 THEN 'Two Credits'
WHEN 3 THEN 'Three Credits'
END AS CreditAlias
FROM Course_Table
WHERE Course_ID IN (220, 300) ;
Course_Name |
|
CreditAlias |
Physical Database Design |
|
? |
SQL Features |
|
Two Credits |
The answer for the Physical Database Design class is null. This is because it fell through the case statement. The answer for the SQL Features course is Two Credits. Once a case statement gets a match, it leaves the statement and gets the next row.
Using an ELSE in the Case Statement
SELECT Course_Name
,CASE Credits
WHEN 1 THEN 'One Credit'
WHEN 2 THEN 'Two Credits'
WHEN 3 THEN 'Three Credits'
ELSE 'Four Credits'
END AS CreditAlias
FROM Course_Table
WHERE Course_ID IN (220, 300) ;
Course_Name |
|
CreditAlias |
Physical Database Design |
|
Four Credits |
SQL Features |
|
Two Credits |
Now that we have an ELSE in our case statement we are guaranteed that nothing will fall through.
Using an ELSE as a Safety Net
SELECT Course_Name
,CASE Credits
WHEN 1 THEN 'One Credit'
WHEN 2 THEN 'Two Credits'
WHEN 3 THEN 'Three Credits'
WHEN 4 THEN 'Four Credits'
ELSE 'Do not know'
END AS CreditAlias
FROM Course_Table ;
Now that we have an ELSE in our case statement we are guaranteed that nothing will fall through. An ELSE should be used in case you forgot a possibility and there was no match.
Rules For a Valued Case Statement
Rules for a Valued CASE:
1. You can only check for equality
2. You can only check the value of the
column Credits
There are two types of CASE statements. There is the Valued CASE and the Searched CASE. Above are the rules for the Valued CASE statement.
Rules for a Searched Case Statement
Rules for a Searched CASE:
1.You can check any way you want. You don't have to just check for equality.
2.You can check other column values also. You can mix and match different columns.
There are two types of CASE statements. There is the Valued CASE and the Searched CASE. Above are the rules for the Searched CASE statement.
Valued Case Vs. A Searched Case
The Searched CASE is better because it is so much more flexible. It is better unless you have a simple query.
Quiz - Valued Case Statement
Look at the CASE Statement and look at the Course_Table, and fill in the Answer Set.
Answer - Valued Case Statement
Above is the full answer set.
Quiz - Searched Case Statement
Look at the CASE Statement and look at the Course_Table, and fill in the Answer Set.
Answer - Searched Case Statement
Above is the full answer set.
Quiz - When NO ELSE is present in CASE Statement
Notice now that we have a 4 under the ‘Credit’ Column. However, in our CASE statement, we don’t have instructions on what to do if the number is 4. What will occur?
Answer - When NO ELSE is present in CASE Statement
Above is the full answer set. All four credit values fell through the CASE statement, thus they produced a Null value.
Quiz -When an Alias is NOT used in a CASE Statement
Notice now that we don’t have an ALIAS for the CASE Statement. What will the system place in there for the Column Title?
Answer -When an Alias is NOT used in a CASE Statement
Without an alias, the system will put (no column name) as the report header for the column.
Combining Searched Case and Valued Case
This Query above uses both a Valued Case and Searched Case. That’s ALLOWED!
A Trick for getting a Horizontal Case
SELECT
AVG(CASE Class_Code
WHEN 'FR' THEN Grade_pt
ELSE NULL END) AS Freshman_GPA
,AVG(CASE Class_Code
WHEN 'SO' THEN Grade_pt
ELSE NULL END) AS Sophomore_GPA
,AVG(CASE Class_Code
WHEN 'JR' THEN Grade_pt
ELSE NULL END) AS Junior_GPA
,AVG(CASE Class_Code
WHEN 'SR' THEN Grade_pt
ELSE NULL END) AS Senior_GPA
FROM Student_Table
WHERE Class_Code IS NOT NULL ;
Aggregates ignore Nulls so knowing this trick has allowed for Horizontal Reporting
Nested Case
SELECT Last_Name
,CASE Class_Code
WHEN 'JR' THEN 'Jr '
+(CASE WHEN Grade_pt < 2 THEN 'Failing'
WHEN Grade_pt < 3.5 THEN 'Passing'
ELSE 'Exceeding'
END)
ELSE 'Sr'
+(CASE WHEN Grade_pt < 2 THEN 'Failing'
WHEN Grade_pt < 3.5 THEN 'Passing'
ELSE 'Exceeding'
END)
END AS Status
FROM Student_Table WHERE Class_Code IN ('JR','SR')
ORDER BY Class_Code, Last_Name;
Last_Name |
Status |
Bond |
Jr Exceeding |
McRoberts |
Jr Failing |
Delaney |
Sr Passing |
Phillips |
Sr Passing |
A NESTED Case occurs when you have a Case Statement within another CASE Statement. Notice the + signs that provide Concatenation.
Put a CASE in the ORDER BY
I will bet you didn't know you could put a CASE statement in the Order By? You do now! There cannot be an ALIAS for the CASE statement because it is not a column in the report, but instead is a sort key.