THE BIG SIX

Which expressions are necessary?
Examples:
– Pre-existing columns/fields/attributes
– Derived attributes (e.g., attributes resulting from built-in functions, concatenation of first and last name)
– Scalar values (e.g., 1.10, 100, -10, etc.)
– An expression that is any combination of those examples above

Tip: Specify only those expressions required to solve your problem. Avoid using SELECT * to retrieve all pre-existing attributes in any matters other than exploratory. Less is more when it comes to query processing.

SELECT
	hr.employees.employee_id, -- pre-existing attribute
    (
    	hr.employees.first_name
        || ' '
        || hr.employees.last_name
    ) AS full_name, -- derived attribute
    SYSDATE AS date_value, -- built-in function
    hr.employees.salary, -- pre-existing attribute
    hr.employees.salary * 1.10 AS new_salary
FROM
	hr.employees;

What is the data source?
Examples:
– Pre-existing table, view, or temporary table
– Derived table/inline-view (i.e., subquery result set)
– Common table expression

Tip: Fully-qualify pre-existing tables in the FROM clause and their attributes in the SELECT clause by specifying the schema for which they belong. This is important, not only for readability, but also to avoid ambiguity errors because there will be instances where multiple tables and attributes across different schemas share the same name.

SELECT
	oe.orders.order_id,
    oe.orders.customer_id,
    oe.orders.order_date,
	oe.orders.order_total
FROM -- pre-existing table in oe schema
	oe.orders; 

Do any filters need to be applied to the data?
Examples:
– Applying filters based on a pre-existing attribute’s values
– Applying filters based on a derived value
– Applying filters based on the result of a subquery
– Applying filters that are any combination of those examples above

Tip: Use this clause assist you in retrieving the minimum amount of data required to solve your problem.

SELECT
	oe.orders.order_id,
    oe.orders.customer_id,
    oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
    oe.orders.order_total >= 50000
    AND oe.orders.order_total <= 100000;

Does the data need to be grouped?
Rows are grouped based on one or more shared attribute values and a calculation is usually performed across each set of grouped rows. Each set of grouped rows are collapsed into a single row.
Examples:
– Group rows based on their customer ID values and compute the sum of sales across each set of grouped rows, resulting in sales by customer ID
– Group rows based on employee job title and location values and compute the average salary across each set of grouped rows, resulting in the average salary for each job title and location combination

Tip: You will come across code where the GROUP BY clause is not accompanied by an aggregation (e.g., SUM, MIN, MAX, etc.) in the SELECT clause. This is usually because the author has opted to use the GROUP BY clause to address duplicates in the data based on the specified combinations. In this very specific case, I suggest you use the DISTINCT keyword to make the intent of your code clear. The query should be processed the same using either method.

SELECT
    oe.orders.customer_id,
	SUM(oe.orders.order_total) AS total_sales
FROM
	oe.orders
GROUP BY
	oe.orders.customer_id;

Do any filters need to be applied to the resulting grouped rows?
Typically, the GROUP BY function is accompanied by an aggregate function. The HAVING clause can be used to apply filters based on the results of aggregate functions.
Examples:
– Applying filters based on the aggregation of pre-existing attribute values
– Applying filters based on the aggregation of derived values
– Applying filters based on the result of a subquery
– Applying filters that are any combination of those examples above

Tip: Use this clause assist to you in retrieving the minimum amount of data required to solve your problem. Remember, the HAVING clause should be used to apply filter conditions on GROUPED rows while the WHERE clause should be used apply filter conditions on UNGROUPED rows.

SELECT
    oe.orders.customer_id,
	SUM(oe.orders.order_total) AS total_sales
FROM
	oe.orders
GROUP BY
	oe.orders.customer_id
HAVING
    SUM(oe.orders.order_total) > 100000;

Does the output of the query need to be sorted?
Examples:
– Sorting the output of the query by total sales, descending (i.e., greatest to least)
– Sorting the output of the query by total number of orders placed

Tip: Sorting the output of a query can be a costly operation. In today’s business intelligence environment, your queries will likely originate from a data visualization tool such as Tableau or Power BI. Avoid including an ORDER BY clause in these queries because it is likely you and the consumers of your reports will be using the tool to sort the data displayed dynamically. In the case of Tableau, an ORDER BY clause is not allowed when using the tool’s Custom SQL feature.

SELECT
    oe.orders.customer_id,
	SUM(oe.orders.order_total) AS total_sales
FROM
	oe.orders
GROUP BY
	oe.orders.customer_id
HAVING
    SUM(oe.orders.order_total) > 100000
ORDER BY
    total_sales DESC;