Building the Database (DDL)

Introduction

Every column you define in a table needs two things: what kind of value it holds, and any rules restricting what values you're actually allowed to put in it. These are data types and constraints.

Concept

Common SQL data types:

• INT — whole numbers.
• FLOAT/DECIMAL — numbers with a decimal point.
• CHAR(n) — a fixed-length text field of exactly n characters, padded with spaces if the actual text is shorter.
• VARCHAR(n) — a variable-length text field, up to n characters — uses only as much space as the actual text needs. This is the standard choice for names and most text data.
• DATE — calendar dates.

Common constraints:

• NOT NULL — the column cannot be left empty.
• UNIQUE — no two rows may have the same value in this column.
• DEFAULT — a value used automatically if none is provided.
• CHECK — restricts values to ones satisfying a condition (e.g., Marks >= 0).

Worked Example

Here's how you'd actually use these together, defining a table for a school's book collection:

CREATE TABLE BOOK (
    BookID INT PRIMARY KEY,
    Title VARCHAR(50) NOT NULL,
    Category CHAR(10) DEFAULT 'General',
    Price INT CHECK (Price >= 0)
);

Title is VARCHAR since book titles vary a lot in length. Category is CHAR(10) — short, fairly fixed-length labels like "Fiction". If you don't specify a Category when adding a book, it DEFAULTs to 'General'. And CHECK (Price >= 0) stops you from ever inserting a negative price by mistake.

Explanation

CHAR vs. VARCHAR is worth understanding precisely: CHAR(10) always reserves exactly 10 bytes, no matter how short the actual text is — storing "SQL" in a CHAR(10) column still uses 10 bytes (padded with spaces). VARCHAR(10) would use only as much as "SQL" actually needs. That's why VARCHAR is generally preferred for names and free text.

Note: UNIQUE is the constraint specifically for "no duplicate values allowed" — it's easy to confuse with NOT NULL (which is about emptiness, not duplication) or Primary Key (which combines both UNIQUE and NOT NULL automatically).

Summary

INT, FLOAT, CHAR(n), VARCHAR(n), and DATE are the core SQL data types — CHAR is fixed-length (always reserves its full size), VARCHAR is variable-length (uses only what's needed). NOT NULL, UNIQUE, DEFAULT, and CHECK are the constraints that restrict what values a column may actually hold.

CHAR vs. VARCHAR

Why do we have two types for text? It's about Memory vs. Speed.

2. Constraints

Introduction

CREATE TABLE and DROP TABLE are the two commands that bring a table into existence and remove it entirely — the very first and potentially last commands you'll run against any table.

Concept

CREATE TABLE defines a table's name, its columns, each column's data type, and any constraints, all in one statement:

CREATE TABLE STUDENT (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Marks INT
);

DROP TABLE removes a table completely — both its structure and every row of data inside it, permanently:

DROP TABLE OLD_DATA;

Explanation

DROP TABLE is one of the most dangerous commands in SQL specifically because of what it removes: not just the rows (which DELETE would do), but the table's entire structure too — its columns, its constraints, everything. Once dropped, the table doesn't exist to query at all; a SELECT * FROM it afterward fails with an error like "no such table", not an empty result.

Warning: DROP TABLE cannot be undone by any SQL command — there's no "UNDROP". In a real system, only a full backup restore can bring the data back.

Summary

CREATE TABLE name (column type constraints, ...) defines a new table's structure. DROP TABLE name permanently deletes both the table's structure and all its data — the most destructive DDL command, with no built-in way to reverse it.

Syntax & Rules (MySQL)

Common Constraints (Rules):

• PRIMARY KEY: Uniquely identifies each row (e.g., Admission Number).
• NOT NULL: The column cannot be left empty (e.g., Student Name).
• UNIQUE: No duplicates allowed (e.g., Email ID).

/* ==================================================
   TASK: Create the STUDENT table
   1. AdmNo (INT PRIMARY KEY)
   2. Name (VARCHAR(20) NOT NULL)
   3. Marks (INT)
   ================================================== */

-- Write your CREATE TABLE command here:
-- Example: CREATE TABLE STUDENT (...);



/* ==================================================
   SYSTEM VERIFICATION (Do not delete)
   This checks if your table was created correctly.
   ================================================== */
-- In MySQL we use: DESC STUDENT;
-- Since this lab runs on a browser engine, we use:
PRAGMA table_info(STUDENT);

/* ==================================================
   SYSTEM SETUP: Creating a dummy table
   (We create it so you can destroy it!)
   ================================================== */
DROP TABLE IF EXISTS OLD_DATA;
CREATE TABLE OLD_DATA (ID INT, Info VARCHAR(50));
INSERT INTO OLD_DATA VALUES (1, 'Useless Data');

/* ==================================================
   YOUR TASK STARTS HERE
   Write the command to DROP the 'OLD_DATA' table
   ================================================== */

-- Type your DROP TABLE command here:



/* ==================================================
   VERIFICATION
   This query checks if the table still exists.
   If successful, it should say "no such table: OLD_DATA"
   ================================================== */
SELECT * FROM OLD_DATA;

Introduction

Sometimes a table's structure needs to change after you've already created it — you need a new column, or an existing one needs a different size. ALTER TABLE is the command for exactly that: changing structure, not data.

Concept

-- Add a new column
ALTER TABLE STUDENT ADD City VARCHAR(20);

-- Change an existing column's definition
ALTER TABLE STUDENT MODIFY Name VARCHAR(50);

ADD introduces a brand-new column; MODIFY changes the definition (typically the size or data type) of a column that already exists.

Explanation

ALTER TABLE only ever touches the table's structure — its columns and their definitions. It's never the right tool if you want to change or remove actual row data; that's UPDATE's and DELETE's job specifically. This distinction — structure vs. data — is exactly what separates DDL commands (CREATE, ALTER, DROP) from DML commands (INSERT, UPDATE, DELETE), which you'll meet in the next chapter.

Note: ALTER TABLE cannot delete a row of data — if a question asks how to remove data (not a column), the answer is DELETE, never ALTER.

Summary

ALTER TABLE ... ADD adds a new column; ALTER TABLE ... MODIFY changes an existing column's definition. ALTER TABLE only changes structure — never row data, which stays the job of DML commands like UPDATE and DELETE.

-- SETUP (Do not edit)
DROP TABLE IF EXISTS STUDENT;
CREATE TABLE STUDENT (AdmNo INT, Name VARCHAR(20));

-- TASK: Write the ALTER command to ADD 'City'
-- Example: ALTER TABLE STUDENT ADD ...


-- VERIFY (Shows the new columns)
SELECT * FROM STUDENT;

Other ALTER Operations

Memorize these syntaxes for 1-mark questions.

Introduction

ALTER TABLE doesn't just add or resize columns — it can also add or remove the constraints attached to them, including a table's own Primary Key, after the table already exists.

Concept

-- Add a Primary Key to a table that doesn't already have one
ALTER TABLE BOOK ADD PRIMARY KEY (BookID);

-- Remove a Primary Key
ALTER TABLE STUDENT DROP PRIMARY KEY;

Adding a Primary Key requires naming exactly which column(s) it applies to — BOOK was created without one, so BookID needs to be named explicitly here. Dropping one doesn't need a column name — since a table can only ever have one Primary Key, there's nothing to specify. STUDENT already has AdmNo as its Primary Key from when it was created, which is what the DROP example removes.

Explanation

Dropping a Primary Key doesn't delete any data — the rows and their values stay exactly as they were. What changes is the rule: with no Primary Key enforcing uniqueness, the column that used to require unique values can now silently accept duplicates.

Warning: After DROP PRIMARY KEY, the column's existing data is untouched, but the uniqueness guarantee is gone — new duplicate values become possible where they weren't before.

Summary

ALTER TABLE ... ADD PRIMARY KEY (column) attaches a Primary Key to an existing table; ALTER TABLE ... DROP PRIMARY KEY removes it. Adding requires naming the column; dropping doesn't. Neither operation deletes data — only the constraint rule changes.

MySQL Syntax (Memorize This)

Removing Constraints

/* ==================================================
   SYNTAX PRACTICE
   Write the command below.
   (Note: This is a design exercise. It will not run 
   in this browser preview, but is required for Exams)
   ================================================== */

-- Your Answer:
-- ALTER TABLE ...