Scribd
Upload
40 views16 pages

Practical 7

Uploaded by

Mohammad Ashar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
40 views16 pages

Practical 7

Uploaded by

Mohammad Ashar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 16

OBJECT # 7:

Create Database “Hospital” Having following tables:


TABLE 1 is PATIENT having Fields: Patient_ID, Pateint_Name, Gender and Age.
TABLE 2 is Patient_Status Having fields: Pateint_ID, Doctor_Id, Disease,
Admit_Date, Medicine_Cost and Doctor_Fees.
TABLE 3 is Doctor_Profile having fields: Doctor_Id, Doctor_Name, Department
and Salary.
 Create Suitable Primary keys.
 Input relevant records in each table.
 Create relationships between the tables.

QUERIES:
1. Display Disease and doctor_Name of Pateint with ID P2.
2. Display ID, Name, Gender, Disease and Admit_Date of all Female Patients.
3. Calculate Room_Charges to be paid.
4. Search all Pateints under treatment of doctor Shariq Hameed.
5. Display Second_Highest_slary among Doctors.
6. Display recods of patients with particular disease.
7. Display recods of patients with min room charges.
8. Delete pateints record admitted on 1-Apr-23.
9. Update any patient’s status.
PROCEDURE:

 Open MS Access from start menu.


 Select Blank Desktop to start a new Database.
 Give Database a Name of SCHOOL FEES then click on create.

Table Design:

TABLE 1: PATIENT

 Goto menu bar then select create → Table design.


 Enter Field names with suitable data type.
 Assign Patient_ID as Primary key of table 1: Patient
 Save the table by ctrl+s.

 Open the Table PATIENT and and enter the records and then close this tab.

TABLE 2: PATIENT_STATUS

 Goto menu bar then select create → Table design.


 Enter Field names with suitable data type.
 Assign Patient_ID as Primary key of table 2: Patient_Status.
 Save the table by ctrl+s.

 Open table PATIENT_STATUS and enter the records and close this tab.

TABLE 3: DOCTOR_PROFILE:
 Goto menu bar then select create → Table design.
 Enter Field names with suitable data type.
 Assign Doctor_ID as Primary key of table 2:DOCTOR_PROFILE.
 Save the table by ctrl+s.

 Open table DOCTOR_PROFILE and enter the records and close this tab.
RELATIONSHIPS:

Now create Relationship between the tables , go to database tools in the tool bar then go to relationships and add
all three tables and drag the fields to relate them from one table to the other.

QUERY # 1:

 Goto Create → Query Design add TableS DOCTOR_PROFILE and PATIENT_STATUS


then close the Dialogue box.
 Select Pateint_ID from Patient_status in column 1 and in its criteria write “P2”, write
Disease from patient_status in column 2, and Doctor_name from DOCTOR_PROFILE in
column 3.

OUTPUT:

QUERY # 2:

 Goto Create → Query Design add TableS DOCTOR_PROFILE and PATIENT_STATUS


then close the Dialogue box.
 Select Pateint_ID from Patient_status in column 1, write Patient_Name from patient in
column 2, Gender from table PATIENT in column 3and in its criteria write “Female”,
Disease from table PATIENT STATUS in column 4, and Admit_Date from PATIENT
STATUS in column 5

OUTPUT:

QUERY # 3:

 Goto Create → Query Design add Table PATIENT STATUS close the Dialogue box.
 In column one select PATIENT STATUS*.
 In second column open build function in the column of expression elements go to
Database HOSPITAL → Tables →PATIENT STATUS ,Open bracket Add
Medicine_Cost (by double clicking on it) + Doctor_Fees(by double clicking)close the
bracket then click on ok. Then erase Expr1 from the column and in its place write
Room_Charges.

OUTPUT:
QUERY # 4:

 Goto Create → Query Design add Tables PATIENT, DOCTOR_PROFILE and


PATIENT_STATUS then close the Dialogue box.
 Select Pateint_Name from Patient in column 1, Patient_ID from PATIENT in column 2,
Doctor_Name from DOCTOR _PROFILE in column 3 and in its criteria write “Dr Shariq
Hameed”.

OUTPUT:

QUERY # 5:

OUTPUT:
QUERY # 6:

 Goto Create → Query Design add Table PATIENT and close the dialogue box.
 Select PATIENT STATUS* in column 1, and Disease In column 2 and in its critera write
[Enter Disease] and untick the box. To display records of patients having particular
disease.

OUTPUT:

QUERY # 7:

 Goto Create → Query Design , Add Query3 close the dialogue box.
 Select Patient_Id in column 1, Doctor_Id in column 2, Disease in column 3,
Admit_Datein column 4, Medicine_cost in column 5, Doctor_Fees in column6,
Room_Charges in column 7 and in its criteria write: (select min (room_charges) from
Query3).

OUTPUT:
QUERY # 8:

 Goto Create → Query Design add PATIENT STATUS Table close the Dialogue box.
 In the tool bar click on Delete, then select Admit_Date in column one and in its criteria write
“1-Apr-23”.

OUTPUT:

QUERY # 9:

OUTPUT:

SQL QUERIES:
CREATE QUERY:
Create table PATEINT
(
Patient_ID TEXT Primary key,

Patient_Name TEXT,

Gender TEXT,

Age Number

);
INSERT QUERY:
Insert into table PATIENT
Values (“P1”, “Saqib Khan” , “Male” , “40”);
Query # 1:
Select Patient_status.Patient_ID, Pateint_status.Disease, Doctor_Profile.Doctor_Name
From Doctor_Profile inner join Patient_status
ON Doctor_Profile.Doctor_ID = Patient_Status.Doctor_ID
WHERE Patient_Status.Patient_ID = “P2”;
QUERY # 2:
Select patient.patient_ID,Patient.Patient_Name,patient.Gender,Patient_status.disease,
patient_status.Admit_Date
FROM Patient inner join Patient_status
ON patient.Patient_ID=Patient_status.patient_ID
WHERE patient.Gender=”FEMALE”;
QUERY # 3:
SELECT PATIENT STATUS.*,(Medicine_Cost+Doctor_Fees) AS Room_Charges
FROM PATIENT STATUS;
QUERY # 4:
SELECT PATIENT.Patient_Name, PATIENT.Patient_ID, DOCTOR_PROFILE.Doctor_Name
FROM (DOCTOR_PROFILE INNER JOIN PATIENT_STATUS ON DOCTOR_PROFILE.Doctor_ID =
PATIENT_STATUS.Doctor_ID) INNER JOIN PATIENT ON PATIENT_STATUS.Pateint_ID =
PATIENT.Patient_ID

WHERE (((DOCTOR_PROFILE.Doctor_Name)="Dr shariq Hameed"));

QUERY # 5:
SELECT Max(salary) AS Second_Highest_salary
FROM Doctor_Profile
WHERE Salary<(Select max (salary) from Doctor_Profile);
QUERY # 6:
SELECT PATIENT_STATUS.*
FROM PATIENT_STATUS
WHERE Disease=[Enter Disease];
QUERY # 7:
SELECT Query3.Pateint_ID, Query3.Doctor_ID, Query3.Disease, Query3.Admit_Date,
Query3.Medicine_Cost, Query3.Doctor_Fees, Query3.Room_Charges, *
FROM Query3
WHERE Query3.Room_Charges=(select min(Room_Charges) from Query3);
QUERY # 8:
DELETE*
FROM PATIENT_STAUS
WHERE Admit_Date = “1-Apr-23”;
QUERY # 9:
UPDATE PATIENT_STATUS
SET Patient_ID = “P4”
WHERE Patient_ID = “P5”

You might also like