Assume that you have these 5 tables.. Answer this question by using those tables. Please. Create in PL/SQL a stored Function called 'Checkavailability' to check whether 'Salalah Hall' is busy or not in a given date.
Assume that you have these 5 tables.. Answer this question by using those tables. Please.
- Create in PL/SQL a stored Function called 'Checkavailability' to check whether 'Salalah Hall' is busy or not in a given 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
