SQL Overview: Schemas, Data Types, and Table Definitions (DDL)

What is SQL? (In Simple Words)

SQL (Structured Query Language) is the universal, standardized programming language used by developers worldwide to communicate with relational database management systems.

Originally developed at IBM by Donald Chamberlin and Raymond Boyce in the 1970s (originally named SEQUEL).
It is a Declarative language: you tell the database what data you want, and the DBMS query engine figures out the most optimized physical access path.

1. Classification of SQL Sub-Languages

SQL commands are divided into five fundamental functional sub-languages:

The Sub-Languages of SQL

1. DDL (Data Definition)

Defines & alters table structures.
CREATE, ALTER, DROP, TRUNCATE

2. DML (Data Manipulation)

Inserts, modifies & queries records.
INSERT, UPDATE, DELETE, SELECT

3. DCL (Data Control)

Manages user security & permissions.
GRANT, REVOKE

4. TCL (Transaction Control)

Manages database transactions.
COMMIT, ROLLBACK, SAVEPOINT

2. Standard SQL Data Types and Domains

Before creating a table, columns must be assigned specific storage data types:

Data Type	Description	Real-World Example
`INT` or `INTEGER`	Whole numbers without decimals (positive or negative)	`Roll_No`, `Age`, `Quantity`
`NUMERIC(p, s)` or `DECIMAL(p, s)`	Exact precision numbers (`p` = total digits, `s` = decimal places)	`Salary NUMERIC(10, 2)` (e.g. `75000.50`)
`CHAR(n)`	Fixed-length string (pads unused space with blanks)	`Gender CHAR(1)` (`'M'` or `'F'`), `State_Code CHAR(2)`
`VARCHAR(n)`	Variable-length string (only consumes memory for actual text)	`Student_Name VARCHAR(50)`, `Email VARCHAR(100)`
`DATE`	Calendar date format (`YYYY-MM-DD`)	`DOB DATE` (e.g. `'2004-08-15'`)
`TIME` / `TIMESTAMP`	Time (`HH:MM:SS`) or combined Date + Time	`Login_Time TIMESTAMP`
`BOOLEAN`	Stores logical `TRUE`, `FALSE`, or `NULL`	`Is_Fee_Paid BOOLEAN`

3. Creating Tables and Enforcing Constraints (`CREATE TABLE`)

The CREATE TABLE command creates a new relation and establishes its column definitions, data domains, and business rules.

The Six Essential Column Constraints:

PRIMARY KEY: Guarantees column values are Unique and NOT NULL.
FOREIGN KEY: References the primary key of another table (Referential Integrity).
NOT NULL: Prevents a column from ever holding a NULL value.
UNIQUE: Ensures all values in a column are distinct (allows NULLs unless NOT NULL is also specified).
CHECK: Validates that data values satisfy a specific boolean condition.
DEFAULT: Automatically supplies a fallback value if no value is provided on insertion.

Complete Practical SQL Code Example:

-- 1. Create the Parent DEPARTMENT Table
CREATE TABLE Department (
    Dept_ID INT PRIMARY KEY,
    Dept_Name VARCHAR(50) NOT NULL UNIQUE,
    Building VARCHAR(50) DEFAULT 'Main Campus'
);

-- 2. Create the Child STUDENT Table with Foreign Key & Constraints
CREATE TABLE Student (
    Roll_No INT PRIMARY KEY,
    Student_Name VARCHAR(60) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 17 AND Age <= 60),
    Gender CHAR(1) CHECK (Gender IN ('M', 'F', 'O')),
    Admission_Date DATE DEFAULT CURRENT_DATE,
    Dept_ID INT,
    CONSTRAINT fk_student_dept 
        FOREIGN KEY (Dept_ID) 
        REFERENCES Department(Dept_ID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

4. Modifying Tables (`ALTER TABLE`)

When business requirements change, you don’t need to delete and recreate tables; use ALTER TABLE:

-- A. Add a brand new column
ALTER TABLE Student ADD Phone_No VARCHAR(15);

-- B. Modify an existing column data type or width
ALTER TABLE Student MODIFY Student_Name VARCHAR(100);

-- C. Drop an unnecessary column
ALTER TABLE Student DROP COLUMN Phone_No;

-- D. Add a new constraint
ALTER TABLE Student ADD CONSTRAINT chk_phone CHECK (LENGTH(Phone_No) = 10);

5. Removing Tables: `DROP` vs. `TRUNCATE` vs. `DELETE`

Students frequently confuse these three commands in university viva and theory exams:

Feature	`DROP TABLE`	`TRUNCATE TABLE`	`DELETE FROM`
Language Category	DDL	DDL	DML
What is Removed?	Removes data AND table schema structure permanently from disk	Removes all data rows, but keeps empty table schema	Removes selected rows matching `WHERE` clause
Speed	Extremely fast	Extremely fast (deallocates disk pages)	Slower (logs row-by-row deletions)
Rollback Capability	Cannot be rolled back (auto-committed)	Cannot be rolled back in most DBMS	Can be rolled back using `ROLLBACK`
WHERE Clause	Not applicable	No `WHERE` clause allowed	`WHERE` clause is supported

💡 DAVV Exam Preparation Tips

5-Mark Question: “Differentiate between DROP, TRUNCATE, and DELETE with a clear comparison table.”

Highlight DDL vs DML distinction.

Point out that DELETE logs row deletions and supports WHERE, while TRUNCATE deallocates table pages quickly, and DROP deletes the table structure entirely.

10-Mark Question: “Explain Data Definition Language (DDL) in SQL. Write SQL commands to create a STUDENT table with Roll_No (PK), Name (NOT NULL), Age (CHECK), Email (UNIQUE), and Dept_ID (FK referencing DEPARTMENT). Show how to add a column using ALTER TABLE.”

Define DDL and its primary commands (CREATE, ALTER, DROP, TRUNCATE).

Provide the full, clean SQL script with all 5 constraints properly labeled.

Show the ALTER TABLE Student ADD ... syntax.

SQL Overview: Schemas, Data Types & Table Definitions (DDL)