Assume that you have these 5 tables.. Answer this question by using those tables. Please. 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,
Assume that you have these 5 tables.. Answer this question by using those tables. Please.
- 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
