ORACLE SQL
SQL is a database computer language designed for the retrieval and
management of data in a relational database. SQL stands for Structured Query
Language. SQL is a language to operate databases; it includes database
creation, deletion, fetching rows, modifying rows, etc. SQL is an ANSI
(American National Standards Institute) standard language, but there are many
different versions of the SQL language.
Features
● High Performance.
● High Availability.
● Scalability and Flexibility Run anything.
● Robust Transactional Support.
● Web and Data Warehouse Strengths.
● Strong Data Protection.
● Comprehensive Application Development.
● Management Ease.
● Open Source Freedom and 24 x 7 Support.
● Lowest Total Cost of Ownership.
Applications of SQL
● Allows users to access data in the relational database management
systems.
● Allows users to describe the data.
● Allows users to define the data in a database and manipulate that data.
● Allows to embed within other languages using SQL modules, libraries &
pre-compilers.
● Allows users to create and drop databases and tables.
● Allows users to create view, stored procedure, functions in a database.
● Allows users to set permissions on tables, procedures and views.
SQL AND RELATIONAL ALGEBRA QUERIES:
1. Find all the patient’s names whose treatment description says
physiotherapy.
Answer: select PName from PATIENT, TREATMENT where PATIENT.PID =
TREATMENT.PID and Description = ‘physiotherapy’;
RESULT π (PName) ((σ (Description=’Physiotherapy’) (TREATMENT))* PATIENT)
2. Find the names of the patient who has been allotted the equipment name
catheters.
Answer: select PName from PATIENT, EQUIPMENT where PATIENT.PID =
EQUIPMENT.PID and EName = ‘Catheters’;
RESULT π (PName) ((σ (EName=’Catheters’) (EQUIPMENT)) * PATIENT)
3. Find all the names of the staff people who are receptionists and nurse but
not doctor.
Answer: select name from STAFF, RECEPTIONIST, NURSE where STAFF.StaffID
= RECEPTIONIST.StaffID and STAFF.StaffID = NURSE.StaffId;
RESULT π (Name) (RECEPTIONIST * STAFF * NURSE)
4. Find the names of the patients who are paying above 2000 for medicines.
Answer: select Pname from PATIENT, MEDICINES where PATIENT.PID =
MEDICINES.PID and Price > 2000;
RESULT π (PName) ((σ (Price>2000) (MEDICINES))* PATIENT)
5. Find the receptionist id of all the receptionists who have made the record
from record number 10 to 20.
Answer: select RID from RECEPTIONIST, RECORD where RECEPTIONIST.RID=
RECORD.RID and recordno between 10 and 20;
RESULT π (RID) ((σ (RecordNo >=10 ^ RecordNo <=20) (RECORD))* RECEPTIONIST)
6. Find the count of patients who have taken treatment from cardiologists.
Answer: select count(PID) from PATIENT,DOCTOR where PATIENT.DID =
DOCTOR.DID and Dspecialization = ‘Cardiology’;
T1 (σ(DSpecialization = ’Cardiologist’) (DOCTOR))* PATIENT
RESULT FCOUNT PID (T1)
7. Find all the patients name who have been allotted the ward 2.
Answer: select PName from PATIENT, WARD where PATIENT.WNO =
WARD.WNO and WNO =2;
π (PName) ((σ (WNO = 2) (WARD)) * PATIENT)
8. Find all the details of the patient who is in ward 10 and room number 2.
Ans: select * from patient, ward where ward.wno = 10 and roomno = 2;
9. Find all the names of the patients who have been admitted from 20th April
2019 and are in ward 5.
Ans : select Pname from patient, ward where patient.wno = ward.wno and
patientadmissiondate >= ‘20-APR-2019’;
10.Find the names of the nurse who is assigned to ward 8
Ans : select name from STAFF, NURSE, GOVERNED_BY where STAFF.STAFFID
= NURSE.STAFFID and NURSE.NID = GOVERNED_BY.NID and wno=8;
11. Find the number of patients whose contact is unknown.
Ans : select count(patient.pid) from PATIENT, PATIENT_CONTACTS WHERE
PATIENT.PID = PATIENT_CONTACTS.PID group by PContact having Pcontact is
null
12. Find the total number of employees that the hospital has.
Ans : select count(staffid) from HOSPITAL_HAS;
13. Find the names of the medicines which have been issued to the ward 3.
Ans : select name from medicines, ward, patient,MEDICINES where
medicines.pid = patient.pid and WARD.WNO=PATIENT.WNO and ward.wno =3;
14. Find the number of patients who have been treated by dentist.
Ans : select count(pid) from patient, doctor where patient.did = doctor.did
and Dspecialization = ‘Dentistry’;
15. Find the count of patients who are less than 18 years old.
Ans : select count(pid) from patients where age < 18;
