BIT320 Texts

October 13, 2005

A Guide to SQL, Chapter 3

We start querying, the way to get useful information from your database.

Topics:

In this chapter you'll begin to see some of the power of SQL and Oracle. You're not going to see any foreign key or purely "relational" stuff yet - that'll come in the next chapter. However, never fear, this chapter will make you think a bit. In this chapter you begin the process of learning how to write the SQL select statements that we talked about toward the end of E-R modeling.

  1. Before you start 1: Make sure you complete the exercises in chapter 2 before starting this chapter. You will be querying against the tables you create in those exercises so you won't have anything to do if you haven't created the tables and put data into them.
  2. Before you start 2: In these few steps you're going to change the settings of sqlplus so that it prints the results of queries across a wider line. Currently, it is set to wrap output to a new line after 79 characters; we're going to change it so that it wraps after 128 characters.
    1. Use the SSH Secure Shell to log in to elab-linux4 and change to the SQL directory
    2. Maximize the SSH window
    3. Type pico login.sql
    4. Change the third line to set linesize 128
    5. Save the file and exit pico
    6. Start sqlplus
    7. Type select * from customer
    8. The results of this query should be around 100 characters and should not wrap to a new line. If so, then you successfully completed the above steps. If not, contact me.
  3. Examples: You should type these into sqlplus as you go. Pay attention to what you're typing to try to get a feel for how SQL select statements are structured.
  4. p78, after Note after Example 4: Yes, this is true - but there are two ways around it.
    1. Convert the values in the field to all upper case when comparing it to your target string. For example, you would type in the following instead of what's in Figure 3.4: 
      select num from customer where upper(last) = 'ADAMS';
      This ensures that, no matter how you enter the data (e.g., 'adams', 'ADAMS', 'Adams'), the select statement will retrieve the proper records.
    2. Type the values in as all upper case to begin with. You can then use Oracle functions to format the output as initial-caps (e.g., "Adams" instead of "ADAMS").
  5. p82, Figure 3.15: Notice the heading of the 4th column of the output. This is sorta ugly. If you want to make the meaning of this computation more clear, you can rename this column. Change the select statement to the following: 
    select customer_number, last, first, 
      (credit_limit - balance) "AVAILABLE CREDIT"
  from customer
  where credit_limit >= 1500;
    Look at the output. Now it doesn't even appear that the last column is any different than the other columns. Unless you wrote the query, you would never know that the last column was computed and not simply retrieved.
  6. p84, note: This is quite true, no doubt. You won't be using any large databases in this class. By "large" the author means one with millions of rows, not dozens or hundreds.
  7. p89, 2nd note: This is a really good point. When using a numeric field, you should be very careful about leaving it blank when you might really mean to put a zero in it. The "average" example that the author talks about in this paragraph is a good question to ask yourself: "If an average is calculated on this field, do I want this row to be ignored (then put a NULL in the field) or do I want it to count as zero (then put a zero in the field).
  8. p91, Figure 3.29: Make sure you understand this answer and why the previous answer is wrong.
  9. p91, Nesting Queries: This is where queries can get to be really really hairy. Hang on. These will become much clearer with practice.

  10. p100, exercises: Do all these exercises for Premiere Products. If you forget the name of a column, type DESC tableName (e.g., desc order_line), and Oracle will return the names of each field in that table. If you can't figure out how to do an exercise, ask someone sitting around you if you're in the lab or post a message to the remix site if you're not.

    For each exercise you should create a separate command file. Call it ex3-XX, where XX is the number of the exercise. To review: you type edit ex3-xx to create or edit the file, and @ex3-xx to run the file. Of course, feel free to just use jedit

    1. In question 8, be sure re-name the calculated field "ON_HAND_VALUE".
    2. For question 9, I tried the following: 
      select part_num, description, (on_hand * price) as on_hand_value
from part
where on_hand_value > 7500;
      Oracle doesn't like this. It appears that the re-naming of the column (in the first line) doesn't have any affect in the where clause. So I had to type 
      select part_num, description, (on_hand * price) as on_hand_value
from part
where (on_hand * price) > 7500;
      This works just fine.

Bud edited this on October 13, 2005

Comments

Post a comment




Remember Me?