Create Tables

1. Students table with unique id
CREATE TABLE students ( id SERIAL PRIMARY KEY, name TEXT,age INT);

PRIMARY KEY ensures each student is uniquely identified.

2. Employees table with required fields
CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL,phone_number TEXT);

NOT NULL ensures name and email cannot be empty, phone is optional.

3. Users table with unique values
CREATE TABLE users (id SERIAL PRIMARY KEY,username TEXT UNIQUE,email TEXT UNIQUE);

UNIQUE prevents duplicate usernames and emails.

4. Products table with constraints
CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,price INT CHECK (price > 0),stock INT CHECK (stock >= 0));

CHECK ensures valid price and non-negative stock.

5. Orders table with defaults
CREATE TABLE orders (id SERIAL PRIMARY KEY,status TEXT DEFAULT 'pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

DEFAULT assigns automatic values if none provided.

6. Accounts table with rules
CREATE TABLE accounts (id SERIAL PRIMARY KEY,account_number TEXT UNIQUE NOT NULL,balance INT CHECK (balance >= 0));

Ensures valid account number and no negative balance.

7. Enrollments with composite uniqueness
CREATE TABLE enrollments (student_id INT,course_id INT,UNIQUE (student_id, course_id));

Prevents duplicate enrollment for same student-course pair.

8. Foreign key relationship
CREATE TABLE departments (id SERIAL PRIMARY KEY,name TEXT);

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

FOREIGN KEY ensures employees belong to valid departments.

9. Cascade update and delete
CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT,department_id INT,FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE);

ON DELETE CASCADE removes employees when a department is deleted.
ON UPDATE CASCADE updates department_id automatically.