More SQL

Recall that we looked at examples of joining - entries from two tables, and entries from a single table. These joins were based on 'join conditions'.

It is also possible to join tables using the 'JOIN' keyword..

Note that JOINs can be based on != (aka <>), >, <, >= and <= as well, in addition to equality. Eg. to list all students who will be getting a 'A' (uses two inequality comparisons indirectly):

// http://www.comp.nus.edu.sg/~ooibc/courses/sql/dml_query_join.htm
SELECT  a.name, a.score
FROM student_scores a, grade_class b
WHERE b.grade = 'A' AND a.score BETWEEN b.low_end AND b.high_end;

Compare against a LIST of values..

As we saw earlier, this restricts the results of a GROUP BY clause. Eg. here's how to list all products sold, whose totals are greater than the average quantity sold:

Recall that 'IN' is an equality comparison against a list. To do inequality comparison of a value against a list of values (eg. need to be greater than ALL, need to be less than ANY..), use ALL, ANY.

Eg. "which products do we own [in our store], whose value is more than ALL other products's values supplied by vendors in Florida?"

Note that 'greater than ALL' is eqvt to 'greater than the largest of'. 'ALL' is used to select rows [plural in general] that comparison-succeed against all values in a list.

---

'ANY' is used to select rows [plural in general] that comparison-succeed with any value in a list.

Note that '= ANY(list of values)' is equivalent to the 'IN' operator (which is itself equivalent to multiple == conditions joined by ORs). So the following are all equivalent, for a given value of 'M':

(M==6) OR (M==8) OR (M==10) 

M IN (6,8,10) 

M = ANY (6,8,10)

So loosely speaking, ALL is equivalent to AND, and ANY is equivalent to OR.

A SELECT query that appears in FROM, creates a **virtual table** against which the main query can run.

These subqueries determine what columns get output by the main query - they can be actual (existing) columns or computed columns or results of aggregate functions.

These are also known as 'column subqueries' or 'inline subqueries'.

In a correlated subquery, the inner (sub) query is repeatedly run, for each row of the outer query! The inner is said to be (co-)related with the outer query when it references a column in the outer query's table. This is in effect, like a double (nested) 'for' loop..

for each row in OUTER table 
  run subquery on EACH row in INNER table, gather
  results, use in outer table's query

Here is the Wikipedia entry on correlated subqueries. This is the example shown there [select employees who make more than the average salary for their department]:

SELECT employee_number, name
  FROM employees AS Bob
  WHERE salary > (
    SELECT AVG(salary)
      FROM employees
      WHERE department = Bob.department);

In the above, the outer query "passes in", for each employee (each row), the employee's dept. [which the inner query refers to as Bob.department]. The inner query selects all salaries for that dept., computes the average, compares it with the passed-in employee's salary; if the test passes, the outer query selects the employee's # and name.

In the second query above, we have TWO correlated subqueries (that are identical), both of which need to run for every row of the main query.

If the columns below are in two different tables, the interection of them would list items in both (eg. History, Physics...):

We looked at several variations of queries and subqueries (SELECT, WHERE, HAVING, IN..).

Most interestingly, a SELECT subquery can appear at the top (SELECT), middle (FROM) or bottom (WHERE) of a parent query, which provides a flexible way to express complex logic (since such subqueries can be recursively nested):

Functions return values...

NEXTVAL returns the current value, then does ++ (ie. it does 'post increment', ie. C++ as opposed to ++C); CURRVAL on the other hand, just fetches the current value (does not ++ it).

PL/SQL involves extra (augmented) syntax that lets us do looping, branching, variable declaration and function declaration - these are of course not possible using 'plain' SQL.

PL/SQL can be used to create:

• blocks of code for one-time execution
• triggers - callbacks to invoke
• stored procedures - named procedures (no return values) for repeated calling
• stored functions - named functions (with return values) for repeated calling

Reminder - these can RETURN a value.

The following is an example from http://www.tutorialspoint.com/plsql.

Creating/defining a function:

FUNCTION findMax(x IN number, y IN number) 
RETURN number
IS
    z number;
BEGIN
   IF x > y THEN
      z:= x;
   ELSE
      Z:= y;
   END IF;

   RETURN z;
END; 

Calling/executing/running the function:

DECLARE
   a number;
   b number;
   c number;
BEGIN
   a:= 23;
   b:= 45;

   c := findMax(a, b);
   dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

Result:

Maximum of (23,45): 45