An international hotel chain has asked you to design a multidimensional database for its marketing department. What identifier and variable dimensions would you select?
Apply the basic prompts outlined in the book.
| Identifiers | When | Arrival date and time |
| Where | Country Region City |
|
| What | Room type Hotel class (stars) |
|
| How | Reservation channel (phone, Web, mobile, fax) | |
| Variables | Outcomes | Duration of stay Room revenue Meals revenue Amenities revenue |
Compute total payments by country without and with ROLLUP
SELECT DISTINCT country, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments WHERE Customers.customerNumber = Payments.customerNumber GROUP BY country;
SELECT country, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments WHERE Customers.customerNumber = Payments.customerNumber GROUP BY country WITH ROLLUP;
Compute total payments by country and year without and with ROLLUP
SELECT DISTINCT country, YEAR(paymentDate) AS year, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments WHERE Customers.customerNumber = Payments.customerNumber GROUP BY country, year;
SELECT country, YEAR(paymentDate) AS year, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments WHERE Customers.customerNumber = Payments.customerNumber GROUP BY country, year WITH ROLLUP;
Compute total value of orders by country and product line without and with ROLLUP
SELECT DISTINCT country AS 'Country', ProductLines.productLine, FORMAT(sum(quantityOrdered*priceEach),0) AS 'Order value' FROM Customers, Orders, OrderDetails, Products, ProductLines WHERE Customers.customerNumber = Orders.customerNumber AND Orders.orderNumber = OrderDetails.orderNumber AND OrderDetails.productCode = Products.productCode AND Products.productLine = ProductLines.productLine GROUP BY country, ProductLines.productLine, 'Order value';
SELECT country AS 'Country', ProductLines.productLine, FORMAT(sum(quantityOrdered*priceEach),0) AS 'Order value' FROM Customers, Orders, OrderDetails, Products, ProductLines WHERE Customers.customerNumber = Orders.customerNumber AND Orders.orderNumber = OrderDetails.orderNumber AND OrderDetails.productCode = Products.productCode AND Products.productLine = ProductLines.productLine GROUP BY country, ProductLines.productLine, 'Order value' WITH ROLLUP;
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson |