Assignement 2 (DML)

divya sachan

@divyasachan

Created

Oct 31, 2025

Last Modified

4 months ago

Assignement 2

📘 Practical #2 – DML (Data Manipulation Language)

Objective:

To manipulate the data of a table.

🧠 Theory (Clean & Readable)

1. DML – Data Manipulation Language

DML is used for:

Retrieving information from the database
Inserting new information
Deleting existing information
Modifying stored information

Types of DML:

Type	Meaning
Procedural DML	Specify what data is needed & how to get it
Non-Procedural DML	Specify what data is needed only

UPDATE (Modify Data in Table)

Used when we want to change specific values without altering entire rows.

Syntax:

sql

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE (Remove Data from Table)

Delete all rows:

sql

DELETE FROM table_name;

Delete selected rows:

sql

DELETE FROM table_name
WHERE condition;

2. Restricting Data – WHERE Clause

Used to filter rows.

Syntax:

sql

SELECT column1, column2
FROM table_name
WHERE condition;

LIKE Operator (Pattern Matching)

Symbol	Meaning
`%`	Zero or many characters
`_`	Exactly one character

Logical Operators

Operator	Meaning
AND	All conditions must be true
OR	At least one condition must be true
NOT	Excludes values

3. Sorting Data – ORDER BY

ASC = Ascending (default)
DESC = Descending

sql

SELECT * FROM table_name
ORDER BY column_name ASC|DESC;

4. Column Alias

Used to rename column headings.

sql

SELECT column_name AS alias_name FROM table_name;

🧪 Assignment #2 – Queries with Expected Output

Using the same client_master table from Practical #1.

📍 Data Before Operations

client_no	name	city	pincode	state	bal_due
0001	Ivan	Bombay	400054	Maharashtra	15000
0002	Vandana	Madras	780001	Tamilnadu	0
0003	Pramada	Bombay	400057	Maharashtra	5000
0004	Basu	Bombay	NULL	Maharashtra	0
0005	Ravi	Delhi	100001	NULL	2000
0006	Rukmini	Bombay	400050	Maharashtra	0

✅ ALL QUERIES + EXPECTED OUTPUT (Single Block)

sql

-- i. Change the bal_due of client_no '0002' to 10000
UPDATE client_master
SET bal_due = 10000
WHERE client_no = '0002';

-- Expected Output (only updated row)
-- 0002 | Vandana | Madras | 780001 | Tamilnadu | 10000


-- ii. Delete the record with client_no '0001'
DELETE FROM client_master
WHERE client_no = '0001';

-- Expected: Row of client_no 0001 removed


-- iii. Change the city of client_no '0005' to 'Bombay'
UPDATE client_master
SET city = 'Bombay'
WHERE client_no = '0005';

-- Expected Output (only updated row)
-- 0005 | Ravi | Bombay | 100001 | NULL | 2000


-- iv. Display clients with bal_due > 5000 and show (bal_due*15) as Updated Balance
SELECT name, bal_due, (bal_due * 15) AS "Updated Balance"
FROM client_master
WHERE bal_due > 5000;

-- Expected Output
-- Vandana | 10000 | 150000


-- v. Clients whose city’s second letter is 'a'
SELECT * FROM client_master
WHERE city LIKE '_a%';

-- Expected Output
-- Vandana | Madras | ...
-- Ravi | Bombay | ... (Because now 'Bombay' second letter = o → so Ravi NOT included)
-- Only 'Madras' matches


-- vi. Names having 'a' as the second letter
SELECT name
FROM client_master
WHERE name LIKE '_a%';

-- Expected Output
-- Vandana
-- Basu


-- vii. List clients in sorted order of their name
SELECT * FROM client_master
ORDER BY name ASC;

-- Expected Output (names alphabetically)
-- Basu, Pramada, Ravi, Rukmini, Vandana


-- viii. Display distinct cities of the clients
SELECT DISTINCT city FROM client_master;

-- Expected Output
-- Bombay
-- Madras
-- Null? (ignored if using DISTINCT)
-- (Delhi removed earlier since changed to Bombay)


-- ix. Clients whose pincode AND state are missing (both NULL)
SELECT * FROM client_master
WHERE pincode IS NULL AND state IS NULL;

-- Expected Output
-- No exact record matches this
-- (0004 has NULL pincode but state present; 0005 has NULL state but pincode present)


-- x. Clients whose balance lies between 10000 and 15000
SELECT * FROM client_master
WHERE bal_due BETWEEN 10000 AND 15000;

-- Expected Output
-- Vandana | 10000
-- (0001 was deleted earlier so not included)