PRANVEER SINGH INSTITUTE OF TECHNOLOGY, KANPUR

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

Odd Semester 2023-24

B. Tech.- Third Year

Semester- V

Lab File
Database Management System
(KCS551)

Submitted To : Submitted By :
Faculty Name :Mr. Pradeep Rai Name :Satyam Prajapati
Designation :Assistant Professor Roll No. :2101640100236
Section :CS-3-D
 Table of Contents
• Vision and Mission Statements of the Institute

• Vision and Mission Statements of the Department

• PEOs, POs, PSOs of the Department

• Course Objective and Outcomes

• List of Experiments

• Index
 Department Vision Statement
To be a recognized Department of Computer Science & Engineering that produces versatile computer
engineers, capable of adapting to the changing needs of computer and related industry.

Department Mission Statements

The mission of the Department of Computer Science and Engineering is:

i. To provide broad based quality education with knowledge and attitude to succeed in Computer
Science & Engineering careers.

ii. To prepare students for emerging trends in computer and related industry.

iii. To develop competence in students by providing them skills and aptitude to foster culture of
continuous and lifelong learning.

iv. To develop practicing engineers who investigate research, design, and find workable solutions to
complex engineering problems with awareness & concern for society as well as environment.

Program Educational Objectives (PEOs)

i. The graduates will be efficient leading professionals with knowledge of computer science &
engineering discipline that enables them to pursue higher education and/or successful careers in
various domains.

ii. Graduates will possess capability of designing successful innovative solutions to real life problems
that are technically sound, economically viable and socially acceptable.

iii. Graduates will be competent team leaders, effective communicators and capable of working in
multidisciplinary teams following ethical values.

iv. The graduates will be capable of adapting to new technologies/tools and constantly upgrading their
knowledge and skills with an attitude for lifelong learning
 Department Program Outcomes (POs)
The students of Computer Science and Engineering Department will be able:

1. Engineering knowledge: Apply the knowledge of mathematics, science, Computer Science &
Engineering fundamentals, and an engineering specialization to the solution of complex engineering
problems.

2. Problem analysis: Identify, formulate, review research literature, and analyze complex
engineering problems reaching substantiated conclusions using first principles of mathematics,
natural sciences, and Computer Science & Engineering sciences.

3. Design/development of solutions: Design solutions for complex Computer Science &

Engineering problems and design system components or processes that meet the specified needs
with appropriate consideration for the public health and safety, and the cultural, societal, and
environmental considerations.

4. Investigation: Use research-based knowledge and research methods including design of

experiments, analysis and interpretation of data, and synthesis of the information to provide valid
conclusions.

5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern
engineering and IT tools including prediction and modelling to complex Computer Science &
Engineering activities with an understanding of the limitations.

6. The Engineering and Society: Apply reasoning informed by the contextual knowledge to assess
societal, health, safety, legal and cultural issues and the consequent responsibilities relevant to the
professional engineering practice in the field of Computer Science and Engineering.

7. Environment and sustainability: Understand the impact of the professional Computer Science
& Engineering solutions in societal and environmental contexts, and demonstrate the knowledge of,
and need for sustainable development.

8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities and norms
of the Computer Science & Engineering practice.

9. Individual and team work: Function effectively as an individual, and as a member or leader in
diverse teams, and in multidisciplinary settings.

10. Communication: Communicate effectively on complex Computer Science & Engineering

activities with the engineering community and with society at large, such as, being able to
comprehend and write effective reports and design documentation, make effective presentations,
and give and receive clear instructions.

11. Project management and finance: Demonstrate knowledge and understanding of the Computer
Science & Engineering and management principles and apply these to one’s own work, as a member
and leader in a team, to manage projects and in multidisciplinary environments.

12. Life-long learning: Recognize the need for, and have the preparation and ability to engage in
independent and life-long learning in the broadest context of technological change.
 Department Program Specific Outcomes (PSOs)
The students will be able to:

1. Use algorithms, data structures/management, software design, concepts of programming

languages and computer organization and architecture.

2. Understand the processes that support the delivery and management of information systems
within a specific application environment.

Course Outcomes
*Level of Bloom’s Level to be
*Level of Bloom’s Taxonomy Level to be met
Taxonomy met
L1: Remember 1 L2: Understand 2
L3: Apply 3 L4: Analyze 4
L5: Evaluate 5 L6: Create 6

CO Number Course Outcomes

Able to learn brief knowledge [L1: Remember] about modeling and Data
KCS-551.1 definition language (DDL) operations on table.
Able to handle Data Base by applying [L3: Apply] data manipulation
KCS-551.2 operations (DML) operations on table.
Able to analyze [L4: Analyze] normalization of data and uses of cursor trigger
KCS-551.3 and views operations on table.
 List of Experiments

Lab Lab Experiment Corresponding CO

No.
Write different syntaxes of DDL, DML, DCL, TCL
0
with example. CO1
DDL Statement: Create Command (For Hospital
1
Management System Schema) CO1
2 DDL Statement: ALTER, DROP and TRUNCATE CO2
3 DML Statement: Insert, update, delete and select CO2
Use of Operators and Aggregate function (min, max,
4
sum, count and average) CO2
5 Use of Group by, Order by and Having clause CO2
6 Use of JOIN Operations CO2
7 Use of Sub Query CO2
8 Use of Co-Related sub queries CO2
9 PL /SQL – Use of basic PL/SQL CO2
10 PL /SQL – Use of Procedure, cursor and trigger CO3
 INDEX
S Lab Experiment Date of Date of Marks Faculty
No Experiment Submission Signature
0

10
 Experiment-0
Objective: Write different syntaxes of DDL, DML, DCL, TCL with example.
• Data Definition Language(DDL)
DDL or Data Definition Language actually consists of the SQL commands that can be used
to define the database schema. It simply deals with descriptions of the database schema
and is used to create and modify the structure of database objects in the database. DDL
is a set of SQL commands used to create, modify, and delete database structures but not
data. These commands are normally not used by a general user, who should be
accessing the database via an application.
List of DDL commands:
1-CREATE: This command is used to create the database or its objects (like table, index,
function, views, store procedure, and triggers).
Syntax:
CREATE TABLE TableName (
Column1 DataType,
Column2 DataType,
...
);
Example:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2)
);
2-DROP: This command is used to delete objects from the database.
Syntax:
DROP TABLE TableName;

Example:
DROP TABLE Employees;
3-ALTER: This is used to alter the structure of the database.
Syntax:
ALTER TABLE TableName
ADD COLUMN NewColumn DataType;
 Example:
ALTER TABLE Employees
ADD COLUMN Department VARCHAR(50);
4-TRUNCATE: This is used to remove all records from a table, including all spaces
allocated for the records are removed.
Syntax:
TRUNCATE TABLE TableName;
Example:
TRUNCATE TABLE Employees;
5-COMMENT: This is used to add comments to the data dictionary.
6-RENAME: This is used to rename an object existing in the database.
• Data Manipulation Language(DML)
The SQL commands that deal with the manipulation of data present in the database belong
to DML or Data Manipulation Language and this includes most of the SQL statements.
It is the component of the SQL statement that controls access to data and to the
database. Basically, DCL statements are grouped with DML statements.
List of DML commands:
1-INSERT: It is used to insert data into a table.
Syntax:
INSERT INTO TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
Example:
INSERT INTO Employees (FirstName, LastName, Salary)
VALUES ('John', 'Doe', 60000);
2-UPDATE: It is used to update existing data within a table.
Syntax:
UPDATE TableName
SET Column1 = Value1, Column2 = Value2
WHERE Condition;
Example:
UPDATE Employees
SET Salary = 65000
WHERE LastName = 'Doe';
3-DELETE: It is used to delete records from a database table.
Syntax:
 DELETE FROM TableName
WHERE Condition;
Example:
DELETE FROM Employees
WHERE Salary < 50000;
4-LOCK: Table control concurrency.
5-CALL: Call a PL/SQL or JAVA subprogram.
6-EXPLAIN PLAN: It describes the access path to data.
• Data Control Language(DCL)
DCL includes commands such as GRANT and REVOKE which mainly deal with the
rights, permissions, and other controls of the database system
List of DCL commands:
1-GRANT: This command gives users access privileges to the database.
Syntax:
GRANT Permission
ON TableName
TO User;
Example:
GRANT SELECT, INSERT
ON Employees
TO Manager;
2-REVOKE: This command withdraws the user’s access privileges given by using the
GRANT command.
Syntax:
REVOKE Permission
ON TableName
FROM User;
Example:
REVOKE INSERT
ON Employees
FROM Manager;
• Transaction Control Language(TCL)
Transactions group a set of tasks into a single execution unit. Each transaction begins with
a specific task and ends when all the tasks in the group are successfully completed. If
any of the tasks fail, the transaction fails. Therefore, a transaction has only two results:
 success or failure. You can explore more about transactions here. Hence, the following
TCL commands are used to control the execution of a transaction:
List of TCL commands:
1-BEGIN: Opens a Transaction.
2-COMMIT: Commits a Transaction.
Syntax:
COMMIT;
3-ROLLBACK: Rollbacks a transaction in case of any error occurs.
Syntax:
ROLLBACK;
4-SAVEPOINT: Sets a save point within a transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;
 Experiment-1
Objective: DDL Statement: Create Command (For Hospital Management System Schema)
Step 1: E-R Diagram

Step 2: Converting the E-R Diagram into Tables

Converting entity to table and attribute to columns

Hospital
 HCity

HAddress

Hos-Name

Pat-id Foreign key references to Pat-id of Patient table

Doc-id Foreign key references to Doc-id of Doctor table

Patient

Pat-id Primary Key

PName

PAddress

PDiagnosis

Record-id Foreign key references to Record-id of Medical Record table

 Pat-id Primary Key

Hosp-id Foreign key references to Hosp-id of Hospital table

Medical Record

Record-id Primary Key

Problem

Date_of_examination

Pat-id Foreign key references to Pat-id of Patient table

Doctor

Doc-id Primary Key

DName
 Doc-id Primary Key

Qualification

Salary

Hosp-id Foreign key references to Hosp-id of Hospital table

Step 3: Mapping of Attributes

• Simple Attributes

Simple Attributes which can not be divided into subparts.

Example: Salary of Doctor

• Composite Attributes

Composite Attributes which can be divided into subparts.

Example: Patient Name, Doctor Name

Step 4: Mapping of Relationships

Hosp_patient

Pat-id Hospital table makes foreign key references to Pat-id of Patient table

Hosp-id Patient table makes foreign key references to Hosp-id of Hospital table

Hosp_Doctor

Hosp-id Doctor table makes foreign key references to Hosp-id of Hospital table

Doc-id Hospital table makes foreign key references to Doc-id of Doctor table

Patient_MedicalRecord

Pat-id Medical Record table makes foreign key references to Pat-id of Patient table

Record-id Patient table makes foreign key references to Record-id of Medical Record table

Step 5: Identifying the relationships

a. Hospital has a set of patients.

Therefore the relations is 1……..N.

b. Hospital has a set of doctors.

Therefore the relations is 1……..N.

c. Doctor are associated with each patient.

Therefore the relations is N……..1.

d. Each patient has record of various test and examination conducted.

Therefore the relations is 1……..N.

 Experiment- 2
Objective: DDL Statement: ALTER, DROP and TRUNCATE
1. Create the tables as per schema:-
CREATE TABLE DEPARTMENT (
D_NAME varchar2(50),
D_LOCATION varchar2(20),
FACILITIES varchar2(50))

CREATE TABLE DOCTOR (

DOC_ID varchar2(5),
DEPARTMENT varchar2(50))

CREATE TABLE PATIENT (

P_ID varchar2(5),
P_NAME varchar2(50),
P_AGE number(2),
P_SEX varchar2(1),
P_ADDRESS varchar2(50),
P_CITY varchar2(20),
P_CONTACT number(10),
P_CHECKUP_DATE date,
P_DIAGNOSIS varchar2(50),
P_REFDOC varchar2(5),
DEPARTMENT varchar2(50))

CREATE TABLE ROOM_DETAILS (

ROOM_NO number(5),
R_TYPE varchar2(10),
R_STATUS varchar2(1),
P_ID varchar2(5),
DAILY_CHARGE number(5))
CREATE TABLE DOC_REG (
DOC_ID varchar2(50),
DOC_NAME varchar2(50),
QUALIFICATION varchar2(20),
SALARY number(10),
CONTACT_NO number(10),
DOJ date)

CREATE TABLE DOC_ON_CALL (

DOC_ID varchar2(50),
DOC_NAME varchar2(50),
QUALIFICATION varchar2(20),
FEE_PER_CALL number(5),
CONTACT_NO number(10))
CREATE TABLE PAT_CHECKUP (
P_ID varchar2(5),
DOC_ID varchar2(50),
P_DIAGNOSIS varchar2(50),
STATUS varchar2(25),
TREATMENT varchar2(50))

CREATE TABLE PAT_ADMIT (

P_ID varchar2(5),
DOC_ID varchar2(50),
P_DIAGNOSIS varchar2(50),
STATUS varchar2(25),
TREATMENT varchar2(50),
ADMT_ON date,
ROOM_NO number(5))

CREATE TABLE PAT_DISCHRG (

P_ID varchar2(5),
DOC_ID varchar2(50),
P_DIAGNOSIS varchar2(50),
TRMNT_GVN varchar2(50),
PYMNT number(10),
DSCHRG_ON date)

CREATE TABLE PAT_REG (

P_ID varchar2(5),
DATE_VISIT date,
DIAGNOSIS varchar2(50),
TREATMENT varchar2(50),
MEDICINES_RECMND varchar2(50))

CREATE TABLE PAT_OPR (

P_ID varchar2(5),
DOC_ID varchar2(50),
DATE_ADMIT date,
DATE_OPR date,
OPRTN_TYPE varchar2(5),
OPTH_NO varchar2(5))
2. Apply the alter command to make the following changes to the tables:-
a) Add constraint for Department Table: d_name as primary key.\
ALTER TABLE Department
ADD CONSTRAINT PK_Department PRIMARY KEY (d_name);

b) Add constraint for Department and Doctor Table: doc_id as primary key constraint in Doctor
Table and department as foreign key constraint referencing d_name from Department Table.
ALTER TABLE Doctor
ADD CONSTRAINT PK_Doctor PRIMARY KEY (doc_id);
ALTER TABLE Doctor
ADD CONSTRAINT FK_Doctor_Department FOREIGN KEY (department)
REFERENCES Department(d_name);

c) Add constraint for Patient Table: p_id as primary key constraint, department as foreign key
constraint referencing d_name from Department Table and p_refdoc as foreign key constraint
referencing doc_id from Doctor Table.
ALTER TABLE Patient
ADD CONSTRAINT PK_Patient PRIMARY KEY (p_id);
ALTER TABLE Patient
ADD CONSTRAINT FK_Patient_Department FOREIGN KEY (department)
REFERENCES Department(d_name);
ALTER TABLE Patient
 ADD CONSTRAINT FK_Patient_Doctor FOREIGN KEY (p_refdoc) REFERENCES
Doctor(doc_id);

d) Implement a constraint in Pat_dischrg table to ensure the Payment cannot be Null.

ALTER TABLE Pat_dischrg
ADD CONSTRAINT CHK_Pat_dischrg_Payment_Not_Null CHECK (Payment IS NOT
NULL);

e) Implement a constraint in Room_details table so that Room status is either ‘Y’ or ‘N’.
ALTER TABLE Room_details
ADD CONSTRAINT CHK_Room_details_RoomStatus CHECK (RoomStatus IN ('Y', 'N'));

f) Implement a constraint in Pat_admit table to make Admt_on as Not Null.

ALTER TABLE Pat_admit
ADD CONSTRAINT CHK_Pat_admit_Admt_on_Not_Null CHECK (Admt_on IS NOT
NULL);

g) Alter table Doc_on_call table so that fee per call cannot be more than 2000.

ALTER TABLE Doc_on_call

ADD CONSTRAINT CHK_Doc_on_call_fee_per_call CHECK (fee_per_call <= 2000);

h) Implement a constraint in Doc_reg table to make date of joining cannot be before 2000.
ALTER TABLE Doc_reg
ADD CONSTRAINT CHK_Doc_reg_Date_of_Joining CHECK (date_of_joining >= '2000-
01-01');

i) Implement a constraint on Doc_on_call to make qualification MBBS as default.

ALTER TABLE Doc_on_call
ALTER COLUMN qualification SET DEFAULT 'MBBS';

j) Modify Patient Details to make Contact Number and Age as Not Null.
ALTER TABLE PatientDetails
ALTER COLUMN ContactNumber SET NOT NULL,
ALTER COLUMN Age SET NOT NULL;
3. Write a drop command to drop a table.
To drop a table in SQL, you can use the DROP TABLE statement followed by the table
name. Here's an example for dropping the DEPARTMENT table:
-- Drop the 'DEPARTMENT' table
DROP TABLE DEPARTMENT;
You can use a similar command to drop any other table by replacing the table name
accordingly. For example, to drop the DOCTOR table:
-- Drop the 'DOCTOR' table
DROP TABLE DOCTOR;
Repeat this pattern for each table you want to drop. Be cautious when using the DROP
TABLE command, as it permanently removes the table and all its data. Ensure that you
have appropriate backups or that you really want to delete the table before executing this
command, especially in a production environment
 Experiment-3
Objective: DML: Insert, update, delete using with, Select using between, or and not operator.

1. Perform the following insert commands to fill the records in the created tables.
INSERT ALL
into Department values ('Anaesthesia','Floor 1','Critical Care & Pain Management')
into Department values ('Cardiac','Floor 2','Surgery')
into Department values ('Diagnostics','Floor 3','Diagnosis')
into Department values ('ENT','Floor 4','Medicine')
into Department values ('General Surgery','Floor 5','Surgery')
into Department values ('Neuro Sciences','Floor 6','Diagnosis with Surgery')
into Department values ('Physiotherapy','Floor 7','Critical care & Pain Management')
into Department values ('Psychiatry','Floor 8','Diagnosis')
into Department values ('Orthopaedic','Floor 9','Critical Care & Pain Management')
into Department values ('Pulmonary','Floor 10','Reserved')
into Department values ('General','Floor 11','Reserved')
SELECT * from DUAL

INSERT ALL
into Doctor values ('DR01','Anaesthesia')
into Doctor values ('DR02','Anaesthesia')
into Doctor values ('DC01','Anaesthesia')
into Doctor values ('DC02','Anaesthesia')
into Doctor values ('DR03','Cardiac')
into Doctor values ('DR04','Cardiac')
into Doctor values ('DC03','Cardiac')
into Doctor values ('DC04','Cardiac')
into Doctor values ('DR05','Diagnostics')
into Doctor values ('DR06','Diagnostics')
into Doctor values ('DC05','Diagnostics')
into Doctor values ('DC06','Diagnostics')
into Doctor values ('DR07','ENT')
into Doctor values ('DR08','ENT')
into Doctor values ('DC07','ENT')
into Doctor values ('DC08','ENT')
into Doctor values ('DR09','General Surgery')
into Doctor values ('DR10','General Surgery')
into Doctor values ('DC09','General Surgery')
into Doctor values ('DC10','General Surgery')
into Doctor values ('DR11','Neuro Sciences')
into Doctor values ('DR12','Neuro Sciences')
into Doctor values ('DC11','Neuro Sciences')
into Doctor values ('DC12','Neuro Sciences')
into Doctor values ('DR13','Physiotherapy')
into Doctor values ('DR14','Physiotherapy')
into Doctor values ('DC13','Physiotherapy')
into Doctor values ('DC14','Physiotherapy')
into Doctor values ('DR15','Psychiatry')
into Doctor values ('DR16','Psychiatry')
into Doctor values ('DC15','Psychiatry')
into Doctor values ('DC16','Psychiatry')
into Doctor values ('DR17','Orthopaedic')
into Doctor values ('DR18','Orthopaedic')
into Doctor values ('DC17','Orthopaedic')
into Doctor values ('DC18','Orthopaedic')
into Doctor values ('DR19','Pulmonary')
into Doctor values ('DR20','Pulmonary')
into Doctor values ('DC19','Pulmonary')
into Doctor values ('DC20','Pulmonary')
SELECT * from DUAL

INSERT ALL
into Patient values ('PT001','AAA',35,'M','Civil Lines','Kanpur',9080706051,'01-JUN-2016','Cardiac
Problem','DR03','Cardiac')
into Patient values ('PT002','AAB',40,'F','Kalyanpur','Kanpur',9080706052,'02-JUN-2016','Physio
Problem','DR13','Physiotherapy')
into Patient values ('PT003','AAC',45,'M','Parade','Kanpur',9080706053,'01-JUN-2016','ENT
Problem','DC13','ENT')
into Patient values ('PT004','AAD',50,'F','Rawatpur','Kanpur',9080706054,'02-JUN-
2016','Diagnostics Problem','DR05','Diagnostics')
into Patient values ('PT005','AAE',55,'M','Harjinder Nagar','Kanpur',9080706055,'03-JUN-
2016','Neuro Problem','DR11','Neuro Sciences')
into Patient values ('PT006','BAA',35,'M','Civil Lines','Lucknow',9080706061,'01-JUN-2016','Ortho
Problem','DC17','Orthopaedic')
into Patient values ('PT007','BAB',40,'F','Charbagh','Lucknow',9080706062,'02-JUN-
2016','Surgery','DC09','General Surgery')
into Patient values ('PT008','BAC',45,'M','Alambagh','Lucknow',9080706063,'01-JUN-2016','ENT
Problem','DC13','ENT')
into Patient values ('PT009','BAD',50,'F','Gomti Nagar','Lucknow',9080706064,'02-JUN-
2016','Surgery','DR09','General Surgery')
into Patient values ('PT010','BAE',55,'M','Hazrat Ganj','Lucknow',9080706065,'03-JUN-2016','Neuro
Problem','DR11','Neuro Sciences')
into Patient values ('PT011','CAA',35,'M','Civil Lines','New Delhi',9080706071,'01-JUN-
2016','Ortho Problem','DC17','Orthopaedic')
into Patient values ('PT012','CAB',40,'F','Charbagh','New Delhi',9080706072,'02-JUN-
2016','Surgery','DC09','General Surgery')
into Patient values ('PT013','CAC',45,'M','Alambagh','New Delhi',9080706073,'01-JUN-2016','ENT
Problem','DC13','ENT')
into Patient values ('PT014','DAD',50,'F','Gomti Nagar','New Delhi',9080706074,'02-JUN-
2016','Surgery','DR09','General Surgery')
into Patient values ('PT015','DAE',55,'M','Hazrat Ganj','New Delhi',9080706075,'03-JUN-
2016','Neuro Problem','DR11','Neuro Sciences')
SELECT * from DUAL

INSERT ALL
into ROOM_DETAILS values (101,'P AC','N','PT001',5000)
into ROOM_DETAILS values (102,'P AC','N','PT003',5000)
into ROOM_DETAILS values (103,'P AC','N','PT010',5000)
into ROOM_DETAILS values (104,'P AC','N','PT019',5000)
into ROOM_DETAILS values (105,'P AC','N','PT020',5000)
into ROOM_DETAILS values (201,'P NON-AC','N','PT007',3000)
into ROOM_DETAILS values (202,'P NON-AC','N','PT005',3000)
into ROOM_DETAILS values (203,'P NON-AC','Y','',3000)
into ROOM_DETAILS values (204,'P NON-AC','Y','',3000)
into ROOM_DETAILS values (205,'P NON-AC','Y','',3000)
into ROOM_DETAILS values (301,'G AC','N','PT009',3000)
into ROOM_DETAILS values (302,'G AC','N','PT012',3000)
into ROOM_DETAILS values (303,'G AC','N','PT014',3000)
into ROOM_DETAILS values (304,'G AC','Y','',3000)
into ROOM_DETAILS values (305,'G AC','Y','',3000)
into ROOM_DETAILS values (401,'G NON-AC','Y','PT011',2000)
into ROOM_DETAILS values (402,'G NON-AC','Y','PT017',2000)
into ROOM_DETAILS values (403,'G NON-AC','Y','',2000)
into ROOM_DETAILS values (404,'G NON-AC','Y','',2000)
into ROOM_DETAILS values (405,'G NON-AC','Y','',2000)
SELECT * from DUAL

INSERT ALL
into DOC_REG values ('DR01','Dr. A','MD',80000,8090607011,'01-Jan-2004')
into DOC_REG values ('DR02','Dr. B','MD',60000,8090607012,'01-Mar-2007')
into DOC_REG values ('DR03','Dr. C','MBBS',100000,8090607013,'01-May-2008')
into DOC_REG values ('DR04','Dr. D','MBBS',120000,8090607014,'01-Jul-2010')
into DOC_REG values ('DR05','Dr. E','MD',50000,8090607015,'01-Sep-2006')
into DOC_REG values ('DR06','Dr. F','MD',60000,8090607016,'01-Nov-2012')
into DOC_REG values ('DR07','Dr. G','MBBS',80000,8090607017,'01-Feb-2010')
into DOC_REG values ('DR08','Dr. H','MBBS',90000,8090607018,'01-Apr-2013')
into DOC_REG values ('DR09','Dr. I','MBBS',120000,8090607019,'01-Jun-2010')
into DOC_REG values ('DR10','Dr. J','MBBS',150000,8090607020,'01-Aug-2015')
into DOC_REG values ('DR11','Dr. K','MBBS',140000,8090607031,'01-Oct-2012')
into DOC_REG values ('DR12','Dr. L','MD',120000,8090607032,'01-Dec-2010')
into DOC_REG values ('DR13','Dr. M','MBBS',80000,8090607033,'01-Nov-2011')
into DOC_REG values ('DR14','Dr. N','MD',60000,8090607034,'01-Aug-2014')
into DOC_REG values ('DR15','Dr. O','MD',60000,8090607035,'01-Apr-2015')
into DOC_REG values ('DR16','Dr. P','MD',60000,8090607036,'01-Jan-2010')
into DOC_REG values ('DR17','Dr. Q','MBBS',90000,8090607037,'01-Mar-2007')
into DOC_REG values ('DR18','Dr. R','MD',70000,8090607038,'01-Jun-2009')
into DOC_REG values ('DR19','Dr. S','MD',650000,8090607039,'01-Sep-2010')
into DOC_REG values ('DR20','Dr. T','MD',70000,8090607040,'01-Dec-2012')
SELECT * from DUAL

INSERT ALL
into DOC_ON_CALL values ('DC01','Dr. AT','MD',800,8090607051)
into DOC_ON_CALL values ('DC02','Dr. BS','MD',600,8090607052)
into DOC_ON_CALL values ('DC03','Dr. CR','MBBS',1000,8090607053)
into DOC_ON_CALL values ('DC04','Dr. DQ','MBBS',1200,8090607054)
into DOC_ON_CALL values ('DC05','Dr. EP','MD',500,8090607055)
into DOC_ON_CALL values ('DC06','Dr. FO','MD',600,8090607056)
into DOC_ON_CALL values ('DC07','Dr. GN','MBBS',800,8090607057)
into DOC_ON_CALL values ('DC08','Dr. HM','MBBS',900,8090607058)
into DOC_ON_CALL values ('DC09','Dr. IL','MBBS',1200,8090607059)
into DOC_ON_CALL values ('DC10','Dr. JK','MBBS',1500,8090607060)
into DOC_ON_CALL values ('DC11','Dr. KJ','MBBS',1400,8090607081)
into DOC_ON_CALL values ('DC12','Dr. LI','MD',1200,8090607082)
into DOC_ON_CALL values ('DC13','Dr. MH','MBBS',800,8090607083)
into DOC_ON_CALL values ('DC14','Dr. NG','MD',600,8090607084)
into DOC_ON_CALL values ('DC15','Dr. OF','MD',600,8090607085)
into DOC_ON_CALL values ('DC16','Dr. PE','MD',600,8090607086)
into DOC_ON_CALL values ('DC17','Dr. QD','MBBS',900,8090607087)
into DOC_ON_CALL values ('DC18','Dr. RC','MD',700,8090607088)
into DOC_ON_CALL values ('DC19','Dr. SB','MD',500,8090607089)
into DOC_ON_CALL values ('DC20','Dr. TA','MD',600,8090607090)
SELECT * from DUAL

INSERT ALL
into Pat_checkup values ('PT001','DR03','Cardiac Problem','Referred for Operation','Preliminary')
into Pat_checkup values ('PT002','DR13','Physio Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT003','DC13','ENT Problem','Admitted','Adviced Treatment')
into Pat_checkup values ('PT004','DR05','Diagnostics Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT005','DR11','Neuro Problem','Referred for Operation','Preliminary')
into Pat_checkup values ('PT006','DC17','Ortho Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT007','DC09','Surgery','Referred for Operation','Preliminary')
into Pat_checkup values ('PT008','DC13','ENT Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT009','DR09','Surgery','Referred for Operation','Preliminary')
into Pat_checkup values ('PT010','DR11','Neuro Problem','Admitted','Adviced Treatment')
into Pat_checkup values ('PT011','DR19','Pulmonary Problem','Admitted','Adviced Treatment')
into Pat_checkup values ('PT012','DC09','Surgery','Referred for Operation','Preliminary')
into Pat_checkup values ('PT013','DC13','ENT Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT014','DR09','Surgery','Referred for Operation','Preliminary')
into Pat_checkup values ('PT015','DR11','Neuro Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT016','DR19','Pulmonary Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT017','DR09','Surgery','Referred for Operation','Preliminary')
into Pat_checkup values ('PT018','DR18','Ortho Problem','RegularPatient','Regular Treatment')
into Pat_checkup values ('PT019','DR10','Surgery','Referred for Operation','Preliminary')
into Pat_checkup values ('PT020','DC12','Neuro Problem','Admitted','Adviced Treatment')
SELECT * from DUAL

INSERT ALL
into Pat_Admit values ('PT001','DR03','Cardiac Problem','Referred for Operation','Preliminary','31-
May-2016',101)
into Pat_Admit values ('PT003','DC13','ENT Problem','Admitted','Adviced Treatment','29-May-
2016',102)
into Pat_Admit values ('PT005','DR11','Neuro Problem','Referred for Operation','Preliminary','30-
May-2016',202)
into Pat_Admit values ('PT007','DC09','Surgery','Referred for Operation','Preliminary','30-May-
2016',201)
into Pat_Admit values ('PT009','DR09','Surgery','Referred for Operation','Preliminary','29-May-
2016',301)
into Pat_Admit values ('PT010','DR11','Neuro Problem','Admitted','Adviced Treatment','03-Jun-
2016',103)
into Pat_Admit values ('PT011','DR19','Pulmonary Problem','Admitted','Adviced Treatment','30-
May-2016',401)
into Pat_Admit values ('PT012','DC09','Surgery','Referred for Operation','Preliminary','31-May-
2016',302)
into Pat_Admit values ('PT014','DR09','Surgery','Referred for Operation','Preliminary','30-May-
2016',303)
into Pat_Admit values ('PT017','DR09','Surgery','Referred for Operation','Preliminary','31-May-
2016',402)
into Pat_Admit values ('PT019','DR10','Surgery','Referred for Operation','Preliminary','31-May-
2016',104)
into Pat_Admit values ('PT020','DC12','Neuro Problem','Admitted','Adviced Treatment','30-May-
2016',105)
SELECT * from DUAL

INSERT ALL
into Pat_Reg values ('PT001','21-Feb-2016','Cardiac Problem','Preliminary','Regular')
into Pat_Reg values ('PT002','31-Mar-2016','Physio Problem','RegularTreatment','Basic')
into Pat_Reg values ('PT003','12-Feb-2016','ENT Problem','AdvicedTreatment','Basic')
into Pat_Reg values ('PT004','05-Apr-2016','Diagnostics Problem','RegularTreatment','Basic')
into Pat_Reg values ('PT005','11-May-2016','Neuro Problem','Preliminary','Regular')
into Pat_Reg values ('PT006','17-May-2016','Ortho Problem','RegularTreatment','Regular')
into Pat_Reg values ('PT007','30-May-2016','Surgery','Preliminary','Cured')
into Pat_Reg values ('PT008','13-Mar-2016','ENT Problem','RegularTreatment','Regular')
into Pat_Reg values ('PT009','19-May-2016','Surgery','Preliminary','Cured')
into Pat_Reg values ('PT010','11-May-2016','Neuro Problem','AdvicedTreatment','Regular')
into Pat_Reg values ('PT011','19-Feb-2016','Pulmonary Problem','AdvicedTreatment','Basic')
into Pat_Reg values ('PT012','30-May-2016','Surgery','Preliminary','Cured')
into Pat_Reg values ('PT013','13-Apr-2016','ENT Problem','RegularTreatment','Basic')
into Pat_Reg values ('PT014','27-May-2016','Surgery','Preliminary','Cured')
into Pat_Reg values ('PT015','11-Mar-2016','Neuro Problem','RegularTreatment','Regular')
into Pat_Reg values ('PT016','19-Apr-2016','Pulmonary Problem','RegularTreatment','Basic')
into Pat_Reg values ('PT017','29-May-2016','Surgery','Preliminary','Cured')
into Pat_Reg values ('PT018','18-May-2016','Ortho Problem','RegularTreatment','Regular')
into Pat_Reg values ('PT019','31-May-2016','Surgery','Preliminary','Cured')
into Pat_Reg values ('PT020','02-Jun-2016','Neuro Problem','AdvicedTreatment','Regular')
SELECT * from DUAL

INSERT ALL
into Pat_Oprtn values ('PT001','DR03','31-May-16','01-Jun-2016','Major','OT 1')
into Pat_Oprtn values ('PT005','DR11','30-May-16','01-Jun-2016','Major','OT 2')
into Pat_Oprtn values ('PT007','DC09','30-May-16','02-Jun-2016','Minor','OT 1')
into Pat_Oprtn values ('PT009','DR09','29-May-16','31-May-2016','Minor','OT 2')
into Pat_Oprtn values ('PT012','DC09','31-May-16','02-Jun-2016','Minor','OT 1')
into Pat_Oprtn values ('PT014','DR09','30-May-16','03-Jun-2016','Major','OT 2')
into Pat_Oprtn values ('PT017','DR09','31-May-16','03-Jun-2016','Minor','OT 1')
into Pat_Oprtn values ('PT019','DR10','31-May-16','31-May-2016','Major','OT 2')
SELECT * from DUAL

INSERT ALL
into Pat_Dischrg values ('PT001','DR03','Cardiac Problem','Regular',154165,'06-Jun-16')
into Pat_Dischrg values ('PT003','DC13','ENT Problem','Short Term',52304,'04-Jun-16')
into Pat_Dischrg values ('PT005','DR11','Neuro Problem','Long Term',184234,'05-Jun-16')
into Pat_Dischrg values ('PT007','DC09','Surgery','Short Term',41652,'05-Jun-16')
into Pat_Dischrg values ('PT009','DR09','Surgery','Regular',35485,'03-Jun-16')
into Pat_Dischrg values ('PT010','DR11','Neuro Problem','Regular',166168,'07-Jun-16')
into Pat_Dischrg values ('PT011','DR19','Pulmonary Problem','Long Term',55262,'03-Jun-16')
into Pat_Dischrg values ('PT012','DC09','Surgery','Short Term',54316,'04-Jun-16')
into Pat_Dischrg values ('PT014','DR09','Surgery','Long Term',124285,'08-Jun-16')
into Pat_Dischrg values ('PT017','DR09','Surgery','Short Term',34165,'06-Jun-16')
into Pat_Dischrg values ('PT019','DR10','Surgery','Long term',24165,'02-Jun-16')
into Pat_Dischrg values ('PT020','DC12','Neuro Problem','Regular',124784,'04-Jun-16')
SELECT * from DUAL
)
2. Write a query to update a record of Department table from General to Medicine where
location of department is at Floor 11.
UPDATE DEPARTMENT
SET FACILITIES = 'Medicine'
WHERE D_LOCATION = 'Floor 11';
3. Write a query to delete a record of Department table where location of department is at
Floor 11.
DELETE FROM DEPARTMENT
WHERE D_LOCATION = 'Floor 11';
 Experiment-4
Objective: Use of Operators and Aggregate function (min, max, sum, count and average)
In database management systems (DBMS), aggregate functions are used to perform a calculation on
a set of values and return a single value. Here are some common aggregate functions:
• MIN(): Returns the minimum value in a set.
Example:
SELECT MIN(salary) FROM employees;
This query returns the minimum salary from the "employees" table.

• MAX(): Returns the maximum value in a set.

Example:
SELECT MAX(salary) FROM employees;
This query returns the maximum salary from the "employees" table.

• SUM(): Returns the sum of all values in a set.

Example:
SELECT SUM(sales) FROM transactions;
This query returns the total sales from the "transactions" table.

• COUNT(): Returns the number of rows in a set. It can also be used with a specific column to
count non-null values.
Examples:
-- Count all rows in a table
SELECT COUNT(*) FROM students;

-- Count the number of non-null grades

SELECT COUNT(grade) FROM students;
• AVG(): Returns the average value of a numeric column.
Example:
SELECT AVG(age) FROM employees;
This query returns the average age of employees from the "employees" table.
These aggregate functions are often used to generate summary statistics and insights from databases.
They can also be combined with the GROUP BY clause to perform aggregate calculations for groups
of rows based on the values in one or more columns. Here's an example using GROUP BY:
SELECT department, AVG(salary), MAX(salary), MIN(salary), COUNT(*)
FROM employees
GROUP BY department;
This query calculates average, maximum, minimum salary, and the number of employees for each
department in the "employees" table.
 Experiment 5
Objective: In SQL, the GROUP BY, ORDER BY, and HAVING clauses are used to manipulate and
organize the results of a query. Here's a brief overview of each:
• GROUP BY: The GROUP BY clause is used to group rows that have the same values in
specified columns into summary rows.
It is often used with aggregate functions like SUM, AVG, COUNT, etc., to perform calculations on
each group of rows.
Example:
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
This query groups employees by department and calculates the average salary for each department.
• ORDER BY: The ORDER BY clause is used to sort the result set based on one or more
columns in ascending (ASC) or descending (DESC) order.
Example:
SELECT employee_id, first_name, last_name
FROM employees
ORDER BY last_name ASC, first_name ASC;
This query retrieves employee names and IDs from the "employees" table, ordered by last name in
ascending order and, in case of ties, by first name in ascending order.
• HAVING: The HAVING clause is used to filter the results of a GROUP BY query based on
a condition applied to the grouped data. It is similar to the WHERE clause but operates on the
results of aggregate functions.
Example:
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
This query first groups employees by department and then filters the results to include only those
departments where the average salary is greater than 50,000
 Experiment 6
Objective: JOIN and different types join operations

In the context of databases and relational database management systems (RDBMS), "JOIN"
operations are used to combine rows from two or more tables based on a related column between
them. There are different types of JOIN operations, each serving a specific purpose. Here are some
common types of JOIN operations:

• INNER JOIN: Returns only the rows where there is a match in both tables based on the
specified condition.
Syntax:
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
• LEFT JOIN (or LEFT OUTER JOIN): Returns all the rows from the left table and the
matching rows from the right table. If there is no match, NULL values are returned for
columns from the right table.
Syntax:
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all the rows from the right table and
the matching rows from the left table. If there is no match, NULL values are returned for
columns from the left table.
Syntax:
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
• FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either
the left or the right table. If there is no match, NULL values are returned for columns from
the table without a match.
Syntax:
SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column;
• CROSS JOIN: Returns the Cartesian product of the two tables, i.e., all possible
combinations of rows. It does not require a specific condition.
Syntax:
SELECT * FROM table1 CROSS JOIN table2;
• SELF JOIN: Joins a table with itself. Useful when dealing with hierarchical data or when
comparing rows within the same table.\
Syntax:
SELECT * FROM table1 t1, table1 t2 WHERE t1.column = t2.column;
 • NATURAL JOIN: Performs a JOIN using all columns with the same name in both tables. It
automatically matches columns with identical names without the need to specify them in the
query.
Syntax:
SELECT * FROM table1 NATURAL JOIN table2;
Understanding these different JOIN operations is crucial for efficiently querying and retrieving data
from relational databases, depending on the specific requirements of your queries.
 Experiment-7
Objective: Use of Sub Query
1. Display the name and ID of the regular doctors of each department.
SELECT
d.doctor_id,
d.doctor_name,
d.doctor_department_id,
dd.department_name
FROM
Doctors d
JOIN
Departments dd ON d.doctor_department_id = dd.department_id
WHERE
d.is_regular = 1;
2. Display the name and ID of the regular doctors according to the descending order of
their date of joining.
SELECT
d.doctor_id,
d.doctor_name,
d.doctor_department_id,
dd.department_name
FROM
Doctors d
JOIN
Departments dd ON d.doctor_department_id = dd.department_id
WHERE
d.is_regular = 1
ORDER BY
d.date_of_joining DESC;
3. Find the name and ID of doctors of a particular area of specialization which can be
called.
SELECT
doctor_id,
doctor_name
FROM
 Doctors
WHERE
specialization = 'YourSpecialization';
4. Display the name and ID of doctors as per their department and salary.
SELECT
d.doctor_id,
d.doctor_name,
d.doctor_department_id,
dd.department_name
FROM
Doctors d
JOIN
Departments dd ON d.doctor_department_id = dd.department_id
WHERE
d.salary BETWEEN MinSalary AND MaxSalary;
5. Display the name and ID of the regular doctors who have joined the hospital in the
2010.
SELECT
doctor_id,
doctor_name
FROM
Doctors
WHERE
is_regular = 1
AND YEAR(date_of_joining) = 2010;
6. Find the name and patient ID of those patients that are admitted to the hospital.
SELECT
patient_id,
patient_name
FROM
Patients
WHERE
admission_status = 'Admitted';
7. Display the name and patient ID of those patients that are treated by 'Dr. I'.
SELECT
 p.patient_id,
p.patient_name
FROM
Patients p
JOIN
Appointments a ON p.patient_id = a.patient_id
JOIN
Doctors d ON a.doctor_id = d.doctor_id
WHERE
d.doctor_name = 'Dr. I';
8. Display the name and patient ID of those patients that are diagnosed a 'ENT Problem'.
SELECT
p.patient_id,
p.patient_name
FROM
Patients p
JOIN
Diagnoses d ON p.patient_id = d.patient_id
WHERE
d.diagnosis = 'ENT Problem';
9. Find the name and patient ID of those patients that are regular patient to the hospital.
SELECT
patient_id,
patient_name
FROM
Patients
WHERE
is_regular = 1;
10. Print the name and patient ID of those patients that are admitted to the hospital on '30-
May-2016'.
SELECT
patient_id,
patient_name
FROM
Patients
 WHERE
admission_date = '2016-05-30';
11. Display the name and patient ID of those male patients that have 'Ortho Problem’,
'Neuro Problem' and 'ENT Problem'.
SELECT
p.patient_id,
p.patient_name
FROM
Patients p
JOIN
Diagnoses d ON p.patient_id = d.patient_id
JOIN
Gender g ON p.gender_id = g.gender_id
WHERE
g.gender = 'Male'
AND d.diagnosis IN ('Ortho Problem', 'Neuro Problem', 'ENT Problem')
GROUP BY
p.patient_id, p.patient_name
HAVING
COUNT(DISTINCT d.diagnosis) = 3;
12. Display the name and patient ID of those patients that are treated by 'Dr. MH'.
SELECT
p.patient_id,
p.patient_name
FROM
Patients p
JOIN
Appointments a ON p.patient_id = a.patient_id
JOIN
Doctors d ON a.doctor_id = d.doctor_id
WHERE
d.doctor_name = 'Dr. MH';
13. Print the name and ID of the lowest and highest paid regular doctor.
-- Lowest Paid Regular Doctor
SELECT
 doctor_id,
doctor_name,
salary
FROM
Doctors
WHERE
is_regular = 1
ORDER BY
salary
LIMIT 1;

-- Highest Paid Regular Doctor

SELECT
doctor_id,
doctor_name,
salary
FROM
Doctors
WHERE
is_regular = 1
ORDER BY
salary DESC
LIMIT 1;
14. Find the name and ID of those patients whose is admitted to hospital having 'Neuro
Problem'.
SELECT
p.patient_id,
p.patient_name
FROM
Patients p
JOIN
Admissions a ON p.patient_id = a.patient_id
JOIN
Diagnoses d ON p.patient_id = d.patient_id
WHERE
 d.diagnosis = 'Neuro Problem';
15. Print the distinct number of diseases that are diagnosed in order.
SELECT DISTINCT
diagnosis
FROM
Diagnoses
ORDER BY
diagnosis;
SELECT
COUNT(DISTINCT diagnosis) AS distinct_diseases_count
FROM
Diagnoses;
16. Print the number of patients having 'Ortho Problem' in 2016.
SELECT
COUNT(DISTINCT p.patient_id) AS ortho_patients_count
FROM
Patients p
JOIN
Diagnoses d ON p.patient_id = d.patient_id
JOIN
Admissions a ON p.patient_id = a.patient_id
WHERE
d.diagnosis = 'Ortho Problem'
AND YEAR(a.admission_date) = 2016;
17. Display the names and ID of that patient that have ‘Minor’ type of operation and is
treated by 'Dr. I'.
SELECT
p.patient_id,
p.patient_name
FROM
Patients p
JOIN
Operations o ON p.patient_id = o.patient_id
JOIN
Appointments a ON o.appointment_id = a.appointment_id
 JOIN
Doctors d ON a.doctor_id = d.doctor_id
WHERE
o.operation_type = 'Minor'
AND d.doctor_name = 'Dr. I';
18. Find the ID number of the doctor who has been called up by at least 1 patient.
SELECT DISTINCT
doctor_id
FROM
Appointments;
19. Find the name and ID number of the doctor who has not been called up by any of the
patients.
SELECT
d.doctor_id,
d.doctor_name
FROM
Doctors d
LEFT JOIN
Appointments a ON d.doctor_id = a.doctor_id
WHERE
a.appointment_id IS NULL;
20. Find the name of the doctor and their contact number who has been called up by at
least 1 patient.
SELECT
d.doctor_name,
d.contact_number
FROM
Doctors d
JOIN
Appointments a ON d.doctor_id = a.doctor_id
GROUP BY
d.doctor_id, d.doctor_name, d.contact_number
HAVING
COUNT(a.appointment_id) >= 1;
21. Calculate and print the amount earned by the doctor 'Dr. MH'.
 SELECT
d.doctor_name,
SUM(a.fee) AS amount_earned
FROM
Doctors d
JOIN
Appointments a ON d.doctor_id = a.doctor_id
WHERE
d.doctor_name = 'Dr. MH'
GROUP BY
d.doctor_id, d.doctor_name;
22. Display department ID, department name, with the number of doctors associated with
them.
SELECT
d.department_id,
d.department_name,
COUNT(doctor_id) AS number_of_doctors
FROM
Doctors d
GROUP BY
d.department_id, d.department_name;
23. Find the name and ID number of the patients that have gone 'Major' type of operation
in Operation theatre 'OT 2'.
SELECT
p.patient_id,
p.patient_name
FROM
Patients p
JOIN
Operations o ON p.patient_id = o.patient_id
JOIN
OperationTheatres ot ON o.operation_theatre_id = ot.operation_theatre_id
WHERE
o.operation_type = 'Major'
AND ot.theatre_name = 'OT 2';
24. Find the name and ID number of the patients that are admitted in 'May' and
discharged in 'June'.
SELECT
patient_id,
patient_name
FROM
Patients
WHERE
MONTH(admission_date) = 5
AND MONTH(discharge_date) = 6;
25. Make the salary of those doctors 1.2 * salary who earn less than Rs. 70000.
UPDATE
Doctors
SET
salary = salary * 1.2
WHERE
salary < 70000;
 Experiment 8
Objective: Use of Co-Related sub queries.
1. Display the name and ID of the regular doctors according to the descending order of
their date of joining.
SELECT doctor_name, doctor_id
FROM doctors
WHERE doctor_type = 'Regular'
ORDER BY join_date DESC;
2. Find the name and ID of doctors of a particular area of specialization which can be
called.
SELECT doctor_name, doctor_id
FROM doctors
WHERE specialization = 'YourSpecialization';
3. Display the name and ID of doctors as per their department and salary.
SELECT doctor_name, doctor_id
FROM doctors
ORDER BY department, salary DESC;
4. Display the name and ID of the regular doctors who have joined the hospital in the
2010.
SELECT doctor_name, doctor_id
FROM doctors
WHERE doctor_type = 'Regular' AND YEAR(join_date) = 2010;
5. Display the name and patient ID of those patients that are treated by 'Dr. I'.
SELECT patients.patient_name, patients.patient_id
FROM patients
JOIN treatments ON patients.patient_id = treatments.patient_id
JOIN doctors ON treatments.doctor_id = doctors.doctor_id
WHERE doctors.doctor_name = 'Dr. I';
6. Display the name and patient ID of those patients that are diagnosed a 'ENT Problem'.
SELECT patients.patient_name, patients.patient_id
FROM patients
JOIN diagnoses ON patients.patient_id = diagnoses.patient_id
WHERE diagnoses.diagnosis = 'ENT Problem';
7. Display the name and patient ID of those male patients that have 'Ortho Problem’,
'Neuro Problem' and 'ENT Problem'.
SELECT p.patient_name, p.patient_id
FROM patients p
JOIN diagnoses d ON p.patient_id = d.patient_id
JOIN gender g ON p.patient_id = g.patient_id
WHERE g.gender = 'Male'
AND d.diagnosis IN ('Ortho Problem', 'Neuro Problem', 'ENT Problem')
GROUP BY p.patient_id, p.patient_name
HAVING COUNT(DISTINCT d.diagnosis) = 3;
8. Print the name and ID of the lowest and highest paid regular doctor.
-- Lowest Paid Regular Doctor
SELECT doctor_name AS lowest_paid_doctor_name, doctor_id AS
lowest_paid_doctor_id, salary AS lowest_salary
FROM doctors
WHERE doctor_type = 'Regular'
ORDER BY salary ASC
LIMIT 1;
-- Highest Paid Regular Doctor
SELECT doctor_name AS highest_paid_doctor_name, doctor_id AS
highest_paid_doctor_id, salary AS highest_salary
FROM doctors
WHERE doctor_type = 'Regular'
ORDER BY salary DESC
LIMIT 1;
9. Print the distinct number of diseases that are diagnosed in order.
SELECT DISTINCT disease
FROM diagnoses
ORDER BY disease;
10. Print the number of patients having 'Ortho Problem' in 2016.
SELECT COUNT(DISTINCT patient_id) AS number_of_patients
FROM diagnoses
WHERE diagnosis = 'Ortho Problem' AND YEAR(date_diagnosed) = 2016;
11. Display the names and ID of that patient that have ‘Minor’ type of operation and is
treated by 'Dr. I'.
SELECT patients.patient_name, patients.patient_id
FROM patients
JOIN treatments ON patients.patient_id = treatments.patient_id
JOIN operations ON treatments.operation_id = operations.operation_id
JOIN doctors ON treatments.doctor_id = doctors.doctor_id
WHERE operations.operation_type = 'Minor' AND doctors.doctor_name = 'Dr. I';
12. Find the name and ID number of the doctor who has not been called up by any of the
patients.
SELECT doctors.doctor_name, doctors.doctor_id
FROM doctors
LEFT JOIN treatments ON doctors.doctor_id = treatments.doctor_id
WHERE treatments.patient_id IS NULL;
13. Calculate and print the amount earned by the doctor 'Dr. MH'.
SELECT doctor_name, SUM(fee) AS total_earnings
FROM treatments
JOIN doctors ON treatments.doctor_id = doctors.doctor_id
WHERE doctors.doctor_name = 'Dr. MH'
GROUP BY doctor_name;
14. Find the name and ID number of the patients that have gone 'Major' type of operation
in Operation theatre 'OT 2'.
SELECT patients.patient_name, patients.patient_id
FROM patients
JOIN operations ON patients.patient_id = operations.patient_id
WHERE operations.operation_type = 'Major' AND operations.operation_theatre = 'OT 2';
15. Make the salary of those doctors 1.2 * salary who earn less than Rs. 70000.
UPDATE doctors
SET salary = salary * 1.2
WHERE salary < 70000;
 Experiment 9
Objective: Use of Basic PL/SQL
1. Write a PL/SQL code to print your name.
DECLARE
v_my_name VARCHAR2(50);
BEGIN
v_my_name := 'ChatGPT';
DBMS_OUTPUT.PUT_LINE('My name is:Ayushi ' || v_my_name);
END;
/
2.Write a PL/SQL code to find and average of three numbers.
DECLARE
v_num1 NUMBER := 10; -- Replace with your first number
v_num2 NUMBER := 20; -- Replace with your second number
v_num3 NUMBER := 30; -- Replace with your third number
v_average NUMBER;

BEGIN
-- Calculate the average
v_average := (v_num1 + v_num2 + v_num3) / 3;
-- Display the result
DBMS_OUTPUT.PUT_LINE('The average of ' || v_num1 || ', ' || v_num2 || ', and ' || v_num3 || ' is: '
|| v_average);
END;
/
3. Write a PL/SQL code to find factorial of a given number.
DECLARE
v_number NUMBER := 5; -- Replace with your desired number
v_factorial NUMBER := 1;

BEGIN
-- Calculate the factorial
FOR i IN 1..v_number LOOP
v_factorial := v_factorial * i;
END LOOP;
 -- Display the result
DBMS_OUTPUT.PUT_LINE('The factorial of ' || v_number || ' is: ' || v_factorial);
END;
/
4. Write a PL/SQL code to find simple interest.
DECLARE
v_principal NUMBER := 1000; -- Replace with the principal amount
v_rate NUMBER := 5; -- Replace with the rate of interest (in percentage)
v_time NUMBER := 2; -- Replace with the time (in years)
v_simple_interest NUMBER;

BEGIN
-- Calculate the simple interest
v_simple_interest := (v_principal * v_rate * v_time) / 100;

-- Display the result

DBMS_OUTPUT.PUT_LINE('The simple interest is: ' || v_simple_interest);
END;
/
5. Write a PL/SQL code to enter a record in a new table (EMP_New) from the existing table
(EMP).
DECLARE
v_emp_id EMP.emp_id%TYPE; -- Assuming emp_id is a column in EMP table
v_emp_name EMP.emp_name%TYPE; -- Assuming emp_name is a column in EMP table
v_salary EMP.salary%TYPE; -- Assuming salary is a column in EMP table
BEGIN
-- Replace the following query with your desired condition to select a record from EMP
SELECT emp_id, emp_name, salary
INTO v_emp_id, v_emp_name, v_salary
FROM EMP
WHERE emp_id = 101; -- Replace with the condition for the record you want to insert

-- Insert the record into EMP_New

INSERT INTO EMP_New (emp_id, emp_name, salary)
 VALUES (v_emp_id, v_emp_name, v_salary);

COMMIT; -- Commit the transaction

DBMS_OUTPUT.PUT_LINE('Record inserted successfully into EMP_New.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Record not found in EMP table.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
 Experiment 10
Objective: Use of PL/SQL Procedure, Cursor & Trigger
Question-1:
1) Create table rad_vals:
a) Add Following Attribute
Radius of type number
b) Insert some records in the above table
2) Create table Area:
a) Add following Attributes
Radius of type number with precision
➤ Area of type number with scale and precision
> Perimeter of type number with scale and precision
Using Cursors Find the area and perimeter of Circle and insert the value of radius and
calculated area and perimeter in the area table
-- Create table rad_vals
CREATE TABLE rad_vals (
Radius NUMBER
);
-- Insert some records into rad_vals
INSERT INTO rad_vals VALUES (5);
INSERT INTO rad_vals VALUES (8);
INSERT INTO rad_vals VALUES (10);

-- Create table Area

CREATE TABLE Area (
Radius NUMBER(10,2),
Circle_Area NUMBER(10,2),
Circle_Perimeter NUMBER(10,2)
);
-- Cursor to calculate area and perimeter and insert into Area table
DECLARE
v_radius rad_vals.Radius%TYPE;
v_area NUMBER(10,2);
v_perimeter NUMBER(10,2);
CURSOR c_rad_vals IS
 SELECT Radius
FROM rad_vals;
BEGIN
FOR rad_rec IN c_rad_vals LOOP
v_radius := rad_rec.Radius;
-- Calculate area and perimeter
v_area := 3.14159 * v_radius * v_radius;
v_perimeter := 2 * 3.14159 * v_radius;
-- Insert into Area table
INSERT INTO Area VALUES (v_radius, v_area, v_perimeter);
END LOOP;
COMMIT; -- Commit the transaction
DBMS_OUTPUT.PUT_LINE('Values inserted successfully into Area table.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Question-2:
Suppose we have the following table:
Create table mylog(
who varchar2(30),
logon_num number);
Write a procedure to keep track of how many times someone logged on to the DB.
When running, if user is already in table, increment logon_num. Otherwise, add user
into the table.
CREATE TABLE mylog (
who VARCHAR2(30),
logon_num NUMBER
);
CREATE OR REPLACE PROCEDURE logon_tracking(p_username VARCHAR2) IS
v_existing_user NUMBER;
BEGIN
-- Check if the user already exists in the table
SELECT COUNT(*) INTO v_existing_user
 FROM mylog
WHERE who = p_username;
-- If the user exists, increment logon_num; otherwise, add the user
IF v_existing_user > 0 THEN
UPDATE mylog
SET logon_num = logon_num + 1
WHERE who = p_username;
ELSE
INSERT INTO mylog (who, logon_num) VALUES (p_username, 1);
END IF;
COMMIT; -- Commit the transaction
DBMS_OUTPUT.PUT_LINE('Logon tracking updated for user: ' || p_username);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END logon_tracking;
/
BEGIN
logon_tracking('JohnDoe'); -- Replace with the actual username
END;
/

Question-3:
An HR system has an employee table that holds a row for each employee within the
company. Each record in the table has a manager field (mgr) that holds the id for the
employee's manager.
Write a trigger so that when a manager record is deleted, the mgr field of that manager's
employees is set to NULL.
In other words, implement the SQL statement as:
WHEN AN EMPLOYEE IS DELETED,
UPDATE employee
SET mgr = null
WHERE mgr = employee id of the deleted employee
CREATE OR REPLACE TRIGGER delete_manager_trigger
BEFORE DELETE ON employee
FOR EACH ROW
BEGIN
-- Update employees' mgr field to NULL when a manager record is deleted
UPDATE employee
SET mgr = NULL
WHERE mgr = :OLD.employee_id;
END delete_manager_trigger;
/