CIS4365: Database Applications
Fall, 2017

SQL Question Files

Return to Home Page

This might sound like a lot of extra work, but in fact it is intended to save you time. You should either:

  1. Enter the query needed for each question in a separate file, or

  2. Enter the query needed for each question in one file, that you can cut-and-paste from into the Oracle Environment

Why?

When you go to turn in your answers, you will have to rerun all of your queries. If you don't have them in a separate file, or in one file that you will cut-and-paste from. Otherwise, you will have to re-enter each question query each time you run it (a very time consuming process). 

Let's go over the procedures necessary, using question 1 as an example.

Question 1 is: "Get a List of all of Dr. Smith’s Patients".

This is a very simple query, and you could readily enter it from the SQL prompt as:

SQL> SELECT * FROM patient WHERE physid = '123456789;  

The problem is, that you have just mis-entered it (You forgot to enter the closing single quote). That means that you must now retype the entire query as:

SQL> SELECT * FROM patient WHERE physid = '123456789';  

Congratulations! You have gotten it to work, and you will find out that this will result in a list of 11 patients. The problem is that you had to type the question twice. You will also have to retype it when you make your final submission (Tedious -- Don't you agree??).

Let's see how you might do this using my suggested Option 1 (Create a separate file for each question):

Once again, open up notebook (recommended):

Type in the command:

Save the file (in this case as "q1.sql") and then run it in the Oracle Environment:

SQL> @q1.sql;

As we already know, we will get an error message. This time, however, we need merely go back and correct the file "q1.sql":

When we know execute the file in the Oracle Environment:

SQL> @q1.sql;

We will get the correct answer.

Now let's see how you might do this using my suggested Option 2 (Create a single question file and cut-and-paste). Let's assume that we have created a file called "Questions.sql" that will contain all of the questions. We need to open the file, select the question we wish to evaluate, and copy it:

Now we just need to paste it into the prompt menu:

SQL> select * from patient where physid = '12345689';

Which is the better option?? Six of one, half-dozen of the other. I might personally suggest option 2, but the choice is up to you. It's a trade-off (isn't everything??)

 

This page was last updated on 08/24/03