A Guide to the Use of SQLBase SQL Based on DateBernard W Bennetto - Technology Translation Ltd

IntroductionSeveral 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.

The DatabaseThe 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

CITYS1

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

CITYP1

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#

QTYS1 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 AlgebraPart 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 OperatorThe 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 SELECTEDThe 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.

Given the relation/table

P#

PNAME

COLOR

WEIGHT

CITYP1

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 SELECTEDDate 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 SELECTEDThe 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 SELECTEDIn 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 OperatorThe 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.

It has the form

restrict <relation> where <condition>Given the same relation/table P representing parts

P#

PNAME

COLOR

WEIGHT

CITYP1

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 SELECTEDThe 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 SELECTEDThe 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 PredicateRelational 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 INSERTEDThe 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 SELECTEDBecause 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 SELECTEDThe 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 OperatorUNION 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.

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 200P2 BOLT GREEN 17 PARIS

S1 P3 400P3 SCREW BLUE 17 ROME

S1 P4 200 P4 SCREW RED 14 LONDON

S1 P5 100 P5 CAM BLUE 12 PARIS

S1 P6 100P6 COG RED 19 LONDON

S2 P1 300

S2 P2 400

S3 P2 200

S4 P2 200

S4 P4 300

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

(restrict sp where S# = 'S2') union (restrict P where weight > 17) giving Cand 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 19Thus 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 OperatorThese 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.

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 cand

- 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 2ABC

a 1 a 1

b b 1

c c 1

2 a 2

b 2

c 2

3 rows 2 rows 6 rowsThe algebraic form

a times b giving Cis 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 SELECTEDThe 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.

Queries Based Upon the Join OperatorsSuch 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! <

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

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#QTYSNAMESTATUSCITYS1 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 LONDONS1 SMITH 20 P4 SCREW RED 14 LONDONS1 SMITH 20 P6 COG RED 19 LONDONS2 JONES 10 P2 BOLT GREEN 17 PARISS2 JONES 10 P5 CAM BLUE 12 PARISS3 BLAKE 30 P2 BOLT GREEN 17 PARISS3 BLAKE 30 P5 CAM BLUE 12 PARISS4 CLARK 20 P1 NUT RED 12 LONDONS4 CLARK 20 P4 SCREW RED 14 LONDONS4 CLARK 20 P6 COG RED 19 LONDON10 ROWS SELECTEDThe 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 LONDONS1 SMITH 20 LONDON P4 SCREW RED 14 LONDONS1 SMITH 20 LONDON P6 COG RED 19 LONDONS2 JONES 10 PARIS P2 BOLT GREEN 17 PARISS2 JONES 10 PARIS P5 CAM BLUE 12 PARISS3 BLAKE 30 PARIS P2 BOLT GREEN 17 PARISS3 BLAKE 30 PARIS P5 CAM BLUE 12 PARISS4 CLARK 20 LONDON P1 NUT RED 12 LONDONS4 CLARK 20 LONDON P4 SCREW RED 14 LONDONS4 CLARK 20 LONDON P6 COG RED 19 LONDON10 ROWS SELECTEDThis 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 JoinSQLBase 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 SELECTEDAn 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 JoinsSuch 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 PARISThe 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 ROMEThis 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 SELECTEDThis 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 FunctionsThere 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 SELECTEDWhile 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 SELECTEDThis 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 SELECTEDWHERE 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 SELECTEDTo 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 AggregratesThere 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 SELECTEDThe 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 PredicatesThe 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 SELECTEDThe 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 SELECTEDAnother 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 SELECTEDAnother 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 SubqueriesThe 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 SELECTEDIn 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 SELECTEDIt 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 SELECTEDThe 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 SELECTEDThe 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 SELECTEDAggregrate 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 OperatorsTo 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 SELECTEDThe 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 CThis yields the following set of data a relation of 4 rows with the following components.

INTERSECTION

S2

P2- -

- -

P2

17DIFFERENCE A

S2P1- -

DIFFERENCE B - - P317

- -

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 SELECTEDOne 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 SELECTEDThe 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 PredicatesThe 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 SELECTEDThe 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 SELECTEDA 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 CommandWithin 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 INSERTEDWhen 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 INSERTEDTo 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 INSERTEDThe 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 CommandThe 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 UPDATEDA multiple record update would be provided by the following command.

UPDATE S

SET STATUS = 2 * STATUS

WHERE CITY = 'LONDON';

2 ROWS UPDATEDA 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 UPDATEDA 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 CommandReferential 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 DELETEDMultiple record deletes are very powerful. This deletes SP rows with quantities greater than 300.

DELETE

FROM SP

WHERE QTY > 300;

1 ROW DELETEDA form using a subquery can also be used.

DELETE

FROM SP

WHERE 'LONDON' =

(SELECT CITY

FROM S

WHERE S.S# = SP.S#);

8 ROWS DELETEDThe 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.

Last updated: 11/04/97 19:55:07

Authored and Designed by:

Bernard W Bennetto bennetto@tt-ltd.com© Bernard W Bennetto 1996 - 1997