Create Tables

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

Answer:
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT,
age INT
);

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

Answer:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
phone_number TEXT
);

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

Answer:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE,
email TEXT UNIQUE
);

4) Create a table products where price must always be greater than 0 and stock cannot be negative.

Answer:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
price INT 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.

Answer:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status TEXT 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

Answer:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
account_number TEXT UNIQUE NOT NULL,
balance INT 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.

Answer:
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);

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.

Answer:
Table 1:
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name TEXT
);

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

9) Modify the previous foreign key example so that:

When a department is deleted, all related employees are also deleted

When a department ID is updated, it reflects in the employees table

Answer:
Table 1:
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name TEXT
);

Table 2:
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
);