-- Steven Carr
-- CS630 Assignment 3, Question 1
-- October 22nd, 2024

-- Answer for part a; I insert values into the tables for testing
CREATE TABLE customers(
        cid INT PRIMARY KEY,
        firstname VARCHAR(50),
        lastname VARCHAR(50),
        city VARCHAR(20),
        state VARCHAR(2)
);

INSERT INTO customers(cid, firstname, lastname, city, state) VALUES(1000, 'Steven', 'Carr', 'Brockton', 'MA');
INSERT INTO customers(cid, firstname, lastname, city, state) VALUES(1001, 'Malik', 'Thomas', 'Goldsboro', 'NC');
INSERT INTO customers(cid, firstname, lastname, city, state) VALUES(1002, 'Kendall', 'Brinson', 'Brockton', 'MA');

CREATE TABLE accounts(
        aid INT PRIMARY KEY,
        atype VARCHAR(20),
        amount REAL
);

INSERT INTO accounts(aid, atype, amount) VALUES(12345, 'Checking', 7892.77);
INSERT INTO accounts(aid, atype, amount) VALUES(54321, 'Savings', 15635.90);
INSERT INTO accounts(aid, atype, amount) VALUES(67890, 'Savings', 27601.52);
INSERT INTO accounts(aid, atype, amount) VALUES(77684, 'Checking', 508.74);
INSERT INTO accounts(aid, atype, amount) VALUES(94681, 'Savings', 11931.31);

CREATE TABLE has_account(
        cid INT,
        aid INT,
        since DATE,
        PRIMARY KEY (cid, aid),
        FOREIGN KEY (cid) REFERENCES customers(cid),
        FOREIGN KEY (aid) REFERENCES accounts(aid)
);

INSERT INTO has_account(cid, aid, since) VALUES(1000, 12345, TO_DATE('2008-08-27', 'YYYY-MM-DD'));
INSERT INTO has_account(cid, aid, since) VALUES(1001, 54321, TO_DATE('2013-05-19', 'YYYY-MM-DD'));
INSERT INTO has_account(cid, aid, since) VALUES(1002, 67890, TO_DATE('2019-12-14', 'YYYY-MM-DD'));
INSERT INTO has_account(cid, aid, since) VALUES(1002, 77684, TO_DATE('2014-03-16', 'YYYY-MM-DD'));
INSERT INTO has_account(cid, aid, since) VALUES(1000, 94681, TO_DATE('2010-10-20', 'YYYY-MM-DD'));

-- Answer for part b
SELECT customers.cid, customers.firstname, customers.lastname FROM customers
JOIN has_account ON customers.cid = has_account.cid
JOIN accounts ON has_account.aid = accounts.aid
WHERE customers.state = 'MA' AND accounts.amount > 1000
GROUP BY customers.cid, customers.firstname, customers.lastname
ORDER BY customers.lastname ASC;

-- Answer for part c
SELECT atype, COUNT(*) AS acc_number FROM accounts
GROUP BY atype;

-- Answer for part d
SELECT customers.cid, customers.firstname, customers.lastname, COUNT(has_account.aid) AS acc_total
FROM customers
JOIN has_account ON customers.cid = has_account.cid
WHERE customers.state = 'MA'
GROUP BY customers.cid, customers.firstname, customers.lastname
HAVING COUNT(has_account.aid) >= 2;

-- Answer for part e
SELECT customers.cid, customers.firstname FROM customers
JOIN has_account ON customers.cid = has_account.cid
JOIN accounts ON has_account.aid = accounts.aid
WHERE accounts.atype = 'Checking'
GROUP BY customers.cid, customers.firstname
HAVING COUNT(accounts.aid) >= 2;

-- Answer for part f
SELECT customers.cid, customers.firstname, customers.lastname FROM customers
JOIN has_account ON customers.cid = has_account.cid
WHERE EXTRACT(YEAR FROM has_account.since) = 2018
INTERSECT
SELECT customers.cid, customers.firstname, customers.lastname FROM customers
JOIN has_account ON customers.cid = has_account.cid
WHERE EXTRACT(YEAR FROM has_account.since) = 2020;

-- Answer for part g
SELECT customers.cid, customers.lastname FROM customers
JOIN has_account ON customers.cid = has_account.cid
WHERE has_account.since < TO_DATE('2020-01-01', 'YYYY-MM-DD')
GROUP BY customers.cid, customers.lastname;

-- Answer for part h
SELECT customers.cid, customers.lastname FROM customers
JOIN has_account ON customers.cid = has_account.cid
JOIN accounts ON has_account.aid = accounts.aid
WHERE accounts.atype IN ('Savings', 'Checking')
GROUP BY customers.cid, customers.lastname
HAVING COUNT(DISTINCT accounts.atype) = 2;

-- Answer for part i
SELECT customers.cid, customers.lastname FROM customers
JOIN has_account ON customers.cid = has_account.cid
JOIN accounts ON has_account.aid = accounts.aid
GROUP BY customers.cid, customers.lastname
HAVING SUM(accounts.amount) >= 20000;

-- Answer for part j
SELECT accounts.aid FROM accounts
JOIN has_account ON accounts.aid = has_account.aid
WHERE accounts.atype = 'Checking'
GROUP BY accounts.aid
HAVING COUNT(accounts.aid) >= 2;