DDL Statements in SQL: (PostgreSQL vs SQL Server vs MySQL)

DDL is used to define, alter, and manage schema objects like tables, indexes, constraints, etc.

1. CREATE TABLE

Creates a new table

-- PostgreSQL

					CREATE TABLE Students (
    StudentID SERIAL PRIMARY KEY,
    Name VARCHAR(50),
    Age INT

); 
				

-- SQL Server

					CREATE TABLE Students (
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    Age INT

);  
				

-- MySQL

					CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
); 
				

2. ALTER TABLE

Modifies an existing table (add/drop/modify columns)

a) Add Column

					ALTER TABLE Students ADD Email VARCHAR(100); 
				

(Same syntax for PostgreSQL, SQL Server, and MySQL)

b) Modify/Alter Column

-- PostgreSQL

					ALTER TABLE Students ALTER COLUMN Age TYPE SMALLINT; 
				

-- SQL Server

					ALTER TABLE Students ALTER COLUMN Age SMALLINT; 
				

-- MySQL

					ALTER TABLE Students MODIFY Age SMALLINT; 
				

c) Drop Column

					ALTER TABLE Students DROP COLUMN Email; 
				

(Works same across all three platforms)

3. DROP TABLE

Deletes the table and its data permanently

DROP TABLE Students;

(Same syntax for all: PostgreSQL, SQL Server, MySQL)

4. TRUNCATE TABLE

Removes all data but keeps table structure

TRUNCATE TABLE Students;

Note:

• In PostgreSQL, it's faster than DELETE and can be rolled back (if inside a transaction).
• In SQL Server, cannot be used if foreign key constraints exist.
• In MySQL, auto-increment counter resets unless CONTINUE option is used.

5. RENAME TABLE

-- PostgreSQL

					ALTER TABLE Students RENAME TO Learners; 
				

-- SQL Server

					EXEC sp_rename 'Students', 'Learners'; 
				

-- MySQL

					RENAME TABLE Students TO Learners; 
				

6. CREATE DATABASE

CREATE DATABASE SchoolDB;

(Same syntax for PostgreSQL, SQL Server, MySQL)

7. DROP DATABASE

DROP DATABASE SchoolDB;

(Same for all platforms)

8. RENAME COLUMN

-- PostgreSQL

					ALTER TABLE Students RENAME COLUMN Name TO FullName; 
				

-- SQL Server

					EXEC sp_rename 'Students.Name', 'FullName', 'COLUMN'; 
				

-- MySQL

					ALTER TABLE Students RENAME COLUMN Name TO FullName;