Queries

Now we enter the world of queries. A query is what you do with a SELECT command. Queries can be simple or complex. In a simple query, you might ask for information from one table ”for example, SELECT TABLE_NAME from ALL_TABLES . In a complex query, the SELECT statement is qualified by condi-tions such as WHERE or by the use of union, join, or other such construct. Let's investigate these a little further.

In a join , columns selected from two tables on the basis of a key match ”primary to foreign ”are connected:

 SELECT L_NAME, TEST_ID from STUDENTS, TEST_HISTORY WHERE STUDENTS.STUDENT_ID = TEST_HISTORY.STUDENT_ID AND STUDENTS.L_NAME LIKE '%Klie%'  

(1) Note the use of LIKE.

This join connects the STUDENT_ID columns in both tables:

L_Name	Test_ID
Klien	1
Klien	2
Klien	3
Klien	4
Klien	5

You can join unrelated columns in what is called a non-key join . Suppose that you wanted to see if any author had the same name as any student. You could write a SELECT statement matching the two columns. (Non-key joins are not common, but you should know that they are possible.)

Outer joins can be left-handed or right-handed. Are you confused ? Well, here's how an outer join works. We would use an outer join if we wanted all rows in a column when we joined tables, even those without a match on both tables. For example, some students in the STUDENTS table will not yet have taken an exam. Suppose that we want to list all students, along with the exams they have taken. If we do a normal join,

 WHERE STUDENTS.STUDENT_ID = TEST_HISTORY.STUDENT_ID 

we will get only the students that have taken exams. To get all students, we have to write our query like this (this is a left outer join):

 WHERE STUDENTS.STUDENT_ID(+) = TEST_HISTORY.STUDENT_ID. 

We would use a right outer join to do something like finding all the tests, in- cluding those that have not been taken:

 WHERE TEST_HISTORY.TEST_ID = TEST_ID.TEST_ID(+) 

Now as long as we're talking about joins, what is a union? A union is a way of combining similar data from several tables. If you wanted to send a mailing to everyone in your STUDENTS table who had also taken exams, or only to those who had not ever taken an exam, or to everyone in both tables, but you did not want to send two letters to them, a union would be the tool for you.

To get everyone listed in both tables, use the simple UNION statement:

 SELECT STUDENTS.STUDENT_ID from STUDENTS UNION SELECT TEST_HISTORY.STUDENT_ID; 

And here's the list you will get:

Student_ID ^[1]

111111111

123456789

222222222

333333333

444444444

555555555

666666666

777777777

^[1] In our test tables, only five students have taken tests so far, but the union lists all students from both tables without repeating any names .

To get students who are in the STUDENTS table and not in the TEST_HISTORY table, use the MINUS command:

 SELECT STUDENT_ID from STUDENTS MINUS SELECT STUDENT_ID from TEST_HISTORY; 

Here are the results:

Student_ID ^[2]

123456789

666666666

777777777

^[2] Students not in the TEST_HISTORY table.

And you guessed it, to get only names in both tables, use INTERSECT :

 SELECT STUDENT_ID from STUDENTS INTERSECT SELECT STUDENT_ID from TEST_HISTORY; 

Here's the resulting report:

Student_ID ^[3]

111111111

222222222

333333333

444444444

555555555

^[3] Only those students who are in both tables.

Putting the Date in the Spool File Name

You've probably noticed that the preceding examples give the spool file a simple name. Sometimes you want to create the spool file with the current date in its name, for obvious reasons. One way to do this is to add another column, then build your spool file name by concatenating the date and the name:

 col filename new_value XSPOOLNAME  set term off  select to_char(sysdate, 'mondd')  'PERSONS.txt' XSPOOLNAME from dual;  spool c:\&XSPOOLNAME  select * from person_table;  spool off;  

(1) Create a column to hold the variable XSPOOLNAME.

(2) By setting TERM OFF, you fool the system into using XSPOOLNAME instead of prompting you for a name.

(3) Build the full spool file name.

(4) Now spool your output to it.

(5) Here's a simple SQL command.

(6) End by turning spooling off.

OK, how fancy can we get with the spool file name and dates? Here's an example of another script that has not only the date, but also the time and the type of application (GL). This script is also on the book's CD:

 REM REM        Here's a nifty little script to use the current date REM        and time as an output file name REM REM        There's actually quite a bit going on here. First, REM        the SCAN ON command is used to REM        make sure our setup will handle substitution variables REM        Second, a variable is created - FILEDATE. REM        Third, we load FILEDATE with the current date and REM        time REM        Now we set the SPOOL FILE to FILEDATE by using the REM        substitution symbol '&' REM        Then we just continue processing, and finally turn REM        spooling off. REM REM       Note that you can add other text to the spool file REM       name, such as "GENERAL LEDGER" and REM       so forth by just adding additional concatenations. I REM       put the "GL" both at the beginning and at the REM       end to show you that you can append additional text REM       anywhere: REM REM     SELECT REM        'GL'TO_CHAR(SYSDATE,'YYYYMMDDHH24MI')'GL''.TXT' REM        filedate from dual; REM        and the spool file name is: GL200208280952GL.TXT REM REM REM  Step 1: Set "scan on" to make sure that your setup will REM       use substitution variables SET SCAN ON REM  Step 2: Create the variable         COLUMN FILEDATE NEW_VALUE FILEDATE REM  Step 3: Load the date and time         SELECT 'GL'TO_CHAR(SYSDATE,'YYYYMMDDHH24MI')'GL.TXT' FILEDATE FROM DUAL; REM  Step 4: Set the SPOOL FILE         SPOOL C:\&FILEDATE; REM  Step 5: Continue processing      Sdate_takenELECT FNAME from STUDENTS; REM At the end, turn spooling off      SPOOL OFF;