Blood Donation Project
Database Name: blood_donation
Tables Overview
| S.No | Table Name | No. of Fields | Demo Data | |
|---|---|---|---|---|
| Create | Insert | |||
| 1 | blood_banks | 4 | Create | Insert |
| 2 | blood_groups | 4 | Create | Insert |
| 3 | camp | 6 | Create | Insert |
| 4 | camp_details | 7 | Create | Insert |
| 5 | camp_members | 4 | Create | Insert |
| 6 | staff | 9 | Create | Insert |
| 7 | donors | 13 | Create | Insert |
| 8 | hospitals | 4 | Create | Insert |
| 9 | hospital_blood | 6 | Create | Insert |
| 10 | staff_types | 4 | Create | Insert |
| 11 | patients | 10 | Create | Insert |
Table Structure: blood_banks
| Field Name | Data Type |
|---|---|
| Id | int |
| blood_bank | varchar |
| created_at | datetime |
| update_at | datetime |
Following SQL creates a new table called "blood_banks"
CREATE TABLE blood_banks (
id int,
blood_bank varchar(100),
created_at datetime ,
updated_at datetime );
id int,
blood_bank varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE blood_banks
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "blood_banks"
INSERT INTO blood_banks (id, blood_bank, created_at, updated_at) VALUES
(1, 'Rotary Club Blood Bank', '2020-12-12 09:43:17', '2020-12-12 09:43:17'),
(2, 'Sri Surya Sakthi Voluntary Blood Bank', '2020-12-12 09:43:17', '2020-12-12 09:43:17'),
(3, 'Govt. Blood Bank', '2020-12-12 09:43:17', '2020-12-12 09:43:17');
(1, 'Rotary Club Blood Bank', '2020-12-12 09:43:17', '2020-12-12 09:43:17'),
(2, 'Sri Surya Sakthi Voluntary Blood Bank', '2020-12-12 09:43:17', '2020-12-12 09:43:17'),
(3, 'Govt. Blood Bank', '2020-12-12 09:43:17', '2020-12-12 09:43:17');
Table Structure: blood_groups
| Field Name | Data Type |
|---|---|
| Id | int |
| blood_group | varchar |
| created_at | datetime |
| update_at | datetime |
Following SQL creates a new table called "blood_groups"
CREATE TABLE blood_groups (
id int,
blood_group varchar(100),
created_at datetime ,
updated_at datetime );
id int,
blood_group varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE blood_groups
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "blood_groups"
INSERT INTO blood_groups (id, blood_group, created_at, updated_at) VALUES
(1, 'A-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(2, 'A-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(4, 'B-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(5, 'B-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(3, 'AB-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(6, 'AB-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(7, 'O-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(8, 'O-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00');
(1, 'A-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(2, 'A-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(4, 'B-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(5, 'B-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(3, 'AB-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(6, 'AB-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(7, 'O-positive', '2020-12-12 09:34:00', '2020-12-12 09:34:00'),
(8, 'O-negative', '2020-12-12 09:34:00', '2020-12-12 09:34:00');
Table Structure: camp
| Field Name | Data Type |
|---|---|
| Id | int |
| camp | varchar |
| address | varchar |
| venue | varchar |
| created_at | datetime |
| update_at | datetime |
Following SQL creates a new table called "camp"
CREATE TABLE camp (
id int,
camp varchar(100),
address varchar(100),
venue varchar(100),
created_at datetime ,
updated_at datetime );
id int,
camp varchar(100),
address varchar(100),
venue varchar(100),
created_at datetime ,
updated_at datetime );
Following SQL inserts 6 records in "camp"
INSERT INTO camp (id, camp, address, venue, created_at, updated_at) VALUES
(1, 'amaravathi_bloodcamp', 'amaravathi', 'mangalagiri', '2020-12-12 11:10:48', '2020-12-12 11:10:48'),
(2, 'vizag_bloodcamp', 'vishakapatnam', 'arakuvalley', '2020-12-12 11:10:48', '2020-12-12 11:10:48'),
(3, 'vizag2_bloodcamp', 'vishakaptnam', 'steelplant', '2020-12-12 11:10:48', '2020-12-12 11:10:48');
(1, 'amaravathi_bloodcamp', 'amaravathi', 'mangalagiri', '2020-12-12 11:10:48', '2020-12-12 11:10:48'),
(2, 'vizag_bloodcamp', 'vishakapatnam', 'arakuvalley', '2020-12-12 11:10:48', '2020-12-12 11:10:48'),
(3, 'vizag2_bloodcamp', 'vishakaptnam', 'steelplant', '2020-12-12 11:10:48', '2020-12-12 11:10:48');
Table Structure: camp_details
| Field name | Data type |
|---|---|
| Id | int |
| camp_id | varchar |
| no_doctors | varchar |
| no_nurse | varchar |
| no_beds | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "camp_details"
CREATE TABLE camp_details (
id int,
camp_id varchar(100),
no_doctors varchar(100),
no_nurse varchar(100),
no_beds varchar(100),
created_at datetime ,
updated_at datetime );
id int,
camp_id varchar(100),
no_doctors varchar(100),
no_nurse varchar(100),
no_beds varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE camp_details
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 7 records in "camp_details"
INSERT INTO camp_details (id, camp_id, no_doctors, no_nurse, no_beds, created_at, updated_at) VALUES
(1, '1', '10', '10', '25', '2020-12-12 11:12:08', '2020-12-12 11:12:08'),
(2, '2', '15', '12', '20', '2020-12-12 11:12:08', '2020-12-12 11:12:08'),
(3, '3', '10', '18', '15', '2020-12-12 11:12:08', '2020-12-12 11:12:08');
(1, '1', '10', '10', '25', '2020-12-12 11:12:08', '2020-12-12 11:12:08'),
(2, '2', '15', '12', '20', '2020-12-12 11:12:08', '2020-12-12 11:12:08'),
(3, '3', '10', '18', '15', '2020-12-12 11:12:08', '2020-12-12 11:12:08');
Table Structure: camp_members
| Field name | Data type |
|---|---|
| Id | int |
| staff_id | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "camp_members"
CREATE TABLE camp_members (
id int,
staff_id varchar(100),
created_at datetime ,
updated_at datetime );
id int,
staff_id varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE camp_members
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "camp_members"
INSERT INTO camp_members (id, staff_id, created_at, updated_at) VALUES
(1, '1', '2020-12-12 11:12:53', '2020-12-16 15:39:36'),
(2, '1', '2020-12-12 11:12:53', '2020-12-16 15:39:40'),
(3, '2', '2020-12-12 11:12:53', '2020-12-16 15:39:44');
(1, '1', '2020-12-12 11:12:53', '2020-12-16 15:39:36'),
(2, '1', '2020-12-12 11:12:53', '2020-12-16 15:39:40'),
(3, '2', '2020-12-12 11:12:53', '2020-12-16 15:39:44');
Table Structure: staff
| Field name | Data type |
|---|---|
| Id | int |
| staff_id | varchar |
| name | varchar |
| address | varchar |
| mobile | varchar |
| alternate_mobile | varchar |
| qualification | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "staff"
CREATE TABLE staff (
id int,
staff_id varchar(100),
name varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
qualification varchar(100),
created_at datetime ,
updated_at datetime );
id int,
staff_id varchar(100),
name varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
qualification varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE staff
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 9 records in "staff"
INSERT INTO staff (id, staff_id, name, address, mobile, alternate_mobile, qualification, created_at, updated_at) VALUES
(1, '1', 'jhon', 'mumbai', '1254785412', '6985471254', 'MBBS', '2020-12-12 12:57:42', '2020-12-12 12:57:42'),
(2, '2', 'pallavi', 'delhi', '5847563214', '9654871254', 'bs.nursing', '2020-12-12 12:57:42', '2020-12-12 12:57:42'),
(3, '3', 'taapsee ', 'hyderabad', '2587413690', '36958214441', 'technician', '2020-12-12 12:57:42', '2020-12-12 12:57:42');
(1, '1', 'jhon', 'mumbai', '1254785412', '6985471254', 'MBBS', '2020-12-12 12:57:42', '2020-12-12 12:57:42'),
(2, '2', 'pallavi', 'delhi', '5847563214', '9654871254', 'bs.nursing', '2020-12-12 12:57:42', '2020-12-12 12:57:42'),
(3, '3', 'taapsee ', 'hyderabad', '2587413690', '36958214441', 'technician', '2020-12-12 12:57:42', '2020-12-12 12:57:42');
Table Structure: donors
| Field name | Data type |
|---|---|
| Id | int |
| donor_id | varchar |
| name | varchar |
| gender | varchar |
| age | varchar |
| address | varchar |
| mobile | varchar |
| alternate_mobile | varchar |
| blood_group | varchar |
| disease | varchar |
| units | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "donors"
CREATE TABLE donors (
id int,
donor_id varchar(100),
name varchar(100),
gender varchar(100),
age varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
blood_group varchar(100),
disease varchar(100),
units varchar(100),
created_at datetime ,
updated_at datetime );
id int,
donor_id varchar(100),
name varchar(100),
gender varchar(100),
age varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
blood_group varchar(100),
disease varchar(100),
units varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE `donors`
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 13 records in "donors"
INSERT INTO donors (id, donor_id, name, gender, age, address, mobile, alternate_mobile, blood_group, disease,units ,created_at, updated_at) VALUES
(1, 'dn1', 'aamirkhan', 'male', '45', 'mumbai', '1234567890', '9876543210', '7', '-','1', '2020-12-12 12:00:04', '2020-12-12 12:00:04'),
(2, 'dn1', 'salmankhan', 'male', '50', 'mumbai', '0987456321', '5698741254', '7', '-','2', '2020-12-12 12:00:04', '2020-12-12 12:00:04'),
(3, 'dn1', 'shahrukhkhan', 'male', '40', 'mumbai', '5987436514', '5987456321', '7', '-','3', '2020-12-12 12:00:04', '2020-12-12 12:00:04');
(1, 'dn1', 'aamirkhan', 'male', '45', 'mumbai', '1234567890', '9876543210', '7', '-','1', '2020-12-12 12:00:04', '2020-12-12 12:00:04'),
(2, 'dn1', 'salmankhan', 'male', '50', 'mumbai', '0987456321', '5698741254', '7', '-','2', '2020-12-12 12:00:04', '2020-12-12 12:00:04'),
(3, 'dn1', 'shahrukhkhan', 'male', '40', 'mumbai', '5987436514', '5987456321', '7', '-','3', '2020-12-12 12:00:04', '2020-12-12 12:00:04');
Table Structure: hospitals
| Field name | Data type |
|---|---|
| Id | int |
| hospital | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "hospitals"
CREATE TABLE hospitals (
id int,
hospitals varchar(100),
created_at datetime ,
updated_at datetime );
id int,
hospitals varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE hospitals
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "hospitals"
INSERT INTO hospitals (id, hospitals, created_at, updated_at) VALUES
(1, 'A.N. Beach Hospital', '2020-12-12 12:01:27', '2020-12-12 12:01:27'),
(2, 'Aarif Nursing Home', '2020-12-12 12:01:28', '2020-12-12 12:01:28'),
(3, 'AakarAsha Hospitals', '2020-12-12 12:01:28', '2020-12-12 12:01:28');
(1, 'A.N. Beach Hospital', '2020-12-12 12:01:27', '2020-12-12 12:01:27'),
(2, 'Aarif Nursing Home', '2020-12-12 12:01:28', '2020-12-12 12:01:28'),
(3, 'AakarAsha Hospitals', '2020-12-12 12:01:28', '2020-12-12 12:01:28');
Table Structure: hospital_blood
| Field name | Data type |
|---|---|
| Id | int |
| hospital_id | varchar |
| blood_group | varchar |
| date | date |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "hospital_blood"
CREATE TABLE hospital_blood (
id int,
hospital_id varchar(100),
blood_group varchar(100),
date date,
created_at datetime ,
updated_at datetime );
id int,
hospital_id varchar(100),
blood_group varchar(100),
date date,
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE hospital_blood
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 6 records in "hospital_blood "
INSERT INTO hospital_blood (id, hospital_id, blood_group, date, created_at, updated_at) VALUES
(1, '1', '7', '2020-05-03', '2020-12-12 12:02:24', '2020-12-12 12:02:24'),
(2, '2', '6', '2020-08-03', '2020-12-12 12:02:24', '2020-12-12 12:02:24'),
(3, '3', '5', '2020-02-03', '2020-12-12 12:02:24', '2020-12-12 12:02:24');
(1, '1', '7', '2020-05-03', '2020-12-12 12:02:24', '2020-12-12 12:02:24'),
(2, '2', '6', '2020-08-03', '2020-12-12 12:02:24', '2020-12-12 12:02:24'),
(3, '3', '5', '2020-02-03', '2020-12-12 12:02:24', '2020-12-12 12:02:24');
Table Structure: staff_types
| Field name | Data type |
|---|---|
| Id | int |
| type | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "staff_types"
CREATE TABLE staff_types (
id int,
type varchar(100),
created_at datetime ,
updated_at datetime );
id int,
type varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE staff_types
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "staff_types"
INSERT INTO staff_types (id, type, created_at, updated_at) VALUES
(1, 'doctor', '2020-12-12 12:54:15', '2020-12-12 12:54:15'),
(2, 'nurse', '2020-12-12 12:54:15', '2020-12-12 12:54:15'),
(3, 'technician', '2020-12-12 12:54:15', '2020-12-12 12:54:15'),
(4, 'security', '2020-12-12 12:54:15', '2020-12-12 12:54:15');
(1, 'doctor', '2020-12-12 12:54:15', '2020-12-12 12:54:15'),
(2, 'nurse', '2020-12-12 12:54:15', '2020-12-12 12:54:15'),
(3, 'technician', '2020-12-12 12:54:15', '2020-12-12 12:54:15'),
(4, 'security', '2020-12-12 12:54:15', '2020-12-12 12:54:15');
Table Structure: patients
| Field name | Data type |
|---|---|
| Id | int |
| patient_id | varchar |
| name | varchar |
| address | varchar |
| mobile | varchar |
| alternate_mobile | varchar |
| disease | varchar |
| medical_repots | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "patients"
CREATE TABLE patients (
id int,
patient_id varchar(100),
name varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
disease varchar(100),
medical_reports varchar(100),
created_at datetime ,
updated_at datetime );
id int,
patient_id varchar(100),
name varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
disease varchar(100),
medical_reports varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE patients
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 10 records in "patients"
INSERT INTO patients (id, patient_id, name, mobile, alternate_mobile, disease, medical_reports, created_at, updated_at) VALUES
(1, 'p1', 'sandeep', '8524716932', '3698524671', '-', '-', '2020-12-12 12:06:58', '2020-12-12 12:06:58'),
(2, 'p2', 'asif', '8965412789', '3579514863', '-', '-', '2020-12-12 12:06:58', '2020-12-12 12:06:58'),
(3, 'p3', 'javeed', '3479852146', '6754128963', '-', '-', '2020-12-12 12:06:58', '2020-12-12 12:06:58');
(1, 'p1', 'sandeep', '8524716932', '3698524671', '-', '-', '2020-12-12 12:06:58', '2020-12-12 12:06:58'),
(2, 'p2', 'asif', '8965412789', '3579514863', '-', '-', '2020-12-12 12:06:58', '2020-12-12 12:06:58'),
(3, 'p3', 'javeed', '3479852146', '6754128963', '-', '-', '2020-12-12 12:06:58', '2020-12-12 12:06:58');
Questions
1. Display the camp members?
A. SELECT*FROM 'camp_members'
2. Display the patient details based on date?
A. SELECT* FROM `patients` where DATE(created_at) = DATE('2020-12-12')
3. Display the list of blood bank names?
A. SELECT * FROM `blood_banks`
4. Display the list of blood groups?
A. SELECT * FROM `blood_groups`
5. Display the date time of the donors records?
A. SELECT d.id,d.donor_id,d.name,d.created_at from donors d
6. Which donor has donated maximum units?
A. SELECT* FROM `camp` cp left join camp_details cd on cp.id=cd.camp_id
7. Dispaly the camp details based on camp id?
A. SELECT * FROM `donors` where DATE(created_at) = DATE('2020-12-12')
(Here given example date )
(Here given example date )
8. Display the donor details based on today's date?
A. SELECT d.id,d.name,d.age,d.gender from donors d where d.units="3"
9. Display the camp members based on staff id?
A. SELECT * FROM `camp_members` cm left join staff_types tp on cm.id=tp.id
10. Which age donor is given high units of blood and displays the gender also?
A. SELECT * FROM `donors` where units="3"
(Here 3 is maximum number of units for particular donor)
(Here 3 is maximum number of units for particular donor)
11. On which date we provided the blood to the hospitals?
A. SELECT * FROM `hospital_blood` where date="2020-05-03"
(Here date is user wish based on database)
(Here date is user wish based on database)
12. Which blood group is having high units in our database?
A. SELECT d.id,d.name,bg.blood_group,d.units from donors d left join blood_groups bg on d.blood_group=bg.id
13. How many beds are allotted to each camp?
A. SELECT cd.id,cp.camp,cd.no_doctors,cd.no_nurse,cd.no_beds from camp_details cd left join camp cp on cp.id=cd.camp_id
(Based on database)
(Based on database)
14. How many doctors are available in each camp?
A. SELECT cm.id,ty.type from camp_members cm left join staff_types ty on cm.staff_id=ty.id
15. How many nurses are available in each camp?
A. SELECT cm.id,ty.type from camp_members cm left join staff_types ty on cm.staff_id=ty.id
16. Which age donor is given high units of blood and displays the gender also?
A. SELECT * FROM `donors` where units="3"
(Here 3 is maximum number of units for particular donor)
(Here 3 is maximum number of units for particular donor)
