Creating and Managing Tables in SQL

Introduction

Tables are the foundation of any relational database. They store structured data in rows and columns, defining the schema of a database. In this tutorial, we will cover how to create, modify, and manage tables in SQL using various SQL commands.

Creating a Table

To create a table in SQL, use the CREATE TABLE statement. A table consists of columns with defined data types and constraints.

Syntax:

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

Example:

Creating a students table:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18),
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE DEFAULT CURRENT_DATE
);

Explanation:

student_id INT PRIMARY KEY: Unique identifier for each student.

name VARCHAR(50) NOT NULL: Name cannot be empty.

age INT CHECK (age >= 18): Ensures students are at least 18 years old.

email VARCHAR(100) UNIQUE: No two students can have the same email.

enrollment_date DATE DEFAULT CURRENT_DATE: Defaults to the current date if not provided.

Viewing Table Structure

To check the structure of a table:

DESCRIBE students; -- MySQL
PRAGMA table_info(students); -- SQLite
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'students'; -- PostgreSQL

Modifying a Table

You can modify a table using the ALTER TABLE statement.

Adding a Column:

ALTER TABLE students ADD COLUMN phone VARCHAR(15);

Modifying a Column:

ALTER TABLE students ALTER COLUMN age SET DEFAULT 20; -- PostgreSQL

Renaming a Column:

ALTER TABLE students RENAME COLUMN phone TO contact_number;

Deleting a Column:

ALTER TABLE students DROP COLUMN contact_number;

Deleting a Table

If you need to remove a table, use the DROP TABLE statement.

DROP TABLE students;

Truncating a Table

To delete all records without removing the structure:

TRUNCATE TABLE students;

Summary

• Use CREATE TABLE to define a new table with appropriate constraints.
• Use ALTER TABLE to modify existing tables.
• Use DROP TABLE to remove a table completely.
• Use TRUNCATE TABLE to delete all records but keep the table structure.

With these concepts, you can effectively create and manage tables in SQL.