SELECT QUERIES FROM DVD RENTAL DATABASE


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;

since, 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;

since, sorting is done first by last_name and then by first_name.

QN: 5

Unique replacement costs.

ANS: SELECT DISTINCT replacement_cost FROM film;

since, 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;

since, we 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;

since, we take name and active column.. and rename active.

QN: 8

Film categories sorted.

ANS: SELECT name FROM category ORDER BY name;

since, we select category names and sort alphabetically.

QN: 9

Films by length descending.

ANS: SELECT title, length FROM film ORDER BY length DESC;

since, 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;

since, sorting is based on first_name in reverse.

QN: 11

Unique ratings.

ANS: SELECT DISTINCT rating FROM film;

since, only unique ratings are needed.

QN: 12

Unique rental durations.

ANS: SELECT DISTINCT rental_duration FROM film;

since, 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;

since, 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;

since, 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;

since, 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;

since, highest customer_id will come first.

QN: 17

Unique store ids.

ANS: SELECT DISTINCT store_id FROM inventory;

since, DISTINCT removes duplicates.

QN: 18

Unique replacement cost sorted.

ANS: SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;

since, 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;

since, we join tables and get earliest rental date per store.

QN: 20

Unique ratings sorted.

ANS: SELECT DISTINCT rating FROM film ORDER BY rating;

since, ratings are sorted alphabetically.

QN: 21

Films by rating and length.

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

since, 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;

since, 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;

since, first replacement_cost is sorted.. then rental_rate.

QN: 24

Customers sorted.

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

since, last_name ascending and first_name descending.

QN: 25

Rentals sorted.

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

since, 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;

since, duration is sorted first.. then title in reverse order.

 

Comments

Post a Comment

Popular posts from this blog

THE DELIVERY MAN

  The Delivery Man – Python List Operations  Introduction Lists in Python are used to store multiple items in a single variable. They are flexible, allowing us to add, remove, update, and manipulate elements easily. In this task, different list operations are performed to simulate a delivery man's workflow. 1. Creating a list and accessing an item items = ["Notebook", "Pencil", "Eraser", "Ruler", "Marker"] print(items[2]) Approach: Indexing Why: Lists use zero-based indexing, so the third item is accessed using index 2. 2. Adding a new item items.append("Glue Stick") print(items) Approach: append() method Why: It adds an item at the end of the list, which is ideal for adding new deliveries. 3. Inserting an item items.insert(2, "Highlighter") print(items) Approach: insert() method Why: Used when we need to add an item at a specific position instead of the end. 4. Removing an item items.remove("Ruler...
Read more

EC2 LAUNCHING

  EC2 Step 1: Launch EC2 Instance Go to: AWS Console --> EC2 --> Launch Instance Configure: Name : my-web-server AMI : Amazon Linux 2 (easy for beginners) Instance Type : t2.micro (free tier) Step 2: Key Pair Create or select a key pair. Step 3: Network Settings  Allow these: SSH (port 22) --> for login  HTTP (port 80) --> for web access Step 4: Launch Instance Click Launch Instance Step 5: Connect to Instance Use: EC2 --> Connect --> EC2 Instance Connect (easiest) OR via terminal: with this command. for this you have to download the pem files while selecting the key pair.  ssh -i your-key.pem ec2-user@<public-ip> Step 6: Install Web Server For Amazon Linux: sudo yum update -y sudo yum install httpd -y Step 7: Start Web Server sudo systemctl start httpd sudo systemctl enable httpd Step 8: Add a Web Page echo "Hello from EC2" | sudo tee /var/www/html/index.html Step 9: Get Public IP Go to EC2 d...
Read more

SORT A LINKED LIST USING MERGE SORT

  Sort a Linked List using Merge Sort Introduction Sorting a linked list efficiently is a common problem in data structures. Unlike arrays, linked lists do not allow direct access to elements, so algorithms like quicksort are not ideal. Merge Sort is the best choice for linked lists because: It does not require random access It works efficiently with node-based structures Problem Statement Given the head of a linked list: Sort the list in ascending order Return the sorted linked list Example Input: 10 -> 30 -> 20 -> 40 -> 60 -> 50 Output: 10 -> 20 -> 30 -> 40 -> 50 -> 60 Solution Code class Solution: def mergeSort(self, head): if not head or not head.next: return head mid = self.getMiddle(head) next_to_mid = mid.next mid.next = None left = self.mergeSort(head) right = self.mergeSort(next_to_mid) return self.merge(left, right) def getMiddle(self,...
Read more