Back to 321 Main Page

Mobius strip

Assignment 4

The objective of this assignment is to give you some practice working with SQL.

Due: 01 May 2003

This is where we finally get to work with some data. You'll create two SQL scripts--one of DDL and one of DML statements. You will need:

Also, you will find some SQL resources at the bottom of this page if you need them.

DDL

SnarkDreams.com gets a lot of email from visitors to its website. It would be helpful if there were a place to record some of these questions and comments. We are going to add another table, called QUESTION, to the SnarkDreams database using only SQL.

Start SQLPlus by typing "sqlplus" at your command prompt and log in. To access the buffer, type "select" and hit enter twice. This creates a buffer with something in it to edit. Then you can type "edit" to change the buffer; this opens Notepad. After you've made changes, you can run it by closing Notepad (saving the file on the way out) and then typing "run" or "/" at the SQL prompt. Once you have a working SQL statement, copy it from the Notepad buffer and paste it into a separate document where you can save it permanently.

In your separate document, collect the SQL commands that do the following:

  1. In a single SQL statement, create a table called QUESTION. It should have four columns:
    --Email. Datatype: varchar2. Width: 35. Cannot be null.
    --RecDate. Datatype: date. Cannot be null.
    --Name. Datatype: varchar2. Width: 50. Can be null.
    --Question. Datatype: varchar2. Width: 500. Cannot be null.
  2. Now use an ALTER statement to add a constraint that makes Email and Date the composite, primary key. (If you want to do this as part of the Create statement above, that is also acceptable.)
  3. View the resulting table schema with "desc".
  4. After implementing all this, your user decides that (email, recdate) doesn't work as a primary key because sometimes he gets more than one email from the same person on the same day. He wants a surrogate key named Question_ID (datatype number, width 5) instead. Drop the existing composite key constraint, and then add the new column as a primary key to the QUESTION table. (Note that this may be done in two statements.)
  5. View the table schema again to show the results of your changes.

Since you pasted each statement into a separate document once you got it working, you know have an SQL file containing 6 statements: a create, a desc, 3 alters, and another desc. Save this file as "ddl.sql".

If you want to test that it works properly, delete the Question table with the drop command and then type "start ddl.sql". It should recreate the table over again without errors.

DML

Now we'll do some DML work. Import ztomasze.dmp, if you haven't done so already. Also, I have created a SQL file that will populate your QUESTION table with data. Run the file by typing "start questions.sql". If your QUESTION table is correct, it will add 5 rows of data to the table. (You can check by typing "select * from question;".) Again, record each of these statements in a separate document as you get them written:

  1. List all the email addresses of people who have asked me questions. (Each address should appear only once in the list you create.)
  2. Generate a list of email addresses of customers, excluding any addresses that also appear in QUESTION. (You may want to use a subquery.)
  3. How many customers have also emailed me a question? (That is, how many email addresses are listed in both QUESTION and CUSTOMER?)
  4. What products have not yet been ordered by anyone? Give me the description, not the name. (Hint: there are two products that haven't been ordered yet.)
  5. Give me the name (first and last) of anyone who has ordered mint shisha. (Hint: there is only one person who has ordered mint.)
  6. Finally, I want to know which countries my orders are coming from and how profitable each country is for me. Give me a list of the countries I've shipped to, the number of orders I've shipped to each one, and the total profit for each. Order it so the countries with the most orders is listed first, like so: 
    COUNTRY              COUNT(SDNUMBER) SUM(PROFIT)
-------------------- --------------- -----------
USA                                3         112
UK                                 2          62
Mexico                             1         195
Mongolia                           1        -5.2

Save your document of DML statements as "dml.sql".

What to Turn In

I want you to turn in a log of your SQL statements and their results. You can create this as follows:

  1. Drop the QUESTION table.
  2. Type "spool assign4.txt". This will start dumping everything you do to the file at c:\assign4.txt.
  3. Type "set echo on". This will echo your SQL statements to the screen before executing them.
  4. Type "start ddl.sql". This will run all your DDL statements, recreating the QUESTION table.
  5. Type "start questions.sql". This will fill the QUESTION table with data.
  6. Type "start dml.sql". This will run your DML statements.
  7. Type "spool off". This will stop dumping to the file.

Now just send me the log file assign4.txt. You can either email it, or print it and hand it to me in class. It should show each of your SQL statements and their results.

Let me know if you have any questions.

Resources

Questions & Answers

I keep getting an "invalid character" error.
Look at the error message--it should show you the line with the error with an * under the error. It is probably due to a closing semicolon. You do not need the semicolon when you're working with the buffer. You do need the semicolon when you are running an .sql file or typing directly at the prompt.
How do I view my constraints to see if my SQL statement really did what I think it should?
I too am working on how to see these in SQLPlus. You can view them from the Enterprise Manager Console though.
Should we use "Description" or "Name" when we want to find out who ordered the mint shisha?
You can use either one.
Help! ztomasze.dmp won't import properly! I get warnings and errors of the "922" variety.
This is due to incompatabilities between Oracle9.0 and Oracle9.2. Try using this new SQL file instead:
ztomasze.sql
Run it by typing "start ztomasze.sql" at the SQLPlus command line. Don't include the results of running it in the spooled output that you send me. (If you have trouble downloading the file, you can download this zip, which contains ztomasze.dmp, ztomasze.sql, and questions.sql.)
Do I need to include a sequence?
No. When I say make Question_ID a surrogate key, I just mean a primary key that doesn't come from any of the data. It's the sort of key a user would never need to see since it doesn't correspond to anything meaningful. Thus, it is helpful to have the default value for such a key be the next number in a sequence so it's handled automatically. But a sequence is not a required part of a surrogate key, nor is it required for this assignment.

Grading

This assignment is worth 15 points:

  • 3 points for turning in assignment in the requested "spooled" format
  • 5 points for the DDL statements
  • 7 points for the DML statements.

Solution

Spooled run of ddl.sql and dml.sql: assign4sol.txt


~ztomasze Index : TA Details: ICS321: Assignment 4
http://www2.hawaii.edu/~ztomasze
 Last Edited: 16 May 2003
©2002 by Z. Tomaszewski.