DB-TASK-002

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;

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

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

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

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

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

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

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

QN: 9

Customers without email.

ANS: SELECT * FROM customer WHERE email IS NULL;

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

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

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

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

since, BETWEEN is used for date range.

QN: 14

Actors with "man" in last name.

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

since, %man% means anywhere in the name.

QN: 15

Movies with no special features.

ANS: SELECT * FROM film WHERE special_features IS NULL;

since, NULL values are checked using IS NULL.

QN: 16

Movies with rental duration more than 7.

ANS: SELECT * FROM film WHERE rental_duration > 7;

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

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

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

since, checking multiple words using LIKE.

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