Banner

 

14 - Organizational Intelligence
Slide Exercises

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
Departure date and time
Season level (high, medium, low)
Weather

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
For questions and comments please contact the author

Date revised: 02-Dec-2022