Assignement 2 (DML)

SQL DML: Update, Delete, Filter and Sort Data

In the previous assignment, we created a client_master table and inserted records into it. Now we go one step further — actually working with that data. This post covers DML (Data Manipulation Language): how to update rows, delete them, filter with conditions, sort results, and use pattern matching.

What is DML?

DML stands for Data Manipulation Language. While DDL defines the structure of your database, DML is what you use day-to-day — reading, changing, and removing the actual data inside your tables.

There are two flavors of DML:

SQL falls into the non-procedural category, which is what makes it approachable even for beginners. For a broader understanding of how databases work underneath, check out this DBMS overview.

UPDATE — Changing Existing Data

When you want to modify specific values in a table without touching the rest of the rows, UPDATE is what you reach for.

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

Always use a WHERE clause with UPDATE. Without it, every single row in the table gets changed — which is almost never what you want.

DELETE — Removing Rows

To remove all rows from a table:

DELETE FROM table_name;

To remove only specific rows:

DELETE FROM table_name
WHERE condition;

Same rule applies here — skip the WHERE and you wipe the entire table.

WHERE Clause — Filtering Rows

The WHERE clause is how you tell SQL which rows to work with. It works with SELECT, UPDATE, and DELETE alike.

SELECT column1, column2
FROM table_name
WHERE condition;

LIKE — Pattern Matching

When you don't know the exact value but know what it looks like, use LIKE:

So LIKE '_a%' means: any value where the second character is a.

Logical Operators

ORDER BY — Sorting Results

By default, SQL returns rows in no guaranteed order. Use ORDER BY to sort:

SELECT * FROM table_name
ORDER BY column_name ASC|DESC;

ASC is ascending (default), DESC is descending. You can sort by multiple columns too — just comma-separate them.

Column Alias — Renaming Output Columns

Sometimes the column name in the table isn't what you want to show in the result. Use AS to rename it in the output:

SELECT column_name AS alias_name FROM table_name;

This doesn't change the actual column — just what it's called in the result set.

Lab Assignment: Queries on client_master

All queries below use the client_master table from Assignment 1. Here's the data we're starting with:

Important: Some queries below modify the data (UPDATE, DELETE). The expected outputs for later queries reflect those earlier changes — run them in order.

i. Update bal_due of client 0002 to 10000

UPDATE client_master
SET bal_due = 10000
WHERE client_no = '0002';

Expected output (updated row):

0002 | Vandana | Madras | 780001 | Tamilnadu | 10000

ii. Delete the record for client 0001

DELETE FROM client_master
WHERE client_no = '0001';

Row with client_no = '0001' (Ivan) is permanently removed from the table.

iii. Change city of client 0005 to Bombay

UPDATE client_master
SET city = 'Bombay'
WHERE client_no = '0005';

Expected output (updated row):

0005 | Ravi | Bombay | 100001 | NULL | 2000

iv. Clients with bal_due > 5000, show computed balance

This one uses a column alias — (bal_due * 15) is displayed as Updated Balance in the output.

SELECT name, bal_due, (bal_due * 15) AS "Updated Balance"
FROM client_master
WHERE bal_due > 5000;

Expected output:

Vandana | 10000 | 150000

Ivan's row was deleted earlier, so only Vandana qualifies.

v. Clients whose city has 'a' as the second letter

SELECT * FROM client_master
WHERE city LIKE '_a%';

Expected output:

Vandana | Madras | ...

After the earlier update, Ravi's city is now Bombay — second letter is o, so it doesn't match. Only Madras qualifies.

vi. Clients whose name has 'a' as the second letter

SELECT name
FROM client_master
WHERE name LIKE '_a%';

Expected output:

Vandana
Basu

vii. All clients sorted alphabetically by name

SELECT * FROM client_master
ORDER BY name ASC;

Expected output:

Basu, Pramada, Ravi, Rukmini, Vandana

viii. Distinct cities across all clients

SELECT DISTINCT city FROM client_master;

Expected output:

Bombay
Madras

Delhi was changed to Bombay in query iii, so it no longer appears.

ix. Clients where both pincode AND state are NULL

SELECT * FROM client_master
WHERE pincode IS NULL AND state IS NULL;

Expected output: No rows returned.

Client 0004 has a NULL pincode but has a state (Maharashtra). Client 0005 has a NULL state but has a pincode. Neither satisfies both conditions being NULL simultaneously.

x. Clients with balance between 10000 and 15000

SELECT * FROM client_master
WHERE bal_due BETWEEN 10000 AND 15000;

Expected output:

Vandana | 10000

BETWEEN is inclusive on both ends. Ivan (15000) was deleted earlier, so only Vandana shows up.

Also Explore These Topics

• SQL Assignment 1 — create tables, insert data, and basic SELECT queries

• SQL Assignment 3 — next in the series

• SQL Quick Recap — fast revision of all core SQL concepts

• Triggers in SQL — automate actions when data changes

• Views in SQL — create virtual tables from your queries

• PL/SQL Assignment — add procedural logic on top of SQL

• DBMS Overview — understand the full database landscape

Want to practice these queries interactively without any setup? W3Schools SQL editor lets you run SQL right in your browser. For the official reference on UPDATE, DELETE, and WHERE syntax used here, see the Oracle SQL documentation.