-- Hamish Getty #16449057
-- Question 1

SET SERVEROUTPUT ON;

DECLARE
  CURSOR emp_cursor IS
     SELECT e_number, e_name, e_address1, e_address3
     FROM employees;

temp_number employees.e_number%TYPE;
temp_name employees.e_name%TYPE;
temp_street employees.e_address1%TYPE;
temp_city employees.e_address3%TYPE;

BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO temp_number, temp_name, temp_street, temp_city;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID: '||temp_number);
    DBMS_OUTPUT.PUT_LINE('Name: '||temp_name);
    DBMS_OUTPUT.PUT_LINE('Address: '||temp_street||', '||temp_city||CHR(10));
  END LOOP;
  CLOSE emp_cursor;
END;
/
SET SERVEROUTPUT OFF;

-- Question 2

SET SERVEROUTPUT ON;

DECLARE
  CURSOR emp_cursor IS
     SELECT e_number, e_name, e_address1, e_address2
     FROM employees
	 WHERE e_address3 = 'Te Anautu';

temp_number employees.e_number%TYPE;
temp_name employees.e_name%TYPE;
temp_street employees.e_address1%TYPE;
temp_suburb employees.e_address2%TYPE;

BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO temp_number, temp_name, temp_street, temp_suburb;
    EXIT WHEN emp_cursor%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE('Employees from Te Anautu');
    DBMS_OUTPUT.PUT_LINE('ID: '||temp_number);
	DBMS_OUTPUT.PUT_LINE('Name: '||temp_name);
    DBMS_OUTPUT.PUT_LINE('Address: '||temp_street||', '||temp_suburb||CHR(10));
  END LOOP;
  CLOSE emp_cursor;
END;
/
SET SERVEROUTPUT OFF;

-- Question 3

SET SERVEROUTPUT ON;

DECLARE
  CURSOR sup_cursor IS
     SELECT s_name, sup_number, p_description, p_unit_price
     FROM suppliers, employees, products
	 ORDER BY p_description;

temp_name suppliers.s_name%TYPE;
temp_number employees.sup_number%TYPE;
temp_description products.p_description%TYPE;
temp_price products.p_unit_price%TYPE;

BEGIN
  OPEN sup_cursor;
  LOOP
    FETCH sup_cursor INTO temp_name, temp_number, temp_description, temp_price;
    EXIT WHEN sup_cursor%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE('Products Sold by Suppliers');
    DBMS_OUTPUT.PUT_LINE('Supplier: '||temp_name||', '||'Supplier ID: '||temp_number);
    DBMS_OUTPUT.PUT_LINE('Product: '||temp_description);
	DBMS_OUTPUT.PUT_LINE('Price: '||'$'||temp_price||' '||CHR(10));
  END LOOP;
  CLOSE sup_cursor;
END;
/
SET SERVEROUTPUT OFF;

-- Question 4
-- The "Update o_totals" from previous the AMICO lab seems to do nothing, therefore there is no order total?

SET SERVEROUTPUT ON;

DECLARE
  CURSOR ord_cursor IS
     SELECT o_number, e_name, o_order_date, o_total
     FROM orders, employees;

temp_number orders.o_number%TYPE;
temp_name employees.e_name%TYPE;
temp_date orders.o_order_date%TYPE;
temp_total orders.o_total%TYPE;

BEGIN
  OPEN ord_cursor;
  LOOP
    FETCH ord_cursor INTO temp_number, temp_name, temp_date, temp_total;
    EXIT WHEN ord_cursor%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE('Orders Greater Than $100');
    DBMS_OUTPUT.PUT_LINE('Order Number: '||temp_Number||', '||'Employee Name: '||temp_name);
	DBMS_OUTPUT.PUT_LINE('Date: '||temp_date);
    DBMS_OUTPUT.PUT_LINE('Total: '||'$'||temp_total||' '||CHR(10));
  END LOOP;
  CLOSE ord_cursor;
END;
/
SET SERVEROUTPUT OFF;

-- Question 5

SET SERVEROUTPUT ON;

DECLARE
  CURSOR pay_cursor IS
     SELECT e_name, p_amount, p_date, o_number
     FROM employees, payments
	 ORDER BY e_name;

temp_name employees.e_name%TYPE;
temp_amount payments.p_amount%TYPE;
temp_date payments.p_date%TYPE;
temp_number payments.o_number%TYPE;

BEGIN
  OPEN pay_cursor;
  LOOP
    FETCH pay_cursor INTO temp_name, temp_amount, temp_date, temp_number;
    EXIT WHEN pay_cursor%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE('Payments Made by Each Employee');
    DBMS_OUTPUT.PUT_LINE('Employee Name: '||temp_Name);
    DBMS_OUTPUT.PUT_LINE('Total: '||'$'||temp_amount);
	DBMS_OUTPUT.PUT_LINE('Date: '||temp_date);
	DBMS_OUTPUT.PUT_LINE('Order Number: '||temp_number||' '||CHR(10));

  END LOOP;
  CLOSE pay_cursor;
END;
/
SET SERVEROUTPUT OFF;