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.

Procedure

From the side menu, click RUN SQL to open the SQL editor. Click Blank.

Copy and paste the following query into the editor pane.

This query defines two tables, SALES and INVENTORY, which are the result of subqueries.


WITH
sales AS (
  SELECT sf.*
    FROM
      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
    WHERE
      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.*
    FROM
      gosalesdw.go_branch_dim AS bd,
      gosalesdw.dist_inventory_fact AS if
    WHERE
      if.branch_key = bd.branch_key
      AND bd.branch_code > 20)
SELECT
    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;

Click Run all.
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.
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.

Results

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.