DEV Community: Mohammed Azim J

CA 30 - Basic Select SQL Queries

Mohammed Azim J — Sun, 29 Mar 2026 13:53:04 +0000

First problem was Select By ID. The question was to select all columns from CITY table where ID = 1661.

SELECT *
FROM CITY
WHERE ID = 1661;

This was simple, we just filter using WHERE and ID. Since we need all columns, we used *.

Next was Japanese Cities Attributes. We need to get all columns for cities in Japan.

SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN';

Here we filter rows where country code is JPN. Again * because all columns needed.

Next was Revising the Select Query 2. We need city names where population > 120000.

SELECT NAME
FROM CITY
WHERE POPULATION > 120000;

Here we only need NAME column so we did not use *.

Next was Weather Observation Station 9. We need city names that do not start with vowels.

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%';

I used NOT LIKE because we need cities not starting with vowels and DISTINCT to remove duplicates.

Next was Weather Observation Station 1. We need city and state from station table.

SELECT CITY, STATE
FROM STATION;

Here just selecting two columns.

Next was Revising the Select Query. We need all columns where population > 100000.

SELECT *
FROM CITY
WHERE POPULATION > 100000;

Used WHERE to filter population.

Next was Weather Observation Station 4. Count number of duplicate city entries.

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

This was interesting. Total cities minus unique cities gives duplicate count.

Next was Japanese Cities Name. We need names of cities in Japan.

SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN';

Same logic as earlier but only NAME column.

Last was Select All SQL. Display all columns from CITY.

SELECT *
FROM CITY;

This is the most basic SELECT query.

After doing these problems I understood basic SELECT, WHERE, DISTINCT, COUNT, LIKE, and filtering. These are very basic SQL but very important because almost every query uses these concepts.

CA 28 – Create a simple EC2 instance and run a webserver

Mohammed Azim J — Sun, 29 Mar 2026 13:44:16 +0000

This task was about creating a simple EC2 instance in AWS and running a web server and accessing it from the internet.

First i went to AWS EC2 dashboard and launched a new instance. I selected Amazon Linux and a small instance type . Then i created a key pair and downloaded it so i can connect to the server using SSH.

One important step here was security group , I allowed port 22 for SSH and port 80 for HTTP otherwise the website will not open from browser.

After the instance started I connected to it using SSH and installed a web server (apache/httpd). Then I started the web server service and created a simple HTML file in the web server folder.

After that I copied the public IP address of the EC2 instance and opened it in the browser. The webpage loaded, so that means the web server is accessible from outside.

So overall steps were: create EC2 -> allow ports -> connect using SSH -> install web server -> start service -> open public IP in browser.

CA 29 – Setup a DNS Hosted Zone in Route53 (AWS)

Mohammed Azim J — Sun, 29 Mar 2026 13:41:51 +0000

This task was about setting up a DNS hosted zone in AWS Route53. Before doing this, I only knew that DNS converts domain name into IP address, but I did not know how we actually configure DNS in real systems. This exercise helped me understand that part a little better.

First I logged into AWS console and searched for Route53. Inside Route53 there is something called Hosted Zones. Hosted zone is basically a place where we store DNS records for a domain. So if we own a domain like example.com, we create a hosted zone for that domain and then add records like A record, CNAME etc.

So I created a hosted zone by giving the domain name and selecting public hosted zone. After creating it, AWS automatically created some default records like NS and SOA records. The NS records are important because they tell the internet that AWS Route53 is handling DNS for this domain.

Then the next step was to add records. I added an A record which maps a domain name to an IP address. For example, I mapped my domain to a server IP so that when someone types the domain name in the browser, it will go to that server.

One thing I understood here is DNS is not instant. After changing records, it takes some time to propagate. Sometimes a few minutes, sometimes longer.

So overall the steps I did were:
First create hosted zone
Then note the name servers
Then add DNS records like A record or CNAME
Then wait for DNS propagation

This exercise helped me understand that DNS is like a phonebook of the internet. Route53 is basically AWS service that manages that phonebook.

CA 40 – Alter Tables

Mohammed Azim J — Sun, 29 Mar 2026 13:37:39 +0000

This exercise was about ALTER TABLE. Instead of creating table again, we modify existing table like adding constraints, defaults, columns etc.

Make email NOT NULL in customers table

sql
id="h4r9b2"
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

This makes sure future rows must have email.

Make username unique in users table

sql
id="9zv1qx"
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

This prevents duplicate usernames.

Add check constraint price > 0 in products table

sql
id="g6p8la"
ALTER TABLE products
ADD CONSTRAINT price_check CHECK (price > 0);

Now price cannot be zero or negative.

Set default status 'pending' in orders table

sql
id="s8k3po"
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

If status not given, it will automatically become pending.

Add salary column in employees table with conditions

sql
id="x2w7mn"
ALTER TABLE employees
ADD COLUMN salary INT NOT NULL;

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

Salary cannot be null and must be > 10000.

Modify foreign key so delete department deletes employees

sql
id="f5t9yr"
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;

Now deleting department deletes employees automatically.

Remove check constraint balance >= 0 from accounts table

sql
id="k7d3qp"
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

This removes the check constraint.

Make combination of user_id and transaction_id unique in payments table

sql
id="m1c8zs"
ALTER TABLE payments
ADD CONSTRAINT unique_payment UNIQUE (user_id, transaction_id);

This prevents duplicate transaction per user.

From this exercise I learned ALTER TABLE is used to modify table structure without deleting table. Very useful when database already has data.

CA 39 – Create Tables

Mohammed Azim J — Sun, 29 Mar 2026 11:27:05 +0000

This exercise was about creating tables with different constraints like primary key, unique, not null, check, default and foreign key. I wrote the queries and tried to understand why we use each constraint.

Create students table with id unique

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

Here id is primary key so it will be unique automatically and identify each student.

Create employees where name and email cannot be empty

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

phone_number is optional so no NOT NULL.

Users table where username and email must be unique

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

UNIQUE makes sure no duplicate username or email.

Products table where price > 0 and stock not negative

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

CHECK constraint used for conditions.

Orders table with default status and timestamp

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

DEFAULT automatically inserts value.

Accounts table

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

Enrollments table unique combination

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

This prevents same student enrolling same course twice.

Departments and Employees with foreign key

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

Foreign key ensures department must exist.

Foreign key with delete and update cascade

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

If department deleted, employees also deleted. If department id updated, it updates here also.

From this exercise I understood constraints are very important because they prevent wrong data from entering into database and maintain consistency.

CA 38 – Idempotency Situation

Mohammed Azim J — Sun, 29 Mar 2026 11:24:00 +0000

This task was about what happens if same transaction runs multiple times, like due to network retry or user clicking twice.

First I checked initial data:

```sql id="t9z4an"
SELECT * FROM accounts;




Alice -> 1000
Bob -> 500

Then I did a transfer (Alice → Bob 200):



```sql id="u2x9pl"
BEGIN;

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

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

COMMIT;

Now balances:

Alice -> 800
Bob -> 700

Then I ran same transaction again (same query again):

```sql id="l8k2ws"
BEGIN;

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

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

COMMIT;




Now balances became:

Alice -> 600
Bob -> 900

So same request executed twice and money got transferred twice.
Database did not stop it.

So I understood this point:
DB alone will not prevent duplicate transactions.

Then I thought how real systems solve this.

One idea is using transaction_id:



```sql id="p7n3ye"
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    txn_id TEXT UNIQUE,
    sender TEXT,
    receiver TEXT,
    amount INT
);

Then before inserting:

```sql id="q3m8hv"
INSERT INTO transactions (txn_id, sender, receiver, amount)
VALUES ('TXN123', 'Alice', 'Bob', 200);




If same txn_id comes again, it will fail because of UNIQUE constraint.

So same transaction will not run again.

So what I understood:

Running same query again = duplicate money transfer
Database allows it
Idempotency must be handled using unique request id or logic

So idempotency means even if same request comes multiple times, result should happen only once.

CA 37 – Durability (ACID)

Mohammed Azim J — Sun, 29 Mar 2026 11:06:36 +0000

This one is about Durability, means once data is saved (commit), it should not disappear even if system crashes.

First I checked initial data:

SELECT * FROM accounts;

Alice -> 1000
Bob -> 500

Then I did a transfer:

BEGIN;

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

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

COMMIT;

After commit I checked again:

SELECT * FROM accounts;

Alice -> 700
Bob -> 800

So transfer done.

Now imagine system crash or restart. After reconnecting I ran same query again:

SELECT * FROM accounts;

Still:
Alice -> 700
Bob -> 800

So data is not lost. That is durability.

Then I thought what if crash happens before commit → changes not saved.
If crash happens after commit → data stays safe.

So database makes sure once COMMIT happens, data is stored properly (in disk/logs).

So simple idea:

Before commit → not permanent
After commit → permanent

That is durability, even crash also cannot remove committed data.

CA 36 – Isolation (ACID)

Mohammed Azim J — Sun, 29 Mar 2026 10:36:07 +0000

This one was about Isolation, basically what happens when two people try to use the same account at the same time.

I used two sessions (like opening two terminals).

First I checked data:

SELECT * FROM accounts;

Alice -> 1000
Bob -> 500

Now in Session 1:

BEGIN;

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

I did not commit yet.

Now in Session 2, I tried:

SELECT * FROM accounts WHERE name = 'Alice';

It still shows 1000, not 200.
So uncommitted change is not visible → no dirty read.

Then in Session 2 I tried to update:

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

This got stuck (waiting).
Because Session 1 is still holding the lock.

Then I commit in Session 1:

COMMIT;

Now Session 2 continues and updates based on new value.

So final balance becomes:
1000 - 800 - 300 = -100 (but this fails due to CHECK constraint)

So transaction fails and rollback happens.

Then I tried changing isolation level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Default level, works like above (no dirty reads).

Then tried:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Now if two transactions conflict, one will fail with error instead of waiting silently.

So what I understood:

Isolation makes sure one transaction does not mess with another
Uncommitted data is not visible
Database uses locks to control access
Prevents issues like dirty read and lost update

So even if two users try to send money at same time, DB keeps things safe.

CA 35 – Consistency (ACID)

Mohammed Azim J — Sun, 29 Mar 2026 10:24:50 +0000

This exercise was about Consistency in databases using the same wallet system accounts table. Atomicity was about all or nothing transactions, but Consistency is about making sure the data always follows the rules defined in the database. The database should never go into an invalid state.

In our accounts table we already have a constraint:

balance INT NOT NULL CHECK (balance >= 0)

This means balance should never become negative. So the database itself is enforcing a rule.

First I checked the initial data.

SELECT * FROM accounts;

Alice -> 1000
Bob -> 500

Everything is valid.

Then I tried to deduct more money than Alice has.

BEGIN;

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

COMMIT;

This failed and PostgreSQL showed an error saying the check constraint failed because balance cannot be negative. So the database did not allow the update. This shows consistency is maintained by constraints.

Then I tried directly setting a negative balance.

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

Again this failed because of the CHECK constraint. So we cannot manually put invalid data also.

Then I tested inside a transaction.

BEGIN;

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

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

COMMIT;

This worked because no rule was broken and balances were still valid.

Then I understood something important. Consistency is maintained in two ways:

Database level (constraints like CHECK, NOT NULL, PRIMARY KEY)
Application level (logic like cannot transfer if balance is low)

For example database prevents negative balance using CHECK constraint. But database will not automatically check if Alice has enough balance before transfer unless we write logic for that in transaction or application code.

So consistency means before transaction and after transaction, the database must be in a valid state and all rules must be satisfied.

CA 34 – Atomicity – Reliable Wallet Transfer System (ACID)

Mohammed Azim J — Sun, 29 Mar 2026 10:21:57 +0000

This exercise was about understanding Atomicity in databases using a wallet transfer system like PhonePe or GPay. The main idea is when money is transferred from one account to another, both debit and credit must happen together. If one fails, everything should rollback. That is what Atomicity means either everything happens or nothing happens.

First we created the accounts table and inserted dummy data like Alice and Bob with balances.

Before doing any transfer, I checked the balances.

SELECT * FROM accounts;

Suppose Alice sends 200 to Bob. We must do this inside a transaction block.

Correct Transaction (Atomic Transfer):

BEGIN;

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

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

COMMIT;

Here what happens is first Alice balance reduces, then Bob balance increases, and finally COMMIT saves the changes permanently.

If we check balances after commit:
Alice = 800
Bob = 700

So transfer successful.

Then we tested Atomicity by introducing an error after the debit step.

Transaction With Error (Testing Atomicity):

BEGIN;

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

-- Intentional error
UPDATE accounts
SET balance = balance + 200
WHERE name = 'NonExistingUser';

COMMIT;

This will cause an error because the user does not exist or the query fails. When error happens, PostgreSQL will not complete the transaction.

Then we run:

ROLLBACK;

After rollback, if we check balances again:

SELECT * FROM accounts;

We will see:
Alice = 1000
Bob =500

So even though we deducted money from Alice in the first query, because the second query failed, the whole transaction was cancelled. This proves Atomicity – partial update did not happen.

Then we tested another failure case where we forced an error after debit.

BEGIN;

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

Force error
SELECT 1/0;

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

COMMIT;

This gives divide by zero error. Because of that error, the transaction fails and we rollback.

Again checking balances shows no change. So money was not lost from Alice even though debit query ran earlier. That means database protected the data using Atomicity.

So finally what I understood from this exercise is, transactions are very important in banking or wallet systems. Without transactions, money may get deducted but not added to the other person, which is very dangerous. Atomicity ensures that both debit and credit happen together, otherwise nothing happens.

So the main commands used in this exercise are:
BEGIN – start transaction
UPDATE – modify balance
COMMIT – save changes
ROLLBACK – undo changes

This experiment clearly showed that PostgreSQL follows ACID properties and prevents partial updates in money transfer systems.

Users, Roles, Groups

Mohammed Azim J — Sun, 29 Mar 2026 10:13:37 +0000

Task 1: Create a login role report_user that can only read from the film table
CREATE ROLE report_user WITH LOGIN PASSWORD 'report123';

GRANT SELECT ON film TO report_user;

This creates a login user and gives only SELECT permission on film table.

Task 2: report_user cannot access customer table – Fix it
GRANT SELECT ON customer TO report_user;

This gives read access to customer table.

Task 3: Allow report_user to see only customer_id, first_name, last_name

First remove full access:

REVOKE SELECT ON customer FROM report_user;

Then give column level access:

GRANT SELECT (customer_id, first_name, last_name)
ON customer TO report_user;
Task 4: Create support_user who can SELECT from customer, UPDATE only email column, Cannot DELETE
CREATE ROLE support_user WITH LOGIN PASSWORD 'support123';

GRANT SELECT ON customer TO support_user;

GRANT UPDATE (email) ON customer TO support_user;

We did not give DELETE permission, so they cannot delete.

Task 5: Remove SELECT access on film from report_user
REVOKE SELECT ON film FROM report_user;
Task 6: Create readonly_group that has SELECT on all tables
CREATE ROLE readonly_group;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;
Task 7: Create analyst1 and analyst2 and add them to readonly_group
CREATE ROLE analyst1 WITH LOGIN PASSWORD 'analyst123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'analyst123';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

DB-TASK-002

Mohammed Azim J — Wed, 25 Mar 2026 17:15:01 +0000

Get all movies that have rental rate greater than 3
SELECT * FROM film
WHERE rental_rate > 3;

I used this to filter movies which are costly rental movies.

Movies rental rate > 3 and replacement cost < 20
SELECT * FROM film
WHERE rental_rate > 3 AND replacement_cost < 20;

Here I used AND because both condition must be true.

Movies rated PG or rental rate 0.99
SELECT * FROM film
WHERE rating = 'PG' OR rental_rate = 0.99;

I used OR because any one condition is enough.

First 10 movies sorted by rental rate highest first
SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC
LIMIT 10;

I sorted high price first then limited to 10.

Skip first 5 movies and get next 3 sorted by rental rate ascending
SELECT title, rental_rate
FROM film
ORDER BY rental_rate ASC
OFFSET 5
LIMIT 3;

OFFSET used to skip rows.

(same question again so same query)
SELECT title, rental_rate
FROM film
ORDER BY rental_rate ASC
OFFSET 5
LIMIT 3;
Movies with rental duration between 3 and 7
SELECT title, rental_duration
FROM film
WHERE rental_duration BETWEEN 3 AND 7;

BETWEEN is easier than writing >= and <=.

Movies title starts with A and ends with e
SELECT title
FROM film
WHERE title LIKE 'A%e';

LIKE used for pattern search.

Customers who do not have email
SELECT first_name, last_name
FROM customer
WHERE email IS NULL;

IS NULL used because = NULL will not work.

Movies released in 2006, rental rate 2.99 or 3.99 and title starts with S, top 5
SELECT title, rental_rate, release_year
FROM film
WHERE release_year = 2006
AND rental_rate IN (2.99, 3.99)
AND title LIKE 'S%'
LIMIT 5;

IN used instead of multiple OR.

Display 10 customers after skipping first 20 sorted by last name
SELECT first_name, last_name
FROM customer
ORDER BY last_name
OFFSET 20
LIMIT 10;

Used for pagination type query.

Top 5 movies highest replacement cost skipping most expensive one
SELECT title, replacement_cost
FROM film
ORDER BY replacement_cost DESC
OFFSET 1
LIMIT 5;

Offset 1 skips most expensive movie.

Rentals between two dates
SELECT *
FROM rental
WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

Used BETWEEN for date range.

Actors last name contain "man"
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE '%man%';

%man% means contains man anywhere.

Movies where special features is NULL
SELECT title
FROM film
WHERE special_features IS NULL;
Movies where rental duration more than 7
SELECT title, rental_duration
FROM film
WHERE rental_duration > 7;
First 10 movies rental rate 2.99 or 4.99, rating R and title contains L
SELECT title, rental_rate, rating
FROM film
WHERE rental_rate IN (2.99, 4.99)
AND rating = 'R'
AND title LIKE '%L%'
LIMIT 10;
Movies title starts with A or B and ends with s
SELECT title
FROM film
WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
Movies title contains Man, Men or Woman
SELECT title
FROM film
WHERE title LIKE '%Man%'
OR title LIKE '%Men%'
OR title LIKE '%Woman%';