Database- Querying and Filtering Data

Database:

An organized collection of structured information.

Relational DB:

To have relationship between table [includes structured & unstructured] ,query alone differs.

Tasks:

1. 
   

   Retrieve film titles and their rental rates. Use column aliases to rename title as "Movie Title" and rental_rate as "Rate".

   
   
   

   I need only two columns which is title and rental_rate, and rename it as asked using AS.

   
   
   

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

   
   

2.List customer names and their email addresses. Alias first_name and last_name as "First Name" and "Last Name".

 We have first name, last name, and email,rename the first two using AS.



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

3.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.

  Sort by rental rate descending,if two values are same then sort by title in ascending.



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

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

Sort by last_name first, then first_name.

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

5.List all unique replacement costs from the film table.

If Duplicates exist, then use DISTINCT for unique values.

SELECT DISTINCT replacement_cost FROM film;

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

Rename length to Duration(min) and list out the titles from film

SELECT title, length AS "Duration (min)" FROM film;

7.Retrieve customer first and last names along with their active status. Alias active as "Is Active".

Retrieve first and last name and also rename active using AS.

SELECT first_name, last_name, active AS "Is Active" FROM customer;

8.Retrieve the list of film categories sorted alphabetically.

  Categories are in category table, sort it by name.

SELECT name FROM category ORDER BY name;

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

Longest(lengthy) movies first,use desc,retrieve title also from film.

SELECT title, length FROM film ORDER BY length DESC;

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

Reverse alphabetical order in first name from actor.

SELECT first_name, last_name FROM actor ORDER BY first_name DESC;

11.List all unique ratings available in the film table.

Using distinct we can retrieve unique values.

SELECT DISTINCT rating FROM film;

12.Find all unique rental durations from the film table.

Use distinct for unique values.

SELECT DISTINCT rental_duration FROM film;

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

SELECT DISTINCT customer_id, active FROM customer ORDER BY customer_id;

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

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

For short size files listing we can use limit .

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

16.Get the top 5 customers with the highest customer_id. Include the first and last name.

By using limit we get top specific elements.

SELECT first_name, last_name FROM customer ORDER BY customer_id DESC LIMIT 5;

17.Retrieve all unique values of store_id from the inventory table.

SELECT DISTINCT store_id FROM inventory;

18.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 ASC;

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

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

SELECT DISTINCT rating FROM film ORDER BY rating;

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

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

22.Retrieve actor names sorted by last_name in ascending order and first_name in descending order.

We can use asc,desc for sorting.

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

23.List films ordered by replacement_cost in ascending order and rental_rate in descending order.

list out all films ordered by replacement cost in asc and rental rate using desc

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

24.Retrieve customer names sorted by last_name ascending and first_name descending.

list out first name and last name from customer ordered by asc for last name and desc in first name.

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

25.List all rentals sorted by customer_id ascending and rental_date descending.

using asc and desc sort the customer id and rental date from rental.

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

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

using asc ,desc order the rental_duration and title from film.

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