JNTUH - DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
Database Management Systems Laboratory
DBMS LABORATORY MANUAL
Supplement to Experiment 1
Additional Worked Examples on DDL & DML Commands
This supplement provides three additional, fully worked examples — a Library database, a Bank
Account database, and a Hospital Patient database — so that students can practice the same DDL
and DML commands from Experiment 1 (CREATE, DESC, ALTER, INSERT, SELECT, UPDATE,
DELETE, DROP, TRUNCATE) in new, unfamiliar contexts.
EXAMPLE 1 — Library Database (Books Table)
Step 1 — Create the table:
CREATE TABLE Library_Book
(
Book_ID NUMBER(5),
Title VARCHAR2(40),
Author VARCHAR2(30),
Category VARCHAR2(15),
Price NUMBER(7,2),
Available CHAR(1)
);
Output:
Table created.
Step 2 — View the structure:
DESC Library_Book;
Output:
Name Null? Type
------------------------------------------------
BOOK_ID NUMBER(5)
TITLE VARCHAR2(40)
AUTHOR VARCHAR2(30)
CATEGORY VARCHAR2(15)
PRICE NUMBER(7,2)
AVAILABLE CHAR(1)
Step 3 — Insert records:
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 1
INSERT INTO Library_Book VALUES
(1001, 'Database System Concepts', 'Silberschatz', 'Technical', 650.00, 'Y');
INSERT INTO Library_Book VALUES
(1002, 'The Alchemist', 'Paulo Coelho', 'Fiction', 299.00, 'Y');
INSERT INTO Library_Book VALUES
(1003, 'Wings of Fire', 'A.P.J. Abdul Kalam', 'Biography', 250.00, 'N');
Output:
1 row created.
1 row created.
1 row created.
Step 4 — Display all books:
SELECT * FROM Library_Book;
Output:
BOOK_ID TITLE AUTHOR CATEGORY PRICE AV
AILABLE
--------------------------------------------------------------------------------
-----
1001 Database System Concepts Silberschatz Technical 650.00 Y
1002 The Alchemist Paulo Coelho Fiction 299.00 Y
1003 Wings of Fire A.P.J. Abdul Kalam Biography 250.00 N
Step 5 — Add a new column for publication year:
ALTER TABLE Library_Book
ADD Pub_Year NUMBER(4);
Output:
Table altered.
Step 6 — Update availability after a book is returned:
UPDATE Library_Book
SET Available = 'Y'
WHERE Book_ID = 1003;
Output:
1 row updated.
Step 7 — Delete a book record that is discarded from the library:
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 2
DELETE FROM Library_Book
WHERE Book_ID = 1002;
Output:
1 row deleted.
Step 8 — Display only Title and Price for available books:
SELECT Title, Price
FROM Library_Book
WHERE Available = 'Y';
Output:
TITLE PRICE
-----------------------------------
Database System Concepts 650.00
Wings of Fire 250.00
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 3
EXAMPLE 2 — Bank Database (Account Table)
Step 1 — Create the table:
CREATE TABLE Bank_Account
(
Acc_No NUMBER(8),
Cust_Name VARCHAR2(30),
Acc_Type VARCHAR2(10),
Balance NUMBER(12,2),
Branch VARCHAR2(20),
IFSC_Code VARCHAR2(11)
);
Output:
Table created.
Step 2 — Insert account records:
INSERT INTO Bank_Account VALUES
(10023456, 'Anil Kumar', 'Savings', 25000.00, 'Hyderabad', 'SBIN0001234');
INSERT INTO Bank_Account VALUES
(10023457, 'Priya Sharma', 'Current', 150000.00, 'Warangal', 'SBIN0005678');
Output:
1 row created.
1 row created.
Step 3 — Display all accounts:
SELECT * FROM Bank_Account;
Output:
ACC_NO CUST_NAME ACC_TYPE BALANCE BRANCH IFSC_CODE
-----------------------------------------------------------------------------
10023456 Anil Kumar Savings 25000.00 Hyderabad SBIN0001234
10023457 Priya Sharma Current 150000.00 Warangal SBIN0005678
Step 4 — Deposit amount (increase balance) using UPDATE:
UPDATE Bank_Account
SET Balance = Balance + 5000
WHERE Acc_No = 10023456;
Output:
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 4
1 row updated.
• Notice that Balance = Balance + 5000 uses the existing column value in the expression — this is a
very common real-world UPDATE pattern for deposits and withdrawals.
Step 5 — Withdraw amount (decrease balance):
UPDATE Bank_Account
SET Balance = Balance - 2000
WHERE Acc_No = 10023457;
Output:
1 row updated.
Step 6 — Modify column size to store longer customer names:
ALTER TABLE Bank_Account
MODIFY Cust_Name VARCHAR2(50);
Output:
Table altered.
Step 7 — Close an account (delete the record):
DELETE FROM Bank_Account
WHERE Acc_No = 10023457;
Output:
1 row deleted.
Note: In real banking systems, DELETE is rarely used on account tables directly; accounts are usually
marked 'closed' using UPDATE instead, to preserve transaction history. This example uses DELETE purely
for command practice.
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 5
EXAMPLE 3 — Hospital Database (Patient Table)
Step 1 — Create the table:
CREATE TABLE Patient
(
Patient_ID NUMBER(6),
P_Name VARCHAR2(30),
Age NUMBER(3),
Gender CHAR(1),
Disease VARCHAR2(30),
Admit_Date DATE,
Doctor VARCHAR2(30)
);
Output:
Table created.
Step 2 — Insert patient records:
INSERT INTO Patient VALUES
(500001, 'Lakshmi Reddy', 45, 'F', 'Diabetes', '02-JUL-2026', 'Dr. Rao');
INSERT INTO Patient VALUES
(500002, 'Suresh Babu', 60, 'M', 'Hypertension', '05-JUL-2026', 'Dr. Menon');
INSERT INTO Patient VALUES
(500003, 'Kavya Sri', 8, 'F', 'Viral Fever', '10-JUL-2026', 'Dr. Rao');
Output:
1 row created.
1 row created.
1 row created.
Step 3 — List all patients treated by a specific doctor:
SELECT P_Name, Disease
FROM Patient
WHERE Doctor = 'Dr. Rao';
Output:
P_NAME DISEASE
----------------------------
Lakshmi Reddy Diabetes
Kavya Sri Viral Fever
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 6
Step 4 — Add a new column to track discharge status:
ALTER TABLE Patient
ADD Discharged CHAR(1);
Output:
Table altered.
Step 5 — Update discharge status for a recovered patient:
UPDATE Patient
SET Discharged = 'Y'
WHERE Patient_ID = 500003;
Output:
1 row updated.
Step 6 — Remove the record of a discharged patient from the active list:
DELETE FROM Patient
WHERE Discharged = 'Y';
Output:
1 row deleted.
Step 7 — Empty the entire table at the end of an academic demo (structure preserved):
TRUNCATE TABLE Patient;
Output:
Table truncated.
Summary of commands practiced across all three examples:
Command Used In Purpose
CREATE TABLE All 3 examples Define a new table structure
DESC Library example View column structure
INSERT INTO All 3 examples Add new rows of data
SELECT All 3 examples Retrieve / filter data
UPDATE All 3 examples Modify existing values, incl. Balance +/- expr.
ALTER TABLE ADD All 3 examples Add a new column
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 7
Command Used In Purpose
ALTER TABLE MODIFY Bank example Change a column's size
DELETE All 3 examples Remove specific rows
TRUNCATE TABLE Hospital example Clear all rows, keep structure
Note: For your lab record, repeat each of these three examples yourself in SQL*Plus, screenshot or note
the actual output, and compare it with what is shown here.
DBMS Lab Manual | Additional Worked Examples | JNTUH - CSE Page 8