Procedural programming:
-------------------------


Programming language + SQL
----------------------



DB Server supported languages:
-------------------------------
Specific to the DBMS or standard

plpgsql



Existing programming languages with DB hooks:
-----------------------------------------------
Some standardized, some specialized

- involving pre-compilers (embedded SQL)

- involving modules/drivers

    Java -> JDBC
    Python -> db2api
    

Frameworks:
-------------
Take over DB programming

Django
Grails

-------------------


--> connect to db
--> cursor (query runner object)

execute queries:

-> queries that return a single tuple: run/read
-> queries that return a multiple tuples:
          run/open stream/iterate over it/close

-> statements that do not return anything but change data
         run - check status - number of tuples impacted

input data into query /output: read data returned by query

error handling//status processing


--------------------

Find the top 3 most frequent y for each x in table R(x,y,z).


     q: select x, count(distinct y) from r group by x;


     for tuples in q:
         find the top 3


--------------------

CREATE FUNCTION sales_tax(subtotal real, state varchar) RETURNS real AS $$
DECLARE
   adjusted_subtotal real ;
BEGIN
   IF state = 'NY' THEN
       adjusted_subtotal = subtotal * 0.08 ;
   ELSIF state = 'AL' THEN
       adjusted_subtotal = subtotal ;
   ELSE
       adjusted_subtotal = subtotal * 0.06;
   END IF ;
   RETURN adjusted_subtotal ;
END ;
$$ LANGUAGE plpgsql ;