Full Stack • Java • System Design • Cloud • AI Engineering

SQL • 2026-06-12

SQL Basics & DDL Operations: Complete Guide

Comprehensive guide to SQL basics and DDL operations including database creation, data types, table operations, constraints, best practices, and interview questions

SQL Basics & DDL Operations

📋 Table of Contents

  1. Introduction to SQL
  2. Database Creation and Management
  3. Data Types
  4. CREATE TABLE
  5. ALTER TABLE
  6. DROP Operations
  7. TRUNCATE vs DELETE
  8. Best Practices
  9. Interview Questions

Introduction to SQL

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases.

SQL Categories

┌─────────────────────────────────────────────────────────┐
│                    SQL COMMANDS                          │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌────────┐ │
│  │   DDL    │  │   DML    │  │   DCL    │  │  TCL   │ │
│  │          │  │          │  │          │  │        │ │
│  │ CREATE   │  │ SELECT   │  │ GRANT    │  │ COMMIT │ │
│  │ ALTER    │  │ INSERT   │  │ REVOKE   │  │ ROLLBK │ │
│  │ DROP     │  │ UPDATE   │  │          │  │ SAVEPT │ │
│  │ TRUNCATE │  │ DELETE   │  │          │  │        │ │
│  └──────────┘  └──────────┘  └──────────┘  └────────┘ │
│                                                          │
└─────────────────────────────────────────────────────────┘
  • DDL (Data Definition Language): Define database structure
  • DML (Data Manipulation Language): Manipulate data
  • DCL (Data Control Language): Control access
  • TCL (Transaction Control Language): Manage transactions

Database Creation and Management

Creating a Database

-- MySQL/PostgreSQL
CREATE DATABASE ecommerce_db;

-- SQL Server
CREATE DATABASE ecommerce_db
ON PRIMARY
(
    NAME = ecommerce_data,
    FILENAME = 'C:\Data\ecommerce_data.mdf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);

-- Oracle
CREATE DATABASE ecommerce_db
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password;

Using a Database

-- MySQL
USE ecommerce_db;

-- SQL Server
USE ecommerce_db;
GO

-- PostgreSQL
\c ecommerce_db

-- Oracle
ALTER SESSION SET CURRENT_SCHEMA = ecommerce_db;

Viewing Databases

-- MySQL
SHOW DATABASES;

-- SQL Server
SELECT name FROM sys.databases;

-- PostgreSQL
\l
-- or
SELECT datname FROM pg_database;

-- Oracle
SELECT * FROM dba_users;

Dropping a Database

-- MySQL/PostgreSQL
DROP DATABASE ecommerce_db;

-- SQL Server
DROP DATABASE ecommerce_db;

-- With safety check
DROP DATABASE IF EXISTS ecommerce_db;

Data Types

Numeric Data Types

Type Description Range Example
TINYINT Very small integer 0 to 255 age TINYINT
SMALLINT Small integer -32,768 to 32,767 quantity SMALLINT
INT/INTEGER Standard integer -2,147,483,648 to 2,147,483,647 user_id INT
BIGINT Large integer -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 transaction_id BIGINT
DECIMAL(p,s) Fixed-point number Precision p, Scale s price DECIMAL(10,2)
NUMERIC(p,s) Same as DECIMAL Precision p, Scale s salary NUMERIC(12,2)
FLOAT Floating-point number Approximate rating FLOAT
REAL Single precision float Approximate temperature REAL
DOUBLE Double precision float Approximate coordinates DOUBLE

String Data Types

Type Description Max Length Example
CHAR(n) Fixed-length string n characters country_code CHAR(2)
VARCHAR(n) Variable-length string n characters email VARCHAR(255)
TEXT Long text 65,535 bytes description TEXT
MEDIUMTEXT Medium text 16,777,215 bytes article MEDIUMTEXT
LONGTEXT Very long text 4,294,967,295 bytes content LONGTEXT
NCHAR(n) Unicode fixed string n characters unicode_name NCHAR(50)
NVARCHAR(n) Unicode variable string n characters unicode_desc NVARCHAR(500)

Date and Time Data Types

Type Description Format Example
DATE Date only YYYY-MM-DD birth_date DATE
TIME Time only HH:MM:SS start_time TIME
DATETIME Date and time YYYY-MM-DD HH:MM:SS created_at DATETIME
TIMESTAMP Timestamp YYYY-MM-DD HH:MM:SS updated_at TIMESTAMP
YEAR Year only YYYY graduation_year YEAR

Binary Data Types

Type Description Max Size Example
BINARY(n) Fixed-length binary n bytes hash BINARY(32)
VARBINARY(n) Variable-length binary n bytes signature VARBINARY(256)
BLOB Binary large object 65,535 bytes image BLOB
MEDIUMBLOB Medium BLOB 16,777,215 bytes video MEDIUMBLOB
LONGBLOB Large BLOB 4,294,967,295 bytes file LONGBLOB

Other Data Types

Type Description Example
BOOLEAN/BOOL True/False is_active BOOLEAN
ENUM Enumerated list status ENUM('active','inactive')
SET Set of values permissions SET('read','write','delete')
JSON JSON data metadata JSON
XML XML data config XML
UUID Universally unique identifier id UUID

CREATE TABLE

Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
    table_constraints
);

Example 1: Simple Table

CREATE TABLE customers (
    customer_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20)
);

Example 2: Table with Constraints

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    date_of_birth DATE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    credit_limit DECIMAL(10,2) DEFAULT 1000.00,
    CONSTRAINT chk_email CHECK (email LIKE '%@%.%')
);

Example 3: Table with Foreign Key

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT,
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Example 4: Composite Primary Key

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    discount DECIMAL(5,2) DEFAULT 0.00,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Example 5: Table with Multiple Constraints

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_code VARCHAR(10) NOT NULL UNIQUE,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    job_title VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    department_id INT,
    manager_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT chk_hire_date CHECK (hire_date <= CURDATE()),
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id),
    CONSTRAINT fk_manager 
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
);

CREATE TABLE AS SELECT (CTAS)

-- Create table from existing table
CREATE TABLE customers_backup AS
SELECT * FROM customers;

-- Create table with specific columns
CREATE TABLE active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE is_active = TRUE;

-- Create table with aggregated data
CREATE TABLE customer_summary AS
SELECT 
    customer_id,
    COUNT(*) as total_orders,
    SUM(total_amount) as total_spent,
    AVG(total_amount) as avg_order_value
FROM orders
GROUP BY customer_id;

Temporary Tables

-- MySQL
CREATE TEMPORARY TABLE temp_sales (
    product_id INT,
    total_quantity INT,
    total_revenue DECIMAL(10,2)
);

-- SQL Server
CREATE TABLE #temp_sales (
    product_id INT,
    total_quantity INT,
    total_revenue DECIMAL(10,2)
);

-- Global temporary table (SQL Server)
CREATE TABLE ##global_temp_sales (
    product_id INT,
    total_quantity INT,
    total_revenue DECIMAL(10,2)
);

ALTER TABLE

Adding Columns

-- Add single column
ALTER TABLE customers
ADD middle_name VARCHAR(50);

-- Add multiple columns
ALTER TABLE customers
ADD COLUMN address_line1 VARCHAR(100),
ADD COLUMN address_line2 VARCHAR(100),
ADD COLUMN city VARCHAR(50),
ADD COLUMN state VARCHAR(50),
ADD COLUMN zip_code VARCHAR(10);

-- Add column with constraints
ALTER TABLE customers
ADD COLUMN loyalty_points INT DEFAULT 0 NOT NULL;

-- Add column after specific column (MySQL)
ALTER TABLE customers
ADD COLUMN suffix VARCHAR(10) AFTER last_name;

-- Add column at first position (MySQL)
ALTER TABLE customers
ADD COLUMN title VARCHAR(10) FIRST;

Modifying Columns

-- Change column data type
ALTER TABLE customers
MODIFY COLUMN phone VARCHAR(30);

-- Change column with constraints
ALTER TABLE customers
MODIFY COLUMN email VARCHAR(150) NOT NULL UNIQUE;

-- Rename column (MySQL 8.0+)
ALTER TABLE customers
RENAME COLUMN phone TO phone_number;

-- Rename column (SQL Server)
EXEC sp_rename 'customers.phone', 'phone_number', 'COLUMN';

-- Rename column (PostgreSQL)
ALTER TABLE customers
RENAME COLUMN phone TO phone_number;

-- Change column definition (PostgreSQL)
ALTER TABLE customers
ALTER COLUMN email TYPE VARCHAR(150),
ALTER COLUMN email SET NOT NULL;

-- Set default value
ALTER TABLE customers
ALTER COLUMN is_active SET DEFAULT TRUE;

-- Drop default value
ALTER TABLE customers
ALTER COLUMN is_active DROP DEFAULT;

Dropping Columns

-- Drop single column
ALTER TABLE customers
DROP COLUMN middle_name;

-- Drop multiple columns (MySQL)
ALTER TABLE customers
DROP COLUMN address_line2,
DROP COLUMN suffix;

-- Drop column if exists
ALTER TABLE customers
DROP COLUMN IF EXISTS temp_column;

Adding Constraints

-- Add primary key
ALTER TABLE customers
ADD PRIMARY KEY (customer_id);

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE;

-- Add unique constraint
ALTER TABLE customers
ADD CONSTRAINT uq_email UNIQUE (email);

-- Add check constraint
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

-- Add not null constraint (PostgreSQL)
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

-- Add composite unique constraint
ALTER TABLE employees
ADD CONSTRAINT uq_employee_code UNIQUE (employee_code, department_id);

Dropping Constraints

-- Drop primary key
ALTER TABLE customers
DROP PRIMARY KEY;

-- Drop foreign key
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

-- Drop unique constraint
ALTER TABLE customers
DROP CONSTRAINT uq_email;

-- Drop check constraint
ALTER TABLE products
DROP CONSTRAINT chk_price;

-- Drop not null constraint (PostgreSQL)
ALTER TABLE customers
ALTER COLUMN phone DROP NOT NULL;

Renaming Tables

-- MySQL
RENAME TABLE customers TO clients;

-- SQL Server
EXEC sp_rename 'customers', 'clients';

-- PostgreSQL
ALTER TABLE customers RENAME TO clients;

-- Oracle
RENAME customers TO clients;

Changing Table Properties

-- Change storage engine (MySQL)
ALTER TABLE customers ENGINE = InnoDB;

-- Change character set (MySQL)
ALTER TABLE customers
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Add table comment
ALTER TABLE customers COMMENT = 'Customer information table';

DROP Operations

DROP TABLE

-- Drop single table
DROP TABLE customers;

-- Drop table if exists
DROP TABLE IF EXISTS customers;

-- Drop multiple tables
DROP TABLE customers, orders, products;

-- Drop table with cascade (PostgreSQL)
DROP TABLE customers CASCADE;

-- Drop table with restrict (default)
DROP TABLE customers RESTRICT;

DROP DATABASE

-- Drop database
DROP DATABASE ecommerce_db;

-- Drop database if exists
DROP DATABASE IF EXISTS ecommerce_db;

DROP vs TRUNCATE vs DELETE

┌─────────────────────────────────────────────────────────────────┐
│                    DROP vs TRUNCATE vs DELETE                    │
├─────────────┬───────────────┬──────────────┬────────────────────┤
│  Operation  │     Speed     │  Rollback    │    Description     │
├─────────────┼───────────────┼──────────────┼────────────────────┤
│    DROP     │   Fastest     │     No       │ Removes table      │
│             │               │              │ structure & data   │
├─────────────┼───────────────┼──────────────┼────────────────────┤
│  TRUNCATE   │     Fast      │  Limited     │ Removes all data,  │
│             │               │              │ keeps structure    │
├─────────────┼───────────────┼──────────────┼────────────────────┤
│   DELETE    │    Slower     │     Yes      │ Removes specific   │
│             │               │              │ rows, keeps struct │
└─────────────┴───────────────┴──────────────┴────────────────────┘

TRUNCATE vs DELETE

TRUNCATE TABLE

-- Remove all rows from table
TRUNCATE TABLE customers;

-- SQL Server with identity reset
TRUNCATE TABLE customers;

-- Cannot use WHERE clause with TRUNCATE
-- TRUNCATE TABLE customers WHERE city = 'New York'; -- ERROR!

Characteristics of TRUNCATE:

  • Removes all rows
  • Cannot use WHERE clause
  • Faster than DELETE
  • Resets AUTO_INCREMENT counter
  • Minimal logging
  • Cannot be rolled back (in most databases)
  • Requires ALTER permission
  • Cannot truncate table with foreign key references

DELETE Statement

-- Delete all rows
DELETE FROM customers;

-- Delete specific rows
DELETE FROM customers
WHERE is_active = FALSE;

-- Delete with subquery
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id 
    FROM customers 
    WHERE registration_date < '2020-01-01'
);

-- Delete with join (MySQL)
DELETE o
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.is_active = FALSE;

Characteristics of DELETE:

  • Can remove specific rows using WHERE
  • Slower than TRUNCATE
  • Fully logged
  • Can be rolled back
  • Triggers are fired
  • Does not reset AUTO_INCREMENT
  • Requires DELETE permission

Comparison Example

-- Create test table
CREATE TABLE test_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    value VARCHAR(50)
);

-- Insert data
INSERT INTO test_data (value) VALUES ('A'), ('B'), ('C');

-- Method 1: DELETE (can rollback)
BEGIN TRANSACTION;
DELETE FROM test_data;
ROLLBACK; -- Data restored

-- Method 2: TRUNCATE (cannot rollback in most cases)
TRUNCATE TABLE test_data; -- Data permanently removed

-- Method 3: DROP (removes table)
DROP TABLE test_data; -- Table no longer exists

Best Practices

1. Naming Conventions

-- ✅ Good: Clear, descriptive names
CREATE TABLE customer_orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL
);

-- ❌ Bad: Unclear abbreviations
CREATE TABLE co (
    oid INT PRIMARY KEY,
    cid INT NOT NULL,
    od DATE NOT NULL
);

2. Use Appropriate Data Types

-- ✅ Good: Appropriate types
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,  -- Exact decimal for money
    quantity INT NOT NULL,
    is_available BOOLEAN DEFAULT TRUE
);

-- ❌ Bad: Inappropriate types
CREATE TABLE products (
    product_id VARCHAR(50) PRIMARY KEY,  -- Waste of space
    name TEXT,                           -- Too large for product name
    price FLOAT,                         -- Imprecise for money
    quantity VARCHAR(10),                -- Should be numeric
    is_available VARCHAR(5)              -- Should be boolean
);

3. Always Use Primary Keys

-- ✅ Good: Every table has a primary key
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- ❌ Bad: No primary key
CREATE TABLE categories (
    name VARCHAR(50) NOT NULL
);

4. Use NOT NULL Where Appropriate

-- ✅ Good: Required fields are NOT NULL
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20)  -- Optional field
);

5. Add Indexes for Foreign Keys

-- ✅ Good: Index on foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    INDEX idx_customer_id (customer_id)
);

6. Use Constraints for Data Integrity

-- ✅ Good: Multiple constraints ensure data quality
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE NOT NULL,
    birth_date DATE NOT NULL,
    CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT chk_email CHECK (email LIKE '%@%.%'),
    CONSTRAINT chk_age CHECK (DATEDIFF(CURDATE(), birth_date) / 365 >= 18),
    CONSTRAINT chk_hire_date CHECK (hire_date >= '2000-01-01')
);

7. Document Your Schema

-- ✅ Good: Well-documented table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Unique product identifier',
    sku VARCHAR(50) NOT NULL UNIQUE COMMENT 'Stock Keeping Unit',
    name VARCHAR(100) NOT NULL COMMENT 'Product display name',
    description TEXT COMMENT 'Detailed product description',
    price DECIMAL(10,2) NOT NULL COMMENT 'Current selling price in USD',
    cost DECIMAL(10,2) NOT NULL COMMENT 'Product cost for margin calculation',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp'
) COMMENT = 'Product catalog table';

8. Plan for Growth

-- ✅ Good: Use BIGINT for high-volume tables
CREATE TABLE page_views (
    view_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    page_url VARCHAR(500) NOT NULL,
    view_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_timestamp (view_timestamp)
);

9. Use Transactions for DDL Changes

-- ✅ Good: Wrap related changes in transaction
BEGIN TRANSACTION;

ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(20);
ALTER TABLE customers ADD COLUMN loyalty_points INT DEFAULT 0;
UPDATE customers SET loyalty_tier = 'Bronze' WHERE loyalty_points < 1000;

COMMIT;

10. Test Before Production

-- ✅ Good: Test on copy first
CREATE TABLE customers_test LIKE customers;
INSERT INTO customers_test SELECT * FROM customers LIMIT 100;

-- Test your changes
ALTER TABLE customers_test ADD COLUMN new_column VARCHAR(50);

-- If successful, apply to production
ALTER TABLE customers ADD COLUMN new_column VARCHAR(50);

Summary

This section covered:

  • ✅ Database creation and management
  • ✅ Comprehensive data types
  • ✅ CREATE TABLE with various constraints
  • ✅ ALTER TABLE operations
  • ✅ DROP operations
  • ✅ TRUNCATE vs DELETE comparison
  • ✅ Best practices for DDL operations

Interview Questions

Question 1: What is the difference between CHAR and VARCHAR?

Answer:

Feature CHAR(n) VARCHAR(n)
Storage Fixed length Variable length
Space Always uses n bytes Uses actual length + 1-2 bytes
Performance Faster (fixed size) Slightly slower
Padding Right-padded with spaces No padding
Use Case Fixed-length data (codes, flags) Variable-length data (names, emails)

Example:

-- CHAR example - always uses 10 bytes
CREATE TABLE example1 (
    country_code CHAR(2),      -- 'US' stored as 'US'
    state_code CHAR(2)         -- 'CA' stored as 'CA'
);

-- VARCHAR example - uses actual length + overhead
CREATE TABLE example2 (
    email VARCHAR(100),        -- '[email protected]' uses ~17 bytes
    name VARCHAR(50)           -- 'John' uses ~5 bytes
);

Question 2: Explain PRIMARY KEY vs UNIQUE constraint

Answer:

Feature PRIMARY KEY UNIQUE
NULL values Not allowed Allowed (one NULL)
Per table Only one Multiple allowed
Index Clustered index (default) Non-clustered index
Purpose Uniquely identify row Ensure uniqueness

Example:

CREATE TABLE users (
    user_id INT PRIMARY KEY,           -- Only one, NOT NULL
    email VARCHAR(100) UNIQUE,         -- Can have multiple UNIQUE
    phone VARCHAR(20) UNIQUE,          -- Another UNIQUE constraint
    ssn VARCHAR(11) UNIQUE             -- NULL allowed in UNIQUE
);

-- Valid inserts
INSERT INTO users VALUES (1, '[email protected]', '1234567890', '123-45-6789');
INSERT INTO users VALUES (2, '[email protected]', NULL, NULL);  -- NULL in UNIQUE is OK
INSERT INTO users VALUES (3, NULL, '9876543210', NULL);         -- NULL email is OK

-- Invalid insert
INSERT INTO users VALUES (NULL, '[email protected]', '5555555555', '987-65-4321');  -- ERROR: PRIMARY KEY cannot be NULL

Question 3: What is the difference between DELETE and TRUNCATE?

Answer:

-- DELETE - Row-by-row deletion
DELETE FROM orders WHERE order_date < '2020-01-01';

-- Characteristics:
-- ✅ Can use WHERE clause
-- ✅ Can be rolled back
-- ✅ Fires triggers
-- ✅ Slower (row-by-row)
-- ❌ Does not reset AUTO_INCREMENT

-- TRUNCATE - Fast table clearing
TRUNCATE TABLE orders;

-- Characteristics:
-- ✅ Very fast
-- ✅ Resets AUTO_INCREMENT
-- ✅ Minimal logging
-- ❌ Cannot use WHERE clause
-- ❌ Cannot be rolled back (usually)
-- ❌ Does not fire triggers

Question 4: Explain AUTO_INCREMENT and its behavior

Answer:

-- AUTO_INCREMENT automatically generates unique IDs
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

-- Inserting without specifying ID
INSERT INTO products (name) VALUES ('Product A');  -- product_id = 1
INSERT INTO products (name) VALUES ('Product B');  -- product_id = 2
INSERT INTO products (name) VALUES ('Product C');  -- product_id = 3

-- Delete a row
DELETE FROM products WHERE product_id = 2;

-- Next insert continues from last value
INSERT INTO products (name) VALUES ('Product D');  -- product_id = 4 (not 2!)

-- Reset AUTO_INCREMENT
ALTER TABLE products AUTO_INCREMENT = 1;

-- Check current AUTO_INCREMENT value
SELECT AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE TABLE_NAME = 'products';

-- Manually insert specific ID
INSERT INTO products (product_id, name) VALUES (100, 'Product E');
-- Next auto-generated ID will be 101

Question 5: What are the different types of constraints?

Answer:

-- 1. PRIMARY KEY - Unique identifier
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT
);

-- 2. FOREIGN KEY - Referential integrity
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 3. UNIQUE - Ensure uniqueness
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

-- 4. NOT NULL - Prevent NULL values
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- 5. CHECK - Validate data
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    age INT,
    salary DECIMAL(10,2),
    email VARCHAR(100),
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65),
    CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT chk_email CHECK (email LIKE '%@%.%')
);

-- 6. DEFAULT - Set default value
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    quantity INT DEFAULT 1
);

Question 6: Explain ON DELETE CASCADE vs ON DELETE SET NULL

Answer:

-- ON DELETE CASCADE - Delete child records when parent is deleted
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

-- Example:
-- If customer with ID 1 is deleted, all their orders are also deleted

-- ON DELETE SET NULL - Set foreign key to NULL when parent is deleted
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE SET NULL
);

-- Example:
-- If customer with ID 1 is deleted, customer_id in orders becomes NULL

-- ON DELETE RESTRICT (default) - Prevent deletion if child records exist
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);

-- Example:
-- Cannot delete customer if they have orders

-- Comparison table
/*
┌──────────────────┬────────────────────────────────────────┐
│     Action       │              Result                    │
├──────────────────┼────────────────────────────────────────┤
│ CASCADE          │ Delete child records                   │
│ SET NULL         │ Set foreign key to NULL                │
│ RESTRICT         │ Prevent deletion (error)               │
│ NO ACTION        │ Same as RESTRICT                       │
│ SET DEFAULT      │ Set to default value (if supported)    │
└──────────────────┴────────────────────────────────────────┘
*/

Question 7: How do you handle large text data?

Answer:

-- Choose appropriate text type based on size
CREATE TABLE content (
    content_id INT PRIMARY KEY,
    
    -- TINYTEXT - Up to 255 bytes
    status_message TINYTEXT,
    
    -- TEXT - Up to 65,535 bytes (~64 KB)
    description TEXT,
    
    -- MEDIUMTEXT - Up to 16,777,215 bytes (~16 MB)
    article_content MEDIUMTEXT,
    
    -- LONGTEXT - Up to 4,294,967,295 bytes (~4 GB)
    book_content LONGTEXT
);

-- Best practices:
-- 1. Use VARCHAR for short text (< 255 chars)
-- 2. Use TEXT for medium content (articles, descriptions)
-- 3. Use MEDIUMTEXT for large content (books, documents)
-- 4. Consider storing large files externally (S3, CDN) and store URL
-- 5. Use FULLTEXT index for searching

-- FULLTEXT index example
CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT idx_search (title, content)
);

-- Search using FULLTEXT
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

Question 8: What is the difference between DATETIME and TIMESTAMP?

Answer:

Feature DATETIME TIMESTAMP
Range 1000-01-01 to 9999-12-31 1970-01-01 to 2038-01-19
Storage 8 bytes 4 bytes
Time Zone No conversion Converts to UTC
Auto-update No Yes (with ON UPDATE)
Use Case Fixed dates/times Auto-tracking changes

Example:

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    
    -- DATETIME - Store exact date/time
    event_datetime DATETIME NOT NULL,
    
    -- TIMESTAMP - Auto-track creation
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- TIMESTAMP - Auto-track updates
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert example
INSERT INTO events (event_datetime) VALUES ('2024-12-25 10:00:00');

-- created_at and updated_at are automatically set
-- If you UPDATE the record, updated_at changes automatically

Question 9: How do you create a composite primary key?

Answer:

-- Method 1: Inline definition
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Method 2: Separate constraint
CREATE TABLE enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE NOT NULL,
    grade VARCHAR(2),
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id)
);

-- Method 3: ALTER TABLE
CREATE TABLE course_schedule (
    course_id INT,
    day_of_week VARCHAR(10),
    time_slot TIME
);

ALTER TABLE course_schedule
ADD PRIMARY KEY (course_id, day_of_week, time_slot);

-- Use case: Many-to-Many relationships
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Question 10: Explain ENUM vs SET data types

Answer:

-- ENUM - Single value from predefined list
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending'
);

-- Can only store ONE value
INSERT INTO orders VALUES (1, 'pending');      -- ✅ Valid
INSERT INTO orders VALUES (2, 'shipped');      -- ✅ Valid
-- INSERT INTO orders VALUES (3, 'pending,shipped');  -- ❌ Invalid

-- SET - Multiple values from predefined list
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    permissions SET('read', 'write', 'delete', 'admin')
);

-- Can store MULTIPLE values
INSERT INTO users VALUES (1, 'read');                    -- ✅ One permission
INSERT INTO users VALUES (2, 'read,write');              -- ✅ Two permissions
INSERT INTO users VALUES (3, 'read,write,delete');       -- ✅ Three permissions
INSERT INTO users VALUES (4, 'read,write,delete,admin'); -- ✅ All permissions

-- Query SET values
SELECT * FROM users WHERE FIND_IN_SET('write', permissions);
SELECT * FROM users WHERE permissions LIKE '%write%';

-- Comparison
/*
┌──────────┬─────────────────┬──────────────────────┐
│   Type   │  Values Stored  │      Use Case        │
├──────────┼─────────────────┼──────────────────────┤
│   ENUM   │  Single value   │  Status, Category    │
│   SET    │  Multiple values│  Permissions, Tags   │
└──────────┴─────────────────┴──────────────────────┘
*/

Summary

This comprehensive guide covered:

✅ SQL Fundamentals

  • SQL command categories (DDL, DML, DQL, DCL, TCL)
  • Database creation and management
  • Comprehensive data type reference

✅ DDL Operations

  • CREATE TABLE with various constraints
  • ALTER TABLE for modifications
  • DROP operations and comparisons

✅ Data Integrity

  • Primary keys and foreign keys
  • Unique constraints and check constraints
  • Referential integrity with CASCADE options

✅ Interview Preparation

  • 10 common SQL interview questions
  • Detailed explanations with examples
  • Comparison tables and best practices

Key Takeaways:

  1. Always use appropriate data types for efficiency
  2. Implement constraints for data integrity
  3. Use foreign keys with proper CASCADE options
  4. Choose between TRUNCATE and DELETE based on needs
  5. Document your schema with comments
  6. Test DDL changes before production deployment