Answer these question by using these tables. Please. Create in PL/SQL a stored procedure called 'StudentsActivities' to display the list of students and the activities in which they participated. Create in PL/SQL a stored Function called 'Checkavailability' to check whether 'Salalah Hall' is busy or not in a given date. Create in PL/SQL a stored Procedure called 'CheckavailabilityProc' to call the function 'Checkavailability' and display the appropriate message according to the value returned by the function (If function returns true, the procedure will display 'Salalah Hall is free on date ######' otherwise'Salalah Hall is busy on date ###### ').

Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
icon
Related questions
Question

Answer these question by using these tables. Please.

  1. Create in PL/SQL a stored procedure called 'StudentsActivities' to display the list of students and the activities in which they participated.
  2. Create in PL/SQL a stored Function called 'Checkavailability' to check whether 'Salalah Hall' is busy or not in a given date.
  3. Create in PL/SQL a stored Procedure called 'CheckavailabilityProc' to call the function 'Checkavailability' and display the appropriate message according to the value returned by the function (If function returns true, the procedure will display 'Salalah Hall is free on date ######' otherwise'Salalah Hall is busy on date ###### ').

 

CREATE TABLE Student (studid Char(9) Primary Key,
FirstName Varchar2(50) NOT NULL,
LastName Varchar2(50) NOT NULL,
Email Varchar2(50) NOT NULL UNIQUE,
PhoneNumber Number(8) NOT NULL UNIQUE,
GPA Number(1,2) NOT NULL);

Insert into Student(studid, FirstName, LastName, Email, PhoneNumber, DateOfBirth, GPA) values (11,'Peter','Campbell','peter.campbell@abc.com',12345678,'12/1/2000',5)
Insert into Student(studid, FirstName, LastName, Email, PhoneNumber, DateOfBirth, GPA) values (22,'Alex','Campbell','alex.campbell@abc.com',23456789,'12/2/2001',9)
Insert into Student(studid, FirstName, LastName, Email, PhoneNumber, DateOfBirth, GPA) values (33,'Baird','Campbell','baird.campbell@abc.com',34567890,'12/3/2002',8)
Insert into Student(studid, FirstName, LastName, Email, PhoneNumber, DateOfBirth, GPA) values (44,'Justin','Campbell','justin.campbell@abc.com',46781239,'12/4/2003',7)
 

 

CREATE TABLE Club (clubID Number(3) Primary Key,
ClubName Varchar2(20) NOT NULL,
studid Char(9) NOT NULL UNIQUE, Foreign key(studid) references Student(studid));

Insert into Club(clubID, ClubName, studid) values (1,'BasketBall',11)
Insert into Club(clubID, ClubName, studid) values (2,'Books',22)
Insert into Club(clubID, ClubName, studid) values (3,'BaseBall',33)
Insert into Club(clubID, ClubName, studid) values (4,'Cricket',44)

 

CREATE TABLE MemberOf (ClubID Number(3) Primary Key, 
Studid char(9), 
JoiningDate date, 
Foreign Key(ClubID) references club(clubID), Foreign Key(Studid) references Student(studid));

Insert into MemberOf(ClubID, Studid, JoiningDate) values (1,11,'01/01/2021')
Insert into MemberOf(ClubID, Studid, JoiningDate) values (2,22,'02/01/2021')
Insert into MemberOf(ClubID, Studid, JoiningDate) values (3,33,'03/01/2021')
Insert into MemberOf(ClubID, Studid, JoiningDate) values (4,44,'04/01/2021')


CREATE TABLE Activities (Actid Number(3) Primary Key,
Acdt DATE NOT NULL,
Place Varchar2(50) NOT NULL,
durationNbHour Number NOT NULL);

Insert into Activities(Actid, Acdt, Place, durationNbHour) values (111, '12/20/2021', 'Stadium', 1)
Insert into Activities(Actid, Acdt, Place, durationNbHour) values (222, '12/21/2021', 'Ground', 2)
Insert into Activities(Actid, Acdt, Place, durationNbHour) values (333, '12/22/2021', 'School', 3)
Insert into Activities(Actid, Acdt, Place, durationNbHour) values (444, '12/23/2021', 'College', 2)

 


CREATE TABLE Organize (actid Number(3) Primary Key, 
clubID Number(3), 
Fee Number(4,2), Foreign Key(actid) references Activities(Actid),
Foreign Key(clubID) references MemberOf(ClubID));

Insert into Organize (actid, clubID, Fee) values (111,1,20)
Insert into Organize (actid, clubID, Fee) values (222,2,30)
Insert into Organize (actid, clubID, Fee) values (333,3,40)
Insert into Organize (actid, clubID, Fee) values (444,4,50)

 

 

Expert Solution
steps

Step by step

Solved in 2 steps with 1 images

Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY