Data Manipulation Language (DML)

DML stands for Data Manipulation Language. It is a subset of SQL (Structured Query
Language) that deals with modifying and interacting with data stored in a database. DML
provides commands to insert, update, delete, and retrieve data from tables.
DML statements require Transaction Control (TCL) since any modification made to the
database is considered a transaction. Hence, DML statements must be controlled using TCL
statements like COMMIT, ROLLBACK, and SAVEPOINT.
DML Statements in SQL:
1. INSERT – Adds new records to a table.
2. SELECT – Retrieves data from a table.
3. UPDATE – Modifies existing records.
4. DELETE – Removes records from a table.

INSERT Statement in SQL

The INSERT statement is used to add new records into a table.
Syntax:
INSERT INTO table_name (col1, col2, col3, … colN)
VALUES (val1, val2, val3, … valN);
 table_name → Name of the table where data is inserted.
 col1, col2, col3… → Column names where values will be inserted.
 val1, val2, val3… → Values corresponding to the respective columns.

Example:
INSERT INTO visitor (student_id, visitor_id, visitor_name, visitor_contact, visitor_relation,
checkin_time, checkout_time, purpose_of_visit)
VALUES
(1001, 1, 'Rajesh Kumar', '9876543210', 'Father', '2025-03-10 10:00:00', '2025-03-10 12:00:00',
'Dropping essentials'),
(1001, 2, 'Sunita Kumar', '9823456789', 'Mother', '2025-03-11 14:00:00', '2025-03-11 16:00:00', 'Health
check-up'),
(1002, 3, 'Amit Shah', '9812345678', 'Brother', '2025-03-08 11:00:00', '2025-03-08 13:00:00', 'Casual
visit'),
(1002, 4, 'Deepak Shah', '9876504321', 'Uncle', '2025-03-12 15:30:00', '2025-03-12 17:30:00', 'Bringing
study material'),
(1005, 5, 'Priya Sharma', '9845678901', 'Sister', '2025-03-10 09:30:00', '2025-03-10 11:30:00', 'Family
visit'),
(1006, 6, 'Ramesh Singh', '9807654321', 'Guardian', '2025-03-05 10:30:00', '2025-03-05 12:00:00', 'Fee
Payment');
select * from visitor;
SELECT Statement in SQL
The SELECT statement is used to retrieve data from a table in the database. It is the most
commonly used SQL statement.
Syntax to Retrieve All Columns:
SELECT * FROM table_name;
 * → Retrieves all columns from the table.
 table_name → Name of the table from which data is retrieved.
Syntax to Retrieve Specific Columns:
SELECT col1, col2, col3 FROM table_name;
 col1, col2, col3 → Specifies which columns to retrieve.
 table_name → Name of the table from which data is retrieved.
EXAMPLE:-
select * from visitor where visitor_name='Rajesh Kumar';
UPDATE Statement in SQL
The UPDATE statement is used to modify existing records in a table.
Syntax:
UPDATE table_name
SET col1 = val1, col2 = val2, col3 = val3
WHERE condition;
 table_name → Name of the table where records are updated.
 col1, col2, col3 → Column names to be updated.
 val1, val2, val3 → New values for the columns.
 WHERE condition → Specifies which records should be updated. (If omitted, all rows will be
updated, which may not be desirable.)
EXAMPLE:-
DELETE Statement in SQL
The DELETE statement is used to remove records from a table.
Syntax:
DELETE FROM table_name WHERE condition;
 table_name → Name of the table from which records will be deleted.
 WHERE condition → Specifies which records to delete. (If omitted, all records in the table will
be deleted.)
EXAMPLE:-
DELETE FROM visitor
WHERE visitor_id = 5;
SELECT * FROM VISITOR;
INSERT INTO room (room_no, room_type, rent_amount, rent_status, occupancy_status,
last_inspection_date, hostel_no)

VALUES

(101, 'NON AC', 7000, 'paid', 'occupied', '2025-03-01', 1),

(102, 'AC', 12000, 'paid', 'occupied', '2025-02-28', 1),

(103, 'NON AC', 7100, 'paid', 'occupied', '2025-03-05', 1),

(104, 'AC', 11800, 'paid', 'occupied', '2025-02-25', 1),

(105, 'NON AC', 7050, 'paid', 'occupied', '2025-03-10', 1),

(106, 'NON AC', 7000, 'not paid', 'not occupied', NULL, 1);
-- Rooms for Hostel 2

INSERT INTO room VALUES

(201, 'AC', 12000, 'paid', 'occupied', '2025-02-20', 2),

(202, 'NON AC', 7000, 'paid', 'occupied', '2025-03-03', 2),

(203, 'AC', 11900, 'paid', 'occupied', '2025-03-01', 2),

(204, 'NON AC', 7050, 'not paid', 'not occupied', NULL, 2),

(205, 'NON AC', 7000, 'not paid', 'not occupied', NULL, 2),

(206, 'NON AC', 7000, 'not paid', 'not occupied', NULL, 2);

-- Rooms for Hostel 3

INSERT INTO room VALUES

(301, 'NON AC', 7000, 'paid', 'occupied', '2025-02-27', 3),

(302, 'AC', 12000, 'paid', 'occupied', '2025-02-22', 3),

(303, 'NON AC', 7100, 'paid', 'occupied', '2025-03-04', 3),

(304, 'AC', 11800, 'paid', 'occupied', '2025-03-06', 3),

(305, 'NON AC', 7050, 'not paid', 'not occupied', NULL, 3),

(306, 'NON AC', 7000, 'not paid', 'not occupied', NULL, 3);

-- Rooms for Hostel 4

INSERT INTO room VALUES

(401, 'NON AC', 7000, 'paid', 'occupied', '2025-03-02', 4),

(402, 'AC', 12000, 'paid', 'occupied', '2025-02-24', 4),

(403, 'NON AC', 7100, 'paid', 'occupied', '2025-03-07', 4),

(404, 'AC', 11800, 'paid', 'occupied', '2025-03-09', 4),

(405, 'NON AC', 7050, 'not paid', 'not occupied', NULL, 4),

(406, 'NON AC', 7000, 'not paid', 'not occupied', NULL, 4);

-- Rooms for Hostel 5

INSERT INTO room VALUES

(501, 'NON AC', 7000, 'paid', 'occupied', '2025-02-26', 5),

(502, 'AC', 12000, 'paid', 'occupied', '2025-03-08', 5),

(503, 'NON AC', 7100, 'paid', 'occupied', '2025-02-21', 5),

(504, 'AC', 11800, 'paid', 'occupied', '2025-03-11', 5),

(505, 'NON AC', 7050, 'not paid', 'not occupied', NULL, 5),

(506, 'NON AC', 7000, 'not paid', 'not occupied', NULL, 5);

SELECT * FROM room;

-- Male Students in Hostels 1, 2, and 3

INSERT INTO student (student_id, student_name, gender, room_no, city, street, house_no)

VALUES

(1001, 'Rohan Sharma', 'male', 101, 'Mumbai', 'Linking Road', 12),

(1002, 'Amit Singh', 'male', 102, 'Mumbai', 'SV Road', 34),

(1003, 'Karan Patel', 'male', 103, 'Pune', 'FC Road', 56),

(1004, 'Rahul Joshi', 'male', 104, 'Mumbai', 'Andheri West', 78),

(1005, 'Arjun Mehta', 'male', 105, 'Thane', 'Ghodbandar Road', 90),

(1006, 'Yash Thakur', 'male', 201, 'Nashik', 'Gangapur Road', 45),

(1007, 'Rajesh Kumar', 'male', 202, 'Mumbai', 'Bandra East', 67),

(1008, 'Manish Verma', 'male', 203, 'Pune', 'JM Road', 11),

(1009, 'Sahil Kapoor', 'male', 301, 'Nagpur', 'Civil Lines', 23),

(1010, 'Ishaan Khanna', 'male', 302, 'Mumbai', 'Vile Parle', 89);

-- Female Students in Hostels 4 and 5

INSERT INTO student (student_id, student_name, gender, room_no, city, street, house_no)

VALUES

(1011, 'Pooja Desai', 'female', 401, 'Mumbai', 'Marine Lines', 13),

(1012, 'Nisha Gupta', 'female', 402, 'Mumbai', 'Borivali West', 25),

(1013, 'Kavita Nair', 'female', 403, 'Pune', 'MG Road', 47),

(1014, 'Ritika Sharma', 'female', 404, 'Mumbai', 'Powai', 60),

(1015, 'Meera Iyer', 'female', 501, 'Thane', 'Manpada', 72),

(1016, 'Shruti Reddy', 'female', 502, 'Mumbai', 'Malad East', 31),

(1017, 'Ankita Dutta', 'female', 503, 'Pune', 'Shivaji Nagar', 84),

(1018, 'Divya Menon', 'female', 504, 'Mumbai', 'Colaba', 19),

(1019, 'Sneha Pillai', 'female', 405, 'Nagpur', 'Dharampeth', 54),

(1020, 'Priya Shetty', 'female', 406, 'Mumbai', 'Churchgate', 40);

SELECT * FROM student;

-- Student Contact Details (Including Multiple Contacts for Some Students)

INSERT INTO student_contact (student_id, contact_no)

VALUES

(1001, '9876543210'),

(1001, '9123456789'), -- Additional contact for student 1001

(1002, '9876543211'),

(1003, '9876543212'),

(1004, '9876543213'),

(1004, '9321122334'), -- Additional contact for student 1004

(1005, '9876543214'),

(1006, '9876543215'),
(1006, '9000000000'), -- Additional contact for student 1006

(1007, '9876543216'),

(1008, '9876543217'),

(1009, '9876543218'),

(1010, '9876543219'),

(1011, '9876543220'),

(1011, '8888888888'), -- Additional contact for student 1011

(1012, '9876543221'),

(1013, '9876543222'),

(1014, '9876543223'),

(1015, '9876543224'),

(1015, '7777777777'), -- Additional contact for student 1015

(1016, '9876543225'),

(1017, '9876543226'),

(1018, '9876543227'),

(1019, '9876543228'),

(1020, '9876543229');

SELECT * FROM student_contact;

INSERT INTO visitor (student_id, visitor_id, visitor_name, visitor_contact, visitor_relation,

checkin_time, checkout_time, purpose_of_visit)

VALUES

(1001, 1, 'Rajesh Kumar', '9876543210', 'Father', '2025-03-10 10:00:00', '2025-03-10 12:00:00',

'Dropping essentials'),

(1001, 2, 'Sunita Kumar', '9823456789', 'Mother', '2025-03-11 14:00:00', '2025-03-11 16:00:00', 'Health
check-up'),

(1002, 3, 'Amit Shah', '9812345678', 'Brother', '2025-03-08 11:00:00', '2025-03-08 13:00:00', 'Casual
visit'),

(1002, 4, 'Deepak Shah', '9876504321', 'Uncle', '2025-03-12 15:30:00', '2025-03-12 17:30:00', 'Bringing
study material'),
(1005, 5, 'Priya Sharma', '9845678901', 'Sister', '2025-03-10 09:30:00', '2025-03-10 11:30:00', 'Family
visit'),

(1006, 6, 'Ramesh Singh', '9807654321', 'Guardian', '2025-03-05 10:30:00', '2025-03-05 12:00:00', 'Fee
Payment'),

(1006, 7, 'Asha Singh', '9887654321', 'Aunt', '2025-03-07 13:30:00', '2025-03-07 15:30:00', 'Delivering
parcels'),

(1010, 8, 'Anil Mehta', '9776543210', 'Family Friend', '2025-03-03 14:30:00', '2025-03-03 16:00:00',
'Meeting for career advice'),

(1012, 9, 'Vikram Gupta', '9765432100', 'Cousin', '2025-03-15 10:15:00', '2025-03-15 12:00:00', 'Friendly
visit'),

(1015, 10, 'Kavita Mehta', '9654321000', 'Mother', '2025-03-17 09:00:00', '2025-03-17 11:00:00', 'Health
check-up'),

(1015, 11, 'Rahul Mehta', '9543210001', 'Brother', '2025-03-18 13:00:00', '2025-03-18 15:00:00',
'Delivering items'),

(1018, 12, 'Dinesh Patel', '9871234567', 'Guardian', '2025-03-20 11:00:00', '2025-03-20 13:00:00',
'Document submission'),

(1020, 13, 'Suresh Iyer', '9876541230', 'Father', '2025-03-22 10:00:00', '2025-03-22 12:30:00', 'Discussing
progress'),

(1020, 14, 'Meena Iyer', '9876549876', 'Mother', '2025-03-25 14:00:00', '2025-03-25 16:00:00', 'Meeting
warden');

SELECT * FROM visitor;

-- Staff for Hostel 1

INSERT INTO staff (staff_id, hostel_no, staff_name, staff_salary, city, street, house_no)

VALUES

(3000, 1, 'Rajesh Patil', 15000, 'Mumbai', 'Andheri East', 101),

(3001, 1, 'Neha Sharma', 12000, 'Mumbai', 'Powai', 202),

(3002, 1, 'Suresh Nair', 13000, 'Mumbai', 'Vile Parle', 303),

(3003, 1, 'Anita Iyer', 14000, 'Mumbai', 'Bandra West', 404);

-- Staff for Hostel 2

INSERT INTO staff VALUES

(3004, 2, 'Vikram Singh', 16000, 'Pune', 'FC Road', 505),

(3005, 2, 'Kavita Mehta', 12500, 'Pune', 'JM Road', 606),

(3006, 2, 'Prakash Menon', 11000, 'Pune', 'MG Road', 707),

(3007, 2, 'Meera Desai', 14500, 'Pune', 'Kothrud', 808);

-- Staff for Hostel 3

INSERT INTO staff VALUES

(3008, 3, 'Amit Kapoor', 17000, 'Nagpur', 'Civil Lines', 909),

(3009, 3, 'Rohit Verma', 13500, 'Nagpur', 'Dharampeth', 1010),

(3010, 3, 'Poonam Reddy', 14000, 'Nagpur', 'Sadar', 1111),

(3011, 3, 'Nikita Pillai', 13000, 'Nagpur', 'Ramdaspeth', 1212);

-- Staff for Hostel 4

INSERT INTO staff VALUES

(3012, 4, 'Sanjay Joshi', 12500, 'Thane', 'Ghodbandar Road', 1313),

(3013, 4, 'Smita Iyer', 12000, 'Thane', 'Kasarvadavali', 1414),

(3014, 4, 'Ankur Gupta', 13000, 'Thane', 'Vasai Road', 1515),

(3015, 4, 'Priti Deshmukh', 14000, 'Thane', 'Majiwada', 1616);

-- Staff for Hostel 5

INSERT INTO staff VALUES

(3016, 5, 'Arun Nair', 15500, 'Mumbai', 'Mulund West', 1717),

(3017, 5, 'Minal Shah', 13000, 'Mumbai', 'Borivali East', 1818),

(3018, 5, 'Ravi Patil', 14000, 'Mumbai', 'Kandivali West', 1919),

(3019, 5, 'Shweta Rao', 15000, 'Mumbai', 'Goregaon East', 2020);

SELECT * FROM staff;

-- Cleaners

INSERT INTO cleaner (staff_id, area_assigned)

VALUES

(3001, 'Hostel 1 - Ground Floor'),

(3003, 'Hostel 1 - First Floor'),

(3005, 'Hostel 2 - Ground Floor'),

(3007, 'Hostel 2 - First Floor'),

(3010, 'Hostel 3 - Entire Building'),

(3013, 'Hostel 4 - Ground Floor'),

(3016, 'Hostel 5 - Common Areas');

SELECT * FROM cleaner;

-- Wardens

INSERT INTO warden (staff_id, asigned_floor)

VALUES

(3002, 1),

(3004, 2),

(3008, 3),

(3012, 4),

(3015, 5),

(3018, 6),

(3019, 7);

SELECT * FROM warden;

-- Watchmen

INSERT INTO watchman (staff_id, shift_start, shift_end)

VALUES

(3000, '06:00:00', '14:00:00'),

(3006, '14:00:00', '22:00:00'),

(3009, '22:00:00', '06:00:00'),

(3011, '06:00:00', '14:00:00'),

(3014, '14:00:00', '22:00:00'),

(3017, '22:00:00', '06:00:00');

SELECT * FROM watchman;

UPDATE room SET occupancy_status='occupied';

SELECT * FROM room;

DELETE FROM cleaner WHERE staff_id='3003';

SELECT * FROM cleaner;

DELETE FROM watchman;

SELECT *from watchman;