Manipulating Data (DML)

Introduction

CREATE TABLE builds an empty structure — INSERT INTO is how you actually put data into it, one row at a time.

Concept

-- All columns, in table order
INSERT INTO STUDENT VALUES (101, 'Ravi', 80);

-- Only some columns -- must name them explicitly
INSERT INTO STUDENT (AdmNo, Name) VALUES (102, 'Amit');

If you list values for every column, you can skip naming them, as long as you provide them in the table's exact column order. If you only want to fill in some columns, you must explicitly name which ones you're providing.

Explanation

Whatever column you don't provide a value for gets NULL automatically (unless that column has a DEFAULT value defined) — it isn't zero, it isn't an empty string, it's specifically NULL, meaning "no value at all."

Note: INSERT INTO always adds exactly one new row. There's no version of INSERT that modifies an existing row — changing data that's already there is UPDATE's job.

Summary

INSERT INTO table VALUES (...) adds a new row — list every column's value in order, or name specific columns explicitly if skipping others. Any column left out becomes NULL, unless a DEFAULT is defined for it.

Method 1: Insert All Values (The Shortcut)

If you have data for every single column in the correct order, you don't need to list the column names.

Method 2: Insert Specific Columns (The Safe Way)

If you only have data for some columns (e.g., you don't know the Marks yet), you must specify the column names. The missing columns will get NULL.

/* ==================================================
   SYSTEM SETUP (Table Created Automatically)
   Columns: AdmNo, Name, Marks
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (AdmNo INT, Name VARCHAR(20), Marks INT);

/* ==================================================
   YOUR TASK STARTS HERE
   ================================================== */

-- 1. Insert Ravi (All 3 values)
-- INSERT INTO ...


-- 2. Insert Amit (Only AdmNo and Name)
-- INSERT INTO ...


/* ==================================================
   VERIFICATION (See your data)
   ================================================== */
SELECT * FROM STUDENT;

Introduction

UPDATE changes the value(s) of existing rows — it's the command for correcting or adjusting data that's already in a table, without touching the table's structure.

Concept

UPDATE STUDENT SET Marks = 95 WHERE AdmNo = 101;

-- Updating multiple columns at once -- separate with commas
UPDATE STUDENT SET Marks = Marks + 5, City = 'Delhi' WHERE AdmNo = 101;

SET specifies which column(s) to change and their new value; WHERE specifies exactly which row(s) that change applies to.

Explanation

The WHERE clause isn't optional in practice — it's the only thing standing between "update one specific row" and "update every single row in the table." Leaving it out doesn't cause an error; SQL will happily and silently apply the change to every row, which is one of the most common, costly real-world mistakes with this command.

Warning: UPDATE STUDENT SET Marks = 0; with no WHERE clause sets every single student's marks to 0, not just one. Always double-check the WHERE clause before running an UPDATE.

Summary

UPDATE table SET column = value WHERE condition changes existing data — multiple columns can be updated at once, separated by commas. Omitting WHERE applies the change to every row in the table, not just one.

The Safety Lock: WHERE Clause

The most important part of an Update is the Condition. Without it, you destroy data.

/* ==================================================
   SYSTEM SETUP (Table with Data)
   101 | Ravi | 80
   102 | Amit | 75
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (AdmNo INT, Name VARCHAR(20), Marks INT);
INSERT INTO STUDENT VALUES (101, 'Ravi', 80);
INSERT INTO STUDENT VALUES (102, 'Amit', 75);

/* ==================================================
   YOUR TASK STARTS HERE
   Change Ravi's Marks (80) to 95.
   ================================================== */

-- Write your UPDATE command here:
-- UPDATE STUDENT SET ...


/* ==================================================
   VERIFICATION (Did it change?)
   ================================================== */
SELECT * FROM STUDENT;

Calculation Updates

You can also perform math inside an update.

Introduction

DELETE removes rows of data from a table — but, unlike DROP TABLE, it leaves the table's structure completely intact and ready to hold new data.

Concept

-- Delete one specific row
DELETE FROM STUDENT WHERE AdmNo = 102;

-- Delete every row, but keep the table structure
DELETE FROM STUDENT;

Explanation

This is the cleanest way to separate the three data-removal commands you'll meet across this unit: DELETE removes rows (data), optionally filtered by WHERE; DROP removes the entire table (structure and data both); TRUNCATE (a related command) also removes all rows but does so faster, without the ability to filter by WHERE. For CBSE purposes, the key contrast is DELETE (data only, structure survives) vs. DROP (both structure and data gone).

Note: DELETE FROM STUDENT; with no WHERE clause removes every row, but STUDENT still exists afterward as an empty table — you can immediately INSERT into it again. DROP TABLE STUDENT; leaves nothing to insert into at all.

Summary

DELETE FROM table WHERE condition removes matching rows; without WHERE, it removes all rows — but the table itself, its columns and constraints, remain. That's the core distinction from DROP TABLE, which removes the structure too.

Case 1: Safe Deletion (Using WHERE)

Always use a condition (like ID) to pinpoint the row you want to remove.

Case 2: The Disaster (Omitting WHERE)

If you forget the WHERE clause, SQL assumes you want to delete ALL rows.

/* ==================================================
   SYSTEM SETUP (Current Data)
   101 | Ravi
   102 | Amit
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (AdmNo INT, Name VARCHAR(20));
INSERT INTO STUDENT VALUES (101, 'Ravi');
INSERT INTO STUDENT VALUES (102, 'Amit');

/* ==================================================
   YOUR TASK STARTS HERE
   Delete Amit (AdmNo 102)
   ================================================== */

-- Write your DELETE command here:
-- DELETE FROM STUDENT ...


/* ==================================================
   VERIFICATION (Is Amit gone?)
   ================================================== */
SELECT * FROM STUDENT;