classic CASE statement

UPDATE Books
   SET price = CASE WHEN price <= $25.00
      THEN price *1.10
      WHEN price > $25.00
      THEN price *0.85
      ELSE price END;

 
 

Here we make expensive books less so while making cheap books less so too. We have to have an ELSE as otherwise ELSE would be null. This is one of many SQL things that Joe Celko spoke about at the Austin .NET User Group last night in a presentation.

This was sort of a Scott Hanselmanesque talk that constantly went off on wild tangents assuming, wisely, short attention spans for audience members. Random things mentioned:

1. Joe’s advice: Don’t duplicate tables when you don’t need to. Boss and Employee are not two separate database tables. Boss is a kind of Employee. Joe had a story about a company that only made steel-toed work boots. From the perspective of manufacturing there was only one type of product. From the perspective of marketing there were two products however because construction workers bought the boots with shoe sizes of 12 or greater while Goth Girls! bought the boots with small shoe sizes. Different shoe sizes were sold to different types of stores. The big boots were not sold to Hot Topic I suppose. The gap between the two sizes allowed marketing to use the same database as manufacturing while merely refining its querying to account for shoe size in the name of understand data. The wrong thing to do in this case would have been to break MensBoots and GirlsBoots into two separate database tables.
2. It’s a good thing we finally got away from Roman numbers! When the church did not recognize the number zero one could have logic problems as highlighted by this syllogism:
   One cat has one more tails than no cat.
   No cat has twelve tails.
   Therefore, one cat has thirteen tails.
3. Joe recommend "The Manga Guide to Databases" as the best book on SQL!