Data Definition Language (DDL) in SQL – CREATE, ALTER, DROP with Examples

Data Definition Language (DDL) – SQL Tutorial

Data Definition Language (DDL) is a part of SQL used to define, modify, and remove the structure of database objects such as tables. DDL commands do not work on data; they work on the database schema.

1. CREATE Command

Definition: The CREATE command is used to create new database objects.

Purpose:

  • Create tables
  • Define column names and data types
  • Apply constraints

Syntax:

CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint
);

Example:

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE,
  age INT CHECK (age >= 18)
);

2. ALTER Command

Definition: The ALTER command modifies the structure of an existing table.

Uses of ALTER:

  • Add new columns
  • Modify column size or data type
  • Delete columns

Add a Column:

ALTER TABLE Student ADD phone VARCHAR(15);

Modify a Column:

ALTER TABLE Student MODIFY name VARCHAR(100);

Drop a Column:

ALTER TABLE Student DROP phone;

3. DROP Command

Definition: The DROP command permanently deletes database objects.

Important Points:

  • All data is lost permanently
  • Cannot be rolled back in most DBMS

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Student;

Difference Between CREATE, ALTER, and DROP

Command Purpose Effect
CREATE Create new table Adds structure
ALTER Modify table structure Changes structure
DROP Delete table Deletes structure & data

Lab Practice Tasks

  1. Create an Employee table with 5 columns
  2. Add a new column using ALTER
  3. Modify a column data type
  4. Drop a column
  5. Drop the table

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)