Click here to Skip to main content
15,845,742 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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:


SQL
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.



SQL
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
SQL
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.
Posted
Updated 5-Mar-13 22:54pm
v8
Comments
samulcristina 6-Mar-13 0:39am    
i have tried all questions above mentioned but 5th question i written like


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;


--but my sir saying output is correct but query is wrong .
plese help to complete this.

SQL
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
 
Share this answer
 
We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

Try it yourself, you may find it is not as difficult as you think!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900