| 1 |
lars |
1 |
<sqlmap>
|
|
|
2 |
|
|
|
3 |
<select id="order-subtotal">
|
|
|
4 |
SELECT
|
|
|
5 |
"Order Details".OrderID as OrderID,
|
|
|
6 |
Sum(("Order Details".UnitPrice*Quantity*(1-Discount)/100)*100) AS Subtotal
|
|
|
7 |
FROM "Order Details"
|
|
|
8 |
GROUP BY "Order Details".OrderID
|
|
|
9 |
</select>
|
|
|
10 |
|
|
|
11 |
<!-- Show all the Cities we ship to or where a supplier is located -->
|
|
|
12 |
<select id="all-cities">
|
|
|
13 |
SELECT City FROM SUPPLIERS
|
|
|
14 |
Union
|
|
|
15 |
SELECT ShipCity FROM ORDERS
|
|
|
16 |
</select>
|
|
|
17 |
|
|
|
18 |
<!-- Find Suppliers that supply the categories such as 'Produce', 'Seafood', 'Condiments' -->
|
|
|
19 |
<select id="suppliers-with" parameterClass="array">
|
|
|
20 |
SELECT SupplierID, COUNT(P.CategoryID)
|
|
|
21 |
FROM (SELECT DISTINCT SupplierID, CategoryID FROM Products) P
|
|
|
22 |
INNER Join Categories C on C.CategoryID = P.CategoryID
|
|
|
23 |
WHERE CategoryName IN ('Produce', 'Seafood', 'Condiments')
|
|
|
24 |
GROUP BY SupplierID
|
|
|
25 |
HAVING COUNT(P.CategoryID) =
|
|
|
26 |
(SELECT COUNT(CategoryID)
|
|
|
27 |
from Categories
|
|
|
28 |
WHERE CategoryName
|
|
|
29 |
IN ('Produce', 'Seafood', 'Condiments'))
|
|
|
30 |
</select>
|
|
|
31 |
|
|
|
32 |
<!-- Show Cities we ship to that also have a supplier located there -->
|
|
|
33 |
<select id="supplier-cities">
|
|
|
34 |
SELECT DISTINCT ShipCity FROM ORDERS
|
|
|
35 |
WHERE EXISTS (SELECT 1 from SUPPLIERS WHERE ShipCity = City)
|
|
|
36 |
</select>
|
|
|
37 |
|
|
|
38 |
<!-- Show Cities we ship to that do not have a supplier located there -->
|
|
|
39 |
<select id="shipping-cities">
|
|
|
40 |
SELECT DISTINCT ShipCity FROM ORDERS
|
|
|
41 |
WHERE NOT EXISTS (SELECT 1 from SUPPLIERS WHERE ShipCity = City)
|
|
|
42 |
</select>
|
|
|
43 |
|
|
|
44 |
<!-- Show all possible Supplier Product Combinations -->
|
|
|
45 |
<select id="supplier-products">
|
|
|
46 |
SELECT * FROM Suppliers S CROSS Join Products
|
|
|
47 |
</select>
|
|
|
48 |
|
|
|
49 |
<!-- Products over a certain unit price -->
|
|
|
50 |
<select id="products-with-price">
|
|
|
51 |
<![CDATA[
|
|
|
52 |
SELECT
|
|
|
53 |
p.ProductName,
|
|
|
54 |
c.CategoryName,
|
|
|
55 |
p.UnitPrice
|
|
|
56 |
FROM Products p
|
|
|
57 |
INNER JOIN Categories c ON
|
|
|
58 |
c.CategoryID = p.CategoryID
|
|
|
59 |
WHERE p.UnitPrice < #value#
|
|
|
60 |
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC
|
|
|
61 |
]]>
|
|
|
62 |
</select>
|
|
|
63 |
|
|
|
64 |
<!-- employee's manager's name and number of subordinates (if the employee has a manager) -->
|
|
|
65 |
<select id="employee-subordinates">
|
|
|
66 |
SELECT
|
|
|
67 |
Employee.LastName,
|
|
|
68 |
Employee.FirstName,
|
|
|
69 |
Employee.NumberOfSubordinates,
|
|
|
70 |
Manager.LastName as ManagerLastName,
|
|
|
71 |
Manager.FirstName as ManagerFirstName,
|
|
|
72 |
Manager.NumberOfSubordinates as ManagerNumberOfSubordinates
|
|
|
73 |
FROM EmployeeSubordinatesReport Employee
|
|
|
74 |
LEFT JOIN EmployeeSubordinatesReport Manager ON
|
|
|
75 |
Employee.ReportsTo = Manager.EmployeeID
|
|
|
76 |
</select>
|
|
|
77 |
|
|
|
78 |
<select id="pivot-test">
|
|
|
79 |
SELECT
|
|
|
80 |
o.customerID,
|
|
|
81 |
c.CompanyName,
|
|
|
82 |
p.productName,
|
|
|
83 |
sum(od.quantity) as Qty
|
|
|
84 |
FROM orders o
|
|
|
85 |
INNER JOIN
|
|
|
86 |
[order details] od on o.orderID = od.orderID
|
|
|
87 |
INNER JOIN
|
|
|
88 |
Products p on od.ProductID = p.ProductID
|
|
|
89 |
INNER JOIN
|
|
|
90 |
Customers c on o.CustomerID = c.CustomerID
|
|
|
91 |
GROUP BY
|
|
|
92 |
o.customerID, c.CompanyName, p.ProductName
|
|
|
93 |
</select>
|
|
|
94 |
|
|
|
95 |
<select id="employee-sales">
|
|
|
96 |
SELECT
|
|
|
97 |
e.firstName,
|
|
|
98 |
c.CompanyName,
|
|
|
99 |
COUNT(o.orderID)
|
|
|
100 |
FROM Employees e
|
|
|
101 |
JOIN Orders o ON e.employeeID=o.employeeID
|
|
|
102 |
JOIN Customers c ON c.customerID=o.customerID
|
|
|
103 |
GROUP BY e.firstName, c.CompanyName
|
|
|
104 |
ORDER BY e.firstName, c.CompanyName
|
|
|
105 |
</select>
|
|
|
106 |
|
|
|
107 |
</sqlmap>
|