📅 Table Creation and Data Manipulation Commands

Querying data is only half the job; a database also has to be built and its data managed. This chapter covers creating databases and tables, applying constraints to them, changing data with the DML commands INSERT, UPDATE and DELETE, and restructuring or removing tables with more DDL commands such as ALTER and DROP.

11.1 🏁 Introduction

In the previous chapters, we learnt to query data with SELECT. But before any querying can happen, someone must have created the database, created the tables, and inserted the data — and that someone could be you!

graph LR
    C1["11.2\n🗄️ Create / Open /\nDrop Database"]
    C2["11.3\n📋 Create Table\nwith Constraints"]
    C3["11.4\n✍️ DML: INSERT\nUPDATE DELETE"]
    C4["11.5\n🔧 More DDL:\nALTER and DROP Table"]

    C1 --> C2 --> C3 --> C4

    style C1 fill:#2196F3,color:#fff
    style C2 fill:#9C27B0,color:#fff
    style C3 fill:#FF9800,color:#fff
    style C4 fill:#F44336,color:#fff

Think of it this way: Chapter 11 taught you how to read books in a library. Chapter 12 teaches you how to build the shelves, organise the sections, add new books, remove old ones, and rearrange everything — the full librarian role!

11.2 🗄️ Databases in MySQL

Before creating tables, you need a database to put them in. A MySQL server can host many databases simultaneously — each isolated from the others.

11.2.1 Creating Databases 🏗️

The CREATE DATABASE command creates a fresh, empty database on the MySQL server.

Syntax:

CREATE DATABASE database_name;

Examples:

-- setup_databases.sql
CREATE DATABASE SchoolDB;
CREATE DATABASE CompanyDB;
CREATE DATABASE IF NOT EXISTS LibraryDB;

11.2.2 Opening Databases 🔓

After creating a database, you must select it before any table operations can be performed on it.

Syntax:

USE database_name;

Examples:

-- open_database.sql
USE SchoolDB;

-- Confirm which database is currently active:
SELECT DATABASE();

11.2.3 Removing Databases 🗑️

The DROP DATABASE command permanently and irreversibly deletes an entire database — including every table and every row of data inside it.

Syntax:

DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

Examples:

-- remove_database.sql
DROP DATABASE OldTestDB;
DROP DATABASE IF EXISTS TempDB;

graph LR
    DROP["💣 DROP DATABASE SchoolDB"]
    GONE["❌ SchoolDB is GONE\nAll tables deleted\nAll data deleted\nNO undo possible!"]

    DROP --> GONE

    style DROP fill:#F44336,color:#fff
    style GONE fill:#9E9E9E,color:#fff

11.3 📋 Creating Tables

With an active database selected, CREATE TABLE defines a new table — specifying each column's name, data type, size, and any constraints.

Syntax:

CREATE TABLE table_name (
    column1_name  datatype(size)  [constraints],
    column2_name  datatype(size)  [constraints],
    ...
    [table_level_constraints]
);

Complete Example — Creating the Employee Table:

-- create_employee.sql
CREATE TABLE Employee (
    EmpId     INT             NOT NULL,
    Name      VARCHAR(30)     NOT NULL,
    Dept      VARCHAR(20),
    Salary    DECIMAL(10,2)   DEFAULT 0.00,
    DOJ       DATE,
    City      VARCHAR(20)     DEFAULT 'Delhi',
    PRIMARY KEY (EmpId)
);

Breaking down each part:

CREATE TABLE Student (
    RollNo  INT          PRIMARY KEY,
    Name    VARCHAR(30),
    Marks   DECIMAL(5,2)
);

11.3.1 Data Integrity Through Constraints 🔒

Constraints are rules applied to columns (or the whole table) that prevent invalid or inconsistent data from being stored. They are the database's built-in quality control system.

graph TD
    C["🔒 CONSTRAINTS\nData Integrity Rules"]
    C1["NOT NULL\nColumn must have a value"]
    C2["UNIQUE\nNo two rows can have\nthe same value in this column"]
    C3["PRIMARY KEY\nNot Null plus Unique\nIdentifies each row"]
    C4["FOREIGN KEY\nLinks to Primary Key\nof another table"]
    C5["CHECK\nValue must satisfy\na specified condition"]
    C6["DEFAULT\nAuto-fills a value\nif none is provided"]

    C --> C1
    C --> C2
    C --> C3
    C --> C4
    C --> C5
    C --> C6

    style C fill:#9C27B0,color:#fff

All Six Constraints — Detailed Explanation:

NOT NULL

Ensures a column cannot be left empty — every row must provide a value for this column.

-- not_null_demo.sql
CREATE TABLE Student (
    RollNo  INT         NOT NULL,
    Name    VARCHAR(30) NOT NULL,
    Marks   INT         -- NULL is allowed here
);

UNIQUE

Ensures no two rows can have the same value in this column — but unlike PRIMARY KEY, UNIQUE columns CAN contain NULL values (multiple NULLs allowed).

-- unique_demo.sql
CREATE TABLE Employee (
    EmpId   INT         PRIMARY KEY,
    Email   VARCHAR(50) UNIQUE,
    Phone   VARCHAR(15) UNIQUE
);

PRIMARY KEY

The most important constraint — uniquely identifies every row. It combines NOT NULL + UNIQUE into one powerful rule. Every table should have exactly one Primary Key.

-- primary_key_demo.sql
-- Method 1: Inline (single column PK)
CREATE TABLE Student (
    RollNo  INT PRIMARY KEY,
    Name    VARCHAR(30)
);

-- Method 2: Table-level (also required for COMPOSITE primary key)
CREATE TABLE Enrolment (
    StudentId   INT,
    CourseId    INT,
    PRIMARY KEY (StudentId, CourseId)
);

FOREIGN KEY

A Foreign Key in one table is a column that refers to the Primary Key of another table, creating a link between the two tables and enforcing Referential Integrity.

-- foreign_key_demo.sql
CREATE TABLE Department (
    DeptId    INT         PRIMARY KEY,
    DeptName  VARCHAR(30) NOT NULL
);

CREATE TABLE Employee (
    EmpId   INT         PRIMARY KEY,
    Name    VARCHAR(30),
    DeptId  INT,
    FOREIGN KEY (DeptId) REFERENCES Department(DeptId)
);

graph LR
    DEPT["📋 Department Table\nDeptId PK"]
    EMP["📋 Employee Table\nDeptId FK"]

    EMP -->|"REFERENCES"| DEPT

    style DEPT fill:#4CAF50,color:#fff
    style EMP fill:#2196F3,color:#fff

CHECK

The CHECK constraint ensures a column's value satisfies a user-defined condition before being accepted.

-- check_constraint_demo.sql
CREATE TABLE Student (
    RollNo  INT         PRIMARY KEY,
    Name    VARCHAR(30) NOT NULL,
    Marks   INT         CHECK (Marks >= 0 AND Marks <= 100),
    Age     INT         CHECK (Age >= 15)
);

DEFAULT

The DEFAULT constraint automatically provides a pre-set value for a column when no value is explicitly given during INSERT.

-- default_constraint_demo.sql
CREATE TABLE Employee (
    EmpId    INT           PRIMARY KEY,
    Name     VARCHAR(30)   NOT NULL,
    City     VARCHAR(20)   DEFAULT 'Delhi',
    Salary   DECIMAL(10,2) DEFAULT 0.00
);

-- This INSERT doesn't specify City or Salary:
INSERT INTO Employee (EmpId, Name) VALUES (101, 'Priya');
-- City automatically becomes 'Delhi', Salary becomes 0.00

Constraints — Complete Quick Reference:

11.4 ✍️ Changing Data with DML Commands

DML (Data Manipulation Language) commands modify the actual data inside tables — inserting new rows, updating existing rows, and deleting rows.

graph TD
    DML["✍️ DML COMMANDS"]
    INS["📥 INSERT\nAdd new rows"]
    UPD["🔄 UPDATE\nChange existing data"]
    DEL["🗑️ DELETE\nRemove rows"]

    DML --> INS
    DML --> UPD
    DML --> DEL

    style DML fill:#FF9800,color:#fff
    style INS fill:#4CAF50,color:#fff
    style UPD fill:#2196F3,color:#fff
    style DEL fill:#F44336,color:#fff

11.4.1 Inserting Data into Table — INSERT 📥

The INSERT INTO command adds one or more new rows to a table.

Syntax — Two Forms:

-- Form 1: Specify both columns and values (recommended)
INSERT INTO table_name (col1, col2, col3, ...)
VALUES (val1, val2, val3, ...);

-- Form 2: Provide values for ALL columns in table order
INSERT INTO table_name
VALUES (val1, val2, val3, ...);

Inserting a Single Row:

-- insert_single.sql
INSERT INTO Employee (EmpId, Name, Dept, Salary, DOJ, City)
VALUES (101, 'Priya Sharma', 'Sales', 45000.00, '2022-03-15', 'Delhi');

Inserting Multiple Rows in One Statement:

-- insert_multiple.sql
INSERT INTO Employee (EmpId, Name, Dept, Salary, DOJ, City)
VALUES
    (102, 'Arjun Kumar',  'IT',   62000.00, '2021-07-01', 'Mumbai'),
    (103, 'Sita Roy',     'HR',   38000.00, '2023-01-20', 'Delhi'),
    (104, 'Rohan Das',    'IT',   71000.00, '2020-11-05', 'Bangalore');

Inserting with NULL and DEFAULT:

-- insert_null_default.sql
-- Explicitly inserting NULL:
INSERT INTO Employee (EmpId, Name, Dept, Salary, DOJ, City)
VALUES (105, 'Anjali Gupta', 'Sales', NULL, '2023-06-10', 'Mumbai');

-- Omitting columns that have DEFAULT values:
INSERT INTO Employee (EmpId, Name, Dept, DOJ)
VALUES (106, 'Vikram Singh', 'HR', '2022-09-25');
-- City and Salary will use their DEFAULT values automatically

11.4.2 Modifying Data with UPDATE Command 🔄

The UPDATE command changes the values of existing rows. Almost always used with a WHERE clause — without it, EVERY row in the table gets updated!

Syntax:

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

Updating a Single Row:

-- update_single.sql
UPDATE Employee
SET Salary = 50000.00
WHERE EmpId = 101;
-- Only Priya Sharma's salary changes; everyone else is untouched

Updating Multiple Columns at Once:

-- update_multiple_cols.sql
UPDATE Employee
SET Salary = 75000.00, City = 'Hyderabad'
WHERE EmpId = 104;

Updating Multiple Rows with One Statement:

-- update_multiple_rows.sql
UPDATE Employee
SET Salary = Salary * 1.10
WHERE Dept = 'IT';
-- ALL IT employees get a 10% salary increase in one query!

graph LR
    U1["UPDATE Employee\nSET Salary = 50000\nWHERE EmpId = 101"]
    R1["Only EmpId 101\nrow is updated"]

    U2["UPDATE Employee\nSET Salary = 50000\n(No WHERE)"]
    R2["ALL rows updated\nExtremely Dangerous!"]

    U1 --> R1
    U2 --> R2

    style R1 fill:#4CAF50,color:#fff
    style R2 fill:#F44336,color:#fff

11.4.3 Deleting Data with DELETE Command 🗑️

The DELETE command removes existing rows from a table. Like UPDATE, it should almost always have a WHERE clause.

Syntax:

DELETE FROM table_name
[WHERE condition];

Deleting a Single Specific Row:

-- delete_single.sql
DELETE FROM Employee
WHERE EmpId = 105;
-- Only Anjali Gupta's record is removed

Deleting Multiple Rows Matching a Condition:

-- delete_multiple.sql
DELETE FROM Employee
WHERE Dept = 'HR';
-- All HR department employees are removed

Deleting All Rows (No WHERE):

-- delete_all.sql
DELETE FROM Employee;
-- ALL rows removed; table structure remains intact (unlike DROP TABLE)

DELETE vs TRUNCATE vs DROP TABLE — The Critical Three-Way Comparison:

11.5 🔧 More DDL Commands

After a table is created, your requirements often change — new columns needed, data type changes, constraints to add or remove. ALTER TABLE handles all of this. And when a table is no longer needed, DROP TABLE removes it entirely.

11.5.1 ALTER TABLE Command 🔧

ALTER TABLE modifies the structure of an existing table without losing its data (in most cases). It is the Swiss Army knife of DDL.

graph TD
    ALTER["🔧 ALTER TABLE"]
    A1["ADD\nAdd a new column\nor constraint"]
    A2["MODIFY\nChange a column's\ndata type or size"]
    A3["CHANGE\nRename a column\nand optionally change type"]
    A4["DROP COLUMN\nRemove a column\nand all its data"]
    A5["ADD CONSTRAINT\nAdd PRIMARY KEY,\nFOREIGN KEY etc."]
    A6["DROP CONSTRAINT\nRemove an existing\nconstraint"]

    ALTER --> A1
    ALTER --> A2
    ALTER --> A3
    ALTER --> A4
    ALTER --> A5
    ALTER --> A6

    style ALTER fill:#9C27B0,color:#fff

ADD — Adding a New Column:

-- alter_add_column.sql
ALTER TABLE Employee
ADD Email VARCHAR(50);
-- New 'Email' column added; existing rows get NULL for this column

ALTER TABLE Employee
ADD Gender CHAR(1) DEFAULT 'M' AFTER Name;
-- AFTER specifies where in column order it appears

MODIFY — Changing a Column's Data Type or Size:

-- alter_modify.sql
ALTER TABLE Employee
MODIFY Name VARCHAR(50);
-- Expands Name column from VARCHAR(30) to VARCHAR(50)

ALTER TABLE Employee
MODIFY Salary DECIMAL(12,2) NOT NULL;
-- Changes precision AND adds NOT NULL constraint simultaneously

CHANGE — Renaming a Column (and Optionally Changing Type):

-- alter_change.sql
ALTER TABLE Employee
CHANGE DOJ DateOfJoining DATE;
-- Renames column 'DOJ' to 'DateOfJoining'; data type stays DATE

ALTER TABLE Employee
CHANGE City Location VARCHAR(30);
-- Renames AND changes data type simultaneously

DROP COLUMN — Removing a Column:

-- alter_drop_column.sql
ALTER TABLE Employee
DROP COLUMN Email;
-- Email column and ALL its data are permanently deleted!

ADD CONSTRAINT — Adding a Primary or Foreign Key After Creation:

-- alter_add_constraint.sql
ALTER TABLE Employee
ADD PRIMARY KEY (EmpId);

ALTER TABLE Employee
ADD CONSTRAINT fk_dept
FOREIGN KEY (DeptId) REFERENCES Department(DeptId);

ALTER TABLE Employee
ADD UNIQUE (Email);

DROP CONSTRAINT:

-- alter_drop_constraint.sql
ALTER TABLE Employee
DROP PRIMARY KEY;

ALTER TABLE Employee
DROP FOREIGN KEY fk_dept;

ALTER TABLE Employee
DROP INDEX Email;

Multiple Changes in a Single ALTER TABLE:

-- alter_multiple.sql
ALTER TABLE Employee
    ADD Phone VARCHAR(15),
    MODIFY Name VARCHAR(50) NOT NULL,
    DROP COLUMN Gender;
-- Multiple structural changes in one efficient statement!

ALTER TABLE Operations — Quick Summary:

11.5.2 The DROP TABLE Command 💣

DROP TABLE permanently deletes an entire table — its structure, all its data, all its constraints, and all its indexes — in one command.

Syntax:

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

Examples:

-- drop_table.sql
DROP TABLE TempResults;

DROP TABLE IF EXISTS OldArchive;
-- Safe: no error if OldArchive doesn't exist

graph LR
    BEFORE["📋 Employee Table\nStructure + 6 rows of data\nPrimary Key and Indexes"]
    DROP["💣 DROP TABLE Employee;"]
    AFTER["❌ Table COMPLETELY GONE\nStructure deleted\nData deleted\nConstraints deleted\nIndexes deleted"]

    BEFORE --> DROP --> AFTER

    style DROP fill:#F44336,color:#fff
    style AFTER fill:#9E9E9E,color:#fff

DROP TABLE vs DROP DATABASE:

Final Three-Way Deletion Comparison:

⚠️ Common Errors and Misconceptions

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) Which SQL command is used to select an active database? → USE database_name;

b) Which constraint ensures no two rows can have the same value in a column? → UNIQUE

c) Which DML command is used to modify existing data in a table? → UPDATE

d) What is the purpose of the DEFAULT constraint? → It automatically assigns a pre-defined value to a column when no value is provided during INSERT.

e) Which ALTER TABLE clause is used to rename a column? → CHANGE

Q2: Short Answer [2 marks]

Q: Differentiate between PRIMARY KEY and UNIQUE constraints.

Q3: Practical SQL [3 marks]

Q: Write SQL statements to: a) Create a table Student with RollNo (INT, PK), Name (VARCHAR 30, NOT NULL), Marks (DECIMAL 5,2), Grade (CHAR 1, DEFAULT 'C'). b) Insert a record: RollNo=1, Name='Priya', Marks=91.5 c) Update Grade to 'A' for students with Marks >= 90.

-- a) Create table
CREATE TABLE Student (
    RollNo  INT           PRIMARY KEY,
    Name    VARCHAR(30)   NOT NULL,
    Marks   DECIMAL(5,2),
    Grade   CHAR(1)       DEFAULT 'C'
);

-- b) Insert record
INSERT INTO Student (RollNo, Name, Marks)
VALUES (1, 'Priya', 91.5);

-- c) Update grade
UPDATE Student
SET Grade = 'A'
WHERE Marks >= 90;

Q4: Short Answer [2 marks]

Q: Write SQL to add a column Phone (VARCHAR 15) to an existing Employee table, and then remove the column City.

ALTER TABLE Employee ADD Phone VARCHAR(15);
ALTER TABLE Employee DROP COLUMN City;

Q5: Output Based Question [2 marks]

Q: What will happen when the following statement is executed?

DELETE FROM Employee;

→ All rows from the Employee table will be deleted. However, the table structure (columns, constraints) will remain intact — the table will exist but be empty. Unlike DROP TABLE, the table itself is not removed from the database.

✏️ Practice Problems

1. Write SQL to create a Library database, select it, and then create a Books table with: BookId (INT, PK), Title (VARCHAR 50, NOT NULL), Author (VARCHAR 30), Price (DECIMAL 7,2, CHECK Price > 0), Available (BOOLEAN, DEFAULT TRUE).

2. Insert three records into the Books table, one of which has a NULL Author.

3. Write SQL to give a 15% price increase to all books priced below 300.

4. Write SQL to delete all books where Available = FALSE.

5. Add a new column ISBN (CHAR 13, UNIQUE) to the Books table using ALTER TABLE.

6. Rename the column Available to InStock using ALTER TABLE CHANGE.

7. Write the difference between DROP TABLE Books; and DELETE FROM Books; — what state is the database in after each?

8. A table Employee was created without a PRIMARY KEY. Write the ALTER TABLE command to add EmpId as the Primary Key.

9. Write SQL to create a Marks table with a FOREIGN KEY that links StudentId to the Student table's RollNo column.

10. Without running the query, predict what UPDATE Employee SET Dept = 'IT'; does (no WHERE clause). How would you fix it to only update employees currently in the 'HR' department?