In this lab, you will use the Oracle database to try out relational SQL queries on our textbook's CAP database. All the work for this lab can be most easily done on one of the MCS lab PCs running Linux, since we have the Oracle SQL Developer client software installed on those machines. However, you could also download and install this software on your own machine and still connect to our server, thebe.gac.edu.
For this lab, you need not write a true lab report. Instead, just turn in your SQL queries and the results they produced. You can copy these in SQL Developer and paste them into whatever program you are using to write your lab report, such as OpenOffice.org Writer or emacs. If a query produces a very large result, you can replace a large chunk of rows from the middle with "...", leaving only the first few and last few rows. However, you should in all cases include the count of how many rows the full result contains. You can find this in SQL Developer by right clicking in the result area and selecting "Count Rows" from the popup menu. (Do not use the count of rows fetched shown at the bottom of the screen: SQL Developer does not always fetch all the rows.) Alternatively, you could use the Run Script buttton and copy the results from the Script Output tab. If in any case you have reason to doubt that the result is correct, but haven't been able to track down what is wrong with your SQL query or fix it, you should indicate what is wrong with the result.
Be sure to answer each question using a single SQL query that directly produces the desired result. It is not acceptable to issue one query, manually extract some information from the result, and use that to type in a second query, which produces the desired result.
Your queries should not produce results with duplicate rows. In some
cases this may require that you use the keyword
"distinct" after "select".
Try not to make your queries unnecessarily complex. In particular, don't mention a table if it isn't needed to find the result.
Note that some queries are easier to formulate than others, and I haven't necessarily listed them from easiest to hardest. So if you are stuck on one, you may want to go on and then come back when you have more experience. (Asking for help is also a good idea when you are stuck.)
You can run SQL Developer from the Programming submenu of the main Fedora menu. (I'll demonstrate this in class.) In the following instructions, I'll pretend your username is jstudent; substitute your real one. You should create a connection called jstudent-thebe, specify the username jstudent and the password I give you, and set the hostname to thebe.gac.edu.
Because the password I issue you isn't very secure, you should probably start by changing your password, which you can do with an SQL command like
alter user jstudent identified by myNewSecretPassword;
To create the tables for the CAP database and load in the data, open the SQL script file /Net/gac/home/m/a/max/MCS-274/CAP.sql and run it. Note that the first time you do this, there will be error messages about attempts to drop nonexistant tables. This happens because the script starts by dropping the tables in case they already exist. These error messages can be ignored. Be sure to make the insertions of data stay in the database beyond your current session by clicking the "Commit" button, which is the one with the green checkmark.
Do exercise 3.1 parts (b), (f), (p), as well as a modified version of (r), in which agent a01 takes the place of a03. I have omitted those parts that were assigned as homework and those for which solutions are given in the back of the textbook, but you may want to do some of those parts as well for your own sake. This exercise is on page 163, but you'll also need to refer to exercise 2.5 on pages 74-75.
Do exercise 3.5 from pages 164-165. Note that the subselect can be a different one than in the problem statement.
Produce a table of products with two columns: the name of the product, and the number of customers who ordered that product. You will receive full credit even if your query wouldn't list products ordered by 0 customers. (There are no such products in the current contents of the database.) However, you will get some extra credit if you find a way to also include such products, with a customer count of 0.
Do exercise 3.11 parts (f), (h), (j), and (l) from page 167. In part (j), you should show the table after it has been updated.