CREATE & DROP

Lesson 1 of 16 Intermediate Interactive

CREATE DATABASE and CREATE TABLE define your schema.

Data Types

INT � integers
VARCHAR(n) � variable-length string
TEXT � long text
DECIMAL(m,d) � precise numbers
DATE, DATETIME, TIMESTAMP
BOOLEAN (TINYINT(1))

Syntax

MYSQL

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS old_table;

CREATE, ALTER, DROP

SQL

CREATE TABLE products (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(120) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   stock INT DEFAULT 0
);

-- Add a column
ALTER TABLE products
ADD COLUMN category VARCHAR(50) AFTER name;

-- Modify a column
ALTER TABLE products
MODIFY COLUMN price DECIMAL(12,2);

-- Rename a column
ALTER TABLE products
RENAME COLUMN stock TO quantity;

-- Add an index
CREATE INDEX idx_products_name ON products (name);

-- Drop index / column
DROP INDEX idx_products_name ON products;
ALTER TABLE products DROP COLUMN category;

-- Drop the whole table
DROP TABLE IF EXISTS products;

Try It Yourself

Practice

Exercise

Create an orders table with FK to users.

Answer

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Quick Quiz

What does AUTO_INCREMENT do?

Auto-fill values

Auto-generate unique IDs

Speed up queries

Automatically generates sequential unique IDs.

Interview Questions

INT stores up to ~2.1 billion. BIGINT stores up to ~9.2 quintillion. Use BIGINT for large tables or future-proofing.

Next Lesson

Constraints & Keys