Forem: Santhosh V

CA 40 - Alter Tables

Santhosh V — Sun, 29 Mar 2026 17:26:07 +0000

Today I practiced ALTER TABLE in SQL.
This is used when we want to change an existing table.

1. Make Email NOT NULL (customers)

ALTER TABLE customers
MODIFY email VARCHAR(100) NOT NULL;

Now email is required

2. Make Username UNIQUE (users)

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

No duplicate usernames

3. Add CHECK on Price (products)

ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);

price must be > 0

4. Default Status (orders)

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

if not given → pending

5. Add Salary Column (employees)

ALTER TABLE employees
ADD salary INT NOT NULL CHECK (salary > 10000);

salary required + > 10000

6. Auto Delete Employees (FK)

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

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

delete department → employees also deleted

7. Remove CHECK (accounts)

ALTER TABLE accounts
DROP CONSTRAINT check_balance;

removed balance rule

8. Unique Combination (payments)

ALTER TABLE payments
ADD CONSTRAINT unique_payment UNIQUE (user_id, transaction_id);

same pair not allowed twice

Final Thought

ALTER TABLE = update your table rules
Very useful in real projects

CA 39 - Create Tables

Santhosh V — Sun, 29 Mar 2026 17:23:03 +0000

Today I practiced some basic SQL constraints. I kept it simple and tried different rules like primary key, unique, not null, check, and foreign key.

1. Students Table (Unique ID)

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

id is unique for every student

2. Employees Table (No Empty Name & Email)

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

name & email must be filled
phone is optional

3. Users Table (Unique Username & Email)

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

no duplicates allowed

4. Products Table (Valid Price & Stock)

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

price > 0
stock ≥ 0

5. Orders Table (Default + Timestamp)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

status auto = pending
time auto added

6. Accounts Table (Safe Balance)

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

no duplicate account numbers
no negative balance

7. Enrollments Table (No Duplicate Entry)

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

same student can't join same course twice

8. Foreign Key (Departments & Employees)
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

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

employee must belong to a valid department

9. Auto Delete & Update (Advanced FK)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

delete department → employees also deleted
update department id → auto update in employees

Final Thought

Constraints = rules for your data
Without rules → data becomes messy

CA 38 - Idempotency Situation

Santhosh V — Sun, 29 Mar 2026 17:15:19 +0000

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);

INSERT INTO accounts VALUES
(1, 'Alice', 5000),
(2, 'Bob', 3000);

Transfer

UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 1000 WHERE name = 'Bob';

I ran the same transfer twice.

Result
Alice → 3000 Bob → 5000

Problem

Money can be deducted multiple times
Data becomes wrong

Simple Fix

Use a transaction ID:

CREATE TABLE transactions (
    txn_id VARCHAR(50) PRIMARY KEY
);
INSERT INTO transactions VALUES ('TXN1');

CA 37 - Durability

Santhosh V — Sun, 29 Mar 2026 17:03:43 +0000

I built a simple wallet system like GPay / PhonePe.
Users can:
Store money
Send money
View transactions

Here I focused on Durability

Because once money is sent, it should never be lost.

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Transfer Money

BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';
COMMIT;

After Restart

I restarted the DB and checked again:

SELECT * FROM accounts;

After COMMIT, data is saved permanently Even if system crashes, data is not lost

If crash happens:

Before commit the changes gone After commit the changes safe

CA 35 - Consistency

Santhosh V — Sun, 29 Mar 2026 16:19:26 +0000

I worked on a simple wallet system like GPay / PhonePe / Paytm.
Users can:

Store money
Send money
View transaction history
even a small mistake can cause:
Money loss
Duplicate transactions
Wrong balances

created a simple accounts table:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Added some dummy data:

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Now I tried some invalid operations to see what happens.

Case 1: Sending more money than available

UPDATE accounts
SET balance = balance - 2000
WHERE name = 'Alice';

Alice has only 1000

Result: Query fails

Reason:
CHECK (balance >= 0) does not allow negative balance

Case 2: Directly setting negative balance

UPDATE accounts
SET balance = -100
WHERE name = 'Bob';

The database is enforcing rules (constraints).
This rule:

CHECK (balance >= 0)

ensures:
Balance is always valid No negative values This is handled automatically by the database

CA 33 - Users, Roles, Groups

Santhosh V — Sun, 29 Mar 2026 16:04:09 +0000

Task 1: Create a read-only user for film table

Here I created a user called report_user. This user should only read data from the film table.

CREATE ROLE report_user LOGIN PASSWORD 'password123';
GRANT CONNECT ON DATABASE dvdrental TO report_user;
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON film TO report_user;

Task 2: Fix permission error for customer table

When I tried to access the customer table using report_user, it showed permission denied.

So I fixed it by giving SELECT permission.

GRANT SELECT ON customer TO report_user;

Task 3: Restrict columns in customer table

Now I don’t want full access. I only want customer_id, first_name, and last_name.

REVOKE SELECT ON customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) 
ON customer TO report_user;

Task 4: Create support_user with limited update

Here I created another user called support_user.

This user:

Can read customer table
Can update only email column
Cannot delete anything

CREATE ROLE support_user LOGIN PASSWORD 'password123';
GRANT CONNECT ON DATABASE dvdrental TO support_user;
GRANT USAGE ON SCHEMA public TO support_user;
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;

Task 5: Remove film access from report_user

Now I removed access from film table.

REVOKE SELECT ON film FROM report_user;

Task 6: Create readonly_group

This group will have SELECT access on all tables.

CREATE ROLE readonly_group;
GRANT CONNECT ON DATABASE dvdrental TO readonly_group;
GRANT USAGE ON SCHEMA public TO readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

Task 7: Add users to readonly_group

Now I created two users and added them to the group.

CREATE ROLE analyst1 LOGIN PASSWORD 'password123';
CREATE ROLE analyst2 LOGIN PASSWORD 'password123';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

CA 34 - Atomicity - Design a Reliable Wallet Transfer System with ACID Guarantees

Santhosh V — Sun, 29 Mar 2026 15:10:23 +0000

Today I worked on a wallet system like GPay or PhonePe, where users can send money to each other. The main focus was Atomicity from ACID properties.

Atomicity means “all or nothing”.
If a transaction fails in between, everything should go back to the original state.

Transaction
I wrote a transaction like this

BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';
COMMIT;

Now Testing Failure

I intentionally broke the second query (credit to Bob).

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';
UPDATE accounts
SET balancee = balance + 200
WHERE name = 'Bob';
COMMIT;

BEGIN - start transaction
Do all operations
If success - COMMIT
If error - ROLLBACK

Atomicity ensures no partial transactions, which is very important in payment systems.

CA 32 - Filter Assignments

Santhosh V — Sun, 29 Mar 2026 14:45:35 +0000

Today I practiced writing SQL queries using different conditions. This helped me understand how to filter data properly.

First, I used conditions like WHERE to get movies with rental rate greater than 3, or based on multiple conditions like rental rate and replacement cost.

Then I learned using AND / OR to combine conditions. For example, getting movies that are either rated “PG” or have a cheap rental rate.

I also practiced sorting and limiting using ORDER BY, LIMIT, and OFFSET. Like showing top 10 movies or skipping some and getting the next few.

Next, I used BETWEEN to find movies within a range (like rental duration between 3 and 7 days).

I also tried pattern matching using LIKE, such as:

Titles starting with “A”
Titles ending with “e”
Titles containing words like “Man” or “Woman”

Then I worked with NULL values, like finding customers without email or movies without special features.

I also filtered data using dates, like finding rentals between two dates.

WHERE - filter data
AND / OR - combine conditions
ORDER BY - sort
LIMIT & OFFSET - control results
BETWEEN - range filter
LIKE - pattern search
NULL - missing values

CA 31 - Select Queries from DVD Rental database

Santhosh V — Sun, 29 Mar 2026 14:41:55 +0000

Today I practiced some basic SQL queries using the DVD Rental database. It helped me understand how to get data from tables in different ways.

First, I learned how to select specific columns and also rename them using aliases. For example, showing movie titles and rental rates with better names.

Then I worked with sorting data using ORDER BY. I sorted films by rental rate, actors by name, and customers in different ways (ascending and descending).

Next, I used DISTINCT to get unique values, like different ratings, rental durations, and replacement costs.

I also tried filtering and limiting results, like getting the top 5 customers or the 10 shortest movies.

One important thing I learned was using GROUP BY with functions like MIN() to find things like the earliest rental date for each customer.

I even used JOIN to connect tables, like getting store-wise rental data.

CA 29 - Setup a DNS hosted zone in Route53 in AWS.

Santhosh V — Sun, 29 Mar 2026 14:36:37 +0000

Today I learned how to create a DNS hosted zone in AWS. It’s not that hard once you understand the steps.

First, I went to AWS and opened Route 53. Then I clicked on Hosted Zones and selected Create Hosted Zone.

Next, I entered my domain name (like mywebsite.com) and selected Public Hosted Zone. Then I clicked create.

After creating, AWS automatically gave me some NS (Name Server) records and SOA record. These are important.

Now the main step - I went to my domain registrar (where I bought the domain) and updated the nameservers with the ones AWS gave.

After that, I came back to Route 53 and created a record:

Click Create Record
Choose type A record
Add value (like IP address of my server)

Now I saved it.

After some time (DNS takes a little time), I opened my domain in the browser… and it worked

CA 28 - Create a simple EC2 instance and run a webserver and access it from outside.

Santhosh V — Sun, 29 Mar 2026 14:34:54 +0000

Today I tried creating a small server in AWS and accessed it from my browser. It’s actually very simple.

First, I went to AWS and opened EC2. Then I clicked “Launch Instance”. I gave it a name, selected Amazon Linux, and chose a small free-tier instance.

Next, I created a key pair (this is important for login) and downloaded it. Then in network settings, I allowed HTTP (port 80) so I can access the website from outside.

After that, I launched the instance.

Once it started running, I connected to it using SSH. After logging in, I installed a web server using this

Once it started running, I connected to it using SSH. After logging in, I installed a web server using this command:

sudo yum install httpd -y

Then I started the server:

sudo systemctl start httpd

I also enabled it so it runs always:

sudo systemctl enable httpd

Then I created a simple webpage:

echo "Hello from my EC2 server" | sudo tee /var/www/html/index.html

CA 27 - Write a blog on how DNS resolver is happening.

Santhosh V — Sun, 29 Mar 2026 14:31:29 +0000

When you type google.com, your computer doesn’t understand that name. It needs an IP address.

First, your computer checks: “Do I already know this IP?” If yes, it uses it directly.

If no, it asks a DNS resolver for help.

Now the resolver goes and finds the IP:

It asks: “Who handles .com?”
Then: “Where is google.com?”
Then: “What is its IP?”

Finally, it gets the IP address.

It sends that IP back to your computer. Now your browser connects to the server and opens the website.

Also, it remembers (caches) the IP, so next time it’s faster.