Lesson 3.2: Running a complex query

Use the SQL editor in Db2® Big SQL console to run a complex query.

About this task

This complex query has common table expressions, aggregate functions, and a ranking function to rank products based on quantity sold online and quantity sold in stores.


  1. From the side menu, click RUN SQL to open the SQL editor. Click Blank.
  2. Copy and paste the following query into the editor pane.
    This query defines two tables, SALES and INVENTORY, which are the result of subqueries.
    sales AS (
      SELECT sf.*
          gosalesdw.sls_order_method_dim AS md,
          gosalesdw.sls_product_dim AS pd,
          gosalesdw.emp_employee_dim AS ed,
          gosalesdw.sls_sales_fact AS sf
          pd.product_key = sf.product_key
          AND pd.product_number > 10000
          AND pd.base_product_key > 30
          AND md.order_method_key = sf.order_method_key
          AND md.order_method_code > 5
          AND ed.employee_key = sf.employee_key
          AND ed.manager_code1 > 20),
    inventory AS (
      SELECT if.*
          gosalesdw.go_branch_dim AS bd,
          gosalesdw.dist_inventory_fact AS if
          if.branch_key = bd.branch_key
          AND bd.branch_code > 20)
        sales.product_key AS PROD_KEY,
        SUM(CAST (inventory.quantity_shipped AS BIGINT)) AS inv_shipped,
        SUM(CAST (sales.quantity AS BIGINT)) AS inv_sold_in_store,
        RANK() OVER (ORDER BY SUM(CAST (sales.quantity AS BIGINT)) DESC) AS prod_rank
      FROM sales, inventory
      WHERE sales.product_key = inventory.product_key
      GROUP BY sales.product_key;
  3. Click Run all.
  4. Review the results in the Result pane.
    The results show the products (represented by their product key) ranked by the number of units that were shipped for online sales, and by the number of units that were sold in stores.
  5. Save this query so that you can share it with other members of your team who connect to the bigsql database.
    1. Click the Save icon.
    2. In the Save script box, type a name for the script and click OK.
    3. Select the Script Library tab.
    4. Find the script that you saved and click the Share icon.


Learn more about this complex query:
  • The query joins multiple tables. When choosing the order of tables in a query, remember to eliminate rows as early as possible.
  • Tables that use predicates to filter out many rows, or tables with rows that are removed as a result of a join, should be located early in a query. Such tables are considered to be highly selective. Ordering the tables in this way reduces the number of rows that must be moved to the next step of the query.
  • The INV_SHIPPED column is derived from the SUM aggregate function and a CAST function, as shown in the following statement from the query:
    SUM(CAST (inventory.quantity_shipped AS BIGINT)) AS inv_shipped
    The original column, QUANTITY_SHIPPED, is defined as an integer column. The CAST function converts the output to another data type; in this case, BIGINT. The SUM function returns a single aggregate value for the column.
  • The PROD_RANK column is derived from the RANK function, as shown in the following statement from the query:
    RANK() OVER (ORDER BY SUM(CAST (sales.quantity AS BIGINT)) DESC) AS prod_rank
    The SALES.QUANTITY column is sorted in descending order. The RANK function generates a number that is based on the sorted order.