Some help ;

1. Execute the command set serveroutput on;

What does this command do? Google it on the Internet. 2.

Type the following program and execute it. Can you tell from the result what the command

DBMS_OUTPUT.PUT_LINE does?

--PL/SQL program to display current date

DECLARE

today_date DATE;

BEGIN

today_date := SYSDATE;

DBMS_OUTPUT.PUT_LINE('Today''s date is ');

DBMS_OUTPUT.PUT_LINE(today_date);

END;

3. Before you begin to work on the following questions, first download createDB.sql from the course website and execute it.

Read the following program and figure out what it does. Then type it and execute it to verify if you are right.

--PL/SQL program to get grade statistics

DECLARE

na integer :=0;

BEGIN Select count(*)

into na

from gradeReport

where grade='A'; if (na > 0) then

DBMS_OUTPUT.PUT_LINE('there are total ' || na || ' A''s');

else

DBMS_OUTPUT.PUT_LINE('No student makes an A');

end if;

END;

4. Revise the above program, so that it will output the statistics for the grades A, B, C, D, and F.

-----------------------------------------------------------------------------------------------------------

this is createDB.sql :

rem this example shows creating tables and specifying FK keys with rem on delete constraint drop table Student cascade constraints; create table Student ( name varchar2(30) not null, StudentNumber number(4) not null, class number(2), major varchar2(4), primary key (StudentNumber) );

insert into Student values('Smith', 17, 1, 'CS'); insert into Student values('Brown', 8, 2, 'CS');

drop table Course cascade constraints; create table Course ( CourseName varchar2(30) not null, CourseNumber varchar2(10), CreditHours number(1), Department varchar2(10), primary key (CourseNumber) );

insert into Course values('Intro to Computer Science','CS1310',4,'CS'); insert into Course values('Data Structures','CS3320',4,'CS'); insert into Course values('Discrete Mathematics','MATH2410',3,'MATH'); insert into Course values('Database','CS3380',3,'CS');

drop table Section cascade constraints; create table Section ( SectionIdentifier number(4), CourseNumber varchar2(10), Semester varchar2(10), Year number(2), Instructor varchar2(20), primary key (SectionIdentifier), foreign key (CourseNumber) references Course on delete cascade );

insert into Section values(85, 'MATH2410','Fall',98,'King'); insert into Section values(92, 'CS1310','Fall',98,'Anderson'); insert into Section values(102, 'CS3320','Spring',99,'Knuth'); insert into Section values(112, 'MATH2410','Fall',99,'Chang'); insert into Section values(119, 'CS1310','Fall',99,'Anderson'); insert into Section values(135, 'CS3380','Fall',99,'Stone');

drop table GradeReport cascade constraints; create table GradeReport ( StudentNumber number(4) not null, SectionIdentifier number(4), Grade char(1), primary key (StudentNumber, SectionIdentifier), foreign key (StudentNumber) references Student on delete cascade, foreign key (SectionIdentifier) references Section on delete cascade );

insert into GradeReport values (17, 112, 'B'); insert into GradeReport values (17, 119, 'C'); insert into GradeReport values (8, 85, 'A'); insert into GradeReport values (8, 92, 'A'); insert into GradeReport values (8, 102, 'B'); insert into GradeReport values (8, 135, 'A');

drop table Prerequisite cascade constraints; create table Prerequisite ( CourseNumber varchar2(10) not null, PrerequisiteNumber varchar2(10), primary key (CourseNumber, PrerequisiteNumber), foreign key (CourseNumber) references Course on delete cascade, foreign key (PrerequisiteNumber) references Course on delete set null );

insert into Prerequisite values('CS3380', 'CS3320'); insert into Prerequisite values('CS3380', 'MATH2410'); insert into Prerequisite values('CS3320', 'CS1310');