A Guide to the Use of SQLBase SQL Based on Date

Bernard W Bennetto - Technology Translation Ltd

Introduction

Several people have asked me for some altenative document or manual which would provide a short and general purpose guide to the SQLBase or the various implementations of the ANSI SQL language. Until recently, to my knowledge, there was no guide which covered SQLBASE and its mappings to ANSI SQL. A manual is not wholly appropriate since it represents just one particular dialect of the language. They are not wholly comprehensive and fail to cover the more powerful aspects of the language. Chapter 6 of Chris Date's book 'An Introduction to Database Systems' does provide the best basic introductory guide but it is not wholly SQLBase compatible. I have therefore re hashed a SQLBase version of it. 2015 Hottest Replica TAG Heuer Aquaracer On Sale

The Database

The following SQL generates Chris Date's famous supplier and parts database. It has appearred in all five editions of Date's book and is now often widely quoted in the literature.

CREATE TABLE S
S# CHAR (2) NOT NULL,#
SNAME CHAR (6),
STATUS SMALLINT,
CITY CHAR (6));
INSERT INTO S VALUES (:1,:2,:3,:4)
\
S1,SMITH,20,LONDON
S2,JONES,10,PARIS
S3,BLAKE,30,PARIS
S4,CLARK,20,LONDO
S5,ADAMS,30,ATHENS
/
CREATE TABLE P
(P# CHAR (2) NOT NULL,
PNAME CHAR (6),
COLOR CHAR (6),
WEIGHT NUMBER,
CITY CHAR (6));
INSERT INTO P VALUES (:1,:2,:3,:4,:5)
\
P1,NUT,RED,12,LONDON
P2,BOLT,GREEN,17,PARIS
P3,SCREW,BLUE,17,ROME
P4,SCREW,RED,14,LONDON
P5,CAM,BLUE,12,PARIS
P6,COG,RED,19,LONDON
/
CREATE TABLE SP
(S# CHAR (2) NOT NULL,
P# CHAR (2) NOT NULL,
QTY SMALLINT);
INSERT INTO SP VALUES (:1,:2,:3)
\
S1,P1,300
S1,P2,200
S1,P3,400
S1,P4,200
S1,P5,100
S1,P6,100
S2,P1,300
S2,P2,400
S3,P2,200
S4,P2,200
S4,P4,300
S4,P5,400

/

It is a tiny database and a somewhat unrealistic database but it has proved itself comprehehensive for the purpose of this and many other examples. It is extended slightly for the purposes of our discussion. The original version comprises:

S which is a supplier table with the number, name, status and city of the supplier

S#

SNAME

STATUS

CITY

S1

SMITH

20

LONDON

S2

JONES

10

PARIS

S3

BLAKE

30

PARIS

S4

CLARK

20

LONDON

S5

ADAMS

30

ATHENS

P which is a parts table with number, name, colour, weight and stockholding city and

P#

PNAME

COLOR

WEIGHT

CITY

P1

NUT

RED

12

LONDON

P2

BOLT

GREEN

17

PARIS

P3

SCREW

BLUE

17

ROME

P4

SCREW

RED

14

LONDON

P5

CAM

BLUE

12

PARIS

P6

COG

RED

19

LONDON

SP which is a supplier parts table with details of part and supplier numbers and quantity supplied.

S#

P#

QTY

S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100

S1

P6

100

S2

P1

300

S2

P2

400

S3 P2 200
S4 P2 200

S4

P4

300

S4

P5

400

Note there are no primary or foreign key vales defined in these tables (although they can be implied). This is not a problem since the concept of primary and foreign key is a relational database implementation concept and here we are considering data access and manipulation partially from a theoretical standpoint. Moreover, such support was only provided with SQLBASE 5 and the ANSI standard which required this feature has only existed since 1989. In addition, large scale database implementations did not use the supplier's implementation for performance reasons!

I shall make reference only to the DQL (Data Query Language) and DML (Data Manipulation Language) components of SQL. No coverage of DDL (Data Definition Language) and DCL (Data Control Language) is intended.

The Algebra

Part of Codd's theory of relational database was an algebra for manipulating and retrieving a component table either by itself or in conjunction with a second table. This defined a theoretical framework for the development of SQL and any comprehensive treatment of its facilities need to refer to it. It consists of operations for manipulating a single table and operations for manipulating two tables A and B such that a third table C is created.

The algebraic operations which are permitted on a single relation/table are project and restrict (also known as Select but do NOT confuse with the SQL SELECT). They are referred to as unary operators and result in a relation/table which is a sub set of the original table. The restrict operator will extraction of tuples from a specified relation to those which will satisfy a particular condition. The project operator will extract specified attributes from a specified relation.

The binary group of algebraic operations involve two relations/tables (A and B) and a resultant relation/table (C).

The binary operators include the Product and also the Natural and Theta Join operators. They also include the Union, Intersection and Difference Operators. Finally there is the Divide operator.

Simple Queries Supporting the Project Operator

The STAR select is the most general purpose when SQL is used in an interactive command line context. An example is the following command. This retrieves all columns for all rows.

SELECT *
FROM S;


S# SNAME STATUS CITY
== ===== ====== ====
S1 SMITH 20     LONDON
S2 JONES 10     PARIS
S3 BLAKE 30     PARIS
S4 CLARK 20     LONDON
S5 ADAMS 30     ATHENS

5 ROWS SELECTED

The use of the wild card star or asterisk (*) selects all columns from the supplier table. All rows are presented since no WHERE restriction has been applied. It represents a null operation on the table. It is extremely powerful and therefore by issuing this command one could retrieve all columns of a several million row database. It use in such a context brings overnight notoriety!

The project operator within relational algebra limits display of a single relation/table to the extraction of one or more particular columns or attributes of the relation/table with distinct sets of values. It corresponds to the list of columns or attributes which are presented in the first part/line of the SELECT clause. It is equivalent to a slice which takes a 'vertical' view of table contents eg. column a, column c, etc from a table containing colums a through n.

Select

Given the relation/table

P#

PNAME

COLOR

WEIGHT

CITY

P1

NUT

RED

12

LONDON

P2

BOLT

GREEN

17

PARIS

P3

SCREW

BLUE

17

ROME

P4

SCREW

RED

14

LONDON

P5

CAM

BLUE

12

PARIS

P6

COG

RED

19

LONDON

then the command

project P over pname

produces the relation

P# PNAME COLOR WEIGHT CITY
NUT
BOLT
SCREW
CAM
COG

Non projected columns and duplicate values within the projected columns are ignored. The SQL command to perform such a projection of one column alone for the name column of the P table is illustrated by the following command.

SELECT DISTINCT PNAME
FROM P;

PNAME
=====
NUT  
BOLT
SCREW
CAM  
COG  

5 ROWS SELECTED

Date uses the following SQL command to display the projection of a column. It is illustrated by the following command which limits the columns which are displayed to the part number column of the SP supplier parts table.

SELECT P#
FROM SP;

P#
==
P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5

12 ROWS SELECTED

The previous query displays duplicate values of the part number column of the supplier parts table. However, to correctly implement the project operator and to display only unique values, it must have the additional predicate DISTINCT and have the form:

SELECT DISTINCT P#
FROM SP;

P#
==
P1
P2
P3
P4
P5
P6

6 ROWS SELECTED

In addition to displaying the column values, the result of column expressions and literal values can be calculated and displayed. The following converts pound weights to grammes and displays the result of the conversion and the conversion unit.

SELECT P#, WEIGHT * 454, 'GRAMS'
FROM P;

P# WEIGHT * 454 'GRAMS'
== ============ =======
P1 5448         GRAMS
P2 7718         GRAMS
P3 7718         GRAMS
P4 6356         GRAMS
P5 5448         GRAMS
P6 8626         GRAMS

6 ROWS SELECTED

Simple Queries Supporting the Restrict Operator

The restrict operator limits display to particular tuples or rows of the table. It corresponds to the tuples/rows which pass the tests which are applied to particular column or attribute values of a table which are found in the WHERE part of the SELECT clause. It is equivalent to a slice which takes a 'horizontal' view of table contents eg. row 1, row 3, row 7, etc from a table containing rows 1 through n.

RESTRICT

It has the form

restrict <relation> where <condition>

Given the same relation/table P representing parts

P#

PNAME

COLOR

WEIGHT

CITY

P1

NUT

RED

12

LONDON

P2

BOLT

GREEN

17

PARIS

P3

SCREW

BLUE

17

ROME

P4

SCREW

RED

14

LONDON

P5

CAM

BLUE

12

PARIS

P6

COG

RED

19

LONDON

then the command

restrict P where city = 'PARIS'

produces the relation

P# PNAME COLOR WEIGHT CITY
P2 BOLT GREEN 17 PARIS
P5 CAM BLUE 12 PARIS

Date's example uses the S table and combines a project of two columns and a restrict of the rows. It uses the simplest form of the WHERE clause. This involves a single boolean operator.

SELECT S#, STATUS
FROM S
WHERE CITY = 'PARIS';
S# STATUS
== ======
S2 10
S3 30

2 ROWS SELECTED

The next two forms further restrict the rows which are displayed by broadening the WHERE condition by the use of an additional 'ANDED' boolean operator

SELECT S#
FROM S
WHERE CITY = 'PARIS' AND STATUS > 20;

S#
==
S3

1 ROW SELECTED

The order in which the values of a query are displayed is determined by the position of the row or key value value in a base table or index file so that the default order is NO particular order. To overcome this the ORDER BY clause can be used as in the following instance

SELECT S#, STATUS
FROM S
WHERE CITY = 'PARIS'
ORDER BY STATUS DESC;

S# STATUS
== ======
S3 30
S2 10

2 ROWS SELECTED

The Null Predicate

Relational databases make a distinction between an attribute or column occurrence of a row which has been populated with data and those which have yet to be populated ie. it contains a null entry or a null value. Use of a WHERE clause against columns which contain null values result in the return of an unknown truth value. Thus three possible truth values can be returned ie. true, false and unknown. The WHERE clause ONLY returns TRUE values.

Currently there are no rows with NULL entries. Therefore, the following command creates a new row in the S table for supplier S6 who is called Jones and located in Leed. No status was provided so the row is created with a NULL status.

INSERT INTO S VALUES (:1,:2,:3,:4)
\
S6,JONES,,LEEDS
/

1 ROW INSERTED

The first SELECT displays the amended table and the second SELECT proves that this new occurrence will be ignored.

SELECT *
FROM S
WHERE S# = 'S6';

S# SNAME  STATUS     CITY
== ====== ========== ======
S6 JONES             LEEDS

1 ROW SELECTED

SELECT S#
FROM S
WHERE STATUS > 25;

S#
==
S3
S5

2 ROWS SELECTED

Because NULL represent an unknown value then it is inappropriate to use the equal operator (=). Therefore, the equal sign cannot be used in expressions which are used to identify NULL values. The following syntax is necessary.

SELECT S#
FROM S
WHERE STATUS IS NULL;

S#
==
S6

1 ROW SELECTED

The opposite of this is the command.

SELECT S#
FROM S
WHERE STATUS IS NOT NULL;

S#
==
S1
S2
S3
S4
S5

5 ROWS SELECTED

The UNION Operator

UNION is one of that second group of relational operators - a binary rather than a unary operator. It is used in relational algebra and is equivalent to the traditional set theory operator. It returns all unique row occurrences (ie. removes redundant rows) in a query involving two relations.

Union

To select rows which will match one of two criteria and remove redundant ie. duplicated rows it is necessary to use the UNION clause to link and relate the rows retrieved by two separate SELECT commands.

Consider the SP and the P tables.

S# P# QTY       P# PNAME COLOR WEIGHT CITY

S1 P1 300       P1 NUT RED 12 LONDON
S1 P2 200       P2 BOLT GREEN 17 PARIS
S1 P3 400       P3 SCREW BLUE 17 ROME
S1 P4 200       P4 SCREW RED 14 LONDON
S1 P5 100       P5 CAM BLUE 12 PARIS
S1 P6 100       P6 COG RED 19 LONDON
S2 P1 300
S2 P2 400

S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400

The restrict operations and the union of these tables is expressed as:

(restrict sp where S# = 'S2') union (restrict P where weight > 17) giving C

and yields the following set of data - a relation of 4 rows. Obviously the relation will need to consist of common columns for the UNION to have any meaning.

 S2    P1    --

 S2    P2       --

(--    P2    17)

 --    P3    17

 --    P6    19

Thus to get part numbers for parts weighing more than 16 pounds or are supplied by supplier S2 the following command is necessary.

SELECT P#
FROM P
WHERE WEIGHT > 16
UNION
SELECT P#
FROM SP
WHERE S# = 'S2';

P#
==
P1
P2
P3
P6

4 ROWS SELECTED

A Query Supporting the Product Operator

These forms of the SELECT command permit one to operate on more than table and to build new tables associating the contents of other tables. Thus two tables are referenced in the FROM clause. The most basic is based upon the relational algebra product operation. Like the union operator, it is one of that second group of algebraic binary operations. These operators involve two tables A and B and a resultant table C.

Product

This product, also known as a cartesian product, represents the association of M tuple values for one relation/table column A with the N tuple values of a second relation/table column B. This generates a table C with both columns and with M*N rows.

With two columns which contain:

- parts a, b and c

and

- suppliers 1 and 2

will yield a new table with:

pt a, sp 1 / pt a, sp 2 / pt b, sp 1 / pt b, sp 2 / pt c, sp 1 / pt c sp 2
A            B             C

a            1            a 1
b                         b 1
c                         c 1
             2            a 2
                          b 2
                          c 2

3 rows       2 rows       6 rows

The algebraic form

a times b giving C

is shown below as a SQL command. It corresponds to SQL SELECT command with a FROM clause with NO qualifying WHERE clause.

SELECT S.*, P.*
FROM S, P;

S.S# S.SNAME S.STATUS   S.CITY P.P# P.PNAME P.COLOR P.WEIGHT   P.CITY
==== ======= ========== ====== ==== ======= ======= ========== ======

S1   SMITH   20         LONDON P1   NUT     RED     12         LONDON
S2   JONES   10         PARIS  P1   NUT     RED     12         LONDON
S3   BLAKE   30         PARIS  P1   NUT     RED     12         LONDON
S4   CLARK   20         LONDON P1   NUT     RED     12         LONDON
S5   ADAMS   30         ATHENS P1   NUT     RED     12         LONDON
S6   JONES              LEEDS  P1   NUT     RED     12         LONDON
S1   SMITH   20         LONDON P2   BOLT    GREEN   17         PARIS
S2   JONES   10         PARIS  P2   BOLT    GREEN   17         PARIS
S3   BLAKE   30         PARIS  P2   BOLT    GREEN   17         PARIS
S4   CLARK   20         LONDON P2   BOLT    GREEN   17         PARIS
S5   ADAMS   30         ATHENS P2   BOLT    GREEN   17         PARIS
S6   JONES              LEEDS  P2   BOLT    GREEN   17         PARIS
S1   SMITH   20         LONDON P3   SCREW   BLUE    17         ROME
S2   JONES   10         PARIS  P3   SCREW   BLUE    17         ROME
S3   BLAKE   30         PARIS  P3   SCREW   BLUE    17         ROME
S4   CLARK   20         LONDON P3   SCREW   BLUE    17         ROME
S5   ADAMS   30         ATHENS P3   SCREW   BLUE    17         ROME
S6   JONES              LEEDS  P3   SCREW   BLUE    17         ROME
S1   SMITH   20         LONDON P4   SCREW   RED     14         LONDON
S2   JONES   10         PARIS  P4   SCREW   RED     14         LONDON
S3   BLAKE   30         PARIS  P4   SCREW   RED     14         LONDON
S4   CLARK   20         LONDON P4   SCREW   RED     14         LONDON
S5   ADAMS   30         ATHENS P4   SCREW   RED     14         LONDON
S6   JONES              LEEDS  P4   SCREW   RED     14         LONDON
S1   SMITH   20         LONDON P5   CAM     BLUE    12         PARIS
S2   JONES   10         PARIS  P5   CAM     BLUE    12         PARIS
S3   BLAKE   30         PARIS  P5   CAM     BLUE    12         PARIS
S4   CLARK   20         LONDON P5   CAM     BLUE    12         PARIS
S5   ADAMS   30         ATHENS P5   CAM     BLUE    12         PARIS
S6   JONES              LEEDS  P5   CAM     BLUE    12         PARIS
S1   SMITH   20         LONDON P6   COG     RED     19         LONDON
S2   JONES   10         PARIS  P6   COG     RED     19         LONDON
S3   BLAKE   30         PARIS  P6   COG     RED     19         LONDON
S4   CLARK   20         LONDON P6   COG     RED     19         LONDON
S5   ADAMS   30         ATHENS P6   COG     RED     19         LONDON
S6   JONES              LEEDS  P6   COG     RED     19         LONDON

36 ROWS SELECTED

The result of the execution of this query will be to produce what is referred to as the Cartesian Product of the two tables. It associates each row in one table with a row in the second table. Given S consists of 5 rows and P of 6 rows then the results table consists of 30 rows regardless of the sense or otherwise of their association. fake patek philippe watches

Queries Based Upon the Join Operators

Such operations as the product must be avoided on the grounds of performance and sociability to fellow users. Use of the product to say link 200 potential applicants with 400 potential vacancies will yield a new table of 80,000 rows! cheap ralph lauren t shirts

Limitting the impacts of such requests will normally be done by qualifying the all embracing join with the WHERE clause.

The 'Natural Join' is a further algebraic binary operation involving two tables A and B and a resultant table C.

A

ralph lauren polo sale ralph lauren romance for women polo ralph lauren cheap

B

C

a1

b1

b1

c1

a1

b1

c1

a2

b1

b2

c2

a2

b1

c1

a3

b2

b3

c3

a3

b2

c2

This has the form in relational algebra

join S and SP where S.S# = SP.S# giving c

which produces the following relation. The natural join, rather than cutting or slicing the relation, pastes the linked tuples together and, note, only one column of the two linking columns are migrated to the new relation.

S# P# QTY SNAME STATUS CITY
S1 P1 300 SMITH 20 LONDON
S1 P2 200 SMITH 20 LONDON
S1 P3 400 SMITH 20 LONDON
S1 P4 200 SMITH 20 LONDON
S1 P5 100 SMITH 20 LONDON
S1 P6 100 SMITH 20 LONDON
S2 P1 300 JONES 10 PARIS
S2 P2 400 JONES 10 PARIS
S3 S2 200 BLAKE 30 PARIS
S4 S2 200 CLARK 20 LONDON
S4 P4 300 CLARK 20 LONDON
S4 P5 400 CLARK 20 LONDON

Such a join would involve joining the S and P tables so as, for instance, to find which suppliers were located in the same city as our part stockholdings. The WHERE clause defines the equality condition which must be met.

It is represented schematically below with the shaded area representing occurrences meeting the equality condition. Like the product the join builds a relation from two relations which consists of all possible conditions of tuples. However, the tuple is constructed from the attributes of the two tuples. The eligible tuples must meet some specified condition.

In SQLBase this must be expressed as:

SELECT S#, SNAME, STATUS, P#, PNAME, COLOR, WEIGHT, S.CITY
FROM S, P
WHERE S.CITY = P.CITY;

S# SNAME      STATUS P# PNAME  COLOR	  WEIGHT S.CITY
== ====== ========== == ====== ====== ========== ======
S1 SMITH	  20 P1 NUT    RED	      12 LONDON
S1 SMITH	  20 P4 SCREW  RED	      14 LONDON
S1 SMITH	  20 P6 COG    RED	      19 LONDON
S2 JONES	  10 P2 BOLT   GREEN	      17 PARIS
S2 JONES	  10 P5 CAM    BLUE	      12 PARIS
S3 BLAKE	  30 P2 BOLT   GREEN	      17 PARIS
S3 BLAKE	  30 P5 CAM    BLUE	      12 PARIS
S4 CLARK	  20 P1 NUT    RED	      12 LONDON
S4 CLARK	  20 P4 SCREW  RED	      14 LONDON
S4 CLARK	  20 P6 COG    RED	      19 LONDON

10 ROWS SELECTED

The natural join is the main form of join and is the most widely used. This defines a list of columns to be associated on a common column with common column values. Certain implementations will identify and support it as a seperate form of join.

In Watcom SQL it is the additional detail in the FROM clause and the composition of the columns defined in the SELECT list which defines the equivalent of the WHERE condition.

SELECT S#, SNAME, STATUS, P#, PNAME, COLOR, WEIGHT, CITY
FROM S NATURAL JOIN P;

In SQLbase, the following can NOT be regarded as a natural join since it will result in display of both S.CITY and P.CITY.

SELECT S.*, P.*
FROM S, P
WHERE S.CITY = P.CITY;

S.S# S.SNAME S.STATUS S.CITY PP# P.PNAME P.COLOR P.WEIGHT P.CITY
==== ======= ======== ====== ==== ======= ======= ======== ======
S1   SMITH	    20 LONDON P1   NUT     RED 	    12 LONDON
S1   SMITH	    20 LONDON P4   SCREW   RED 	    14 LONDON
S1   SMITH	    20 LONDON P6   COG     RED 	    19 LONDON
S2   JONES	    10 PARIS  P2   BOLT    GREEN    17 PARIS
S2   JONES	    10 PARIS  P5   CAM     BLUE	    12 PARIS
S3   BLAKE	    30 PARIS  P2   BOLT    GREEN    17 PARIS
S3   BLAKE	    30 PARIS  P5   CAM     BLUE	    12 PARIS
S4   CLARK	    20 LONDON P1   NUT     RED 	    12 LONDON
S4   CLARK	    20 LONDON P4   SCREW   RED 	    14 LONDON
S4   CLARK	    20 LONDON P6   COG     RED 	    19 LONDON

10 ROWS SELECTED

This differs from the previously defined form of join and is known as a theta join. A theta join permits inclusion of both columns S.CITY and P.CITY in the resultant table. This more compact SQL example would be known as a special form of Theta Join which is termed the EQUI JOIN. It is a more general purpose form since, where relevant, the operators can involve the greater or smaller than operators.

The Outer Join

SQLBase like most relational database systems supports what is known as the outer join. In the above example, supplier and parts date was joined on the basis of whether the supplier city was the same as the partholding/stockholding city. To identify, those suppliers who are not in the same city, an outer join is necessary. One can perform an outer join of supplier data or an outer join of parts data. Both cannot be done simultaneously.

The direction of the join is indicated by adding a plus sign to the join column of the WHERE clause which might not have rows to satisfy the join condition.

SELECT S.*, P.*
FROM S, P
WHERE S.CITY = P.CITY(+);

S.S# S.SNAME S.STATUS   S.CITY P.P# P.PNAME P.COLOR P.WEIGHT   P.CITY
==== ======= ========== ====== ==== ======= ======= ========== ======
S1   SMITH   20         LONDON P1   NUT     RED     12         LONDON
S1   SMITH   20         LONDON P4   SCREW   RED     14         LONDON
S1   SMITH   20         LONDON P6   COG     RED     19         LONDON
S2   JONES   10         PARIS  P2   BOLT    GREEN   17         PARIS
S2   JONES   10         PARIS  P5   CAM     BLUE    12         PARIS
S3   BLAKE   30         PARIS  P2   BOLT    GREEN   17         PARIS
S3   BLAKE   30         PARIS  P5   CAM     BLUE    12         PARIS
S4   CLARK   20         LONDON P1   NUT     RED     12         LONDON
S4   CLARK   20         LONDON P4   SCREW   RED     14         LONDON
S4   CLARK   20         LONDON P6   COG     RED     19         LONDON
S5   ADAMS   30         ATHENS
S6   JONES              LEEDS

12 ROWS SELECTED

An extra row will be added to the results table with the supplier details which do not have a matching city in the P table with null values representing the missing parts data. It is then possible to select and retrieve such non joins by the following variant on the above SELECT command.

SELECT S.*, P.*
FROM S, P
WHERE S.CITY = P.CITY(+)
AND P.CITY IS NULL;

S.S# S.SNAME S.STATUS S.CITY P.P# P.PNAME P.COLOR P.WEIGHT P.CITY
==== ======= ========== ====== ==== ======= ======= ========== ======
S5 ADAMS 30 ATHENS
S6 JONES LEEDS

2 ROWS SELECTED

Self Joins

Such queries are best understood by imagining that there are two separate copies of the table and by performing a join on the contents of the two tables. The following, slightly amended form of the P relation, documents a part number for the assembly on which a part is used.

P# AP# PNAME COLOR WEIGHT CITY
== === ===== ===== ====== ======
P1 P7  NUT   RED       12 LONDON
P2 P7  BOLT  GREEN     17 PARIS
P3 P7  SCREW BLUE      17 ROME
P4 P8  SCREW RED       14 LONDON
P5 P8  CAM   BLUE      12 PARIS
P6 P9  COG   RED       19 LONDON
P7     DUBRY PINK      45 LONDON
P8     THING ROSE      75 ATHENS
P9     WOTSY RED       37 PARIS

The algebra used to display the parts used on the 'DUBRY' assembly is:

restrict P where PNAME = 'DUBRY' giving AP

join AP and P where AP.P# = P.P# giving C

and the resultant relation C looks like this.

P# AP# PNAME COLOR WEIGHT CITY   P#  PNAME COLOR WEIGHT CITY
== === ===== ===== ====== ====== === ===== ===== ====== ======
P7     DUBRY PINK      45 LONDON P1  NUT   RED       12 LONDON
P7     DUBRY PINK      45 LONDON P2  BOLT  GREEN     17 PARIS
P7     DUBRY PINK      45 LONDON P3  SCREW BLUE      17 ROME

This is implemented using SQL with the command

SELECT A.P#, A.AP#, A.PNAME, A.COLOR, A.WEIGHT, A.CITY,
B.P#, B.PNAME, B.COLOR, B.WEIGHT B.CITY
FROM P A, P B
WHERE A.PNAME = `DUBRY' AND A.P# = B.AP#;

P# AP# PNAME COLOR WEIGHT CITY P# PNAME COLOR WEIGHT CITY
== === ===== ====== ====== ====== == ===== ====== ====== ======

P7 DUBRY PINK 45 LONDON P1 NUT RED 12 LONDON
P7 DUBRY PINK 45 LONDON P2 BOLT GREEN 17 PARIS
P7 DUBRY PINK 45 LONDON P3 SCREW BLUE 17 ROME

3 ROWS SELECTED

Date uses the supplier table for his example. The query identifies suppliers who are located in the same city.

SELECT A.S#, B.S#, A.CITY
FROM S A, S B
WHERE A.CITY = B.CITY
AND A.S# < B.S#;

A.S# B.S# A.CITY
==== ==== ======
S2   S3   PARIS
S1   S4   LONDON

2 ROWS SELECTED

This lists S1 and S4 which are located in London and S2 and S3 which are located in Paris.

Without the use of the greater than symbol (<) in the second part of the where clause then the London pairs S1 with S1, S1 with S4 and S4 with S1 would be displayed. This test precludes display of the London pairs S1 and S1 and S4 and S1.

Aggregate Functions

There are five types of aggregate functions which operate on the collection of numeric or character values contained in a column. They are COUNT, MIN and MAX which operate on numeric or character values and AVG and SUM which only operate on numeric values. They normally result in a table with a single row and with no column headings.

To determine the total number of rows in the S table the STAR COUNT command is relevant.

SELECT COUNT(*)
FROM S;

COUNT(*)
==========
6

1 ROW SELECTED

While it returns a value of 5, the number of suppliers, it does not necessarily return the number of suppliers. If the table contains null values then these will be counted and if the table contains duplicate supplier numbers then these will be included. In order to return the number of suppliers then the command would need to be written with a qualifying column and the DISTINCT predicate.

SELECT COUNT (DISTINCT S#)
FROM S;

COUNT (DISTINCT S#)
===================
6

1 ROW SELECTED

This is best illustrated using the same command on the SP table which counts the number of suppliers making shipments. Null values would be excluded.

SELECT COUNT (DISTINCT S#)
FROM SP;

COUNT (DISTINCT S#)
===================
4

1 ROW SELECTED

WHERE clauses can qualify these aggregations. To get the number of rows which contain shipment details for Part P2 the following command is appropriate.

SELECT COUNT(*)
FROM SP
WHERE P# = 'P2';

COUNT(*)
==========
4

1 ROW SELECTED

To provide further detail such as the quantity of Part P2 which is supplied then the SUM function is needed.

SELECT SUM (QTY)
FROM SP
WHERE P# = 'P2';

SUM (QTY)
==========
1000

1 ROW SELECTED

Predicates used with Aggregrates

There are a whole series of predicates which provide further qualification. They include those that are used in association with the aggregrate functions such as GROUP BY and HAVING, those that are used in retrieval functions such as IN, LIKE and NULL and the more esoteric EXISTS and NOT EXISTS.

The previous function calculated the quantity supplied for a particular part. If one wishes this detail for each individual part, then it is necessary to use the GROUP BY clause. This rearranges the data into a grouped order (not necessarily a sorted order) so that the function can calculate the required values.

SELECT P#, SUM (QTY)
FROM SP
GROUP BY P#;

P# SUM (QTY)
== ==========
P1 600
P2 1000
P3 400
P4 500
P5 500
P6 100

6 ROWS SELECTED

The HAVING clause enables us to qualify the results produced by the GROUP BY. It is equivalent to the WHERE clause but is used only in conjunction with the GROUP BY clause. HAVING will eliminate identify groups for inclusion in the output from the table just as WHERE will identify rows for inclusion in the output.

SELECT P#
FROM SP
GROUP BY P#
HAVING COUNT(*) > 1;

P#
==
P1
P2
P4
P5

4 ROWS SELECTED

Additional Predicates

The IN or LIKE predicates are similar. IN is the most powerful since it can be used with numeric and character attributes. IN permits one to list a string of variables for run time execution so that suppliers and their status are listed at the latest opportunity.

An example is shown for the suppliers table.

SELECT S#, STATUS
FROM S
WHERE CITY IN ('PARIS','ATHENS');

S# STATUS
== ==========
S2 10
S3 30
S5 30

3 ROWS SELECTED

The LIKE predicate is less powerful because it is restricted to performing character matches. It can be used whenever one wishes to list all parts beginning with a letter 'C'.

SELECT P.*
FROM P
WHERE P.PNAME LIKE 'C%';

P.P# P.PNAME P.COLOR P.WEIGHT   P.CITY
==== ======= ======= ========== ======
P5   CAM     BLUE            12 PARIS
P6   COG     RED             19 LONDON

2 ROWS SELECTED

Another variant which searches for a color containing the string RE is:

SELECT P.*
FROM P
WHERE P.COLOR LIKE '%RE%';

P.P# P.PNAME P.COLOR P.WEIGHT   P.CITY
==== ======= ======= ========== ======
P1   NUT     RED             12 LONDON
P2   BOLT    GREEN           17 PARIS
P4   SCREW   RED             14 LONDON
P6   COG     RED             19 LONDON

4 ROWS SELECTED

Another variant which searches for a color which does not contain the string RE is:

SELECT P.*
FROM P
WHERE P.COLOR NOT LIKE '%RE%';

P.P# P.PNAME P.COLOR P.WEIGHT   P.CITY

==== ======= ======= ========== ======
P3   SCREW   BLUE            17 ROME
P5   CAM     BLUE            12 PARIS

2 ROWS SELECTED

Retrievals Using Subqueries

The power of SQL and complexity of SQL is considerably increased by the ability to nest queries to any number of levels. There are ways of avoiding the use of such subqueries and nesting by using set restriction features and cursor features by performining queries on a results table and by working with the temporary tables. There are limits to such approaches and this is not necessarily the professional approach and one should, therefore, attempt to master subqueries. They enable one to relate the contents of data in one table to that in another without doing a join.

The following gets the name of suppliers who supply part P2. The suppliers of part P2 are provided from the SP table and the results of this subquery is then used to extract data from the S table.

SELECT SNAME
FROM S
WHERE S# IN
     (SELECT S#
      FROM SP
      WHERE P# = 'P2');

SNAME
======
SMITH
JONES
BLAKE
CLARK

4 ROWS SELECTED

In a sense it is equivalent to

SELECT SNAME
FROM S
WHERE S# IN
     ( 'S1', 'S2', 'S3', 'S4');

S.SNAME
=======
SMITH
JONES
BLAKE
CLARK

4 ROWS SELECTED

It is also equivalent to a join which is equally correct.

SELECT S.SNAME
FROM S, SP
WHERE S.S# = SP.S#
AND SP.P# = 'P2';

SNAME
======
SMITH
JONES
BLAKE
CLARK

4 ROWS SELECTED

The query can be nested down to several levels so as to determine the names of suppliers who supply at least one red part. The following therefore involves the three tables. The first query interrogates the P table to determine the part numbers which are red, the second identifies the supplier numbers who supply that part number and finally the supplier name of that supplier number is determined.

SELECT SNAME
FROM S
WHERE S# IN
     (SELECT S#
      FROM SP
      WHERE P# IN
           (SELECT P#
            FROM P
            WHERE COLOR = 'RED'));

SNAME
======
SMITH
JONES
CLARK

3 ROWS SELECTED

The above queries use the IN predicate since more than one value could be returned by the nested query. If a single value is returned then the equal operator (=) could be used. Since a supplier is only located in a single city, this command could determine the suppliers who are located in the same city as supplier S1.

SELECT S#
FROM S
WHERE CITY =
     (SELECT CITY
      FROM S
      WHERE S# = 'S1');

S#
==
S1
S4

2 ROWS SELECTED

Aggregrate functions can be used in a similar function which involve a comparision operator. This gets supplier numbers for suppliers with a status value less than the SINGLE current maximum status value.

SELECT S#
FROM S
WHERE STATUS <
     (SELECT STATUS
      FROM S);

S#
==
S1
S2
S4

3 ROWS SELECTED

Implementing Intersection and the Difference Operators

To determine the component parts of the UNION it is necessary to identify the intersection and the two sides of the difference.

The UNION was defined by the following command.

SELECT P#
FROM P
WHERE WEIGHT > 16
UNION

SELECT P#
FROM SP
WHERE S# = 'S2';

P#
==
P1
P2
P3
P6

4 ROWS SELECTED

The component parts of the union are the intersection, which is unshaded, and the two differences. The interection is the result of one operation while the remainder is the result of the other two sides of the operation. These are known as the difference and these are represented by a shaded and a black area within the diagram below.

The `intersection' represents the operation which builds a relation of all tuples appearing in both of the specified relations. There `difference' represents a relation which consists of all tuples appearing in the first but NOT the second of two specified relations.

This implemented restrict operations and the union of these tables which was expressed as:

(restrict sp where S# = 'S2') union (restrict P where weight > 17) giving C

This yields the following set of data a relation of 4 rows with the following components.

 INTERSECTION 

     S2    

    P2    

       - -      

   - -

    P2    

    17    

DIFFERENCE A 

   S2  

          P1    

       - -      

  DIFFERENCE B                       - -               P3            17  

   - -

       P6    

     19    

The intersection can be determined with an equi join. The results table must consists of sharing, common columns.

SELECT P.P#, SP.P#
FROM P, SP
WHERE P.P# = SP.P#
AND P.WEIGHT > 16
AND SP.S# = 'S2';

P.P# SP.P#
==== =====
P2   P2

1 ROW SELECTED

One side of the difference is determined by the following SELECT command.

SELECT P#
FROM P
WHERE P.WEIGHT > 16
AND P.P# NOT IN

               (SELECT SP.P#
        FROM SP
        WHERE S# = 'S2');

P#
==
P3
P6

2 ROWS SELECTED

The obverse would be be as follows.

SELECT P#
FROM SP
WHERE S# = 'S2'
AND SP.P# NOT IN
        (SELECT P.P#
         FROM P
         WHERE WEIGHT > 16);

P#
==
P1

1 ROW SELECTED

Queries Using EXIST Predicates

The Exists predicate is normally shown as a reversed letter E and represents what is known as the existential quantifier.   This is a form of mathematical shorthand for stating that, within a particular set, there exists at least one member of a set which has a particular value.

The expression, which has the form:

...... WHERE EXISTS (SELECT ....

will evaluate to true if the subsequent SELECT evaluates to a non empty set.

An example for querying the supplier names of those suppliers who supply part P2 is shown below.

SELECT SNAME
FROM S
WHERE EXISTS
     (SELECT *
      FROM SP
      WHERE S# = S.S#
      AND P# = 'P2');

SNAME
======
SMITH
JONES
BLAKE
CLARK

4 ROWS SELECTED

The converse of this form of uses the NOT EXISTS form which evaluates to true if there DOES NOT exist at least one member of a set which has a particular value ie the set is EMPTY. The form of the command to find suppliers who do not supply part P2 is as follows.

SELECT SNAME
FROM S
WHERE NOT EXISTS
     (SELECT *
      FROM SP
      WHERE S# = S.S#
      AND P# = 'P2');

SNAME
======
ADAMS
JONES

2 ROW SELECTED

A doubly negated form involving three levels of nesting is more difficult to understand. This gets supplier names for suppliers who supply ALL parts. To understand it, it should be re stated as, select supplier names for suppliers such that there does not exist a part that they do not supply.

SELECT SNAME
FROM S
WHERE NOT EXISTS
     (SELECT *
      FROM P
      WHERE NOT EXISTS
           (SELECT *
            FROM SP
            WHERE S# = S.S#
            AND P# = 'P2'));

SNAME
======
SMITH
JONES
BLAKE
CLARK

4 ROWS SELECTED

The Insert Command

Within SQLBase there are a variety of formats for the INSERT. The first group of formats perform single record inserts and the second group perform multiple record inserts, normally, using a subquery. Referential integrity and not null rules must be observed. To insert a complete row the following command is suitable. The field names can be omitted if the values are presented in left to right order.

INSERT INTO P
VALUES ('P7', 'SPRING', 'PINK', 14, 'NICE');

1 ROW INSERTED

When not all columns, such as name and colour, are to be filled then the following form is appropriate. One assumes that these two columns have not been defined as NOT NULL.

INSERT INTO P (P#, CITY, WEIGHT)
VALUES ('P8', 'ATHENS', 24);

1 ROW INSERTED

To update several sets of row values with a single insert then a form used earlier in this document must be used.

INSERT INTO P VALUES (:1,:2,:3,:4,:5)
\
P9,DUBRY,RED,18,LYONS
P0,WOTSIT,GREEN,17,REIMS
/

2 ROWS INSERTED

The form using a subquery can also be used with a permanent or temporary table. Values from the sub query will be stored in the table rather in the normal, temporary results table. To store the total quantities supplied, the following command is necessary.

CREATE TABLE PQTY
(P# CHAR (2) NOT NULL,
PTOT SMALLINT);

INSERT INTO PQTY
SELECT P#, SUM(QTY)
FROM SP
GROUP BY P#;

6 ROWS INSERTED

The Update Command

The variety of formats for the UPDATE parallel those for the INSERT with an additional class. The first type is used for single record updates, a second group for performing multiple record inserts and a third type for multiple table updates. Referential integrity and not null rules must still be be observed, particularly in the case of the final group.

The single record update form is as follows. Note the SET clause can contain literals, expressions and, by default, a NULL predicate.

UPDATE P
SET COLOR = 'YELLOW'
WEIGHT = WEIGHT + 5
CITY IS NULL
WHERE P# = 'P2';

1 ROW UPDATED

A multiple record update would be provided by the following command.

UPDATE S
SET STATUS = 2 * STATUS
WHERE CITY = 'LONDON';

2 ROWS UPDATED

A multiple record update involving a subquery is exemplified by the following command.

UPDATE SP
SET QTY = 0
WHERE 'LONDON' =
     (SELECT CITY
      FROM S
      WHERE S.S# = SP.S#);

9 ROWS UPDATED

A multiple table update is illustrated by the following command. Even though the tables of the database are only temporaily out of step, with referential integrity in force, the following would not be permitted.

UPDATE S
SET S# = 'S9'
WHERE S# = 'S2';

1 ROW UPDATED

UPDATE SP
SET S# = 'S9'
WHERE S# = 'S2';

2 ROWS UPDATED

The Delete Command

Referential integrity must also be considered when deleting records. The single record delete has the following form. It will be honoured because no records exist in table SP for supplier S5. A similar delete would not be honoured for suppliers S1 through S4.

DELETE
FROM S
WHERE S# = 'S5';

1 ROW DELETED

Multiple record deletes are very powerful. This deletes SP rows with quantities greater than 300.

DELETE
FROM SP
WHERE QTY > 300;

1 ROW DELETED

A form using a subquery can also be used.

DELETE
FROM SP
WHERE 'LONDON' =
     (SELECT CITY
      FROM S
      WHERE S.S# = SP.S#);

8 ROWS DELETED

The following deletes all rows from the table since no where clause is provided! It is not a DROP since an empty table structure exists. It provides a final demonstration of SQLs capability and the dangers of its ill considered use and its use being placed in the wrong hands.

DELETE
FROM SP;

3 ROWS DELETED

Copyright rests with Bernard W Bennetto and  Technology Translation Limited.

Home
Home

Last updated: 11/04/97 19:55:07
Authored and Designed by:
Bernard W Bennetto   bennetto@tt-ltd.com

© Bernard W Bennetto 1996 - 1997