Chapter 5 Practice
1) Write the code that will prompt you for the employee’s last name and then will display
message about his/her pay with the appropriate note regarding the salary range:
- Less than $3,000 POOR
- Less than $6,000 FAIR
- Less than $10,000 GOOD
- Less or equal than $15,000 EXCELLENT
- More than $15,000 WOW
You also need to be prepared for a name that does NOT exist and for the case when more than
one person holds the same name.
Here is the FIRST output:
Employee HIGGINS has a monthly income of $12000 which is EXCELLENT
PL/SQL procedure successfully completed.
Here is the SECOND output:
Employee KOCHHAR has a monthly income of $17000 which is WOW
PL/SQL procedure successfully completed.
Here is the THIRD output:
Employee ADAMS does NOT exist
PL/SQL procedure successfully completed.
Here is the FOURTH output:
There is more than one employee with such last name: GRANT
PL/SQL procedure successfully completed.
2)Write the code that will prompt you for the Student Id and also for Section Id and then will display message about the Letter Grade for that student and that course section (based on the numeric value for the Final Grade in table Enrollment).
You also need to be prepared for an input that will yield NO output and also to deal with the situation where Final Grade is not entered yet (blank value in the table). In that case Letter Grade should be ‘I’.
Here is the FIRST output:
Enter value for student: / /Enter value for section: / /
This student OR this section does NOT exist
PL/SQL procedure successfully completed.
Here is the SECOND output:
Enter value for student: / /Enter value for section: / /
Letter grade is : A
PL/SQL procedure successfully completed.
Here is the THIRD output:
Enter value for student: / /Enter value for section: / /
Letter grade is : I
PL/SQL procedure successfully completed.
3)Firstly create the table TEST with three columns:
Result -- numeric with 3 digits
Note -- variable character of maximum 50 bytes
Last -- variable character of maximum 20 bytes
Write the code that will prompt you for the employee’s Salary and then will count number of characters in his/her last name. Then will populate the table TEST’ column NOTE with that many ‘X’ characters like this length. Column RESULT will store this number and column LAST will store the last name of that employee. Use WHILE loop.
Then display content of the table TEST and finally undo your population.
You also need to be prepared for a salary that does NOT exist and for the case when more than one person gets the same salary.
Here is the FIRST output:
Top of Form
Enter value for pay: / /PL/SQL procedure successfully completed.
RESULT / NOTE / LAST7 / XXXXXXX / Russell
Rollback complete.
Here is the SECOND output:
Top of Form
Enter value for pay: / /There is more than one employee with such a salary: 7000
PL/SQL procedure successfully completed.
no rows selected
Rollback complete.
Here is the THIRD output:
Top of Form
Enter value for pay: / /Salary value of $20000 does NOT exist
PL/SQL procedure successfully completed.
no rows selected
Rollback complete.
4)Rewrite the Question 3 by using numeric FOR loop.
ANSWERS
1)
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
lname employees.last_name%TYPE := '&lname' ;
pay employees.salary%TYPE;
note VARCHAR2(20) := 'FAIR';
BEGIN
SELECT salary INTO pay
FROM employees
WHERE UPPER(last_name) = lname;
IF pay < 3000 THEN
note := 'POOR';
ELSIF pay < 6000 THEN
note := 'FAIR';
ELSIF pay < 10000 THEN
note := 'GOOD';
ELSIF pay <= 15000 THEN
note := 'EXCELLENT';
ELSE
note := 'WOW';
END IF;
DBMS_OUTPUT.PUT_LINE('Employee ' || lname || ' has a monthly income of $' || pay || ' which is ' || note);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || lname || ' does NOT exist');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('There is more than one employee with such last name: ' || lname);
END;
2)
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
v_id student.student_id %TYPE := &student;
v_sec enrollment.section_id%TYPE := §ion;
v_fingrade NUMBER(3);
v_letgrade CHAR(1);
BEGIN
SELECT final_grade INTO v_fingrade
FROM Enrollment
WHERE student_id = v_id
AND section_id = v_sec;
IF v_fingrade BETWEEN 90 AND 100 THEN
v_letgrade := 'A';
ELSIF v_fingrade BETWEEN 80 AND 89 THEN
v_letgrade := 'B';
ELSIF v_fingrade BETWEEN 70 AND 79 THEN
v_letgrade := 'C';
ELSIF v_fingrade BETWEEN 60 AND 69 THEN
v_letgrade := 'D';
ELSE
v_letgrade := 'F';
END IF;
IF v_fingrade IS NULL THEN
v_letgrade := 'I';
END IF;
DBMS_OUTPUT.PUT_LINE('Letter grade is : ' || v_letgrade);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('This student OR this section does NOT exist');
END;
3)
CREATE TABLE test (result NUMBER(3),note VARCHAR2(50), last VARCHAR2(20) )
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
counter NUMBER(3) := 1;
a VARCHAR2(30);
b NUMBER(2) := 0;
lname employees.last_name%TYPE;
pay employees.salary%TYPE := &pay;
BEGIN
SELECT last_name, LENGTH(last_name) INTO lname, b
FROM employees
WHERE salary = pay;
WHILE counter <= b LOOP
a := a || 'X' ;
counter := counter + 1;
END LOOP;
INSERT INTO test (result, note, last) VALUES (b,a, lname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Salary value of $' || pay || ' does NOT exist');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('There is more than one employee with such a salary: ' || pay);
END;
/
SELECT * FROM test;
ROLLBACK;
4)
DECLARE
-- NO counter declared here
a VARCHAR2(30);
b NUMBER(2) := 0;
lname employees.last_name%TYPE;
pay employees.salary%TYPE := &pay;
BEGIN
SELECT last_name, LENGTH(last_name) INTO lname, b
FROM employees
WHERE salary = pay;
FOR indIN 1 .. b LOOP -- NO ind declared here
a := a || 'X' ;
END LOOP;
The rest is the SAME