Unit 1: Database Concepts (RDBMS)

Chapter 4: Table Operations - CREATE, ALTER, and DROP

1. Creating Tables: The CREATE TABLE Command

The CREATE TABLE command defines the structure of a new relational table, including column names, data types, and column constraints.

Syntax

CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    [table_level_constraints]
);

Common Integrity Constraints

• PRIMARY KEY: Uniquely identifies each row; forbids NULL and duplicates.
• NOT NULL: Ensures that a column cannot be left empty.
• UNIQUE: Ensures all values in the column are distinct; allows multiple NULL values.
• DEFAULT value: Specifies a default fallback value when no value is provided.
• CHECK (condition): Enforces a boolean condition on column values.
• FOREIGN KEY REFERENCES: Enforces referential integrity linking to parent primary key.

Practical Example

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(40) NOT NULL,
    Gender CHAR(1) DEFAULT 'M',
    Marks DECIMAL(5, 2) CHECK (Marks >= 0 AND Marks <= 100),
    DOB DATE,
    Stream VARCHAR(20) NOT NULL
);

2. Inserting Data: The INSERT INTO Command

The INSERT INTO command adds new records into an existing table.

Syntax Options

-- Syntax 1: Explicit column list (recommended)
INSERT INTO Student (RollNo, Name, Gender, Marks, DOB, Stream)
VALUES (101, 'Ananya Sen', 'F', 95.50, '2006-08-14', 'Science');

-- Syntax 2: Default/omitted columns
INSERT INTO Student (RollNo, Name, Marks, Stream)
VALUES (102, 'Varun Dave', 82.00, 'Commerce');

-- Syntax 3: Inserting multiple rows simultaneously
INSERT INTO Student (RollNo, Name, Gender, Marks, DOB, Stream) VALUES
(103, 'Preeti Kaur', 'F', 89.00, '2006-03-21', 'Humanities'),
(104, 'Amit Verma', 'M', 76.50, '2006-11-05', 'Science');

3. Modifying Table Schema: The ALTER TABLE Command

ALTER TABLE is a DDL command used to modify the structure of an existing table without deleting its data.

1. Adding a New Column

ALTER TABLE Student ADD Email VARCHAR(60);

2. Modifying an Existing Column (Data Type or Size)

ALTER TABLE Student MODIFY Name VARCHAR(60) NOT NULL;

3. Renaming a Column

ALTER TABLE Student CHANGE Stream Department VARCHAR(30);

4. Dropping a Column

ALTER TABLE Student DROP COLUMN Gender;

5. Adding a Primary Key Constraint

ALTER TABLE Student ADD PRIMARY KEY (RollNo);

6. Adding a Foreign Key Constraint

ALTER TABLE Student ADD CONSTRAINT fk_dept 
FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID);

4. Deleting Tables and Records: DROP vs. TRUNCATE vs. DELETE

Syntax Examples

-- Delete students from Humanities stream
DELETE FROM Student WHERE Stream = 'Humanities';

-- Truncate all rows from Student table
TRUNCATE TABLE Student;

-- Completely delete table structure
DROP TABLE Student;

5. Board Exam Practice Questions

1. Write SQL query to create a table LIBRARY with BookID (Primary Key), Title (VARCHAR 50), Price (DECIMAL 6,2), and PubDate (DATE).
2. Differentiate between ALTER TABLE and UPDATE command.
3. Ans: ALTER TABLE is a DDL command that modifies the table's structural definition (columns, data types, constraints). UPDATE is a DML command that modifies existing data values inside the table rows.
4. What is the difference between DROP TABLE and DELETE FROM?