"The difference between genius and stupidity is that genius has its limits"
- Albert Einstein
The functions above are often used for algebraic, trigonometric, or geometric calculations.
Find the cube root with the cbrt function.
Find the smallest integer not smaller than x by using the ceil command. This stands for a numbers integer ceiling.
Find the largest integer not greater than x by using the floor command. This stands for a numbers integer floor.
Use the round function to round to the precision you need.
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.
SELECT Class_Code
,Grade_Pt / (Grade_Pt * 2 ) as Math1
FROM Student_Table
ORDER BY 1, 2 ;
Class_Code |
Math1 |
FR |
0 |
FR |
0.5000000000000000000000000 |
FR |
0.5000000000000000000000000 |
JR |
0.5000000000000000000000000 |
JR |
0.5000000000000000000000000 |
SO |
0.5000000000000000000000000 |
SO |
0.5000000000000000000000000 |
SR |
0.5000000000000000000000000 |
SR |
0.5000000000000000000000000 |
? |
? |
Above are your answers.
SELECT Class_Code
,Grade_Pt / ( NULLIFZERO (Grade_pt) * 2 ) AS Math1
FROM Student_Table
ORDER BY 1, 2 ;
If you have a calculation where a ZERO is not desired, you can use the NULLIFZERO command to convert any zero value to a null value. Turn the page and see the results.
If you have a calculation where a ZERO is not desired, you can use the NULLIFZERO command to convert any zero value to a null value.
SELECT NULLIFZERO (Cust_No) AS Cust_No
,NULLIFZERO (Acc_Balance) AS Acc_Balance
,NULLIFZERO (Location) AS Location
FROM Sample_Table ;
Fill in the Answer Set above after looking at the table and the query.
Okay! Time to show me your brilliance! What would the Answer Set produce?
SELECT NULLIFZERO (Cust_No) AS Cust_No
,NULLIFZERO (Acc_Balance) AS Acc_Balance
,NULLIFZERO (Location) AS Location
FROM Sample_Table ;
Here is the answer set! How did you do? The NULLIFZERO command found a zero in Cust_No, so it made it Null. The others were not zero, so they retained their value. The only time NULLIFZERO changes data is if it finds a zero, and then it changes it to null.
What would the above Answer Set produce from your analysis?
Look at the answers above, and if it doesn’t make sense, go over it again until it does.
SELECT ZEROIFNULL (Cust_No) as Cust
,ZEROIFNULL (Acc_Balance) as Balance
,ZEROIFNULL (Location) as Location
FROM Sample_Table ;
Fill in the Answer Set above after looking at the table and the query.
This is the ZEROIFNULL. What it will do is put a zero into a place where a NULL shows up. Fill in what you think the answer set will be.
SELECT ZEROIFNULL (Cust_No) as Cust
,ZEROIFNULL (Acc_Balance) as Balance
,ZEROIFNULL (Location) as Location
FROM Sample_Table ;
The answer set placed a zero in the place of the NULL Acc_Balance, but the other values didn’t change because they were NOT 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 don’t want NULLs, notice 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.
Coalesce returns the first non-Null value in a list, and if all values are Null, returns Null.
SELECT
Last_Name
,Grade_Pt
,Student_ID
,COALESCE (Grade_Pt, Student_ID) 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.
SELECT
Last_Name
,Grade_Pt
,Class_Code
,COALESCE (Grade_Pt, Student_ID) as ValidStudents
FROM Student_Table ;
SELECT
Last_Name
,Grade_Pt
,Class_Code
, CASE
WHEN Grade_PtIS NOT NULL THEN Grade_Pt
WHEN Student_ID IS NOT NULL THEN Student_ID
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 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.
SELECT CAST(121.53 AS SMALLINT) AS Whole
,CAST(121.53 AS DECIMAL(3,0)) AS Rounder ;
Whole |
Rounder |
122 |
122 |
SELECT CAST(121.49 AS SMALLINT) AS Whole
,CAST(121.53 AS DECIMAL(3,0)) AS Rounder ;
Whole |
Rounder |
121 |
122 |
SELECT CAST(121.50 AS SMALLINT) AS Whole
,CAST(121.53 AS DECIMAL(3,0)) AS Rounder ;
Whole |
Rounder |
122 |
122 |
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, but also rounds up because 53 is > 50. The CAST in the next column called Rounder is converted to a DECIMAL as 3 digits with no decimals, so it will also round data values. Since .53 is greater than .5, it is rounded up to 122.
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"
Rounding isn't always intuitive as you can see from the examples above.
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 Order_Date ;
Notice how the rounding did not take place as you might have expected.
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 SQLFeatures |
|
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 |
? |
SQLFeatures |
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.
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 |
SQLFeatures |
Two 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.
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.
Sample_Table
Course_Name |
Credits |
Tera-Tom on SQL |
1 |
SELECT Course_Name
,CASE Credits
WHEN 1 THEN 'One Credit'
WHEN 2 THEN 'Two Credits'
WHEN 3 THEN 'Three Credits'
END AS CreditAlias
FROM Sample_Table ;
Course_Name |
CreditAlias |
|
|
Fill in the Answer Set aboveafter looking at the table and the query.
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?
Course_Name |
CreditAlias |
Tera-Tom on SQL |
One Credit |
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?
The second example is better unless you have a simple query like the first example.
Look at the CASE Statement and look at the Course_Table, and fill in the Answer Set.
Above is the full answer set.
Look at the CASE Statement and look at the Course_Table, and fill in the Answer Set.
Above is the full answer set.
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?
A null value will occur when the evaluation falls through the case and there is no else statement. Notice above 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. That is why the null value is in the report.
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?
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” with the actual apostrophe.
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
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
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 Double Pipe symbols (||) that provide Concatenation.
I will bet you didn't know you could put a CASE statement in the Order By? You do now!