API Builder: Sample Queries

Modified on Tue, 03 Apr 2018 at 10:55 AM

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;


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article