Lesson 4

The key to SQL reporting is to break apart the request and find out what information is needed and where it needs to come from. In the course project, we had extra tables. Neither the Customer nor the Inventory tables were needed at all. You will often find that this is the case in your queries for reports. You will find that people just "want what they want" and do not really know nor care how the information is retrieved. You will usually be given the request, and then you need to figure out how to get the information.
You can use all these techniques to build the queries that will feed your

1. Access report writer,
2. the Excel pivot tables, or
3. build your own report generator.

The queries provide a solid foundation for getting better information out of the systems that put the information in. Study the database layout carefully. Look for the keys between tables, and determine the relationships. Finally, break apart the requirements for reporting that you have. Start at the most finite bit of information that you need to determine and work from there to the most general information. Be sure you go back through the functions, GROUP BY clauses, and many other elements we have covered this course.

Reporting applications generally require aggregated data, and views are a great way to make it appear as though data is being pre-aggregated and stored in the database. As an example, let us say that an application generates a report each month showing the number of accounts and total deposits for every customer. Rather than allowing the application developers to write queries against the base tables, you could provide them with the following view:

CREATE VIEW customer_totals_vw
(cust_id,
cust_type_cd,
cust_name,
num_accounts,
tot_deposits
)
AS
SELECT cst.cust_id, cst.cust_type_cd,
CASE
WHEN cst.cust_type_cd = 'B' THEN
(SELECT bus.name FROM business bus WHERE bus.cust_id = cst.cust_id)
ELSE
(SELECT concat(ind.fname, ' ', ind.lname)
FROM individual ind
WHERE ind.cust_id = cst.cust_id)
END cust_name,
sum(CASE WHEN act.status = 'ACTIVE' THEN 1 ELSE 0 END) tot_active_accounts,
sum(CASE WHEN act.status = 'ACTIVE' THEN act.avail_balance ELSE 0 END) tot_balance
FROM customer cst INNER JOIN account act
ON act.cust_id = cst.cust_id
GROUP BY cst.cust_id, cst.cust_type_cd;

Using this approach gives you a great deal of flexibility as a database designer. If you decide at some point in the future that query performance would improve dramatically if the data were preaggregated in a table rather than summed using a view, you can create a customer_totals table and modify the customer_totals_vw view definition to retrieve data from this table. Before modifying the view definition, you can use it to populate the new table. Here are the necessary SQL statements for this scenario:

mysql> CREATE TABLE customer_totals
-> AS
-> SELECT * FROM customer_totals_vw;
Query OK, 13 rows affected (3.33 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> CREATE OR REPLACE VIEW customer_totals_vw
-> (cust_id,
-> cust_type_cd,
-> cust_name,
-> num_accounts,
-> tot_deposits
-> )
-> AS
-> SELECT cust_id, cust_type_cd, cust_name, num_accounts, tot_deposits
-> FROM customer_totals;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)

From now on, all queries that use the customer_totals_vw view will pull data from the new customer_totals table, meaning that users will see a performance improvement without needing to modify their queries.