CA 31 - Select Queries from DVD Rental database

DB-TASK-001

     Here LIMIT 10 will be used to limit the number of outputs, so onlly a handy amount of output is displayed due to the usage of Screenshot for the outputs

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



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



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.

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


5. List all unique replacement costs from the film table


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


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


8. Retrieve the list of film categories sorted alphabetically.




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

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


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


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


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

14. List the earliest rental date for each customer. Include customer_id and rental_date, and order by customer_id.


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


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

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


18. Find all unique replacement_cost values in the film table. Sort the results in ascending order.


19. List the first rental date for each store. Include store_id and rental_date, and sort by store_id.

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


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


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


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

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

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


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



Comments

Post a Comment

Popular posts from this blog

CA 04 - Two Sum & Sorted Two Sum

Image
  MY CODE: In brute force method the intuitive way of solving this problem with two for loop. Using nested loop to traverse through the list and try different combination of two sum to check if it matches with the target.    Time complexity: O(n^2)       OPTIMIZED:          In the optimized method of approaching the Two Sum problem. The given list is already sorted in ascending order. This gives us the leverage of using Two pointer technique to solve this problem. Thus using single loop to approach this problem. If current sum is less than target move the left pointer, if it is greater move the right pointer else return the current index (equal to target).  Time complexity: O(n) MY CODE:
Read more

CA 05 - Reverse the array

  Array Reverse Reverse an array  arr[] . Reversing an array means  rearranging  the elements such that the  first  element becomes the  last , the  second  element becomes  second last  and so on. Input: arr[] = [1, 4, 3, 2, 6, 5] Output: [5, 6, 2, 3, 4, 1] Explanation: The first element 1 moves to last position, the second element 4 moves to second-last and so on. Input: arr[] = [4, 5, 1, 2] Output: [2, 1, 5, 4] Explanation: The first element 4 moves to last position, the second element 5 moves to second last and so on. CODE:     BRUTE FORCE METHOD:          Here, in this method we will create a temporary array to store the reversed given array and then assign it to the given array class Solution :     def reverseArray ( arr ):         n = len (arr)         temp = [ 0 ] * n         for i in range (n- 1 ,- 1 ,- 1 ): ...
Read more

CA 21 - Basic Select SQL Queries

Image
 1.  Query all columns for a city in   CITY   with the   ID   1661 . The  CITY  table is described as follows: Here we make use of the where conditon cllause to retrieve the details of the Id with 1661, we select all the columns to be displayed and "*" is avoided for best practice. SOLUTION: SELECT ID,NAME,COUNTRYCODE,DISTRICT,POPULATION FROM CITY where ID = 1661 ; 2. Query all columns for all American cities in the   CITY   table with populations larger than   100000 . The   CountryCode   for America is   USA . The  CITY  table is described as follows: Here we make use of the where condtional clause, to obatin the population to be over thhe given number we make use of the' >' symbol to achieve this. Also the AND operator is used to state two where conditons where we want only the details of cities in USA so we use the countrycode check with 'USA' single quotes are used to check with the value, usage ...
Read more