DEV Community: Sandhya Steffy M

ALTER TABLE

Sandhya Steffy M — Thu, 26 Mar 2026 16:36:20 +0000

Today I learned that creating a table is only the first step in SQL. Sometimes, after creating a table, we may realize that we need to change some rules. For example, maybe a column should not allow empty values, or maybe we want to add a new constraint later.
For this, SQL gives us an important command called ALTER TABLE.

ALTER TABLE is used when we want to change the structure of an existing table without deleting it and creating it again.

In this blog, I am going to explain different ALTER TABLE tasks in a simple way.

Making email compulsory in the customers table Suppose we already have a table called customers, and its email column currently allows NULL values. But now we want all future records to обязательно have an email value.

For that, we can modify the column using:

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
What this does:
This command makes the email field compulsory from now on.
So, while inserting new records, we cannot leave the email empty.

Making username unique in the users table In many applications, usernames should be unique. Two users should not have the same username. If the table is already created, we can add this rule later using ALTER TABLE.

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
What this does:
This adds a UNIQUE constraint on the username column.
That means duplicate usernames are not allowed.

Ensuring product price is always greater than 0 In the products table, price should never be zero or negative. This can be enforced using a CHECK constraint.

ALTER TABLE products
ADD CONSTRAINT check_price_positive CHECK (price > 0);
What this does:
This makes sure the price value is always greater than 0.
So invalid prices cannot be inserted.

Setting default status in orders table Sometimes when we insert a new order, we may forget to mention the order status. In such cases, it is useful to automatically set a default value like 'pending'.

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
What this does:
If no status is given during insertion, SQL will automatically store 'pending'.

Adding a salary column in employees table Now let us say we want to add a new column called salary in the employees table. The condition is:

salary should not be NULL
salary should always be greater than 10,000 This can be done like this:

ALTER TABLE employees
ADD COLUMN salary DECIMAL(10,2) NOT NULL CHECK (salary > 10000);
What this does:

This creates a new column called salary and applies two rules:
it must have a value
the value must be above 10,000
Changing foreign key so deleting a department also deletes employees Suppose the employees table is connected to the departments table using a foreign key. Now we want this behavior:

If a department is deleted, all employees belonging to that department should also be deleted automatically.

Usually, first we remove the old foreign key constraint, then add a new one with ON DELETE 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;
What this does:
If a department row is deleted, all matching employee rows are also deleted automatically.

Removing an existing CHECK constraint from accounts table In the accounts table, assume there is already a CHECK constraint that ensures balance >= 0. If we want to remove that rule, we need to drop the constraint.

Example:

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;
What this does:
This removes the check condition from the balance column.

Making combination of user_id and transaction_id unique in payments table In the payments table, one user_id and transaction_id combination should not repeat. This means the pair together must be unique.

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

What this does:
This prevents duplicate entries with the same user_id and transaction_id combination.

Create Tables

Sandhya Steffy M — Thu, 26 Mar 2026 16:32:48 +0000

Today I learned how to create tables in SQL and how to apply different constraints. At first, it felt confusing, but when I understood the purpose behind each rule, it became much easier.

Let me explain each task in a simple way.

Creating a Students Table First, I created a table called students. Each student should have an id, name, and age. The important thing here is that id must be unique, so we use a PRIMARY KEY.

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);
Here, PRIMARY KEY ensures that no two students will have the same id.

Employees Table with Required Fields Next, I created an employees table. In this case, name and email should not be empty, but phone number is optional.

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
phone_number VARCHAR(15)
);
NOT NULL means the user must provide a value.

Users Table with Unique Values In this task, both username and email must be unique. This is useful in real systems like login systems.

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
Here, UNIQUE prevents duplicate entries.

Products Table with Conditions For products, I learned how to restrict values using CHECK constraints.

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) CHECK (price > 0),
stock INT CHECK (stock >= 0)
);
This ensures:

Price is always positive
Stock is never negative

Orders Table with Default Values Sometimes, we want default values. For example, if no status is given, it should be pending.

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This automatically sets:

status → pending
created_at → current time
Accounts Table with Rules In banking-like systems, we must be very careful with data.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
account_number VARCHAR(20) UNIQUE NOT NULL,
balance DECIMAL(10,2) CHECK (balance >= 0)
);
This ensures:

account number is always unique
balance never goes below zero
Enrollments Table (Combination Unique) Here I learned something interesting. A student can take many courses, but the same student cannot enroll in the same course twice.

CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
This is called a composite key.

Foreign Key Relationship (Departments & Employees) Now I created two tables where one depends on another.

CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

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

This ensures that an employee can only belong to an existing department.
Cascade Delete and Update Finally, I improved the previous example by adding automatic updates.

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
This means:

If a department is deleted → its employees are also deleted
If department id changes → it updates automatically

Idempotency Situation

Sandhya Steffy M — Wed, 25 Mar 2026 13:39:30 +0000

In this task, I understood the concept of idempotency in a wallet system. In real applications like GPay or PhonePe, sometimes the same request may be sent multiple times due to network issues or retries. The system should ensure that the same transaction is not executed more than once.

First, I created 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
);

Then I inserted sample data.

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

To check initial balances:

SELECT * FROM accounts;

Alice had 1000 and Bob had 500.

Next, I performed a transfer of 200 from Alice to Bob.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;
After this, Alice had 800 and Bob had 700.

Then I executed the same transaction again.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;

Now Alice had 600 and Bob had 900.

This shows that the same transaction was applied again, which means the system is not idempotent. The database does not automatically prevent duplicate execution.

To solve this, real systems use a unique transaction ID.

CREATE TABLE wallet_transactions (
txn_id TEXT PRIMARY KEY,
sender_id INT,
receiver_id INT,
amount INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert a transaction:

INSERT INTO wallet_transactions (txn_id, sender_id, receiver_id, amount)
VALUES ('TXN1001', 1, 2, 200);

If the same request is sent again:

INSERT INTO wallet_transactions (txn_id, sender_id, receiver_id, amount)
VALUES ('TXN1001', 1, 2, 200);
PostgreSQL will give an error because the transaction ID already exists. This prevents duplicate processing.

Durability in Wallet Transfer System

Sandhya Steffy M — Wed, 25 Mar 2026 13:29:34 +0000

In this task, I understood the Durability property of ACID using a wallet system. Durability means that once a transaction is committed, the data will be permanently stored in the database and will not be lost even if the system crashes or restarts.

First, I created 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
);
Then I inserted sample data.

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
To check the initial data:

SELECT * FROM accounts;
Alice had 1000 and Bob had 500.

Next, I performed a successful transaction to transfer 300 from Alice to Bob.

BEGIN;

UPDATE accounts
SET balance = balance - 300
WHERE id = 1;

UPDATE accounts
SET balance = balance + 300
WHERE id = 2;

COMMIT;
After committing, I checked the data again.

SELECT * FROM accounts;
Now Alice had 700 and Bob had 800.

Then I simulated a system restart by reconnecting to the database and checked the data again.

SELECT * FROM accounts;
The values remained the same, which shows that the committed transaction was permanently saved.

If a failure happens before COMMIT, the changes are not saved.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

-- crash before COMMIT
After restart, the balance remains unchanged.

If a failure happens after COMMIT, the data is already stored and will not be lost.

This happens because PostgreSQL uses Write-Ahead Logging (WAL), which ensures that committed data can be recovered even after a crash.

From this task, I understood that Durability ensures that once a transaction is completed, the data remains safe and permanent. This is very important in wallet systems to prevent loss of money or incorrect balances.

ISOLATION

Sandhya Steffy M — Wed, 25 Mar 2026 13:25:30 +0000

Isolation in Wallet Transfer System

In this task, I understood the Isolation property of ACID using a simple wallet system. In applications like GPay or PhonePe, many users perform transactions at the same time. Isolation ensures that one transaction does not affect another transaction in a wrong way.

I created a table called accounts to store user balances.

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

Then I inserted sample data.

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

To check initial data:

SELECT * FROM accounts;

At this stage, Alice had 1000 and Bob had 500.

Testing Isolation using two sessions
To understand isolation, I opened two query windows (Session 1 and Session 2).

Session 1
In the first session, I started a transaction and deducted money from Alice, but I did not commit.

BEGIN;

UPDATE accounts
SET balance = balance - 700
WHERE id = 1;

SELECT * FROM accounts WHERE id = 1;
Here, Session 1 shows Alice’s balance as 300.

Session 2
In another session, I checked Alice’s balance.

SELECT * FROM accounts WHERE id = 1;
It still showed 1000, not 300.

This means Session 2 cannot see the uncommitted changes of Session 1. This prevents dirty reads.

Trying concurrent update
In Session 2, I tried to update the same row.

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
This query did not execute immediately. It waited because Session 1 was already using that row.

Commit in Session 1
Now I committed the first transaction.

COMMIT;
After this, Session 2 continued execution. This shows that PostgreSQL uses locking to avoid conflicts.

Testing Isolation Levels
By default, PostgreSQL uses Read Committed isolation level.

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM accounts WHERE id = 1;
This level only shows committed data.

Repeatable Read
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM accounts WHERE id = 1;
In this level, the same query will give the same result within the transaction, even if another transaction changes the data.

Serializable
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
This is the highest level. It ensures transactions behave like they are executed one after another.

Consistency

Sandhya Steffy M — Wed, 25 Mar 2026 12:13:00 +0000

In this task, I explored the Consistency property of ACID using a simple wallet system. In payment applications like PhonePe, GPay, or Paytm, the data must always remain correct and valid. If consistency is not maintained, it may lead to negative balances, money loss, or incorrect account details.

Consistency means that the database should always move from one valid state to another valid state. It should never allow invalid data to be stored.

To understand this, I used the following 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
);
Here, the important part is:

CHECK (balance >= 0)
This rule ensures that no account balance can become negative.

Then I inserted sample data into the table.

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
To see the current data, I ran:

SELECT * FROM accounts;
At this stage, Alice had 1000 and Bob had 500.

Testing Consistency by violating the rule
First, I tried to directly make Alice’s balance negative.

UPDATE accounts
SET balance = -200
WHERE id = 1;
PostgreSQL did not allow this update. It gave an error because the CHECK (balance >= 0) constraint was violated.

This shows that the database itself is protecting the data and not allowing an invalid state.

Trying to deduct more money than available
Next, I tried to deduct more money than Alice actually had.

UPDATE accounts
SET balance = balance - 2000
WHERE id = 1;
Since Alice had only 1000, this operation would make her balance negative. Again, PostgreSQL rejected the update because of the check constraint.

Testing inside a transaction
I also tested the same case inside a transaction block.

BEGIN;

UPDATE accounts
SET balance = balance - 2000,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1;

UPDATE accounts
SET balance = balance + 2000,
last_updated = CURRENT_TIMESTAMP
WHERE id = 2;

COMMIT;
In this case, the first update itself failed because it would make Alice’s balance negative. Since the transaction failed, the database did not move to an invalid state.

When I checked the table again:

SELECT * FROM accounts;
the balances remained unchanged.

Handling consistency at application level
Even though the check constraint prevents negative balances, some rules should still be handled in the transaction logic or application code.

For example, before deducting money, we should verify that the sender has enough balance.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1
AND balance >= 200;

UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 2;

COMMIT;
Here, the condition:

AND balance >= 200
acts as a logical check before updating the account.

Atomicity - Design a Reliable Wallet Transfer System with ACID Guarantees

Sandhya Steffy M — Wed, 25 Mar 2026 12:05:27 +0000

In this task, I implemented a simple wallet transfer system to understand the Atomicity property of ACID. Atomicity means that a transaction should either complete fully or not happen at all. There should be no partial updates.

First, I created a table called accounts to store user details and balance.

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

Then I inserted some sample data.

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

To verify the initial state, I checked the table.

SELECT * FROM accounts;

At this point, Alice had 1000 and Bob had 500.

Next, I performed a successful money transfer of 200 from Alice to Bob using a transaction block.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 2;

COMMIT;

After executing this, I checked the table again.

SELECT * FROM accounts;

Now, Alice’s balance became 800 and Bob’s balance became 700. This shows that both debit and credit operations were completed successfully.

To test Atomicity, I reset the balances.

UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500 WHERE id = 2;

Then I introduced an error during the transaction.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1;

-- Intentional error
UPDATE accounts
SET balanc = balance + 200
WHERE id = 2;

COMMIT;

The second query fails because the column name is wrong. So the transaction is not completed.

Now I checked the table again.

SELECT * FROM accounts;

I observed that Alice’s balance was still 1000 and Bob’s balance was still 500. Even though the first update was executed, it was not saved.
This clearly shows that PostgreSQL does not allow partial updates. If any step fails, the entire transaction is rolled back.

Users, Roles, Groups

Sandhya Steffy M — Wed, 25 Mar 2026 11:25:28 +0000

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

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

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

GRANT SELECT ON customer TO report_user;

Task 3: Allow report_user to see only customer_id, first_name, last_name of the customer table
If full SELECT was already given in Task 2, first remove it, then give column-level permission:

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

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

CREATE ROLE support_user WITH LOGIN PASSWORD 'support123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;

DELETE permission is not given, so support_user cannot delete.

Task 5: Remove SELECT access on film from report_user

REVOKE SELECT ON film FROM report_user;

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;

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

CREATE ROLE analyst1 WITH LOGIN PASSWORD 'analyst123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'analyst123';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Move Zeros

Sandhya Steffy M — Wed, 25 Mar 2026 11:18:27 +0000

When I saw the Move Zeroes problem, it looked very easy, but it needs a small trick to solve it properly. The task is to move all the zero values in an array to the end, without changing the order of the other numbers.

For example, if the input is [0, 1, 0, 3, 12], the output should be [1, 3, 12, 0, 0]. Here, we are not sorting the array, just shifting zeroes to the end.

The idea is simple. Instead of thinking about zeroes, we focus on non-zero elements. We take a variable to track the position where the next non-zero value should be placed. Then we go through the array and place all non-zero elements in the front. After that, we fill the remaining positions with zero.

This method works in-place and does not use extra space. It is also efficient because it only scans the array.

def moveZeroes(nums):
index = 0
for i in range(len(nums)):
if nums[i] != 0:
nums[index] = nums[i]
index += 1
for i in range(index, len(nums)):
nums[i] = 0
return nums

This problem helped me understand how to handle arrays efficiently and think in a better way while solving problems.

Users, Roles, Groups

Sandhya Steffy M — Tue, 24 Mar 2026 17:13:44 +0000

Task 1:Create a login role report_user that can only read from the film table
CREATE ROLE report_user WITH LOGIN PASSWORD 'report123';

GRANT SELECT ON film TO report_user;
Here, we first create a new login role named report_user.
Then we give only SELECT permission on the film table, so this user can only read that table.

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 happens because report_user was not given permission on the customer table.
So we fix it by granting SELECT access on customer.

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;
If we want the user to see only some columns, full table access should be removed first.
Then column-level SELECT permission is given only for the required columns.

Task 4: Create support_user who can SELECT from customer, UPDATE only email column, and cannot DELETE
CREATE ROLE support_user WITH LOGIN PASSWORD 'support123';

GRANT SELECT ON customer TO support_user;

GRANT UPDATE (email) ON customer TO support_user;
This user should be able to read customer details and change only the email column.
Since we do not give DELETE permission, the user cannot delete any row.

Task 5: Remove SELECT access on film from report_user
REVOKE SELECT ON film FROM report_user;
Earlier, report_user was allowed to read from film.
Now this command removes that permission.

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;
Here, readonly_group acts like a common read-only role.
Anyone added to this group will get SELECT access on all tables in the public schema.

Task 7: Create analyst1 and analyst2 and add them to readonly_group
CREATE ROLE analyst1 WITH LOGIN PASSWORD 'analyst123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'analyst123';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;
First, we create two login users.
Then we add both of them to readonly_group,so they automatically get all read-only permissions of that group.

Bonus Q/A

Sandhya Steffy M — Tue, 24 Mar 2026 16:41:17 +0000

SELECT title, special_features
FROM film
WHERE special_features IS NULL;
This question is asking us to find movies where no special feature is given at all.
So instead of checking for empty text, we use IS NULL because NULL means “no value stored”.

SELECT title, rental_duration
FROM film
WHERE rental_duration > 7;
Here we only want movies whose rental duration is greater than 7 days.
So we simply filter the rows using the > operator.

SELECT title, rental_rate, replacement_cost
FROM film
WHERE rental_rate = 4.99
AND replacement_cost > 20;
This question has two conditions, and both must be true at the same time.
That is why we use AND to combine rental rate and replacement cost.

SELECT title, rental_rate, rating
FROM film
WHERE rental_rate = 0.99
OR rating = 'PG-13';
Here the movie can satisfy either one condition or the other.
So we use OR because even if one part matches, the row should come.

SELECT title
FROM film
ORDER BY title ASC
LIMIT 5;
First, the movies must be arranged in alphabetical order by title.
After sorting, LIMIT 5 gives only the first 5 rows.

SELECT title, replacement_cost
FROM film
ORDER BY replacement_cost DESC
OFFSET 10
LIMIT 3;
This question says to skip the first 10 rows, then take the next 3.
So we sort first, then use OFFSET 10 and LIMIT 3.

SELECT title, rating
FROM film
WHERE rating IN ('G', 'PG', 'PG-13');
Instead of writing many OR conditions, we can use IN.
It makes the query shorter and easier to read when checking multiple values.

SELECT title, rental_rate
FROM film
WHERE rental_rate BETWEEN 2 AND 4;
The word “between” clearly tells us to use the BETWEEN operator.
This checks whether the rental rate is inside the range from 2 to 4.

SELECT title
FROM film
WHERE title LIKE 'The%';
If the title starts with “The”, then “The” must come at the beginning.
So we use LIKE 'The%', where % means anything can come after it.

10.

SELECT title, rental_rate, rating
FROM film
WHERE rental_rate IN (2.99, 4.99)
AND rating = 'R'
AND title ILIKE '%Love%'
LIMIT 10;
This question combines many filters together, so all of them must match.
ILIKE is useful here because it checks for the word “Love” without worrying about uppercase or lowercase.

11.

SELECT title
FROM film
WHERE title LIKE '%\%%' ESCAPE '\';
Normally % is treated as a wildcard in LIKE, not as a real symbol.
So we use ESCAPE '\' to tell SQL that here % should be treated as an actual character.

12.

SELECT title
FROM film
WHERE title LIKE '%_%' ESCAPE '\';
The underscore _ is also a wildcard in LIKE, for one character.
So we escape it to search for a real underscore symbol inside the title.

13.

SELECT title
FROM film
WHERE (title LIKE 'A%s' OR title LIKE 'B%s');
The title must begin with either A or B, and it must end with s.
So we write two patterns and join them using OR.

14.

SELECT title
FROM film
WHERE title ILIKE '%Man%'
OR title ILIKE '%Men%'
OR title ILIKE '%Woman%';
This question wants titles containing any one of these words.
So we check each word separately and connect them using OR.

15.

SELECT title
FROM film
WHERE title ~ '[0-9]';
Here we need titles that contain numbers anywhere inside them.
The regex [0-9] means “any digit from 0 to 9”.

16.

SELECT title
FROM film
WHERE title LIKE '%\%' ESCAPE '\';
Since backslash is a special escape character, searching for it is a little tricky.
So we escape it properly to make SQL look for a real \ inside the title.

17.

SELECT title
FROM film
WHERE title ILIKE '%Love%'
OR title ILIKE '%Hate%';
This question asks for movies whose titles contain either “Love” or “Hate”.
So we search for both words and use OR because either one is enough.

18.

SELECT title
FROM film
WHERE title ILIKE '%er'
OR title ILIKE '%or'
OR title ILIKE '%ar'
LIMIT 5;
The question is asking for titles that end with specific letter combinations.
So we use patterns ending in er, or, or ar, and then take only the first 5 rows.

DB-TASK-001

Sandhya Steffy M — Tue, 24 Mar 2026 15:37:29 +0000

Retrieving Data with Column Aliases Sometimes, column names in tables are not user-friendly. So, we can rename them using aliases.

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

Displaying Customer Details We can also rename customer details to make them clear.

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

Sorting Data (ORDER BY) Sorting helps us organize data.

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

Sorting Actor Names
SELECT first_name, last_name
FROM actor
ORDER BY last_name, first_name;
This sorts actors by last name first.
Using DISTINCT (Unique Values)
To avoid duplicates, we use DISTINCT.

SELECT DISTINCT replacement_cost
FROM film;
This shows only unique values.

Showing Film Duration
SELECT title, length AS "Duration (min)"
FROM film;
Here, length is renamed to make it clearer.
Customer Active Status
SELECT first_name, last_name, active AS "Is Active"
FROM customer;
This shows whether a customer is active or not.
Film Categories Sorted
SELECT name
FROM category
ORDER BY name;
This displays categories alphabetically.
Sorting Films by Length
SELECT title, length
FROM film
ORDER BY length DESC;
This shows longest movies first.
Sorting Actors in Reverse Order
SELECT first_name, last_name
FROM actor
ORDER BY first_name DESC;
11 & 12. Unique Ratings and Rental Durations
SELECT DISTINCT rating FROM film;
SELECT DISTINCT rental_duration FROM film;
Unique Customer Based on Status
SELECT DISTINCT ON (active) customer_id, active
FROM customer
ORDER BY active, customer_id;
Earliest Rental Date for Each Customer
SELECT customer_id, MIN(rental_date) AS rental_date
FROM rental
GROUP BY customer_id
ORDER BY customer_id;
Shortest Films
SELECT title, length
FROM film
ORDER BY length ASC
LIMIT 10;
Top 5 Customers
SELECT first_name, last_name
FROM customer
ORDER BY customer_id DESC
LIMIT 5;
Unique Store IDs
SELECT DISTINCT store_id
FROM inventory;
Sorted Replacement Cost
SELECT DISTINCT replacement_cost
FROM film
ORDER BY replacement_cost;
First Rental Date Per Store
SELECT store_id, MIN(rental_date)
FROM rental
GROUP BY store_id
ORDER BY store_id;
Unique Ratings Sorted
SELECT DISTINCT rating
FROM film
ORDER BY rating;
21.

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

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

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

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

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

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