BIT320 Texts
October 11, 2005
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.
- The stuff under "Entities, Attributes, and Relationships" should be very familiar to you. Look how far you've already come this semester!
- 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.
- 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.
- 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.
- 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.
- 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.
- 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!
- 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?
- 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.
- p44, Example 2: Use jedit to create a file called ex2.sql. Execute it in sqlplus by typing @ex2.
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.
- p48, Example 4: Guess what? Create a command file called ex4.sql, save it, execute it.
- 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.
- 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.
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.- 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.
- 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.
- 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.
- p68, Henry Books: As I've already stated: you don't have to do the Henry Books exercises.
Bud edited this on October 11, 2005