Question Answer

How to Create a Table and Insert Data into It (SQL for Beginners)

This guide explains how to create a table and insert data using SQL. It is written for B.Tech CSE students learning web technologies and databases. Examples are generic and work on popular systems like MySQL, PostgreSQL, and SQLite with minor adjustments.

What Is a Table?

A table is a structured collection of related data in rows and columns. Each column has a name and a data type (like INT, VARCHAR, DATE), and each row stores one record.

Step 1: Select or Create a Database

First, make sure you are using the correct database.

-- Create a database (optional)
CREATE DATABASE college_db;

-- Switch to that database (MySQL, MariaDB)
USE college_db;

Step 2: Create a Table

Use the CREATE TABLE statement. Define columns, data types, and constraints such as PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK (where supported).

-- Create a "students" table
CREATE TABLE students (
  student_id    INT PRIMARY KEY,
  first_name    VARCHAR(50)  NOT NULL,
  last_name     VARCHAR(50)  NOT NULL,
  email         VARCHAR(100) UNIQUE,
  branch        VARCHAR(20)  NOT NULL,
  semester      INT          CHECK (semester BETWEEN 1 AND 8),
  join_date     DATE         DEFAULT CURRENT_DATE
);

• student_id: Unique identifier for each student (Primary Key).
• VARCHAR(n): Text with a maximum length n.
• NOT NULL: Value must be provided.
• UNIQUE: No duplicates allowed (useful for emails).
• CHECK: Restricts allowed values (PostgreSQL, newer MySQL versions with sql_mode, etc.).
• DEFAULT: Fills a value automatically if none is provided.

Auto-Incrementing IDs (Optional)

If you want the database to generate IDs automatically:

• MySQL/MariaDB: use AUTO_INCREMENT
• PostgreSQL: use SERIAL or GENERATED AS IDENTITY
• SQLite: use INTEGER PRIMARY KEY AUTOINCREMENT (special rule)

-- MySQL/MariaDB style
CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(50) NOT NULL,
  email      VARCHAR(100) UNIQUE,
  branch     VARCHAR(20) NOT NULL,
  semester   INT,
  join_date  DATE DEFAULT CURRENT_DATE
);

Step 3: Insert Data

Use the INSERT INTO statement. You can insert a single row, multiple rows, or select from another table.

1) Insert a Single Row (Specify All Columns)

INSERT INTO students (student_id, first_name, last_name, email, branch, semester, join_date)
VALUES (101, 'Anita', 'Sharma', 'anita.sharma@example.com', 'CSE', 6, '2025-01-10');

2) Insert a Single Row (Let Defaults Apply)

Omit columns that have DEFAULT values or allow NULL. If using auto-increment, omit the ID.

-- With auto-increment ID
INSERT INTO students (first_name, last_name, email, branch, semester)
VALUES ('Rahul', 'Verma', 'rahul.verma@example.com', 'CSE', 6);

3) Insert Multiple Rows at Once

INSERT INTO students (student_id, first_name, last_name, email, branch, semester, join_date)
VALUES
  (102, 'Meera', 'Khan',   'meera.khan@example.com',   'CSE', 6, '2025-01-11'),
  (103, 'Vikram','Patel',  'vikram.patel@example.com', 'CSE', 6, '2025-01-12'),
  (104, 'Sara',  'Das',    'sara.das@example.com',     'CSE', 6, '2025-01-12');

4) Insert into Selected Columns Only

Column order in the list must match the VALUES order.

INSERT INTO students (student_id, first_name, last_name, branch)
VALUES (105, 'Neha', 'Rao', 'CSE');

5) Insert from Another Table (INSERT INTO ... SELECT)

-- Example: copy final-year CSE students into an alumni table
CREATE TABLE alumni AS
SELECT student_id, first_name, last_name, email, branch, join_date
FROM students
WHERE semester = 8;

-- Or append:
INSERT INTO alumni (student_id, first_name, last_name, email, branch, join_date)
SELECT student_id, first_name, last_name, email, branch, join_date
FROM students
WHERE semester = 8;

Verify Your Data

SELECT * FROM students;

Best Practices

• Always define a PRIMARY KEY (use auto-increment or a natural key if stable).
• Choose suitable data types (INT for numbers, VARCHAR for short text, DATE for dates).
• Use NOT NULL and CHECK to enforce valid data.
• Use UNIQUE for fields like email or roll number.
• Prefer multiple-row INSERT for better performance.
• Wrap batches of inserts in transactions when needed:

  BEGIN;
  INSERT INTO students (...);
  INSERT INTO students (...);
  COMMIT; -- or ROLLBACK;
      

Common Errors and Fixes

• Data type mismatch: Ensure values match column types (no text into INT).
• NOT NULL violation: Provide values for NOT NULL columns or set DEFAULTs.
• Duplicate key: Do not insert a row with an existing PRIMARY KEY or UNIQUE value.
• Wrong column order: Match the column list with the VALUES order.
• Date format issues: Use ISO format 'YYYY-MM-DD' for DATE.

Quick Recap

1. Create or select a database.
2. Define a table with proper columns and constraints using CREATE TABLE.
3. Insert data using INSERT INTO with single or multiple rows.
4. Verify results with SELECT and use transactions for safety.