ixlo/db-samples
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
db-samples/vets/vets_db_setup.sql
Dia Pacifica a07a5a15de initial commit
2024-10-01 18:46:15 -07:00

607 lines
27 KiB
SQL
Raw Permalink Blame History

-- Setup
-- To set up this database run the following command:
-- sudo mysql < altgeld_setup.sql
drop database vets;
create database vets;
GRANT SELECT, INSERT, UPDATE, CREATE, ALTER, DELETE, DROP, REFERENCES, CREATE VIEW
ON vets.*
TO 'felicia'@'localhost';
delimiter ;
use vets;
-- Creates
-- remove any prior version of tables
drop table if exists vets.vt_exam_details;
drop table if exists vets.vt_exam_headers;
drop table if exists vets.vt_animals;
drop table if exists vets.vt_clients;
drop table if exists vets.vt_animal_types;
drop table if exists vets.vt_services;
drop table if exists vets.vt_staff_pay;
drop table if exists vets.vt_staff;
-- MySQL will not enforce the check constraints
-- they are included here to indicate what the values should be
create table vets.vt_staff (
stf_id int unsigned not null
, stf_name_last varchar(25) not null
, stf_name_first varchar(25) not null
, stf_job_title varchar(25) not null
, constraint vt_staff_pk primary key (stf_id)
, constraint stf_id_range check (stf_id > 0)
, constraint job_title_values check
( stf_job_title in ('vet', 'vet assnt', 'clerical', 'kennel'))
)engine = INNODB;
create table vets.vt_staff_pay (
stf_id int unsigned not null
, stf_ssn char(9) not null
, stf_salary numeric(8,2) unsigned not null
, stf_hire_date date not null
, constraint vt_staff_pay_pk primary key(stf_id)
, constraint stf_hire_date_range check (stf_hire_date >= date '1995-01-01')
, constraint vt_staff_pay_staff_fk foreign key(stf_id) references vets.vt_staff(stf_id)
, constraint stf_salary_range check (stf_salary >= 0)
, constraint stf_id2_range check (stf_id > 0)
, constraint stf_ssn_un unique (stf_ssn)
)engine = INNODB;
create table vets.vt_services(
srv_id int unsigned not null
, srv_list_price numeric(6,2) unsigned not null
, srv_desc varchar(50) not null unique
, srv_type varchar(25) not null
, constraint vt_services_pk primary key (srv_id)
, constraint srv_type_ck check (srv_type in
('office visit', 'consult', 'medicine', 'treatment'))
)engine = INNODB;
create table vets.vt_animal_types(
an_type varchar(25) not null
, constraint an_types_pk primary key (an_type)
)engine = INNODB;
create table vets.vt_clients(
cl_id int unsigned not null
, cl_name_last varchar(25) not null
, cl_name_first varchar(25) null
, cl_address varchar(25) not null
, cl_city varchar(25) not null
, cl_state char(2) not null
, cl_postal_code varchar(12) not null
, cl_phone varchar(12) null
, constraint vt_clients_pk primary key (cl_id)
, constraint cl_id_range check (cl_id > 100)
)engine = INNODB;
create table vets.vt_animals(
an_id int unsigned not null
, an_type varchar(25) not null
, an_name varchar(25) null
, an_dob date not null
, cl_id int unsigned not null
, constraint vt_animals_pk primary key (an_id)
, constraint vt_animals_animal_types_fk foreign key (an_type) references vets.vt_animal_types (an_type)
, constraint vt_animals_clients_fk foreign key (cl_id) references vets.vt_clients (cl_id)
, constraint an_id_range check (an_id > 0)
)engine = INNODB;
create table vets.vt_exam_headers(
ex_id int unsigned not null
, an_id int unsigned not null
, stf_id int unsigned not null
, ex_date datetime not null
, constraint vt_exam_headers_pk primary key (ex_id)
, constraint vt_exam_headers_animals_fk foreign key (an_id) references vets.vt_animals (an_id)
, constraint vt_exam_headers_staff_fk foreign key (stf_id) references vets.vt_staff (stf_id)
, constraint exam_id_range check (ex_id > 0)
, constraint exam_date_range check (ex_date >= date '2010-01-01')
)engine = INNODB;
create table vets.vt_exam_details(
ex_id int unsigned not null
, line_item int unsigned not null
, srv_id int unsigned not null
, ex_fee numeric(6,2) unsigned not null
, ex_desc varchar(50) not null
, constraint evt_exam_details_pk primary key (ex_id, line_item )
, constraint evt_exam_details_headers_fk foreign key (ex_id) references vets.vt_exam_headers (ex_id)
, constraint evt_exam_details_services_fk foreign key (srv_id) references vets.vt_services (srv_id)
, constraint line_item_range check (line_item > 0)
, constraint ex_fee_range check (ex_fee >= 0)
)engine = INNODB;
-- Inserts
-- clear earlier versions
delete from vt_exam_details;
delete from vt_exam_headers;
delete from vt_animals;
delete from vt_clients;
delete from vt_animal_types;
delete from vt_services;
delete from vt_staff_pay;
delete from vt_staff;
-- staff inserts
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (30, 'Wasilewski', 'Marcin', 'vet');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (15, 'Dolittle', 'Eliza', 'vet');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (20, 'Horn', 'Shirley', 'clerical');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (25, 'Wilkommen', 'Bridgette', 'vet');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (43, 'Halvers', 'Pat', 'kennel');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (29, 'Helfen', 'Sandy', 'vet assnt');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (37, 'Webster', 'Brenda', 'vet assnt');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (38, 'Wabich', 'Rhoda', 'vet');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (52, 'Gordon', 'Dexter', 'vet assnt');
insert into vt_staff (stf_id, stf_name_last, stf_name_first, stf_job_title)
values (55, 'Helfen', 'Sandy', 'vet assnt');
-- staff pay inserts
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (30, '323445996',99000, '1995-01-06');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (15, '123456789', 40000, '2005-06-01');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (20, '398678765', 35000, '2005-08-01');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (25, '876582345',65000, '2011-06-01');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (43, '325771545', 15500, '2011-08-12');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (37, '323458945', 20000, '2013-01-13');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (38, '322588945', 20000, '2013-01-13');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (52, '325778945', 20000, '2014-08-12');
insert into vt_staff_pay (stf_id, stf_ssn, stf_salary, stf_hire_date)
values (55, '323438454', 37500, '2014-09-26');
-- services inserts
insert into vt_services (srv_id, srv_list_price, srv_desc, srv_type)
values
(101, 50.00, 'Dental Cleaning-Canine', 'treatment')
, (105, 80.00, 'Routine Exam-Canine', 'office visit')
, (110, 100.00, 'Dental Cleaning-Other', 'treatment')
, (225, 75.00, 'Feline PCR Series', 'medicine')
, (102, 45.00, 'Dental Cleaning-Feline', 'treatment')
, (398, 35.00, 'Followup Exam-Canine', 'office visit')
, (400, 10.50, 'Hazardous Materials Disposal', 'treatment')
, (106, 75.00, 'Routine Exam-Bird', 'treatment')
, (306, 32.00, 'Followup Exam-Bird', 'office visit')
, (108, 80.00, 'Routine Exam-Feline', 'office visit')
, (308, 45.00, 'Followup Exam-Feline', 'office visit')
, (104, 60.00, 'Routine Exam-Reptile', 'office visit')
, (341, 25.00, 'Followup Exam-Reptile', 'office visit')
, (523, 60.00, 'Routine Exam-Small Mammal', 'office visit')
, (524, 25.00, 'Followup Exam-Small Mammal', 'office visit')
, (551, 35.50, 'First Feline PCR', 'medicine')
, (552, 25.15, 'Second Feline PCR', 'medicine')
, (553, 25.85, 'Third Feline PCR', 'medicine')
, (112, 25.33, 'Scaly Mite Powder', 'medicine')
, (113, 26.00, 'Intestinal Parasite Screen', 'treatment')
;
insert into vt_services (srv_id, srv_list_price, srv_desc, srv_type)
values
(461, 275.00, 'Feline Behaviour Modification Consultation', 'treatment')
, (687, 45.99, 'Vitamin E- Concentrated', 'medicine')
, (748, 29.50, 'Preds-liver', 'medicine')
, (749, 29.50, 'Preds-chicken', 'medicine')
, (602, 222.00, 'General Anaesthesia 1 hour', 'treatment')
, (603, 78.00, 'Feline Dental X_ray', 'treatment')
, (604, 109.00, 'Dental Scaling', 'treatment')
, (605, 535.00, 'Tooth extraction Level 1', 'treatment')
, (606, 40.00, 'Tooth extraction Level 2', 'treatment')
, (607, 57.00, 'Tooth extraction Level 3', 'treatment')
, (612, 25.91, 'Buprenex oral drops 1 ml', 'medicine')
, (613, 47.00, 'Hospital stay- short', 'treatment')
, (615, 78.30, 'Antibiotics', 'medicine')
, (625, 155.00, 'CBC and scan', 'treatment')
;
-- animal type inserts
insert into vt_animal_types( an_type)
Values
('bird')
, ('porcupine' )
, ('capybara')
, ('cat')
, ('crocodilian')
, ('dog')
, ('dormouse' )
, ('hamster')
, ('hedgehog')
, ('lizard')
, ('olinguito')
, ('snake')
, ('turtle')
;
-- clients inserts
insert into vt_clients (cl_id, cl_name_last, cl_name_first, cl_address, cl_city, cl_state, cl_postal_code, cl_phone)
values
(254, 'Boston', 'Edger', 'POB 2', 'Boston', 'MA', 21555, null)
, (411, 'Carter', 'James', '2 Marshall Ave', 'Big Rock', 'AR', '71601', '510.258.4546')
, (1825, 'Harris', 'Eddie', '2 Marshall Ave', 'Big Rock', 'AR', '71601', null)
, (1852, 'Dalrymple', 'Jack', '2 Marshall Ave', 'Big Rock', 'ND', '58503', '701.328.2725')
, (3423, 'Hawkins', 'Coleman', '23 Ruby Lane', 'Springfield', 'OH', '45502', '937.258.5645')
, (3560, 'Monk', 'Theo', '345 Post Street', 'New York', 'NY', '10006', '212.582.6245')
, (3561, 'Pickett', 'Wilson', 'POB 397', 'New York', 'NY', '10006', '212.584.9871')
, (4087, 'Turrentine', 'Stanley', '2920 Zoo Drive', 'San Diego', 'CA', '92101', '619.231.1515')
, (4534, 'Montgomery', 'Wes', 'POB 345', 'Dayton', 'OH', '45402', null )
, (5686, 'Biederbecke', null, '50 Phelan Ave', 'Springfield', 'IL', '62701', '217.239.6945')
, (5689, 'Biederbecke', null, '50 Phelan Ave', 'San Francisco', 'CA', '94112', '415.239.6945')
, (5698, 'Biederbecke', 'Sue', '50 Phelan Ave', 'Springfield', 'IL', '62701', '217.239.6875')
, (5699, 'Biederbecke', 'Sam', '549 Market Ave', 'San Francisco', 'CA', '94101', '415.239.6875')
, (6426, 'Hawkins', 'Coleman', '26 Ruby Lane', 'Springfield', 'OH', '45502', null)
, (6897, 'Drake', 'Donald', '50 Phelan Ave', 'Springfield', 'MO', '65802', null)
, (7152, 'Brubeck', 'Dave', '50 Green St', 'Spring Valley', 'MA', '21579', '258.257.2727')
, (7212, 'Davis', 'Donald', '124 Fifth', 'San Francisco', 'NM', '87801', null)
;
-- animal inserts
insert into vt_animals ( an_id, cl_id, an_name, an_type, an_dob)
values
(10002, 3560, 'Gutsy', 'cat', '2010-04-15' )
, (11015, 3560, 'Kenny', 'bird', '2012-02-23' )
, (11025, 3560, null, 'bird', '2012-02-01' )
, (11028, 3560, null, 'bird', '2015-10-01' )
, (11029, 3560, null, 'bird', '2015-10-01' )
, (12035, 3560, 'Mr Peanut', 'bird', '1995-02-28' )
, (12038, 3560, 'Gutsy', 'porcupine', '2012-04-29' )
, (15001, 3561, 'Big Mike', 'turtle', '2010-02-02' )
, (15002, 5699, 'George', 'turtle', '1998-02-02' )
, (15165, 411, 'Burgess', 'dog', '2005-11-20' )
, (15401, 411, 'Pinkie', 'lizard', '2010-03-15' )
, (16002, 3423, 'Fritz', 'porcupine', '2015-05-25' )
, (16003, 1825, 'Ursula', 'cat', '2013-02-06' )
, (16043, 1825, 'Ursula', 'dog', '2014-06-06' )
, (16044, 1825, 'Triton', 'dog', '2014-06-06' )
, (17002, 5699, 'Fritzchen', 'porcupine', '2012-06-02' )
, (17025, 3561, '25', 'lizard', '2013-01-10' )
, (17026, 7152, '3P#_26', 'lizard', '2010-01-10' )
, (17027, 7152, '3P#_25', 'lizard', '2010-01-10' )
, (19845, 6426, 'Pinkie', 'dog', '2009-02-02' )
, (21001, 411, 'Yoggie', 'hedgehog', '2009-05-22' )
, (21002, 1825, 'Edger', 'hedgehog', '2002-10-02' )
, (21003, 6426, 'Calvin Coolidge', 'dog', '2014-06-18' )
, (21004, 3561, 'Gutsy', 'snake', '2011-05-12' )
, (21005, 3423, 'Koshka', 'dormouse', '2011-03-06' )
, (21006, 3423, 'Koshka', 'hamster', '2011-06-06' )
, (21007, 1852, 'Pop 22', 'snake', '2010-06-12' )
, (21205, 5689, 'Manfried', 'dog', '2015-03-30' )
, (21305, 4087, 'Spot', 'dog', '2014-07-27' )
, (21306, 4087, 'Shadow', 'dog', '2014-07-27' )
, (21307, 4087, 'Buddy', 'dog', '2014-07-27' )
, (21314, 4087, 'Adalwine', 'cat', '2013-06-11' )
, (21315, 4534, 'Baldric', 'cat', '2013-06-11' )
, (21316, 5698, 'Etta', 'cat', '2010-06-11' )
, (21317, 5698, 'Manfried', 'cat', '2011-06-11' )
, (21318, 5698, 'Waldrom', 'cat', '2012-06-11' )
, (21320, 1852, 'Morris', 'olinguito', '2014-06-11' )
, (21321, 1852, 'Morton', 'olinguito', '2014-06-03' )
;
-- exam inserts
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2228, 21306, 38, '2015-04-04 12:30:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2228, 1, 398, 30.00, 'Follow-up exam' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2205, 21307, 38, '2015-04-08 10:30:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2205, 1, 105, 75.00, 'Routine exam' )
, (2205, 2, 101, 50.00, 'Dental' )
, (2205, 3, 602, 200.00, 'Anaesthesia' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2289, 21320, 38, '2015-04-11 13:00:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2289, 1, 523, 275.00, 'Routine exam' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2290, 21320, 38, '2015-04-11 17:00:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2290, 1, 524, 275.00, 'Follow-up exam' )
, (2290, 2, 613, 41.00, 'Overnight observation' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2300, 21316, 38, '2015-05-08 09:15:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2300, 1, 108, 75.00, 'Routine exam' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2352, 10002, 38, '2015-05-12 09:15:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2352, 1, 108, 75.00, 'Routine exam' )
, (2352, 2, 615, 75.00, 'Antibiotics' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2389, 21006, 38, '2015-05-20 09:45:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2389, 1, 523, 60.00, 'Initial exam' )
, (2389, 5, 110, 50.00, 'Dental');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (2400, 12038, 38, '2015-06-02 13:00:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (2400, 1, 461, 275.00, 'Aggressive behaviour' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3002, 11028, 38, '2015-10-02 13:00:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3002, 1, 106, 60.00, 'Initial exam' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3003, 11029, 38, '2015-10-02 13:00:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3003, 1, 106, 60.00, 'Initial exam' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4243, 16002, 38, '2015-06-08 15:30:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4243, 1, 461, 275.00, 'Aggressive behaviour' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4255, 16002, 38, '2015-07-08 15:30:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4255, 1, 461, 275.00, 'Aggressive behaviour' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4612, 21317, 15, '2015-07-23 08:30:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4612, 1, 602, 222.00, 'Dental anaesthesia' )
, (4612, 2, 603, 78.00, 'Dental' )
, (4612, 3, 606, 40.00, 'Dental tooth 1' )
, (4612, 4, 612, 25.91, 'As needed at home' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4514, 15002, 29, '2015-08-10 10:45:00' );
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4514, 1, 104, 30.00, 'Routine exam' )
, (4514, 2, 112, 15.00, 'Mite infection' )
, (4514, 3, 113, 25.00, 'Parasite screen' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4203, 16002, 29, '2015-08-03 14:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4203, 1, 524, 20.00, 'Follow-up on parasites');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4282, 21001, 15, '2015-08-23 10:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4282, 1, 523, 60.00, 'Yearly checkup')
, (4282, 2, 110, 50.00, 'Minor buildup')
, (4282, 3, 615, 25.25, 'Antibiotic half dosage');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4233, 16002, 29, '2015-09-03 14:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4233, 1, 524, 20.00, 'Follow-up on parasites');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3105, 17002, 29, '2015-10-10 9:15:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3105, 1, 523, 50.50, 'Checkup')
, (3105, 2, 110, 150.00, 'Dental');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3010, 17026, 29, '2015-10-22 10:45:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3010, 1, 104, 25.00, 'Routine exam')
, (3010, 2, 605, 535.00, 'Tooth extraction');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3001, 17027, 29, '2015-10-24 10:45:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3001, 1, 104, 25.00, 'Routine exam');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3202, 17025, 29, '2015-10-03 14:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3202, 1, 341, 20.00, 'Follow-up on parasites');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3513, 15401, 15, '2015-11-06 10:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3513, 1, 104, 30.00, 'Yearly checkup')
, (3513, 2, 110, 75.00, 'Repeat treatment');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3552, 16003, 15, '2015-11-10 10:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3552, 1, 308, 2.25, 'Dental follow-up- phone')
, (3552, 3, 615, 25.25, 'Antibiotic half dosage');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3304, 17027, 15, '2015-11-06 10:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3304, 1, 341, 25.00, 'Yearly checkup')
, (3304, 2, 113, 25.00, 'Test for repeat infection');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3306, 17025, 29, '2015-11-06 10:45:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3306, 1, 104, 30.00, 'Routine exam')
, (3306, 2, 687, 45.00, 'Liquid form')
, (3306, 3, 112, 25.00, 'Parasite external');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3390, 19845, 15, '2015-11-22 09:00:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3390, 1, 105, 55.00, 'Yearly checkup')
, (3390, 2, 101, 70.00, 'Major build-up');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3411, 17025, 29, '2015-12-29 14:00:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3411, 1, 341, 20.00, 'Follow-up on parasites');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3412, 17025, 29, '2015-12-30 14:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3412, 1, 341, 20.00, 'Follow-up on parasites');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3413, 16003, 15, '2015-12-01 16:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3413, 1, 308, 5.00, 'Follow up checkup');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3612, 16003, 15, '2015-12-23 08:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3612, 1, 602, 222.00, 'Dental anaesthesia')
, (3612, 2, 603, 78.00, 'Dental')
, (3612, 3, 604, 109.00, 'Dental- mild')
, (3612, 4, 625, 155.00, 'Dental -pre tests')
, (3612, 5, 606, 40.00, 'Dental tooth 1')
, (3612, 6, 607, 57.00, 'Dental tooth 2')
, (3612, 7, 612, 25.91, 'As needed at home')
, (3612, 8, 613, 47.00, 'Feline cage')
, (3612, 9, 615, 78.30, 'In-patient');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3392, 21003, 15, '2015-12-26 09:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3392, 1, 398, 30.00, 'Weight loss Follow up ')
, (3392, 2, 400, 21.00, 'Discard first sample')
, (3392, 3, 113, 45.00, 'Retest for parasites' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3393, 16002, 29, '2015-12-23 12:15:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3393, 1, 748, 29.50, 'Rat ulcer recurrence')
, (3393, 3, 749, 0.00, 'Rat ulcer recurrence');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3409, 17027, 29, '2015-12-27 10:45:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3409, 1, 104, 25.00, 'Routine exam');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3486, 21005, 15, '2015-12-31 13:00:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3486, 1, 461, 275.00, 'Aggressive behaviour' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4101, 15001, 15, '2016-01-02 13:00:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4101, 1, 104, 60.00, 'Routine exam' )
, (4101, 2, 615, 75.00, 'Possible infection' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4102, 15002, 15, '2016-01-08 13:00:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4102, 1, 104, 60.00, 'Routine exam' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (4103, 16002, 38, '2016-01-08 15:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (4103, 1, 461, 275.00, 'Aggressive behaviour' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3104, 12035, 38, '2016-01-09 16:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3104, 1, 106, 75, 'Moult' )
, (3104, 2, 613, 47, 'Confine for the first molt' );
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3325, 17026, 29, '2016-01-15 10:45:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3325, 1, 104, 25.00, 'Routine exam')
, (3325, 2, 604, 59.00, 'Dental');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3420, 16003, 15, '2016-01-01 16:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3420, 1, 108, 80.00, 'Yearly checkup');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3494, 15001, 25, '2016-01-22 09:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3494, 1, 104, 30, 'Follow-up checkup');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3288, 15001, 25, '2016-01-31 09:00:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3288, 1, 104, 30, 'Checkup')
, (3288, 2, 112, 25, 'Mite infestation')
, (3288, 3, 687, 45, 'Shell softening');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3322, 16002, 29, '2016-02-10 9:15:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3322, 1, 748, 29.50, 'Rat ulcer recurrence');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3321, 16002, 29, '2016-02-17 10:45:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3321, 1, 748, 0.00, 'Rat ulcer recurrence');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3324, 17025, 29, '2016-02-25 10:45:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3324, 1, 104, 30.00, 'Routine exam')
, (3324, 2, 687, 45.00, 'Liquid form')
, (3324, 3, 112, 25.00, 'Parasite external');
insert into vt_exam_headers(ex_id, an_id, stf_id, ex_date)
values (3323, 16002, 29, '2016-02-25 14:30:00');
insert into vt_exam_details(ex_id, line_item, srv_id, ex_fee, ex_desc)
values (3323, 1, 524, 20.00, 'Follow-up on parasites')
, (3323, 3, 687, 45.00, 'Follow-up on parasites');
-- End
Reference in a new issue View git blame Copy permalink