Table Alias Implementation

The query in Listing 13.5 is an example of the table alias type. The query in the script essentially uses the date dimension table twice, once for the order date (whose alias is order_date_dim) and once for the request delivery date (whose alias is request_delivery_date_dim).

Listing 13.5: Daily sales summary

 /**********************************************************************/ /*                                                                    */ /* table_alias.sql                                                    */ /*                                                                    */ /**********************************************************************/ SELECT   order_date_dim.date order_date , request_delivery_date_dim.date request_delivery_date , SUM (order_amount) , COUNT(*) FROM   sales_order_fact a , date_dim order_date_dim , date_dim request_delivery_date_dim WHERE     a.order_date_sk = order_date_dim.date_sk AND a.request_delivery_date_sk = request_delivery_date_dim.date_sk GROUP BY   order_date_dim.date , request_delivery_date_dim.date ORDER BY   order_date_dim.date , request_delivery_date_dim.date ; /* end of script                                                      */ 

You run the query using this command.

 mysql> \. c:\mysql\scripts\table_alias.sql

The result of the query is as follows.

 +-----------+-----------------------+--------------------+----------+ | order_date| request_delivery_date | SUM (order_amount) | COUNT(*) | +-----------+-----------------------+--------------------+----------+ | 2007-03-04| 2007-03-30            |            9500.00 |        3 | +-----------+-----------------------+--------------------+----------+ 1 row in set (0.00 sec)

The output shows the total of the three new orders whose request delivery dates are selected.