Hard problem with solutions 20

             Hard problem

1) Find any branch name that has 'r' in the second position and four positions?

   SQL QUERY: select branch_name from loan where branch_name LIKE '_r' and 

                      branch_name LIKE '___R

  Description: '_' The underscore represents a single character and  " r " in 

                    our searching character. We search " r " in 2nd position so 

                    we can use 1st position in one underscore and " r " in 4th position 

                    so we can use 3st position in one underscore

2) Find any branch name that has 'r' in the second position or four positions?

   SQL QUERY: select branch_name from loan where branch_name LIKE '_r' or

                      branch_name LIKE '___R

  Description: '_' The underscore represents a single character and  " r " in 

                    our searching character. We search " r " in 2nd position so 

                    we can use 1st position in one underscore or " r " in 4th position 

                    so we can use 3st position in one underscore

3) List of the minimum amount in each branch from loan table and where amount less than 2500tk?

     SQL query: select min( amount), branch_name  from loan group by branch_name and amount < 2500.

4) List of the maximum amount in each branch from loan table and where amount greater than 2500tk?

     SQL query: select max( amount), branch_name  from loan group by branch_name and amount > 2500.

5) Find the car owner phone number who parked his car in "Netrokona" or "Dhaka"

 SQL Query:  select Car_owner .phone

                      from (( Car_owner INNER JOIN Rent ON Car_owner .c_id  =  Rent .c_id )

                      INNER JOIN Car_parking  ON Rent.p_id  =  Car_parking.p_id 

                      and  Car_parking .position = ' Netrokona '  or  Car_parking .position = ' Dhaka' );

6)   Find the car owner all the information who parked his car in "10/12/2018" or "26/07/2018"

      SQL Query:  select *

                      from  Car_owner INNER JOIN Rent ON Car_owner .c_id  =  Rent .c_id  
                      and Rent.date = ' 10/12/2018 '  or  Rent.date = ' 26/07/2018 ';

7)   Find the car owner all the information who parked his car in Dhaka And duration in more 

      then 5 hours

    

      SQL Query:  select *

                      from (( Car_owner INNER JOIN Rent ON Car_owner .c_id  =  Rent .c_id )

                      INNER JOIN Car_parking  ON Rent.p_id  =  Car_parking.p_id 

                      and  Car_parking .position = ' Dhaka ' and Car_parking .duration > 5);

8)   Find the car owner all the information who parked his car in Dhaka And duration in less

      then 7  hours

    

      SQL Query:  select *

                      from (( Car_owner INNER JOIN Rent ON Car_owner .c_id  =  Rent .c_id )

                      INNER JOIN Car_parking  ON Rent.p_id  =  Car_parking.p_id 

                      and  Car_parking .position = ' Dhaka ' and Car_parking .duration < 7);

9)   Find the car owner all the information who parked his car in Dhaka Or Netrakona?

    

      SQL Query:  select *

                      from (( Car_owner INNER JOIN Rent ON Car_owner .c_id  =  Rent .c_id )

                      INNER JOIN Car_parking  ON Rent.p_id  =  Car_parking.p_id 

                      and  Car_parking .position = ' Dhaka ' or Car_parking .position = ' Netrakona' );

10) Show amount from the loan but amount attribute is addition by 10 and after amount division by 5;

       SQL Query:   Select ( amount + 10 )/5  from loan 

11)  Query a list of CITY names from STATION with even ID numbers only. 

     You may print the results in any order but must exclude duplicates from your answer.

      Input Format

     The STATION table is described as follows:

     Ans:
            select distinct CITY from STATION where (ID%2=0) order by CITY;

12)  Let be the number of CITY entries in STATION, and let be the number 

     of distinct CITY names in STATION; query the value of from STATION. 

     In other words, find the difference between the total number of CITYentries 

     in the table and the number of distinct CITY entries in the table.

     Input Format

    The STATION table is described as follows

   
    Ans:
          Select  count(CITY) - count(DISTINCT CITY) 
          from station

13)  Query the two cities in STATION with the shortest and longest CITY 

     names, as well as their respective lengths (i.e.: number of characters in 

     the name). If there is more than one smallest or largest city, choose 

     the one that comes first when ordered alphabetically.

      Input Format

     The STATION table is described as follows:

     Ans: 

            SELECT CITY, LENGTH(CITY)
            FROM STATION
            ORDER BY LENGTH(CITY) DESC LIMIT 1;

            SELECT CITY, LENGTH(CITY)
            FROM STATION
            ORDER BY LENGTH(CITY) ASC LIMIT 1;

14)  Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) 
     from STATION. Your result cannot contain duplicates.

      Input Format

     The STATION table is described as follows:

     Ans:

            select city from station where city like 'A%' or city like 'E%' or 
            city like 'I%' or city like 'O%' or city like 'U%' order by city;

15)  Query the list of CITY names ending with vowels (a, e, i, o, u) 
     from STATION. Your result cannot contain duplicates.

     Input Format

    The STATION table is described as follows

    Ans:

           select distinct CITY from STATION where CITY like '%a' or CITY like '%e' or 
           CITY like '%i' or CITY like '%o' or CITY like '%u' order by CITY;

16)   Query the list of CITY names from STATION which have vowels 
      (i.e., a, e, i, o, and u) as both their first and last characters. Your result 
      cannot contain duplicates.

      Input Format

     The STATION table is described as follows

     Ans:
            select distinct city from station where substring(city,1,1) IN ('A','E','I','O','U') 
            and substring(city,-1,1) IN ('A','E','I','O','U');

      OR

             SELECT DISTINCT CITY
             FROM STATION
             WHERE SUBSTRING(CITY, 1, 1) IN ('A', 'E', 'I', 'O', 'U')
            AND SUBSTRING(CITY, -1) IN ('a', 'e', 'i', 'o', 'u')
            ORDER BY CITY

      OR

            SELECT City
            FROM Station
            WHERE SUBSTRING(City,1,1) IN ('a','e','o','i','u')
           && SUBSTRING(City,-1) IN ('a','e','o','i','u')
           ORDER BY City ASC

17)  Query the list of CITY names from STATION that do not start with vowels. 
     Your result cannot contain duplicates.

      Input Format

     The STATION table is described as follows:

     Ans:

            SELECT DISTINCT CITY FROM STATION WHERE NOT CITY LIKE 'a%' 
            AND NOT CITY LIKE 'e%' AND NOT CITY LIKE 'i%' AND NOT CITY 
            LIKE 'O%' AND NOT CITY LIKE 'u%' ORDER BY CITY ASC ; 
        
       OR

            SELECT DISTINCT(CITY) FROM STATION WHERE CITY 
            REGEXP '^[^AEIOU]

      OR
           
            select distinct CITY from STATION where CITY RLIKE '^[^AEIOU]'

18)  Query the list of CITY names from STATION that do not end with vowels. 
     Your result cannot contain duplicates.

     Input Format

    The STATION table is described as follows:

    Ans:

           SELECT DISTINCT CITY
           FROM STATION
           WHERE NOT(CITY LIKE '%a' OR CITY LIKE '%e' OR CITY LIKE '%i' OR 
           CITY LIKE '%o' OR CITY LIKE '%u')
           ORDER BY CITY ASC

19)  Query the list of CITY names from STATION that do not start with 
     vowels and do not end with vowels. Your result cannot contain duplicates.

     Input Format

    The STATION table is described as follows:

     Ans:

             select distinct CITY from STATION where CITY not regexp '^[aeiou]' 
             and CITY not regexp '[aeiou]$' order by CITY;

20)  Query the list of CITY names from STATION that either do not start 

       with vowels or do not end with vowels. Your result cannot contain 

       duplicates.

        Input Format

       The STATION table is described as follows

      Ans:

            select distinct CITY from STATION where CITY RLIKE '^[^AEIOU]' 

            or CITY RLIKE '[^aeiou]$' order by CITY

<-- Back Page