Next: SQL Select Up: Relational Databases Previous: Relational Model Contents

Relational Operators [16]

Algebra

Math expressions using $\pi,\sigma,\delta,\cup,\cap,-,\times,\Join,\div,\Sigma$

SQL

Standard Query Language: creation, modification, and query of relational databases.

Shell SQL

Relational Algebra and SQL in the Unix Shell

              help      project restrict delete create    insert  
              rename    extend  update   unique copy      destroy 
              fields    data    sort     sum    summarize union   
              intersect minus   times    join   divide    compact

sql_select

Powerful SQL command to query databases.

VISUAL DB

Query-by-Example (QBE) is a GUI to SQL.

VISUAL Btree

GUI to a B-tree (Index).

Help [17]

Purpose

To list help for all Shell SQL commands.

Shell SQL

sql_help

$\begin{picture}(100,50)(20,845) \thicklines\put( 20,680){\framebox (120,140){}} \put( 67,745){\makebox(0,0)[lb]{\raisebox{0pt}[0pt][0pt]{\Huge ?}}} \end{picture}$

sql_create sql_insert sql_fields sql_column sql_data sql_sort sql_project sql_union sql_join sql_restrict sql_delete sql_update sql_where sql_parse sql_copy sql_sum sql_summarize sql_intersect sql_minus sql_times sql_divide sql_rename sql_extend sql_unique sql_destroy sql_select sql_compact

Project [18]

Purpose

To list specified columns of a table.

Algebra

$\pi_{S\char93 }(S)$
$\pi_{COLOR, CITY} (P)$

Shell SQL

sql_project S S#
sql_project P COLOR CITY

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,700){\framebox (100,120){}} ... ... \put(100,740){\line(-1,-1){ 20}} \put(100,720){\line(-1,-1){ 20}} \end{picture}$

COLOR CITY Red London Green Paris Blue Rome Red London Blue Paris Red London

sql_project (Help) [19]

SQL: PROJECT
   Usage  : sql_project <tbl> <fld1> <fld2>...<fldn> 
   Output : stdout
   Purpose: To list specified fields of table <tbl> in a specified order.
   Example: sql_project animals color sex
   Comment: Two columns of the animals table are listed.
   Example: sql_project animals all
   Comment: All columns of the animals table are listed.
   
   animals.tbl:                stdout:
   +------+---+-----+         +-----+---+
   |animal|sex|color|         |color|sex|
   +------+---+-----+ project +-----+---+
   |  21  | m |brown| ------> |brown| m |
   |  22  | m |black|         |black| m | 
   |  13  | m |brown|         |brown| m |
   +------+---+-----+         +-----+---+

Restrict [20]

Purpose

To list rows of a table based on a condition.

Algebra

$\sigma_{COLOR=''Red''}(P)$
$\sigma_{COLOR=''Red'' \vee CITY=''Rome''} (P)$

Shell SQL

sql_restrict P where 'COLOR=="Red"'
sql_restrict P where '(COLOR=="Red")||(CITY=="Rome")'

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,700){\framebox (100,120){}} ... ... \put(100,740){\line(-1,-1){ 20}} \put(120,740){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY P1 Nut Red 12 London P3 Screw Blue 17 Rome P4 Screw Red 14 London P6 Cog Red 19 London

sql_restrict (Help) [21]

SQL: RESTRICT
   Usage  : sql_restrict <tbl> [where "<condition>"]
   Output : stdout
   Purpose: To list records of table <tbl> based on <condition>.
   Default: All records are listed.
   Example: sql_restrict animals where "animal==13"
   Example: sql_restrict animals where '(animal==13)||(color=="brown")'
   Rule   : Must use single quotes on the outside.
   Rule   : Must use double quotes for string values on the inside.
   Comment: Animal records are listed that match the condition.
   
   animals.tbl:                stdout:
   +------+---+-----+          +------+---+-----+
   |animal|sex|color|          |animal|sex|color|
   +------+---+-----+          +------+---+-----+
   |  21  | m |brown| restrict |  21  | m |brown|
   |  22  | m |black| -------> |  13  | m |brown|
   |  13  | m |brown|          +------+---+-----+
   +------+---+-----+

Delete [22]

Purpose

To delete rows of a table based on a condition.

Algebra

$\sigma_{\neg COLOR=''Red''}(P)$
$\sigma_{\neg (COLOR=''Red'' \vee CITY=''Rome'')} (P)$

Shell SQL

sql_delete P where 'COLOR=="Red"'
sql_delete P where '(COLOR=="Red")||(CITY=="Rome")'

sql_delete (Help) [23]

SQL: DELETE
   Usage  : sql_delete <tbl> [where "<condition>"]
   Output : stdout
   Purpose: To delete records in table <tbl> based on <condition>.
   Default: All records are deleted.
   Example: sql_delete animals where "animal==13"
   Comment: Delete the record for animal 13.
   
   animals.tbl:              stdout:
   +------+---+-----+        +------+---+-----+
   |animal|sex|color|        |animal|sex|color| 
   +------+---+-----+        +------+---+-----+
   |  21  | m |brown| delete |  21  | m |brown|
   |  22  | m |black| -----> |  22  | m |black|
   |  13  | m |brown|        +------+---+-----+
   +------+---+-----+

Create [24]

Purpose

To make a new table with specified columns.

Shell SQL

sql_create S S# SNAME STATUS CITY
sql_create P P# PNAME COLOR WEIGHT CITY

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,800){\framebox (120,20){}} \... ... \put(120,820){\line(-1,-1){ 20}} \put(140,820){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY

sql_create (Help) [25]

SQL: CREATE
   Usage  : sql_create <tbl> <fld1> <fld2>...<fldn> 
   Output : new file <tbl>
   Purpose: To create a new table with n fields.
   Example: sql_create animals animal sex color
   Comment: The new animals table has three fields - try "more animals.tbl".
   
          animals.tbl:
   create +------+---+-----+
   -----> |animal|sex|color|
          +------+---+-----+

Insert [26]

Purpose

To add a new row to a table.

Shell SQL

sql_insert P P6 Cog Red 19 London

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put(120,700){\line(-1,-1){ 20}} \put(140,700){\line(-1,-1){ 20}} \end{picture}$

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

sql_insert (Help) [27]

SQL: INSERT
   Usage  : sql_insert <tbl> <val1> <val2>...<valn> 
   Output : update file <tbl>
   Purpose: To add new data values to a table <tbl>.
   Example: sql_insert animals 21 m brown
   Example: sql_insert animals 22 m black
   Example: sql_insert animals 13 m brown
   Comment: Three new records are added to animals - try "more animals.tbl".
   
   animals.tbl:            animals.tbl:                   animals.tbl: 
   +------+---+-----+insert+------+---+-----+insert insert+------+---+-----+
   |animal|sex|color| ---> |animal|sex|color| --->   ---> |animal|sex|color|
   +------+---+-----+      +------+---+-----+             +------+---+-----+ 
                           |  21  | m |brown|             |  21  | m |brown|
                           +------+---+-----+             |  22  | m |black|
                                                          |  13  | m |brown|
                                                          +------+---+-----+

Rename [28]

Purpose

To change the name of a column.

Algebra

$\delta_{CITY\rightarrow SCITY}(S)$
$\delta_{CITY\rightarrow PCITY}(P)$

Shell SQL

sql_rename S CITY SCITY
sql_rename P CITY PCITY

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put(120,800){\line( 0, 1){ 20}} \put(120,820){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT PCITY 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

sql_rename (Help) [29]

SQL: RENAME
   Usage  : sql_rename <tbl> <fld1> <fld2>
   Output : stdout
   Purpose: To change the name of field <fld1> to field <fld2>.
   Example: sql_rename animals sex gender
   Comment: The second column is now gender.
   Example: sql_rename animals all a.
   Comment: All fields start with "a.".
  
   animals.tbl:              stdout:
   +------+---+-----+        +------+------+-----+
   |animal|sex|color|        |animal|gender|color|
   +------+---+-----+        +------+------+-----+
   |  21  | m |brown| rename |  21  |  m   |brown|
   |  22  | m |black| -----> |  22  |  m   |black|
   |  13  | m |brown|        |  13  |  m   |brown|
   +------+---+-----+        +------+------+-----+

Extend [30]

Purpose

To add a new column to a table.

Shell SQL

sql_extend S PHONE
sql_extend P PHONE

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (80,140){}} \... ... \put(140,720){\line(-1,-1){ 20}} \put(140,700){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY PHONE 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

sql_extend (Help) [31]

SQL: EXTEND
   Usage  : sql_extend <tbl> <fld1> <fld2>...<fldn> 
   Output : stdout
   Purpose: To add on new fields to table <tbl>.
   Example: sql_extend animals status >temp.tbl
   Comment: A status field is added with null data.
   Example: sql_update temp set 'status = 1'
   Comment: The null data is converted to an OK status.
  
   animals.tbl:              stdout:
   +------+---+-----+        +------+---+-----+------+
   |animal|sex|color|        |animal|sex|color|status|
   +------+---+-----+        +------+---+-----+------+
   |  21  | m |brown| extend |  21  | m |brown|      |
   |  22  | m |black| -----> |  22  | m |black|      |
   |  13  | m |brown|        |  13  | m |brown|      |
   +------+---+-----+        +------+---+-----+------+

Update [32]

Purpose

To change data in a column based on a condition.

Shell SQL

sql_extend P PHONE >temp.tbl
sql_update temp set 'CITY="Athens";
PHONE="555-5555"' where 'P#=="P3"'

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put( 60,800){\line(-1,-1){ 20}} \put( 60,720){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY PHONE P1 Nut Red 12 London P2 Bolt Green 17 Paris P3 Screw Blue 17 Athens 555-5555 P4 Screw Red 14 London P5 Cam Blue 12 Paris P6 Cog Red 19 London

sql_update (Help) [33]

SQL: UPDATE
   Usage  : sql_update <tbl> set "<fld1> = <expr1> ;...; fldn = <exprn>" 
                       where ["<condition>"]
   Output : stdout
   Purpose: To modify data in <tbl> based on <condition>.
   Default: All records are modified.
   Example: sql_update animals set "animal=13"
   Example: sql_update animals set 'color="brown"'
   Rule   : Must use single quotes on the outside.
   Rule   : Must use double quotes for string values on the inside.
   Comment: All animals are brown.
   Example: sql_update animals set 'color="brown"' where "animal==13"
   Comment: Animal 13 is brown.
   Example: sql_update animals set 'animal=animal+1; color="brown"'
   Comment: All animal numbers are incremented and all colors are brown.
   
   animals.tbl:              stdout:
   +------+---+-----+        +------+---+-----+
   |animal|sex|color|        |animal|sex|color|
   +------+---+-----+        +------+---+-----+
   |  21  | m |brown| update |  22  | m |brown|
   |  22  | m |black| -----> |  23  | m |brown|
   |  13  | m |brown|        |  14  | m |brown|
   +------+---+-----+        +------+---+-----+

Unique [34]

Purpose

To remove duplicate rows from a table.

Algebra

AUTOMATIC

Shell SQL

sql_project P COLOR CITY >temp.tbl
sql_unique temp

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,700){\framebox (100,120){}} ... ... \put(100,740){\line(-1,-1){ 20}} \put(120,740){\line(-1,-1){ 20}} \end{picture}$

COLOR CITY Red London Green Paris Blue Rome Blue Paris

sql_unique (Help) [35]

SQL: UNIQUE
   Usage  : sql_unique <tbl>
   Output : stdout
   Purpose: To show only the unique records in <tbl>
   Example: sql_insert animals 21 f brown
   Example: sql_unique animals
   Comment: The duplicate records are removed.
   
   animals.tbl:              stdout:
   +------+---+-----+        +------+---+-----+
   |animal|sex|color|        |animal|sex|color|
   +------+---+-----+        +------+---+-----+
   |  21  | f |brown| unique |  21  | f |brown|
   |  22  | f |black| -----> |  20  | f |black|
   |  13  | f |brown|        |  13  | f |brown|
   |  21  | f |brown|        +------+---+-----+
   +------+---+-----+

Copy [36]

Purpose

To copy the contents of one table to a new table.

Shell SQL

sql_copy P NEWP

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} \put( 40,660){\framebox (120,140){}} \end{picture}$

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

sql_copy (Help) [37]

SQL: COPY
   Usage  : sql_copy <tbl1> <tbl2> 
   Output : new file <tbl2>
   Purpose: To copy table <tbl1> to a new table <tbl2>.
   Example: sql_update animals set 'sex="f"' >temp.tbl
   Rule   : Must use single quotes on the outside.
   Rule   : Must use double quotes for string values on the inside.
   Example: sql_destroy animals
   Example: sql_copy temp animals
   Comment: Update makes temp.tbl, which is copied to animals table.
   
   animals.tbl:              temp.tbl:               animals.tbl:
   +------+---+-----+        +------+---+-----+      +------+---+-----+
   |animal|sex|color|        |animal|sex|color|      |animal|sex|color|
   +------+---+-----+        +------+---+-----+      +------+---+-----+
   |  21  | m |brown| update |  21  | f |brown| copy |  21  | f |brown|
   |  22  | m |black| -----> |  22  | f |black| ---> |  22  | f |black|
   |  13  | m |brown|        |  13  | f |brown|      |  13  | f |brown|
   +------+---+-----+        +------+---+-----+      +------+---+-----+

Destroy [38]

IBdestroy

Purpose

To remove a table.

Shell SQL

sql_destroy P

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ...0,820){\line( 5,-6){118.033}} \put(140,820){\line(-5,-6){118.033}} \end{picture}$

sql_destroy (Help) [39]

SQL: DESTROY
   Usage  : sql_destroy <tbl> 
   Output : empty
   Purpose: To remove table <tbl>.
   Example: sql_destroy animals
   Comment: The animals table is removed.
   SeeAlso: sql_copy   
   
   animals.tbl:
   +------+---+-----+
   |animal|sex|color|
   +------+---+-----+
   |  21  | f |brown| destroy
   |  22  | f |black| ------> 
   |  13  | f |brown|      
   +------+---+-----+

Fields [40]

Purpose

To get the names of the columns in a table.

Shell SQL

sql_fields P

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put(120,820){\line(-1,-1){ 20}} \put(140,820){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY

sql_fields (Help) [41]

SQL: FIELDS
   Usage  : sql_fields <tbl> 
   Output : stdout
   Purpose: To list field names in <tbl> without the data.
   Example: sql_fields animals
   Comment: The names of the fields for animals are displayed.
   
   animals.tbl:              stdout:
   +------+---+-----+ fields +------+---+-----+
   |animal|sex|color| -----> |animal|sex|color|
   +------+---+-----+        +------+---+-----+
   |  21  | m |brown| 
   |  22  | m |black| 
   |  13  | m |brown|
   +------+---+-----+

Data [42]

Purpose

To get the data in the columns in a table.

Shell SQL

sql_data P

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put(140,720){\line(-1,-1){ 40}} \put(140,700){\line(-1,-1){ 20}} \end{picture}$

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

sql_data (Help) [43]

SQL: DATA
   Usage  : sql_data <tbl>
   Output : stdout
   Purpose: To list data in table <tbl> without field names.
   Example: sql_data animals
   Comment: List the data in the animals table.
   
   animals.tbl:            stdout:
   +------+---+-----+                        
   |animal|sex|color|                        
   +------+---+-----+      +------+---+-----+
   |  21  | m |brown| data |  21  | m |brown|
   |  22  | m |black| ---> |  22  | m |black|
   |  13  | m |brown|      |  13  | m |brown|
   +------+---+-----+      +------+---+-----+

Sort [44]

Purpose

To order the data based on one or more columns.

Shell SQL

sql_sort S SNAME STATUS
sql_sort P WEIGHT

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ...t(110,760){\vector( 0,-1){ 20}} \put(110,720){\vector( 0,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY P1 Nut Red 12 London P5 Cam Blue 12 Paris P4 Screw Red 14 London P2 Bolt Green 17 Paris P3 Screw Blue 17 Rome P6 Cog Red 19 London

sql_sort (Help) [45]

SQL: SORT
   Usage  : sql_sort <tbl> <fld1> <fld2>...<fldn>
   Output : stdout
   Purpose: To list table <tbl> sorted on first key <fld1>, then <fld2>...
   Example: sql_sort animals animal
   Comment: The animals table is listed according to animal.
   
   animals.tbl:            stdout:
   +------+---+-----+      +------+---+-----+
   |animal|sex|color|      |animal|sex|color|
   +------+---+-----+      +------+---+-----+
   |  21  | m |brown| sort |  13  | m |brown|
   |  22  | m |black| ---> |  21  | m |brown|
   |  13  | m |brown|      |  22  | m |black|
   +------+---+-----+      +------+---+-----+

Sum [46]

Purpose

To add up the data in one or more columns.

Shell SQL

sql_sum P WEIGHT

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ...+}}} \put(100,705){\makebox(0,0)[lb]{\raisebox{0pt}[0pt][0pt]{+}}} \end{picture}$

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 ------ 91 ======

sql_sum (Help) [47]

SQL: SUM
   Usage  : sql_sum <tbl> <fld1> <fld2>...<fldn> 
   Output : stdout
   Purpose: To list all fields of <tbl> and to sum the specified field.
   Example: sql_sum animals animal
   Comment: Three columns are listed along with a summation of animal.
   
   animals.tbl:           stdout:
   +------+---+-----+     +------+---+-----+
   |animal|sex|color|     |animal|sex|color|
   +------+---+-----+ sum +------+---+-----+
   |  21  | f |brown| --> |  21  | f |brown|
   |  22  | f |black|     |  22  | f |black|
   |  13  | f |brown|     |  13  | f |brown|
   +------+---+-----+     +------+---+-----+
                           ------  
                             56
                           ======

Summarize [48]

Purpose

To summarize the data in a column.

Algebra

$\Sigma_{WEIGHT,PNAME}(P)$

Shell SQL

sql_summarize P WEIGHT PNAME

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put(120,660){\line(-1,-1){ 20}} \put(140,660){\line(-1,-1){ 20}} \end{picture}$

COL CNT SUM AVG MIN MAX WEIGHT 6 91.0 15.2 12 19 PNAME 6 0.0 0.0 Bolt Screw

sql_summarize (Help) [49]

SQL: SUMMARIZE
   Usage  : sql_summarize <tbl> <fld1> <fld2>...
   Output : stdout
   Purpose: Summarize the fields <fld1> <fld2>...in table <tbl>.
   Example: sql_summarize animals animal color
   Comment: The result is a table that can be projected, etc.
 
   animals.tbl:                 stdout:
   +------+---+-----+           +------+---+----+----+-----+-----+
   |animal|sex|color|           |   COL|CNT| SUM| AVG|  MIN|  MAX|
   +------+---+-----+ summarize +------+---+----+----+-----+-----+
   |  21  | f |brown| --------> |animal| 3 |56.0|18.7|   13|   22|
   |  22  | f |black|           | color| 3 | 0.0| 0.0|black|brown|
   |  13  | f |brown|           +------+---+----+----+-----+-----+
   +------+---+-----+

Union [50]

Purpose

To combine the data in two tables.

Algebra

$\sigma_{CITY=''Rome''}(P) \bigcup \sigma_{COLOR=''Red''}(P)$

Shell SQL

sql_restrict P where 'CITY=="Rome"' >temp.tbl
sql_restrict P where 'COLOR=="Red"' >temp1.tbl
sql_union temp temp1

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put(160,700){\line(-1,-1){ 40}} \put(160,680){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY P3 Screw Blue 17 Rome P1 Nut Red 12 London P4 Screw Red 14 London P6 Cog Red 19 London

sql_union (Help) [51]

SQL: UNION
   Usage  : sql_union <tbl1> <tbl2> 
   Output : stdout
   Purpose: To combine the data of both table <tbl1> and <tbl2>.
   Example: sql_union animals animals
   Rule   : Must union tables with the same number of fields (columns).
   Comment: The new table has all of the data.
   
   animals.tbl:             animals.tbl:          stdout: 
   +------+---+-----+       +------+---+-----+    +------+---+-----+
   |animal|sex|color|       |animal|sex|color|    |animal|sex|color|
   +------+---+-----+       +------+---+-----+    +------+---+-----+
   |  21  | m |brown| union |  21  | m |brown| -> |  21  | m |brown|
   |  22  | m |black|       |  22  | m |black|    |  22  | m |black|
   |  13  | m |brown|       |  13  | m |brown|    |  13  | m |brown|
   +------+---+-----+       +------+---+-----+    |  21  | m |brown|
                                                  |  22  | m |black|
                                                  |  13  | m |brown|
                                                  +------+---+-----+

Intersect [52]

Purpose

To find the common records in two tables.

Algebra

$P \bigcap \sigma_{CITY=''Rome''}(P)$

Shell SQL

sql_restrict P where 'CITY=="Rome"' >temp.tbl
sql_intersect P temp

sql_intersect (Help) [53]

SQL: INTERSECT
   Usage  : sql_intersect <tbl1> <tbl2>
   Output : stdout
   Purpose: To show the common (identical) records in <tbl1> and <tbl2>
   Example: sql_intersect animals animals
   Comment: All records are common.
   
   animals.tbl:                 animals.tbl:          stdout:
   +------+---+-----+           +------+---+-----+    +------+---+-----+
   |animal|sex|color|           |animal|sex|color|    |animal|sex|color|
   +------+---+-----+           +------+---+-----+    +------+---+-----+
   |  21  | f |brown| intersect |  21  | f |brown| -> |  21  | f |brown|
   |  22  | f |black|           |  22  | f |black|    |  22  | f |black|
   |  13  | f |brown|           |  13  | f |brown|    |  13  | f |brown|
   +------+---+-----+           +------+---+-----+    +------+---+-----+

Minus (Difference) [54]

Purpose

To find records in A but not in B.

Algebra

$P - \sigma_{CITY=''Rome''}(P)$

Shell SQL

sql_restrict P where 'CITY=="Rome"' >temp.tbl
sql_minus P temp

$\begin{picture}(100,50)(20,810) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put( 40,720){\line(-1,-1){ 20}} \put( 40,700){\line(-1,-1){ 20}} \end{picture}$

P# PNAME COLOR WEIGHT CITY P1 Nut Red 12 London P2 Bolt Green 17 Paris P4 Screw Red 14 London P5 Cam Blue 12 Paris P6 Cog Red 19 London

sql_minus (Help) [55]

SQL: MINUS 
   Usage  : sql_minus <tbl> <tbl2> 
   Output : stdout
   Purpose: To list records in table <tbl1> that do not appear in table <tbl2>.
   Example: sql_restrict animals where "animal==13" >temp.tbl
   Example: sql_minus animals temp 
   Comment: All animal records are listed except 13.
  
   animals.tbl:             temp.tbl:             stdout: 
   +------+---+-----+       +------+---+-----+    +------+---+-----+
   |animal|sex|color|       |animal|sex|color|    |animal|sex|color| 
   +------+---+-----+       +------+---+-----+    +------+---+-----+
   |  21  | m |brown| minus |  13  | m |brown| -> |  21  | m |brown|
   |  22  | m |black|       +------+---+-----+    |  22  | m |black|
   |  13  | m |brown|                             +------+---+-----+
   +------+---+-----+

Times (Product) [56]

Purpose

To list all records in table 1 with all records in table 2.

Algebra

$\sigma_{CITY=''Paris''}(S) \times \sigma_{COLOR=''Red''}(P)$

Shell SQL

sql_restrict S where 'CITY=="Paris"' >temp.tbl
sql_restrict P where 'COLOR=="Red"' >temp1.tbl
sql_times temp temp1

$\begin{picture}(100,50)(20,845) \thicklines\put( 20,800){\framebox (60,20){}} \p... ... \put(160,720){\line(-1,-1){ 40}} \put(160,700){\line(-1,-1){ 20}} \end{picture}$

S# SNAME STATUS CITY P# PNAME COLOR WEIGHT CITY S2 Jones 10 Paris P1 Nut Red 12 London S2 Jones 10 Paris P4 Screw Red 14 London S2 Jones 10 Paris P6 Cog Red 19 London S3 Blake 30 Paris P1 Nut Red 12 London S3 Blake 30 Paris P4 Screw Red 14 London S3 Blake 30 Paris P6 Cog Red 19 London

sql_times (Help) [57]

SQL: TIMES
   Usage  : sql_times <tbl> <tbl2> 
   Output : stdout
   Purpose: To list every record in <tbl1> with every record in <tbl2>.
   Example: sql_project animals animal >temp.tbl
   Example: sql_project animals color >temp1.tbl
   Example: sql_times temp temp1
   Comment: All animals are listed with every color.
  
   temp.tbl:      temp1.tbl: stdout: 
   +------+       +-----+    +------+-----+
   |animal|       |color|    |animal|color| 
   +------+       +-----+    +------+-----+
   |  21  | times |brown| -> |  21  |brown|
   |  22  |       |black|    |  21  |black|
   |  13  |       +-----+    |  22  |brown|
   +------+                  |  22  |black|
                             |  13  |brown| 
                             |  13  |black| 
                             +------+-----+

Join [58]

Purpose

To combine 2 tables using common data in field(s).

Algebra

$S \Join P$

Shell SQL

sql_join S P CITY CITY

$\begin{picture}(100,50)(20,845) \thicklines\put( 20,680){\framebox (40,140){}} \... ... \put(140,720){\line(-1,-1){ 40}} \put(140,700){\line(-1,-1){ 20}} \end{picture}$

CITY S# SNAME STATUS P# PNAME COLOR WEIGHT London S1 Smith 20 P1 Nut Red 12 London S1 Smith 20 P4 Screw Red 14 London S1 Smith 20 P6 Cog Red 19 London S4 Clark 20 P1 Nut Red 12 London S4 Clark 20 P4 Screw Red 14 London S4 Clark 20 P6 Cog Red 19 Paris 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

sql_join (Help) [59]

SQL: JOIN
   Usage  : sql_join <tbl1> <tbl2> [<fld1> <fld2>] 
   Output : stdout
   Purpose: To join tables <tbl1> and <tbl2> based on the values in the fields.
   Default: <fld1> is the first field of <tbl1>
   Default: <fld2> is the first field of <tbl2>
   Example: sql_create rooms animal room box
   Example: sql_insert rooms 22 R1 B6 
   Example: sql_insert rooms 21 R1 B7 
   Example: sql_join animals rooms animal animal
   Example: sql_join animals rooms
   Comment: The new table has field names and data from the input tables.
   Rule   : Do not output unmatched records.
   
   animals.tbl:            rooms.tbl            stdout: 
   +------+---+-----+      +------+----+---+    +------+---+-----+----+---+
   |animal|sex|color|      |animal|room|box|    |animal|sex|color|room|box| 
   +------+---+-----+      +------+----+---+    +------+---+-----+----+---+
   |  21  | m |brown| join |  22  | R1 | B6| -> |  21  | m |brown| R1 | B7|
   |  22  | m |black|      |  21  | R1 | B7|    |  22  | m |black| R1 | B6|
   |  13  | m |brown|      +------+----+---+    +------+---+-----+----+---+
   +------+---+-----+

Divide (Quotient) [60]

Purpose

To find the entire image of table 2 next to fields of table 1 with common values.

Algebra

$\pi_{S\char93 ,P\char93 }(SP) \div \pi_{P\char93 }(P)$

Shell SQL

sql_project SP S# P# >temp.tbl
sql_project P P# >temp1.tbl
sql_divide temp temp1

Example

Find the S# that supplies ALL of the parts.

$\begin{picture}(100,50)(20,845) \thicklines\put( 20,680){\framebox (120,140){}} ... ... \put( 80,800){\line(-1,-1){ 40}} \put( 80,780){\line(-1,-1){ 20}} \end{picture}$

S# S1

sql_divide (Help) [61]

SQL: DIVIDE
   Usage  : sql_divide <tbl> <tbl2> 
   Output : stdout
   Purpose: To list records in <tbl1> that match all records in <tbl2>.
   Example: sql_project animals sex color >temp.tbl
   Example: sql_project animals color >temp1.tbl
   Example: sql_divide temp temp1 
   Comment: Which sexes have all of the colors?
  
   temp.tbl:          temp1.tbl: stdout: 
   +---+-----+        +-----+    +---+
   |sex|color|        |color|    |sex| 
   +---+-----+        +-----+    +---+
   | m |brown| divide |brown| -> | m |
   | m |black|        |black|    +---+
   | m |brown|        |brown|        
   | f |brown|        |black|
   +---+-----+        +-----+

Compact [62]

Purpose

To make a compact display by removing extra blanks.

Shell SQL

sql_restrict P where 'P#=="P1"' | sql_compact -
sql_compact P

$\begin{picture}(100,50)(20,845) \thicklines\put( 20,680){\framebox (80,140){}} \... ... \put(140,720){\line( 1,-1){ 20}} \put(140,700){\line( 1,-1){ 20}} \end{picture}$

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

sql_compact (Help) [63]

SQL: COMPACT
   Usage  : sql_compact <tbl> 
   Usage  : | sql_compact -
   Output : stdout
   Purpose: To make a compact display by removing extra blanks.
   Example: sql_compact animals 
   Comment: Input can be piped to compact using the "-" option.

   animals.tbl: (or stdin:)             stdout:
   +--------+--------+--------+         +------+---+-------+
   |  animal|     sex|   color|         |animal|sex|  color|
   +--------+--------+--------+ compact +------+---+-------+
   |      21|       m|   brown| ------> |    21|  m|  brown|
   |      22|       m|   black|         |    22|  m|  black|
   |      13|       m|   brown|         |    13|  m|  brown|
   |      77|       m| magenta|         |    77|  m|magenta|
   +--------+--------+--------+         +------+---+-------+

Next: SQL Select Up: Relational Databases Previous: Relational Model Contents

Ted Billard 2001-10-31