| Lesson 3 | Report Course project |
| Objective | Generate a report that outlines the best sales associates. |
SQL Report Course Project
For the course project, put yourself in the shoes of the database programmer. You have just been asked by your manager to retrieve a listing of sales by sales associate. You only want those sales associates that have sold the product with the highest sales, based on overall quantity.
Grouping Rows using GROUP BY
I have used aggregate functions to summarize all the values in a column or just those values that matched a WHERE search clause.
You can use the GROUP BY clause to divide a table into logical groups (categories) and calculate aggregate statistics for each group. List the number of books each author wrote (or cowrote). See Figure 5.3 for the result.
SELECT
au_id,
COUNT(*) AS "num_books"
FROM title_authors
GROUP BY au_id;
au_id num_books
----- ---------
A01 3
A02 4
A03 2
A04 4
A05 1
A06 3
Figure 5.3 Result of Listing 5.3
Reports for Output Use Cases
Snapshots are important in data warehouses, distributed systems, and many other contexts. In all such cases, the reasoning is that applications can often tolerate "in some cases even require" data as of some particular point in time.
Reporting and accounting applications are a case in point; such applications typically require the data to be frozen at an appropriate moment (for example, at the end of an accounting period), and snapshots allow such freezing to occur without locking out other applications.
The problem is, snapshots have come to be known (at least in some circles) not as snapshots at all but as materialized views. However, they are not views.
Views are not supposed to be materialized at all.
Operations on views are supposed to be implemented by mapping them into suitable operations on the underlying relational variables. Thus, materialized view is simply a contradiction in terms.
Worse yet, the unqualified term view is now often taken to mean a materialized view.
At least in some circles, so we are in danger of no longer having a good term to mean a view in the original sense. I use the term view in its original sense, but be warned that it does not always have that meaning elsewhere.
Recommendations: Never use the term view, unqualified, to mean a snapshot; never use the term materialized view; and watch out for violations of these recommendations on the part of others
Course Project - Exercise