[SQL : AQL] correspondence : examples

60 views

Skip to first unread message

Fred Eisele

unread,

Apr 19, 2018, 3:33:32 PM4/19/18

to categoricaldata

The question came up about representing various SQL queries.

I would like to discuss how the following examples would be implemented in AQL.

Each topic should probably be done under separate threads.

As they are completed I will put them in https://github.com/CategoricalData/fql/tree/master/resources/examples/aql

Examples drawn from :

* https://www.w3schools.com/sql/default.asp

* https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_subselectexamples.html

distinct

   SELECT COUNT(DISTINCT A))
     FROM T1
     WHERE A3 > 0
     HAVING AVG(DISTINCT A4) >1;

and

SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

SELECT * FROM Customers
WHERE City='Berlin' OR City='München';

not

SELECT * FROM Customers
WHERE NOT Country = 'Germany';

order by

SELECT EMPNO, FIRSTNME, LASTNAME
     FROM DSN8A10.EMP
     ORDER BY HIREDATE;

SELECT *                             
  FROM (SELECT * FROM T1             
         UNION ALL                   
        (SELECT * FROM T2 ORDER BY 1)
       ) AS UTABLE                   
  ORDER BY ORDER OF UTABLE;

min/max

   SELECT JOB, MAX(SALARY), MIN(SALARY)
     FROM DSN8A10.EMP
     GROUP BY JOB
     HAVING COUNT(*) > 1 AND MAX(SALARY) > 50000;

count/avg/sum

   SELECT COUNT(DISTINCT A))
     FROM T1
     WHERE A3 > 0
     HAVING AVG(DISTINCT A4) >1;

like/wildcard

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
    AND SUPPLIER NOT LIKE 'A%';

SELECT EMPNO, ACTNO, CHAR(EMSTDATE,USA), CHAR(EMENDATE,USA)
     FROM DSN8A10.EMPPROJACT
     WHERE EMPNO IN (SELECT EMPNO FROM DSN8A10.EMP
                     WHERE WORKDEPT = 'E11');

between

inner join

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

outer join [right and left]

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;

self join

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

union

(SELECT * FROM T1 ORDER BY C1)
UNION
(SELECT * FROM T2 ORDER BY C2);

SELECT *                             
  FROM (SELECT * FROM T1             
         UNION ALL                   
        (SELECT * FROM T2 ORDER BY 1)
       ) AS UTABLE                   
  ORDER BY ORDER OF UTABLE;

group by

SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8A10.EMP Q
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8A10.EMP
                             WHERE NOT WORKDEPT = Q.WORKDEPT);

having

SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8A10.EMP
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8A10.EMP);

exists

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

any/all

subselect from [derived table/column]

SELECT HIREYEAR, AVG(SALARY)
     FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
             FROM DSN8A10.EMP) AS NEWEMP
     GROUP BY HIREYEAR;

Ryan Wisnesky

unread,

Apr 21, 2018, 5:23:19 PM4/21/18

to categor...@googlegroups.com

Hi Fred,

AQL is both more expressive than SQL in some ways (AQL can ‘chase’ and SQL can't), but it is less expressive in some ways (SQL can aggregate and AQL can’t). But AQL is not meant to replace SQL, it is meant to replace certain uses of SQL for data integration/exchange purposes. So the answer to most of your queries below is that AQL can’t represent them without some kind of encoding. Or, you can migrate AQL instances to SQL inside the AQL IDE and then use AQL’s built-in SQL engine (H2) to do the queries below.

Ryan

On Apr 19, 2018, at 3:33 PM, Fred Eisele <fredric...@gmail.com> wrote:
The question came up about representing various SQL queries.
I would like to discuss how the following examples would be implemented in AQL.
Each topic should probably be done under separate threads.

As they are completed I will put them in https://github.com/CategoricalData/fql/tree/master/resources/examples/aql

Examples drawn from :
* https://www.w3schools.com/sql/default.asp
* https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_subselectexamples.html

distinct
   SELECT COUNT(DISTINCT A))
     FROM T1
     WHERE A3 > 0
     HAVING AVG(DISTINCT A4) >1;

AQL contains a ‘distinct’ keyword.

and
SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin’;

AQL’s where clauses are ‘and’ by default of the list of equalities in them.

or
SELECT * FROM Customers
WHERE City='Berlin' OR City='München’;

Need Boolean algebra in the type side or use sigma.

not
SELECT * FROM Customers
WHERE NOT Country = 'Germany’;

Need Boolean algebra in the type side.

order by

SELECT EMPNO, FIRSTNME, LASTNAME
     FROM DSN8A10.EMP
     ORDER BY HIREDATE;

SELECT *                             
  FROM (SELECT * FROM T1             
         UNION ALL                   
        (SELECT * FROM T2 ORDER BY 1)
       ) AS UTABLE                   
  ORDER BY ORDER OF UTABLE;

AQL cannot order its rows because it uses set semantics

min/max

   SELECT JOB, MAX(SALARY), MIN(SALARY)
     FROM DSN8A10.EMP
     GROUP BY JOB
     HAVING COUNT(*) > 1 AND MAX(SALARY) > 50000;

count/avg/sum

   SELECT COUNT(DISTINCT A))
     FROM T1
     WHERE A3 > 0
     HAVING AVG(DISTINCT A4) >1;

like/wildcard

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#
    AND SUPPLIER NOT LIKE 'A%';

SELECT EMPNO, ACTNO, CHAR(EMSTDATE,USA), CHAR(EMENDATE,USA)
     FROM DSN8A10.EMPPROJACT
     WHERE EMPNO IN (SELECT EMPNO FROM DSN8A10.EMP
                     WHERE WORKDEPT = 'E11');

We don’t really know how to do aggregation in AQL.

between

inner join
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS INNER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;
outer join [right and left]
SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#;
self join
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

AQL Select/From/Where clauses work exactly the same as in SQL.

union

(SELECT * FROM T1 ORDER BY C1)
UNION
(SELECT * FROM T2 ORDER BY C2);

SELECT *                             
  FROM (SELECT * FROM T1             
         UNION ALL                   
        (SELECT * FROM T2 ORDER BY 1)
       ) AS UTABLE                   
  ORDER BY ORDER OF UTABLE;

You can use ‘union’ or ‘coproduct’ keywords

group by

SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8A10.EMP Q
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8A10.EMP
                             WHERE NOT WORKDEPT = Q.WORKDEPT);

having

SELECT WORKDEPT, MAX(SALARY)
     FROM DSN8A10.EMP
     GROUP BY WORKDEPT
     HAVING MAX(SALARY) < (SELECT AVG(SALARY)
                             FROM DSN8A10.EMP);

exists

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

any/all

subselect from [derived table/column]

SELECT HIREYEAR, AVG(SALARY)
     FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
             FROM DSN8A10.EMP) AS NEWEMP
     GROUP BY HIREYEAR;

We can’t do aggregation in AQL

--
You received this message because you are subscribed to the Google Groups "categoricaldata" group.
To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply all

Reply to author

Forward

0 new messages