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 ###### ').
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 ###### ').
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)
Step by step
Solved in 2 steps with 1 images
