Lab 5.1 Exercises

5.1.1 Use the IF-THEN Statement

In this exercise, you will use the IF-THEN statement to test whether the date provided by the user falls on the weekend . In other words, if the day happens to be Saturday or Sunday.

Create the following PL/SQL script:

 -- ch05_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');    v_day VARCHAR2(15); BEGIN    v_day := RTRIM(TO_CHAR(v_date, 'DAY'));    IF v_day IN ('SATURDAY', 'SUNDAY') THEN       DBMS_OUTPUT.PUT_LINE (v_date' falls on weekend');    END IF;    --- control resumes here    DBMS_OUTPUT.PUT_LINE ('Done...'); END; 

In order to test this script fully, execute it twice. For the first run, enter '09-JAN-2002', and for the second run, enter '13-JAN-2002'. Execute the script, and then answer the following questions:

a)	What output was printed on the screen (for both dates)?
b)	Explain why the output produced for the two dates is different.

Remove the RTRIM function from the assignment statement for v_day as follows :

 v_day := TO_CHAR(v_date, 'DAY'); 

Run the script again, entering '13-JAN-2002' for v_date .

c)	What output was printed on the screen? Why?
d)	Rewrite this script using the LIKE operator instead of the IN operator, so that it produces the same results for the dates specified earlier.
e)	Rewrite this script using the IF-THEN-ELSE construct. If the date specified does not fall on the weekend, display a message to the user saying so.

5.1.2 Use the IF-THEN-ELSE Statement

In this exercise, you will use the IF-THEN-ELSE statement to check how many students are enrolled in course number 25, section 1. If there are 15 or more students enrolled, section 1 of course number 25 is full. Otherwise, section 1 of course number 25 is not full and more students can register for it. In both cases, a message should be displayed to the user indicating whether section 1 is full. Try to answer the questions before you run the script. Once you have answered the questions, run the script and check your answers. Note that the SELECT INTO statement uses ANSI 1999 SQL standard.

Create the following PL/SQL script:

 -- ch05_2a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    v_total NUMBER; BEGIN    SELECT COUNT(*)      INTO v_total      FROM enrollment e      JOIN section s USING (section_id)     WHERE s.course_no = 25       AND s.section_no = 1;    -- check if section 1 of course 25 is full    IF v_total >= 15 THEN       DBMS_OUTPUT.PUT_LINE          ('Section 1 of course 25 is full');    ELSE       DBMS_OUTPUT.PUT_LINE          ('Section 1 of course 25 is not full');    END IF;    -- control resumes here END; 

Notice that the SELECT INTO statement uses an equijoin . The join condition is listed in the JOIN clause, indicating columns that are part of the primary key and foreign key constraints. In this example, column SECTION_ID of the ENROLLMENT table has a foreign key constraint defined on it. This constraint references column SECTION_ID of the SECTION table, which, in turn , has a primary key constraint defined on it.

You will find detailed explanations and examples of the statements using new ANSI 1999 SQL standard in Appendix E and in the Oracle help. Throughout this book we try to provide you with examples illustrating both standards; however, our main focus is on PL/SQL features rather than SQL.

In the previous versions of Oracle, this statement would look as follows:

 SELECT COUNT(*)   INTO v_total   FROM enrollment e, section s  WHERE e.section_id = s.section_id    AND s.course_no = 25    AND s.section_no = 1; 

Try to answer the following questions first and then execute the script:

a)	What DBMS_OUTPUT.PUT_LINE statement will be displayed if there are 15 students enrolled in section 1 of course number 25?
b)	What DBMS_OUTPUT.PUT_LINE statement will be displayed if there are 3 students enrolled in section 1 of course number 25?
c)	What DBMS_OUTPUT.PUT_LINE statement will be displayed if there is no section 1 for course number 25?
d)	How would you change this script so that both course and section numbers are provided by a user?
e)	How would you change this script so that if there are less than 15 students enrolled in section 1 of course number 25, a message indicating how many students can still be enrolled is displayed?