DEV Community: Christina Sharon S

Alter Queries

Christina Sharon S — Sun, 29 Mar 2026 12:14:03 +0000

In this assignment, I worked on modifying existing tables using ALTER TABLE. This helped me understand how to update constraints without recreating tables.

1.Make email NOT NULL in customers

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

2.Make username UNIQUE in users

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

3.Add CHECK constraint on price > 0 in products

ALTER TABLE products ADD CONSTRAINT price_check CHECK (price > 0);

4.Set default 'pending' for status in orders

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

5.Add salary column with constraints in employees

ALTER TABLE employees ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);

6.Modify foreign key with CASCADE

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;

7.Remove CHECK constraint on balance in accounts

ALTER TABLE accounts DROP CONSTRAINT accounts_balance_check;

8.Add composite UNIQUE in payments

ALTER TABLE payments ADD CONSTRAINT unique_user_transaction UNIQUE (user_id, transaction_id);

What I Learned

ALTER TABLE helps modify structure without deleting data
Constraints can be added, removed, or updated anytime
Naming constraints properly makes them easier to manage

Creating Tables using constraints

Christina Sharon S — Sun, 29 Mar 2026 12:09:36 +0000

In this assignment, I practiced creating tables with different constraints like PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT and FOREIGN KEY.

1.Students Table (Unique ID)

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

2.Employees Table (NOT NULL fields)

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

3.Users Table (Unique username & email)

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

4.Products Table (CHECK constraints)

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

5.Orders Table (DEFAULT values)

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

6.Accounts Table (UNIQUE + CHECK)

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

7.Enrollments Table (Composite UNIQUE)

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

8.Departments & Employees (Foreign Key)

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT
);
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

9.Foreign Key with CASCADE

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT
);
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department_id INT,
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

What I Learned

PRIMARY KEY -> uniquely identifies rows
NOT NULL -> prevents empty values
UNIQUE -> avoids duplicates
CHECK -> enforces rules
DEFAULT -> sets automatic values
FOREIGN KEY -> maintains relationships
CASCADE -> keeps related data consistent

Idempotency in a DB

Christina Sharon S — Sun, 29 Mar 2026 11:56:35 +0000

Idempotency is another important topic after the ACID properties.
Idempotency means:
Performing the same operation multiple times should give the same result, not duplicate effects.

Scenario: Duplicate Transfer

I ran the same transfer twice:

~ First transfer

UPDATE accounts SET balance = balance - 200 WHERE name = 'Christina';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Sharon';

~ Same transfer again (duplicate)

UPDATE accounts SET balance = balance - 200 WHERE name = 'Christina';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Sharon';

What Happened?

Christina lost ₹400
Sharon gained ₹400

The system allowed duplicate processing

What I learned

Database does NOT automatically prevent duplicates
Same request executed twice = double transaction
This can lead to wrong balances

Understanding Durability

Christina Sharon S — Sun, 29 Mar 2026 11:47:49 +0000

After exploring Atomicity and Consistency, I tested Durability which makes sure that once a transaction is committed, it is never lost even after a crash.

Creating a base table

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts (name, balance) VALUES ('Christina', 1000), ('Sharon', 500);

Step 1: Perform a Transfer

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Christina';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Sharon';
COMMIT;

After commit:
Christina = 800
Sharon = 700

Step 2: Simulate a crash by closing abruptly

I closed my database connection then reconnected and checked:

SELECT name, balance FROM accounts;

Result After:

Christina = 800
Sharon = 700 The committed transaction was saved.

What I Learned

Once I use COMMIT, the data is permanently saved
Even if the system crashes, committed changes are not lost
PostgreSQL uses mechanisms like Write-Ahead Logging (WAL) to ensure this

Learning the importance of consistency in a DB

Christina Sharon S — Sun, 29 Mar 2026 11:40:18 +0000

After understanding Atomicity, I explored another important concept: Consistency. Consistency ensures that the database always stays in a valid state(i.e no invalid data)

Creating a base table

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts (name, balance) VALUES ('Christina', 1000), ('Sharon', 500);

Trying to Break the Rule (Negative Balance)

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

What Happened?

PostgreSQL rejected it:

ERROR: new row violates check constraint "balance >= 0"

Balance does not change.

Direct Invalid Update

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

Again, it fails due to the CHECK constraint

What I Learned

The rule CHECK (balance >= 0) is enforced by the database schema
PostgreSQL automatically blocks invalid data
This is how Consistency is maintained

Ensuring safety in ewallets using the concept of ATOMICITY

Christina Sharon S — Sun, 29 Mar 2026 11:30:04 +0000

We use ewallets like gpay and phonepe everyday right and I have always wondered where the money goes when the server is down in the middle of a transaction.And that's where the concept of atomicity comes in.Atomicity means all or nothing.Either all the transactions are completed or everything is rolled back.

Creating a base table

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts (name, balance) VALUES ('Christina', 1000), ('Sharon', 500);

Correct Transaction

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Christina';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Sharon';
COMMIT;

Result:
Christina = 800
Sharon = 700

Failure Test

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Christina';
~Error occurs
UPDATE accounts SET balanc = balance + 200 WHERE name = 'Sharon';
COMMIT;

What Happened?

The transaction failed.
Balances remain:
Christina = 1000
Sharon = 500

Creating Users, Roles and Groups in PostgreSQL

Christina Sharon S — Sun, 29 Mar 2026 11:12:53 +0000

In this exercise, I worked with the DVD Rental Database to practice role-based access control in PostgreSQL. The goal was to create users, assign permissions, and control access to tables using SQL.

All tables:

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

Task 2:Fix permission denied when accessing customer table

Task 3:Restrict access to specific columns

Task 4:Create support_user with controlled permissions

Task 5:Create support_user with controlled permissions

Task 6:Create readonly_group with SELECT on all tables

Task 7:Create analyst users and assign them to readonly_group

What I Learned

How to create roles using CREATE ROLE
How to control access using GRANT and REVOKE
Column level permissions in PostgreSQL
How group roles simplify permission management

Filtering a DB using SQL Queries

Christina Sharon S — Sun, 29 Mar 2026 10:40:31 +0000

While practicing SQL, I worked on a movie rental database and tried solving different types of queries using conditions, sorting,limits and pattern matching.

1.Movies with rental rate > $3

SELECT * FROM film WHERE rental_rate > 3;

2.Rental rate > $3 AND replacement cost < $20

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

3.Movies rated 'PG' OR rental rate = $0.99

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

4.Top 10 movies by highest rental rate

SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;

5.Skip first 5, fetch next 3 (ascending rental rate)

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

6.First 5 movies sorted by title

SELECT * FROM film ORDER BY title ASC LIMIT 5;

7.Skip first 10, fetch next 3 (highest replacement cost)

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

8.Top 5 movies with highest replacement cost (skip most expensive)

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

9.Rental duration between 3 and 7 days

SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;

10.Rentals between two dates

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

11.Title starts with 'A' and ends with 'e'

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

12.Title starts with 'A' or 'B' and ends with 's'

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

13.Title contains "Man", "Men", or "Woman"

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

14.Title starts with 'The'

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

15.Title contains "Love" or "Hate"

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

16.Titles ending with 'er', 'or', or 'ar' (top 5)

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

17.Movies released in 2006, rental rate 2.99 or 3.99, title starts with 'S'

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;

18.Movies with rental rate 4.99 and replacement cost > 20

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

19.Movies with rental rate 0.99 OR rating 'PG-13'

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

20.Movies with rating in ('G', 'PG', 'PG-13')

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

21.Movies with rental rate between $2 and $4

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;

22.Movies where special_features is NULL

SELECT * FROM film WHERE special_features IS NULL;

23.Customers without email

SELECT * FROM customer WHERE email IS NULL;

24.Movies with rental duration > 7 days

SELECT * FROM film WHERE rental_duration > 7;

25.Movies with rental rate 2.99 or 4.99, rating 'R', title contains "Love"

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

26.Titles containing % symbol

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

27.Titles containing underscore _

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

28.Titles containing digits

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

29.Titles containing backslash ()

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

30.10 customers after skipping 20 (sorted by last name)

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

What I Learned

How to filter data using WHERE, AND, OR
Sorting with ORDER BY
Pagination(Page navigation) using LIMIT and OFFSET
Pattern matching with LIKE and REGEXP
Handling missing data using NULL

Setting up a DNS hosted zone in ROUTE53 in AWS

Christina Sharon S — Sun, 29 Mar 2026 10:14:00 +0000

What is Route 53?

Amazon Web Services provides Route 53, which helps map a domain name (like www.wikipedia.org) to resources like EC2 instances. Instead of remembering an IP address, users can simply type a domain name which is much easier to remember.

Why Do We Need a Hosted Zone?

A hosted zone is like a container for DNS records of my domain. It helps to:

Connect a domain to an EC2 server
Manage DNS records (A, CNAME, MX, etc.)
Make a website accessible using a custom domain
Control routing and traffic

Steps I Followed to Create a Hosted Zone

1.Open Route 53 Console

I logged into AWS and searched for Route 53.

Then I clicked on:
Hosted Zones → Create Hosted Zone

2.Enter Domain Details

I filled in:

Domain Name: mywebsite.com (example)
Type: Public Hosted Zone

Then I clicked Create Hosted Zone

Understanding Name Servers

After creating the hosted zone, AWS automatically gave me:

4 NS (Name Server) records
1 SOA record

The NS records looked something like:

ns-123.awsdns-45.com
ns-678.awsdns-12.net

Connecting My Domain to Route 53

If I bought my domain from somewhere like GoDaddy or Namecheap, I would need to:

Go to my domain registrar
Find DNS / Nameserver settings
Replace existing nameservers with the AWS ones

This step links my domain to Route 53.

Adding an A Record (Pointing to EC2)

Next, I connected my domain to my EC2 instance.

Steps:

Inside Hosted Zone → Click Create Record
Configure:

Record Name: (leave blank for root domain)
Record Type: A
Value: My EC2 Public IP
TTL: Default

3.Click Create Record

Testing My Domain

After a few minutes, I opened:

http://mywebsite.com

And it worked! My domain was now pointing to my EC2 web server.

What I Learned

How DNS works in real-world applications
How domain names map to IP addresses
Importance of nameservers and DNS records
How AWS simplifies domain management

Creating a simple EC2 instance,running a webserver and accessing it from outside.

Christina Sharon S — Sun, 29 Mar 2026 10:02:31 +0000

What is EC2?

Amazon Web Services provides a service called EC2 (Elastic Compute Cloud), which allows you to create virtual servers in the cloud.Instead of buying a physical computer and maintaining it,you can simply launch a server online in minutes. These virtual servers are called instances.

Why Do We Need EC2?

You don’t need to buy or maintain hardware
You can scale resources up or down anytime
Applications are accessible from anywhere
You only pay for what you use
Built in security and reliability

Basically, EC2 lets us to deploy real world applications without worrying about infrastructure.

Steps I Followed to Create an EC2 Instance

1.Logging into AWS Console

I logged into my AWS account and searched for EC2 in the services.

2.Launching an Instance

I clicked on Launch Instance and configured:

Name: MyWebServer2
AMI: Amazon Linux
Instance Type: t2.micro (Free Tier)
Key Pair: Created a new key pair for SSH access

3.Configuring Security Group

I allowed:

Port 22 (SSH) (To connect to my instance)
Port 80 (HTTP) (To access my website)

4.Launching the Instance

After reviewing everything, I launched the instance and waited for it to start.

Connecting to My Instance

Once the instance was running, I connected using SSH:

ssh -i mykey.pem ec2-user@<public-ip>

Installing a Web Server

Inside the instance, I installed Apache (These lines will be provided by AWS when you create a instance itself)

sudo yum update -y
sudo yum install httpd -y
sudo systemctl start httpd
sudo systemctl enable httpd

Then I created a simple web page:

echo "<h1>Hello from my EC2 server!</h1>" | sudo tee /var/www/html/index.html

Accessing My Website

I copied the Public IP address of my EC2 instance and opened it in my browser:

http://<public-ip>

And just like that my web server was live and accessible from anywhere.

SELECT QUERIES FROM A SPECIFIC DATABASE

Christina Sharon S — Sun, 29 Mar 2026 09:50:14 +0000

The SELECT statement is one of the most fundamental SQL commands and is used to retrieve data from a PostgreSQL database.

Syntax:

SELECT column_name FROM table_name;

I was given a few tasks to strengthen my sql basics so this blog will contain the queries that i performed after connecting my dvdrental database to PostgreSQL.

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;

2.List customer names and their email addresses. Alias first_name and last_name as "First Name" and "Last Name".

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

3.Get a list of films sorted by rental rate in descending order. If two films have the same rental rate, sort them alphabetically by title.

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

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

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

5.List all unique replacement costs from the film table.

SELECT DISTINCT replacement_cost FROM film;

We use DISTINCT here to remove duplicates.

6.List all films' title and length in minutes. Alias length as "Duration (min)".

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

7.Retrieve customer first and last names along with their active status. Alias active as "Is Active".

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

8.Retrieve the list of film categories sorted alphabetically.

SELECT name FROM category ORDER BY name;

To sort use ORDER BY.

9.List films by length, sorted in descending order. Include only the title and length.

SELECT title, length FROM film ORDER BY length DESC;

To sort in a specific order use ASC/DESC,by default it is ASC.

10.Retrieve all actor names, sorted by their first name in descending order.

SELECT first_name, last_name FROM actor ORDER BY first_name DESC;

11.List all unique ratings available in the film table.

SELECT DISTINCT rating FROM film;

12.Find all unique rental durations from the film table.

SELECT DISTINCT rental_duration FROM film;

13.Retrieve the first unique customer ID based on active status. Include the customer_id and active columns, and order by customer_id.

SELECT DISTINCT customer_id, active FROM customer ORDER BY customer_id;

14.List the earliest rental date for each customer. Include customer_id and rental_date, and order by customer_id.

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

15.List the 10 shortest films by length. Include the title and length.

To print till a certain number of records we can use LIMIT

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

16.Get the top 5 customers with the highest customer_id. Include the first and last name.

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

17.Retrieve all unique values of store_id from the inventory table.

SELECT DISTINCT store_id FROM inventory;

18.Find all unique replacement_cost values in the film table. Sort the results in ascending order.

SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;

19.List the first rental date for each store. Include store_id and rental_date, and sort by store_id.

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

20.Retrieve a list of film ratings sorted alphabetically and include only unique values.

SELECT DISTINCT rating FROM film ORDER BY rating;

21.List films by rating in ascending order and length in descending order.

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

22.Retrieve actor names sorted by last_name in ascending order and first_name in descending order.

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

23.List films ordered by replacement_cost in ascending order and rental_rate in descending order.

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

24.Retrieve customer names sorted by last_name ascending and first_name descending.

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

25.List all rentals sorted by customer_id ascending and rental_date descending.

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

26.Retrieve a list of films ordered by rental_duration ascending and title descending.

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

From this set of assignment questions,I was able to understand basic sql select questions and more specifically I understood the situations where some keywords play an important role.

UNDERSTANDING BASIC SELECT SQL QUERIES

Christina Sharon S — Tue, 24 Mar 2026 14:54:13 +0000

What is SQL?

SQL (Structured Query Language) is a language used to interact with databases. A database is just a collection of organized data. For example:

Student records
City information
Weather data

SQL helps us:

Store data
Retrieve data
Update data
Delete data

Why do we need SQL?

Imagine you have a huge table with thousands of rows.

Without SQL:

You would manually search data

With SQL:

You can get exactly what you want in seconds

For example:

Find all cities
Find cities in Japan
Count how many unique cities exist

SQL makes data powerful and usable.

Understanding Tables

STATION Table

ID
CITY
STATE
LAT_N
LONG_W

CITY Table

ID
NAME
COUNTRYCODE
DISTRICT
POPULATION

Each row = one record
Each column = one attribute

What is SELECT?

SELECT is one of the most important SQL command.

It is used to retrieve data from a table.

Basic syntax:

SELECT column_name FROM table_name;

1. Select all columns

SELECT * FROM CITY;

* means "all columns".
Used when you want full data

2. Select specific columns

SELECT CITY, STATE FROM STATION;

3. Select with condition (WHERE)

SELECT * FROM CITY WHERE ID = 1661;

4. Filtering using text condition

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

Keyword is filtering so use WHERE

5. Removing duplicates (DISTINCT)

SELECT DISTINCT CITY FROM STATION;

6. Using functions (COUNT)

SELECT COUNT(CITY) FROM STATION;

7. Difference between total and unique cities

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

8. Filtering cities not starting with vowels

SELECT DISTINCT CITY
FROM STATION
WHERE LOWER(SUBSTR(CITY, 1, 1)) 
NOT IN ('a','e','i','o','u');

SUBSTR(CITY,1,1) -> first letter
LOWER() -> handles uppercase
NOT IN -> excludes vowels

MAIN KEYWORDS

SELECT = fetch data
* = all columns
WHERE = filter rows
DISTINCT = remove duplicates
COUNT() = aggregate data
SUBSTR() = extract part of text
LOWER() = handle case sensitivity