April 2015 “Fun with SQL” Challenge 2
This month on the Content Committee blog we’ll be doing an online version of the “Fun with SQL” dojo. Each week we’ll present an SQL challenge and request the community to solve it. People can respond to the challenge in the comments section of the blog post, or they can email Kurt directly at email@example.com with a solution or idea and he will post it to the blog. You can post a possible answer, ask a question, and comment on something someone else has posted, or offer alternatives to something that’s been posted.
Fully using the power of SQL, becomes clear using SQL PL, so that is where this week's challenge will be. understanding how to write native stored procedures, isn't only a good skill to have, since the are ZIIP offloadable when called over DRDA, they pack a performance benefit as well.
Here is the second SQL challenging based upon the DB2 sample database:
create a SQL stored procedure that has the following features:
with one input parameter with a datatype of char(3)
Use the department number input parameter to do an existence check against the department table before processing the cursor against the employee table. If the department does not exist then fail the call to the stored procedure with SQLSTATE '38001'.
If the department does exist then continue with returning the employee cursor. The cursor to the stored procedure reads the employee table (columns empno, lastname, and salary) for a workdept value equal to the new input parameter.
return the cursor to the caller
Don’t be shy about posting your answer if someone else has already posted. At the end of the month, the content committee will take the names of the people that have participated and randomly select one to receive a reward of an IPad case, courtesy of IBM! So, don’t be shy, just dive in and share your experience or inexperience and get a free and fun education!
have fun !