DEV Community: Shreya Princy

Alter Tables

Shreya Princy — Sun, 29 Mar 2026 17:45:40 +0000

Modifying TabModifying Tables Using ALTER in PostgreSQL

In real-world database systems, requirements often change after tables are created. Instead of recreating tables, we use the ALTER TABLE command to update structure, constraints, and relationships. Below are practical examples demonstrating how to modify existing tables effectively.

Making Email Mandatory in Customers Table

Initially, the email column may allow NULL values. To enforce that every new record must include an email:

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
Enforcing Unique Usernames

To ensure that no two users share the same username, we add a unique constraint:

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
Adding Price Validation in Products Table

We enforce that product prices must always be greater than zero:

ALTER TABLE products
ADD CONSTRAINT check_price_positive CHECK (price > 0);
Setting Default Status in Orders Table

To automatically assign a default value of 'pending' when no status is provided:

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
Adding Salary Column with Constraints

We extend the employees table by adding a salary column with strict rules:

ALTER TABLE employees
ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);
Updating Foreign Key with Cascade Delete

To ensure that deleting a department also removes all related employees, we modify the foreign key constraint:

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;

Removing a CHECK Constraint from Accounts Table
If there is an existing constraint enforcing balance >= 0, it can be removed as follows:

ALTER TABLE accounts DROP CONSTRAINT accounts_balance_check;

To prevent duplicate transactions for the same user, we enforce a composite unique constraint:

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

Create Tables

Shreya Princy — Sun, 29 Mar 2026 17:36:10 +0000

Students Table

Every student should have a unique identifier. This is achieved using a primary key.

CREATE TABLE students (id SERIAL PRIMARY KEY,name TEXT,age INT);
Employees Table

In many real-world cases, certain fields must always have values. For employees, name and email are required, while phone number can be optional.

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

To prevent duplicate accounts, both username and email should be unique.

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

Products Table
Constraints can also enforce logical rules. A product cannot have a negative stock or a price less than or equal to zero.

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

Orders Table
Defaults make data handling easier. If no status is provided, it automatically becomes "pending". The creation time is also stored automatically.

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

Accounts Table
Here we combine multiple constraints. Account numbers must be unique and cannot be null, while balance should never go below zero.

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

Enrollments Table
A student can enroll in multiple courses, but cannot enroll in the same course more than once. This is handled using a composite unique constraint.

CREATE TABLE enrollments (id SERIAL PRIMARY KEY,student_id INT,course_id INT,UNIQUE (student_id, course_id));
Departments and Employees Relationship

To connect employees with departments, we use a foreign key. This ensures that every employee belongs to a valid department.

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

Sometimes, changes in one table should reflect in another. If a department is deleted, its employees should also be removed. Similarly, updates to department IDs should propagate.

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

CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT,department_idINT,FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE);

Conclusion

SQL constraints play a crucial role in maintaining data accuracy and reliability. By using primary keys, unique constraints, checks, defaults, and foreign keys, we can design robust database systems that prevent invalid data and enforce meaningful relationships between tables.

Idempotency Situation

Shreya Princy — Sun, 29 Mar 2026 17:22:13 +0000

Ensuring Reliable Money Transfers Using Database Transactions

In a digital wallet system similar to PhonePe, Google Pay, or Paytm, users expect their money to be handled accurately and securely. Even a small inconsistency—such as deducting money from one account without crediting another—can lead to serious financial issues. To prevent such problems, database systems rely on ACID properties, especially Durability, to guarantee safe and consistent transactions.

System Overview

The system maintains an accounts table where each user has a balance. Users can:

Store money in their wallet
Transfer money to other users
View their transaction history

To ensure correctness, the database enforces rules like non-negative balances and timestamp tracking.

Performing a Secure Transfer

A typical money transfer between two users (for example, Alice to Bob) is executed within a transaction:

The system begins a transaction
Deducts money from the sender’s account
Adds the same amount to the receiver’s account
Commits the transaction

If all steps succeed, the transaction is permanently saved. After committing, querying the database shows the updated balances correctly.

What Happens During a System Failure?

Failure Before Commit

If the system crashes before the transaction is committed, none of the changes are saved. The database automatically rolls back the transaction, ensuring that no partial updates occur. This prevents situations like money being deducted without being credited.

Failure After Commit

If the system crashes immediately after the commit, the changes remain persisted. When the database restarts, the updated balances remain intact. This behavior demonstrates Durability, meaning once a transaction is committed, it will not be lost.

How the Database Ensures Durability

Modern databases like PostgreSQL use a mechanism called Write-Ahead Logging (WAL). Before applying any changes to the actual data, the database records them in a log file. In case of a crash, the system replays this log to restore the latest committed state. This guarantees that committed transactions survive unexpected failures.

Without proper transaction handling:

Users could lose money
Duplicate transactions might occur
Account balances could become incorrect

By enforcing ACID properties:

Transactions are either fully completed or not applied at all
Data remains consistent at all times
Concurrent operations do not interfere with each other
Committed data is permanently stored

Handling Idempotency in Money Transfers

In real-world payment systems, a single transaction request may be sent multiple times due to network retries, timeouts, or client-side errors. If not handled properly, this can result in duplicate money transfers, leading to incorrect balances and financial inconsistencies.

Simulating Duplicate Transactions

Consider a scenario where a transfer of ₹200 from Alice to Bob is executed more than once:

First execution: Alice → 800, Bob → 700
Second execution (duplicate): Alice → 600, Bob → 900

This shows that the same operation is applied repeatedly, causing unintended deductions and credits.

Problem Observation

The database processes each request independently. Without additional safeguards, it does not recognize whether a transaction has already been executed. As a result, duplicate requests lead to repeated updates.
Why This Is Dangerous

Users may be charged multiple times
Account balances become inaccurate
Trust in the system is reduced

How Real Systems Prevent This

To avoid duplicate processing, modern systems implement idempotency.

Unique Transaction IDs

Each transaction is assigned a unique identifier. Before processing, the system checks if the ID already exists. If it does, the request is ignored.

Idempotency Keys

Clients send a unique key with each request. The server stores this key and ensures that repeated requests with the same key produce the same result without reprocessing.

Database Constraints

Unique constraints or indexes can be applied on transaction identifiers to prevent duplicate entries at the database level.

4. Transaction Logs

Maintaining a transaction history helps verify whether a request has already been completed.

Durability

Shreya Princy — Sun, 29 Mar 2026 17:08:27 +0000

Ensuring Reliable Money Transfers Using Database Transactions

In a digital wallet system similar to PhonePe, Google Pay, or Paytm, users expect their money to be handled accurately and securely. Even a small inconsistency such as deducting money from one account without crediting another can lead to serious financial issues. To prevent such problems, database systems rely on ACID properties , especially Durability, to guarantee safe and consistent transactions.

System Overview

The system maintains an accounts table where each user has a balance. Users can:

Store money in their wallet
Transfer money to other users
View their transaction history

Performing a Secure Transfer:
A typical money transfer between two users (for example, Alice to Bob) is executed within a transaction:

The system begins a transaction
Deducts money from the sender’s account
Adds the same amount to the receiver’s account
Commits the transaction

If all steps succeed, the transaction is permanently saved. After committing, querying the database shows the updated balances correctly.
What Happens During a System Failure?
Failure Before Commit:

Failure After Commit:

When the database restarts, the updated balances remain intact. This behavior demonstrates Durability, meaning once a transaction is committed, it will not be lost.

How the Database Ensures Durability:
Modern databases like PostgreSQL use a mechanism called WAL. Before applying any changes to the actual data, the database records them in a log file. In case of a crash, the system replays this log to restore the latest committed state. This guarantees that committed transactions survive unexpected failures.

Why This Matters:
Without proper transaction handling:

Users could lose money
Duplicate transactions might occur
Account balances could become incorrect

By enforcing ACID properties:

Transactions are either fully completed or not applied at all
Data remains consistent at all times
Concurrent operations do not interfere with each other
Committed data is permanently stored

Step 1: Successful Money Transfer (Transaction)

Example: Transfer 200 from Alice to Bob

UPDATE accounts SET balance = balance - 200, last_updated = CURRENT_TIMESTAMP WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 200,last_updated = CURRENT_TIMESTAMP WHERE name = 'Bob';
COMMIT;

CA 33 - Users, Roles, Groups

Shreya Princy — Sun, 29 Mar 2026 16:57:01 +0000

Task 1: Create report_user with read access only to film:-

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

Task 2: Fix permission denied for customer table:-

GRANT SELECT ON TABLE customer TO report_user;

Task 3: Allow only specific columns from customer:-

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

Task 4: Create support_user with restricted permissions:-

CREATE ROLE support_user WITH LOGIN PASSWORD 'password123';
GRANT CONNECT ON DATABASE dvdrental TO support_user;
GRANT USAGE ON SCHEMA public TO support_user;
Allow SELECT GRANT SELECT ON TABLE customer TO support_user;
Allow UPDATE only on email column GRANT UPDATE (email) ON customer TO support_user;
Ensure DELETE is not allowed REVOKE DELETE ON customer FROM support_user;

Task 5: Remove SELECT access on film from report_user:-

REVOKE SELECT ON TABLE film FROM report_user;

Task 6: Create readonly_group with SELECT on all tables:-

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

Task 7: Create users and add to group:-

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

CA 32 - Filter Assignments

Shreya Princy — Sun, 29 Mar 2026 16:48:06 +0000

Movies where special_features is NULL:-
SELECT * FROM film WHERE special_features IS NULL;
Movies with rental duration > 7 days:-
SELECT *FROM film WHERE rental_duration > 7;
Rental rate = 4.99 AND replacement cost > 20
SELECT *FROM film WHERE rental_rate = 4.99 AND replacement_cost > 20;
Rental rate = 0.99 OR rating = 'PG-13':-
SELECT * FROM film WHERE rental_rate = 0.99 OR rating = 'PG-13';
First 5 movies sorted alphabetically:-
SELECT *FROM film ORDER BY title ASC LIMIT 5;
Skip first 10, get next 3 (highest replacement cost):-
SELECT * FROM film ORDER BY replacement_cost DESC OFFSET 10 LIMIT 3;
Rating in ('G', 'PG', 'PG-13'):-
SELECT * FROM film WHERE rating IN ('G', 'PG', 'PG-13');
Rental rate between 2 and 4:-
SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;
Titles starting with "The":-
SELECT * FROM film WHERE title LIKE 'The%';
First 10 movies with multiple conditions
SELECT * FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%Love%' LIMIT 10;
Titles containing %:-
SELECT *FROM film WHERE title LIKE '%\%%' ESCAPE '\';
Titles containing _
SELECT * FROM film WHERE title LIKE '%_%' ESCAPE '\';
Titles start with A or B and end with s:-
SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
Titles containing Man, Men, or Woman:-
SELECT * FROM film WHERE title ILIKE '%Man%' OR title ILIKE '%Men%' OR title ILIKE '%Woman%';
Titles containing digits:-
SELECT * FROM film WHERE title ~ '[0-9]';
Titles containing backslash ():-
SELECT * FROM film WHERE title LIKE '%\%';
Titles containing "Love" or "Hate":-
SELECT * FROM film
WHERE title ILIKE '%Love%'
OR title ILIKE '%Hate%';
First 5 movies ending with er, or, ar
SELECT *FROM filmWHERE title LIKE '%er' OR title LIKE '%or'OR title LIKE '%ar'
LIMIT 5;

DB-TASK-001

Shreya Princy — Sun, 29 Mar 2026 16:38:19 +0000

Film titles with rental rates:- SELECT title AS "Movie Title", rental_rate AS "Rate" FROM film;
Customer names and email:-

SELECT first_name AS "First Name", last_name AS "Last Name", email FROM customer;

Films sorted by rental rate (desc), then title:-

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC;

Actor names sorted by last name, then first name:- SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name ASC;
Unique replacement costs:- SELECT DISTINCT replacement_cost FROM film;
Film title and duration:- SELECT title, length AS "Duration (min)" FROM film;
Customer active status:- SELECT first_name, last_name, active AS "Is Active" FROM customer;
Film categories sorted alphabetically:- SELECT nameFROM category ORDER BY name ASC;
Films by length (descending):- SELECT title, length FROM film ORDER BY length DESC;
Actor names sorted by first name (descending) SELECT first_name, last_name FROM actor ORDER BY first_name DESC;
Unique ratings:- SELECT DISTINCT rating FROM film;
Unique rental durations:- SELECT DISTINCT rental_duration FROM film;
First unique customer ID based on active status:- SELECT DISTINCT customer_id, active FROM customer ORDER BY customer_id LIMIT 1;
Earliest rental date for each customer:- SELECT customer_id, MIN(rental_date) AS rental_date FROM rental GROUP BY customer_id ORDER BY customer_id;
10 shortest films SELECT title, length FROM film ORDER BY length ASC LIMIT 10;
Top 5 customers with highest customer_id:- SELECT first_name, last_name FROM customer ORDER BY customer_id DESC LIMIT 5;
Unique store IDs from inventory:- SELECT DISTINCT store_id FROM inventory;
Unique replacement_cost sorted ascending SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;
First rental date for each store:- SELECT i.store_id, MIN(r.rental_date) AS rental_date FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id GROUP BY i.store_id ORDER BY i.store_id;
Unique film ratings sorted alphabetically:- SELECT DISTINCT rating FROM film ORDER BY rating ASC;
Films by rating (asc) and length (desc):- SELECT title, rating, length FROM film ORDER BY rating ASC, length DESC;
Actor names sorted last asc, first desc SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;
Films by replacement_cost asc and rental_rate desc SELECT title, replacement_cost, rental_rate FROM film ORDER BY replacement_cost ASC, rental_rate DESC;
Customers sorted last asc, first desc SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;
Rentals sorted by customer_id asc, rental_date desc SELECT * FROM rental ORDER BY customer_id ASC, rental_date DESC;
Films ordered by rental_duration asc and title desc SELECT title, rental_duration FROM film ORDER BY rental_duration ASC, title DESC;

Python Variables, Print

Shreya Princy — Thu, 26 Mar 2026 18:34:19 +0000

How do you print the string “Hello, world!” to the screen?

print("Hello, world!")

2.How do you print the value of a variable name which is set to “Syed Jafer” or your name?

name = "Shreya"

How do you print the variables name, age, and city with labels “Name:”, “Age:”, and “City:”?

name = "Shreya" age = 21 city = "Chennai"
print("Name:", name, "Age:", age, "City:", city)

How do you use an f-string to print name, age, and city in the format “Name: …, Age: …, City: …”?

print(f"Name: {name}, Age: {age}, City: {city}")

How do you concatenate and print the strings greeting (“Hello”) and target (“world”) with a space between them?

greeting = "Hello" target = "world"
print(greeting + " " + target)

How do you print three lines of text with the strings “Line1”, “Line2”, and “Line3” on separate lines?

print("Line1\nLine2\nLine3")

How do you print the string He said, "Hello, world!" including the double quotes?

print('He said, "Hello, world!"')

How do you print the string C:\Users\Name without escaping the backslashes?

print(r"C:\Users\Name")

How do you print the result of the expression 5 + 3?
print(5 + 3)
How do you print the strings “Hello” and “world” separated by a hyphen -?

print("Hello-world")

How do you print the string “Hello” followed by a space, and then print “world!” on the same line?

print("Hello", end=" ")
print("world!")

How do you print the value of a boolean variable is_active which is set to True?

is_active = True
print(is_active)

How do you print the string “Hello ” three times in a row?

print("Hello " * 3)

How do you print the sentence The temperature is 22.5 degrees Celsius. using the variable temperature?

temperature = 22.5
print(f"The temperature is {temperature} degrees Celsius.")

How do you print name, age, and city using the .format() method in the format “Name: …, Age: …, City: …”?

print("Name: {}, Age: {}, City: {}".format(name, age, city))

How do you print the value of pi (3.14159) rounded to two decimal places in the format The value of pi is approximately 3.14?

print(f"The value of pi is approximately {pi:.2f}")

How do you print the words “left” and “right” with “left” left-aligned and “right” right-aligned within a width of 10 characters each?

print(f"{'left':<10}{'right':>10}")

TASK 2

Create a variable named name and assign your name to it. Then print the value of the variable.

name = "Shiny"
print(name)

Create a variable age and assign your age to it. Later, reassign the variable with a new value and print the new value.

age = 20
age = 21
print(age)

Assign the values 5, 10, and 15 to three variables a, b, and c in a single line. Print their values.

a, b, c = 5, 10, 15
print(a, b, c)

Swap the values of two variables x and y without using a third variable. Print their values before and after swapping. x = 5 y = 10 print("Before:", x, y) x, y = y, x

print("After:", x, y)

Define constants PI with appropriate values and print them.

PI = 3.14159
print(PI)

Write a program that calculates the area of a circle using the constant PI and a variable radius. Print the area.

PI = 3.14159
radius = 5
area = PI * radius * radius
print(area)

Define constants for the length and width of a rectangle. Calculate and print the area.

LENGTH = 10
WIDTH = 5
area = LENGTH * WIDTH
print(area)

Define a constant for π (pi) and a variable for the radius. Calculate and print the circumference of the circle.

radius = 5 ,circumference = 2 * PI * radius
print(circumference)

TASK 3

Create a list of five delivery items and print the third item in the list.

items = ["Notebook", "Pencil", "Eraser", "Ruler", "Marker"]
print(items[2])

Add “Glue Stick” to the end of the list and print the updated list.

items.append("Glue Stick")
print(items)

Insert “Highlighter” between the second and third items and print the updated list.

items.insert(2, "Highlighter")
print(items)

Remove “Ruler” from the list and print the updated list.

items.remove("Ruler")
print(items)

Print a sublist containing only the first three items.

print(items[:3])

Convert all item names to uppercase using list comprehension and print.

upper_items = [item.upper() for item in items]
print(upper_items)

Check if “Marker” is in the list and print a message.

print("Marker found" if "Marker" in items else "Marker not found")

Print the number of delivery items in the list.

print(len(items))

Sort the list in alphabetical order and print it.

items.sort()
print(items)

Reverse the list and print it.

items.reverse()
print(items)

Create a list of items with delivery time and print first item and its time.

delivery = [["Notebook", "10AM"], ["Pencil", "11AM"], ["Eraser", "12PM"]]
print(delivery[0])

Count how many times “Ruler” appears in the list and print.

print(items.count("Ruler"))

Find the index of “Pencil” in the list and print it.

print(items.index("Pencil"))

Extend the list with another list and print updated list.

new_items = ["Pen", "Sharpener"]
items.extend(new_items)
print(items)

Clear the list and print it.

items.clear()
print(items)

Create a list with “Notebook” repeated three times and print.

repeat_list = ["Notebook"] * 3
print(repeat_list)

Create a list of lists with item and its length using nested list comprehension.

items = ["Notebook", "Pencil", "Eraser"]
length_list = [[item, len(item)] for item in items]
print(length_list)

Filter items containing letter “e” and print.

filtered = [item for item in items if "e" in item.lower()]
print(filtered)

Remove duplicate items and print unique list. items = ["Notebook", "Pencil", "Notebook", "Eraser"] unique_items = list(set(items)) print(unique_items) print(name)

Consistency

Shreya Princy — Thu, 26 Mar 2026 18:21:59 +0000

A wallet system must always keep balances correct and never allow invalid states. PostgreSQL helps enforce this using constraints, but safe operations still depend on how queries are written.

The balance column is protected by a rule that prevents it from going below zero. If a query tries to violate this, PostgreSQL stops it.

Example of an invalid update:
UPDATE accounts
SET balance = -100
WHERE name = 'Alice';

This fails because the database constraint blocks negative values.

Another invalid case:
UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';

If the balance is only 1000, this also fails for the same reason. These errors are enforced by the database schema itself.

However, transferring money is more complex and must be handled carefully.

A correct transfer must run inside a transaction:
UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice' AND balance >= 300;

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

The condition balance >= 300 ensures that the deduction only happens if enough money is available. If not, no row is updated and the transaction can be safely stopped.

To make the system safe during concurrent transactions, locking is required,there are 2 types of loocking optimistic locking and pesimistic locking.
Optimistic locking: Assumes no conflict and checks for changes before updating, failing if data was modified.
Pessimistic locking: Locks the data before updating to prevent any concurrent access or conflicts.

SELECT balance FROM accounts
WHERE name = 'Alice'
FOR UPDATE;

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

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

This prevents multiple operations from modifying the same account at the same time.

In this system, PostgreSQL constraints prevent invalid data like negative balances, while transactions and locking ensure correct behavior during money transfers. Both are required to maintain consistency and avoid financial errors.

Basic Select SQL Queries

Shreya Princy — Thu, 26 Mar 2026 17:47:06 +0000

SELECT * FROM CITY WHERE ID = 1661;

select * from city where countrycode='JPN';

select name from city where population> 120000 and countrycode='USA';

SELECT DISTINCT CITY FROM STATION WHERE CITY NOT LIKE 'A%' AND CITY NOT LIKE 'E%' AND CITY NOT LIKE 'I%' AND CITY NOT LIKE 'O%' AND CITY NOT LIKE 'U%';

select city, state from station;

SELECT * FROM CITY WHERE CountryCode = 'USA' AND Population > 100000;

SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;

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

Shreya Princy — Thu, 26 Mar 2026 17:27:20 +0000

Step 1: Launch Your Virtual Server ,log into your AWS Management Console.

Search for EC2 in the top search bar and click on it.

Give your server a name, like "My-First-Web-Server".

Step 2:Under Application and OS Images (Amazon Machine Image), select Amazon Linux. It is optimized for AWS.

Ensure the Free tier eligible tag is visible so you don't accidentally run up a bill.

For the Instance type, leave it as t2.micro (or t3.micro depending on your region).

Step 3: Create the Keys to the instance.
To securely connect to your server later, you need a digital key.

Under Key pair (login), click Create new key pair.

Name it something like "web-server-key", leave the defaults , and hit create.

Step 4: Configure the Security Groups
By default, AWS blocks all incoming traffic to protect your server. Since we are building a web server, we need to open the doors for the internet to see it.

Under Network settings, check the box that says Allow SSH traffic from . Set the dropdown to My IP for maximum security.

next step: Check the box for Allow HTTP traffic from the internet. This opens Port 80, which is the universal channel for web browsers to access your site.

Step 5: Launch and Connect
Hit the orange Launch instance button on the right side of your screen.

Once it says "Success", click on the instance ID to go to your instances page. Wait until the "Instance state" says Running.

Select your instance, and click the Connect button at the top.

navigate to the EC2 Instance Connect tab and hit Connect. A black terminal window will pop up in your browser. You are now inside your server.

Step 6: Install the Web Server
Now that we are inside the machine, we need to install the software that serves web pages . Copy and paste these commands into your terminal one by one, hitting Enter after each, Update the system, Let's make sure our software is up to date.

Install Apache: This is the actual web server software.

Start the server: Turn the engine on.

Keep it on: Tell the server to start automatically if the machine ever reboots.

Step 7:It is time to see the work.

Go back to your EC2 dashboard where your instance details are.

Find the Public IPv4 address like 3.85.22.112.

Copy that address, open a new tab in your web browser, paste it in, and hit enter.
You have officially launched a server in the cloud and made it accessible to the entire world.

"Terminate instance" to destroy it and stop any potential future billing.

Atomicity - Design a Reliable Wallet Transfer System with ACID Guarantees

Shreya Princy — Thu, 26 Mar 2026 08:54:15 +0000

Atomicity in Action: Designing a Reliable Wallet Transfer System with ACID Guarantees

Building a wallet system like PhonePe, GPay, or Paytm requires strict data consistency. Even a small error can lead to money loss, duplicate transactions, or incorrect balances. This is why ACID properties, especially Atomicity, are critical in such systems.

Atomicity ensures that all operations in a transaction are completed successfully. If any step fails, the entire transaction is rolled back, and the database remains unchanged.

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 sample data:

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

Initial state of the table:

ID	Name	Balance
1	Alice	1000
2	Bob	500

Successful Transaction

Transfer 200 from Alice to Bob using a transaction:

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

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

After execution:

Name	Balance
Alice	800
Bob	700

Both operations succeed, and the transaction is committed. The data remains consistent.

Failure After Debit Operation

Now introduce an error after deducting money from Alice:

BEGIN;

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

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

The second query fails due to an invalid column name. The database aborts the transaction.

Final state:

Name	Balance
Alice	1000
Bob	500

The deduction from Alice is rolled back. No partial update occurs.

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

ROLLBACK;

Final state remains unchanged:

Name	Balance
Alice	1000
Bob	500

The rollback cancels all changes made during the transaction.

Without Transaction

If the same operations are executed without a transaction:

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

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

Alice’s balance is reduced, but Bob does not receive the money. This leads to inconsistency.

Transactions ensure all operations are executed completely or not at all.
Errors automatically trigger rollback.
Partial updates are prevented.
Data integrity is maintained.
Transactions are essential for financial systems.

Atomicity guarantees that a transaction is treated as a single unit of work. In a wallet system, this ensures that money is neither lost nor duplicated. By using transaction blocks, the system remains consistent even in the presence of failures.