SQL Queries

In IS 331 Text - Chapter 10

Tables are available in the IS331 tablespace for practicing the SQL queries in Chapter 10 of Database Processing by David M. Kroenke. Please copy these tables to your tablespace by using the instructions found in SQL Exercise 1.

The following tables are used in the sample queries throughout the chapter.

JUNIOR

HONOR_STUDENT

STUDENT

CLASS

ENROLLMENT

FACULTY

Please note the following differences between the SQL queries printed in Chapter 10 and ORACLE's implementation of SQL.

When using the keyword IN followed by a set of values, the list of values should be enclosed by parentheses, not square brackets as shown in the text.
In ORACLE, only !=, ^= and <> are allowed for typing the not equal operator. The text uses NOT =. This will generate an ORACLE error.
In the section of the chapter that is entitled EXISTS and NOT EXISTS, there is a very complex query that is seeking the names of graduate students taking classes only with other graduate students. The query will not run on ORACLE as it is printed in the text. The query is rewritten below.

SQL> select a.name

from student a

where a.gradelevel = 'GR'

and not exists

(select *

from enrollment b

where a.sid = b.studentnumber

and b.classname in

(select c.classname

from enrollment c

where b.classname = c.classname

and c.studentnumber in

(select d.sid

from student d

where c.studentnumber = d.sid

and d.gradelevel != 'GR')));

In the section of the chapter entitled CHANGING DATA, in the subsection Inserting Data, look at the command that is used to fill the JUNIOR table. The word VALUES is not needed in this expression. The parentheses around the select statement are optional.

In the subsection Modifying Data, also in the CHANGING DATA section, several commands need to be changed.
The first update expression should be written:

UPDATE enrollment

SET positionnumber = 44

WHERE studentnumber = 400;

The next update expression can be written as follows to work with ORACLE.

SQL> update enrollment

set positionnumber =

(select max(positionnumber) + 1

from enrollment)

where studentnumber = 400;

The last update expression should be written:

UPDATE class

SET Name = 'BD564'

WHERE Name = 'BD445';

The following tables may be used with Group 1 Questions at the end of the chapter.

SALESPERSON

SALES_ORDER

CUSTOMER

Return to ORACLE/SQL Tutorial Contents Page