DEV Community: Luckshvadhan B

Alter Tables

Luckshvadhan B — Sat, 28 Mar 2026 18:35:42 +0000

1 alter table customers modify email varchar(150) not null

2 alter table users add unique (username)

3 alter table products add check (price > 0)

4 alter table orders alter status set default 'pending'

5 alter table employees add salary decimal(10,2) not null check (salary > 10000)

6 alter table employees drop foreign key department_id
alter table employees add foreign key (department_id) references departments(id) on delete cascade

7 alter table accounts drop check balance

8 alter table payments add unique (user_id transaction_id)

Create Tables

Luckshvadhan B — Sat, 28 Mar 2026 18:13:30 +0000

1 create table students id int primary key name varchar(100) not null age int check age >= 0

2 create table employees id int primary key name varchar(100) not null email varchar(150) not null phone_number varchar(20)

3 create table users id int primary key username varchar(100) unique not null email varchar(150) unique not null

4 create table products id int primary key name varchar(100) not null price decimal(10,2) check price > 0 stock int check stock >= 0

5 create table orders id int primary key status varchar(50) default 'pending' created_at timestamp default current_timestamp

6 create table accounts id int primary key account_number varchar(50)
unique not null balance decimal(10,2) check balance >= 0

7 create table enrollments student_id int course_id int primary key (student_id, course_id)

8 create table departments id int primary key name varchar(100) not null
create table employees id int primary key name varchar(100) not null department_id int foreign key (department_id) references departments(id)

9 create table departments id int primary key name varchar(100) not null
create table employees id int primary key name varchar(100) not null department_id int foreign key (department_id) references departments(id) on delete cascade on update cascade

Basic Select SQL Queries

Luckshvadhan B — Wed, 25 Mar 2026 17:17:58 +0000

Get all details of city with id 1661
Filter CITY table where id equals 1661 and return all columns

Get all cities from Japan
Filter CITY table where countrycode is JPN and return all columns

Get all cities from Japan
Same filter using countrycode JPN to retrieve all records

Get city names not starting with vowels
Use pattern filtering to exclude names starting with A E I O U and remove duplicates

Get city names not starting with vowels
Apply same condition with distinct to avoid repeated values

Get city names not starting with vowels
Filter using NOT LIKE patterns and ensure unique results

Find difference between total and unique city entries
Subtract count of distinct city names from total count

Get names of Japanese cities
Select only city name where countrycode is JPN

Get names of Japanese cities
Same filtering on countrycode but return only name column

Idempotency Situation

Luckshvadhan B — Wed, 25 Mar 2026 17:11:55 +0000

The problem is
Same transaction may be executed multiple times
This can happen due to network retry or duplicate request
This may lead to wrong balances

Initial Data
Alice has 1000
Bob has 500

Normal Transfer
Transfer 200 from Alice to Bob

After transaction
Alice becomes 800
Bob becomes 700

Duplicate Execution
Same transfer runs again

After second execution
Alice becomes 600
Bob becomes 900

Transaction is applied again

Database allows repeated execution
It does not know whether request is duplicate
Each execution is treated as a new operation

Because database only ensures consistency per transaction
It does not track duplicate requests
There is no built in mechanism to stop same transfer again

Database ensures No partial updates and Valid data state

Use unique transaction id for each request
Check if transaction already exists before processing
Reject duplicate requests

Database ensures correctness of each transaction
Application ensures transaction is not repeated

Durability

Luckshvadhan B — Wed, 25 Mar 2026 17:08:52 +0000

The problem
We are transferring money between accounts
After transaction is committed data should not be lost
Even if system crashes data must remain correct

Initial Data
Alice has 1000
Bob has 500

Transaction Flow
Transfer 200 from Alice to Bob

Step 1
Start transaction
Step 2
Deduct 200 from Alice
Step 3
Add 200 to Bob
Step 4
Commit transaction

After Commit

Alice balance becomes 800
Bob balance becomes 700
Changes are saved in database
After System Restart
Reconnect to database
Check balances again
Alice still has 800
Bob still has 700

Changes are not lost

Database follows durability property
Once transaction is committed data is permanently stored
Even if system crashes
Committed data remains

Failure before commit

Transaction is not completed
Changes are rolled back
Final state
Alice 1000
Bob 500

Failure after commit

Transaction already saved
Data remains unchanged
Final state
Alice 800
Bob 700

Commit makes data permanent
Database ensures committed data is never lost

Consistency

Luckshvadhan B — Wed, 25 Mar 2026 17:06:18 +0000

The problem is
We are managing account balances in a system
Balance should never become negative
Invalid operations must be rejected

Initial Data
Alice has 1000
Bob has 500

Database Rule
Balance has a constraint balance greater than or equal to 0
This ensures invalid values are not allowed

Valid Operation
Deduct 200 from Alice

After update
Alice balance becomes 800

This works because balance is still valid

Error Case 1
Trying to deduct more than available balance
Deduct 1200 from Alice

Database throws error
Check constraint fails

Final state
Alice still has 1000
No change happens

Error Case 2
Directly setting balance to negative
Update balance to minus 100

Database rejects the update
Constraint violation occurs

Final state
Balance remains unchanged

Because
Database enforces rules using constraints
It does not allow invalid data to be stored

Even if query is correct syntactically
It will fail if it breaks defined rules

Database handles structural rules like
Balance should not be negative

But logical rules like
Checking enough balance before transfer
Should be handled in application or transaction logic

Constraints act as a safety layer
They prevent invalid data from entering database

Atomicity

Luckshvadhan B — Wed, 25 Mar 2026 17:03:06 +0000

The problem
We are transferring money between two users
If something fails in between it can cause wrong balance or money loss
So both debit and credit must happen together or not happen at all

Initial Data
Alice has 1000
Bob has 500

Transaction Logic
Step 1
Start transaction
Step 2
Deduct amount from sender
Step 3
Add amount to receiver
Step 4
Commit transaction

If any step fails rollback everything

Normal Flow
Transfer 200 from Alice to Bob

After transaction
Alice balance becomes 800
Bob balance becomes 700

Both updates happen successfully

Error Case 1
Debit happens but credit fails
Transaction is not committed
Database rolls back

Final state

Alice still has 1000
Bob still has 500
No partial update happens

Error Case 2
Query fails after debit step
Transaction stops
Rollback is triggered

Final state

Alice balance is unchanged
Bob balance is unchanged

BECAUSE:
Database follows ACID properties
Transaction ensures all operations succeed together
If any step fails entire transaction is cancelled

There is no situation where
Alice loses money and Bob does not receive it
Either both balances update
Or nothing changes

If transactions are not used data inconsistency will happen
Improper handling can still cause issues in high concurrency systems
Transaction acts like a safety system
It guarantees consistency in money transfer
This is critical for systems like payment apps

Users, Roles, Groups

Luckshvadhan B — Wed, 25 Mar 2026 16:59:14 +0000

Create a report_user role with read access only on film table
Create a login role and grant SELECT permission only on film table

Fix permission denied when accessing customer table
Grant SELECT permission on customer table to report_user

Allow report_user to see only selected columns from customer
Revoke full access and grant SELECT only on customer_id first_name last_name columns

Create support_user with limited permissions
Grant SELECT on customer and UPDATE only on email column and do not give DELETE permission

Remove SELECT access on film from report_user
Revoke SELECT permission on film table from report_user

Create readonly_group with read access on all tables
Create role and grant SELECT permission on all tables in the database

Add analyst users to readonly_group
Create analyst1 and analyst2 and assign them to readonly_group

Filter Assignments

Luckshvadhan B — Wed, 25 Mar 2026 16:57:19 +0000

Get movies with rental rate greater than 3
Filter films where rental_rate is greater than 3

Get movies with rental rate greater than 3 and replacement cost less than 20
Apply two conditions using AND on rental_rate and replacement_cost

Get movies rated PG or rental rate 0.99
Use OR condition between rating and rental_rate

Get first 10 movies sorted by rental rate descending
Order by rental_rate in descending order and limit to 10

Skip first 5 and get next 3 movies by rental rate ascending
Use offset 5 and limit 3 after sorting by rental_rate ascending

Skip first 5 and get next 3 movies by rental rate ascending
Same as above using offset and limit with ascending order

Get movies with rental duration between 3 and 7
Use between condition on rental_duration

Get movies where title starts with A and ends with e
Use pattern matching with LIKE for starting A and ending e

Find customers without email
Filter where email is NULL

Get movies from 2006 with rate 2.99 or 3.99 and title starts with S limit 5
Apply multiple conditions with AND and OR and limit results to 5

Get 10 customers after skipping 20 sorted by last name
Sort by last_name and use offset 20 and limit 10

Get top 5 movies by replacement cost skipping highest
Order by replacement_cost descending skip first then limit 5

Find rentals between two dates
Use between on rental_date from given start to end date

Get actors whose last name contains man
Use LIKE with pattern containing man

Find movies where special features is NULL
Filter rows where special_features is NULL

Find movies with rental duration more than 7
Use greater than condition on rental_duration

Get first 10 movies with rate 2.99 or 4.99 rating R and title contains L
Combine multiple conditions and limit to 10

Find movies starting with A or B and ending with s
Use LIKE with OR for starting letters and ending pattern

Find movies containing Man Men or Woman
Use multiple LIKE conditions combined with OR

Select Queries from DVD Rental database

Luckshvadhan B — Wed, 25 Mar 2026 16:55:27 +0000

Retrieve film titles and rental rates
Select title and rental_rate and rename them as Movie Title and Rate using aliases

List customer names and email
Select first_name last_name and email and rename names as First Name and Last Name

Sort films by rental rate descending then title
Order films by rental_rate in descending order and use title as secondary ascending sort

Sort actor names by last then first name
Order actors first by last_name then by first_name

Get unique replacement costs
Use distinct on replacement_cost to remove duplicates

List film title and duration
Select title and length and rename length as Duration min

Get customer names with active status
Select first_name last_name and active and rename active as Is Active

List film categories alphabetically
Select category names and sort them in ascending order

List films by length descending
Select title and length and sort by length in descending order

Sort actor names by first name descending
Order actors by first_name in descending order

Get unique film ratings
Use distinct on rating column to list unique values

Get unique rental durations
Use distinct on rental_duration to remove duplicates

Get first unique customer by active status
Select customer_id and active remove duplicates and order by customer_id

Get earliest rental date for each customer
Group by customer_id and get minimum rental_date then sort by customer_id

Get 10 shortest films
Order films by length ascending and limit results to 10

Get top 5 customers by highest id
Order customers by customer_id descending and limit to 5

Get unique store ids
Use distinct on store_id from inventory

Get unique replacement cost sorted
Use distinct on replacement_cost and sort in ascending order

Get first rental date per store
Group by store_id and get minimum rental_date then sort by store_id

Get unique film ratings sorted
Use distinct on rating and sort alphabetically

Sort films by rating and length
Order by rating ascending and length descending

Sort actors by last asc and first desc
Order by last_name ascending and first_name descending

Sort films by cost and rental rate
Order by replacement_cost ascending and rental_rate descending

Sort customers by last asc and first desc
Order by last_name ascending and first_name descending

Sort rentals by customer and date
Order by customer_id ascending and rental_date descending

Sort films by duration and title
Order by rental_duration ascending and title descending

Setup a DNS hosted zone in Route53 in AWS

Luckshvadhan B — Wed, 25 Mar 2026 16:48:36 +0000

Route53 is a DNS service provided by AWS
It helps map domain names to IP addresses

Steps to setup hosted zone
Step 1
Login to AWS console
Step 2
Go to Route53 service
Step 3
Click Hosted Zones
Step 4
Click Create Hosted Zone
Step 5
Enter your domain name
Step 6
Select Public Hosted Zone
Step 7
Click Create
Step 8
Note the Name Servers provided
Step 9
Go to your domain registrar
Step 10
Update the domain with these Name Servers
Step 11
Wait for DNS propagation

Now your domain is connected to Route53
You can create records like A record to point to your server

DNS changes may take time to reflect
Wrong name server configuration will break domain resolution

EC2 instance and run a webserver and access it from outside

Luckshvadhan B — Wed, 25 Mar 2026 16:47:01 +0000

EC2 is a virtual server provided by AWS
It allows you to run applications on the cloud

Steps to create EC2 instance
Step 1
Login to AWS console
Step 2
Go to EC2 dashboard
Step 3
Click Launch Instance
Step 4
Choose an OS like Amazon Linux
Step 5
Select instance type like t2 micro
Step 6
Create or select a key pair
Step 7
Allow HTTP and SSH in security group
Step 8
Launch the instance

Running a web server on an EC2 instance involves connecting to the instance using SSH and installing a web server like Apache
First update the system using sudo yum update -y
Then install the server using sudo yum install httpd -y
After installation start the service using sudo systemctl start httpd
Enable it to run on boot using sudo systemctl enable httpd
Create a simple page by adding content
If the security group allows HTTP traffic you can access the web server using the public IP of the instance in a browser

To access from outside

Copy the public IP of the instance

Open browser and enter
http://your-public-ip

You will see the web page

Make sure port 80 is open in security group
Without this the page will not load