Create Table in SQL - CA39

My Thinking and Approach

---

Introduction

In this task, I focused on creating database tables using SQL. Instead of modifying existing tables, the goal here was to define new tables with proper structure and constraints.

This helped me understand how to design tables in a way that ensures data consistency and integrity.

---

Problem Statement

• Create tables with appropriate columns
• Apply constraints like PRIMARY KEY, NOT NULL, UNIQUE, CHECK
• Define default values
• Establish relationships using foreign keys

---

My Initial Thought

At first, I thought:

• Creating tables is just defining columns
• Constraints are optional

But I realized:

• Constraints are important for maintaining correct data
• Table design plays a major role in database performance

---

Key Observation

• PRIMARY KEY uniquely identifies each record
• NOT NULL ensures required fields are filled
• UNIQUE prevents duplicate values
• CHECK enforces conditions
• FOREIGN KEY maintains relationships

---

Solutions

1. Create students table

Code (SQL)

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

Explanation:

• id uniquely identifies each student
• PRIMARY KEY ensures uniqueness

---

2. Create employees table

Code (SQL)

CREATE TABLE employees (
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15)
);

Explanation:

• name and email cannot be null
• phone_number is optional

---

3. Create users table with unique fields

Code (SQL)

CREATE TABLE users (
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

Explanation:

• username and email must be unique

---

4. Create products table with conditions

Code (SQL)

CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

Explanation:

• price must be greater than 0
• stock cannot be negative

---

5. Create orders table with defaults

Code (SQL)

CREATE TABLE orders (
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation:

• status defaults to pending
• created_at stores current time automatically

---

6. Create accounts table

Code (SQL)

CREATE TABLE accounts (
    account_number VARCHAR(20) NOT NULL UNIQUE,
    balance DECIMAL(10,2) CHECK (balance >= 0)
);

Explanation:

• account_number must be unique and not null
• balance must be non-negative

---

7. Create enrollments table with composite key

Code (SQL)

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

Explanation:

• combination of student_id and course_id must be unique

---

8. Create departments and employees with foreign key

Code (SQL)

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Explanation:

• department_id must exist in departments table

---

9. Foreign key with cascade actions

Code (SQL)

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Explanation:

• deleting a department removes related employees
• updating department id updates employee records

---

Final Understanding

• Table creation is not just structure, it includes rules
• Constraints ensure valid and consistent data
• Relationships connect multiple tables effectively

---

Conclusion

This task helped me understand how to design tables properly with constraints and relationships. It also showed how important planning is while creating a database structure.

---