Examples - Teradata Database

Example: Simple Subquery Using the IN Logical Predicate

This example selects the names and department locations of those employees who report to manager Aguilar (whose employee number is 10007).

     SELECT name, loc
     FROM employee, department
     WHERE employee.dept_no = department.dept_no
     AND   employee.dept_no IN (SELECT dept_no
                                FROM department
                                WHERE mgr_no = 10007);

Example: Simple Subquery Using an AVG Aggregate Expression

The following SELECT statement finds every employee in the employee table with a salary that is greater than the average salary of all employees in the table.

     SELECT name, dept_no, jobtitle, salary
     FROM employee
     WHERE salary > (SELECT AVG(salary)
                     FROM employee)
     ORDER BY name;

The statement returns this results table.

Example: Simple Subquery Using the ALL Logical Predicate

The following SELECT statement retrieves the employees with the highest salary and the most years of experience:

     SELECT emp_no, name, job_title, salary, yrs_exp
     FROM employee
     WHERE (salary,yrs_exp) >= ALL (SELECT salary,yrs_exp
                                    FROM employee);

For the current data in the employee table, the result looks like this:

     EmpNo Name      JobTitle     Salary  YrsExp
     ----- --------- --------- --------- -------
     10018 Russell S President 65,000.00      25

The result shows that one employee has both the highest salary and the most years of experience.

Example: Subquery Using the SUM and COUNT Aggregate Functions

The following statement uses a nested ordered analytical function and a HAVING clause to find those items that appear in the top 1 percent of profitability in more than 20 stores:

    SELECT item, COUNT(store)
    FROM (SELECT store,item,profit,QUANTILE(100,profit) AS percentile
          FROM (SELECT ds.store, it.item, SUM(sales)-
              COUNT(sales) * it.item_cost AS profit
              FROM daily_sales AS ds, items AS it
              WHERE ds.item = it.item
              GROUP BY ds.store, it.item, it.item_cost) AS item_profit
          GROUP BY store, item, profit
          QUALIFY percentile = 0) AS top_one_percent
    GROUP BY item
    HAVING COUNT(store) >= 20;

The results of this query might look like this:

              Item  Count(Store)
     ----------------  ------------
     Chilean sea bass           99
     Mackerel                  149
     Tako                      121
     Herring                   120
     Salmon                    143

Example: Subqueries Using the RANK Functions and QUALIFY Clauses

The following example uses nested RANK functions and QUALIFY clauses to report the top 100 items by profitability and the top items by revenue, matching those that appear in both lists using an OUTER JOIN:

     SELECT *
     FROM (SELECT item, profit, RANK(profit) AS profit_rank
           FROM item, sales
           QUALIFY profit_rank <= 100 AS p)
     FULL OUTER JOIN
          (SELECT item, revenue, RANK(revenue) AS revenue_rank
           FROM item, sales
           QUALIFY revenue_rank <= 100 AS r)
     ON p.item = r.item;

The results of this query might look something like the following table: