SQL script for Australia Zoo Wildlife Hospital database creation and data insertion

This SQL script contains the code for creating tables and inserting data into the Australia Zoo Wildlife Hospital database. The tables include BREAD_TYPE, AFFICITION, ANIMAL_INFO, PATIENT_INFO, PATIENT_DNA, RESCUER_INFO, CONTACTS, CARER_GROUP, CARER_INFO, TREATMENT, and ACCESSION.

/*--------------------------CREATE DATABASE -----------------------*/
CREATE DATABASE AustraliaZooWildlifeHospital;
/*---------------------------CREATE TABLES------------------------*/
/*----------------------BRAED_TYPE TABLES------------------------*/
CREATE TABLE BREAD_TYPE
(
BREAD_ID INT(10) NOT NULL,
BREAD_TYPE VARCHAR(150),
BREAD_NAME VARCHAR(150),
PRIMARY KEY (BREAD_ID)
)
;
/*-------------------AFFICITION TABLES------------------------*/
CREATE TABLE AFFICITION
(
AFFICITION_NO INT(10) NOT NULL,
AFFICITION_NAME VARCHAR(150),
PRIMARY KEY (AFFICITION_NO)
)
;
/*-------------------ANIMAL_INFO TABLES------------------------*/

CREATE TABLE ANIMAL_INFO
(
ANIMAL_ID VARCHAR(10) NOT NULL,
ANIMAL_TYPE VARCHAR(50) NOT NULL,
ANIMAL_NAME VARCHAR(50) NOT NULL,
RESCUER_LOCATION VARCHAR(50),
SUBURB VARCHAR(50),
LOCATION_GOV_AREA_SHIRE VARCHAR(50) NOT NULL,
ANIMAL_SITUATION VARCHAR(100),
HAPPENED VARCHAR(100),
AFFICITION_NO INT(10) NOT NULL,
FED VARCHAR(50),
MEDICATED VARCHAR(50) NOT NULL,
HOW_LONG_AGO VARCHAR(20),
POSTCODE INT(10) NOT NULL,
PRIMARY KEY (ANIMAL_ID),
FOREIGN KEY (AFFICITION_NO) REFERENCES AFFICITION(AFFICITION_NO)
)
;
/*-----------------------PATIENT_INFO TABLES------------------------*/
CREATE TABLE PATIENT_INFO
(
PATIENT_ID VARCHAR(10) NOT NULL,
ANIMAL_ID VARCHAR(10) NOT NULL,

BREAD_ID INT(10) NOT NULL,
KOALATAG VARCHAR (10),
MICROSHIP VARCHAR (10),
LEFTEARTAGE VARCHAR(10),
RIGHTEARTAG VARCHAR(10),
TURTLESTAG VARCHAR(10),
IDENTIFICATION VARCHAR (255),
PRIMARY KEY (PATIENT_ID),
FOREIGN KEY (ANIMAL_ID) REFERENCES ANIMAL_INFO(ANIMAL_ID),
FOREIGN KEY (BREAD_ID) REFERENCES BREAD_TYPE(BREAD_ID)
)
;
/*--------------------------PATIENT_DNA TABLES------------------------*/
CREATE TABLE PATIENT_DNA
(
DNA_NO VARCHAR(10) NOT NULL,
PATIENT_ID VARCHAR(10) NOT NULL,
DATETIMETAKEN DATETIME,
SYSTEMENTERDATETIME DATETIME,
RESULT VARCHAR(100),
PRIMARY KEY (DNA_NO),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT_INFO(PATIENT_ID)
)
;

/*---------------------RESCUER_INFO TABLES------------------------*/
CREATE TABLE RESCUER_INFO
(
RESCUER_ID VARCHAR(10) NOT NULL,
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50),
ADDRESS VARCHAR(200) NOT NULL,
PHONE VARCHAR(20),
MOBILE VARCHAR(20) NOT NULL,
EMAIL VARCHAR(20),
DATE DATE ,
PRIMARY KEY (RESCUER_ID)
)
;
/*------------------CONTACTS TABLES------------------------*/
CREATE TABLE CONTACTS
(
S_NO INT(10) NOT NULL,
CONTACT_TYPE VARCHAR(100) NOT NULL,
FIRSTNAME VARCHAR(50) NOT NULL,
LASTNAME VARCHAR(50),
TITLE VARCHAR(100) NOT NULL,
EMAIL VARCHAR(50),

PHONE VARCHAR(20) NOT NULL,
STREET VARCHAR(20) NOT NULL,
ADDRESS VARCHAR(200) NOT NULL,
SUBURB VARCHAR(20) NOT NULL,
STATE VARCHAR(10) NOT NULL,
COUNTRY VARCHAR(20) NOT NULL,
POSTCODE INT(10) NOT NULL,
PRIMARY KEY (S_NO)
)
;
/*-----------------CARER GROUP TABLES------------------------*/
CREATE TABLE CARER_GROUP
(
PERMIT_NUMBER INT NOT NULL,
CARERGROUP_NAME VARCHAR(100),
PERMIT_VALID VARCHAR(20),
PRIMARY KEY (PERMIT_NUMBER)
)
;
/*-------------------CARER_INFO TABLES------------------------*/
CREATE TABLE CARER_INFO
(
CARER_ID VARCHAR(10) NOT NULL,

PERMIT_NUMBER INT NOT NULL,
CARRER_NAME VARCHAR(50) NOT NULL,
RELEASE_ANIMAL VARCHAR(50),
REG_WILDLIFECARER VARCHAR(100),
BECOME_WILDLIDECARER VARCHAR(100),
SPECIES VARCHAR(100),
SEX VARCHAR(10),
WEIGHT VARCHAR(10),
AGE VARCHAR(10),
SEND_TO_CARER VARCHAR(10),
DATE DATE,
CARER VARCHAR(10),
TRIAGE_NOTES VARCHAR(255),
TRIAGE_NURSES VARCHAR(100),
PRIMARY KEY (CARER_ID),
FOREIGN KEY (PERMIT_NUMBER) REFERENCES CARER_GROUP(PERMIT_NUMBER)
)
;
/*------------------TREATMENT TABLES------------------------*/
CREATE TABLE TREATMENT
(
TREATMENT_NO INT NOT NULL,

PATIENT_ID VARCHAR(10) NOT NULL,
DIAGNOSIS_TYPE VARCHAR(100),
DIAGNOSIS_NAME VARCHAR(100),
NOTES VARCHAR(255),
ACTION_TAKEN VARCHAR(100),
START_DATE DATE,
STOP_DATE DATE,
PRESCRIBED_DATE DATE,
PRIMARY KEY (TREATMENT_NO),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT_INFO(PATIENT_ID)
)
;
/*----------------------ACCESSION TABLES------------------------*/
CREATE TABLE ACCESSION
(
ACCESSION_NO INT NOT NULL,
PATIENT_ID VARCHAR(10) NOT NULL,
RESCUER_ID VARCHAR(10) NOT NULL,
CARER_ID VARCHAR(10) NOT NULL,
S_NO INT NOT NULL,
PRIMARY KEY (ACCESSION_NO),
FOREIGN KEY (PATIENT_ID) REFERENCES PATIENT_INFO(PATIENT_ID),
FOREIGN KEY(RESCUER_ID) REFERENCES RESCUER_INFO(RESCUER_ID),
FOREIGN KEY (CARER_ID) REFERENCES CARER_INFO(CARER_ID),

FOREIGN KEY (S_NO) REFERENCES CONTACTS(S_NO)
)
;
/*------------------UPDATE_CONTACT TABLES------------------------*/
CREATE TABLE UPDATE_CONTACT
(
LIST_NO INT NOT NULL,
DATE DATE NOT NULL,
ACCESSION_NO INT NOT NULL,
VET VARCHAR(100),
UPDATE_OUTCOME VARCHAR(100),
UNDER_VET_CARER VARCHAR(20),
WITH_CARER VARCHAR(20),
RELEASED VARCHAR(20),
OTHER VARCHAR(100),
PRIMARY KEY (LIST_NO, DATE),
FOREIGN KEY (ACCESSION_NO) REFERENCES ACCESSION(ACCESSION_NO)
)
;
/*-------------------------------------------------------INSERT DATA INTO
TABLES-----------------------------------------------*/

/*----------------------BREAD_TYPE TABLES-----------------------------------------------*/
INSERT INTO BREAD_TYPE VALUES (101, 'Reptile - Snake - Terrestrial', '(Blind Snake) Ramphotyphlops
sp.');
INSERT INTO BREAD_TYPE VALUES (102, 'Avian - Domestic/Feral/Exotic', 'African Grey Parrot');
INSERT INTO BREAD_TYPE VALUES (103, 'Marsupial - Macropod', 'Agile Wallaby');
INSERT INTO BREAD_TYPE VALUES (104, 'Avian - Domestic/Feral/Exotic', 'Alexandrine Parrot');
INSERT INTO BREAD_TYPE VALUES (105, 'Reptile - Lizard', 'Anomalopus Verreauxii');
/*---------------------------AFFICITION TABLES------------------------*/
INSERT INTO AFFICITION VALUES( 1, 'Adhesive/Sticky substance');
INSERT INTO AFFICITION VALUES( 3, 'Boat Strike');
INSERT INTO AFFICITION VALUES( 5, 'Cat Attack');
INSERT INTO AFFICITION VALUES( 7, 'Dog Attack');
INSERT INTO AFFICITION VALUES( 9, 'Drowning');
/*---------------------- ANIMAL_INFO TABLES------------------------*/
INSERT INTO ANIMAL_INFO VALUES ('87157', 'wallabies', 'VELI', 'US', 'US', 'LOCATION GOV
AREA','BAD','EAT SOMETHING',1,'YES','YES','1 HOUR',1234);
INSERT INTO ANIMAL_INFO VALUES ('89415', 'wallabies', 'MILI', 'US', 'US', 'SHIRE','BAD','DONT
KNOW',5,'YES','YES','10 HOUR',9807);
INSERT INTO ANIMAL_INFO VALUES ('89836', 'kangaroos', 'TIRA', 'US', 'US', 'LOCATION GOV
AREA','BAD','TRY TO KILL',3,'YES','YES','2 HOUR',7898);
INSERT INTO ANIMAL_INFO VALUES ('89927', 'kangaroos', 'VLIYA', 'US', 'US', 'LOCATION GOV
AREA','BAD','EAT SOMETHING',7,'YES','YES','3 HOUR',4567);
INSERT INTO ANIMAL_INFO VALUES ('89928', 'possums', 'YENI', 'US', 'US', 'NO','SHIRE','EAT
SOMETHING',9,'YES','YES','2 HOUR',0967);

/*------------------------------PATIENT TABLES------------------------*/
INSERT INTO PATIENT_INFO VALUES ('P101', '87157', 101, 'K101', '87989','L101', 'R101', 'T101', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P102', '89415', 102, 'K102', '89890','L102', 'R102', 'T102', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P103', '89836', 103, 'K103', '45678','L103', 'R103', 'T103', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P104', '89927', 104, 'K104', '12345','L104', 'R104', 'T104', 'TAGES');
INSERT INTO PATIENT_INFO VALUES ('P105', '89928', 105, 'K105', '98978','L105', 'R105', 'T105', 'TAGES');
/*-----------------------------PATIENT_DNA TABLES------------------------*/
INSERT INTO PATIENT_DNA VALUES ('D101', 'P101', '2017-02-01 03:59:05', '2017-02-02 03:59:05',
'MATCH');
INSERT INTO PATIENT_DNA VALUES ('D102', 'P102', '2017-02-02 03:59:05', '2017-02-03
03:59:05','MATCH');
INSERT INTO PATIENT_DNA VALUES ('D103', 'P103', '2017-02-03 03:59:05', '2017-02-04 03:59:05',
'MATCH');
INSERT INTO PATIENT_DNA VALUES ('D104', 'P104', '2017-02-04 03:59:05', '2017-02-05 03:59:05',
'MATCH');
INSERT INTO PATIENT_DNA VALUES ('D105', 'P105', '2017-02-05 03:59:05', '2017-02-06 03:59:05',
'MATCH');
/*----------------------------RESCUER_INFO TABLES------------------------*/
INSERT INTO RESCUER_INFO VALUES ('6033','Katherine', 'Latta', '22a Mary St', '07 1234 5678',
'7865456567', '[e-mail]', '2017-06-01');
INSERT INTO RESCUER_INFO VALUES ('5167','Jesse', 'Meldrum', '22a Mary St', '07 1234 5678',
'3567875777', '[e-mail]', '2017-06-02');
INSERT INTO RESCUER_INFO VALUES ('12901','Jesse', 'Burcher', '22a Mary St', '07 1234 5678',
'5756767878', '[e-mail]', '2017-06-03');
INSERT INTO RESCUER_INFO VALUES ('9217','Jesse', 'Hanger', '22a Mary St', '4675767678',
'7767676789', '[e-mail]', '2017-06-04');

INSERT INTO RESCUER_INFO VALUES ('12902','WANI', 'BINA', '22a Mary St', '6757566788', '5767767888',
'[e-mail]', '2017-06-05');
/*-----------------------------CONTACTS TABLES------------------------*/
INSERT INTO CONTACTS VALUES (1, 'PERSON', 'YANI', 'LINA', 'SOCIAL',
'[e-mail]','7898678979','67YHU','US','US','US','US',4567);
INSERT INTO CONTACTS VALUES (2, 'PERSON', 'VANI', 'LINA', 'SOCIAL',
'[e-mail]','6767787899','68UYU','US','US','US','US',8978);
INSERT INTO CONTACTS VALUES (3, 'PERSON', 'CANI', 'LINA', 'SOCIAL',
'[e-mail]','4565787899','56TGU','US','US','US','US',7654);
INSERT INTO CONTACTS VALUES (4, 'PERSON', 'NINA', 'LINA', 'SOCIAL',
'[e-mail]','5656676776','6JHHU','US','US','US','US',6789);
INSERT INTO CONTACTS VALUES (5, 'PERSON', 'YANI', 'LINA', 'SOCIAL',
'[e-mail]','5656656777','89UHU','US','US','US','US',0978);
/*-----------------------------CARER_GROUP TABLES------------------------*/
INSERT INTO CARER_GROUP VALUES (101, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (102, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (103, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (104, 'CR','VALID');
INSERT INTO CARER_GROUP VALUES (105, 'CR','VALID');
/*---------------------------- CARER_INFO TABLES------------------------*/
INSERT INTO CARER_INFO VALUES ('C101',101,'Jesse','YES', 'YES','YES','TAKECARE
ANIMAL','M','45','23','YES','2018-06-1','YES','UIH','IHJK');
INSERT INTO CARER_INFO VALUES ('C102',102,'Annabelle','NO', 'YES','YES','TAKECARE
ANIMAL','F','45','24','YES','2018-06-2','YES','MKI','HJNBI');
INSERT INTO CARER_INFO VALUES ('C103',103,'Katherine','YES', 'YES','YES','TAKECARE
ANIMAL','M','45','25','YES','2017-06-1','YES','BHI','IHJI');

INSERT INTO CARER_INFO VALUES ('C104',104,'John','NO', 'YES','YES','TAKECARE
ANIMAL','F','45','26','YES','2017-06-1','YES','NJI','IJJK');
INSERT INTO CARER_INFO VALUES ('C105',105,'MNI','YES', 'YES','YES','TAKECARE
ANIMAL','M','45','27','YES','2018-06-1','YES','BHI','IQJK');
/*-------------------------------TREATMENT TABLES-----------------------------------------------*/
INSERT INTO TREATMENT VALUES (1, 'P101','XYZ','NMU','DMTUFJ','UI','2017-10-10','2018-01-01','2017-
12-01');
INSERT INTO TREATMENT VALUES (2, 'P102','NJH','NJK','KGFZDFCGVH','UQ','2017-10-11','2018-01-
02','2017-12-02');
INSERT INTO TREATMENT VALUES (3, 'P103','BNJ','MN','SDFGHJ','UW','2017-10-12','2018-01-03','2017-
12-03');
INSERT INTO TREATMENT VALUES (4, 'P104','VGH','VB','KJHGFGJ','UE','2017-10-13','2018-01-04','2017-
12-04');
INSERT INTO TREATMENT VALUES (5, 'P105','NBV','CVBN','DFGHJHGJ','UR','2017-10-14','2018-01-
05','2017-12-05');
/*--------------------------ACCESSION TABLES------------------------*/
INSERT INTO ACCESSION VALUES (111,'P101','6033','C101',1);
INSERT INTO ACCESSION VALUES (112,'P102','5167','C102',2);
INSERT INTO ACCESSION VALUES (113,'P103','12901','C103',3);
INSERT INTO ACCESSION VALUES (114,'P104','9217','C104',4);
INSERT INTO ACCESSION VALUES (115,'P105','12902','C105',5);
/*-----------------------UPDATE_CONTACT TABLES------------------------*/
INSERT INTO UPDATE_CONTACT VALUES (1, '2018-03-01',111,'IJ','GOOD','YES','YES','YES','UHJ');

INSERT INTO UPDATE_CONTACT VALUES (2, '2018-03-02',112,'IJ','GOOD','YES','YES','NO','IJK');
INSERT INTO UPDATE_CONTACT VALUES (3, '2018-03-03',113,'IJ','GOOD','YES','YES','YES','NMK');
INSERT INTO UPDATE_CONTACT VALUES (4, '2018-03-04',114,'IJ','GOOD','YES','YES','NO','UHI');
INSERT INTO UPDATE_CONTACT VALUES (5, '2018-03-05',115,'IJ','GOOD','YES','YES','YES','NBV');
/*-------------------------QUERIES---------------------------------------------------------------------------*/
/*---------------# query 1--------------------------*/
SELECT PATIENT_INFO.PATIENT_ID, ACCESSION.ACCESSION_NO, ANIMAL_INFO.ANIMAL_NAME,
BREAD_TYPE.BREAD_NAME , ANIMAL_INFO.ANIMAL_TYPE, TREATMENT.TREATMENT_NO,
CARER_INFO.RELEASE_ANIMAL, CARER_INFO.SEND_TO_CARER
FROM ANIMAL_INFO, PATIENT_INFO, TREATMENT, ACCESSION, BREAD_TYPE, CARER_INFO
WHERE PATIENT_INFO.BREAD_ID=BREAD_TYPE.BREAD_ID
AND PATIENT_INFO.ANIMAL_ID=ANIMAL_INFO.ANIMAL_ID
AND PATIENT_INFO.PATIENT_ID=ACCESSION.PATIENT_ID
AND PATIENT_INFO.PATIENT_ID=TREATMENT.PATIENT_ID
AND CARER_INFO.CARER_ID=ACCESSION.CARER_ID
AND CARER_INFO.RELEASE_ANIMAL='NO'
ORDER BY ANIMAL_INFO.ANIMAL_TYPE;
/*---------------# query 2.a.i---------------------*/
SELECT ACCESSION.ACCESSION_NO, ANIMAL_INFO.LOCATION_GOV_AREA_SHIRE, CARER_INFO.DATE
FROM ACCESSION, CARER_INFO, ANIMAL_INFO, PATIENT_INFO
WHERE ANIMAL_INFO.ANIMAL_ID=PATIENT_INFO.ANIMAL_ID
AND PATIENT_INFO.PATIENT_ID=ACCESSION.PATIENT_ID
AND CARER_INFO.CARER_ID=ACCESSION.CARER_ID

AND CARER_INFO.DATE BETWEEN '2018-06-01' AND '2018-07-01'
AND ANIMAL_INFO.LOCATION_GOV_AREA_SHIRE='LOCATION GOV AREA'
GROUP BY ANIMAL_INFO.LOCATION_GOV_AREA_SHIRE;
/*--------------# query 2.a.ii----------------------*/
SELECT ACCESSION.ACCESSION_NO,
AFFICITION.AFFICITION_NAME AS 'CAUSE OF AFFICITION', CARER_INFO.DATE
FROM ACCESSION, CARER_INFO, ANIMAL_INFO, PATIENT_INFO, AFFICITION
WHERE ANIMAL_INFO.ANIMAL_ID=PATIENT_INFO.ANIMAL_ID
AND PATIENT_INFO.PATIENT_ID=ACCESSION.PATIENT_ID
AND CARER_INFO.CARER_ID=ACCESSION.CARER_ID
AND ANIMAL_INFO.AFFICITION_NO=AFFICITION.AFFICITION_NO
AND CARER_INFO.DATE BETWEEN '2018-06-01' AND '2018-07-01'
GROUP BY AFFICITION.AFFICITION_NAME;
/*----------------# query 2.b ---------------------*/
SELECT COUNT(ACCESSION.ACCESSION_NO) AS 'total number of accession'
FROM ACCESSION, CARER_INFO, RESCUER_INFO
WHERE ACCESSION.CARER_ID=CARER_INFO.CARER_ID
AND ACCESSION.RESCUER_ID=RESCUER_INFO.RESCUER_ID
AND CARER_INFO.DATE BETWEEN '2017-06-00' AND '2017-07-00';
/*-----------------# query 3------------------------*/
SELECT CARER, date AS 'expired permit'
FROM CARER_INFO
GROUP BY CARER
HAVING DATE <SYSDATE();