summaryrefslogtreecommitdiff
path: root/demos/northwind-db/protected
diff options
context:
space:
mode:
authorwei <>2007-05-09 02:51:02 +0000
committerwei <>2007-05-09 02:51:02 +0000
commite2614a35a70f609bccc1d65df6f1b92ff1fef5ef (patch)
treee50ba7375212d2275cc2d866d220adca254eadf1 /demos/northwind-db/protected
parent733cc375da74f9bf0fd3d71989bb20d503dbbb7b (diff)
Add compact db demo, add THtmlArea::EnableCompression, add ClientSide options for TColorPicker
Diffstat (limited to 'demos/northwind-db/protected')
-rw-r--r--demos/northwind-db/protected/data/Northwind.dbbin583680 -> 583680 bytes
-rw-r--r--demos/northwind-db/protected/database/sqlmap.xml107
2 files changed, 107 insertions, 0 deletions
diff --git a/demos/northwind-db/protected/data/Northwind.db b/demos/northwind-db/protected/data/Northwind.db
index 9d6b08b4..9503b1e2 100644
--- a/demos/northwind-db/protected/data/Northwind.db
+++ b/demos/northwind-db/protected/data/Northwind.db
Binary files differ
diff --git a/demos/northwind-db/protected/database/sqlmap.xml b/demos/northwind-db/protected/database/sqlmap.xml
new file mode 100644
index 00000000..bf98796e
--- /dev/null
+++ b/demos/northwind-db/protected/database/sqlmap.xml
@@ -0,0 +1,107 @@
+<sqlmap>
+
+ <select id="order-subtotal">
+ SELECT
+ "Order Details".OrderID as OrderID,
+ Sum(("Order Details".UnitPrice*Quantity*(1-Discount)/100)*100) AS Subtotal
+ FROM "Order Details"
+ GROUP BY "Order Details".OrderID
+ </select>
+
+ <!-- Show all the Cities we ship to or where a supplier is located -->
+ <select id="all-cities">
+ SELECT City FROM SUPPLIERS
+ Union
+ SELECT ShipCity FROM ORDERS
+ </select>
+
+ <!-- Find Suppliers that supply the categories such as 'Produce', 'Seafood', 'Condiments' -->
+ <select id="suppliers-with" parameterClass="array">
+ SELECT SupplierID, COUNT(P.CategoryID)
+ FROM (SELECT DISTINCT SupplierID, CategoryID FROM Products) P
+ INNER Join Categories C on C.CategoryID = P.CategoryID
+ WHERE CategoryName IN ('Produce', 'Seafood', 'Condiments')
+ GROUP BY SupplierID
+ HAVING COUNT(P.CategoryID) =
+ (SELECT COUNT(CategoryID)
+ from Categories
+ WHERE CategoryName
+ IN ('Produce', 'Seafood', 'Condiments'))
+ </select>
+
+ <!-- Show Cities we ship to that also have a supplier located there -->
+ <select id="supplier-cities">
+ SELECT DISTINCT ShipCity FROM ORDERS
+ WHERE EXISTS (SELECT 1 from SUPPLIERS WHERE ShipCity = City)
+ </select>
+
+ <!-- Show Cities we ship to that do not have a supplier located there -->
+ <select id="shipping-cities">
+ SELECT DISTINCT ShipCity FROM ORDERS
+ WHERE NOT EXISTS (SELECT 1 from SUPPLIERS WHERE ShipCity = City)
+ </select>
+
+ <!-- Show all possible Supplier Product Combinations -->
+ <select id="supplier-products">
+ SELECT * FROM Suppliers S CROSS Join Products
+ </select>
+
+ <!-- Products over a certain unit price -->
+ <select id="products-with-price">
+ <![CDATA[
+ SELECT
+ p.ProductName,
+ c.CategoryName,
+ p.UnitPrice
+ FROM Products p
+ INNER JOIN Categories c ON
+ c.CategoryID = p.CategoryID
+ WHERE p.UnitPrice < #value#
+ ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
+ ]]>
+ </select>
+
+ <!-- employee's manager's name and number of subordinates (if the employee has a manager) -->
+ <select id="employee-subordinates">
+ SELECT
+ Employee.LastName,
+ Employee.FirstName,
+ Employee.NumberOfSubordinates,
+ Manager.LastName as ManagerLastName,
+ Manager.FirstName as ManagerFirstName,
+ Manager.NumberOfSubordinates as ManagerNumberOfSubordinates
+ FROM EmployeeSubordinatesReport Employee
+ LEFT JOIN EmployeeSubordinatesReport Manager ON
+ Employee.ReportsTo = Manager.EmployeeID
+ </select>
+
+ <select id="pivot-test">
+ SELECT
+ o.customerID,
+ c.CompanyName,
+ p.productName,
+ sum(od.quantity) as Qty
+ FROM orders o
+ INNER JOIN
+ [order details] od on o.orderID = od.orderID
+ INNER JOIN
+ Products p on od.ProductID = p.ProductID
+ INNER JOIN
+ Customers c on o.CustomerID = c.CustomerID
+ GROUP BY
+ o.customerID, c.CompanyName, p.ProductName
+ </select>
+
+ <select id="employee-sales">
+ SELECT
+ e.firstName,
+ c.CompanyName,
+ COUNT(o.orderID)
+ FROM Employees e
+ JOIN Orders o ON e.employeeID=o.employeeID
+ JOIN Customers c ON c.customerID=o.customerID
+ GROUP BY e.firstName, c.CompanyName
+ ORDER BY e.firstName, c.CompanyName
+ </select>
+
+</sqlmap> \ No newline at end of file