-- Question 1
SELECT DISTINCT c.name
    FROM 
        customer as c, 
        borrower as b, 
        loan as l
    WHERE 
        c.name = b.cname AND 
        b.lno = l.no AND 
        l.type= 'jumbo mortgage'

-- Question 2 
SELECT c.name
    FROM
        customer as c,
        borrower as b,
        loan as l
    WHERE 
        c.name = b.cname AND 
        b.lno = l.no AND 
        (l.type= 'jumbo mortgage' OR 
        c.credit >= 750)
    GROUP BY 
        c.name

-- Question 3 
SELECT DISTINCT
    c.name
FROM 
    customer AS c, 
    borrower as b, 
    loan as l,
    (SELECT in_c.name as "name"
        FROM 
            customer as in_c, 
            borrower as in_b, 
            loan as in_l 
        WHERE 
            in_c.name = in_b.cname AND
            in_b.lno = in_l.no AND 
            in_l.type = 'student') as inner_q
WHERE 
    inner_q.name = c.name AND 
    c.name = b.cname AND 
    b.lno = l.no AND 
    l.type = 'jumbo mortgage'

-- Question 4
SELECT c.name
    FROM
        customer as c
    WHERE 
        (
        SELECT 
            COUNT(*) as num_loan
        FROM
            borrower as in_b,
            loan as in_l 
        WHERE 
            c.name = in_b.cname AND 
            in_b.lno = in_l.no
        ) = (
        SELECT 
            COUNT(*)
        FROM loan
        )



-- Question 5 
SELECT c.name
    FROM
        customer as c
    WHERE 
        (
        SELECT
            COUNT(in_l.no)
        FROM loan as in_l
        WHERE type IN ('jumbo mortgage', 'student')
        ) = (
        SELECT 
            COUNT(in_lb.no)
        FROM 
            loan as in_lb,
            borrower as in_bor
        WHERE 
            c.name = in_bor.cname AND
            in_bor.lno = in_lb.no AND
            in_lb.type IN ('jumbo mortgage', 'student')
        )


-- Question 6 
SELECT c.name
    FROM
        customer as c
    WHERE 
        (
        SELECT 
            COUNT(in_l.no)
        FROM loan as in_l
        WHERE type IN ('jumbo mortgage')
        ) = (
        SELECT 
            COUNT(in_lb.no)
        FROM
            loan as in_lb,
            borrower as in_bor
        WHERE 
            c.name = in_bor.cname AND
            in_bor.lno = in_lb.no AND
            in_lb.type IN ('jumbo mortgage')
        ) OR (
        SELECT
            COUNT(in_l.no)
        FROM loan as in_l
        WHERE type IN ('student')
        ) = (
        SELECT 
            COUNT(in_lb.no)
        FROM
            loan as in_lb,
            borrower as in_bor
        WHERE 
            c.name = in_bor.cname AND
            in_bor.lno = in_lb.no AND
            in_lb.type IN ('student')
        )


-- Question 7
SELECT * 
FROM(
    SELECT
        b1.cname as "name1",
        b2.cname as "name2"
    FROM
        borrower as b1,
        borrower as b2
    WHERE 
        b1.cname != b2.cname AND
        b1.lno = b2.lno 
) as in_q
WHERE 
    in_q.name1 > in_q.name2 

-- Question 8 
SELECT 
    l.no 
FROM    
    loan as l
WHERE 
    (
    SELECT
        COUNT(in_c.name) 
    FROM 
        customer as in_c,
        borrower as in_b,
        loan as in_l 
    WHERE 
        in_c.name = in_b.cname AND 
        in_b.lno = in_l.no AND 
        in_l.no = l.no
    ) >= 2

-- Question 9 
SELECT l.no
FROM 
    customer as c
    INNER JOIN borrower as b ON(c.name = b.cname)
    INNER JOIN loan as l ON(b.lno = l.no)
WHERE 
    c.name = 'Smith'
    AND l.minCredit >= (
        SELECT
            MAX(minCredit)
        FROM
            loan as in_l,
            customer as in_c,
            borrower as in_b
        WHERE 
            in_c.name = in_b.cname AND
            in_b.lno = in_l.no AND
            in_c.name = 'Smith')
GROUP BY l.no 

-- Question 10
SELECT 
    l.no 
FROM
    loan as l 
WHERE 
    (SELECT COUNT(*) FROM customer WHERE credit < 600) =
        (SELECT 
            COUNT(in_c.name)
        FROM 
            customer as in_c 
            INNER JOIN borrower as in_b ON(in_c.name = in_b.cname)
            INNER JOIN loan as in_l ON(in_b.lno = in_l.no) 
        WHERE 
            in_l.no = l.no AND
            in_c.credit < 600
        )