RAFT 2 Script Start
-- thses statements may fail the first time you run this script becuase the tables do not yet exist
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)
);
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)
);
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,
ClientID int references client not null,
CounselorID int references counselor not null,
EmployeeID int references employee not null,
RoomNumber int references room not null,
Notes varchar(3000)
);
/*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)
values('Tyrone','McSweeney','5717 Campbell Ave','Richmond','VA','23231','(804) 518-7098','Janessa1820@gmail.com', 'Family','Text');
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)
values('Yasini','Brothers','205 Wickham St','Richmond','VA','23222','(804) 966-8112','brothersk@me.com', 'Family','Text');
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod)
values('Wendi','Overton','915 N 33rd St','Richmond','VA','23223','(804) 649-6464','wendycity99@gmail.com', 'Family','Text');
insert into Client(firstname, lastname, address, city, state, zip, phone, email, foundOut, reminderMethod)
values('Bessie','Murchison','2918 Edgewood Ave','Richmond','VA','23222','(804) 620-9185','bmurch77@yahoo.com', Null,'Postcard');
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');
/*data for Employees*/
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage)
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');
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage)
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');
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage)
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');
insert into Employee (firstname, lastname, address, city, state, zip, phone, email, DateHired, Wage)
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');
/*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, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 8:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select clientid from client where firstname='Wendi'),
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Clear sky')
);
insert into appointment(AppointmentTime,duration, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 11:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select clientid from client where firstname='Bessie'),
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Clear sky')
);
insert into appointment(AppointmentTime,duration, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select clientid from client where firstname='Leola'),
(select counselorid from counselor where firstname='Quinn'),
(select employeeid from employee where firstname='John'),
(select roomnumber from room where roomname='Flowing waters')
);
insert into appointment(AppointmentTime,duration, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select clientid from client where firstname='Shannon'),
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Calm ocean')
);
insert into appointment(AppointmentTime,duration, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select clientid from client where firstname='Bernice'),
(select counselorid from counselor where firstname='Darold'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Peaceful woods')
);
insert into appointment(AppointmentTime,duration, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select clientid from client where firstname='Shannon'),
(select counselorid from counselor where firstname='Elizabeth'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Calm ocean')
);
insert into appointment(AppointmentTime,duration, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select clientid from client where firstname='Leola'),
(select counselorid from counselor where firstname='Quinn'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Flowing waters')
);
insert into appointment(AppointmentTime,duration, clientId, counselorid, employeeid, roomnumber)
values(TO_DATE('2024-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select clientid from client where firstname='Bernice'),
(select counselorid from counselor where firstname='Darold'),
(select employeeid from employee where firstname='Holly'),
(select roomnumber from room where roomname='Peaceful woods')
);
/* show data */
select * from room;
select * from client;
select * from employee;
select * from counselor;
select * from appointment;
Labels: hidden