CA 32 - Filter Assignments

 

DB-TASK-002



1. Get all movies (films) that have a rental rate greater than $3.


2. Get all movies that have a rental rate greater than $3 and a replacement cost less than $20.


3. Get all movies that are either rated as 'PG' or have a rental rate of $0.99.


4. Show the first 10 movies sorted by rental rate (highest first).


5. Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.


6. Skip the first 5 movies and fetch the next 3 sorted by rental rate in ascending order.


7. Get all movies with a rental duration between 3 and 7 days.


8. Get all movies where the title starts with 'A' and ends with 'e'


9. Find all customers who do not have an email address listed.


10. Find all movies released in 2006 with a rental rate of $2.99 or $3.99, and their title starts with 'S'. Display the top 5 results.SELECT title, rental_rate, release_year

    
dvdrental=# 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 10;


    Here the title conditional also came under the OR statement and the query turned out to be (release AND rate (2.99)) OR (rate (3.99 AND title ) so even though the whole second part becomes wrong the record is still printed , but we don't need this. SO we place IN instead of the OR .





11. Display 10 customers after skipping the first 20, sorted alphabetically by last name.


12. Get the top 5 movies with the highest replacement cost, skipping the most expensive one.


13. Find all rentals that occurred between '2005-05-01' and '2005-06-01'.


14. Get all actors whose last names contain the letters "man".


15. Find all movies where the special features are not listed (i.e., special_features is NULL).


16. Find all movies where the rental duration is more than 7 days.



17. Find the first 10 movies with a rental rate of $2.99 or $4.99, a rating of 'R', and a title containing the word "L".


18. Find all movies where the title starts with "A" or "B" and ends with "s".


19. Find all movies where the title contains "Man", "Men", or "Woman".





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