Chapter 6 Test Your Thinking

In this chapter you learned about different types of CASE statements and expressions. You also learned about NULLIF and COALESCE functions. Here are some exercises based on the scripts created in this section in Chapter 5 that will help you test the depth of your understanding.

1)	Create the following script. Modify the script created in this section in Chapter 5 (Question 1 of the Test Your Thinking section). You can use either the CASE statement or the searched CASE statement. Your output should look similar to the output produced by the example created in Chapter 5.
2)	Create the following script. Modify the script created in this section in Chapter 5 (Question 2 of the Test Your Thinking section). You can use either the CASE statement or the searched CASE statement. Your output should look similar to the output produced by the example created in Chapter 5.
3)	Execute the following two SELECT statements and explain why they produce different output: SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade, COALESCE(g.numeric_grade, e.final_grade) grade FROM enrollment e, grade g WHERE e.student_id = g.student_id AND e.section_id = g.section_id AND e.student_id = 102 AND g.grade_type_code = 'FI'; SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade, NULLIF(g.numeric_grade, e.final_grade) grade FROM enrollment e, grade g WHERE e.student_id = g.student_id AND e.section_id = g.section_id AND e.student_id = 102 AND g.grade_type_code = 'FI';

The projects in this section are meant to have you utilize all of the skills that you have acquired throughout this chapter. The answers to these projects can be found in Appendix D and at the companion Web site to this book, located at http://authors.phptr.com/rosenzweig3e. Visit the Web site periodically to share and discuss your answers.