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