RAFT 4 Starting Script
-- thses statements may fail the first time you run this script becuase the tables do not yet exist
drop table scheduledFor;
drop table appointment;
drop table room;
drop table employee;
drop table client;
drop table counselor;
create table Counselor(
CounselorID int GENERATED ALWAYS as IDENTITY(START with 100) primary key ,
FirstName varchar(50),
LastName varchar(50),
Address varchar(50),
City varchar(50),
State char(2),
ZIP char(10),
Phone varchar(14),
Email varchar(100),
DegreeSuffix varchar(50)
);
create table Client(
ClientID int GENERATED ALWAYS as IDENTITY(START with 200) primary key ,
FirstName varchar(50),
LastName varchar(50),
Address varchar(50),
City varchar(50),
State char(2),
ZIP char(10),
Phone varchar(14),
Email varchar(100),
FoundOut varchar(50),
ReminderMethod varchar(50),
ReferredBy int references client
);
create table Employee(
EmployeeID int GENERATED ALWAYS as IDENTITY(START with 300) primary key ,
FirstName varchar(50),
LastName varchar(50),
Address varchar(50),
City varchar(50),
State char(2),
ZIP char(10),
Phone varchar(14),
Email varchar(100),
DateHired date,
Wage number(4,2),
SupervisorID int references Employee
);
create table Room(
RoomNumber int primary key,
RoomName varchar(50)
);
create table Appointment(
AppointmentID int GENERATED ALWAYS as IDENTITY(START with 1000) primary key,
AppointmentTime date,
Duration int,
CounselorID int references counselor not null,
EmployeeID int references employee not null,
RoomNumber int references room not null,
Notes varchar(3000)
);
create table ScheduledFor(
AppointmentID int references Appointment,
ClientID int references Client,
Assignment VARCHAR(300),
kept number(1),
PRIMARY KEY (AppointmentID,ClientID)
);
/*data for Counselors*/
insert into Counselor(firstname, lastname, address, city, state, zip, phone, email, DegreeSuffix)
values('Quinn','Fowler','5208 Walmsley Blvd','Richmond','VA','23224','(804) 286-4422','qfowler@raftcounseling.com', 'MS');
insert into Counselor(firstname, lastname, address, city, state, zip, phone, email, DegreeSuffix)
values('Elizabeth','Baker','3409 Irvington St','Richmond','VA','23234','(804) 539-9337','ebaker@raftcounseling.com', 'PhD');
insert into Counselor(firstname, lastname, address, city, state, zip, phone, email, DegreeSuffix)
values('Darold','Cooper','4003 Corbin St','Richmond','VA','23222','(804) 286-4422','dbcooper@raftcounseling.com', 'MFT');
insert into Counselor(firstname, lastname, address, city, state, zip, phone, email, DegreeSuffix)
values('Diana','Chandler','600 Idlewood Ave','Richmond','VA','23220','(804) 737-9704','dchandler@raftcounseling.com', 'EdD');
insert into Counselor(firstname, lastname, address, city, state, zip, phone, email, DegreeSuffix)
values('Phoebe','Wright','707 Old Locke Lane','Richmond','VA','23226','(804) 592-0056','pwright@raftcounseling.com', 'MFT');
/*data for Clients*/
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut , reminderMethod)
values('Randall','Mason','3513 A Grove Ave','Richmond','VA','23221','(804) 612-2961','masonjar27@msn.com', 'Radio','Phone');
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut , reminderMethod)
values('Leola','White','1625 W Laburnum Ave','Richmond','VA','23227','(804) 786-0698','thewhitehouse@yahoo.com', 'Billboard','Text');
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod, referredBy)
values('Tyrone','McSweeney','5717 Campbell Ave','Richmond','VA','23231','(804) 518-7098','Janessa1820@gmail.com', 'Family','Text',
(select clientID from client where firstName = 'Leola')
);
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod)
values('Shannon','Fake','3019 E Marshall St','Richmond','VA','23223','(804) 223-8622','therealshannon@gmail.com', 'Billboard','Email');
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod, referredBy)
values('Yasini','Brothers','205 Wickham St','Richmond','VA','23222','(804) 966-8112','brothersk@me.com', 'Family','Text',
(select clientID from client where firstName = 'Shannon')
);
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod, referredBy)
values('Wendi','Overton','915 N 33rd St','Richmond','VA','23223','(804) 649-6464','wendycity99@gmail.com', 'Family','Text',
(select clientID from client where firstName = 'Shannon')
);
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod, referredBy)
values('Bessie','Murchison','2918 Edgewood Ave','Richmond','VA','23222','(804) 620-9185','bmurch77@yahoo.com', Null,'Postcard',
(select clientID from client where firstName = 'Shannon')
);
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod)
values('Bernice','Mason','3513 A Grove Ave','Richmond','VA','23221','(804) 612-2961','masonjar27@msn.com', 'Radio','Phone');
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod, referredBy)
values('Russ','Overton','915 N 33rd St','Richmond','VA','23223','(804) 649-6464','russ.overon.1982@gmail.com', 'Family','Text',
(select clientID from client where firstName = 'Wendi')
);
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod, referredBy)
values('Samantha','Murchison','2918 Edgewood Ave','Richmond','VA','23222','(804) 620-9185','samantha.murch.2002@yahoo.com', Null,'Postcard',
(select clientID from client where firstName = 'Bessie')
);
/*data for Employees*/
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage, supervisorId)
values('Holly','White','1414 Rogers St','Richmond','VA','23223','(804) 640-6817','hollyw@raftcounseling.com', TO_DATE('2013-07-01', 'yyyy-MM-dd'),'17.50', 300);
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage, supervisorId)
values('Mark','Lucas','1203 1/2 Oakwood Ave','Richmond','VA','23223','(804) 327-2454','markl@raftcounseling.com', TO_DATE('2013-09-01', 'yyyy-MM-dd'),'15.00', 300);
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage, supervisorId)
values('John','Taylor','913 N 3rd St','Richmond','VA','23219','(804) 331-2700','johnt@raftcounseling.com', TO_DATE('2020-08-01', 'yyyy-MM-dd'),'15.00', 301);
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage, supervisorId)
values('Molly','Helgeson','900 Maggie Walker Ave','Richmond','VA','23222','(804) 888-0759','mollyh@raftcounseling.com', TO_DATE('2013-06-01', 'yyyy-MM-dd'),'16.80', 301);
/*data for Rooms*/
insert into room(roomnumber, roomName) values(1,'Clear sky');
insert into room(roomnumber, roomName) values(2,'Flowing waters');
insert into room(roomnumber, roomName) values(3,'Calm ocean');
insert into room(roomnumber, roomName) values(4,'Peaceful woods');
/*data for Appointments*/
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 8:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Clear sky')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Wendi'),
'Write a letter to Russ listing the things that you are glad he does for the family.',
1
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Russ'),
'Write a letter to Wendi explaining how you first fell in love with her.',
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 11:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Clear sky')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Bessie'),
'Come prepared to explain how you felt the day Samantha was born.',
1
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Samantha'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselor where firstname='Quinn'),
(select employeeid from employee where firstname='John'),
(select roomnumber from room where roomname='Flowing waters')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Leola'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Calm ocean')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Shannon'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select counselorid from counselor where firstname='Darold'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Peaceful woods')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Bernice'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Calm ocean')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Shannon'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselor where firstname='Quinn'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Flowing waters')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Leola'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select counselorid from counselor where firstname='Darold'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Peaceful woods')
);
insert into ScheduledFor values(
(select max(appointmentId) from appointment),
(select clientid from client where firstname='Bernice'),
null,
1
);
/* show data */
select * from room;
select * from client;
select * from employee;
select * from counselor;
select * from appointment;
select * from scheduledFor;
Labels: hide