Some help ; 1. Execute the command set serveroutput on; What does this command d

Question

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');

Explanation / Answer

1) SET SERVER OUTPUT ON are just telling the PL/SQL engine that

*please print the ARGUMENT/VALUES that I will be passing inside dbms_output.put_line
and in turn PL/SQl run time engine prints the argument on the main console .

2) The argument inside DBMS_OUTPUT.PUT_LINE is printed by PL/SQl run time engine on the main console.

3) The statement find number of students having grade 'A'.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.