Answers to Exercises

SQL translation of "Show the unitprice of the 3 most expensive products in each category."  Back to Exercise

SELECT DISTINCT Products.UnitPrice , Categories.CategoryName , Products.ProductName FROM Categories , Products , Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID ; SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Beverages' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) UNION SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Condiments' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) UNION SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Confections' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) UNION SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Dairy Products' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) UNION SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Grains/Cereals' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) UNION SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Meat/Poultry' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) UNION SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Produce' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) UNION SELECT UnitPrice AS Lim, CategoryName FROM ( SELECT TOP 1 UnitPrice, CategoryName FROM [ SELECT TOP 3 UnitPrice, CategoryName FROM elfQ1 WHERE CategoryName='Seafood' ORDER BY UnitPrice DESC ]. AS Sub1 ORDER BY UnitPrice ASC) ; SELECT DISTINCT max ( elfQ1.UnitPrice ) AS Lim , elfQ1.CategoryName FROM elfQ1 group by elfQ1.CategoryName ; SELECT elfQ1.* FROM elfQ1 INNER JOIN elfQ2 ON elfQ1.UnitPrice >= elfQ2.Lim and elfQ1.CategoryName = elfQ2.CategoryName ;

SQL translation of "Which companies buy seafood but not dairy?" The second step is the one we're interested in here. It's an example of a nested query (one SQL statement inside another). This kind of SQL statement (called "correlated subqueries") can be edited using the Edit SubQ button of the Worksheet.   Back to Exercise

SELECT DISTINCT Customers.CompanyName FROM Orders , Customers , [Order Details] , Products , Categories , Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID , Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID , [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID , Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID group by Customers.CompanyName HAVING sum ( IIf( ( Categories.CategoryName = "Seafood" ) ,-1,0) ) <> 0 ;

SELECT DISTINCT NX1.CompanyName FROM Customers AS NX1 WHERE NOT EXISTS ( SELECT DISTINCT Customers.CompanyName FROM Orders , Customers , [Order Details] , Products , Categories , Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID , Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID , [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID , Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE ( Categories.CategoryName = "Dairy Products" and Customers.CompanyName = NX1.CompanyName ) ) ;

SELECT DISTINCT elfQ1.* FROM elfQ1 , elfQ2 , elfQ1 INNER JOIN elfQ2 ON elfQ1.CompanyName = elfQ2.CompanyName ;

SELECT DISTINCT Customers.CompanyName , Categories.CategoryName , Orders.ShipName , Products.ProductName , [Order Details].OrderID FROM elfQ3 , Orders , Customers , [Order Details] , Products , Categories , Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID , Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID , [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID , Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID , Customers INNER JOIN elfQ3 ON Customers.CompanyName = elfQ3.CompanyName WHERE ( Categories.CategoryName = "Seafood" ) ;

In a previous release we were unable to handle multiple aggregates in a single query. This question -- "Show the total quantity and average discount for seafood products." -- was used as an example of how to extend Access ELF's translation capabilities via the Worksheet edit tools. While this particular question now presents no problem, the example is still instructive.  Back to Exercise

"Show the total quantity and average discount for seafood products."

. . . . But using these new features, we can adapt another query to our needs very quickly. With Worksheet mode selected, enter
Show the quantity and discount for each seafood product
Now click the Add button. By default, a new query is opened with the * representing all fields of the previous query (in this case elfQ1). Since we want to create an aggregate query, we'll need to delete the * column and add in all 3 columns from elfQ1, Quantity, Discount and ProductName. Click the Aggregate (Summa) button on the Access toolbar so that the Total line appears in the QBE grid. Choose Sum for Quantity and Avg for Discount. Now since Discount is a percentage, we'll need to multiply it by 100 to get an accurate graph. In fact, when compared to the Sum of the Quantities in the Northwind database, Discounts are rather small values, making for an unbalanced graph. So we can multiply it by another 100 just for the visual effect. So now the top line of the column should read: AvgDiscount: 10000*Discount, and its Total row should say Avg. Save this as elfQ2 (the default name).

Switch into Split View mode and voila, a graph of both the sum of quantities for each seafood product, and the average discount -- corrected for visual balance. As data mining goes, we're not sure this example says anything profound about the Northwind database, but it does illustrate the use of these customization features. By clicking on the More Options label, you can try out the use of the Graph Selected Columns button to get views of the individual aggregates; for instance, by highlighting the SumOfQuantity field only and showing its graph. Double-click an item to remove it from the list; when the list is empty Access ELF reverts back to its own decisions about what fields to graph. (Note that Access ELF will not attempt to graph numeric values which are used as keys to connect tables, also known as join keys.)