DEV Community: Jarvish John

Alter Tables

Jarvish John — Sun, 29 Mar 2026 16:52:25 +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.

The email column in the customers table currently allows NULL values. To ensure every new record has an email, we modify the column and set it to NOT NULL. This prevents future entries from leaving it empty.

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

To avoid duplicate usernames, we update the users table by adding a UNIQUE constraint on the username column. This guarantees that each username is distinct across all records.

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

We modify the products table by adding a CHECK constraint on the price column. This ensures that every product must have a price greater than 0.

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

In the orders table, we set a default value for the status column as 'pending'. This means if no status is provided during insertion, it will automatically be assigned '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 add a new column salary to the employees table. This column is set to not null and also includes a check constraint to ensure the salary is always greater than 10,000.

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

We modify the foreign key relationship between employees and departments. By adding on delete cascade, when a department is deleted, all employees linked to that department are automatically removed as well.

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

We remove an existing CHECK constraint on the balance column in the accounts table. This means the condition (like balance ≥ 0) 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.
In the payments table, we add a composite UNIQUE constraint on (user_id, transaction_id). This ensures that the same combination cannot appear more than once.

Create Tables

Jarvish John — Sun, 29 Mar 2026 15:34:36 +0000

1. Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student.
We create a students table with id, name, and age. Since id should uniquely identify each student and cannot be null, we make it the primary key.

create table students (id serial primary key, name varchar(100), age int);

2. Create a table employees where name and email cannot be empty, but phone_number can be optional.
We create an employees table with name, email, and phone_number. Since name and email are mandatory, we apply not null, while phone_number is left optional.

create table employees (id serial primary key, name varchar(100) not null, email varchar(100) not null, phone_number varchar(20));

3. Create a table users where both username and email must be unique across all records.
We create a users table and ensure that username and email are unique by adding unique constraints so no duplicates are allowed.

create table users (id serial primary key, username varchar(100) unique, email varchar(100) unique);

4. Create a table products where price must always be greater than 0 and stock cannot be negative.
We create a products table and use check constraints to ensure price is greater than 0 and stock is zero or more.

create table products (id serial primary key, price numeric 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.
We create an orders table where status gets a default value of 'pending' if not given, and created_at automatically stores the current timestamp.

create table orders (id serial primary key, status varchar(50) default 'pending', created_at timestamp default current_timestamp);

6. Create a table accounts where account_number must be unique and not null and balance must always be greater than or equal to 0
We create an accounts table where account_number is required and unique, and balance is restricted using a check so it never goes below zero.

create table accounts (id serial primary key, account_number varchar(100) unique not null, balance numeric 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.
We create an enrollments table and apply a composite unique constraint on student_id and course_id so the same pair cannot repeat.

create table enrollments (student_id int, course_id int, unique (student_id, course_id));

8. Create two tables: departments and employees, ensuring department_id in employees must exist in departments.
We create departments first, then employees, and use a foreign key so department_id in employees must match an existing id in departments.

create table departments (id serial primary key, name varchar(100)); create table employees (id serial primary key, name varchar(100), department_id int references departments(id));

9. Modify the previous foreign key example so that when a department is deleted, all related employees are also deleted and when a department ID is updated, it reflects in the employees table
We extend the foreign key by adding cascade rules so deletes and updates in departments automatically reflect in employees.

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 on update cascade;

Idempotency Situation

Jarvish John — Wed, 25 Mar 2026 20:23:58 +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

Jarvish John — Wed, 25 Mar 2026 20:14:44 +0000

SELECT * FROM accounts;

Begin by looking at the current balances so there is a clear idea of the starting point. This makes it easier to confirm later whether the transfer actually went through and stayed saved.

BEGIN;

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

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

COMMIT;

Both updates are wrapped inside a transaction so they execute together. The commit finalizes the change, making it permanent instead of temporary.

SELECT * FROM accounts;

Immediately checking after commit helps confirm that the transfer worked as expected. The balances should reflect the updated values without any mismatch.

Reconnect to the database session after simulating failure.

SELECT * FROM accounts;

Running the same query again verifies whether the data still exists after reconnecting. If the balances remain updated, it proves the changes were stored safely.

Durability ensures that once a transaction is committed, its changes are permanently stored on disk and not just in memory. PostgreSQL uses Write-Ahead Logging (WAL) to record changes before they are applied, allowing recovery even after a crash. If a failure occurs before the commit, none of the changes are saved, but if it happens after the commit, the system uses these logs to restore the committed state, ensuring no data is lost.

Consistency

Jarvish John — Wed, 25 Mar 2026 20:10:37 +0000

SELECT * FROM accounts;

Start by checking the current balances to understand the initial state of the system. This acts as a reference point before performing any operations. Without this, it becomes difficult to verify whether later changes are correct or not. It also helps in spotting unintended modifications after running test queries.

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

This operation intentionally tries to deduct more money than Alice has. The goal here is to push the system into an invalid state and see how it reacts. Since the balance would go below zero, it violates the rule defined in the table. This helps confirm whether the database itself enforces constraints or blindly accepts updates.

SELECT * FROM accounts;

After attempting the invalid update, checking the table again helps verify what actually happened. If the constraint is working correctly, the update should not have been applied at all. This step confirms whether the system prevents invalid data or allows partial corruption.

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

This directly assigns a negative value instead of calculating it. It skips any logical checks and forces a clearly invalid state. The idea is to test whether the database constraint alone is strong enough to block such direct manipulation. It ensures that even careless or malicious updates cannot break the data integrity.

The CHECK (balance >= 0) constraint prevents negative values. PostgreSQL immediately throws an error when this rule is violated. The update never gets committed, which keeps the data safe.

BEGIN;

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

COMMIT;

Here the same invalid operation is wrapped inside a transaction block. This tests whether constraints still apply within transactions. Even though transactions group multiple steps, the database should still reject invalid operations. If any statement fails, the entire transaction should not be committed.

SELECT * FROM accounts;

After the failed transaction, checking the table confirms that no changes were applied. Alice’s balance should remain the same as before. This proves that the system avoids partial updates and maintains consistency even when errors occur mid-process.

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

This introduces a safer way to handle updates by adding a condition. Instead of relying only on database constraints, the query itself ensures that the operation is valid before executing. This reduces the chances of hitting errors and improves efficiency. It shows how application-level logic can prevent bad queries from even reaching the constraint stage.

Constraints act as a final safety net at the database level. They guarantee that invalid data never gets stored. On the other hand, conditions in queries or application logic help avoid triggering those errors in the first place, making the system smoother.

SELECT * FROM accounts;

Finally, checking the table again ensures everything is still consistent. All balances should be valid and non-negative. This confirms that both database constraints and careful query design are working together to maintain data integrity.

Atomicity

Jarvish John — Wed, 25 Mar 2026 20:01:10 +0000

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

BEGIN;

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

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

COMMIT;

Both updates are wrapped inside one block so they either succeed together or not at all. This avoids any mismatch between sender and receiver balances.

Check balances before and after successful transaction

SELECT * FROM accounts;

Look at the balances before running the transfer to know the starting point. After commit, confirm both accounts changed correctly.

Introduce failure after debit (simulate error before credit)

BEGIN;

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

-- forced error
SELECT 10/0;

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

COMMIT;

An intentional error is placed after deducting money. This is to see if the system allows the first update to stay or cancels everything.

Observe rollback behavior after failure

SELECT * FROM accounts;

After the error, check balances again. If rollback works, Alice’s balance should remain unchanged.

Explicit rollback after partial step

BEGIN;

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

ROLLBACK;

Rollback is used to undo any changes made in the transaction. Even though one update ran, it should not persist.

Simulate failure in credit step

BEGIN;

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

UPDATE accounts 
SET balance = balance + 150 
WHERE name = 'Jarvish John';

COMMIT;

The second update targets a non-existing user. This checks if the system prevents incomplete transfers.

Verify no partial update after failed credit

SELECT * FROM accounts;

Balances should be exactly as before the transaction. Sender’s money should not be reduced alone.

Safer version with condition check

BEGIN;

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

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

COMMIT;

Added a condition to ensure sufficient balance before deducting. Helps avoid invalid transactions.

Final consistency check

SELECT * FROM accounts;

Final state should confirm atomic behavior. Either both balances changed or nothing changed at all.

Users, Roles, Groups

Jarvish John — Wed, 25 Mar 2026 19:51:57 +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;

A separate role was needed just for viewing data, so login access was created with minimal privileges. Only read permission on the film table was provided to keep access tight.

Qn 2: Fix permission denied for customer table

GRANT SELECT ON customer TO report_user;

The error indicates missing access, so read permission was added. No other changes required since only viewing was needed.

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;

General access was too broad, so it was removed first. Then access was restricted to only the required columns.

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;

This role is meant for handling customer info, so it can view data and update emails. Delete permission was removed to avoid accidental data loss.

Qn 5: Remove SELECT access on film from report_user

REVOKE SELECT ON film FROM report_user;

Since access is no longer required, the permission was withdrawn. Revoking ensures the role cannot read from that table anymore.

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 repeatedly, a group role was created. This makes it easier to manage read-only access across multiple users.

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;

Users were created individually and then linked to the group. This way, permissions are inherited without assigning them one by one.

Select Queries from DVD Rental database

Jarvish John — Wed, 25 Mar 2026 19:46:26 +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;

Picked only required columns and used aliases to make output more readable. No ordering or filtering needed since question is direct.

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;

Selected identity-related fields and renamed them for clarity. Email already meaningful so kept as is.

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;

Sorting priority is rental rate, so applied DESC first. Used title as secondary sort to handle equal values.

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;

Chose ordering based on common naming convention. Ensured consistency by applying secondary sort.

Qn 5: List all unique replacement costs.

SELECT DISTINCT replacement_cost FROM film;

Used DISTINCT to avoid repeated values. Only one column needed so query kept minimal.

Qn 6: List film title and length with alias.

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

Renamed length to give context of unit. No transformation required, just presentation change.

Qn 7: Retrieve customer names with active status.

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

Included status column and renamed it for better readability. Combined with names for meaningful output.

Qn 8: Retrieve film categories sorted alphabetically.

SELECT name FROM category ORDER BY name;

Single column retrieval with alphabetical ordering. Default ASC works without explicitly mentioning.

Qn 9: List films by length descending.

SELECT title, length FROM film ORDER BY length DESC;

Wanted longest films first, so used DESC. Selected only relevant columns for simplicity.

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

SELECT first_name FROM actor ORDER BY first_name DESC;

Focused only on first names and reversed alphabetical order. No need for additional columns.

Qn 11: List all unique ratings.

SELECT DISTINCT rating FROM film;

Ensured no duplicate ratings appear. Keeps result clean and compact.

Qn 12: Find all unique rental durations.

SELECT DISTINCT rental_duration FROM film;

Same approach as ratings, applied DISTINCT. Focused only on unique duration values.

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

SELECT customer_id, active FROM customer ORDER BY customer_id;

Ordered by ID so smallest appears first. Included active column to match requirement.

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;

Grouped records by customer to isolate each one. Used MIN to extract earliest date.

Qn 15: List 10 shortest films by length.

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

Ascending order brings smallest lengths first. Limited results to only top 10.

Qn 16: Get top 5 customers with highest customer_id.

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

Descending order ensures highest IDs first. LIMIT restricts output to required count.

Qn 17: Retrieve unique store_ids from inventory.

SELECT DISTINCT store_id FROM inventory;

Removed duplicates using DISTINCT. Only one column needed so query stays simple.

Qn 18: Unique replacement_cost sorted ascending.

SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;

Combined uniqueness with ordering. ASC gives lowest values first.

Qn 19: First rental date for each store.

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

Grouped by store to separate records. Used MIN to get earliest date per group.

Qn 20: Unique film ratings sorted alphabetically.

SELECT DISTINCT rating FROM film ORDER BY rating;

Ensured uniqueness first, then sorted. Alphabetical order improves readability.

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

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

Applied primary sort on rating. Used length as secondary descending sort.

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;

Sorted last names normally and reversed first names. Mixed ordering applied carefully.

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;

Sorted cheaper films first. Within same cost, prioritized higher rental rates.

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;

Used last name as primary sorting key. Applied reverse order for first 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;

Grouped by customer order. Latest rentals appear first within each group.

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;

Shorter durations prioritized first. Titles reversed to handle equal durations.

Filter Assignments

Jarvish John — Wed, 25 Mar 2026 19:25:56 +0000

Qn 1 Get all movies (films) that have a rental rate greater than $3.
I identified that I need all columns and a simple condition on rental_rate, so I used SELECT * with a WHERE filter.

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.
There are two conditions, so I combined them using AND in the WHERE clause.

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.
Since either condition can be true, I used OR between rating and rental_rate conditions.

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

Qn 4 Show the first 10 movies sorted by rental rate (highest first).
I sorted using ORDER BY rental_rate DESC and limited results using LIMIT 10.

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.
I used ORDER BY for sorting and OFFSET with LIMIT to skip and fetch rows.

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

Qn 6 Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.
This is identical to the previous question, so I used the same LIMIT and OFFSET logic.

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

Qn 7 Get all movies with a rental duration between 3 and 7 days.
Since it’s a range condition, I used BETWEEN for cleaner syntax.

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'.
I used LIKE with pattern matching for start and end conditions combined with AND.

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

Qn 9 Find all customers who do not have an email address listed.
Missing values are represented by NULL, so I used IS NULL to filter.

SELECT * FROM customer WHERE email IS NULL;

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.
I combined multiple conditions using AND and OR, then limited results using LIMIT.

SELECT title, rental_rate, release_year FROM film WHERE release_year = 2006 AND (rental_rate = 2.99 OR rental_rate = 3.99) AND title LIKE 'S%' LIMIT 5;

Qn 11 Display 10 customers after skipping the first 20, sorted alphabetically by last name.
I sorted using ORDER BY and used OFFSET to skip rows before applying LIMIT.

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

Qn 12 Get the top 5 movies with the highest replacement cost, skipping the most expensive one.
I sorted in descending order and skipped the first row using OFFSET.

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

Qn 13 Find all rentals that occurred between '2005-05-01' and '2005-06-01'.
Since it’s a date range, I used BETWEEN on the rental_date column.

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".
I used LIKE with wildcards on both sides to find substring matches.

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).
I checked for NULL values using IS NULL since equality won’t work with NULL.

SELECT * FROM film WHERE special_features IS NULL;

Qn 16 Find all movies where the rental duration is more than 7 days.
This is a simple comparison condition using greater than operator.

 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".
I combined multiple filters and used LIKE for substring matching, then limited results.

SELECT * FROM film WHERE (rental_rate = 2.99 OR rental_rate = 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".
I used LIKE patterns for both conditions and combined them using OR.

 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".
I used multiple LIKE conditions combined with OR to match any of the words.

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

Bonus Q/A

Qn 1 Find all movies where the special features are not listed (i.e., special_features is NULL).
I checked for missing values using IS NULL.

SELECT * FROM film WHERE special_features IS NULL;

Qn 2 Find all movies where the rental duration is more than 7 days.
I used a simple greater-than condition on rental_duration.

 SELECT * FROM film WHERE rental_duration > 7;

Qn 3 Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.
I combined both conditions using AND.

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

Qn 4 Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.
Since either condition can satisfy the query, I used OR.

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

Qn 5 Retrieve the first 5 rows of movies sorted alphabetically by title.
I sorted using ORDER BY title and limited the output using LIMIT.

SELECT * FROM film ORDER BY title ASC LIMIT 5;

Qn 6 Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.
I sorted descending and used OFFSET to skip rows before LIMIT.

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

Qn 7 Find all movies where the rating is either 'G', 'PG', or 'PG-13'.
I used OR conditions to match multiple possible values.

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

Qn 8 Find all movies with a rental rate between $2 and $4.
I used BETWEEN to simplify the range condition.

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;

Qn 9 Find all movies with titles that start with 'The'.
I used LIKE with a prefix pattern.

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

Qn 10 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".
I combined all conditions and used LIKE for substring matching, then applied LIMIT.

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

Qn 11 Find all movies where the title contains the % symbol.
Since % is a wildcard, I escaped it to treat it as a literal character.

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

Qn 12 Find all movies where the title contains an underscore (_).
I escaped underscore since it’s a wildcard for single character.

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

Qn 13 Find all movies where the title starts with "A" or "B" and ends with "s".
I used LIKE patterns and combined both cases with OR.

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

Qn 14 Find all movies where the title contains "Man", "Men", or "Woman".
I used multiple LIKE conditions joined with OR.

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

Qn 15 Find all movies with titles that contain digits (e.g., "007", "2", "300").
I used pattern matching with numeric ranges.

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

Qn 16 Find all movies with titles containing a backslash ().
I escaped the backslash character in the LIKE pattern.

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

Qn 17 Find all movies where the title does contain the words "Love" or "Hate".
I used LIKE with OR to match either word.

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

Qn 18 Find the first 5 movies with titles that end with "er", "or", or "ar".
I used LIKE with multiple suffix patterns and limited the results.

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

Basic Select SQL Queries

Jarvish John — Wed, 25 Mar 2026 18:56:35 +0000

Qn 1. Query all columns for a city in CITY with the ID 1661.

Since the question asks for all columns for a specific city with ID 1661, I simply use SELECT * and filter the row using WHERE id = 1661.

SELECT * FROM city WHERE id=1661;

Qn 2. Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

Here I need all columns but only for cities in the USA with population greater than 100000, so I use SELECT * and apply both conditions together using AND.

SELECT * 
FROM city 
WHERE countrycode = 'USA' AND population > 100000;

The question asks for all attributes of Japanese cities, so I select all columns and filter rows where the country code is JPN.

Qn 3. Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

The question asks for all attributes of Japanese cities, so I select all columns and filter rows where the country code is JPN.

SELECT * 
FROM city 
WHERE countrycode = 'JPN';

Qn 4. Find the difference between the total number of CITY entries and the number of distinct CITY entries.

I need the difference between total and unique city entries, so I count all city values, count distinct ones, and subtract the two.

SELECT COUNT(city) - COUNT(DISTINCT city) 
FROM station;

Qn 5. Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

Only the name column is required for American cities with population above 120000, so I select name and apply both conditions in the WHERE clause.

SELECT name 
FROM city 
WHERE countrycode = 'USA' AND population > 120000;

Qn 6. Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.

Since only the names of Japanese cities are needed, I select the name column and filter rows where the country code is JPN.

SELECT name 
FROM city 
WHERE countrycode = 'JPN';

Qn 7. Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

I need unique city names that do not start with vowels, so I use DISTINCT and exclude each vowel condition using multiple NOT LIKE clauses combined with AND.

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

Qn 8. Query all columns (attributes) for every row in the CITY table.

The question asks for all columns for every row, so I simply select everything from the city table without any conditions.

SELECT * FROM city;

Qn 9. Query a list of CITY and STATE from the STATION table.

Only the city and state columns are required with no filtering, so I directly select those two columns from the station table.

SELECT city, state 
FROM station;

Setup a DNS hosted zone in Route53 in AWS.

Jarvish John — Wed, 25 Mar 2026 18:45:14 +0000

Setup a DNS Hosted Zone in Route 53

First, go to the AWS Console and open Route 53. Once inside, navigate to Hosted zones. This is where all your domain DNS configurations will be managed.

Next, create a hosted zone. Click on Create hosted zone, then enter your domain name (for example, jarvishj.org). Choose Public hosted zone since you want your domain to be accessible over the internet. After filling in the details, click Create hosted zone.

Once the hosted zone is created, AWS automatically adds two important records: NS (Name Server) records and an SOA (Start of Authority) record.

Add DNS records inside your hosted zone. A record is used to map your domain to an IPv4 address. A CNAME record is used to map one domain to another. An AAAA record is used for IPv6 addresses.

Once everything is set, you need to wait for DNS propagation. This usually takes a few minutes but can take up to 48 hours in some cases. After propagation is complete, your domain will correctly point to your server.

In simple terms, the flow is when a user enters your domain in the browser, Route 53 resolves it to an IP address, and the request is then sent to your server.

Two Sum & Sorted Two Sum

Jarvish John — Wed, 25 Mar 2026 17:01:28 +0000

Problem Statement 1: leetcode

The goal is to find two numbers in the list that add up to the given target.
Instead of checking every pair, we keep track of the numbers already seen.
For each number, we see what value is needed to reach the target.
Then check if that required value has already appeared in the list.
If it has, the solution is found using the index of those two numbers.
If not, store the current number so it can be used for future checks.

def two_sum(nums, target):
    seen={}
    for i in range(len(nums)):
        needed=target-nums[i]
        if needed in seen:
            return[seen[needed],i]
        seen[nums[i]]=i

Problem Statement2: leetcode

def two_sum(numbers, target):
    left = 0
    right = len(numbers) - 1
    while left < right:
        s = numbers[left] + numbers[right]
        if s == target:
            return [left + 1, right + 1]
        elif s < target:
            left += 1
        else:
            right -= 1

The array is already sorted, so instead of checking every pair, we can use pointers at the beginning and end.
At each step, the sum of these two numbers is checked.
If the sum is equal to the target, the answer is found.
If the sum is smaller than the target, left++ to increase the sum.
If the sum is larger, right-- to decrease the sum.

def two_sum(numbers, target):
    left = 0
    right = len(numbers) - 1
    while left < right:
        s = numbers[left] + numbers[right]
        if s == target:
            return [left + 1, right + 1]
        elif s < target:
            left += 1
        else:
            right -= 1