DEV Community: Jeyaprasad R

CA 29 - Setup a DNS hosted zone in Route53 in AWS

Jeyaprasad R — Sun, 05 Apr 2026 11:34:22 +0000

Step 1: Login to AWS

Open AWS Management Console
Search for Route 53
Open the service

Step 2: Create a Hosted Zone (Test Domain)

Click Hosted Zones
Click Create Hosted Zone
Choose:

Private Hosted Zone (best for no real domain)
1. Select a VPC (required for private zone)
2. Click Create Hosted Zone

Step 3: Observe Default Records

You will see:
- NS Record
- SOA Record

Step 4: Create DNS Records

Click Create Record

Add example:

🔹 A Record

Name: server1
Type: A
Value: 192.168.1.10 (dummy IP)

Step 5: (Optional Demo with EC2)

If you want to impress:

Launch an EC2 instance
Use its private IP
Map it in A record:

   server1.mytest.local → EC2 private IP

Step 6: Test DNS (Inside VPC only)

Connect to EC2
Run:

   nslookup server1.mytest.local

It should resolve to your IP

Result

A private hosted zone is successfully created and DNS records are configured without using a public domain.

Conclusion

This project demonstrates internal DNS resolution using Route 53 without requiring a registered domain name.

CA 28 - Creating a simple EC2 instance and run a webserver and access it from outside

Jeyaprasad R — Sun, 29 Mar 2026 16:37:19 +0000

Let’s go step by step and launch a simple web server on AWS.

Step 1: Launch an EC2 Instance
Go to the AWS Console open EC2 and click on Launch Instance.
Now configure the instance:

Step 2: Choose a Key Pair
You need a key pair to securely connect to your instance.
You can either create a new one or use an existing key pair.
Make sure to download the .pem file when creating it, because you’ll need it later to connect.

Step 3: Configure Network Settings
Allow the following ports:
SSH (port 22) for logging into the server
HTTP (port 80) so your website can be accessed from a browser

Step 4: Launch the Instance
Click on Launch Instance.
Your server will now be created and started.

Step 5: Open the terminal of ubuntu in AWS

Step 6: Open sudo nano index.html, this gives a default html file

Step 7: Type a simple html code

Step 8: Access from Your Browser
Open your browser and enter your address- http://44.205.7.223/
NOW STOPPED

CA38- Idempotency Situation

Jeyaprasad R — Sun, 29 Mar 2026 15:26:37 +0000

Let me explain duplicate transaction First we have accounts table
Alice = 1000
Bob = 500
now we try same transfer again and again.

First transfer:

After first transfer:
Alice = 800
Bob = 700
200 is transferred successfully

let's run the transfer again

Again, the same operation is performed, and money is transferred once more
Since the database is not aware that it is a duplicate request, it will execute it over and over again, and hence duplicate transactions are allowed

The problem is, the request is executed multiple times, and hence money is deducted multiple times, which is not correct

Since, at the end of the day, the database is only ensuring that the request is executed correctly. It does not ensure that the request is not executed twice. Hence, the problem has to be addressed at the application level

In most systems, it is addressed by providing a unique ID for the transaction and checking whether the transaction has already been processed or not. Hence, even if the request is executed twice, it will not be processed twice. This way, duplicate transactions are not allowed, and the system remains consistent. This is how most payment systems handle duplicate transactions.

CA37- Durability

Jeyaprasad R — Sun, 29 Mar 2026 15:21:16 +0000

Let’s understand durability using a simple accounts table
Initially, we have:

Alice = 1000
Bob = 500 let's perform a money transfer of 300 from Alice to Bob:

At this point we deducted money from Alice added it to Bob and committed the transaction. So, the updated balances become
Alice = 700
Bob = 800
this is durability

Now we simulate system restart reconnect and check data:
SELECT * FROM accounts;
Result:
Alice = 700
Bob = 800
even after restart data is still there

Conclusion:
Once a transaction is committed its changes are permanently saved in the database. That's exactly what durability means

CA35- Consistency

Jeyaprasad R — Sun, 29 Mar 2026 15:15:43 +0000

This exercise moves from Atomicity (all-or-nothing) to Consistency (the "C" in ACID)

Scenario 1:- The Direct Attack: Manual Negatives

In our first scenario, we attempted a direct UPDATE to set Alice's balance to -100.

Scenario 2:- The Transactional Tug-of-War

In our second scenario, we see a multi-step transfer: deducting 1200 from Alice and adding it to Bob within a BEGIN...COMMIT block.

C34- Atomicity

Jeyaprasad R — Sun, 29 Mar 2026 14:56:43 +0000

STEP 1:

STEP 2:

STEP 3:

Conclusion
In a world of unreliable connections and buggy code, ACID guarantees are what’s holding our digital wallets together and keeping them accurate. The next time you’re greeted by a "Transaction Failed" screen, thank goodness for the ROLLBACK command.

CA40- Alter Tables

Jeyaprasad R — Sun, 29 Mar 2026 10:07:08 +0000

Q1.

Q2.

Q3.

Q4.

Q5.

Q6.

Q7.

Q8.

CA38- Create Tables

Jeyaprasad R — Sun, 29 Mar 2026 09:56:27 +0000

Q1.

Q2.

Q3.

Q4.

Q5.

Q6.

Q7.

Q8.

Q9.

CA33- Users, Roles, Groups

Jeyaprasad R — Sun, 29 Mar 2026 04:19:24 +0000

TASK 1

TASK 2

TASK 3

TASK 4

TASK 5

TASK 6

TASK 7

CA32- Filter Assignments

Jeyaprasad R — Sun, 29 Mar 2026 03:13:58 +0000

1.Get all movies (films) that have a rental rate greater than $3.

2.Get all movies that have a rental rate greater than $3 and a replacement cost less than $20.

3.Get all movies that are either rated as 'PG' or have a rental rate of $0.99.

4.Show the first 10 movies sorted by rental rate (highest first).

5.Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.

6.Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.

7.Get all movies with a rental duration between 3 and 7 days.

8.Get all movies where the title starts with 'A' and ends with 'e'

9.Find all customers who do not have an email address listed.

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.SELECT title, rental_rate, release_year

11.Display 10 customers after skipping the first 20, sorted alphabetically by last name.

12.Get the top 5 movies with the highest replacement cost, skipping the most expensive one.

13.Find all rentals that occurred between '2005-05-01' and '2005-06-01'.

14.Get all actors whose last names contain the letters "man".

15.Find all movies where the special features are not listed (i.e., special_features is NULL).

16.Find all movies where the rental duration is more than 7 days.

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".

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

19.Find all movies where the title contains "Man", "Men", or "Woman".

CA31- Select Queries from DVD Rental database

Jeyaprasad R — Sun, 29 Mar 2026 02:14:07 +0000

Film titles and rental rates (Aliased)

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

2.Customer names and emails (Aliased)

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

3.Films sorted by rental rate (DESC) and title (ASC)

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

4.Actor names sorted by last name, then first name

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

5.Unique replacement costs

   SELECT DISTINCT replacement_cost 
   FROM film;

6.Film title and length (Aliased)

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

7.Customer names and active status (Aliased)

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

8.Film categories sorted alphabetically

   SELECT name FROM category 
   ORDER BY name ASC;

9.Films by length (DESC)

   SELECT title, length 
   FROM film 
   ORDER BY length DESC;

10.Actor names sorted by first name (DESC)

```sql
SELECT first_name, last_name 
FROM actor 
ORDER BY first_name DESC;
```

11.Unique ratings

```sql
SELECT DISTINCT rating 
FROM film;
```

12.Unique rental durations

```sql
SELECT DISTINCT rental_duration 
FROM film;
```

13.First unique customer ID based on active status

```sql
SELECT MIN(customer_id) AS customer_id, active 
FROM customer 
GROUP BY active 
ORDER BY customer_id;
```

14.Earliest rental date for each customer

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

15.10 shortest films

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

16.Top 5 customers with highest IDs

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

17.Unique store IDs from inventory

```sql
SELECT DISTINCT store_id 
FROM inventory;
```

18.Unique replacement costs (Sorted ASC)

```sql
SELECT DISTINCT replacement_cost 
FROM film 
ORDER BY replacement_cost ASC;
```

19.First rental date for each store

```sql
SELECT store_id, MIN(rental_date) AS rental_date 
FROM rental 
JOIN inventory ON rental.inventory_id = inventory.inventory_id
GROUP BY store_id 
ORDER BY store_id;
```

20.Unique film ratings sorted alphabetically

```sql
SELECT DISTINCT rating 
FROM film 
ORDER BY rating ASC;
```

21.Films by rating (ASC) and length (DESC)

```sql
SELECT * FROM film 
ORDER BY rating ASC, length DESC;
```

22.Actor names (Last Name ASC, First Name DESC)

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

23.Films by replacement cost (ASC) and rental rate (DESC)

```sql
SELECT * FROM film 
ORDER BY replacement_cost ASC, rental_rate DESC;
```

24.Customer names (Last Name ASC, First Name DESC)

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

25.Rentals (Customer ID ASC, Rental Date DESC)

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

26.Films (Rental Duration ASC, Title DESC)

```sql
SELECT * FROM film 
ORDER BY rental_duration ASC, title DESC;
```

CA30- Basic Hackerrank Queries

Jeyaprasad R — Sun, 29 Mar 2026 00:43:28 +0000

1.Select All
The Goal: Query all columns for every row in the CITY table.

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x9izg30iwcgr03kjizj3.png

2.Select by ID
The Goal: Query all columns for a city in CITY with the ID 1661.

3.Revising the Select Query I
The Goal: Query all columns for American cities in CITY with populations larger than 100,000. The Country Code for America is USA

4.Revising the Select Query II
The Goal: Query the NAME field for all American cities with populations larger than 120,000.

5.Japanese Cities' Attributes
The Goal: Query all attributes of every Japanese city in the CITY table. The Country Code for Japan is JPN.

6.Japanese Cities' Names
The Goal: Query the names of all the Japanese cities in the CITY table.

7.Weather Observation Station 1
The Goal: Query a list of CITY and STATE from the STATION table.

8.Weather Observation Station 4
The Goal: Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

9.Weather Observation Station 9
The Goal: Query the list of CITY names from STATION that do not start with vowels (a, e, i, o, u). Your result cannot contain duplicates.