Table Manipulation (CREATE, ALTER, DROP)

Beyond manipulating the data within tables, SQL provides a powerful set of commands for managing the structure of the tables themselves. These are known as Data Definition Language (DDL) commands.

You have been creating tables in all the earlier lessons as part of the Database preparation, but now we will learn the syntax behind those operations.

1. CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database. When creating a table, you must define its columns and the data type for each column. It is also best practice to define constraints, such as a PRIMARY KEY.

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
    PRIMARY KEY (column_name)
);

Example: Creating a Library Members Table

Operation: Create a new table to store information about library members.

Query:

DROP TABLE IF EXISTS Members; -- Reset table for a clean state
CREATE TABLE Members (
    MemberID INTEGER PRIMARY KEY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    JoinDate DATE
);

-- Insert a sample record so we can see the column changes later
INSERT INTO Members (FirstName, LastName, JoinDate) 
VALUES ('John', 'Doe', '2023-01-01');

SELECT 'Table Created and Row Inserted' AS Status;

Explanation:

• This creates a new table called Members.
• MemberID is an integer that is designated as the PRIMARY KEY for the table.
• FirstName and LastName are variable-length strings (VARCHAR) that cannot be empty (NOT NULL).
• JoinDate is a date field that can be left empty (NULL).
• Note: We inserted one row so that when we "check the change" in the next steps, we have data to look at!

Result: A new table named Members exists with one sample record.

2. ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.

Add a Column

Operation: Add a new column for the member's email address to the Members table.

Query:

ALTER TABLE Members
ADD Email VARCHAR(255);

-- Check the change
SELECT * FROM Members;

Result: The Members table now has a new Email column. All existing rows will have a NULL value for this new column.

Drop a Column

Operation: Remove the JoinDate column from the Members table.

Query:

ALTER TABLE Members
DROP COLUMN JoinDate;

-- Check the change
SELECT * FROM Members;

Result: The JoinDate column and all its data are permanently removed from the Members table.

Rename a Column

Operation: Change the name of the FirstName column to GivenName.

Query:

ALTER TABLE Members
RENAME COLUMN FirstName TO GivenName;

-- Check the change
SELECT * FROM Members;

Result: The FirstName column has been successfully renamed to GivenName. All existing data remains intact under the new column name.

Modify a Column's Data Type

Operation: Change the column to allow for longer names.

Query (MySQL Syntax):

ALTER TABLE Members
MODIFY COLUMN GivenName VARCHAR(150);

Query (SQL Server/PostgreSQL Syntax):

ALTER TABLE Members
ALTER COLUMN GivenName TYPE VARCHAR(150);

Query (Oracle Syntax):

ALTER TABLE Members
MODIFY GivenName VARCHAR2(150);

Result: The column's capacity is increased to 150 characters (using VARCHAR2 in Oracle), allowing for longer names to be stored.

3. DROP TABLE Statement

The DROP TABLE statement is used to completely remove an existing table in a database.

Syntax

DROP TABLE table_name;

Example

Operation: Delete the Members table.

Query:

DROP TABLE Members;

-- Verify (this should now throw an error because the table is gone)
-- SELECT * FROM Members;
SELECT 'Table Dropped' AS Status;

Warning: This is a permanent action. When you drop a table, all the data, indexes, triggers, constraints, and permission specifications for that table are also permanently deleted. There is no way to undo this command. Be extremely careful when dropping tables.

Hands-on Exercise

Task:

1. Create a table named Books with two columns: BookID (INTEGER PRIMARY KEY) and Title (VARCHAR(255)).
2. Use ALTER TABLE to add a column named Author (VARCHAR(100)) to the Books table.
3. Insert one record into the table and select all columns to verify the change.

Your Query:

-- Write your query here

Expected Results: