Creating Table

1. Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student.

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

2. Create a table employees where name and email cannot be empty, but phone_number can be optional.

NOT NULL contraint is used to ensure that a attribute cannot be left empty

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15)
);

3. Create a table users where both username and email must be unique across all records.

UNIQUE constaint is allow only unique values and no repeated values

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

*4. Create a table products where price must always be greater than 0 and stock cannot be negative.
*
DECIMAL datatype is used to keep records of money in detail and arthmetic operators are used to check certain values

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

5. Create a table orders where status should default to 'pending' if no value is provided, and created_at should store the current timestamp automatically.

DEFAULT is used to fill any string/ value when a record has no input from user

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

6. Create a table accounts where account_number must be unique and not null balance must always be greater than or equal to 0

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    account_number VARCHAR(50) UNIQUE NOT NULL,
    balance DECIMAL CHECK (balance >= 0) 
);

7. Create a table enrollments where a student can enroll in multiple courses, but the combination of student_id and course_id must be unique.

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT UNIQUE, 
    course_id INT UNIQUE,   
);

8. Create two tables:
departments with id and name
employees with id, name, and department_id
Ensure that department_id in employees must exist in departments.

foreign key refernce from the employee table to the departments table as be used with constraint to ensure the presence of dept_id attribute in both table

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

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

9. When a department is deleted, all related employees are also deleted
When a department ID is updated, it reflects in the employees table

the previous foreign key contraint on the employee table as been dropped and a new constraint with refernces to dept_ id with ON DELETE CASCADE & ON UPDATE CASCADE as one cannot have two foreign constraint on the same table

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

ALTER TABLE employees
ADD FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE;