-- Question 1
SELECT DISTINCT b.cname
    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;
-- SAME query for Q2
(SELECT DISTINCT b.cname as name
FROM loan l, borrower b WHERE b.lno = l.no AND l.type = 'Jumbo mortgage')
UNION
(SELECT c.name FROM customer c WHERE c.credit >= 750)

-- Question 3 
-- -- Find the names
-- -- of customers who took out a “jumbo mortgage” loan and a “student” loan.
SELECT DISTINCT
    c.name
FROM 
    customer AS c, 
    borrower as b, 
    loan as l,
    -- get names that took out a student loan
    (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
-- -- Find the names 
-- -- of customers who are involved in every actual loan as borrowers.
SELECT c.name
    FROM
        customer as c
    WHERE 
        (
            -- get number of loans taken out by a customer
        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
        ) = (
            -- get number of loans
        SELECT 
            COUNT(*)
        FROM loan
        )

-- Equivalent query for Q4
SELECT c.name
FROM customer c 
WHERE c.name NOT IN (
    SELECT missed.name
FROM customer missed, loan not_borrowed
WHERE NOT EXISTS (SELECT *
FROM borrower b
WHERE b.cname = missed.name
    AND b.lno = not_borrowed.no)
)


-- Question 5 
-- -- Find the names
-- -- of customers who took out every one among the “jumbo mortgage” and
-- -- “student” loans. 
SELECT c.name
    FROM
        customer as c
    WHERE 
        (
        -- get number of jumbo and student loans
        SELECT
            COUNT(in_l.no)
        FROM loan as in_l
        WHERE type IN ('jumbo mortgage', 'student')
        ) = (
        -- get number of jumbo and student loans specific customer take out
        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 
-- -- Find the names
-- -- of customers who took every “jumbo mortgage” loan or took out every
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
-- -- Find pairs
-- -- of names of customers who share the same loan. Avoid listing a customer with
-- -- himself (e.g. do not list
-- -- (Joe,Joe)). Also avoid repeating pairs which are equal modulo swapping the
-- -- components (e.g. only one of (John,Jane), (Jane,John) should be listed). 
SELECT DISTINCT *
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 
ORDER BY 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
        )