"Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you."
- Carl Sandburg
Can you guess what would return in the Answer Set?
Using the Student_Table above, try and predict what the answer will be if this query was running on the system.
Error – Division by zero
You get an error when you DIVIDE by ZERO! Let’s turn the page and fix it!
SELECT Class_Code
,Grade_Pt / ( NULLIF (Grade_pt, 0) * 2 ) 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 NULLIFZERO command to convert any zero value to a null value.
What would the above Answer Set produce from your analysis?
Look at the answers above. If it doesn’t make sense, go over it again until it does.
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone)
as Phone
FROM Sample_Table ;
Last_Name |
Phone |
Fill in the Answer Set above after looking at the table and the query
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone)
as Phone
FROM Sample_Table ;
Last_Name |
Phone |
Jones |
555-1234 |
Patel |
456-7890 |
Gonzales |
354-0987 |
Nguyen |
? |
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone, 'No Phone')
as Phone
FROM Sample_Table ;
Last_Name |
Phone |
Fill in the answer set above after looking at the table and the query
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null. Since we decided in the above query we didn’t want NULLs, we have placed a literal ‘No Phone’ in the list. How will this affect the Answer Set?
SELECT Last_Name
,COALESCE (Home_Phone, Work_Phone, Cell_Phone, 'No Phone')
as Phone
FROM Sample_Table ;
Last_Name |
Phone |
Jones |
555-1234 |
Patel |
456-7890 |
Gonzales |
354-0987 |
Nguyen |
No Phone |
Answers are above! We put a literal in the list so there’s no chance of NULL returning.
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.
SELECT Order_Total as "Original_Price"
,CAST(Order_Total as Integer) as "Round1"
,CAST(Order_Total as Decimal(5,0)) as "Round2"
,CAST(Order_Total as Decimal(9,4)) as "Decimal4"
FROM Order_Table ;
The Column aliased as "Original_Price" is a Decimal (8,2) data type. When this is CAST as an Integer or a Decimal (5,0) it rounds up or down based on the decimal values. If the values in the decimal are 50 or more it rounds up and if they are 49 or below it rounds down. The column aliased as Decimal (9,4) will add four decimal places and does no rounding.
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?
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 gets the next row.
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.
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:
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.
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.
The second example is better unless you have a simple query like the first example.
Look at the CASE Statement and the Course_Table, and fill in the Answer Set.
Above is the full answer set.
Look at the CASE Statement and the Course_Table, and fill in the Answer Set.
Above is the full answer set.
Since our value of 4 fell through the CASE statement, the ELSE statement kicked in and we delivered ‘Don’t Know’. Notice two single quotes that provided the word “Don’t”.
The above challenge is actually trickier than you might think. Good luck!
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
FROM Employee_Table as E
ORDER BY Dept_No ;
The above challenge was tricky, but if you got the right order, you nailed it.
This Query above uses both a Valued Case and Searched Case. That’s ALLOWED!
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.
I will bet you didn't know you could put a CASE statement in the Order By? You do now!
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')
A NESTED Case occurs when you have a Case Statement within another CASE Statement. Notice the Double Pipe symbols (||) that provide Concatenation.