1. Create table, fill-in content.

SQL QBQL

CREATE TABLE dept (
    deptno          NUMBER(2),
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
);
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');


Dept=[DEPTNO  DNAME      LOC]
      10      ACCOUNTING LONDON
      20      SALES      PARIS
      30      RESEARCH   ROME
      40      OPERATIONS LONDON
;

QBQL notation follows modern programming languages standard and is self explanatory: it is assignment of relation value to the variable Dept. Relation value resembles table exported in comma separated format — except that there are no row and field delimiters. QBQL expressions are allowed to refer to relations by name or, conveniently, inline their values.

2. Copy table.

SQL QBQL

CREATE TABLE dept_copy as 
SELECT * FROM dept;


DeptCopy=Dept;

3. Projection.

SQL QBQL

SELECT DISTINCT loc FROM dept;


Dept v [LOC];

Projection in QBQL is expressed via generalized union of relation with an empty relation. The unary empty relation [LOC] is inlined into a union expression. We’ll show couple more queries where the generalized union operation "v" (aka inner union) shines later.

4. Restriction: find employee by name.

SQL QBQL

SELECT * FROM emp WHERE ename = 'SCOTT';

Emp ^ [ENAME]SCOTT;

Restriction in QBQL is simulated via natural join "^". Here, once again, QBQL leverages inline relations: the [ENAME]SCOTT being unary relation with single tuple content.

5. Attribute renaming.

SQL QBQL

SELECT deptno, dname AS name, loc FROM dept;


Dept /^ "DNAME=NAME";

Here QBQL query introduces binary identity predicate "DNAME=NAME". Renaming is simulated via join of relation with identity predicate, then projecting away the obsolete attribute DNAME. Set intersection join "/^" accomplishes both tasks at once. Note that SQL wildcard column notation can’t be leveraged even if a user wants to rename a single attribute in a relation with large number attributes, while QBQL query stays laconic.

6. Union: find all possible department numbers.

SQL QBQL

SELECT DISTINCT deptno FROM dept
UNION
SELECT DISTINCT deptno FROM emp;


Dept v Emp;

Inner union takes care of projecting both relations to common set of attributes.

7. Inner join: find common set of department numbers.

SQL QBQL

SELECT DISTINCT deptno FROM dept
INTERSECT
SELECT DISTINCT deptno FROM emp;

Dept <and> Emp;

The inner join Dept <and> Emp is user defined operation expressed via built-in operations as (Dept ^ Emp) v ((Dept v Emp) ^ []) . It essentially is natural join of Dept and Emp projected to the common set of attributes.

8. Set Difference/Antijoin: find “unreferenced” department numbers.

SQL QBQL

SELECT DISTINCT deptno FROM dept
MINUS
SELECT DISTINCT deptno FROM emp;

Dept <and> Emp';

QBQL stands for Quasi-Boolean Query Language where every classic boolean operation splits into two. We have already seen two operations — natural join "^" and inner join <and> — which are two relational interpretations of logical AND. There are also two interpretations of logical NOT: relational complement "'" which is set complement of tuples, and relational inversion "`" which is set complement of attributes.

9. Project one attribute away.

SQL QBQL

SELECT DISTINCT 
    empno, ename, job, mgr, hiredate, sal, deptno 
FROM emp;

Emp v [COMM]`;

This operation is known in “New Relational Algebra” as REMOVE. Once again, QBQL gracefully handles set operations on both attributes and tuples, while SQL requires a special wildcard enhancement syntax allowing to project onto subset of attributes (arguably, the most rejected proposal). REMOVE can also be expressed via composition Emp v (Emp /^ [COMM]);.

10. Assertion: “no instructor attends the same course he teaches”.

SQL QBQL

CREATE ASSERTION DisjointAttendsTeaches  
CHECK NOT EXISTS (
    SELECT person, course FROM Attends
    INTERSECTS
    SELECT person, course FROM Teaches
);

Attends /^ Teaches = [].

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: