🛠️ SQL Tutorial: CREATE and ALTER TABLE (with Explanation)

In this tutorial, you’ll learn how to create a new table using CREATE TABLE and how to change that table later using ALTER TABLE. We will use Bangladeshi employee data for practical examples.

📌 1️⃣ CREATE TABLE — Explained

CREATE TABLE is used to define a new table with columns, data types, and constraints.

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  department VARCHAR2(30),
  salary NUMBER(8, 2),
  hire_date DATE
);

Explanation:

id NUMBER PRIMARY KEY → A unique identifier for each employee
name VARCHAR2(50) → Text up to 50 characters
department VARCHAR2(30) → Name of the department (HR, IT, etc.)
salary NUMBER(8,2) → Numeric value like 50000.00
hire_date DATE → Employee joining date

📋 Sample Data (Bangladeshi Employees)

id	name	department	salary	hire_date
1	Arif	Accounts	50000	2021-01-15
2	Nasrin	HR	58000	2022-03-01
3	Faruk	IT	60000	2023-06-10

🧱 2️⃣ ALTER TABLE — Explained

ALTER TABLE is used to modify the structure of an existing table — adding/removing columns or changing data types.

➕ Add a new column

ALTER TABLE employees
ADD email VARCHAR2(100);

Explanation: Adds an email column to store employee email addresses.

✏️ Modify a column type

ALTER TABLE employees
MODIFY salary NUMBER(10, 2);

Explanation: Increases the precision of the salary column to allow bigger salaries.

❌ Drop a column

ALTER TABLE employees
DROP COLUMN department;

Explanation: Removes the department column and all its data from the table.

🔐 Add a constraint

ALTER TABLE employees
ADD CONSTRAINT emp_name_not_null CHECK (name IS NOT NULL);

Explanation: Ensures that name must always have a value (not NULL).

📚 Why These Are Important

CREATE TABLE is the starting point of your database design.
ALTER TABLE allows flexibility to adapt your schema over time.
Constraints protect data integrity (like unique values or not-null rules).

📌 Summary:
CREATE TABLE defines your table with its columns and data types.
ALTER TABLE lets you add, drop, or modify columns, and enforce rules like NOT NULL or CHECK.

Trust Coding : Programming

🧱 CREATE & ALTER TABLE in SQL with Explanation