Database creation and insertion queries DBMS102

Database creation and insertion queries

create database appointmentSystem;

use appointmentSystem;

create table DOCTOR(

doctorID INT NOT NULL,

firstName CHAR(40),

surName CHAR(40),

PRIMARY KEY (doctorID)

);

insert into DOCTOR values(1, 'Hemant', 'williams');

insert into DOCTOR values(2, 'Jai', 'williams');

insert into DOCTOR values(3, 'Om', 'roy');

insert into DOCTOR values(4, 'Smith', 'Linda');

insert into DOCTOR values(5, 'Iya', 'Williams');

insert into DOCTOR values(6, 'Michael', 'Jone');

insert into DOCTOR values(7, 'Hohn', 'Linda');

insert into DOCTOR values(8, 'Raymonds', 'jonas');

insert into DOCTOR values(9, 'Riya', 'Wain');

insert into DOCTOR values(10, 'Wein', 'Micheele');

insert into DOCTOR values(11, 'Charlie', 'Adam');

insert into DOCTOR values(12, 'Alexander', 'Austin');

insert into DOCTOR values(13, 'Leo', 'Adam');

insert into DOCTOR values(14, 'Oliver', 'Archie');

insert into DOCTOR values(15, 'Henry', 'Archie');

create table INSURANCE_COMPANY(

insuranceCompanyID INT NOT NULL,

companyName CHAR(40),

contactNo VARCHAR(10),

email VARCHAR(80),

UNIQUE (companyName),

PRIMARY KEY (insuranceCompanyID)

);

insert into INSURANCE_COMPANY values(1, 'TAL Life Ltd', '111111', 'tal@gmail.com');

insert into INSURANCE_COMPANY values(2, 'AIA Australia Limited', '22222', 'aia@gmail.com');

insert into INSURANCE_COMPANY values(3, 'Zurich Australia Ltd', '33333', 'zurich@gmail.com');

insert into INSURANCE_COMPANY values(4, 'MLC Limited', '44444', 'mlc@gmail.com');

insert into INSURANCE_COMPANY values(5, 'MetLife Australia', '55555', 'met@gmail.com');

insert into INSURANCE_COMPANY values(6, 'ClearView Life Assurance Limited', '66666', 'tal@gmail.com');

insert into INSURANCE_COMPANY values(7, 'Resolution Group', '77777', 'res@gmail.com');

insert into INSURANCE_COMPANY values(8, 'NobleOak Life Limited', '88888', 'noble@gmail.com');

insert into INSURANCE_COMPANY values(9, 'Integrity Life Australia Limited', '99999', 'integrity@gmail.com');

insert into INSURANCE_COMPANY values(10, 'Health Partners', '21212', 'health@gmail.com');

insert into INSURANCE_COMPANY values(11, 'HBF', '43433', 'hbf@gmail.com');

insert into INSURANCE_COMPANY values(12, 'Westfund', '54545', 'west@gmail.com');

insert into INSURANCE_COMPANY values(13, 'HCF', '65656', 'hcf@gmail.com');

insert into INSURANCE_COMPANY values(14, 'BUPA', '76766', 'bupa@gmail.com');

insert into INSURANCE_COMPANY values(15, 'Medibank', '87878', 'medi@gmail.com');

create table PATIENT(

patientID INT NOT NULL,

firstName CHAR(40),

surName CHAR(40),

dob DATE,

address VARCHAR(100),

insurance INT,

PRIMARY KEY (patientID),

FOREIGN KEY (insurance) REFERENCES INSURANCE_COMPANY(insuranceCompanyID));

insert into PATIENT values(1, 'William', 'Austin', '1990-05-05', '1234 NW Bobcat Lane',1);

insert into PATIENT values(2, 'Alexander', 'Austin', '1990-05-05', '721S Cedar Street',2);

insert into PATIENT values(3, 'Harrison', 'Jone', '1990-05-05', '1234 Cedar Street',3);

insert into PATIENT values(4, 'Isaac', 'Austin', '1990-05-05', 'North way',4);

insert into PATIENT values(5, 'Abel', 'Arlo', '1990-05-05', 'South way',4);

insert into PATIENT values(6, 'Hudson', 'Austin', '1990-05-05', '1/2 Western Lane',5);

insert into PATIENT values(7, 'Jack', 'Arlo', '1990-05-05', '1678 Cedar Street',6);

insert into PATIENT values(8, 'Yusuf', 'Peter', '1990-05-05', '678 Eastern Lane',7);

insert into PATIENT values(9, 'rita', 'williams', '1990-05-05', '22-A Western Path',8);

insert into PATIENT values(10, 'Ray', 'roy', '1990-05-05', '12-B Eastern Path',9);

insert into PATIENT values(11, 'Steve', 'Joy', '1990-05-05', '15-P North Path',10);

insert into PATIENT values(12, 'Smith', 'Linda', '1990-05-05', 'Z-32 Southern Path',1);

insert into PATIENT values(13, 'Michael', 'Jone', '1990-05-05', 'P-45 Western Path',2);

insert into PATIENT values(14, 'William', 'Robert', '1990-05-05', 'C-90 Eastern Path',3);

insert into PATIENT values(15 , 'Wilson', 'jonas', '1990-05-05', '112 North Path Street',4);

create table APPOINTMENT(

appointmentID INT NOT NULL,

appointmentDate DATE,

patient INT,

doctor INT,

purpose CHAR(30),

status CHAR(1),

PRIMARY KEY (appointmentID),

FOREIGN KEY (patient) REFERENCES PATIENT(patientID),

FOREIGN KEY (doctor) REFERENCES DOCTOR(doctorID),

CONSTRAINT CHK_t_appointStatus CHECK (status IN ('C', 'P', 'R'))

);

insert into APPOINTMENT values(1, '2024-06-06', 1,1,'Cough', 'P' );

insert into APPOINTMENT values(2, '2024-07-07', 2,2,'Cold', 'C' );

insert into APPOINTMENT values(3, '2024-08-06', 1,1,'Fever', 'P' );

insert into APPOINTMENT values(4, '2024-09-06', 3,3,'vomiting', 'R' );

insert into APPOINTMENT values(5, '2024-10-08', 1,1,'Stomach ache', 'C' );

insert into APPOINTMENT values(6, '2024-06-09', 4,4,'Cough', 'P' );

insert into APPOINTMENT values(7, '2024-05-31', 5,1,'Cold', 'C' );

insert into APPOINTMENT values(8, '2024-05-30', 6,5,'Cough', 'C' );

insert into APPOINTMENT values(9, '2024-06-29', 7,1,'Fever and cold', 'P' );

insert into APPOINTMENT values(10, '2024-05-06', 8,6,'Cough', 'P' );

insert into APPOINTMENT values(11, '2024-07-16', 1,7,'Cough and fever', 'P' );

insert into APPOINTMENT values(12, '2024-09-26', 9,10,'Cough', 'C' );

insert into APPOINTMENT values(13, '2024-10-16', 1,8,'Cold', 'P' );

insert into APPOINTMENT values(14, '2024-07-06', 1,9,'Cough and cold', 'P' );

insert into APPOINTMENT values(15, '2024-06-06', 1,10,'Cough', 'P' );

create table BILL(

billID INT NOT NULL,

appointment INT,

billDate DATE,

amount DECIMAL(4,2),

PRIMARY KEY (billID),

FOREIGN KEY (appointment) REFERENCES APPOINTMENT(appointmentID));

insert into BILL values(1,1,'2024-06-06',12.23);

insert into BILL values(2,2, '2024-07-07',24);

insert into BILL values(3,3, '2024-08-06',12.23);

insert into BILL values(4,4, '2024-09-06',12);

insert into BILL values(5,5, '2024-10-08',34);

insert into BILL values(6,6, '2024-06-09',54);

insert into BILL values(7,7, '2024-05-31',43);

insert into BILL values(8,8, '2024-05-30',56.00);

insert into BILL values(9,9, '2024-06-29',42);

insert into BILL values(10,10, '2024-05-06',76);

insert into BILL values(11,11, '2024-07-16',76);

insert into BILL values(12,12, '2024-09-26',54);

insert into BILL values(13,13, '2024-10-16',78);

insert into BILL values(14,14, '2024-07-06',72);

insert into BILL values(15,15, '2024-06-06',67);

Updation queries

DOCTOR table

1. UPDATE DOCTOR SET surName = 'Jain' where firstName='Jai';
2. UPDATE DOCTOR SET surName = 'Will' where firstName LIKE '%L%';
3. UPDATE DOCTOR SET surName = 'Khan' where firstName IN ('Jai', 'Om');
4. UPDATE DOCTOR SET surName = 'Ray' where doctorID BETWEEN 1 and 3;
5. UPDATE DOCTOR SET firstName='Jai' where doctorID IN (select count(patientID) from patient group by firstName having count(patientID) <3>

PATIENT table

1. UPDATE PATIENT SET firstName='Jai' where patientID IN (select count(doctorID) from doctor group by firstName having count(doctorID) <3>
2. UPDATE PATIENT SET surName = 'Astin' where firstName='Abel';
3. UPDATE PATIENT SET dob='1991-05-05' where firstName LIKE '?el%';
4. UPDATE PATIENT SET dob='1991-05-05' where insurance IN (3,5);
5. UPDATE PATIENT SET dob='1991-05-05' where patientID BETWEEN 1 and 3;

INSURANCE_COMPANY table

1. UPDATE insurance_company SET email= 'tal@yahoo.com' where insurancecompanyID = 1;
2. UPDATE insurance_company SET contactNo = '11231' where companyName LIKE '%Tal%';
3. UPDATE insurance_company SET contactNo = '11111' where insurancecompanyID IN (1,2)
4. UPDATE insurance_company SET contactNo = '22222' where insurancecompanyID BETWEEN 4 and 6;
5. UPDATE insurance_company SET contactNo='5656' where insurancecompanyID IN (select count(doctorID) from doctor group by firstName having count(doctorID) <3>

APPOINTMENT table

1. UPDATE appointment SET status= 'R' where patient = 1;
2. UPDATE appointment SET appointmentDate= '2024-08-08' where purpose LIKE '%cough%';
3. UPDATE appointment SET appointmentDate= '2024-08-08' where patient IN (1,2);
4. UPDATE appointment SET status= 'C' where doctor BETWEEN 4 and 5;
5. UPDATE appointment SET patient=2 where appointmentID IN

(select count(patientID) from patient group by firstName having count(patientID) <2>

BILL table

1. UPDATE BILL SET amount = 44 where appointment = 1;
2. UPDATE BILL SET amount = 44 where appointment IN (select appointmentID from appointment where purpose LIKE 'cough');
3. UPDATE BILL SET appointment= 1 where amount IN (12.23,24);
4. UPDATE BILL SET amount = 44 where appointment between 4 and 5;
5. UPDATE BILL SET amount = 44 where appointment IN (select count(patientID) from patient group by firstName having count(patientID) <2>

Stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()

BEGIN

select * from patient;

END