ADBMS -- Practice programs

Use the following table to do the programs (1 to 7) given below:-

EMPLOYEE (Eid, name, deptno, basicpay, dob)

Q1) Calculate the netpay of the employees using the following information :

Netpay = basicpay + da + hra +cca

da = 41 % of basicpay, hra = 12% of basicpay, cca = 20% of basicpay for basicpay > 8000, 15% of basicpay otherwise

Q2) For every employee in dept. no. 20, insert a record in the following table:

RETD(eid, name, dor) . Here dor is the retirement date of the employee. The retirement date is at the age of 65. If the retirement date is in the middle of the month, show the retirement date at the end of the month.

Q3) Among employees belonging to dept 2, find the employees with the top 3 salaries.

Q4) After any update of the basicpay field, if the basicpay value is increased, then record the increase in the following table GENINC(eid, cngdt, oldbasic, newbasic). The system date is to be inserted in the cngdt field.

Q5) After any update of the basicpay field, if the basicpay value becomes greater than 10,000 (provided earlier it was less than 10,000), then record the increase in the following table INC(eid, cngdt, oldbasic, newbasic). The system date is to be inserted in the cngdt field.

Q6) After any update of the basicpay field, if the difference is at least a 2000 amount increase, then record the increase in the following table HINC(eid, cngdt, oldbasic, newbasic). The system date is to be inserted in the cngdt field.

Q7) After any update of the basicpay field of employees in dept 5, record the increase in the following table DEP5CHG(eid, cngdt, oldbasic, newbasic). The system date is to be inserted in the cngdt field.

Q8) CUST(cid, itno, purchdate, qty, sno) records the item numbers and respective quantities of different items bought by a customer on a particular date from a particular salesman with id sno . The following table SOLDBY(sid, itno) keeps track of the items sold by a particular salesman. Use this table (SOLDBY) to ensure that only records with valid itno-sno combinations are inserted into CUST.

Note : Also practice the programs done in class/lab, particularly the parameterized cursor example