-- 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 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 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);

-- c check, get all loan type and count
-- SELECT l.type, COUNT(b.lno)
-- FROM
--     borrower as b
--     RIGHT JOIN loan as l ON(b.lno = l.no)
-- GROUP BY
--     l.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 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);

-- Problem 2

-- 2a 
-- Create view
GO 
CREATE VIEW Original_loan as (SELECT * FROM loan);
GO
-- Update loan type to student where type is jumbo
UPDATE loan 
SET type = 'student'
WHERE type LIKE 'jumbo%'
-- Updatel loan type to jumbo where type is student
UPDATE loan 
SET type = 'jumbo'
WHERE loan.no IN (
        SELECT no 
        FROM Original_loan
        WHERE type = 'student'
    );

DROP VIEW Original_loan;

-- 2b
GO 
CREATE VIEW Original_loan_2b as (SELECT * FROM loan);
GO

GO
CREATE VIEW Original_customer_2b as (SELECT * FROM customer)
GO

GO
CREATE VIEW Original_borower_2b AS (SELECT * FROM borrower)
GO

-- Delete from customer
DELETE FROM customer
WHERE name IN (SELECT DISTINCT b.cname
        FROM
            borrower as b
            INNER JOIN loan AS l ON(b.lno = l.no)
        WHERE 
        l.type LIKE 'jumbo%');

-- Delete from borrower
DELETE FROM borrower 
WHERE lno IN (SELECT no
        FROM 
            loan as l 
        WHERE 
            l.type LIKE 'jumbo%'
        ) OR 
        cname IN (
            SELECT b.cname
            FROM 
                Original_borower_2b as b 
                INNER JOIN loan as l ON(b.lno = l.no)
            WHERE 
                l.type LIKE 'jumbo%'
        )

-- Delete from loan 
DELETE from loan
WHERE type LIKE 'jumbo%';