Source

htsql-firebird / EXAMPLES

Examples
========

HTSQL provides outstanding clarity without sacrificing rigor.  Not only
is working with HTSQL more productive than SQL, but things are possible
that may have otherwise exceeded a user's mental capacity. 

Let's assume we have a data model, with schools, departments, programs
and courses.  Here it is::

         +-------------+       +--------+
    /---m| DEPARTMENT  |>-----o| SCHOOL |m----\
    |.   +-------------+  .    +--------+    .|
    | .                  .                  . |
    |   department   department    a school   |
    |   offers       may be part   has one or |
    |   courses      of school     programs   |
    |                                         |
    |    +-------------+       +---------+    |
    \---<| COURSE      |       | PROGRAM |>---/
         +-------------+       +---------+


List all schools
----------------

An HTSQL query::

    /school

An equivalent SQL query::

    SELECT code, name
    FROM ad.school
    ORDER BY code;


Programs ordered by the title
-----------------------------

HTSQL::

    /program{title+}

SQL::

    SELECT title
    FROM ad.program
    ORDER BY title, school, code;


All courses missing a description
---------------------------------

HTSQL::

    /course?!description

SQL::

    SELECT department, number, title, credits, description
    FROM ad.course
    WHERE NULLIF(description, '') IS NULL
    ORDER BY 1, 2;


Departments in schools having "art" in its name
-----------------------------------------------

HTSQL::

    /department?school.name~'art'

SQL::

    SELECT d.code, d.name, d.school
    FROM ad.department AS d
    LEFT OUTER JOIN
         ad.school AS s ON (d.school = s.code)
    WHERE s.name ILIKE '%art%'
    ORDER BY 1;


The number of schools
---------------------

HTSQL::

    /count(school)

SQL::

    SELECT COUNT(TRUE)
    FROM ad.school;


Schools with programs
---------------------

HTSQL::

    /school?exists(program)

SQL::

    SELECT s.code, s.name
    FROM ad.school AS s
    WHERE EXISTS(SELECT TRUE
                 FROM ad.program AS p
                 WHERE s.code = p.school)
    ORDER BY 1;


The number of schools with programs
-----------------------------------

HTSQL::

    /count(school?exists(program))

SQL::

    SELECT COUNT(TRUE)
    FROM ad.school AS s
    WHERE EXISTS(SELECT TRUE
                 FROM ad.program AS p
                 WHERE (s.code = p.school));


Number of programs and departments per school
---------------------------------------------

HTSQL::

    /school{name, count(program), count(department)}

SQL::

    SELECT s.name, COALESCE(p.cnt, 0), COALESCE(d.cnt, 0)
    FROM ad.school AS s
    LEFT OUTER JOIN
         (SELECT COUNT(TRUE) AS cnt, p.school
          FROM ad.program AS p
          GROUP BY 2) AS p ON (s.code = p.school)
    LEFT OUTER JOIN
         (SELECT COUNT(TRUE) AS cnt, d.school
          FROM ad.department AS d
          GROUP BY 2) AS d ON (s.code = d.school)
    ORDER BY s.code;


Average number of courses offered by departments in each school
---------------------------------------------------------------

HTSQL::

    /school{name, avg(department.count(course))}

SQL::

    SELECT s.name, d.av
    FROM ad.school AS s
    LEFT OUTER JOIN
         (SELECT AVG(CAST(COALESCE(c.cnt, 0) AS NUMERIC)) AS av, d.school
          FROM ad.department AS d
          LEFT OUTER JOIN
               (SELECT COUNT(TRUE) AS cnt, c.department
               FROM ad.course AS c
               GROUP BY 2) AS c ON (d.code = c.department)
          GROUP BY 2) AS d ON (s.code = d.school)
    ORDER BY s.code;
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.