CREATE TABLES

Tables with constraints

QN:1

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

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

id should uniquely identify each student we use PRIMARY KEY

QN:2

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

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

name and email should not be empty we use NOT NULL phone is optional so no constraint

QN:3

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

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

username and email should not repeat we use UNIQUE

QN:4

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

Query:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
price INT CHECK (price > 0),
stock INT CHECK (stock >= 0)
);

price should be greater than 0 and stock should not be negative we use CHECK

QN: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.

Query:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status TEXT DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

we need default values we use DEFAULT for status and timestamp

QN:6

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

Query:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
account_number TEXT UNIQUE NOT NULL,
balance INT CHECK (balance >= 0)
);

account_number should not be empty and must be unique and balance should not go negative

QN: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.

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

same student should not enroll same course again so we use combination UNIQUE

QN:8

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

Query:
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)
);

employee must belong to valid department so we use FOREIGN KEY

QN:9

Modify the previous foreign key example so that when a department is deleted, all related employees are also deleted and when a department ID is updated, it reflects in the employees table

Query:
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)
ON DELETE CASCADE
ON UPDATE CASCADE
);

we need automatic delete and update so we use CASCADE