# 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: Relational Theory for Practitioners
ISBN: 0596100124
EAN: 2147483647
Year: 2006
Pages: 127
Authors: C.J. Date

Similar book on Amazon