-- 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%';

-- Delete views

DROP VIEW Original_loan_2b;
DROP VIEW Original_borower_2b;
DROP VIEW Original_customer_2b;