please help me to write queries

See more:

Oracle

i have three tables mentioned below bank bank_id bank_name ------- --------- 1 sbi 2 icici 3 hdfc 4 rbs 5 hsbc location location_id location_city ----------- ------------ 1 NY 2 Colorado 3 gzb 4 london 5 bankok bank_location bank_id location_id ---- ------ 1 1 1 2 1 3 1 4 1 5 3 2 3 4 4 1 5 2 5 5

1) write a query to list the bank name with branches at all locations ?



i have tried like below query:

SELECT a.bank_name, b.location_city
  FROM bank a,
       location b,
       (SELECT *
          FROM (  SELECT bank_id, COUNT (location_id) location_count
                    FROM bank_location
                GROUP BY bank_id
                ORDER BY location_count DESC)
         WHERE ROWNUM <=1) c
WHERE a.bank_id = c.bank_id;

output like:

bank_name location_city
-------- -------------
HSBC DELHI
HSBC NOIDA
HSBC GZB
HSBC MUMBAI
HSBC GOA


but my sir saying output was right but query having mistake.
--please solve this.

SELECT B.BANK_NAME,BL.LOC_CITY FROM BANKNAME B
 INNER JOIN LOCA_BANK L ON L.BANK_ID=B.BANK_ID
 INNER JOIN BANK_LOCATION BL ON BL.LOC_ID=L.LOCATION_ID.

this query giving output all bank names with all branches.
but i need to fetch which bank having all locations


i tried below query

SELECT a.bank_name, b.location_city
  FROM bank a,
       location b,( SELECT bank_name, COUNT (location_city) no_locations
    FROM (SELECT a.bank_name, b.location_city
            FROM bank a
                 INNER JOIN bank_location c
                    ON a.bank_id = c.bank_id
                 INNER JOIN location b
                    ON c.location_id = b.location_id)
GROUP BY bank_name
ORDER BY no_locations DESC) c
 where a.bank_name=c.bank_name and c.no_locations=(select count(*) from location);

this query also giving correct output .But He said again query is wrong.

please help to solve this.