// Some examples - These have been built in cs474lab to which you have access
// Vertical view (all rows, some columns)
Create View CustomerTotals (Name, OrderTotal)
AS Select Name, totalOrders from Customer;

// Aggregate View
Create View AllOrders(OrderTotal)
AS Select Sum(totalOrders) From Customer;

// Horizontal View
Create View GreenGrocer(OrderNumber, OrderDate, OrderTotal)
AS Select onum, oDate, orderTotal From Orders JOIN Customer on customer = cID 
WHERE cID = 1112; 

// Grant Permission
Grant ALL privileges on database.* to eid;
Grant Select on cs474lab.GreenGrocer to student;

// Revoke permissions
Revoke Select on cs474lab.GreenGrocer from student;

// Functions
CREATE FUNCTION WEIGHTED_AVERAGE2 (v1 VARCHAR(50))   
RETURNS INT   
DETERMINISTIC    
BEGIN      

	DECLARE i1,i2,i3,i4,avg INT;     
	SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 
	 FROM sfdata WHERE name=v1;     
	SET avg = (i1+i2+i3*2+i4*4)/8;      
RETURN avg;    
END;

A cursor allows a result set to be accessed one row at a time. 
Pointer to a particular row. Must be declared and opened to be used 
and may only scroll in one direction.

Fetch is the operation that retrieves the next row from the set and advances
the cursor.

This code also shows a "handler" declaration. If the NOT FOUND SET condition is raised, 
done is set to 1.

// Cursors (from mysql tutorial)
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

WHERE UPDATE clause can be used to define a cursor that 
will allow updating of rows. (Read a row, update a value...)

// Triggers from mysql tutorial 
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;'