Run the SQL statements in the right hand panel.
Scenario: One lucky employee
A company that produces gambling equipment for casinos is running a
promotion for its full-time employees. Each month their DB2 database will randomly pick the name of one lucky
full-time employee to be rewarded with a cash prize.
To support this lottery, the database has a few specifications:
- The company keeps the ids, names, job status and salary information of all its employees in the EMPLOYEE table.
- There is a view called FULLTIME_EMPLOYEE that contain the records of full-time employees only.
- There is a function called LUCKY_NUMBER that returns a random number based on an employee's name.
- There is another view called LUCKY_FULLTIME_EMPLOYEE defined on top of FULLTIME_EMPLOYEE view. This view selects
the name with the lowest number based on output from LUCKY_NUMBER function.
Please note the order of CREATE commands:
- LUCKY_FULLTIME_EMPLOYEE is created first, even though the underlying FULLTIME_EMPLOYEE does not exist yet.
- FULLTIME_EMPLOYEE view is created before the underlying EMPLOYEE table is created.
- Function LUCKY_NUMBER depends on the existence of EMPLOYEE table, but it is also created, without error, before CREATE TABLE command
This demonstrates the ability of DB2 9.7 to create objects tolerant of certain errors, like a missing object.
Similarly, DB2 9.7 allows certain errors in SQL Inline Functions.
After running the SQL statements, you will see several errors in the Console - lower right panel.
Despite the failure messages, the commands did work. Warnings were returned to inform
you that dependent objects have not yet been created.