DDL commands

The essentials

DDL commands create and change the structure of database objects. The main ones are CREATE, ALTER and DROP.

Key points

• CREATE DATABASE School; creates a database. USE School; makes it the current database.
• CREATE TABLE Student (RollNo INT PRIMARY KEY, Name VARCHAR(30) NOT NULL, DOB DATE); creates a table with columns, types and constraints.
• A foreign key is declared as FOREIGN KEY (DeptNo) REFERENCES Dept(DeptNo).
• ALTER TABLE changes an existing table:
• ALTER TABLE Student ADD Phone CHAR(10); adds a column.
• ALTER TABLE Student DROP Phone; removes a column.
• ALTER TABLE Student MODIFY Name VARCHAR(40); changes the data type or size of a column.
• ALTER TABLE Student ADD PRIMARY KEY (RollNo); adds a primary key; ALTER TABLE Student DROP PRIMARY KEY; removes it.
• DROP TABLE Student; removes the whole table, including its structure and all its data. DROP DATABASE removes a database.
• DESCRIBE Student; shows the structure of a table.

Exam tips

• Know the difference between DROP TABLE (removes table and structure) and DELETE FROM table (DML; removes rows, keeps structure).
• A new column added with ALTER TABLE gets NULL in existing rows unless a DEFAULT is given.

Common mistakes

• Writing ALTER TABLE Student DELETE column. The correct keyword is DROP.
• Using UPDATE to change a column's data type; that needs ALTER TABLE … MODIFY.