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:

• Show me all employees who work for departments that are scattered across multiple sites.

---

General suggestion 1 strategy:

• Create table (view) with just employees and sites
• Create count summary view
• Filter out those with only one site

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