Tql Example Two

Top's QueryLanguage Example 2:

Let's try a little more complicated schema to flex our TQL (or is it SMEQL now?) muscles on. It's not a good schema at all, but it reflects some real-world design. I'm using a completely martian indent-based syntax with no type information -- pretend we're using some freakish SQLite/Python hybrid or something ;)

 CREATE TABLE Employee:
   id
   name
   dept_id REFERENCES Department(id)
   manager REFERENCES Employee(id)

CREATE TABLE Compensation RESTRICT ALL TO ROLE=PAYROLL: id emp_id REFERENCES Employee(id) exempt wage # if exempt, use salary, otherwise use wage. salary # yes, poor schema design is real world, one must cope

CREATE TABLE Department: id name head_id REFERENCES Employee(id)

CREATE TABLE Site: id name

CREATE TABLE Department_Site: dept_id REFERENCES Department(id) site_id REFERENCES Site(id)

This look acceptable? If not, I can redesign. Now let's see some complex queries in TQL. Don't have much time right now, so I'll start with one:


General suggestion 1 strategy:

The idea is that first we create a simplified table view with only the information we need for further processing. Although relational languages are not actually sequential, TQL allows us to easily still think sequentially if we want just as one can think of math functions as sequential if they want. This is the easiest way for most humans to break big problems into smaller problems.

First attempt:

 emp = calc(employee, c("dept_id, (id) emp_id"))  
 empsites = join(emp, department_site, "a.dept_id=b.dept_id")
 counts = group(empsites, "emp_id", c("emp_id, count(site_id) site_count"))
 filter(counts, "site_count > 1")

Another approach to explore is to get a list of departments that are scattered across multiple sites, and then join that with list of all employees. (SQL would probably use an "in" operator, but TQL does not have one because it uses joins and/or filters instead.)


CategoryTql, TqlRoadmap


EditText of this page (last edited March 21, 2005) or FindPage with title or text search