Question

5) The bank branches in cities where the bank has a limited presence are not making any money. The bank decides to close those branches. Write a query to delete the accounts in all branches in cities having the fewest number of branches. (In reality, the bank would probably transfer those accounts to other branches, but this requires use of a high level language, e.g. C++, in conjunction with SQL). Verify that they are deleted by looking at account after the deletion. Let's do this in stages.

a. Find the minimum count of branches among the branches in cities.

b. Find the bank cities having the minimum count of branches.

c. Find the bank names of the banks in cities having the minimum count of branches.

d. Now do the full query.

6) Delete the bank branch(s) that has no assets.

Answer 1

Select (Min(count (no_of_branch))) branch_count From branches

Group By cities having count(no_of_branch)>0

b)

Select cities, (Min(count (no_of_branch))) branch_count From branches

Group By cities having count(no_of_branch)>0

C)create table bank_min_branches AS(

Select bank_name,cities,(Min(count (no_of_branch))) branch_count From branches

Group By cities having count(no_of_branch)>0 );

D)delete from accounts where bank_name in(select bank_name from bank_min_branches)

Explanation

Here we used 2 tables branches and accounts and assumed column names.no_of_branch is a column in branches table we have assumed here.

I have shown queries step by step and written full query in last with help of accounts table.

I have shown creation of new table of required data in third step. Name of the table created is bank_min_branches. It is required to query accounts table to delete required accounts.

In operator will check for all bank names matching with bank_min_branches and account record will be deleted whenever there is a match.

Note:->bank_name is unique here.

Hope it will help

Thanks