DEV Community: Jonah Blessy

Alter Tables in SQL

Jonah Blessy — Sun, 29 Mar 2026 16:03:04 +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.

To our existing customers table, we alter the table customers and alter the column email to set it to not null so that the future entries always have a value for the email and they are no longer null.

2. In the users table, ensure that the username column is unique across all records using an ALTER statement.
To our existing users table, we alter the table and add a unique constraint to the username column so that no two rows can have the same username.

3. In the products table, enforce that price must always be greater than 0 using an ALTER command.
Here we alter the products table and add a check constraint on the price column to make sure all future values are greater than 0.

4. Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.
We alter the orders table and set a default value for the status column so that if no value is given, it automatically becomes '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.
We alter the employees table by adding a new column salary and apply not null along with a check constraint to ensure the value is always greater than 10000.

6. Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.
Since the foreign key already exists, we alter the employees table by dropping the existing constraint and adding it again with on delete cascade so related employees are removed when a department is deleted.

7. In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.
We alter the accounts table and drop the existing check constraint on balance so that this restriction is no longer enforced.

8. In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.
We alter the payments table and add a composite unique constraint on user_id and transaction_id so that the same pair cannot appear more than once.

Create Tables in SQL

Jonah Blessy — Sun, 29 Mar 2026 15:26:25 +0000

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

So this qn asks us to create a table in sql by the name student and give attributes id, name and age. Since id has to be unique we can make it as a primary key so that each value in id is unique and not null.

2. Create a table employees where name and email cannot be empty, but phone_number can be optional.
Similarly we create a table named employees and give attributes name, email and phone_number. Since name and email cannot be empty we give not null to those attributes.

3. Create a table users where both username and email must be unique across all records.
After creating table users, for the attributes username and email we apply "unique" constraint so that all values are unique.

4. Create a table products where price must always be greater than 0 and stock cannot be negative.
For this we give constraints in price to check price>0 and then we 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.
For this we create a table orders with attributes status and created_at. Since status should automatically be 'pending' if no value is given, we use the default constraint. Similarly, to store the current time automatically, we use default current_timestamp for created_at.

6. Create a table accounts where
account_number must be unique and not null
balance must always be greater than or equal to 0
Here we create a table accounts with attributes account_number and balance. Since account_number should not be empty and must be unique, we apply both not null and unique constraints. For balance, we ensure it never goes below 0 by using a check constraint (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.
In this case we create a table enrollments with student_id and course_id. A student can take multiple courses, but the same student should not enroll in the same course more than once. So we apply a composite unique constraint.

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.
First we create a departments table with id and name. Then we create an employees table with id, name, and department_id. To make sure every department_id in employees actually exists in departments, we use a foreign key constraint referencing 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
Here we extend the foreign key constraint. Along with referencing departments(id), we add on delete cascade so that deleting a department automatically deletes related employees. We also add on update cascade so that any change in department id is reflected in the employees table.

Idempotency Situation

Jonah Blessy — Wed, 25 Mar 2026 20:19:17 +0000

SELECT * FROM accounts;

Start by checking current balances so there is a clear baseline before repeating any operations. This helps track how much the values change after duplicate executions.

BEGIN;

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

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

COMMIT;

Run a normal transfer once to simulate a valid request. This acts as the expected correct behavior for a single operation.

SELECT * FROM accounts;

Verify that balances updated correctly after the first transfer. This confirms the system is working as intended for a single execution.

BEGIN;

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

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

COMMIT;

Run the exact same transaction again to simulate a duplicate request. This mimics real-world retries caused by network issues or user actions.

SELECT * FROM accounts;

Observe that the balances change again, meaning the same transfer was applied twice. This shows the database does not automatically detect duplicates.

BEGIN;

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

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

COMMIT;

Repeat once more to further confirm behavior. Each execution keeps modifying balances, proving there is no built-in protection against repeated transactions.

SELECT * FROM accounts;

Balances continue to change with every execution. This clearly shows that duplicate operations are treated as separate valid transactions.

The database only ensures correctness of each individual transaction, not whether the same action was already performed earlier. Preventing duplicate processing must be handled at a higher level, such as using unique transaction IDs, idempotency keys, or logs to track processed requests. Real systems rely on these techniques to ensure that even if the same request is sent multiple times, it is applied only once.

Durability

Jonah Blessy — Wed, 25 Mar 2026 20:12:17 +0000

SELECT * FROM accounts;

Start by checking the current balances to know the initial state before performing any transfer. This gives a clear baseline to compare against after the transaction is completed. It helps confirm whether the changes actually persist later.

BEGIN;

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

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

COMMIT;

A proper transfer is done by grouping both debit and credit inside a single transaction. The commit ensures both operations are saved together as one unit. This avoids cases where money is deducted but not added.

SELECT * FROM accounts;

After committing, checking the balances confirms that the transfer has successfully taken place. Alice’s balance should decrease and Bob’s should increase. This verifies that the transaction was applied correctly.

Reconnect to the database after simulating a restart or crash.

SELECT * FROM accounts;

Querying again after reconnecting ensures the changes are still present. If the balances remain updated, it confirms that the data was permanently stored. This shows that committed transactions are not lost even after a restart.

Durability is ensured because once a transaction is committed, the database writes the changes to persistent storage. PostgreSQL uses mechanisms like Write-Ahead Logging (WAL) to make sure data can be recovered even if a crash happens. If a failure occurs before the commit, none of the changes are saved. If it happens after the commit, the system uses logs to restore the committed state, ensuring no data loss.

Consistency

Jonah Blessy — Wed, 25 Mar 2026 20:06:25 +0000

SELECT * FROM accounts;

Start by seeing current balances to know the baseline. This helps compare what changes after each operation.

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

This attempts to reduce balance below zero. It tests whether the database allows invalid balance updates.

SELECT * FROM accounts;

Check if the update actually went through. The expectation is that constraint should block it.

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

This bypasses calculation and forces an invalid state and helps confirm if schema-level checks are enforced.

The CHECK (balance >= 0) constraint prevents negative values. PostgreSQL throws an error before committing the change.

BEGIN;

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

COMMIT;

Even inside a transaction, constraint violation should stop execution. Transaction will not commit successfully.

SELECT * FROM accounts;

Balances should remain unchanged since invalid operation failed. Confirms no partial updates happened.

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

Adds logic to prevent invalid deduction before it happens. This is handled at query/application level.

Constraints act as a final safety net at database level. Application logic prevents bad operations earlier, reducing errors.

SELECT * FROM accounts;

Ensure all balances are valid and non-negative. Confirms system maintains consistency through both rules and checks.

Atomicity

Jonah Blessy — Wed, 25 Mar 2026 19:58:20 +0000

Design a transaction to transfer money from one account to another (atomic operation)

BEGIN;

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

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

COMMIT;

Both debit and credit are grouped inside one transaction so they act as a single unit. This ensures money is not lost between steps.

Check balances before and after successful transaction

SELECT * FROM accounts;

First check initial state to know starting balances. After commit, verify both accounts reflect the correct updated values.

Introduce failure after debit (simulate error before credit)

BEGIN;

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

-- simulate failure
SELECT 1/0;

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

COMMIT;

An error is triggered after deducting money. This helps check whether the system allows partial updates or not.

Observe rollback behavior after failure

SELECT * FROM accounts;

After failure, the transaction should not commit. The deducted amount should not persist if rollback works correctly.

Explicit rollback after error

BEGIN;

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

ROLLBACK;

Rollback cancels everything inside the transaction. Even completed queries inside the block are undone.

Simulate failure in credit step

BEGIN;

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

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

COMMIT;

Second query fails logically. This checks whether system prevents inconsistent state.

Verify no partial update after failed credit

SELECT * FROM accounts;

Balances should remain unchanged if transaction fails. Sender’s balance should not be reduced alone.

Safe version with validation

BEGIN;

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

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

COMMIT;

Added condition to avoid negative balance. Helps ensure business rule is enforced before transfer completes.

Final verification of consistency

SELECT * FROM accounts;

Final check confirms either both updates happened or none. Ensures system maintains atomicity and consistency.

Users, Roles, Groups

Jonah Blessy — Wed, 25 Mar 2026 19:50:06 +0000

Qn 1: Create a login role report_user that can only read from the film table

CREATE ROLE report_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON film TO report_user;

we need a user who can log in but shouldn’t be able to modify anything. so first create the role with login, then just give select permission on film so it can only view data.

Qn 2: Fix permission denied for customer table

GRANT SELECT ON customer TO report_user;

this error just means the user doesn’t have access. so instead of changing anything else, just give select permission on customer.

Qn 3: Allow only specific columns from customer table

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

i removed the full access first then allowed access only to required columns instead of the whole table.

Qn 4: Create support_user with limited permissions

CREATE ROLE support_user WITH LOGIN PASSWORD 'password123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;
REVOKE DELETE ON customer FROM support_user;

Support role should view data and update emails. So i explicitly removed delete to avoid mistakes.

Qn 5: Remove SELECT access on film from report_user

REVOKE SELECT ON film FROM report_user;

As per change in requirement, i just revoked the access from the report_user.

Qn 6: Create readonly_group with SELECT on all tables

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

Instead of assigning permissions one by one we can create a group so that it is easier to manage multiple users later.

Qn 7: Create analyst 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;

We create users first then attach them to the group. This way they inherit all read-only permissions automatically.

Select Queries from DVD Rental database

Jonah Blessy — Wed, 25 Mar 2026 19:38:38 +0000

Qn 1: Retrieve film titles and their rental rates. Use column aliases to rename title as "Movie Title" and rental_rate as "Rate".

SELECT title AS "Movie Title", rental_rate AS "Rate" FROM film;

Thought: Just selecting needed columns and renaming them using aliases for clarity. No filtering or sorting needed.

Qn 2: List customer names and their email addresses. Alias first_name and last_name.

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

Thought: Pick required columns and use aliases for readability. Email doesn’t need alias since name is clear.

Qn 3: Get films sorted by rental rate descending, then by title ascending.

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

Thought: First priority sorting is rental rate, then resolve ties using title alphabetically.

Qn 4: Retrieve actor names sorted by last name, then first name.

SELECT first_name, last_name FROM actor ORDER BY last_name, first_name;

Thought: Sort hierarchy matters—last name first, then first name for tie-breaking.

Qn 5: List all unique replacement costs.

SELECT DISTINCT replacement_cost FROM film;

Thought: Use DISTINCT to remove duplicates and show only unique values.

Qn 6: List film title and length with alias.

SELECT title, length AS "Duration (min)" FROM film;

Thought: Just renaming a column for better readability, no filtering involved.

Qn 7: Retrieve customer names with active status.

SELECT first_name, last_name, active AS "Is Active" FROM customer;

Thought: Straight selection with alias to make boolean column more understandable.

Qn 8: Retrieve film categories sorted alphabetically.

SELECT name FROM category ORDER BY name;

Thought: Only one column needed, sorted alphabetically using ORDER BY.

Qn 9: List films by length descending.

SELECT title, length FROM film ORDER BY length DESC;

Thought: Sort based on numeric column length in descending order.

Qn 10: Retrieve actor names sorted by first name descending.

SELECT first_name FROM actor ORDER BY first_name DESC;

Thought: Only one column, so directly apply descending sort.

Qn 11: List all unique ratings.

SELECT DISTINCT rating FROM film;

Thought: Ratings may repeat, so DISTINCT ensures uniqueness.

Qn 12: Find all unique rental durations.

SELECT DISTINCT rental_duration FROM film;

Thought: Same idea as above—remove duplicates using DISTINCT.

Qn 13: Retrieve first unique customer_id based on active status.

SELECT customer_id, active FROM customer ORDER BY customer_id;

Thought: Order by customer_id ensures first record appears at top; uniqueness assumed by PK.

Qn 14: List earliest rental date for each customer.

SELECT customer_id, MIN(rental_date) FROM rental GROUP BY customer_id ORDER BY customer_id;

Thought: Use aggregation MIN to get earliest date, grouped per customer.

Qn 15: List 10 shortest films by length.

SELECT title, length FROM film ORDER BY length ASC LIMIT 10;

Thought: Smallest values come first in ascending order, then limit results.

Qn 16: Get top 5 customers with highest customer_id.

SELECT first_name, last_name FROM customer ORDER BY customer_id DESC LIMIT 5;

Thought: Highest IDs come first using DESC, then limit to top 5.

Qn 17: Retrieve unique store_ids from inventory.

SELECT DISTINCT store_id FROM inventory;

Thought: Use DISTINCT since multiple entries may belong to same store.

Qn 18: Unique replacement_cost sorted ascending.

SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;

Thought: Combine DISTINCT and ORDER BY to get sorted unique values.

Qn 19: First rental date for each store.

SELECT store_id, MIN(rental_date) FROM rental GROUP BY store_id ORDER BY store_id;

Thought: Group by store and use MIN to get earliest rental.

Qn 20: Unique film ratings sorted alphabetically.

SELECT DISTINCT rating FROM film ORDER BY rating;

Thought: DISTINCT removes duplicates, ORDER BY arranges alphabetically.

Qn 21: List films by rating ascending and length descending.

SELECT rating, length FROM film ORDER BY rating ASC, length DESC;

Thought: Apply multiple sorting—first by rating, then length for tie-breaking.

Qn 22: Retrieve actor names sorted by last_name ASC and first_name DESC.

SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;

Thought: Mixed sorting—primary ascending, secondary descending.

Qn 23: List films ordered by replacement_cost ASC and rental_rate DESC.

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

Thought: First sort cheaper films, then within same cost sort by higher rate.

Qn 24: Retrieve customer names sorted by last_name ASC and first_name DESC.

SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;

Thought: Same multi-level sorting logic applied on names.

Qn 25: List rentals sorted by customer_id ASC and rental_date DESC.

SELECT * FROM rental ORDER BY customer_id ASC, rental_date DESC;

Thought: Group by customer order, then show latest rentals first.

Qn 26: Retrieve films ordered by rental_duration ASC and title DESC.

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

Thought: Shorter durations first, and for same duration sort titles in reverse order.

Filter Assignments

Jonah Blessy — Wed, 25 Mar 2026 19:13:34 +0000

Qn 1: Get all movies (films) that have a rental rate greater than $3.
here we just need to filter out rows where rental_rate is more than 3. nothing else is needed since the question is very direct, so a simple where condition works.

SELECT * FROM film WHERE rental_rate > 3;

Qn 2: Get all movies that have a rental rate greater than $3 and a replacement cost less than $20.
this is similar to the previous one but now there are two conditions. so we use and because both conditions should be true for a row to be selected.

SELECT * FROM film WHERE rental_rate > 3 AND replacement_cost < 20;

Qn 3: Get all movies that are either rated as 'PG' or have a rental rate of $0.99.
here it is not mandatory for both conditions to be true, even one is enough. so we use or to check for either rating or rental rate.

SELECT * FROM film WHERE rating = 'PG' OR rental_rate = 0.99;

Qn 4: Show the first 10 movies sorted by rental rate (highest first).
Using order by clause I learnt last class and using desc to show the highest value first.

SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;

Qn 5: Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
we first sort in ascending order since that is mentioned. then we skip the first 5 rows using offset and take the next 3 using limit.

SELECT * FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3;

Qn 6: Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
same logic as the previous qn, just applying offset and limit after sorting.

SELECT * FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3;

Qn 7: Get all movies with a rental duration between 3 and 7 days.
instead of writing two conditions like greater than and less than, we can directly use between which makes it cleaner and easier to read.

SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;

Qn 8: Get all movies where the title starts with 'A' and ends with 'e'.
this is pattern matching. a% means starts with a and %e means ends with e. combining both gives us titles that satisfy both conditions.

SELECT * FROM film WHERE title LIKE 'A%e';

Qn 9: Find all customers who do not have an email address listed.
if something is not listed in sql, it usually means the value is null. so we check for null using is null.

SELECT * FROM customer WHERE email IS NULL;

sql

Qn 10: Find all movies released in 2006 with a rental rate of $2.99 or $3.99, and their title starts with 'S'. Display the top 5 results.
this was fun to solve. mixing the smaller queries i already tried to come up with this.

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;

Qn 11: Display 10 customers after skipping the first 20, sorted alphabetically by last name.
as seen earlier we use the offset clause to skip the first 20 values.

SELECT * FROM customer ORDER BY last_name ASC OFFSET 20 LIMIT 10;

Qn 12: Get the top 5 movies with the highest replacement cost, skipping the most expensive one.
highest cost means we need the list in reverse or descending order. for skipping most expensive use offset.

SELECT * FROM film ORDER BY replacement_cost DESC OFFSET 1 LIMIT 5;

Qn 13: Find all rentals that occurred between '2005-05-01' and '2005-06-01'.
again using between makes it easy to filter dates within a range without writing multiple conditions.

SELECT * FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

Qn 14: Get all actors whose last names contain the letters "man".
we want to match anywhere in the string, so we use % on both sides with like "man"

SELECT * FROM actor WHERE last_name LIKE '%man%';

Qn 15: Find all movies where the special features are not listed (i.e., special_features is NULL).
get the rows where value is null for special_features because not listed means null, so we check using is null.

SELECT * FROM film WHERE special_features IS NULL;

Qn 16: Find all movies where the rental duration is more than 7 days.
Filter where rental duration is greater than 7.

SELECT * FROM film WHERE rental_duration > 7;

Qn 17: Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "L".
Combine multiple filters and search for a letter inside title.

SELECT * FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%L%' LIMIT 10;

Qn 18: Find all movies where the title starts with "A" or "B" and ends with "s".
we need two starting conditions, so we use or. each condition uses like to match start and end.

SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');

Qn 19: Find all movies where the title contains "Man", "Men", or "Woman".
Check if title contains any of the given words using OR.

SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';

Qn 20: Find all movies where the special features are not listed (i.e., special_features is NULL).
same as earlier, just checking for null again.

SELECT * FROM film WHERE special_features IS NULL;

Qn 21: Find all movies where the rental duration is more than 7 days.
we just give a condition where

SELECT * FROM film WHERE rental_duration > 7;

Qn 22: Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.
Apply AND condition for both rental rate and replacement cost.

SELECT * FROM film WHERE rental_rate = 4.99 AND replacement_cost > 20;

Qn 23: Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.
Use OR since either rental rate or rating condition works.

SELECT * FROM film WHERE rental_rate = 0.99 OR rating = 'PG-13';

Qn 24: Retrieve the first 5 rows of movies sorted alphabetically by title.
Sort alphabetically and take first 5 rows.

SELECT * FROM film ORDER BY title ASC LIMIT 5;

Qn 25: Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.
Sort by replacement cost descending, skip 10, take next 3.

SELECT * FROM film ORDER BY replacement_cost DESC OFFSET 10 LIMIT 3;

Qn 26: Find all movies where the rating is either 'G', 'PG', or 'PG-13'.

SELECT * FROM film WHERE rating IN ('G', 'PG', 'PG-13');

Qn 27: Find all movies with a rental rate between $2 and $4.

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;

Qn 28: Find all movies with titles that start with 'The'.
Use LIKE to match titles starting with “The”.

SELECT * FROM film WHERE title LIKE 'The%';

Qn 29: Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "Love".
Combine filters and search for specific word “Love” in title.

SELECT * FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%Love%' LIMIT 10;

Qn 30: Find all movies where the title contains the % symbol.
Escape special character % and search in title.

SELECT * FROM film WHERE title LIKE '%\%%' ESCAPE '\';

Qn 31: Find all movies where the title contains an underscore (_).

SELECT * FROM film WHERE title LIKE '%\_%' ESCAPE '\';

Qn 32: Find all movies where the title starts with "A" or "B" and ends with "s".

SELECT * FROM film WHERE (title LIKE 'A%s' OR title LIKE 'B%s');

Qn 33: Find all movies where the title contains "Man", "Men", or "Woman".
Use OR with LIKE to match multiple words.

SELECT * FROM film WHERE title LIKE '%Man%' OR title LIKE '%Men%' OR title LIKE '%Woman%';

Qn 34: Find all movies with titles that contain digits (e.g., "007", "2", "300").
Use regex to check if title contains digits.

SELECT * FROM film WHERE title ~ '[0-9]';

Qn 35: Find all movies with titles containing a backslash ().

SELECT * FROM film WHERE title LIKE '%\\%';

Qn 36: Find all movies where the title does contain the words "Love" or "Hate".
Use OR to check if title contains either word.

SELECT * FROM film WHERE title LIKE '%Love%' OR title LIKE '%Hate%';

Qn 37: Find the first 5 movies with titles that end with "er", "or", or "ar".
Match titles ending with specific suffixes and limit results.

SELECT * FROM film WHERE title LIKE '%er' OR title LIKE '%or' OR title LIKE '%ar' LIMIT 5;

Setup a DNS hosted zone in Route53 in AWS.

Jonah Blessy — Wed, 25 Mar 2026 18:36:19 +0000

To connect your domain to your server using Route 53, we first go to the AWS Console and open the Route 53 service. Here, we create something called a hosted zone, which basically manages all the DNS records for our domain. While creating it, we enter the domain name (I have used jonahblessy.com) and select the type as a public hosted zone since we want it to be accessible over the internet.

Once the hosted zone is created, AWS automatically adds a few default records like NS (Name Server) and SOA (Start of Authority). These are important for DNS to work properly, so we usually don’t touch them.

Next, we need to add our own DNS record to point the domain to our EC2 server. Inside the hosted zone, we create a new record. We choose type A which maps a domain to an IPv4 address, give it a name like www, and set the value as the public IP address of our EC2 instance.

After saving this, we don’t see the change instantly because DNS needs time to update everywhere. This is called propagation, and it usually takes a few minutes, but in some cases it can take up to 48 hours.

Once propagation is done, whenever someone enters the domain in the browser, Route 53 resolves it to the EC2 IP address, and the request reaches our server.

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

Jonah Blessy — Wed, 25 Mar 2026 18:24:27 +0000

To create a simple EC2 instance and host a basic web server, launch a virtual machine on AWS, install a web server on it, and then access it from your browser using its public address.

First, go to the AWS Console and navigate to the EC2 service. From there, click on "Launch Instance". Choose an instance type something simple like t2.micro since it’s lightweight and enough for basic testing. While setting it up, you also need to either create a new key pair or select an existing one. This key pair is important because it’s what you’ll use later to securely connect to your instance.

Next comes the network configuration. Here, we define who can access our instance. For SSH, we usually restrict access to just our own IP so that only we can log in. For HTTP, since we want to access the web server from anywhere, we allow traffic from all sources (0.0.0.0/0). Once all this is configured, we go ahead and launch the instance.

After the instance is up and running, the next step is to connect to it. You can do this using SSH. Once inside the instance, we install a web server. In this case, we use Apache. So we first update the system packages using sudo yum update -y, and then install Apache using sudo yum install httpd -y.

After installation, we start the web server so that it begins listening for incoming requests. We can also add a simple HTML page to verify that everything is working properly. This is usually done by editing files inside /var/www/html.

Finally, to access the web server from outside, we copy the public DNS of the EC2 instance and paste it into a browser. If everything is set up correctly, the webpage we created should load, confirming that our EC2 instance is successfully running a web server and is accessible over the internet.

DNS resolver

Jonah Blessy — Wed, 25 Mar 2026 18:05:56 +0000

When a user types a domain name like www.wikipedia.org in the browser, the system needs to convert that human friendly name into an IP address so it can actually locate the server. This whole process is called DNS resolution. Instead of directly knowing where the server is, the browser has to go through a few steps to figure it out.

The first thing the system does is check locally to save time. It looks into the browser cache and the OS cache to see if the IP address for that domain was already stored from a previous request. If it finds the IP here, it can skip the entire DNS process and directly connect to the server.

If the IP is not found locally, then a DNS query is started. This request first goes to the local resolver, which is responsible for handling DNS queries from the system. If the local resolver doesn’t have the answer, it forwards the request to the ISP’s DNS resolver.

The ISP resolver also checks its own cache. If it still doesn’t have the IP, it starts the actual lookup process. It first contacts the root name server. The root server doesn’t give the exact IP address, but instead tells the resolver which TLD (Top-Level Domain) server to contact based on the domain extension like .com, .org, or .in.

For example, in wikipedia.org, the request is directed to the .org TLD server. This TLD server then points the resolver to the authoritative DNS server, which is where the actual records for the domain are stored.

The authoritative server contains the zone file, which has all the important DNS records like A (IPv4), AAAA (IPv6), CNAME (aliases), and MX (mail-related info). From here, the correct IP address for the domain is obtained.

Once the IP is found, it is sent back through the same path from the authoritative server to the TLD server, then to the ISP resolver, then to the local resolver, and finally back to the browser. After receiving the IP address, the browser can now connect to the actual server using protocols like TCP and send the HTTP request.

So, DNS resolution is what makes it possible for us to use simple domain names instead of remembering complex IP addresses, while ensuring that the request reaches the correct server.