6. Structuring Your Database: CREATE, ALTER, and DROP TABLE

The ability to manipulate the database schema—the structure that holds your data—is a core skill in SQL. The commands CREATE TABLE, ALTER TABLE, DROP TABLE are part of the Data Definition Language (DDL), giving you control over the very foundation of your database.

---

1. Defining a New Structure: CREATE TABLE

The CREATE TABLE command is used to define a new table in your database. You must specify the table name and define the columns, including their data types and any constraints.

Example: Creating a new Inventory table

CREATE TABLE Inventory(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100) NOT NULL,

StockQuantity INT DEFAULT 0,

ProductCode CHAR(5) UNIQUE

);

• Data Types: Define the kind of data a column can hold (INT, VARCHAR, DATE, DECIMAL, etc.).

• Constraints: Rules enforced on data columns (e.g., PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT).

---

2. Modifying an Existing Structure: ALTER TABLE

Once a table is created, you may need to change its structure. The ALTER TABLE command allows you to add, delete, or modify columns and constraints.

A. Adding a Column (ADD COLUMN)

If you realize you need to track when a product was last restocked:

ALTER TABLE Inventory 

ADD COLUMN LastRestocked DATE;

B. Modifying a Column (ALTER COLUMN or MODIFY COLUMN)

To change the data type or constraints of an existing column (syntax varies between database systems):

ALTER TABLE Inventory 

MODIFY COLUMN StockQuantity DECIMAL(10,2);

C. Dropping a Column (DROP COLUMN)

If a column is no longer needed, you can remove it:

ALTER TABLE Inventory
DROP COLUMN ProductCode;

---

3. Deleting an Entire Structure: DROP TABLE

The DROP TABLE command permanently removes a table and all its data, indexes, and constraints from the database. This action is irreversible.

DROP TABLE Inventory;