Class 9 of 58 • Database & Table Management (DDL)

SQL CREATE TABLE & DROP TABLE: Structure, Types & Defaults

Tables are the foundational relational structures where all enterprise data resides. A table consists of vertical columns (each with a defined name, data type, and constraints) and horizontal rows of record data. In this class, we learn the complete syntax of CREATE TABLE, auto-incrementing ID generators, temporary tables, and DROP TABLE.

The CREATE TABLE Syntax

To create a table, specify the table name followed by a comma-separated list of column definitions enclosed in parentheses:

Standard Syntax:

CREATE TABLE [IF NOT EXISTS] table_name (
    column1_name data_type [constraints],
    column2_name data_type [constraints],
    ...,
    [table_level_constraints]
);

Hands-on Example: Designing a Student Management Table

Let us construct a production-ready students table incorporating proper data types, primary keys, and default values:

CREATE TABLE IF NOT EXISTS students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    course VARCHAR(30) DEFAULT 'BCA',
    enrollment_date DATE NOT NULL,
    gpa DECIMAL(3, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• INT AUTO_INCREMENT PRIMARY KEY: Unique integer identifier that automatically generates consecutive IDs (1, 2, 3...) on every new row insert.

• NOT NULL: Enforces that a value must be supplied; empty NULL values are rejected.

• UNIQUE: Guarantees that no two students can share the same email address.

• DEFAULT 'BCA': If the user does not specify a course during insert, the database automatically supplies 'BCA'.

• TIMESTAMP DEFAULT CURRENT_TIMESTAMP: Automatically records the exact system date and time when the record is created.

Inspecting Table Schema with DESCRIBE

In MySQL and Oracle, you can inspect the structural definition of any table with DESCRIBE (or DESC):

DESCRIBE students;

Field	Type	Null	Key	Default	Extra
student_id	int	NO	PRI	NULL	auto_increment
first_name	varchar(50)	NO		NULL
email	varchar(100)	NO	UNI	NULL
course	varchar(30)	YES		BCA
gpa	decimal(3,2)	YES		0.00

Creating Tables from Query Results (CTAS)

You can create a new table and populate it with data from an existing table in a single atomic step using CREATE TABLE AS SELECT (CTAS):

-- Create a dedicated table containing only BCA honors students
CREATE TABLE bca_honors AS
SELECT student_id, first_name, last_name, gpa
FROM students
WHERE course = 'BCA' AND gpa >= 3.80;

The DROP TABLE Statement

To permanently delete a table and all its stored rows from disk:

-- Safely remove obsolete table
DROP TABLE IF EXISTS old_backup_students;

Next, let us learn how to modify table structures without deleting existing data using ALTER TABLE!

Frequently Asked Questions (FAQ)

1. What happens if you execute CREATE TABLE for a table that already exists?

Without IF NOT EXISTS, the database compiler throws an error (e.g., 'Table already exists'). Using CREATE TABLE IF NOT EXISTS instructs the database to gracefully bypass creation without throwing an exception.

2. How do you create an auto-incrementing primary key in different SQL dialects?

In MySQL, use INT AUTO_INCREMENT PRIMARY KEY. In PostgreSQL, use SERIAL PRIMARY KEY or GENERATED ALWAYS AS IDENTITY. In SQLite, use INTEGER PRIMARY KEY AUTOINCREMENT. In SQL Server, use INT IDENTITY(1,1) PRIMARY KEY.

3. What is a Temporary Table in SQL?

A temporary table (CREATE TEMPORARY TABLE) exists only for the duration of the current client connection or session. When the connection terminates or closes, the database automatically drops the temporary table and frees its memory.