-- Finding each customer who has a loan at the bank but no account.
    -----------------------------------------------------------------------
        SELECT cust_ID, customer_name
        FROM customer NATURAL JOIN borrower NATURAL LEFT OUTER JOIN depositor
        WHERE account_number IS NULL

    -- Function to find each customer that lives on the same street and city of another speific customer  
    -----------------------------------------------------------------------
       CREATE OR REPLACE FUNCTION same_street_city(cust_ID VARCHAR(15)) 
        RETURNS TABLE (cust_ID_same VARCHAR(15),
                       customer_name_same VARCHAR(30))
        LANGUAGE plpgsql 
        AS 
        $$ 
           BEGIN
            RETURN QUERY 
            SELECT customer.cust_ID, customer.customer_name
            FROM (SELECT customer_street, customer_city
                  FROM customer
                  WHERE customer.cust_id = same_street_city.cust_ID) AS id_compare(street,city),customer
            WHERE customer_street = street AND customer_city = city;
            END; $$;
            
    -- Example ID
       SELECT *
       FROM same_street_city('12345')
    -- Finding each branch that has at least one customer who has an account and lives in Harrison
    -----------------------------------------------------------------------
        
    SELECT branch_name
    FROM account NATURAL JOIN depositor NATURAL JOIN customer
    WHERE customer_city = 'Harrison'
    GROUP BY branch_name
    HAVING COUNT(cust_id) >=1
    

    -- Finding each customer who as an account at every branch located in Brooklyn
    ----------------------------------------------------------------------
        WITH brooklyn_branch(branches) AS (
                 SELECT branch_name
                 FROM branch
                 WHERE branch_city = 'Brooklyn')
 
               
                 
        SELECT cust_id, customer_name, count(branches)
        FROM   customer NATURAL JOIN depositor NATURAL JOIN account, brooklyn_branch
        WHERE branch_name IN (branches)
        GROUP BY cust_id
        HAVING COUNT(DISTINCT branch_name) >= (SELECT COUNT (*)
                                            FROM brooklyn_branch);