A Guide to SQL, Chapter 2

The key point is to establish good SQL techniques early and practice them.

Topics: SQL

Much of the "concept" stuff at the beginning of this chapter you will already be familiar with.

Our book covers multiple database systems: Oracle, Access, and MySQL. You only need to be concerned with the stuff covering Oracle. The basics of SQL are the same across all three, but the details of interacting with them are different.

1. The stuff under "Entities, Attributes, and Relationships" should be very familiar to you. Look how far you've already come this semester!
2. p27: Notice the name of the field rep.rep_number. I would have named the field (using the first four rules from the document Defining a database's tables and fields) as rep.num. Read this other document to help you understand my reasoning. BTW, I didn't use number as the field name because it is a reserved word in SQL (like table, order, integer, etc.). You have to be careful about these; they can trip you up sometimes.
3. Database creation: Read through this description, and look at the create command on the next page at the same time to ensure that you can read it.
4. p33, "SQL Commands": It is very important that you establish good formatting habits when typing in your SQL statements. It will pay off many times over when you're looking for errors in your SQL statements.
5. p33: Pay attention: You can safely ignore all this command buffer and editing commands stuff. You are going to use jedit (or whatever you choose) to create and maintain command files instead of using the command buffer. If you want to learn all this command buffer stuff, you can. Don't let me stop you. But I think it's much easier to use jedit.
6. p36-40: Instead of following their instructions, use jedit to create a command file, call it ex1.sql, and save it in the SQL directory on your account on elab-linux4. In your ssh window at the Oracle prompt, type @ex1 and press enter. This should execute (if you typed in the command correctly) and tell you that the table has been created. If it gives you an error message, use jedit to edit the file, save it again, and execute it again. Repeat until the command file executes without error.
7. p41, end of page: Also, notice how easy it would be to re-create the table after you've dropped it if you put the create statement in a command file (which you did, I might point out). You simply execute the file again! No need to re-type (and subsequently debug) the command. Yahoo!
8. p42, table 2.13: I almost never use char(n) except for something like state codes (since they have a fixed length of 2 characters). For all other character data I use varchar(n). I also don't use decimal(p,q). I use integer for whole numbers and number for numbers that can have a decimal part. Why? Because I haven't figured out what is gained by specifying (p,q) instead of simply letting Oracle figure it out. Everything has worked so far, so why change a good thing?
9. p43, top of page: On the next page you're going to get to a Question and Answer. Please try to answer these questions before you read the answer, not just this one, but all of them. These are generally pretty good and emphasize points that I think are important.
10. p44, Example 2: Use jedit to create a file called ex2.sql. Execute it in sqlplus by typing @ex2.

11. p45, Example 3 and the following 4 figures: You don't have to do all this. Open another window in jedit. Copy the information from the ex2.sql. Paste this information into the new window twice. Save this new window as ex3.sql. Modify the commands in the new window to reflect the proper data. Save the file again. In the ssh window execute this command in sqlplus. (You should know how to do this by now.)

    I find this way of working much more intuitive than the whole command buffer thing.

12. p48, Example 4: Guess what? Create a command file called ex4.sql, save it, execute it.
13. p50, top: To interact with sqlplus and to enter commands that you're not worried about having to edit or run again, simply type the commands at the SQL> prompt. Do so here. FYI: You can enter select * from sales_rep; all on one line — as before, formatting doesn't matter.
14. p56: The delete command is very powerful. Be very careful. If you enter delete from sales_rep; with the semi-colon at the end and then press return, Oracle will gladly delete all the data from your table! No problem! But that might not be what you want. You might have wanted to enter a where clause (as shown in the middle of p56) and simply pressed the wrong key. Oops! As I said, be careful when using this command.

15. p56, "Saving SQL Commands": You're actually going to do what's in this section, but not how the author says (surprise!). You're going to use the pico text editor. Pico is the text editor used by the pine email program so you should be familiar with it. It can be useful to know how to use when you don't have jedit installed on the machine you're working on or when you're simply making a quick change to a file you haven't opened in jedit.

    To use pico, type
    edit cre_cust
    at the SQL> prompt. This will open pico and show a blank file (or the contents of cre_cust if it has already been created). Type in the create statement. Type ctrl-O to write the information to the elab-linux4 disk without quitting pico. Type ctrl-X to save the information and exit out of pico. You're returned to the sqlplus prompt. As before, type @cre_cust to execute this command. If you have errors in the file, type edit cre_cust to open the file again, make the necessary changes, exit out of pico, and run the file again.

16. p58-62: Create command files for each of the figures on these pages. Execute them in order. Don't execute one until you've successfully executed all the previous ones. You'll have to watch very carefully for typos, and will have to go through many cycles of correcting, editing, and re-running until you get these right.
17. p67, Exercises, Note: It mentions a SPOOL file. This is nice, but generally won't be necessary for a while. Why? Because in the SSH and Telnet document, you went through a process that set up the terminal program so that you can scroll back up to 2000 lines in the display. So, much of what you did is available simply by scrolling up in the window. Later, when you are working with more tables and more data, you will want to use this spool stuff. For now in these simple exercises, it's probably not worth it.
18. p68, Premiere Exercises: You should have already done #1. Do numbers 2, 3, 4, 5, & 6. Why? Because the pain and suffering you go through here will help you out when you're doing your project. Also, this data and these tables are used throughout the rest of the book.
19. p68, Henry Books: As I've already stated: you don't have to do the Henry Books exercises.

Bud last edited this October 11, 2005 | Permalink | Comments (0)

A Guide to SQL, Chapter 3

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

Topics: SQL

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 4^th 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 last edited this October 13, 2005 | Permalink | Comments (0)

A Guide to SQL, Chapter 4

We dive into multitable queries.

Topics: SQL

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 last edited this October 13, 2005 | Permalink | Comments (0)

A Guide to SQL, Chapter 6

Views and integrity constraints are the meat of this chapter.

Topics: SQL

This is the chapter in which all that you have learned about SQL and database design comes together, especially concerning the topics of views and foreign keys. Pay close attention to the discussion of these topics. Views are unbelievably important to a database administrator, and and understanding of them are probably the defining characteristic that differentiates between your random database user and a professional.

1. p160, 4th paragraph, sentence beginning "To illustrate": This is actually quite a realistic example. Think of Juan as being the product manager for housewares. He wouldn't care about any information in the database other than hardware, so the best thing to do is to make the database appear to contain only hardware information.
2. p160-170, examples 1-5: I can't emphasize how much just how important these examples and the concepts presented in these examples are. As I was reading them I kept writing down "important," "important," etc. Not so helpful. It's sorta like reading a chapter and highlighting the whole thing. But that's how this section is. Read these pages carefully.
3. p164, Examples 3-4: This is a very common strategy behind the creation of a view. Find a fairly common select statement that is going to be executed a lot, and define a view for it.
4. p165-9: Make sure you know and understand the benefits of using views.
5. p170-173: Contrary to what the book implies, you can grant and revoke various privileges with your accounts. However, the commands provided in the book are all wrong for Oracle, perhaps because the authors did not test them assuming you would not try them. Below are corrected versions in example order. The main issues are that some of the commands the authors list have to have the arguments in a different order. Also, as a simple user, you can only grant privileges on one table at a time. Note, that my grants are to an account, fpgstu4, my student account. To test this, you might want to grant privileges to one of your friends and see if they can indeed access the tables as you intended.

   grant select on rep to fpgstu4;
   grant insert on part to fpgstu4;
   grant update (last, first, street) on customer to fpgstu4;
   grant delete on order_line to fpgstu4;
   grant select on part to public;
   grant index on rep to fpgstu4;
   grant alter on customer to fpgstu4;
   grant all on rep to fpgstu4;
   

   Here are the scripts that revoke these privileges:

   revoke select on rep from fpgstu4;
   revoke insert on part from fpgstu4;
   revoke update on customer from fpgstu4;
   revoke delete on order_line from fpgstu4;
   revoke select on part from public;
   revoke index on rep from fpgstu4;
   revoke alter on customer from fpgstu4;
   revoke all on rep from fpgstu;
   

6. p173: The main place we use indexes (I hate that word; better to use indices but no one seems to use it in the database world) is on fields that are a foreign key and not part of the primary key. You should index all of these fields. After that it's simply a matter of creating an index for a field if users tend to search on it a lot.
7. p178, Unique Indexes: For what type of relationships (if any) are unique indexes required?
8. p178, System Catalog: The system catalog is a real big deal in big-time databases. There are dozens of tables of information available about the database. One of a database professional's key resources and responsibilities are related to understanding and properly retrieving data from the system catalog.
9. p179, Figure 6.18: This is the query that executes when you type @list_tables.
10. p180, Figure 6.20: You are not the database administrator so you do not have the dba_tab_columns table available to you. You can execute this query against the user_tab_columns table provided you drop the part of the where clause that refers to the owner. Why? Because the user_tab_columns table only contains information about tables that you own, so the owner of each of these tables would be the same, so it does not store this information. The other tables they list can also be accessed by substituting user_ for dba_ and making the appropriate changes to the query as just outlined.
11. p181, Integrity in SQL: Finally, in the last section of this book that we're going to cover, we get to one of my favorite topics.
12. p184, check constraint in middle of page: When I see a constraint like this, I wonder why another table wasn't created for item classes, with one field for the primary key (e.g., AP, HW, SG, etc.) and another for the name of the class (e.g., housewares, etc.). I'm not saying that you have to have a separate table for this information. I'm simply saying that I would raise a red flag and investigate why you decided not to create a separate entity type for this information.

Bud last edited this October 20, 2005 | Permalink | Comments (0)

A Guide to SQL, Chapter 5

All about how to alter tables.

Topics: SQL

In this chapter you're not going to learn anything that's difficult - like all this query stuff - but you are going to learn some really useful commands that will help you maintain and build a database.

Notes on "Commit and Rollback"

Read this section of the book before you read this note.

This is unbelievably useful and comforting. Think about it: you can make any change you want to the data to your database knowing - knowing - that if you need to undo them, then it won't be a problem.

But, Houston, we have a problem. Actually, two. First, we have completely overridden this capability during the first two sessions of using Oracle.

• If you haven't started sqlplus yet, do so.

Look at the notes that appear in the ssh window just above the Oracle8i prompt.

Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production

Pico is the editor.
AutoCommit is on.
Login.sql loaded.

Specifically, notice the second-to-last line. This says that autocommit is on. What this means is that the commit/rollback feature is disabled. Every change to the data is automatically committed to the database. Once a change is committed to the database, it cannot be rolled back.

To turn autocommit off, do the following:

1. Type edit login
2. On the third-to-last line, which says set autocommit on, change it so that it is set to off
3. Change the prompt on the next line to off as well
4. Save the file
5. Exit back to the Oracle prompt
6. Exit out of sqlplus
7. Start up sqlplus again
8. Notice the change in the prompt when sqlplus starts

Of course, if you want to change commit so that it is back on, I think you can probably figure it out.

Above I mentioned that we have two problems, but I've only mentioned one. The second is that autocommit is now off. That means that if you work at Oracle for 24 hours straight, and then lose the connection to Oracle or exit Oracle without typing commit, then you will lose all the work that you've done.

And that can really stink.

That's not a bug, that's a feature. So, if you decide to work with autocommit off, then you need to remember to type commit before you exit Oracle.

1. p142, third sentence: The author says when necessary include a where clause. When necessary!?! Holy smokes! If you don't include the where clause, then the update command will change every row in the table. There are very specific instances when you don't want a where clause, but these are the exception rather than the rule. This is exactly the kind of mistake for which rollback exists.
2. p144, after the Note: To be clear about this, understand that rollback will rollback the effects of every and all commands entered since the last time changes to the database were committed (up to the beginning of your working session). So, if you enter all the statements in this chapter up to the exercises, you can enter rollback after every example or just once right before the exercises. In either case, the effect is that the database will not have changed.
3. p147, after the Q&A: And you enter rollback to get all your data back.
4. p150, Figure 5.12: Notice that the update command has no where clause. This is one of the places where this clause is not used. You have added a column to every row in the database and you want to ensure that every row has a value; thus, you use the update command without a where clause.
5. Exercises: Do the exercises for Premiere Products. Do not commit these changes to the database; i.e., type rollback after each and every exercise.

Bud last edited this October 20, 2005 | Permalink | Comments (0)