Chapter 12 – Interrogating the Data
"The difference between genius and stupidity is that genius has its limits"
- Albert Einstein
Quiz – Fill in the Answers for the NULLIF Command
What would the above Answer Set produce from your analysis?
Quiz – Fill in the Answers for the NULLIF Command
Look at the answers above. Do they make sense? If not, go over it again until they do. The NULLIF command will compare two values in a list. If they are equal it will put a Null in the answer set, but if the two values are not equal, then it will choose the first value.
The COALESCE Command – Fill In the Answers
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_PtIS 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.
Some Great CAST (Convert and Store) Examples
SELECT Order_Number as OrdNo
,Customer_Number as CustNo
,CAST(Order_Time as Date) as Order_Date
,Order_Total
,CAST(Order_Total as int)as Chopped
,CAST(Order_Total as Decimal(5,0)) as Rounded
FROM Order_Table ;
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) ;
Course_Name |
CreditAlias |
Physical Database Design |
|
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 |
? |
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 retrieves 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 |
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 is 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
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 second example is better unless you have a simple query like the first example.
The CASE Challenge
Bring back all columns from the Employee_Table, but then use a CASE statement to specifically follow these parameters exactly:
WHEN Salary < 900000 THEN 'CEO'
If the Salary is BETWEEN 20000 and 40000 THEN 'Worker
If a person is in Dept_No 200 THEN 'Winner'
If a person is making < 50000 Then 'Manager'
If a person is making < 60000 Then 'VP'
Make sure to sort the data by Dept_No.
The above challenge is actually trickier than you might think. Good luck!
The CASE Challenge Answer
SELECT E.*
,CASE
WHEN Dept_No = 200 THEN 'Winner'
WHEN Salary BETWEEN 20000 and 40000 THEN 'Worker'
WHEN Salary < 50000 THEN 'Manager'
WHEN Salary < 60000 THEN 'VP'
WHEN Salary < 900000 THEN 'CEO'
Else 'Don''t know' END as TheTitle
FROM Employee_Table E ORDER BY Dept_No ;
The above challenge was tricky, but if you got the right order you nailed it.
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 allows 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')
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 Double Pipe symbols (||) that provide Concatenation. Concatenation is not a requirement for multiple case statements, but it was used here in our example.
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!