IDUG DB2 2016 SQL Challenge Week 3

A RANDOM PARTICIPANT IN THIS MONTH’S CHALLENGE WILL RECEIVE A $200USD COUPON TO USE TOWARDS TAKING IBM CERTIFICATION EXAMS! POST YOUR ANSWERS IN THE COMMENTS OF THIS ARTICLE FOR A CHANCE. A winner will be chosen at random from the participants at the end of the month. During this week's challenges we will build on the set up we did in week 1, if you missed that please first go to:

http://www.idug.org/p/bl/et/blogaid=476

 We will be working with EMP2 and EMP4 this week.

 1) Write an update statement to give everyone of department D11 a 5% salary increase both for EMP2 and EMP4 table.

How many rows are affected? Does that match the number of rows in your _hist tables for emp2 and emp4? Select all records directly from _hist table.

2) Give all managers a 10% increase both for EMP2 and EMP4 in the same unit of work.

3) Select from EMP2 and EMP4 all data for "Irving Stern" (EMPNO 000060).  Do the same for their _hist tables, to understand your data.

4) Pick a timestamp between both updates select the data for empno 000060 of that particular timestamp both for emp2 and emp4. Give the content of your emp2_hist, your emp4_hist. Give the select to the above question and the result.

                                * I'm looking for the syntax FOR SYSTEM_TIME AS OF TIMESTAMP 

5) Select the data for empno 000060 for first of January 1990 (or any timestamp before you created emp2 and emp4) both of emp2 and emp4. Give the select to the above question and the result. Does that seem normal to you? Is the data correct logically?

6) Select all the data for empno 000060 for the period first of January 1990 until tomorrow both of emp2 and emp4. Give the select to the above question and the result.

                Does that seem normal to you? Is the data correct logically?

                                * I'm looking for FOR SYSTEM_TIME BETWEEN or FROM TO

 

7) How does EMP2/EMP2_HIST and EMP4/EMP4_Hist change when you add Bob Rady  to EMP2 and EMP4

VALUES ('010000','Bob','','Rady','A00','3978','1965-01-01','awesome',18,'M',14.08.1933,52750.00,1000.00,4220.00  ) ;

 8) How does EMP2/EMP2_HIST and EMP4/EMP4_Hist if you delete Bob Rady from EMP2 and EMP4?

 

Post your answers to the above questions online or send them to kurt.struyf@lone-star.be

Hope you have a great time.

Kurt Struyf

Recent Stories
Selecting Appropriate Data Architecture Technologies

SQL Tuning for Developers – Part 2 Explain Output

Some of my favorite tunings tips and tricks for DB2 by Joe Geller