DEV Community: Saranya R

Alter Tables

Saranya R — Sun, 29 Mar 2026 09:53:43 +0000

1) You have a table customers with a column email that currently allows NULL values. Modify the table so that future entries must always have an email.

Answer:
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

2) In the users table, ensure that the username column is unique across all records using an ALTER statement.

Answer:
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

3) In the products table, enforce that price must always be greater than 0 using an ALTER command.

Answer:
ALTER TABLE products
ADD CONSTRAINT price_check CHECK (price > 0);

4) Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.

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

5) Alter the employees table by adding a new column salary such that

It cannot be NULL

It must always be greater than 10,000

Answer:

add column:
ALTER TABLE employees
ADD COLUMN salary INT;
applying conditions:
ALTER TABLE employees
ALTER COLUMN salary SET NOT NULL;

ALTER TABLE employees
ADD CONSTRAINT salary_check CHECK (salary > 10000);

6) Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.

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

7) In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.

Answer:
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

8) In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.

Answer:
ALTER TABLE payments
ADD CONSTRAINT unique_user_transaction
UNIQUE (user_id, transaction_id);

Create Tables

Saranya R — Sun, 29 Mar 2026 09:43:42 +0000

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

Design a Reliable Wallet Transfer System with ACID Guarantees pt - 4 (Durability)

Saranya R — Sun, 29 Mar 2026 09:26:29 +0000

Durability – ensures that once a transaction is successfully committed, its changes are permanently stored in the database, even in case of system failures.

the 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
);
the accounts table is created successfully

Then the dummy data is added to the table

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

Now the initial balance in both accounts are Alice = 1000 & Bob = 500
To test if durability works properly, perform a transaction and commit it

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

After COMMIT:
Alice’s balance becomes 800
the change is permanently saved
To verify durability, even if the system crashes immediately after commit:
When the database restarts, Alice’s balance should be 800
the committed transaction should not be lost
If the committed data persists after restart or failure, then durability works properly
If the system restores the database to the last committed state and does not lose committed changes, durability is maintained
Durability is maintained through mechanisms like write-ahead logging (WAL), disk storage, and crash recovery

Durability guarantees that once a transaction is committed, its effects are permanent and survive any subsequent failures

Design a Reliable Wallet Transfer System with ACID Guarantees pt - 3 (Isolation)

Saranya R — Sun, 29 Mar 2026 09:07:42 +0000

Isolation – ensures that multiple transactions execute independently without interfering with each other, even when running concurrently.

the 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
);
the accounts table is created successfully.
then the dummy data is added to the table

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

Now the initial balance in both accounts are Alice = 1000 & Bob = 500
To test if isolation works properly, simulate two concurrent transactions:
Transaction 1:
BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE name = 'Alice';
Transaction 2 (runs at the same time):
BEGIN;
UPDATE accounts
SET balance = balance - 700
WHERE name = 'Alice';
Without isolation:
both transactions may read the same initial balance (1000)
both updates may proceed based on outdated data
this can lead to incorrect final balance (race condition)
With proper isolation:
one transaction is executed or completed before the other affects the same data
the second transaction will either wait or see the updated value
inconsistent updates are prevented
If the database prevents both transactions from interfering with each other and maintains correct results, then isolation works properly
Isolation is maintained through mechanisms like optimistic and pessimistic locks
Isolation guarantees that concurrent transactions do not affect each other's execution and the database remains reliable under parallel operations

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

Saranya R — Sun, 29 Mar 2026 08:32:42 +0000

Step 1: Launch an EC2 Instance

1.1. Log in to the AWS Management Console.
1.2. Navigate to EC2 Dashboard → Launch Instance.
1.3. Choose an Amazon Machine Image (AMI), e.g., Amazon Linux 2.
1.4. Select an instance type (e.g., t2.micro for free tier).
1.5. Configure instance details and add storage (default is fine).
1.6. Configure Security Group:
1.6.1. Allow SSH (port 22) from your IP.
1.6.2. Allow HTTP (port 80) from anywhere (0.0.0.0/0).

Step 2: Connect to Your Instance

2.1. Open terminal.
2.2. Run:
2.2.1. ssh -i your-key.pem ec2-user@
2.3. Replace with the instance’s public IP from the EC2 dashboard.

Step 3: Install a Webserver

3.1. Update packages:
sudo yum update -y
Install Apache (httpd):
sudo yum install -y httpd
3.2. Start the service:
sudo systemctl start httpd
sudo systemctl enable httpd

Step 4: Deploy a Simple Webpage

4.1. Navigate to the web root:
cd /var/www/html
4.2. Create an index.html file:
echo "

Hello from EC2 Webserver!

" | sudo tee index.html

Step 5: Access from Outside

5.1. Copy the Public IPv4 address of your EC2 instance.
5.2. Open a browser and enter:
http://
5.3. You should see your custom webpage.

Design a Reliable Wallet Transfer System with ACID Guarantees pt - 2 (Consistency)

Saranya R — Thu, 26 Mar 2026 18:44:35 +0000

Consistency - ensures the database always moves from one valid state to another while follow the rules, constraint, etc

the 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 );
the accounts table is created successfully
then the dummy data is added to the table
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);
Now the initial balance in both accounts are Alice=1000 & Bob=500
to try test if the consistency(balance >= 0) works properly
UPDATE accounts
SET balance = -100
WHERE name = 'Alice';
the above query will throw an error, and the update will be rejected.
to try test if a amt more than balance can be deducted
BEGIN;

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

COMMIT;
If query fails because resulting balance would be negative then consistency works properly

Consistency is maintained through two layers: Database-Level Constraints Application / Transaction Logic

Consistency guarantees that every transaction respects the rules of the system.

Design a Reliable Wallet Transfer System with ACID Guarantees pt - 1 (Atomicity)

Saranya R — Thu, 26 Mar 2026 18:33:59 +0000

Atomicity - A transaction is treated as an all-or-nothing operation which means if any one of the step fails the transaction rollsback and starts from start

the 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 );

the accounts table is created successfully

then the dummy data is added to the table
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);
Now the initial balance in both accounts are Alice=1000 & Bob=500
Now a Successful transaction is simulated
BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice' AND balance >= 200;

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

COMMIT;

the balance in both accounts are Alice=800 & Bob=700
Now a Unsuccessful transaction is simulated
BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice' AND balance >= 200;

//wrong col name
UPDATE accounts
SET balance = balance + 200
WHERE username = 'Bob';

COMMIT;

The second query fails hence transaction is not made the balance remains the same (Alice=1000 & Bob=500)

In a wallet system, Atomicity ensures that money is never deducted without being credited. If any part of the transaction fails, the system restores the original state, maintaining consistency and trust

how DNS resolver is happening.

Saranya R — Thu, 26 Mar 2026 18:09:15 +0000

How DNS Resolution Works: From URL to Website

When you type a website URL into your browser, a process happens in milliseconds to connect you to the server.

Step by step process:

Step 1: User Enters URL
the desired domain is entered
Step 2: Browser Cache Check
Browser first checks if it already knows the IP address from a previous request then the process stops here.
Step 3: OS Cache Check
If the browser does not have the record, the operating system checks its local DNS cache.
Step 4: Query to DNS Resolver
If no cached result exists, the request is sent to a DNS resolver (ISP).
Step 5: Resolver Queries Root Server
The resolver asks a root name server, The root server responds with the address of the TLD server for .com.
Step 6: Resolver Queries TLD Server
The resolver then asks the .com TLD server, The TLD server responds with the authoritative name server for that domain.
Step 7: Resolver Queries Authoritative Server
The resolver asks the authoritative server, The authoritative server responds with the correct IP address.
Step 8: Response Returned to Client
The resolver sends the IP address back to your browser.
Step 9: Browser Connects to Server
Now that the browser knows the IP address, it sends an HTTP/HTTPS request to the web server, and the website loads.

All these steps happen a few milliseconds but still a server can process request from all across the globe

Different sorting methodologies

Saranya R — Thu, 26 Mar 2026 17:46:57 +0000

Sorting Methodologies

Sorting is process of arranging elements in a specific order to facilitate efficient searching, retrieval, and analysis of data.

Squares of a Sorted Array

Problem: Given a sorted array that may include negative numbers, return a new array of the squares in sorted order.

Approach: Use a two-pointer technique. Compare values from both ends of the array, place the larger square at the end of the result array, and move inward.

Key Point:
Sorting is avoided entirely by leveraging the structure of the input array.

Merge Two Sorted Linked Lists

Problem: Merge two sorted linked lists into a single sorted list.

Approach: Use a dummy node and two pointers. Compare nodes from both lists and attach the smaller one to the result.

Key Point:
This is efficient merging of already sorted data.

First and Last Occurrence in a Sorted Array

Problem: Find the first and last positions of a target value in a sorted array.

Approach: Perform binary search twice — once to find the left boundary and once to find the right boundary.

Key Point:
Sorting enables precise searching instead of scanning the entire array.

Search in Rotated Sorted Array

Problem: Search for a target value in a rotated sorted array.

Approach: Use a modified binary search. At each step, determine which half is sorted and decide if the target lies within that half.

Key Point:
Even when the array is rotated, partial ordering provides enough structure for efficient search.

Majority Element

Problem: Find the element that appears more than floor(n/2) times.

Approach: Use the Boyer–Moore Voting Algorithm to track a candidate and adjust its count.

Key Point:
Sorting is unnecessary. A linear-time algorithm solves the problem optimally.

Reverse and Deduplicate Linked Lists

Approach: Iteratively reverse pointers to flip the list.

Reverse Linked List:
Approach: Iteratively reverse pointers to flip the list.

Remove Duplicates:
Approach: Traverse the sorted list and skip adjacent duplicate nodes.

Key Point:
Sorting ensures duplicates are adjacent, making removal straightforward.

Sort a Linked List

Problem: Sort a linked list efficiently.

Approach: Use merge sort. Split the list using slow and fast pointers, recursively sort each half, and merge them.

Key Point:
Merge sort is preferred for linked lists because it does not require random access.

Leetcode Two sum problem

Saranya R — Thu, 26 Mar 2026 16:36:01 +0000

1. Two Sum

Working:

seen keeps track of numbers we’ve already visited and their indices.
For each num, we calculate complement = target-num.
If the complement is already in seen, we return the pair of indices.
Otherwise, we store the current number with its index.

167. Two Sum II - Input Array Is Sorted

Working:

Start with two pointers left at the beginning, right at the end.
Compute the sum of the two numbers.
If the sum equals the target, return their indices. - If the sum is too small, move left forward to increase the sum.
If the sum is too large, move right backward to decrease the sum.

Setup a DNS hosted zone in Route53 in AWS.

Saranya R — Wed, 25 Mar 2026 18:09:39 +0000

Step 1: Open Route 53

Log in to AWS Console
Search for Route 53
Click Hosted Zones

Step 2: Create a Hosted Zone

Click Create hosted zone
Enter: Domain name (e.g., MyWebsite.com)
Type: Public hosted zone Click Create

AWS will automatically create default records:
- NS (Name Server)
- SOA (Start of Authority)

Step 3: Update Name Servers
After creating the hosted zone:

Copy the NS records from Route 53
Go to domain registrar
Replace existing name servers with AWS name servers

This connects your domain to AWS

Step 4: Add DNS Records

Now you can map your domain to resources.
Example: Point domain to a server Click Create record Choose: Record type: A Value: Your server IP Save

Example: Connect to AWS services
EC2 → A record
Load Balancer → Alias record
S3 static website → Alias record

Step 5: Verify Setup

Wait for DNS propagation (few minutes to 48 hours)
Open your domain in a browser
It points to my resource

How a request originates from cllient and reaches the server ?

Saranya R — Wed, 25 Mar 2026 17:58:43 +0000

FLOW:
User > DNS > Connection Setup > Secure Connection > Request > Server > Response > Browser

1. User Action
user type a URL or click a link. The browser prepares a request.

2. DNS Lookup
The domain name (google.com) is converted into the IP address of the domain.

3. Connection Setup
A TCP connection is established between client and server for reliable and secure connection.

4. Secure Connection (HTTPS)
If the site uses HTTPS, a secure (encrypted) connection is created.

5. HTTP Request
The browser sends a request (GET, POST, PUT) to the server.

6. Server Processing
The server processes the request and interacts with the database.

7. HTTP Response
The server sends back a response (HTML, JSON, etc.) for the request sent.

8. Browser Rendering
The browser displays the result as a webpage.