Week 5

5.2 Data Definition Language (DDL)

5.2.1 CREATE TABLE

Syntax and example — creating a CUSTOMER table for a Nigerian microfinance bank:

 

CREATE TABLE CUSTOMER (

    CustomerID    INT           PRIMARY KEY AUTO_INCREMENT,

    BVN           VARCHAR(11)   UNIQUE NOT NULL,

    FirstName     VARCHAR(50)   NOT NULL,

    LastName      VARCHAR(50)   NOT NULL,

    PhoneNumber   VARCHAR(15)   NOT NULL,

    LGA           VARCHAR(100),

    State         VARCHAR(50)   DEFAULT 'Niger State',

    AccountType   ENUM('Savings','Current','Fixed') DEFAULT 'Savings',

    DateOpened    DATE          NOT NULL,

    Balance       DECIMAL(15,2) DEFAULT 0.00,

    IsActive      BOOLEAN       DEFAULT TRUE

);

5.2.2 ALTER TABLE

ALTER TABLE CUSTOMER ADD COLUMN Email VARCHAR(100);

ALTER TABLE CUSTOMER MODIFY COLUMN PhoneNumber VARCHAR(14) NOT NULL;

ALTER TABLE CUSTOMER DROP COLUMN IsActive;