-- Problem 1

-- 1a 
SELECT DISTINCT c.name, l.no
FROM
    borrower as b
    INNER JOIN customer as c ON(b.cname = c.name)
    INNER JOIN loan as l ON(b.lno = l.no)
WHERE
    c.credit < l.minCredit;

-- 1b
SELECT DISTINCT c.name as 'name', COUNT(b.lno) as 'count'
FROM
    customer as c
    LEFT JOIN borrower as b ON(c.name = b.cname)
GROUP BY
    c.name;

-- 1c
SELECT DISTINCT l.type
FROM
    borrower as b
    RIGHT JOIN loan as l ON(b.lno = l.no)
GROUP BY
    l.type
HAVING COUNT(b.lno) <= ALL(
        SELECT COUNT(b2.lno)
        FROM
            borrower as b2
            RIGHT JOIN loan as l2 ON(b2.lno = l2.no)
        GROUP BY 
            l2.type
);

-- 1d
SELECT DISTINCT b.cname as 'name'
FROM
    borrower as b
    INNER JOIN loan as l ON(b.lno = l.no)
WHERE 
    -- count of every type of loan
    (SELECT COUNT(DISTINCT l2.type)
FROM
    loan as l2 ) = (
    -- count of every type of loan taken by b.cname
    SELECT COUNT(DISTINCT l3.type)
    FROM borrower as b3
        INNER JOIN loan as l3 ON(b3.lno = l3.no)
    WHERE b3.cname = b.cname
    );

-- 1e 
SELECT DISTINCT in_q_no.no
FROM(
    -- get the number of borrower for each loan.no
    SELECT l.no as 'no', MIN(l.type) as 'type', COUNT(b.lno) as "count"
    FROM
        borrower as b
        RIGHT JOIN loan as l ON(b.lno = l.no)
    GROUP BY 
            l.no
) as in_q_no
    INNER JOIN (
    -- get the average for each loan type 
    SELECT in_q.type as 'type', AVG(in_q.[count]) as 'avg'
    FROM (
        SELECT l.no as 'no', MIN(l.type) as 'type', COUNT(b.lno) as "count"
        FROM
            borrower as b
            RIGHT JOIN loan as l ON(b.lno = l.no)
        GROUP BY 
            l.no
        ) as in_q
    GROUP BY 
        in_q.type
) as in_q_type_avg ON (in_q_no.type = in_q_type_avg.type)
WHERE 
    in_q_no.count > in_q_type_avg.[avg];

-- 1f
SELECT DISTINCT outer_c1.name, outer_c2.name
FROM
    customer as outer_c1,
    customer as outer_c2
WHERE outer_c1.name < outer_c2.name AND(
    (
        -- get number of NULL values of first column of comparing loan no 
        SELECT COUNT(*)
    FROM(
            SELECT test_1.lno as 'c_1_lno'
        FROM(
                SELECT DISTINCT b1.lno
            FROM
                customer as c1
                INNER JOIN borrower as b1 ON (c1.name = b1.cname)
            WHERE
                    c1.name = outer_c1.name
            ) as test_1
            FULL OUTER JOIN (
                SELECT DISTINCT b2.lno
            FROM
                customer as c2
                INNER JOIN borrower as b2 ON (c2.name = b2.cname)
            WHERE
                    c2.name = outer_c2.name
            ) as test_2 ON (test_1.lno = test_2.lno)
            ) as in_q
    WHERE in_q.c_1_lno IS NULL 
    ) = 0 AND (
        -- get number of NULL values of second column of comparing loan no 
        SELECT COUNT(*)
    FROM(
            SELECT test_4.lno as 'c_1_lno'
        FROM(
                SELECT DISTINCT b3.lno
            FROM
                customer as c3
                INNER JOIN borrower as b3 ON (c3.name = b3.cname)
            WHERE
                    c3.name = outer_c1.name
            ) as test_3
            FULL OUTER JOIN (
                SELECT DISTINCT b4.lno
            FROM
                customer as c4
                INNER JOIN borrower as b4 ON (c4.name = b4.cname)
            WHERE
                    c4.name = outer_c2.name
            ) as test_4 ON (test_3.lno = test_4.lno)
        ) as in_q2
    WHERE in_q2.c_1_lno IS NULL 
    ) = 0);