BIT320 Texts

October 13, 2005

A Guide to SQL, Chapter 4

We dive into multitable queries.

Topics:

This is the chapter in which you get to explore the "relational" part of relational databases. This material is critical for the test and the project.

  1. p110, Using Exists: This is useful information. There is an important concept introduced on p111, correlated subqueries. Understanding this concept will stand you in good stead when you are working on the project. One good feature of subqueries is that they allow you to break up the logic of a more complicated query. I typically think of queries in parts. Step 1 "Find this", then Step 2 "Use the thing found in Step 1 to help with finding a new thing".
  2. p112–113, Figures 4.8 & 4.9: I would formulate the query as in 4.8, not 4.9. Both are good but I think in the way shown in 4.8.
  3. p114, Figure 4.10: You should be able to write this query - if not now, then soon. It's really not that complicated, just so long as you put it together in pieces. You would write the select, from, and where clauses first. You would execute this statement to ensure that it works. Then you would add the order by clause since that's not very complicated. Execute it to see if it works. Then you would add the group by clause and test it. Finally, you would add the having clause and test it. Then it works and is a fairly complicated query, but it's not so bad to write if you approach it in pieces.
  4. p114, Using an Alias: I use aliases all the time. Nothing exciting about them, just useful.
  5. p115, Example 9: You should read this example very carefully. Focus on the "Question and Answer" on page 117 also.
  6. p115, Example 10: Remind yourself that you need to build this type of long query piece by piece, as described in my note for Figure 4.10. Don't try to do it all at once. And, if you had an ER diagram for this problem, you would be able to trace a path through the diagram; you could then use this path to determine how to connect the primary and foreign keys.
  7. p120, Figure 4.16: This is exactly the type of query in which you should use aliases when constructing it. It would make it much shorter, less cluttered looking, easier to read.
  8. p121, beginning after the Q&A: Well, what do you know! This is exactly the point I've been trying to make. Must be a coincidence!
  9. p121, Set operations: Be sure that you know the differences among the three set commands. Be doubly sure that you follow the examples and figures in this section. (BTW: for me, this is where I really begin to get the idea that SQL is pretty cool and is a pretty powerful way of interacting with a database. The next section only reinforces these feelings.)
  10. p134-135, Exercises (Premiere Products)Do them all. Every single one of them. You actually don't have to. Only if you want your project grade to be halfway decent. (A little prof humor there). This is really important and powerful stuff that you should be (or get) comfortable with.
  11. Cross-product queries. One thing not mentioned is what happens if you include columns from two tables but do not link the two tables in the where clause of the query. Try it like this: 
    select description, customer_num
      from part, customer
      where customer_nubmer = '462';
    Why do you think this result happened? How is oracle matching customer with part? Note, this example is trivial. It is easy to make this sort of error in complicated queries. My advice, in small databases (like the one in the project) where you know what the data is, you can easily check the query results with what you know the answer should be. Do this.
  12. p134, Exercises:
    1. Ex 1: Begin to get in the habit of using aliases in any query that joins more than one table. Also, use the alias name with every field name throughout the query. Why? Because it's the right thing to do and you should trust your professor. Need more than that? Because if you add another table to the query after its initial formulation, it's very possible that a field from the new table might have the same name as a field from a table already in the query. Which field? Don't know. You'll have to go through all the fields in all the tables to find the ones with the same names. Don't want to do that? Then use aliases like I said. It's saved me countless hours. Further, in my opinion, queries that use aliases correctly and well are easier to read than those that don't.
    2. Ex 4 and 5: Do these two queries produce the same answer for you? If not, why? What could you do to get them to produce the same answer?
    3. Ex 8: As a starting point for this exercise, you should copy your answer from the previous exercise. To do this, type 
      cp ex4-07.sql ex4-08.sql
      Then edit ex4-08.

Bud edited this on October 13, 2005

Comments

Post a comment




Remember Me?