SQL fragments for “More than Query” talk.

fun mask (guess, answer) =
  let
    fun mask2 (m, i, [], answer) = m
      | mask2 (m, i, letter :: rest, answer) =
          mask2 ((m * 3
          + (if sub(answer, i) = letter
               then 2
             else if isSubstring(str letter) answer
               then 1
             else 0)), i + 1, rest, answer)
  in
    mask2 (0, 0, explode guess, answer)
  end;

fun maskToString m =
  let
    fun maskToString2 (m, s, 0) = s
      | maskToString2 (m, s, k) =
        maskToString2 (m div 3,
          List.nth(["b", "y", "g"], m mod 3) ^ s,
          k - 1)
  in
    maskToString2 (m, "", 5)
  end;

val words = from w in file.wordle.words yield w.word;

fun maskCount (guess, remainingWords) =
  from w in remainingWords
    group m = mask (guess, w) compute c = count
    compute count;

fun bestGuesses words =
  from w in words,
    maskCount = maskCount (w, words)
    order maskCount desc;

fun remaining (words, []) = words
  | remaining (words, (guess, m) :: rest) =
      from w in (remaining (words, rest))
      where maskToString (mask (guess, w)) = m;

Functional programming – values, types, operators

1 + 2;
> val it = 3 : int
"Hello, " ^ "world!";
> val it = "Hello, world!" : string
val integers = [1, 2, 3, 4, 5, 6, 7, 8];
> val integers = [1,2,3,4,5,6,7,8] : int list
fun filter f [] = []
  | filter f (first :: rest) =
      if (f first)
        then first :: (filter f rest)
        else filter f rest;
> val filter = fn : ('a -> bool) -> 'a list -> 'a list
filter (fn i => i mod 2 = 0) integers;
> val it = [2,4,6,8] : int list

val union = fn : 'a list * 'a list -> 'a list
val except = fn : 'a list * 'a list -> 'a list
val intersect = fn : 'a list * 'a list -> 'a list
val filter = fn : ('a -> bool) -> 'a list -> 'a list
val map = fn : ('a -> 'b) -> 'a list -> 'b list
val join = fn
  : 'a list * 'b list * ('a * 'b -> bool)
    -> ('a * 'b) list

from e in db.emps
  where e.deptno = 10
  yield {e.name, pay = e.sal + e.comm}

-- SQL
SELECT item, COUNT(*) AS c,
  SUM(sales) AS total
FROM ProduceSales
WHERE item != 'bananas'
  AND category IN ('fruit', 'nut')
GROUP BY item
ORDER BY item DESC;

item      c total
====== ==== =====
apples    2     9

-- GoogleSQL pipe syntax
FROM ProduceSales
|> WHERE item != 'bananas'
    AND category IN ('fruit', 'nut')
|> AGGREGATE COUNT(*) AS c, SUM(sales) AS total
   GROUP BY item
|> ORDER BY item DESC;

from p in produceSales
  where p.item != "bananas"
    andalso p.category elem ["fruit", "nut"]
  group p.item compute c = count,
    total = sum of p.sales
  order item desc

SELECT e.ename, e.sal
FROM emps AS e
WHERE e.deptno = 10
AND e.sal > (SELECT MAX(e2.sal)
             FROM emps AS e2
             WHERE e2.deptno = 20
             AND e2.job = 'PROGRAMMER')

from e in scott.emps
  where e.deptno = 10
    andalso sal >
    (from e2 in scott.emp
      where e2.deptno = 20
        andalso e2.job = "PROGRAMMER"
      compute max 0 of e2.sal)
  yield e.ename

from e in scott.emp
  where e.deptno = 10
    andalso
    (forall e2 in scott.emp
      where e2.deptno = 20
        andalso e2.job = "PROGRAMMER"
      require e.sal > e2.sal)
  yield e.ename

datatype personnel_id =
    EMPLOYEE of int
  | CONTRACTOR of {ssid: string, agency: string};

type member = {name: string, deptno: int, id: personnel_id};

val members = [
  {name = "Smith", deptno = 10, id = EMPLOYEE 100},
  {name = "Jones", deptno = 20,
   id = CONTRACTOR {ssid = "xxx-xx-xxxx", agency = "Cheap & cheerful"}];

val departments = scott.depts;

val primes = [2, 3, 5, 7, 11];

val bands = [["john", "paul", "george", "ringo"], ["simon", "garfunkel"]];

from i in [1, 2],
    j in ["a", "b"],
    k in [3, 4, 5, 6]
  where i + k < 6;
> {i: int, j: string, k: int} list

from dept in [10, 30],
    e in scott.emps
  where e.deptno = dept
  yield e.ename;
> string bag

from dept in [10, 30],
    e in scott.emps
  where e.deptno = dept
  order e.sal desc
  take 3
  yield {e.deptno, e.ename};
> {deptno: int, ename: string} list

-- Delete employees who earn more than 1,000.
DELETE FROM scott.emps
WHERE sal > 1000;

-- Add one employee.
INSERT INTO scott.emps (empno, deptno, ename, job, sal)
VALUES (100, 20, 'HYDE', 'ANALYST', 1150);

-- Double the salary of all managers.
UPDATE scott.emps
SET sal = sal * 2
WHERE job = 'MANAGER';

-- Commit.
COMMIT;

(* Delete employees who earn more than 1,000. *)
delete e in scott.emps
  where e.sal > 1000;
(* Add one employee. *)
insert scott.emps
  [{empno = 100, deptno = 20, ename = "HYDE",
    job = "ANALYST", sal = 1150}];
(* Double the salary of all managers. *)
update e in scott.emps
  where e.job = "MANAGER"
  assign (e, {e with sal = e.sal * 2});
(* Commit. *)
commit;

(* Delete employees who earn more than 1,000. *)
val emps2 =
  from e in scott.emps
    where not (e.sal > 1000);
(* Add one employee. *)
val emps3 = emps2 union
  [{empno = 100, deptno = 20, ename = "HYDE", job = "ANALYST",
    sal = 1150}];
(* Double the salary of all managers. *)
val emps4 =
  from e in emps3
    yield if e.job = "MANAGER"
      then {e with sal = e.sal * 2}
      else e;
(* Commit. *)
commit {scott with emps = emps4};

(* New and removed employees. *)
val empsAdded = emps4 except scott.emps;
val empsRemoved = scott.emps except emps4;
(* Compute the updated summary table. *)
val summary2 =
  from s in scott.summary
    union
      (from e in empsAdded
        yield {e.deptno, c = 1, sum_sal = e.sal}
    union
      (from e in empsRemoved
        yield {e.deptno, c = ~1, sum_sal = ~sum_sal})
    group s.deptno compute c = sum of c, sum_sal = sum of sum_sal
    where c != 0);
(* Commit. *)
commit {scott with summary = summary2};

(* Morel "forwards" relation *)
(* Relation defined using algebra. *)
fun clerks () =
  from e in emps
    where e.job = "CLERK";
(* Query uses regular iteration. *)
from e in clerks,
    d in depts
  where d.deptno = e.deptno
  andalso d.loc = "DALLAS"
  yield e.name;
val it =
  ["SMITH", "ADAMS"] : string list;

(* Morel "backwards" relation *)
(* Relation defined using a predicate. *)
fun isClerk e =
  e.job = "CLERK";
(* Query uses a mixture of constrained
   and regular iteration. *)
from e,
    d in depts
  where isClerk(e)
    andalso d.deptno = e.deptno
    andalso d.loc = "DALLAS"
  yield e.name;
val it =
  ["SMITH", "ADAMS"] : string list;

datatype 'a option = NONE | SOME of 'a;
SOME 1;

- from e in scott.emp
=   where e.deptno = 10
=   andalso (forall e2 in scott.emp
=     where e2.job = "PROGRAMMER"
=     require e.sal > e2.sal)
=   yield {e.ename, e.sal};
val it =
  [{ename="CLARK",sal=2450.0},{ename="KING",sal=5000.0},
   {ename="MILLER",sal=1300.0}] : {ename:string, sal:real} list

(* Initial database value and type (schema). *)
val scott1 = db
  : {emps: {name: string, empno: int, deptno: int,
            hiredate: string} bag,
    depts: {deptno: int, name: string} bag};
(* Shim that makes a v1 database look like v2. *)
fun scott2on1shim scott1 =
  {emps =
    fn () => from e in scott1.emps
      yield {e with hiredate = Date.fromString(e.hiredate)},
   depts = fn () => scott1.depts};
(* Shim that makes v3 database look like v1. *)
fun scott1on3shim scott3 =
  {emps =
    fn () => from e in scott3.emps
      yield {e with hiredate = Date.toString(e.hiredate)
               removing rating},
   depts = fn () => scott3.depts};
(* An application writes its queries & views against version 2;
   shims make it work on any actual version. *)
val scott = scott2;
fun recentHires () =
  from e in scott.e
    where e.hiredate > Date.subtract(Date.now(), 100);