!!! error Construction Ongoing
This is not a finished post. The final version may have drastic differences as compared to this draft.

Declarative Programming

It did not take me so long to realize the difference between high- and low-level languages (actually, it is not a clear dichotomy), but it does take a while for me to realize the distinction between declarative and imperative languages. I have been using both Markdown and C-like for a long time yet I failed to appreciate their intrinsic differences.

Declarative languages (Markdown, HTML, CSS SQL, Regex, Prolog…) focus on WHAT instead of HOW. They are the direct descriptions of the desired result, and the interpreter would figure out how to generate the result. They do not use control flow, and the order of evaluation is often not explicit. Everything is handled by the interpreter and the way (HOW) to retrieve the result is unpredictable, but it is sure that you would get the result.

In imperative languages (C-like, Python, Java, Scheme…), a program is a description of computational process. The interpreter would carry out the computation using a fixed set of evaluation rule and the HOW is predictable. In a way, it is less direct than declarative programs since it does not directly describe the result.

I was told that the dichotomy between declarative and imperative is not clear-cut, either. I am waiting for the day that I feel why this is so by my own heart.

Some heads up on SQL

SQL stands for Structured Query Language. Essentially it is used to define, access, manipulate and query data within a database. Different databases should all support SQL which hAS an ANSI standard. However, different database companies try to come up with their own extended syntax beyond the standard ANSI SQL for market differentiation.

There are relational and nonrelational databases. The “relation” in “relational databases” essentially means that the data are organized in a tabular manner (rather than tree, graph or something else; such databases vanished FROM the modern industry).

In principle SQL keywords are not case-sensitive. However, in practice different companies of databases follow different standards. Capitalization may differ across operating systems, too. For now, we would use capitals for all SQL keywords and all lowercase letters for table/column names.

Basic Operations

!!! info Online Interpreter
Try this SQL interpreter.
When it says “syntax error”, the first thing to check is whether you misses a semicolon.

  • Table Update and Basic Select
-- In general,
-- SELECT <columns> FROM <table> WHERE <condition> ORDER BY <order>
-- <column> defines what the columns in the result table contain

-- Create a table by using both CREATE TABLE and SELECT
DROP TABLE IF EXISTS cities;
CREATE TABLE cities AS
  SELECT 38 AS latitude, 122 AS longitude, "Berkeley" AS city UNION
  SELECT 42,             71,               "Cambridge"        UNION
  SELECT 45,             93,               "Minneapolis";

-- Modify the table
INSERT INTO cities VALUES (-999, -999, "Unknown Land");
INSERT INTO cities VALUES (-888, -888, "Unknown Land");
INSERT INTO cities (latitude, longitude) VALUES (-999, -99); -- only two columns have values

-- Update
UPDATE cities SET latitude = 999 WHERE city = "Unknown Land";

-- SELECT would Select some records from a table and project it to a new table
-- But this table is not saved. Hence, you need CREATE TABLE to save it.
SELECT * FROM cities; -- Simply select everything
SELECT city, longitude FROM cities WHERE latitude > 40;
SELECT city, longitude + latitude as longlat FROM cities WHERE latitude > 40 ORDER BY longitude;

-- Filter out repeats, filter out null values
SELECT DISTINCT city FROM cities WHERE city IS NOT NULL;
-- NULL values are not 0 or whitespace. It is the result of leaving a cell of the table blank
-- Cannot apply comparing operators on NULL; you can only use IS NULL and IS NOT NULL
SELECT city, longitude - latitude AS gap FROM cities WHERE gap % 2 <> 0;
-- WHERE can access the gap variable defined earlier!

-- Order by descending, and take only the first two
SELECT city FROM cities ORDER BY longitude DESC LIMIT 2;

-- Delete some records from the table
DELETE FROM cities WHERE latitude < 0 OR longitude < 0;
-- Deletes all records!
DELETE FROM cities;
-- Delete the whole table
DROP TABLE cities;
  • Inner Join

Inner Join of two tables is Cartesian product.

DROP TABLE IF EXISTS parents;
CREATE TABLE parents AS
  SELECT "abraham" AS parent, "barack" AS child UNION
  SELECT "abraham"          , "clinton"         UNION
  SELECT "delano"           , "herbert"         UNION
  SELECT "fillmore"         , "abraham"         UNION
  SELECT "fillmore"         , "delano"          UNION
  SELECT "fillmore"         , "grover"          UNION
  SELECT "eisenhower"       , "fillmore";

DROP TABLE IF EXISTS dogs;
CREATE TABLE dogs AS
  SELECT "abraham" AS name, "long" AS fur UNION
  SELECT "barack" , "short" UNION
  SELECT "clinton" , "long" UNION
  SELECT "delano" , "long" UNION
  SELECT "eisenhower" , "short" UNION
  SELECT "fillmore" , "curly" UNION
  SELECT "grover" , "short" UNION
  SELECT "herbert" , "curly";

-- single table manipulation
SELECT child FROM parents where parent = "abraham";
SELECT parent FROM parents where parent > child;

-- Join two table
-- Permutes all combination of records from two tables and produces a four-column table
SELECT * FROM parents, dogs
-- Gets a table about all dogs whose parent and fur are both known
SELECT * FROM parents, dogs WHERE child = name
-- Select all parents of curly-furred dogs (no ambiguity of column names)
SELECT parent FROM parents, dogs WHERE child = name AND fur = "curly"

-- Recall: SELECT <columns> FROM <table> WHERE <condition> ORDER BY <order>
-- <table> is a comma-separated list of table names with optional aliases

-- Get all pairs of sibling dogs
-- Use dot operator and alias to disambiguate
CREATE TABLE siblings AS
  SELECT a.child AS firstSib, b.child AS secondSib
    FROM parents AS a, parents AS b
    WHERE a.parent = b.parent AND a.child < b.child;

-- Select all siblings with the same fur
SELECT a.firstSib, a.secondSib
  FROM siblings AS a, dogs AS b, dogs as c
  WHERE a.firstSib = b.name AND a.secondSib = c.name AND b.fur = c.fur;
  • Numeric and String Manipulation

Here is SQLite’s BNF for expression.

-- < <= > >= <> != = (`<>` and `!=` both means inequality. Equality is `=` - only a single equal sign.)
-- + - * / %
-- NOT AND OR
-- ROUND ABS POWER
-- || SUBSTR INSTR

DROP TABLE IF EXISTS players;
CREATE TABLE players AS
  SELECT "Reimu" AS player, 2 AS atk, 2 AS def, 2 AS lvl, "Human" AS race UNION
  SELECT "Marisa",          3,        1,        1,        "Human"         UNION
  SELECT "Youmu",           2,        4,        1,        "Non-human"     UNION
  SELECT "Cirno",           9,        9,        9,        "Baka"          UNION
  SELECT "Sanae",           1,        4,        2,        "Non-human";

SELECT player, atk + 2 * lvl AS total_atk, def + POWER(lvl, 2) AS total_def FROM players;

-- IN can be used as a shorthand to OR
SELECT player FROM players WHERE atk IN (1, 3) -- same as atk = 1 OR atk = 3
  • Aggregation (Cross-row operation)
-- SELECT <columns> FROM <table> WHERE <condition> ORDER BY <order> can only refer to one row
-- We check one row and decides whether to keep it

-- Aggregate functions check
-- SUM, MAX, SUM, AVG

SELECT MAX(atk) as max_raw_atk, MIN(def) as min_raw_def FROM players;
-- The max and the min of course does not occur in the same row; but this works!
SELECT COUNT(*), COUNT(atk), COUNT(DISTINCT atk) FROM players;
-- The aggregate functions also will select a row; sometimes they are meaningful
SELECT MAX(atk), player FROM players; -- It tells you the player with max atk is Marisa
SELECT MIN(atk), player FROM players; -- Which one? There are two mins
SELECT MIN(atk), player FROM players; -- Does not make sense
  • Grouping
-- SELECT <columns> FROM <table> GROUP BY <expressions> HAVING <expressions>
-- GROUP BY allows grouping. Aggregate operators are performed on individual groups.
-- HAVING is used to filter the groups.

SELECT race, AVG(def) FROM players GROUP BY race;
SELECT race, COUNT(*) AS population FROM players GROUP BY race ORDER BY AVG(atk);
SELECT atk/def, COUNT(*) FROM players GROUP BY atk/def HAVING COUNT(*) > 1; -- only wants groups that have more than 1 member
SELECT def, MAX(atk - def) FROM players GROUP BY def ORDER BY atk;

LINQ Equivalence

Probably one of the most powerful tool in C# (and one of the important reasons why I prefer it over the purely OOP Java) is LINQ.

// Linq supports both method syntax and sql syntax
int[] scores = new int[] {1, 3, 5, 6, 98, 99, 101};
// Usually we use var anyways
IEnumerable<int> greatScores =
  FROM score in scores
  WHERE score > 80
  SELECT score
// This is the same as
var greatScores_alt = scores.Where(x => x > 80);
// Note that the previous lines are never evaluated until you use them
// For example, toList() or foreach