API Builder: Sample Queries

The following queries can be used to create APIs within the EMPDB database connection (available within free trial subscriptions).

Copy and paste a single query within the code block of an SQL Definition API.

Query 1 - List of Employees

SELECT ENAME, DNAME, JOB, EMPNO, HIREDATE, LOC

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

ORDER BY ENAME;

Query 2 - Employee Count by Department

SELECT DNAME, COUNT(*) COUNT_OF_EMPLOYEES

FROM DEPT, EMP

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY DNAME

ORDER BY 2 DESC;

Query 3 - Customer Orders

SELECT REPID, ORD.CUSTID, CUSTOMER.NAME CUSTNAME, PRODUCT.PRODID,  DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT

FROM ORD, ITEM, CUSTOMER, PRODUCT

WHERE ORD.ORDID = ITEM.ORDID

AND ORD.CUSTID = CUSTOMER.CUSTID

AND ITEM.PRODID = PRODUCT.PRODID

GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP;

Query 4 - Alternate Query format for #3

SELECT REPID, ORD.CUSTID, CUSTOMER.NAME CUSTNAME, PRODUCT.PRODID, DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT FROM ORD, ITEM, CUSTOMER, PRODUCT WHERE ORD.ORDID = ITEM.ORDID AND ORD.CUSTID = CUSTOMER.CUSTID AND ITEM.PRODID = PRODUCT.PRODID AND PRODUCT.PRODID LIKE & PRODID || '%' GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP;

Query 5 - Department Salary Ranges

SELECT D.DNAME AS DEPARTMENT,

       MIN(SAL) AS MINIMUM_SALARY,

       MAX(SAL) AS MAXIMUM_SALARY

  FROM EMP E

  JOIN DEPT D

    ON E.DEPTNO = D.DEPTNO

 GROUP BY D.DEPTNO, D.DNAME;

Query 6 - Employees by Department Name and Department Number

SELECT

  DEPT.DEPTNO,

  DEPT.DNAME,

  (

    SELECT

      COUNT(*)

    FROM

      EMP

    WHERE

      EMP.DEPTNO = DEPT.DEPTNO

  ) AS EMPLOYEE_COUNT

FROM

  DEPT

GROUP BY

  DEPT.DEPTNO,

  DEPT.DNAME

ORDER BY

  EMPLOYEE_COUNT;