Blog

assignment #2 (60 Points) Based on the and tables created in Assignment #1, write a that acceptsanemployeeIDfrom the user input and displays 1) the employee’sname, hire date,and total pay (salary + commission), 2) his/her manager’s name, hire date,and total pay (salary + commission), 3) the of the department where the employee works, and 4) the maximum total pay (salary + commission) for the department where the employee works. Submitting more than PL/SQL program will receive 0 points. If the employee ID from the user input is not in the table ( ), you display a message telling the user that the employee ID is not in the table. You must display the total pay with a dollar ($) sign, a comma, and two decimal places (e.g., ). The hire date must be displayed in the format (4-digit year). If the employee does not have a manager, the manager’s name, hire date and total paymust be shown as “ ”. If the employee does not belong to any department, the department name must be shown as “ ”, and the maximum total pay (salary + commission) for the department must be shown as “ ”. You will lose 5 points if your output does not have “ ”, “ ”, “ ”, “ ”, or “ ”. To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9 and Enter. Example: Case 1) Output: Case 2) Output: Case 3) Output: Based on the and tables created in Assignment #1, write a that accepts a from the user inputand displaysa) the department ID of that department, b) the address of that department, c) the number of employees working in that department, d) the average salary for that department, e) the num

ber of employees in that department whose salary is higher than the average salary for that department, and f) the number of employees in that department whose salary is higher than the average salary of all employees in the . If the department name from the user input is not in the table ( ), you display a message telling the user that the department name is not in the table. Department name is not case sensitive (e.g., SALES = Sales).  You will lose 5 points if you do not use the or function in your program. You will lose 5 points if your output does not have “ ” or “ ”. Please test your program.The output of your program must match the following: Case 1) Output: Case 2) Output: Case 3) Output: Based on the table created in Assignment #1, write a that displays the number of employees earned salaries in each of the following ranges: $0.00 – $499.99, $500.00 – $999.99, …, $4500.00 – $4999.99, $5000.00 – $5499.99. You can only use statement in your program. Submitting more than one PL/SQL program will receive 0 points. You will lose 10 points if more than one statement is used. nt: Creating cleanly formatted output is a common programming requirement. The format of your output must match mine If your output does not match mine , you will lose some points. In a bug tracking database, there is a table called . The table has several columns: and . Create and populate the table. “Open Bugs” – A bug is considered open on a given day if (1) its “ ” is on or before that day, and (2) its “ ” is on or after that day (or is unknown ( )). For example, we have 5 open bugs on 5/5/2014. The output of your program should match the following: