SQL PROGRAMMING STYLE- P10:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | 172 CHAPTER 9 HEURISTICS CREATE TABLE Orders order_nbr INTEGER NOT NULL CREATE TABLE OrdersDetails order_nbr INTEGER NOT NULL REFERENCES Orders order_nbr ON UPDATE CASCADE ON DELETE CASCADE sku CHAR 10 NOT NULL REFERENCES Inventory sku ON UPDATE CASCADE ON DELETE CASCADE description CHAR 20 NOT NULL qty INTEGER NOT NULL CHECK qty 0 unit_price DECIMAL 12 4 NOT NULL Put the Specification into a Clear Statement This might sound obvious but the operative word is clear statement. You need to ask questions at the start. Let me give some examples from actual problem statements having to do with a schema that models a typical orders and order details database 1. I want to see the most expensive item in each order. How do I handle ties for the most expensive item Did you mean the highest unit price or the highest extension quantity x unit price on each order 2. I want to see how many lawn gnomes everyone ordered. How do I represent someone who never ordered a lawn gnome in the result set Is that a NULL or a zero If they returned all of their lawn gnomes do I show the original order or the net results Or do I show no order ever as a NULL and returns as a zero to preserve information 3. How many orders were over 100 Did you mean strictly greater than 100 or greater than or equal to 100 Add the Words Set of All. in Front of the Nouns 173 In the Dance Partner example we need to ask 1. How do we pair the couples 2. What do we do if there are more boys than girls or vice versa in the table 3. Can someone have more than one partner If so how do we assign them Writing specs is actually harder than writing code. Given a complete clear specification the code can almost write itself. Add the Words Set of All. in Front of the Nouns The big leap in SQL programming is thinking in sets and not in process steps that handle one unit of data at a time. Phrases like for each x. poison your mental model of the problem. Look for set characteristics and not for individual .
