Database Reference
In-Depth Information
Product
Supplier
City
ProductKey
ProductName
QuantityPerUnit
UnitPrice
Discontinued
CategoryKey
SupplierKey
CompanyName
Address
PostalCode
CityKey
CityKey
CityName
StateKey (0,1)
CountryKey (0,1)
Customer
CustomerKey
CustomerID
CompanyName
Address
PostalCode
CityKey
State
Sales
StateKey
StateName
EnglishStateName
StateType
StateCode
StateCapital
RegionName (0,1)
RegionCode (0,1)
CountryKey
AK: CustomerID
Category
CustomerKey
EmployeeKey
OrderDateKey
DueDateKey
ShippedDateKey
ShipperKey
ProductKey
SupplierKey
OrderNo
OrderLineNo
UnitPrice
Quantity
Discount
SalesAmount
Freight
CategoryKey
CategoryName
Description
Territories
EmployeeKey
CityKey
Time
Country
Employee
TimeKey
Date
DayNbWeek
DayNameWeek
DayNbMonth
DayNbYear
WeekNbYear
MonthNumber
MonthName
Quarter
Semester
Year
CountryKey
CountryName
CountryCode
CountryCapital
Population
Subdivision
ContinentKey
EmployeeKey
FirstName
LastName
Title
BirthDate
HireDate
Address
City
Region
PostalCode
Country
SupervisorKey
AK: (OrderNo,
OrderLineNo)
Shipper
Continent
ShipperKey
CompanyName
ContinentKey
ContinentName
AK: Date
Fig. 6.3 Schema of the Northwind data warehouse (repeated from Fig. 5.4 )
Query 6.2. Yearly sales amount for each pair of customer country and
supplier countries.
SELECT CO.CountryName AS CustomerCountry,
SO.CountryName AS SupplierCountry, T.Year,
FORMAT(SUM(SalesAmount), ' $###,##0.00 ' )ASSalesAmount
FROM Sales F, Customer C, City CC, State CS, Country CO,
Supplier S, City SC, State SS, Country SO, Time T
WHERE F.CustomerKey = C.CustomerKey AND C.CityKey = CC.CityKey AND
CC.StateKey = CS.StateKey AND
CS.CountryKey = CO.CountryKey AND
F.SupplierKey = S.SupplierKey AND S.CityKey = SC.CityKey AND
SC.StateKey = SS.StateKey AND
SS.CountryKey = SO.CountryKey AND F.OrderDateKey = T.TimeKey
GROUP BY CO.CountryName, SO.CountryName, T.Year
ORDER BY CO.CountryName, SO.CountryName, T.Year
 
Search WWH ::




Custom Search