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 



Database in Depth
Database in Depth: Relational Theory for Practitioners
ISBN: 0596100124
EAN: 2147483647
Year: 2006
Pages: 127
Authors: C.J. Date

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net