DEV Community: Anjana R.K.

Idempotency Situation

Anjana R.K. — Sun, 29 Mar 2026 17:42:34 +0000

Hi!
To Simulate a situation where the same transfer operation is executed more than once, as might happen in a real system due to network retries or duplicate requests.

CREATE TABLE accounts (id SERIAL PRIMARY KEY,name TEXT NOT NULL,balance INT NOT NULL CHECK (balance >= 0),last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

inserting values to the table accounts,

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000),('Bob', 500);

to observe whether the accpunt balance is affected the deduction and credit operations performed multiple times

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

to analyze whether the system prevents duplicate processing or allows the same transaction to be applied repeatedly
*it does not automatically prevent duplicate processing of the same transaction.

Durability

Anjana R.K. — Sun, 29 Mar 2026 17:35:19 +0000

hi!
To Perform a successful transfer between two accounts and commit the transaction,
first creating a table accounts,

CREATE TABLE accounts (id SERIAL PRIMARY KEY,name TEXT NOT NULL,balance INT NOT NULL CHECK (balance >= 0),last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

the table contains attributes id as peimary key, name,balance >=0,last_updated.next to insert values to the table accounts

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000),('Bob', 500);

To ensuring that the changes are reflected in the database.

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 300 WHERE name = 'Bob';
COMMIT;

here the amount deduction and the amount added is done successfully.
After simulating a system restart or database crash to check whether the reconnect to the database account balance is successful.And to verify whether the committed changes persist even after the restart.

SELECT name, balance FROM accounts;

how the system guarantees durability and consider what could happen if a failure occurs just before or just after a commit, and how the database ensures that committed data is not lost.
For this PostgreSQL uses a mechanism called Write-Ahead Logging (WAL).
Before changes are applied to disk,the database describes the change in log file (the WAL).
When COMMIT, the database ensures this log is stored to the disk.
*if a failure occurs just after-the data will be in wal because commit is executed
*if a failure occurs just before-the is treated as incomplete. Upon restart, the database identifies this as an uncommitted transaction and performs a Rollback.

Consistency

Anjana R.K. — Sun, 29 Mar 2026 17:11:35 +0000

Hi!
To explore how the database enforces rules that maintain valid data states, particularly ensuring that account balances never become negative.
first create a table account,

CREATE TABLE accounts (id SERIAL PRIMARY KEY,name TEXT NOT NULL,balance INT NOT NULL CHECK (balance >= 0),last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

the table contains atributes id as primary key,name,balance hee check for condition as if balance >=0,last_updated here set default value as current timestamp.
Next,insert values to the table.

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000),('Bob', 500);

Attempt to perform operations that violate deducting more money than is available in an account or directly updating a balance to a negative value.

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

IT WILL THROW ERROER as attempting to deduct 1200 from Alice who only has 1000.
In PostgreSQL,the failure occurs due to Constraints because if the deduction occour with balance less than the debit amount while checking the constraints the balance >=0 fails.

Atomicity - Design a Reliable Wallet Transfer System with ACID Guarantees

Anjana R.K. — Sun, 29 Mar 2026 16:46:33 +0000

hi!
to Design a transaction that transfers money from one account to another using the accounts 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);

create a table with attributes id,name,balance to check balance in account,and last_update .then insert values to the table using insert.
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000),('Bob', 500);

BEGIN;
UPDATE accounts 
SET balance = balance - 200 
WHERE name = 'Alice';
UPDATE accounts 
SET balance = balance + 200 
WHERE name = 'Bob';
COMMIT;

the balance from sender in deducted using update and again using update the balance of reciever is added then the transaction is commited to get the updated result.

BEGIN;
UPDATE accounts 
SET balance = balance - 100 
WHERE name = 'Alice';
UPDATE accounts 
SET balances = balance + 100 
WHERE name = 'Bob';
ROLLBACK;

here to show Failed Transaction: Breaking the Credit Operation
first the query 1:updating the account by debiting succeeds
the next query 2:updating the accounts by crediting but here the column name is wrong ,so it fails.Due to the failure of query 2 it roolback.
Creating Failed Transaction for Constraint Violation

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

here the debited amount is more than the available balance so,this will trigger the check constraint violation immediatelyand the system will not allow the next step.

Alter Tables

Anjana R.K. — Sun, 29 Mar 2026 16:20:16 +0000

Hi!
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.

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

here the alter is used to set the column email that should not be blank.
2. In the users table, ensure that the username column is unique across all records using an ALTER statement.

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

here an new contraint unique_name is used to keep unique user name thus ensure that the username column is unique across all records.
3. In the products table, enforce that price must always be greater than 0 using an ALTER command.

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

here the table prducts is altered with price that has values greater than 0.
4. Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.

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

the oders table is modified by setting the default value of status with 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

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

the column salary is added and it should not be null and check condition that salary >10000.
6. Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.

ALTER TABLE employees ADD CONSTRAINT employees_department_id FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;

the on delete cascade is used so that when a department is deleted,all related employees are automatically removed .
7. In the accounts table, remove an existing CHECK constraint that enforces balance >= 0

ALTER TABLE accounts DROP CONSTRAINT accounts_balance;

8. In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.

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

creates a Unique Constraint on table payments.It ensures that the exact same user_id and transaction_id combination can never be recorded twice, effectively preventing duplicate payment entries for the same transaction.

Alter Tables

Anjana R.K. — Sun, 29 Mar 2026 16:11:49 +0000

Hi!
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.

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

here the alter is used to set the column email that should not be blank.
2. In the users table, ensure that the username column is unique across all records using an ALTER statement.

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

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

here the table prducts is altered with price that has values greater than 0.
4. Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.

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

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

ALTER TABLE employees ADD CONSTRAINT employees_department_id FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;

ALTER TABLE accounts DROP CONSTRAINT accounts_balance;

8. In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.

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

Create Tables

Anjana R.K. — Sun, 29 Mar 2026 15:24:28 +0000

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

Create table student(id SERIAL PRIMARY KEY,name varchar(50),age int);

i created a table student wher the id value should be unique ,let it be primary key,then name is give varchar datatype,for age the data type is int.
*2. Create a table employees where name and email cannot be empty, but phone_number can be optional.
*

create table employee(e_id serial primary key,name varchar(50) not null,email va
rchar(101) not null,phone_number int);

here a table employee is created with fields employee id,name ,email,phone number where e_id is primary key ,name and email are not null
3. Create a table users where both username and email must be unique across all records

 CREATE table users(u_id SERIAL PRIMARY KEY,username varchar(50) unique,email var
char(101) unique);

here a table users is created where user id is u_id which is the primary key in the table,while username and email are unique.
*4. Create a table products where price must always be greater than 0 and stock cannot be negative.
*

 create table products(p_id serial primary key,price int check(price > 0),
 product_name varchar(101),stock int check(stock>=0))

in the above code the table product haave primary key p_id,product name, price which should be greater than 0 and stock must not be negative
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.

 create table  orders(o_id serial primary key,order_name varchar(101),status varc
har(50) default 'pending',created_at timestamp);

here the table order has a order id as primary key, order_name,status is set default as pending,and created_at use timestamp datatype.
6.Create a table accounts where account_number must be unique and not null
balance must always be greater than or equal to 0

CREATE TABLE accounts (account_id SERIAL PRIMARY KEY,account_number VARCHAR(20) UNIQUE NOT NULL,balance DECIMAL(15,2) default 0 CHECK (balance >= 0));

create table accounts account_number is unique to prevent duplicates, and uses a CHECK balance is not negative.
*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.
*

CREATE TABLE enrollments (e_id SERIAL PRIMARY KEY,s_id INT,c_id INT,UNIQUE (student_id, course_id));

sql
create table enrollments unique values student_id and course_id. This prevents a student from being registered for the same course more than once while still allowing them to take many different courses.
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.

CREATE TABLE departments (id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,department_id INT REFERENCES departments(id));

sql
the departments table contain id,name and the employee table contain id,name,department_it which exist in departments table foreign key.
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

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) ON DELETE CASCADE ON UPDATE CASCADE);

the table departments contain id,name.where teh table employees contain id,name,department_id as reference from table departments which delete all related employees when deleted ,When a department ID is updated, it reflects in the employees table.

Users, Roles, Groups

Anjana R.K. — Wed, 25 Mar 2026 19:32:20 +0000

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

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

here a role named report_user is created with login password.Then SELECT permission is granted only on the film table. This means the user can read data from film but cannot modify it or access other tables.

Task 2: Now try to access customer table. When report_user tries to query customer and gets permission denied. Fix it.

GRANT SELECT ON customer TO report_user;

The error occurs because report_user does not have permission on the customer table. Granting SELECT allows the user to read data from that table and resolve the permission denied issue.

Task 3: Allow report_user to see only customer_id, first_name, last_name of the customer table.

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

First, full SELECT access is removed to avoid unrestricted viewing. Then column-level permissions are granted so the user can only see specific columns such as customer_id, first_name, last_name .

Task 4: Create support_user who can SELECT from customer, UPDATE only email column, Cannot DELETE

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

The support_user role is created with login access. It is allowed to read all customer data using SELECT. It can only update the email column due to column-level UPDATE permission. DELETE permission is explicitly revoked to ensure the user cannot remove records.

Task 5: Remove SELECT access on film from report_user.

REVOKE SELECT ON film FROM report_user;

here the select removes the previously granted read access on the film table, so report_user can no longer query it.

Task 6: Create readonly_group that has SELECT on all tables.

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

A group role readonly_group is created. It is granted SELECT access on all existing tables in the public schema .The ALTER DEFAULT PRIVILEGES ensures that any future tables created in the schema will also automatically grant SELECT access to this group.

Task 7: Create analyst1 and analyst2 and add them to readonly_group.

CREATE ROLE analyst1 LOGIN PASSWORD 'password';
CREATE ROLE analyst2 LOGIN PASSWORD 'password';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Two new login roles (analyst1 and analyst2) are created. They are added to readonly_group, which means they inherit all its permissions, including read-only access to all tables. This avoids granting permissions individually and simplifies management.

DB-TASK-Filtering Data

Anjana R.K. — Wed, 25 Mar 2026 18:47:06 +0000

Hi everyone!
Find all movies where the special features are not listed (i.e.,special_features is NULL).
This query selects all movies where special_features column has no value (NULL).

SELECT * FROM film WHERE special_features IS NULL;

Find all movies where the rental duration is more than 7 days.
selects movies whose rental duration is greater than 7 days.

SELECT * FROM film WHERE rental_duration > 7;

Find all movies that have a rental rate of $4.99 and a replacement cost of more than $20.
this query selects movies that satisfy both rental_rate = 4.99 and replacement_cost greater than 20.

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

Find all movies that have a rental rate of $0.99 or a rating of 'PG-13'.
this query selects movies that satisfy either rental_rate = 0.99 or rating = 'PG-13'.

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

Retrieve the first 5 rows of movies sorted alphabetically by title.
this query selects first 5 movies sorted in alphabetical order using title.

SELECT * FROM film ORDER BY title ASC LIMIT 5;

Skip the first 10 rows and fetch the next 3 movies with the highest replacement cost.

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

this query sorts movies by replacement_cost in descending order, skips first 10 rows and selects next 3.

Find all movies where the rating is either 'G', 'PG', or 'PG-13'.
this query selects movies whose rating is in the given list.

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

Find all movies with a rental rate between $2 and $4.
this query selects movies whose rental rate is between 2 and 4.

SELECT * FROM film WHERE rental_rate BETWEEN 2 AND 4;

Find all movies with titles that start with 'The'.
this query selects movies whose title starts with "The".

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

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".
here it selects from movies that satisfies all conditions and limits result to 10.

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

Find all movies where the title contains the % symbol.
this query selects movies where title contains % symbol using escape.

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

Find all movies where the title contains an underscore (_).
SELECTS movies where title contains underscore character .

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

Find all movies where the title starts with "A" or "B" and ends with "s".
The query selects movies that start with Aor B and end with s.

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

Find all movies where the title contains "Man","Men", or"Woman".
This query selects movies whose title contains given words.

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

Find all movies with titles that contain digits (e.g.,"007", "2", "300").
this query selects movies whose title contains numbers.

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

Find all movies with titles containing a backslash().
this query selects movies whose title contains backslash symbol.

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

Find all movies where the title does contain the words "Love" or "Hate".
this selects movies containing either Love or Hate in title.

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

Find the first 5 movies with titles that end with "er", "or",or "ar".

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

here it selects movies ending with given patterns and limits to 5.

DB-TASK using dvdrental database

Anjana R.K. — Wed, 25 Mar 2026 18:31:31 +0000

Hi everyoune!

Retrieve film titles and their rental rates. Use column aliases to rename title as "Movie Title" and rental_rate as "Rate".
this query selects title and rental_rate from table film and renames the columns as Movie_Title and Rate.

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

List customer names and their email addresses. Alias first_name and last_name as "First Name" and "Last Name".
This query selects first name, last name, and emailfrom customer table and then renamed first_name as First Name and last_name as Last Name.

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

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;

here we select films sorted by highest rental rate first, and if same rate, sorted alphabetically by title.

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

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

select actors arranged based on last name first, then first name.

List all unique replacement costs from the film table.

SELECT DISTINCT replacement_cost FROM film;

the query selects only unique replacement cost values without duplicates using distinct.

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

SELECT title, length as "Duration" FROM film;

selects title and length from table film and rename length as Duration.

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;

selects customer first_name,last_name and check whether they are active or not based on column active.

Retrieve the list of film categories sorted alphabetically.

SELECT name FROM category ORDER BY name;

selects all categories sorted in alphabetical order.

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

SELECT title, length FROM film ORDER BY length DESC;

selects title and length from table film and order based on length in descending.

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

SELECT first_name, last_name FROM actor ORDER BY first_name DESC;

here the table Actors is sorted in reverse alphabetical order of first name.

List all unique ratings available in the film table

SELECT DISTINCT rating FROM film;

Select all distinct ratings fron the table film.

Find all unique rental durations from the film table.

SELECT DISTINCT rental_duration FROM film;

the query selects unique rental durations from the film table.

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

SELECT customer_id, active FROM customer ORDER BY customer_id;

selects customer_id and active from table customer and sorted by id.

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) FROM rental GROUP BY customer_id ORDER BY customer_id;

selects the earliest rental date for each customer by taking average on each customer_id.

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

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

select first 10 films with smallest duration using limit.

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;

select last 5 customers with highest customer_id by ordering in descending and apply limit.

Retrieve all unique values of store_id from the inventory table.

SELECT DISTINCT store_id FROM inventory;

select all unique values of store_id from the inventory table.

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;

select all unique replacement_cost values in the film table sort in ascending order.

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) FROM rental GROUP BY store_id ORDER BY store_id;

selects store_id,rental_date as the first rental date for each store and sort based on store_id.

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

SELECT DISTINCT rating FROM film ORDER BY rating;

select all unique ratings from film table in alphabetical order.

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

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

here the query order the film by rating in ascending order and length in descending.

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;

select first_name and last_name from table actor and Sort last_name by ascending, and reverse order of first_name.

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;

select columns from table film ordered by replacement_cost in ascending order and rental_rate in descending order.

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 ;

select columns from table Customers sorted by last_name ascending and reverse_first name.

List all rentals sorted by customer_id ascending and rental_date descending.

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

select columns from table rental sorted by customer_id ascending and rental_date descending.

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

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

Basic Select SQL Queries

Anjana R.K. — Wed, 25 Mar 2026 17:29:09 +0000

1.Query all columns for a city in CITY with the ID 1661
here we need to select all columns in city where ID value is equal to 1661.
so

SELECT * FROM CITY WHERE ID = 1661;

2.Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.
we to select all columns from cities where countrycode is USA and population greater than 100000.so,

SELECT * FROM CITY WHERE COUNTRYCODE = 'USA' AND POPULATION > 100000;

3.Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.
select all columns from city where countrycode is JPN.

SELECT * FROM CITY WHERE COUNTRYCODE = 'JPN';

4.Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
select the difference between the cities and distinct cities based on names from city table.

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

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

The query selects the NAME from the CITY table for all cities where the COUNTRYCODE is USA and the population is greater than 120000.

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'USA' AND POPULATION > 120000;

6.Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.
The query selects the NAME field from the CITY table for all cities where the COUNTRYCODE is 'JPN'.

SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN';

7.Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.
The query selects the distinct CITY names from the STATION table where the city names do not start with any vowels (A, E, I, O, U).

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

8.Query all columns (attributes) for every row in the CITY table.
The query selects all columns from the CITY table for every row.

SELECT * FROM CITY;

9.Query a list of CITY and STATE from the STATION table.
this query is to select city and state columns form the station table.

SELECT CITY, STATE FROM STATION;

Setup a DNS hosted zone in Route53 in AWS.

Anjana R.K. — Wed, 25 Mar 2026 16:08:08 +0000

Hi everyone!
Here lets understand how to Setup a DNS hosted zone in Route53 in AWS.

To setup a DNS hosted zone,

In the aws console,search for Rout53.
Then in route53, click hosted zone.
After that click Create Hosted Zone and entered the domain name.
Then for type selected Public Hosted Zone.
After that click create hosted zone.
If you already bought a domain,follow the steps, Copy the NS values given by AWS.
Go back to my domain provider account and updated the nameservers with the AWS nameservers
Next to create record in AWS Rout53,click Create Record.
Enter the record name as www,then selected record type as A.
Give value as the public IP address of EC2 instance.
Then click Create Record.

Finally, I opened the browser and typed the domain name, and the website was displayed.