This tutorial shows in a simple Cω program how to SQL quantifiers such as the ANY, ALL and EXISTS keywords.
To run this tutorial, you may use the following project and source files:
These files are located in the \samples\SQL\Quantifiers subdirectory under the path where you installed Cω, which by default is C:\Program Files\Microsoft Research\Comega.
To run this sample, you need to have a SQL configuration file in the same directory as the Select.exe you make in Visual Studio. Here are the contents of the version of this file that Cω provided you as a starting point:
<configuration>
<appSettings>
<add key="Northwind" value="Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI" />
</appSettings>
</configuration>
Verify that this is the correct SQL connection string for connecting to the Northwind database on your SQL Server 2000 installation. If necessary, you can modify the value attribute in the <add> element
When you have verified it is correct, press F5 in Visual Studio to make and run the sample.
Quantifiers are comparison operators used to test for the quantity or existence of operand values within SQL Select expressions. The following table describes the usage of some of these operators:
| Quantifier | Usage |
|---|---|
| ALL | Use to test for presence of all non-scalar operands in a Boolean expression. |
| ANY | Use to test for presence of any of a non-scalar operand in a Boolean expression. |
| EXISTS | Use to determine if a collection is non-empty or Null. |
The following is a complete Cω program that demonstrates how to use SQL quantifiers.
using System;
using System.Data.SqlTypes;
using System.Query;
using Northwind;
public class Test {
static void Main() {
// find all orders where the freight costs is more than all individual line item costs
results =
select distinct x.OrderID
from x in DB.Orders
where x.Freight > all(select Quantity * UnitPrice from DB.OrderDetails where x.OrderID == OrderID);
Console.WriteLine("Orders that have freight costs exceeding all line item costs");
foreach( row in results ) {
Console.Write("{0}\t", row.OrderID);
}
// find all orders where the freight cost is more than any line item cost
results =
select distinct x.OrderID
from x in DB.Orders
where x.Freight > any(select Quantity * UnitPrice from DB.OrderDetails where x.OrderID == OrderID);
Console.WriteLine("\n\nOrders that have freight costs exceeding any line item cost");
foreach( row in results ) {
Console.Write("{0}\t", row.OrderID);
}
// find all orders that have a line item with quantity > 100 items
short n = 100;
results =
select distinct x.OrderID
from x in DB.Orders
where exists(select * from v in DB.OrderDetails where x.OrderID == v.OrderID && v.Quantity > n);
Console.WriteLine("\n\nOrders that have line items with quantity > 100");
foreach( row in results ) {
Console.Write("{0}\t", row.OrderID);
}
Console.Write("\n\nPress ENTER to continue...");
Console.ReadLine();
}
}
Orders that have freight costs exceeding all line item costs 10983 Orders that have freight costs exceeding any line item cost 10252 10263 10273 10283 10284 10294 10304 10309 10316 10325 10329 10340 10341 10343 10345 10351 10353 10368 10372 10387 10393 10406 10417 10420 10421 10424 10430 10436 10447 10462 10465 10469 10474 10487 10510 10514 10515 10518 10523 10524 10540 10553 10555 10558 10561 10572 10574 10593 10600 10605 10607 10612 10623 10633 10634 10643 10654 10656 10657 10666 10670 10678 10687 10688 10691 10698 10703 10722 10735 10742 10747 10762 10768 10773 10774 10776 10781 10784 10792 10800 10802 10805 10814 10829 10831 10832 10835 10836 10841 10847 10850 10851 10866 10893 10909 10924 10961 10979 10983 10984 10986 11001 11007 11009 11013 11017 11021 11023 11031 11036 11038 11045 11055 11058 11072 Orders that have line items with quantity > 100 10398 10451 10515 10595 10678 10711 10713 10764 10776 10894 10895 11017 11072 Press ENTER to continue...