Queries

In this section I'll show how the calculus notation can be used to formulate queries, using examples from Chapter 5. Again, please note that the formulations shown aren't the only ones possible, in general.

Example 1

Get suppliers in Paris.

     s WHERE s.CITY = 'Paris' 

Example 2

Get suppliers who supply at least one part.

     s WHERE EXISTS sp ( sp.SNO = s.SNO ) 

Example 3

Get all supplier name/status/city combinations.

     s.SNAME, s.STATUS, s.CITY 

We could harmlessly add WHERE TRUE to the foregoing formulation.

Example 4

Get the join of suppliers and shipments.

     s, sp.PNO, sp.QTY WHERE s.SNO = sp.SNO 

Example 5

Get the union of supplier cities and part cities.

     u RANGES OVER { s.CITY, p.CITY }     u 

Bound variable u is defined to range over the union of supplier cities and part cities.

Example 6

Get suppliers who supply no parts at all.

     s WHERE NOT ( EXISTS sp ( sp.SNO = s.SNO ) ) 

Example 7

Get parts with their weight in grams.

     p, ( p.WEIGHT * 454 ) AS GMWT 

Example 8

For each supplier, get the supplier number and a count of the number of parts that supplier supplies.

     s.SNO, COUNT ( sp WHERE sp.PNO = s.SNO ) AS P_COUNT