DEV Community: Ashiq Omar

SELECT QUERIES FROM DVD RENTAL DATABASE

Ashiq Omar — Sun, 29 Mar 2026 06:09:28 +0000

Let me explain it with some "DVD Rental DB" qns

QN: 1

Retrieve film titles and their rental rates.
ANS: SELECT title AS "Movie Title", rental_rate AS "Rate" FROM film;
it is to select title and rental_rate from table film and we are renaming them as Movie Title and Rate using alias.

QN: 2

List customer names and their email addresses.
ANS: SELECT first_name AS "First Name", last_name AS "Last Name", email FROM customer;
since, we need first name, last name and email from customer table and we rename first_name and last_name.

QN: 3

Sort films by rental rate descending and title.
ANS: SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC;
since, it is to sort rental_rate in descending if same means then title will be sorted alphabetically.

QN: 4

Actor names sorted by last name then first name.
ANS: SELECT first_name, last_name FROM actor ORDER BY last_name, first_name;
sorting is done first by last_name and then by first_name.

QN: 5

Unique replacement costs.
ANS: SELECT DISTINCT replacement_cost FROM film;
it is to get only unique values so we use DISTINCT.

QN: 6

Film title and length.
ANS: SELECT title, length AS "Duration (min)" FROM film;
select title and length and rename length as Duration.

QN: 7

Customer with active status.
ANS: SELECT first_name, last_name, active AS "Is Active" FROM customer;
we take name and active column and rename active.

QN: 8

Film categories sorted.
ANS: SELECT name FROM category ORDER BY name;
we select category names and sort alphabetically.

QN: 9

Films by length descending
ANS: SELECT title, length FROM film ORDER BY length DESC;
sorting is from highest length to lowest.

QN: 10

Actors by first name descending.
ANS: SELECT first_name, last_name FROM actor ORDER BY first_name DESC;
sorting is based on first_name in reverse.

QN: 11

Unique ratings.

ANS: SELECT DISTINCT rating FROM film;only unique ratings are needed.

QN: 12

Unique rental durations.
ANS: SELECT DISTINCT rental_duration FROM film;
we need only distinct rental_duration.

QN: 13

First customer_id based on active.
ANS: SELECT DISTINCT ON (active) customer_id, active FROM customer ORDER BY active, customer_id;
for each active value we take first customer_id.

QN: 14

Earliest rental date per customer.
ANS: SELECT customer_id, MIN(rental_date) FROM rental GROUP BY customer_id ORDER BY customer_id;
MIN is used to find earliest date for each customer.

QN: 15

10 shortest films.
ANS: SELECT title, length FROM film ORDER BY length ASC LIMIT 10;
sorting is from smallest length and limiting to 10.

QN: 16

Top 5 customers with highest id.
ANS: SELECT first_name, last_name, customer_id FROM customer ORDER BY customer_id DESC LIMIT 5;
highest customer_id will come first.

QN: 17

Unique store ids.
ANS: SELECT DISTINCT store_id FROM inventory;
DISTINCT removes duplicates.

QN: 18

Unique replacement cost sorted.
ANS: SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;
values are sorted from lowest to highest.

QN: 19

First rental date per store.
ANS: SELECT i.store_id, MIN(r.rental_date) FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id GROUP BY i.store_id ORDER BY i.store_id;
we join tables and get earliest rental date per store.

QN: 20

Unique ratings sorted.
ANS: SELECT DISTINCT rating FROM film ORDER BY rating;
ratings are sorted alphabetically.

QN: 21

Films by rating and length.
ANS: SELECT title, rating, length FROM film ORDER BY rating ASC, length DESC;
first rating is sorted then length is sorted.

QN: 22

Actors sorted last and first name.
ANS: SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;
last_name is ascending and first_name is descending.

QN: 23

Films by replacement and rate.
ANS: SELECT title, replacement_cost, rental_rate FROM film ORDER BY replacement_cost ASC, rental_rate DESC;
first replacement_cost is sortedthen rental_rate.

QN: 24

Customers sorted.
ANS: SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;
last_name ascending and first_name descending.

QN: 25

Rentals sorted.
ANS: SELECT * FROM rental ORDER BY customer_id ASC, rental_date DESC;
sorted by customer_id and latest rental first.

QN: 26

Films by duration and title.
ANS: SELECT title, rental_duration FROM film ORDER BY rental_duration ASC, title DESC;
duration is sorted first then title in reverse order.

HOW A REQUEST ORGINATES FROM CLIENT AND REACHES THE SERVER?

Ashiq Omar — Sun, 29 Mar 2026 06:00:24 +0000

How a Request Travels from Client to Server

A client is simply the end user who makes a request, usually by typing something like a website name into a browser. This request is in a human-readable format. computers dont understand domain names directly the system needs a way to convert this into a machine-readable format. Thats where the Domain Name System comes in.

What Happens Behind the Scenes

When a user enters a domain like www.google.com the request is first handled by DNS
DNS translates the domain name into an IP address which is what servers actually understand.
Once the correct IP address is found, the request is sent to the appropriate server where the required data is stored.
After processing the request the server sends the response back. Even though the communication happens using IP addresses the browser presents everything in a human-readable format.

Example
Request:
www.google.com
DNS converts it into an IP address like:
www.google.com
→ 126.xxx.xxx.0
This IP address helps identify the exact server where the website is hosted.

Role of Protocols and Data Transfer

When the request is sent, it uses protocols like HTTP or HTTPS. These protocols define how data is transferred between the client and the server.
The request includes important details like:
Source IP address
Destination IP address
The data is then broken into smaller packets. These packets travel through multiple routers across the internet.
Routers make sure the packets reach the correct destination without data loss.
Once the server receives the request, it processes it and sends the response back in the same way through the internet and routers, until it reaches the users browser.

Work Flow:

DNS RESOLVER

Ashiq Omar — Sun, 29 Mar 2026 05:53:08 +0000

A DNS resolver works like a cache memory that helps the operating system find the IP address of a domain name by communicating with different DNS servers.

How the Process Works

Let’s say you enter a domain like example.com.
First the request goes from the client to the operating system.
The operating system then asks the DNS resolver if it already knows the IP address for that domain.
If the resolver has the answer stored in its cache, it immediately returns the IP address. This makes the process very fast.
If the resolver does not have the IP address, it starts querying other DNS servers step by step.
It begins with the root server, then moves to the top-level domain server, and finally reaches the authoritative DNS server, which holds the actual IP address.
Once the resolver gets the IP address, it stores it in its cache for future use and then sends it back to the operating system.

What Happens Next Time

When the same request is made again, the operating system first checks with the resolver.
If the resolver already has the IP in its cache, it returns the result instantly without going through the full process again.
This reduces the time taken and improves performance.

Workflow Summary

EC2 LAUNCHING

Ashiq Omar — Sun, 29 Mar 2026 05:46:26 +0000

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

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

Name: my-web-server
AMI: Amazon Linux 2, which is beginner-friendly
Instance Type: t2.micro since it comes under the free tier

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

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

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

Step 5: Connect to the Instance
There are two easy ways to connect:

First option:
Use EC2 Instance Connect directly from the AWS Console. This is the easiest method.

Second option:
Use your terminal with the key pair file you downloaded:

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

Step 6: Install a Web Server
Once connected, install Apache on your instance:

sudo yum update -y
sudo yum install httpd -y

Step 7: Start the Web Server
Start the Apache service and enable it so it runs automatically:

sudo systemctl start httpd
sudo systemctl enable httpd

Step 8: Create a Simple Web Page
Now add a basic web page:

echo "Hello from EC2" | sudo tee /var/www/html/index.html

Step 9: Get the Public IP
Go to the EC2 dashboard and copy the public IP address of your instance.

Step 10: Access from Your Browser
Open your browser and enter:

http://<your-public-ip>

If everything is set up correctly you will see:
Hello from EC2

Setup a DNS hosted zone in Route53 in AWS.

Ashiq Omar — Sun, 29 Mar 2026 05:32:55 +0000

Let’s walk through how DNS works in a simple and practical way using AWS.

Step 1: Open Route 53

Start by logging into your AWS Console search for Amazon Route 53 and open it.
Once inside, go to Hosted Zones This is where your domain DNS settings are managed.

Step 2: Create a Hosted Zone

Click on Create Hosted Zone.
Now enter the details:
Domain Name:
Enter your domain name like yourdomain.com. It can be a real domain or just for practice.

Type:
Choose Public Hosted Zone so your domain can be accessed over the internet.
Then click Create Hosted Zone.

Step 3: Understand the Default Records

After creating the hosted zone, AWS automatically creates two records:
NS Record:
These are AWS DNS servers that handle requests for your domain.
SOA Record:
This contains technical and administrative details about your domain.
You don’t need to change these records.

Step 4: Create an A Record

Now you need to connect your domain to your server.
Click Create Record and fill in

Record Name:
You can use www or leave it empty.
Record Type:
A
Value:
Enter your EC2 instance’s public IP address.
Click Create Record.

Step 5: Connect Your Domain to AWS

If you bought your domain from providers like GoDaddy or Namecheap you need to update the nameservers.
Go to your domain provider’s settings and replace their nameservers with the ones provided by Amazon Route 53.

They will look like:
ns-123.awsdns-45.com
ns-678.awsdns-12.net
This step ensures that your domain uses AWS for DNS resolution.

Step 6: Test in Browser
Open your browser and visit:
http://www.yourdomain.com
If everything is set up correctly your EC2-hosted website will load.

How the Flow Works
When a user enters your domain in the browser this is what happens:

User sends a DNS request
Route 53 receives the request
It checks the A record
Returns the EC2 IP address
Browser loads the website

Basic Select SQL Queries

Ashiq Omar — Sun, 29 Mar 2026 05:30:09 +0000

Let me explain it with some Hackerank problems:

QN: 1
Query all columns for a city in CITY with the ID 1661
ANS: SELECT * from CITY where ID = 1661;

it is to select all columns from table city and the condition is for ID = 1661.

QN: 2
Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.
ANS: SELECT * from CITY where COUNTRYCODE= 'JPN';

it is to select all attributes from the table city and the condition is for a COUNTRYCODE =JPN

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

ANS: SELECT NAME from CITY where POPULATION > 120000 and COUNTRYCODE = 'USA';

since it is to select all the names from the table city condition is population is more than 120000 and CountryCode =USA

QN:4

Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.
ANS: 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%";

Since, it is to select the unique city names from the table STATION and the condition is to the names of the city should not start with vowels(a,e,i,o,u). A%,E%,I%,O%,U% is that all other character after that are considered.so this represents the first letter.

QN:5

Query a list of CITY and STATE from the STATION table.
ANS: SELECT CITY,STATE FROM STATION;

we need both city and state columns from table STATION we select both table

QN:6

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

ANS: SELECT * FROM CITY WHERE POPULATION >100000 AND COUNTRYCODE = 'USA';
Since, it is to select all from table CITY . the condition is where population is more than 100000 and COUNTRYCODE IS USA

QN:7

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
ANS: SELECT COUNT(CITY)-COUNT(DISTINCT CITY) from STATION;

here it is asked for the difference between all city and unique city names to find the repetition we use COUNT keyword to city and distinct city .

QN:8
Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN
ANS: SELECT NAME FROM CITY WHERE COUNTRYCODE = 'JPN';

it is to select the names of the city from table CITY and the condition is COUNTRYCODE to be JPN

DB-TASK-002

Ashiq Omar — Sun, 29 Mar 2026 05:21:05 +0000

Let me explain it with some "DVD Rental DataBase" qns

QN: 1

Get all movies with rental rate greater than 3.


ANS: SELECT * FROM film WHERE rental_rate > 3;

it is to select all columns from film table and condition is rental_rate greater than 3.

QN: 2

Movies with rental rate > 3 and replacement cost < 20.


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

two conditions are given so we use AND to combine both.

QN: 3

Movies rated PG or rental rate 0.99.


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

either one condition can be true so we use OR.

QN: 4

First 10 movies sorted by rental rate (highest first).


ANS: SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;

we sort rental_rate in descending and take first 10.

QN: 5

Skip first 5 and get next 3 (ascending).


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

OFFSET skips first 5 rows then LIMIT gives next 3.

QN: 6

Skip first 5 and get next 3 (ascending).


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

same question so same query.

QN: 7

Movies with rental duration between 3 and 7.


ANS: SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;

BETWEEN is used for range values.

QN: 8

Title starts with A and ends with e.


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

A% means starts with A and %e means ends with e.

QN: 9

Customers without email.


ANS: SELECT * FROM customer WHERE email IS NULL;

NULL is checked using IS NULL.

QN: 10

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


ANS:
SELECT title, rental_rate, release_year
FROM film
WHERE release_year = 2006
AND (rental_rate = 2.99 OR rental_rate = 3.99)
AND title LIKE 'S%'
LIMIT 5;

multiple conditions are combined and we limit to top 5.

QN: 11

Skip 20 customers and show next 10 sorted by last name.


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

`
sorted alphabetically then skip 20 and take next 10.

QN: 12

Top 5 movies with highest replacement cost, skip most expensive one.
`sql

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

`
highest is skipped using OFFSET 1 then next 5 are taken.

QN: 13

Rentals between two dates.
`sql

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

`
BETWEEN is used for date range.

QN: 14

Actors with "man" in last name.
`sql

ANS: SELECT * FROM actor WHERE last_name LIKE '%man%';

`
%man% means anywhere in the name.

QN: 15

Movies with no special features.
`sql

ANS: SELECT * FROM film WHERE special_features IS NULL;

NULL values are checked using IS NULL.

QN: 16

Movies with rental duration more than 7.
`sql

ANS: SELECT * FROM film WHERE rental_duration > 7;

condition is greater than 7.

QN: 17

First 10 movies with rental rate 2.99 or 4.99, rating R, title contains L.
`sql

ANS:
SELECT * FROM film
WHERE (rental_rate = 2.99 OR rental_rate = 4.99)
AND rating = 'R'
AND title LIKE '%L%'
LIMIT 10;

`
multiple conditions and title contains L.

QN: 18

Title starts with A or B and ends with s.
`sql

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

`
starts with A or B ends with s.

QN: 19

Title contains Man, Men or Woman.
`sql

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

`
checking multiple words using LIKE.

CREATE TABLES

Ashiq Omar — Sun, 29 Mar 2026 05:14:05 +0000

Tables with constraints

QN:1

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

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

id should uniquely identify each student we use PRIMARY KEY

QN:2

Create a table employees where name and email cannot be empty, but phone_number can be optional.

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

name and email should not be empty we use NOT NULL phone is optional so no constraint

QN:3

Create a table users where both username and email must be unique across all records.

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

username and email should not repeat we use UNIQUE

QN:4

Create a table products where price must always be greater than 0 and stock cannot be negative.

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

price should be greater than 0 and stock should not be negative we use CHECK

QN: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.

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

we need default values we use DEFAULT for status and timestamp

QN:6

Create a table accounts where account_number must be unique and not null and balance must always be greater than or equal to 0

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

account_number should not be empty and must be unique and balance should not go negative

QN: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.

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

same student should not enroll same course again so we use combination UNIQUE

QN:8

Create two tables: departments with id and name and employees with id, name, and department_id. Ensure that department_id in employees must exist in departments.

Query:
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)
);

employee must belong to valid department so we use FOREIGN KEY

QN:9

Modify the previous foreign key example so that when a department is deleted, all related employees are also deleted and when a department ID is updated, it reflects in the employees table

Query:
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
);

we need automatic delete and update so we use CASCADE

Alter Tables

Ashiq Omar — Sun, 29 Mar 2026 05:07:40 +0000

ALTER TABLE constraints

QN: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.

Query:
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

email should not be empty for future records we use SET NOT NULL

QN:2

In the users table, ensure that the username column is unique across all records using an ALTER statement.

Query:
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

username should not repeat we add UNIQUE constraint

QN:3

In the products table, enforce that price must always be greater than 0 using an ALTER command.

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

price should always be greater than 0 we use CHECK constraint

QN:4

Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.

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

if no value is given status should automatically become pending

QN: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

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

salary should not be empty and must be above 10000 we use NOT NULL and CHECK

QN:6

Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.

Query:
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;

existing foreign key cannot be modified directly we drop and recreate with CASCADE

QN:7

In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.

Query:
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

we want to remove condition on balance we drop CHECK constraint

QN:8

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

Query:
ALTER TABLE payments
ADD CONSTRAINT unique_payment UNIQUE (user_id, transaction_id);

same user should not have duplicate transaction_id we use combination UNIQUE

DB-TASK-002

Ashiq Omar — Sat, 28 Mar 2026 15:31:24 +0000

Let me explain it with some "DVD Rental DataBase" qns

QN: 1
Get all movies with rental rate greater than 3.


SELECT * FROM film WHERE rental_rate > 3

it is to select all columns from film table and condition is rental_rate greater than 3

QN: 2
Movies with rental rate > 3 and replacement cost < 20.


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

two conditions are given so we use AND to combine both.

QN: 3
Movies rated PG or rental rate 0.99.


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

either one condition can be true so we use OR.

QN: 4
First 10 movies sorted by rental rate (highest first).

ANS: SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;

since, we sort rental_rate in descending and take first 10.

QN: 5
Skip first 5 and get next 3 (ascending).


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

OFFSET skips first 5 rows then LIMIT gives next 3.

QN: 6
Skip first 5 and get next 3 (ascending).


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

same question so same query.

QN: 7
Movies with rental duration between 3 and 7.


ANS: SELECT * FROM film WHERE rental_duration BETWEEN 3 AND 7;

BETWEEN is used for range values.

QN: 8
Title starts with A and ends with e.


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

A% means starts with A and %e means ends with e.

QN: 9
Customers without email.


ANS: SELECT * FROM customer WHERE email IS NULL;

NULL is checked using IS NULL.

QN: 10
Movies in 2006, rental rate 2.99 or 3.99, title starts with S.


ANS:
SELECT title, rental_rate, release_year
FROM film
WHERE release_year = 2006
AND (rental_rate = 2.99 OR rental_rate = 3.99)
AND title LIKE 'S%'
LIMIT 5;

multiple conditions are combined and we limit to top 5.

QN: 11
Skip 20 customers and show next 10 sorted by last name.


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

sorted alphabetically then skip 20 and take next 10.

QN: 12
Top 5 movies with highest replacement cost, skip most expensive one.


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

highest is skipped using OFFSET 1 then next 5 are taken.

QN: 13
Rentals between two dates.


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

BETWEEN is used for date range.

QN: 14
Actors with "man" in last name.


ANS: SELECT * FROM actor WHERE last_name LIKE '%man%';

%man% means anywhere in the name.

QN: 15
Movies with no special features.


ANS: SELECT * FROM film WHERE special_features IS NULL;

NULL values are checked using IS NULL.

QN: 16
Movies with rental duration more than 7.

ANS: SELECT * FROM film WHERE rental_duration > 7;

condition is greater than 7.

QN: 17
First 10 movies with rental rate 2.99 or 4.99, rating R, title contains L.


ANS:
SELECT * FROM film
WHERE (rental_rate = 2.99 OR rental_rate = 4.99)
AND rating = 'R'
AND title LIKE '%L%'
LIMIT 10;

multiple conditions and title contains L.

QN: 18
Title starts with A or B and ends with s.


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

starts with A or B ends with s.

QN: 19
Title contains Man, Men or Woman.


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

checking multiple words using LIKE.

Users, Roles, Groups

Ashiq Omar — Sat, 28 Mar 2026 15:18:03 +0000

Lets understand roles and permissions with a few practical questions.

QN 1: Create a login role that can only read from film table

CREATE ROLE report_user LOGIN;
GRANT SELECT ON film TO report_user;

we create a user called report_user and give it only SELECT permission on the film table. So it can read data but cant modify anything.

2: Accessing customer table gives permission denied fix it

GRANT SELECT ON customer TO report_user;

The error happens because report_user doesnt have access to the customer table. Granting SELECT fixes the issue.

3: Allow access to only specific columns

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

we remove full access.
Then we grant access only to selected columns. Now the user can’t see sensitive fields like email or other data.

4: Create a support user with limited permissions

CREATE ROLE support_user LOGIN;
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;

This user can: Read all customer data update only the email column no delete or full update access is given tight control.

5: Remove access from film table

REVOKE SELECT ON film FROM report_user;

This simply removes the permission we granted earlier.

6: Create a read-only group

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

This creates a group role that can read all tables in the schema.

7: Add users to the group

CREATE ROLE analyst1 LOGIN;
CREATE ROLE analyst2 LOGIN;
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Both users inherit permissions from the group so instead of managing permissions individually we manage them centrally.

Consistency

Ashiq Omar — Sat, 28 Mar 2026 15:03:15 +0000

Let’s understand how a database keeps data valid using an accounts table.
Assume the table has a rule like:
balance ≥ 0
This is a constraint which means the database wont allow any negative balance.

Initial data

Alice = 1000
Bob = 500

Trying to break the rule now suppose we try to deduct more money than Alice actually has

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

Alice only has 1000 so this would make her balance -1000 which violates the constraint.
Result:
The database throws an error and rejects the update.

Directly setting a negative value

UPDATE accounts
SET balance = -500
WHERE name = 'Bob';

Again this violates the rule.
Result:
The database blocks the query with an error.

Checking the data again
SELECT * FROM accounts;
Alice = 1000
Bob = 500
invalid operations were rejected the data remains unchanged.

Inside a transaction
Now lets try the same invalid update within a transaction:

BEGIN;

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

COMMIT;

the constraint fails the transaction itself fails and does not complete.

Result:

The database uses constraints like CHECK (balance >= 0) to automatically prevent invalid data, such as negative balances.
This is schema-level protection.
But checking whether a user has enough balance before attempting a transfer is still the responsibility of the application or transaction logic.

Big picture
Database - prevents invalid data
Application - handles business rules

Because of this Balances never go negative data stays consistent and the system remains reliable