Return to tutorial.nr CVS log

Up to [CSRG BSD Unix] / 42BSD / ingres / doc / other

Annotation of 42BSD/ingres/doc/other/tutorial.nr, revision 1.1

1.1     ! root        1: .de @h
        !             2: 'sp 4
        !             3: 'tl ''''
        !             4: 'sp 2
        !             5: .ns
        !             6: ..
        !             7: .wh 0 @h
        !             8: .po 5
        !             9: .rs
        !            10: 
        !            11: .sp 4
        !            12: .ce
        !            13: A TUTORIAL ON INGRES
        !            14: .sp 15
        !            15: .ce
        !            16: by
        !            17: .ce
        !            18: Robert Epstein
        !            19: .sp 18
        !            20: .ce 3
        !            21: Memorandum No. ERL - M77-25
        !            22: December 15, 1977
        !            23: (Revised)
        !            24: .sp 3
        !            25: .ce 4
        !            26: Electronics Research Laboratory
        !            27: College of Engineering
        !            28: University of California, Berkeley
        !            29: 94720
        !            30: .bp 1
        !            31: .de @f
        !            32: 'sp 3
        !            33: 'tl 'A Tutorial on INGRES''Page %'
        !            34: 'bp
        !            35: ..
        !            36: .wh -7 @f
        !            37: .rs
        !            38: .ce 1
        !            39: A Tutorial on INGRES
        !            40: .sp 5
        !            41: This tutorial describes how to use the INGRES data base
        !            42: management system.
        !            43: You should be able to follow the the examples
        !            44: given here and observe the same results.
        !            45: 
        !            46: The data manipulation language supported by the
        !            47: INGRES system is called
        !            48: QUEL (QUEry Language).
        !            49: Complete information on QUEL and INGRES appears
        !            50: in the INGRES reference manual.
        !            51: This tutorial does not attempt to
        !            52: cover every detail of INGRES.
        !            53: 
        !            54: Begin by logging onto UNIX, the
        !            55: time sharing system under which INGRES runs.
        !            56: If at all possible, use a terminal
        !            57: that has both upper and lower case letters;
        !            58: otherwise life is going to be miserable for you.
        !            59: If you are on an upper case only terminal,
        !            60: type "\\\\" everywhere "\\" appears in the
        !            61: tutorial.
        !            62: 
        !            63: There should currently be a "%" printed on your terminal.
        !            64: To start using INGRES type the command:
        !            65: .nf
        !            66: 
        !            67: % ingres demo
        !            68: 
        !            69: .fi
        !            70: This requests "UNIX" to invoke INGRES using the
        !            71: data base called "demo".
        !            72: After a few seconds, the following will appear:
        !            73: .nf
        !            74: 
        !            75: INGRES version 6.1/0 login
        !            76: Tue Aug 30 14:52:23 1977
        !            77: 
        !            78: COPYRIGHT
        !            79: The Regents of the University of California
        !            80: 1977
        !            81: 
        !            82: This program material is the property of the
        !            83: Regents of the University of California and
        !            84: may not be reproduced or disclosed without
        !            85: the prior written permission of the owner.
        !            86: 
        !            87: go
        !            88: *
        !            89: 
        !            90: .fi
        !            91: The first two lines include the INGRES version
        !            92: number (in this case version 6.1) and the current date.
        !            93: Following that is the "dayfile",
        !            94: which includes
        !            95: messages related to the
        !            96: INGRES system.
        !            97: The "go" indicates that INGRES
        !            98: is ready for your interactions.
        !            99: 
        !           100: The INGRES monitor
        !           101: prints an asterisk ("*") at the beginning of
        !           102: each line
        !           103: to remind you that INGRES is waiting for input.
        !           104: 
        !           105: Type the command:
        !           106: .nf
        !           107: 
        !           108: * print parts
        !           109: * \\g
        !           110: Executing . . .
        !           111: 
        !           112: .fi
        !           113: The line "print parts" requests a printout of
        !           114: some data stored in the data base.
        !           115: The "\\g" means "go".
        !           116: The message "Executing . . ."
        !           117: indicates that INGRES is processing your
        !           118: query.
        !           119: The following then appears:
        !           120: .nf
        !           121: 
        !           122: parts relation
        !           123: 
        !           124: |pnum  |pname               |color   |weight|qoh   |
        !           125: |--------------------------------------------------|
        !           126: |     1|central processor   |pink    |    10|     1|
        !           127: |     2|memory              |gray    |    20|    32|
        !           128: |     3|disk drive          |black   |   685|     2|
        !           129: |     4|tape drive          |black   |   450|     4|
        !           130: |     5|tapes               |gray    |     1|   250|
        !           131: |     6|line printer        |yellow  |   578|     3|
        !           132: |     7|l-p paper           |white   |    15|    95|
        !           133: |     8|terminals           |blue    |    19|    15|
        !           134: |    13|paper tape reader   |black   |   107|     0|
        !           135: |    14|paper tape punch    |black   |   147|     0|
        !           136: |     9|terminal paper      |white   |     2|   350|
        !           137: |    10|byte-soap           |clear   |     0|   143|
        !           138: |    11|card reader         |gray    |   327|     0|
        !           139: |    12|card punch          |gray    |   427|     0|
        !           140: |--------------------------------------------------|
        !           141: 
        !           142: continue
        !           143: *
        !           144: 
        !           145: .fi
        !           146: What is printed on your terminal is the "parts relation".
        !           147: Intuitively, a relation is nothing more that a
        !           148: table with rows and columns.
        !           149: 
        !           150: In this case the relation name is "parts".
        !           151: There are five columns (we call them domains)
        !           152: named pnum (part number), pname (part name),
        !           153: color, weight, qoh (quantity on hand).
        !           154: Each row of the relation (called a tuple)
        !           155: represents one entry, which in this
        !           156: case represents one part in a computer
        !           157: installation.
        !           158: A relation can have up to 49 domains and a
        !           159: virtually unlimited number of tuples.
        !           160: 
        !           161: Notice that after the query is executed, INGRES prints
        !           162: "continue", while when we first entered INGRES
        !           163: it printed "go".
        !           164: As you enter a query INGRES
        !           165: saves what you type in
        !           166: a "workspace".
        !           167: If you
        !           168: ever mistype a query, typing "\\r"
        !           169: will "reset" (ie. erase) your workspace.
        !           170: (Later on we will
        !           171: learn ways to edit mistakes so
        !           172: we don't have to retype the entire query.)
        !           173: 
        !           174: At any time you can see what is in the
        !           175: workspace by typing "\\p".
        !           176: Try typing "\\p":
        !           177: 
        !           178: .nf
        !           179: * \\p
        !           180: print parts
        !           181: *
        !           182: 
        !           183: .fi
        !           184: The current contents of the workspace
        !           185: is printed.
        !           186: Now try typing "\\r":
        !           187: 
        !           188: .nf
        !           189: * \\r
        !           190: go
        !           191: *
        !           192: 
        !           193: .fi
        !           194: The workspace is now empty.
        !           195: Whenever
        !           196: INGRES types "continue" the
        !           197: workspace is non-empty;
        !           198: whenever INGRES types "go" the
        !           199: workspace is empty.
        !           200: 
        !           201: After a query is executed,
        !           202: INGRES typically types "continue".
        !           203: If you then type a new query, INGRES
        !           204: automatically erases the previous query,
        !           205: so you don't have to type "\\r" after every query.
        !           206: This will be further explained as we proceed.
        !           207: 
        !           208: Using the "retrieve" command we can write
        !           209: specific queries about relations.
        !           210: As an example, let's have INGRES
        !           211: print only the "pname" domain of the parts
        !           212: relation.
        !           213: Type the command:
        !           214: .nf
        !           215: 
        !           216: * range of p is parts
        !           217: * retrieve (p.pname)
        !           218: * \\g
        !           219: Executing . . .
        !           220: 
        !           221: 
        !           222: |pname               |
        !           223: |--------------------|
        !           224: |central processor   |
        !           225: |memory              |
        !           226: |disk drive          |
        !           227: |tape drive          |
        !           228: |tapes               |
        !           229: |line printer        |
        !           230: |l-p paper           |
        !           231: |terminals           |
        !           232: |paper tape reader   |
        !           233: |paper tape punch    |
        !           234: |terminal paper      |
        !           235: |byte-soap           |
        !           236: |card reader         |
        !           237: |card punch          |
        !           238: |--------------------|
        !           239: 
        !           240: continue
        !           241: *
        !           242: 
        !           243: .fi
        !           244: The output is just the pname domain from the
        !           245: parts relation.
        !           246: What we did required two steps.
        !           247: First we declared what is called a "tuple variable"
        !           248: and assigned it to range over the parts relation.
        !           249: 
        !           250: range of p is parts
        !           251: 
        !           252: What this means in English is that the letter "p"
        !           253: represents the parts relation.
        !           254: It may be thought of as a marker
        !           255: which moves down the "parts" relation to
        !           256: keep our place.
        !           257: INGRES remembers the association so that
        !           258: once p is declared to range over parts,
        !           259: we don't have to repeat the range declaration.
        !           260: This is useful when we are working
        !           261: with more than one relation, as will be seen later on.
        !           262: 
        !           263: Next we used the retrieve command.
        !           264: Its form is
        !           265: 
        !           266: retrieve ( list here what you want retrieved )
        !           267: 
        !           268: "p" by itself refers to the parts relation.
        !           269: "p.pname" refers to the pname domain of the
        !           270: parts relation, so saying:
        !           271: 
        !           272: retrieve (p.pname)
        !           273: 
        !           274: means retrieve the
        !           275: pname domain of the parts relation.
        !           276: 
        !           277: Try the
        !           278: query to retrieve pname and color:
        !           279: 
        !           280: .nf
        !           281: * retrieve p.pname, p.color
        !           282: * \\g
        !           283: Executing . . .
        !           284: 
        !           285: 2500: syntax error on line 1
        !           286: last symbol read was: .
        !           287: 
        !           288: continue
        !           289: *
        !           290: 
        !           291: .fi
        !           292: Unfortunately we've made an error.
        !           293: INGRES tells us that it found a syntax
        !           294: error on the first line of the query.
        !           295: "Syntax error" means that we have
        !           296: typed something which INGRES cannot recognize.
        !           297: The error occured on line 1.
        !           298: INGRES makes a sometimes helpful and
        !           299: sometimes feeble attempt at
        !           300: diagnosing the problem.
        !           301: Whenever possible,
        !           302: INGRES tells us the last thing it read before it got confused.
        !           303: 
        !           304: In this case, the error is that the list of things to be retrieved
        !           305: (called the target list) must be enclosed in parenthesis.
        !           306: The correct query is:
        !           307: .nf
        !           308: 
        !           309: * retrieve (p.pname, p.color)
        !           310: * \\g
        !           311: Executing . . .
        !           312: 
        !           313: 
        !           314: |pname               |color   |
        !           315: |-----------------------------|
        !           316: |central processor   |pink    |
        !           317: |memory              |gray    |
        !           318: |disk drive          |black   |
        !           319: |tape drive          |black   |
        !           320: |tapes               |gray    |
        !           321: |line printer        |yellow  |
        !           322: |l-p paper           |white   |
        !           323: |terminals           |blue    |
        !           324: |paper tape reader   |black   |
        !           325: |paper tape punch    |black   |
        !           326: |terminal paper      |white   |
        !           327: |byte-soap           |clear   |
        !           328: |card reader         |gray    |
        !           329: |card punch          |gray    |
        !           330: |-----------------------------|
        !           331: 
        !           332: continue
        !           333: *
        !           334: 
        !           335: .fi
        !           336: You can restrict which tuples are printed by adding
        !           337: a "qualification" to the query.
        !           338: For example to get the name and color of
        !           339: only those parts which are gray, type:
        !           340: .nf
        !           341: 
        !           342: * retrieve (p.pname, p.color)
        !           343: * where p.color = "gray"
        !           344: * \\g
        !           345: Executing . . .
        !           346: 
        !           347: 
        !           348: |pname               |color   |
        !           349: |-----------------------------|
        !           350: |memory              |gray    |
        !           351: |tapes               |gray    |
        !           352: |card reader         |gray    |
        !           353: |card punch          |gray    |
        !           354: |-----------------------------|
        !           355: 
        !           356: continue
        !           357: *
        !           358: 
        !           359: .fi
        !           360: Notice that INGRES prints only those parts where p.color
        !           361: is gray.
        !           362: Notice also that gray must be in quotes ("gray").
        !           363: This is necessary.
        !           364: The only way INGRES will recognize character strings
        !           365: (e.g. words) is to enclose them in quotes.
        !           366: 
        !           367: What if we wanted part names for gray or pink parts?
        !           368: We only need to append to the previous
        !           369: query the phrase:
        !           370: 
        !           371: or p.color = "pink"
        !           372: 
        !           373: Remember, however, that if the next
        !           374: line typed begins a new query, INGRES
        !           375: will automatically reset the workspace.
        !           376: The workspace will be
        !           377: .ul 
        !           378: saved
        !           379: only if the next line begins with a command
        !           380: such as "\\p" or "\\g".
        !           381: (There are others which we will come to later.)  
        !           382: If such a command is typed,
        !           383: the previous query is saved and anything
        !           384: further will be appended to that query.
        !           385: 
        !           386: Thus, by typing:
        !           387: .nf
        !           388: 
        !           389: * \\p
        !           390: retrieve (p.pname, p.color)
        !           391: where p.color = "gray"
        !           392: *
        !           393: 
        !           394: .fi
        !           395: you can see the previous query.
        !           396: Now type:
        !           397: .nf
        !           398: 
        !           399: * or p.color = "pink"
        !           400: *
        !           401: 
        !           402: .fi
        !           403: INGRES appends that last line to
        !           404: the end of the query.
        !           405: You can verify this yourself by printing the workspace:
        !           406: .nf
        !           407: 
        !           408: * \\p
        !           409: retrieve (p.pname, p.color)
        !           410: where p.color = "gray"
        !           411: or p.color = "pink"
        !           412: *
        !           413: 
        !           414: .fi
        !           415: Now run the query:
        !           416: .nf
        !           417: 
        !           418: * \\g
        !           419: Executing . . .
        !           420: 
        !           421: 
        !           422: |pname               |color   |
        !           423: |-----------------------------|
        !           424: |central processor   |pink    |
        !           425: |memory              |gray    |
        !           426: |tapes               |gray    |
        !           427: |card reader         |gray    |
        !           428: |card punch          |gray    |
        !           429: |-----------------------------|
        !           430: 
        !           431: continue
        !           432: *
        !           433: 
        !           434: .fi
        !           435: The rules about when the workspace is reset may be
        !           436: very confusing at first.
        !           437: In general, INGRES will do exactly what
        !           438: you want without you having to think about it.
        !           439: 
        !           440: We have seen qualifications which used "or" and "=".
        !           441: In general one can use:
        !           442: 
        !           443: .nf
        !           444:        and
        !           445:        or
        !           446:        not
        !           447:        =       (equal)
        !           448:        !=      (not equal)
        !           449:        >       (greater than)
        !           450:        >=      (greater than or equal)
        !           451:        <       (less than)
        !           452:        <=      (less than or equal)
        !           453: .fi
        !           454: 
        !           455: Evaluation occurs in the
        !           456: order the qualification was typed (ie. left to right).
        !           457: Parenthesis can be used to group things
        !           458: in any arbitrary order.
        !           459: 
        !           460: INGRES can do computations on the data
        !           461: stored in a relation.
        !           462: For example, the parts relation has
        !           463: quantity on hand and weight for each item.
        !           464: We might like to know the total weight
        !           465: for each group of parts (i.e. weight multiplied by qoh).
        !           466: 
        !           467: To get the name, part number and total weight
        !           468: for each part type the query:
        !           469: .nf
        !           470: 
        !           471: * retrieve (p.pname, p.pnum, p.qoh * p.weight)
        !           472: * \\g
        !           473: Executing . . .
        !           474: 
        !           475: 2500: syntax error on line 1
        !           476: last symbol read was: *
        !           477: 
        !           478: continue
        !           479: *
        !           480: 
        !           481: .fi
        !           482: Another error.
        !           483: The problem is that when a computation
        !           484: is done, INGRES does not know how to
        !           485: title the domain on the printout.
        !           486: For a simple domain, INGRES uses the
        !           487: domain name as a title.
        !           488: For anything else, you must
        !           489: create a new domain title by specifying:
        !           490: .nf
        !           491: 
        !           492:        tot = p.qoh * p.weight
        !           493: 
        !           494: .fi
        !           495: More generally the form is:
        !           496: .nf
        !           497: 
        !           498:        title = expression
        !           499: 
        !           500: .fi
        !           501: For example:
        !           502: .nf
        !           503: 
        !           504:        name = p.pname
        !           505:        computation = p.weight / 2000 * (p.qoh + 2)
        !           506: 
        !           507: .fi
        !           508: Let's fix the error by retyping
        !           509: the query.
        !           510: As long as the first line after a query
        !           511: does not begin with a "\\p" or "\\g" then
        !           512: INGRES will automatically reset
        !           513: the workspace, erasing the previous
        !           514: query for us.
        !           515: .nf
        !           516: 
        !           517: * retrieve (p.pname, p.pnum, tot=p.qoh * p.weight)
        !           518: * \\g
        !           519: Executing . . .
        !           520: 
        !           521: 
        !           522: |pname               |pnum  |tot   |
        !           523: |----------------------------------|
        !           524: |central processor   |     1|    10|
        !           525: |memory              |     2|   640|
        !           526: |disk drive          |     3|  1370|
        !           527: |tape drive          |     4|  1800|
        !           528: |tapes               |     5|   250|
        !           529: |line printer        |     6|  1734|
        !           530: |l-p paper           |     7|  1425|
        !           531: |terminals           |     8|   285|
        !           532: |paper tape reader   |    13|     0|
        !           533: |paper tape punch    |    14|     0|
        !           534: |terminal paper      |     9|   700|
        !           535: |byte-soap           |    10|     0|
        !           536: |card reader         |    11|     0|
        !           537: |card punch          |    12|     0|
        !           538: |----------------------------------|
        !           539: 
        !           540: continue
        !           541: *
        !           542: 
        !           543: .fi
        !           544: In addition to multiplication, INGRES supports:
        !           545: .in +8
        !           546: 
        !           547: +   addition
        !           548: .br
        !           549: -   subtraction (and unary negation)
        !           550: .br
        !           551: /   division
        !           552: .br
        !           553: *   multiplication
        !           554: .br
        !           555: **  exponentiation (e.g. 3**10)
        !           556: .br
        !           557: abs  absolute value (e.g. abs(p.qoh - 50) )
        !           558: .br
        !           559: mod  modulo division
        !           560: .br
        !           561: 
        !           562: .in -8
        !           563: and many others.
        !           564: Please refer to the INGRES reference manual for
        !           565: a brief but complete description of
        !           566: what is supported.
        !           567: 
        !           568: If all we wanted were part numbers 2 or 10,
        !           569: then we could add the qualification:
        !           570: 
        !           571:        where p.pnum = 2 or p.pnum = 10
        !           572: 
        !           573: CAUTION: if we just started typing "where p.pnum .... "
        !           574: INGRES would understand this as the
        !           575: beginning of a new query
        !           576: and would reset the workspace.
        !           577: To avoid this you could type "\\p" and force INGRES
        !           578: to print the workspace,
        !           579: or you can type "\\a" (append).
        !           580: The append command guarantees that whatever
        !           581: else is typed will be appended to
        !           582: what is already in the workspace.
        !           583: This command is only needed immediately after
        !           584: a query is executed.
        !           585: Any other time data will be appended automatically.
        !           586: Try the following:
        !           587: .nf
        !           588: 
        !           589: * \\a
        !           590: * where p.pnum = 2 or p.pnum = 10
        !           591: * \\g
        !           592: Executing . . .
        !           593: 
        !           594: 
        !           595: |pname               |pnum  |tot   |
        !           596: |----------------------------------|
        !           597: |memory              |     2|   640|
        !           598: |byte-soap           |    10|     0|
        !           599: |----------------------------------|
        !           600: 
        !           601: continue
        !           602: *
        !           603: 
        !           604: .fi
        !           605: To include all part numbers greater than 2 and less than or
        !           606: equal to 10:
        !           607: .nf
        !           608: 
        !           609: * retrieve (p.pname, p.pnum, tot=p.qoh * p.weight)
        !           610: * where p.pnum > 2 and p.pnum <= 10
        !           611: * \\g
        !           612: Executing . . .
        !           613: 
        !           614: 
        !           615: |pname               |pnum  |tot   |
        !           616: |----------------------------------|
        !           617: |disk drive          |     3|  1370|
        !           618: |tape drive          |     4|  1800|
        !           619: |tapes               |     5|   250|
        !           620: |line printer        |     6|  1734|
        !           621: |l-p paper           |     7|  1425|
        !           622: |terminals           |     8|   285|
        !           623: |terminal paper      |     9|   700|
        !           624: |byte-soap           |    10|     0|
        !           625: |----------------------------------|
        !           626: 
        !           627: continue
        !           628: *
        !           629: 
        !           630: .fi
        !           631: Now, suppose we want to change
        !           632: the previous query to give results
        !           633: for part numbers between 5 and 10
        !           634: instead of 2 and 10.
        !           635: You are probably annoyed at having to retype
        !           636: the entire query in order to
        !           637: change one character.
        !           638: Consequently,
        !           639: INGRES lets you use the UNIX text editor
        !           640: to make corrections and/or additions
        !           641: to your workspace.
        !           642: At any time you can type "\\e" and the
        !           643: INGRES monitor will write your workspace to a file
        !           644: and call the UNIX "ed" program.
        !           645: For example:
        !           646: .nf
        !           647: 
        !           648: * \\e
        !           649: >>ed
        !           650: 83
        !           651: 
        !           652: .fi
        !           653: The ">>ed" message tells you that you are now using the
        !           654: editor.
        !           655: The number 83 is the number of characters in your workspace.
        !           656: 
        !           657: We can now edit the query by changing the
        !           658: 2 to a 5.
        !           659: Included in the UNIX documentation is a tutorial on using the
        !           660: text editor.
        !           661: Rather than duplicating that tutorial, we will just
        !           662: use a few of the editor commands to illustrate how
        !           663: to do editing:
        !           664: .nf
        !           665: 
        !           666: 1p
        !           667: retrieve (p.pname,p.pnum,tot = p.qoh * p.weight)
        !           668: 2p
        !           669: where p.pnum > 2 and p.pnum <= 10
        !           670: s/2/5/p
        !           671: where p.pnum > 5 and p.pnum <= 10
        !           672: w
        !           673: 83
        !           674: q
        !           675: <<monitor
        !           676: *
        !           677: 
        !           678: .fi
        !           679: Very briefly, this is what happens.
        !           680: "1p" and "2p" printed lines 1 and 2.
        !           681: "s/2/5/p" substitutes a 5 for a 2 on the current
        !           682: line (line 2), and then prints that line.
        !           683: "w" writes the query back to the INGRES workspace.
        !           684: 
        !           685: Inside the editor you can use any "ed" command
        !           686: except "e" (since e changes the file name).
        !           687: When you quit the editor (q command),
        !           688: the INGRES monitor will print "<<monitor" to
        !           689: remind you that you are back in INGRES.
        !           690: Notice that you MUST precede the "q" command with
        !           691: a "w" command
        !           692: to pass the
        !           693: corrected workspace back to INGRES.
        !           694: 
        !           695: To verify that the query is correct and to run it, type:
        !           696: .nf
        !           697: * \\p\\g
        !           698: retrieve (p.pname,p.pnum,tot = p.qoh * p.weight)
        !           699: where p.pnum > 5 and p.pnum <= 10
        !           700: Executing . . .
        !           701: 
        !           702: 
        !           703: |pname               |pnum  |tot   |
        !           704: |----------------------------------|
        !           705: |line printer        |     6|  1734|
        !           706: |l-p paper           |     7|  1425|
        !           707: |terminals           |     8|   285|
        !           708: |terminal paper      |     9|   700|
        !           709: |byte-soap           |    10|     0|
        !           710: |----------------------------------|
        !           711: 
        !           712: continue
        !           713: *
        !           714: 
        !           715: .fi
        !           716: 
        !           717: Having exhausted the
        !           718: interesting queries concerning the parts relation, lets now
        !           719: look at a new relation called "supply".
        !           720: Type:
        !           721: .nf
        !           722: 
        !           723: * print supply
        !           724: * \\g
        !           725: Executing . . .
        !           726: 
        !           727: 
        !           728: supply relation
        !           729: 
        !           730: |snum  |pnum  |jnum  |shipdate|quan  |
        !           731: |------------------------------------|
        !           732: |   475|     1|  1001|73-12-31|     1|
        !           733: |   475|     2|  1002|74-05-31|    32|
        !           734: |   475|     3|  1001|73-12-31|     2|
        !           735: |   475|     4|  1002|74-05-31|     1|
        !           736: |   122|     7|  1003|75-02-01|   144|
        !           737: |   122|     7|  1004|75-02-01|    48|
        !           738: |   122|     9|  1004|75-02-01|   144|
        !           739: |   440|     6|  1001|74-10-10|     2|
        !           740: |   241|     4|  1001|73-12-31|     1|
        !           741: |    62|     3|  1002|74-06-18|     3|
        !           742: |   475|     2|  1001|73-12-31|    32|
        !           743: |   475|     1|  1002|74-07-01|     1|
        !           744: |     5|     4|  1003|74-11-15|     3|
        !           745: |     5|     4|  1004|75-01-22|     6|
        !           746: |    20|     5|  1001|75-01-10|    20|
        !           747: |    20|     5|  1002|75-01-10|    75|
        !           748: |   241|     1|  1005|75-06-01|     1|
        !           749: |   241|     2|  1005|75-06-01|    32|
        !           750: |   241|     3|  1005|75-06-01|     1|
        !           751: |    67|     4|  1005|75-07-01|     1|
        !           752: |   999|    10|  1006|76-01-01|   144|
        !           753: |   241|     8|  1005|75-07-01|     1|
        !           754: |   241|     9|  1005|75-07-01|   144|
        !           755: |------------------------------------|
        !           756: 
        !           757: continue
        !           758: *
        !           759: 
        !           760: .fi
        !           761: The supply relation contains snum (the supplier number),
        !           762: pnum (the part number which is supplied by that supplier),
        !           763: jnum (the job number),
        !           764: shipdate (the date it was shipped),
        !           765: and quan (the quantity shipped).
        !           766: 
        !           767: To find out what parts are supplied by supplier number 122 type:
        !           768: .nf
        !           769: 
        !           770: * retrieve (s.pnum) where s.snum = 122
        !           771: * \\g
        !           772: Executing . . .
        !           773: 
        !           774: 2109: line 1, Variable 's' not declared in RANGE statement
        !           775: 
        !           776: continue
        !           777: *
        !           778: 
        !           779: .fi
        !           780: We have referenced the tuple variable "s"
        !           781: (i.e. s.pnum) without telling INGRES what
        !           782: "s" represents.
        !           783: We are missing a range declaration.
        !           784: Retype the query as follows:
        !           785: .nf
        !           786: 
        !           787: * range of s is supply
        !           788: * retrieve (s.pnum) where s.snum = 122
        !           789: * \\g
        !           790: Executing . . .
        !           791: 
        !           792: 
        !           793: |pnum  |
        !           794: |------|
        !           795: |     7|
        !           796: |     7|
        !           797: |     9|
        !           798: |------|
        !           799: 
        !           800: continue
        !           801: *
        !           802: 
        !           803: .fi
        !           804: Supplier number 122 supplies part numbers 7, 7 and 9.
        !           805: Note that 7 is listed twice.
        !           806: When retrieving tuples onto a terminal it is
        !           807: more efficient for INGRES NOT to check for duplicate
        !           808: tuples.
        !           809: INGRES can be forced to remove duplicate tuples.
        !           810: We will come to that later.
        !           811: 
        !           812: We now know that supplier 122 supplies
        !           813: part numbers 7 and 9.
        !           814: If you haven't run this query a few hundred
        !           815: times you probably don't know what part
        !           816: names correspond to part numbers 7 and 9.
        !           817: We could find out simply by
        !           818: running the query:
        !           819: .nf
        !           820: 
        !           821: * retrieve (p.pname) where p.pnum = 7 or
        !           822: * p.pnum = 9
        !           823: * \\g
        !           824: Executing . . .
        !           825: 
        !           826: 
        !           827: |pname               |
        !           828: |--------------------|
        !           829: |l-p paper           |
        !           830: |terminal paper      |
        !           831: |--------------------|
        !           832: 
        !           833: continue
        !           834: *
        !           835: 
        !           836: .fi
        !           837: After two queries we know by part name
        !           838: what parts are supplied by supplier number 122.
        !           839: We could do the same thing in one query by asking:
        !           840: .nf
        !           841: 
        !           842: * retrieve (p.pname) where p.pnum = s.pnum
        !           843: * and s.snum = 122
        !           844: * \\g
        !           845: Executing . . .
        !           846: 
        !           847: 
        !           848: |pname               |
        !           849: |--------------------|
        !           850: |l-p paper           |
        !           851: |l-p paper           |
        !           852: |terminal paper      |
        !           853: |--------------------|
        !           854: 
        !           855: continue
        !           856: *
        !           857: 
        !           858: .fi
        !           859: Again note that "l-p paper" is duplicated.
        !           860: Look closely at this query.
        !           861: Note that the domain pnum exists in both
        !           862: the parts and supply relations.
        !           863: By saying p.pnum = s.pnum, we are logically
        !           864: joining the two relations.
        !           865: 
        !           866: Suppose we wished to find all suppliers who supply
        !           867: the central processor.
        !           868: We know that we will want to retrieve s.snum.
        !           869: We want only those s.snum's where the corresponding s.pnum
        !           870: is equal to the part number for the central
        !           871: processor.
        !           872: 
        !           873: If we find the p.pname which is equal to "central processor"
        !           874: then that will tell us the correct p.pnum.
        !           875: Finally we want s.pnum = p.pnum.
        !           876: The query is:
        !           877: .nf
        !           878: 
        !           879: * retrieve (s.snum) where
        !           880: * s.pnum = p.pnum and p.pname = "central processor"
        !           881: * \\g
        !           882: Executing . . .
        !           883: 
        !           884: 
        !           885: |snum  |
        !           886: |------|
        !           887: |   475|
        !           888: |   475|
        !           889: |   241|
        !           890: |------|
        !           891: 
        !           892: continue
        !           893: *
        !           894: 
        !           895: .fi
        !           896: Let's abandon the parts and supply relations and try another.
        !           897: First, we can see what other relations are in the
        !           898: database by typing:
        !           899: .nf
        !           900: 
        !           901: * help \\g
        !           902: * Executing . . .
        !           903: 
        !           904: 
        !           905:  relation name     relation owner
        !           906: 
        !           907:  relation          ingres
        !           908:  attribute         ingres
        !           909:  indexes           ingres
        !           910:  integrity         ingres
        !           911:  constraint        ingres
        !           912:  item              ingres
        !           913:  sale              ingres
        !           914:  employee          ingres
        !           915:  dept              ingres
        !           916:  supplier          ingres
        !           917:  store             ingres
        !           918:  parts             ingres
        !           919:  supply            ingres
        !           920: 
        !           921: 
        !           922: continue
        !           923: * 
        !           924: 
        !           925: .fi
        !           926: Let's look at the "employee" relation.
        !           927: Since we know nothing about the relation we
        !           928: can also use the "help" command to learn about it.
        !           929: Type:
        !           930: .nf
        !           931: 
        !           932: * help employee
        !           933: * \\g
        !           934: Executing . . .
        !           935: 
        !           936: 
        !           937: Relation:              employee
        !           938: Owner:                 ingres
        !           939: Tuple width:           30
        !           940: Saved until:           Fri Mar 25 11:01:30 1977
        !           941: Number of tuples:      24
        !           942: Storage structure:     paged heap
        !           943: relation type:         user relation
        !           944: 
        !           945:  attribute name    type  length  keyno.
        !           946: 
        !           947:  number            i       2
        !           948:  name              c      20
        !           949:  salary            i       2
        !           950:  manager           i       2
        !           951:  birthdate         i       2
        !           952:  startdate         i       2
        !           953: 
        !           954: 
        !           955: continue
        !           956: * 
        !           957: 
        !           958: .fi
        !           959: The help command lists overall information
        !           960: about the employee relation
        !           961: together with each attribute, its
        !           962: type and its length.
        !           963: 
        !           964: INGRES supports three data types: integer numbers,
        !           965: floating point numbers, and characters strings.
        !           966: Character domains can be from 1 to 255 characters
        !           967: in length.
        !           968: Integer domains can be 1, 2, or 4 bytes in length.
        !           969: This means that integers can obtain a maximum
        !           970: value of 127; 32,767; and 2,147,483,647 respectively.
        !           971: Floating point numbers can be either 4 or 8 bytes.
        !           972: Both hold a maximum value of about 10**38;
        !           973: with 7 or 17 digit accuracy respectively.
        !           974: 
        !           975: To look at all domains we could use the print command
        !           976: or we could use the retrieve command and
        !           977: list each domain in the target list.
        !           978: INGRES provides a shorthand way of doing just that.
        !           979: Try the following:
        !           980: .nf
        !           981: 
        !           982: * range of e is employee
        !           983: * retrieve (e.all)
        !           984: * \\g
        !           985: Executing . . .
        !           986: 
        !           987: 
        !           988: |number|name                |salary|manage|birthd|startd|
        !           989: |-------------------------------------------------------|
        !           990: |   157|Jones, Tim          | 12000|   199|  1940|  1960|
        !           991: |  1110|Smith, Paul         |  6000|    33|  1952|  1973|
        !           992: |    35|Evans, Michael      |  5000|    32|  1952|  1974|
        !           993: |   129|Thomas, Tom         | 10000|   199|  1941|  1962|
        !           994: |    13|Edwards, Peter      |  9000|   199|  1928|  1958|
        !           995: |   215|Collins, Joanne     |  7000|    10|  1950|  1971|
        !           996: |    55|James, Mary         | 12000|   199|  1920|  1969|
        !           997: |    26|Thompson, Bob       | 13000|   199|  1930|  1970|
        !           998: |    98|Williams, Judy      |  9000|   199|  1935|  1969|
        !           999: |    32|Smythe, Carol       |  9050|   199|  1929|  1967|
        !          1000: |    33|Hayes, Evelyn       | 10100|   199|  1931|  1963|
        !          1001: |   199|Bullock, J.D.       | 27000|     0|  1920|  1920|
        !          1002: |  4901|Bailey, Chas M.     |  8377|    32|  1956|  1975|
        !          1003: |   843|Schmidt, Herman     | 11204|    26|  1936|  1956|
        !          1004: |  2398|Wallace, Maggie J.  |  7880|    26|  1940|  1959|
        !          1005: |  1639|Choy, Wanda         | 11160|    55|  1947|  1970|
        !          1006: |  5119|Ferro, Tony         | 13621|    55|  1939|  1963|
        !          1007: |    37|Raveen, Lemont      | 11985|    26|  1950|  1974|
        !          1008: |  5219|Williams, Bruce     | 13374|    33|  1944|  1959|
        !          1009: |  1523|Zugnoni, Arthur A.  | 19868|   129|  1928|  1949|
        !          1010: |   430|Brunet, Paul C.     | 17674|   129|  1938|  1959|
        !          1011: |   994|Iwano, Masahiro     | 15641|   129|  1944|  1970|
        !          1012: |  1330|Onstad, Richard     |  8779|    13|  1952|  1971|
        !          1013: |    10|Ross, Stanley       | 15908|   199|  1927|  1945|
        !          1014: |    11|Ross, Stuart        | 12067|     0|  1931|  1932|
        !          1015: |-------------------------------------------------------|
        !          1016: 
        !          1017: continue
        !          1018: *
        !          1019: 
        !          1020: .fi
        !          1021: "All" is a keyword which is expanded by
        !          1022: INGRES to become all domains.
        !          1023: The domains are not guaranteed to be in any particular order.
        !          1024: The previous query is equivalent to:
        !          1025: .nf
        !          1026: 
        !          1027:        range of e is employee
        !          1028:        retrieve (e.number, e.name, e.salary, e.manager
        !          1029:                e.birthdate, e.startdate)
        !          1030: 
        !          1031: .fi
        !          1032: Let's retrieve the salary of Stan Ross.
        !          1033: At this point we will need to be able
        !          1034: to type both upper and lower case letters.
        !          1035: If you are on an upper case only terminal,
        !          1036: type a single "\\" before a letter
        !          1037: you wish to capitalize.
        !          1038: Thus on an upper case only terminal
        !          1039: type "\\ROSS, \\STAN".
        !          1040: If you are on an upper and lower case
        !          1041: terminal, use the shift key to
        !          1042: capitalize a letter.
        !          1043: 
        !          1044: Run the query:
        !          1045: .nf
        !          1046: 
        !          1047: * retrieve (e.name,e.salary)
        !          1048: * where e.name = "Ross, Stan"
        !          1049: * \\g
        !          1050: Executing . . .
        !          1051: 
        !          1052: 
        !          1053: |name                |salary|
        !          1054: |---------------------------|
        !          1055: |---------------------------|
        !          1056: 
        !          1057: continue
        !          1058: *
        !          1059: 
        !          1060: .fi
        !          1061: The result is empty.
        !          1062: There is no e.name which satisfies the qualification.
        !          1063: That's strange because we know there is a Stan Ross.
        !          1064: However, INGRES does not know, for example, that "Stanley"
        !          1065: and "Stan" are semantically the same.
        !          1066: 
        !          1067: To get the correct answer in this
        !          1068: situation you may use the special
        !          1069: "pattern matching" characters
        !          1070: provided by INGRES.
        !          1071: 
        !          1072: One such character is "*".
        !          1073: It matches any string of zero or more characters.
        !          1074: Try the query:
        !          1075: .nf
        !          1076: 
        !          1077: * retrieve (e.name,e.salary)
        !          1078: * where e.name = "Ross, S*"
        !          1079: * \\g
        !          1080: Executing . . .
        !          1081: 
        !          1082: 
        !          1083: |name                |salary|
        !          1084: |---------------------------|
        !          1085: |Ross, Stanley       | 15908|
        !          1086: |Ross, Stuart        | 12067|
        !          1087: |---------------------------|
        !          1088: 
        !          1089: continue
        !          1090: *
        !          1091: 
        !          1092: .fi
        !          1093: In the first case "*" matched the string "tanley" and
        !          1094: in the second case it matched "tuart".
        !          1095: 
        !          1096: Here is another example.
        !          1097: Find the salaries of all people
        !          1098: whose first name is "Paul":
        !          1099: .nf
        !          1100: 
        !          1101: * retrieve (e.name,e.salary)
        !          1102: * where e.name = "*,Paul*"
        !          1103: * \\g
        !          1104: Executing . . .
        !          1105: 
        !          1106: 
        !          1107: |name                |salary|
        !          1108: |---------------------------|
        !          1109: |Smith, Paul         |  6000|
        !          1110: |Brunet, Paul C.     | 17674|
        !          1111: |---------------------------|
        !          1112: 
        !          1113: continue
        !          1114: *
        !          1115: 
        !          1116: .fi
        !          1117: Notice that if we had asked for e.name = "*,Paul"
        !          1118: we would not have gotten the second tuple.
        !          1119: Also,
        !          1120: INGRES ignores blanks
        !          1121: in any character
        !          1122: comparison whether using pattern matching
        !          1123: characters or not.
        !          1124: This means that the following would all give the same results:
        !          1125: .nf
        !          1126: 
        !          1127:        e.name = "Ross,Stanley"
        !          1128:        e.name = "Ross,   Stanley   "
        !          1129:        e.name = "R o s s,Stanley"
        !          1130: 
        !          1131: .fi
        !          1132: Particular characters or ranges of characters can be put
        !          1133: in square brackets ([]).
        !          1134: For example, find all people whose names start with
        !          1135: "B" through "F":
        !          1136: .nf
        !          1137: 
        !          1138: * retrieve (e.name,e.salary)
        !          1139: * where e.name = "[B-F]*"
        !          1140: * \\g
        !          1141: Executing . . .
        !          1142: 
        !          1143: 
        !          1144: |name                |salary|
        !          1145: |---------------------------|
        !          1146: |Evans, Michael      |  5000|
        !          1147: |Edwards, Peter      |  9000|
        !          1148: |Collins, Joanne     |  7000|
        !          1149: |Bullock, J.D.       | 27000|
        !          1150: |Bailey, Chas M.     |  8377|
        !          1151: |Choy, Wanda         | 11160|
        !          1152: |Ferro, Tony         | 13621|
        !          1153: |Brunet, Paul C.     | 17674|
        !          1154: |---------------------------|
        !          1155: 
        !          1156: continue
        !          1157: *
        !          1158: 
        !          1159: .fi
        !          1160: Notice that this last query could be done another way:
        !          1161: .nf
        !          1162: 
        !          1163: * retrieve (e.name,e.salary)
        !          1164: * where e.name >"B" and e.name <"G"
        !          1165: * \\g
        !          1166: Executing . . .
        !          1167: 
        !          1168: 
        !          1169: |name                |salary|
        !          1170: |---------------------------|
        !          1171: |Evans, Michael      |  5000|
        !          1172: |Edwards, Peter      |  9000|
        !          1173: |Collins, Joanne     |  7000|
        !          1174: |Bullock, J.D.       | 27000|
        !          1175: |Bailey, Chas M.     |  8377|
        !          1176: |Choy, Wanda         | 11160|
        !          1177: |Ferro, Tony         | 13621|
        !          1178: |Brunet, Paul C.     | 17674|
        !          1179: |---------------------------|
        !          1180: 
        !          1181: continue
        !          1182: *
        !          1183: 
        !          1184: .fi
        !          1185: The two results are identical;
        !          1186: however, the second way is generally
        !          1187: more efficient for INGRES to process.
        !          1188: 
        !          1189: There are three types of pattern matching constructs.
        !          1190: All three can be used in any combination for
        !          1191: character comparison.
        !          1192: They are:
        !          1193: 
        !          1194: .in +8
        !          1195: .ti -4
        !          1196: *   matches any length character string
        !          1197: .ti -4
        !          1198: ?   matches any one (non-blank) character
        !          1199: .ti -4
        !          1200: [ ] can match any character listed in the brackets.
        !          1201: If two characters are separated by a dash (-),
        !          1202: then it matches any character falling
        !          1203: between the two characters.
        !          1204: .in -8
        !          1205: 
        !          1206: The special meaning of a pattern matching
        !          1207: character can be turned off by preceeding
        !          1208: it with a "\\".
        !          1209: This means that "\\*" refers to the character
        !          1210: "*".
        !          1211: 
        !          1212: We turn now to the aggregation
        !          1213: facilities supported by INGRES.
        !          1214: This allows a user to perform computations
        !          1215: on whole domains of a relation.
        !          1216: For example, one aggregate is average (avg).
        !          1217: To compute the average salary for all employees, we enter:
        !          1218: .nf
        !          1219: 
        !          1220: * retrieve (avgsal=avg(e.salary))
        !          1221: * \\g
        !          1222: Executing . . .
        !          1223: 
        !          1224: 
        !          1225: |avgsal    |
        !          1226: |----------|
        !          1227: | 11867.520|
        !          1228: |----------|
        !          1229: 
        !          1230: continue
        !          1231: *
        !          1232: 
        !          1233: .fi
        !          1234: The particular title "avgsal"
        !          1235: is arbitrary, but necessary;
        !          1236: INGRES needs
        !          1237: .ul
        !          1238: some
        !          1239: sort of title for any
        !          1240: expression in the target list
        !          1241: (other than a simple domain).
        !          1242: 
        !          1243: We can also find the minimum and maximum salaries:
        !          1244: .nf
        !          1245: 
        !          1246: * retrieve (minsal=min(e.salary),maxsal=max(e.salary))
        !          1247: * \\g
        !          1248: Executing . . .
        !          1249: 
        !          1250: 
        !          1251: |minsal|maxsal|
        !          1252: |-------------|
        !          1253: |  5000| 27000|
        !          1254: |-------------|
        !          1255: 
        !          1256: continue
        !          1257: *
        !          1258: 
        !          1259: .fi
        !          1260: If we wanted to know the names of the employees who
        !          1261: make the minimum and maximum salaries, that query would be:
        !          1262: .nf
        !          1263: 
        !          1264: * retrieve (e.name, e.salary)
        !          1265: * where e.salary = min(e.salary) or e.salary = max(e.salary)
        !          1266: * \\g
        !          1267: Executing . . .
        !          1268: 
        !          1269: |name                |salary|
        !          1270: |---------------------------|
        !          1271: |Evans, Michael      |  5000|
        !          1272: |Bullock, J.D.       | 27000|
        !          1273: |---------------------------|
        !          1274: 
        !          1275: continue
        !          1276: * 
        !          1277: 
        !          1278: .fi
        !          1279: INGRES supports the following aggregates:
        !          1280: .nf
        !          1281: 
        !          1282:        count
        !          1283:        min
        !          1284:        max
        !          1285:        avg
        !          1286:        sum
        !          1287:        any
        !          1288: 
        !          1289: .fi
        !          1290: We now indicate the query to list each employee along
        !          1291: with the average salary for all employees:
        !          1292: .nf
        !          1293: 
        !          1294: * retrieve (e.name,peersal=avg(e.salary))
        !          1295: * \\g
        !          1296: Executing . . .
        !          1297: 
        !          1298: 
        !          1299: |name                |peersal   |
        !          1300: |-------------------------------|
        !          1301: |Jones, Tim          | 11867.520|
        !          1302: |Smith, Paul         | 11867.520|
        !          1303: |Evans, Michael      | 11867.520|
        !          1304: |Thomas, Tom         | 11867.520|
        !          1305: |Edwards, Peter      | 11867.520|
        !          1306: |Collins, Joanne     | 11867.520|
        !          1307: |James, Mary         | 11867.520|
        !          1308: |Thompson, Bob       | 11867.520|
        !          1309: |Williams, Judy      | 11867.520|
        !          1310: |Smythe, Carol       | 11867.520|
        !          1311: |Hayes, Evelyn       | 11867.520|
        !          1312: |Bullock, J.D.       | 11867.520|
        !          1313: |Bailey, Chas M.     | 11867.520|
        !          1314: |Schmidt, Herman     | 11867.520|
        !          1315: |Wallace, Maggie J.  | 11867.520|
        !          1316: |Choy, Wanda         | 11867.520|
        !          1317: |Ferro, Tony         | 11867.520|
        !          1318: |Raveen, Lemont      | 11867.520|
        !          1319: |Williams, Bruce     | 11867.520|
        !          1320: |Zugnoni, Arthur A.  | 11867.520|
        !          1321: |Brunet, Paul C.     | 11867.520|
        !          1322: |Iwano, Masahiro     | 11867.520|
        !          1323: |Onstad, Richard     | 11867.520|
        !          1324: |Ross, Stanley       | 11867.520|
        !          1325: |Ross, Stuart        | 11867.520|
        !          1326: |-------------------------------|
        !          1327: 
        !          1328: continue
        !          1329: *
        !          1330: 
        !          1331: .fi
        !          1332: An aggregate always evaluates to a single value.
        !          1333: To process the last query,
        !          1334: INGRES replicated the average salary next to
        !          1335: each e.name.
        !          1336: 
        !          1337: Aggregates can have their own qualification.
        !          1338: For example, we can retrieve a list of each
        !          1339: employee along with the average salary
        !          1340: of those employees over 50.
        !          1341: .nf
        !          1342: 
        !          1343: * retrieve (e.name,peersal=
        !          1344: * avg(e.salary where 1977-e.birthdate > 50))
        !          1345: * \\g
        !          1346: Executing . . .
        !          1347: 
        !          1348: 
        !          1349: |name                |peersal   |
        !          1350: |-------------------------------|
        !          1351: |Jones, Tim          | 19500.000|
        !          1352: |Smith, Paul         | 19500.000|
        !          1353: |Evans, Michael      | 19500.000|
        !          1354: |Thomas, Tom         | 19500.000|
        !          1355: |Edwards, Peter      | 19500.000|
        !          1356: |Collins, Joanne     | 19500.000|
        !          1357: |James, Mary         | 19500.000|
        !          1358: |Thompson, Bob       | 19500.000|
        !          1359: |Williams, Judy      | 19500.000|
        !          1360: |Smythe, Carol       | 19500.000|
        !          1361: |Hayes, Evelyn       | 19500.000|
        !          1362: |Bullock, J.D.       | 19500.000|
        !          1363: |Bailey, Chas M.     | 19500.000|
        !          1364: |Schmidt, Herman     | 19500.000|
        !          1365: |Wallace, Maggie J.  | 19500.000|
        !          1366: |Choy, Wanda         | 19500.000|
        !          1367: |Ferro, Tony         | 19500.000|
        !          1368: |Raveen, Lemont      | 19500.000|
        !          1369: |Williams, Bruce     | 19500.000|
        !          1370: |Zugnoni, Arthur A.  | 19500.000|
        !          1371: |Brunet, Paul C.     | 19500.000|
        !          1372: |Iwano, Masahiro     | 19500.000|
        !          1373: |Onstad, Richard     | 19500.000|
        !          1374: |Ross, Stanley       | 19500.000|
        !          1375: |Ross, Stuart        | 19500.000|
        !          1376: |-------------------------------|
        !          1377: 
        !          1378: continue
        !          1379: *
        !          1380: 
        !          1381: .fi
        !          1382: Contrast the previous query with this next one.
        !          1383: We will retrieve the names of those employees over fifty and
        !          1384: retrieve the average salary for all employees.
        !          1385: .nf
        !          1386: 
        !          1387: * retrieve (e.name,peersal=avg(e.salary))
        !          1388: * where 1977-e.birthdate > 50
        !          1389: * \\g
        !          1390: Executing . . .
        !          1391: 
        !          1392: 
        !          1393: |name                |peersal   |
        !          1394: |-------------------------------|
        !          1395: |James, Mary         | 11867.520|
        !          1396: |Bullock, J.D.       | 11867.520|
        !          1397: |-------------------------------|
        !          1398: 
        !          1399: continue
        !          1400: *
        !          1401: 
        !          1402: .fi
        !          1403: There is a very important distinction between these
        !          1404: last two queries.
        !          1405: An aggregate is completely self-contained.
        !          1406: It is not affected by the qualification of the
        !          1407: query as a whole.
        !          1408: 
        !          1409: In the first case, average is computed only for
        !          1410: those employees over fifty, and all employees are retrieved.
        !          1411: In the second case, however, average is computed for all
        !          1412: employees but only those employees over 50 are retrieved.
        !          1413: 
        !          1414: If we wanted a list of all employees over fifty together
        !          1415: with the average salary of employees over fifty,
        !          1416: we would combine the previous two queries into one.
        !          1417: That query would be:
        !          1418: 
        !          1419: .nf
        !          1420: * retrieve (e.name, peersal=
        !          1421: * avg(e.salary where 1977 - e.birthdate > 50))
        !          1422: * where 1977 - e.birthdate > 50
        !          1423: * \\g
        !          1424: Executing . . .
        !          1425: 
        !          1426: 
        !          1427: |name                |peersal   |
        !          1428: |-------------------------------|
        !          1429: |James, Mary         | 19500.000|
        !          1430: |Bullock, J.D.       | 19500.000|
        !          1431: |-------------------------------|
        !          1432: 
        !          1433: continue
        !          1434: * 
        !          1435: 
        !          1436: .fi
        !          1437: It is sometimes useful to have duplicate values removed before
        !          1438: an aggregation is computed.
        !          1439: For example if you wanted to know how many managers there are,
        !          1440: the following query will not give the right answer:
        !          1441: 
        !          1442: .nf
        !          1443: * retrieve (bosses = count(e.manager))
        !          1444: * \\g
        !          1445: * Executing . . .
        !          1446: 
        !          1447: 
        !          1448: |bosses       |
        !          1449: |-------------|
        !          1450: |           25|
        !          1451: |-------------|
        !          1452: 
        !          1453: continue
        !          1454: * 
        !          1455: 
        !          1456: .fi
        !          1457: Notice that that gives the count of how many tuples there are in
        !          1458: employee.
        !          1459: What we want to know is
        !          1460: how many unique e.manager's there are.
        !          1461: 
        !          1462: INGRES provides three special forms of
        !          1463: aggregation.
        !          1464: 
        !          1465: .nf
        !          1466:        countu          count unique values
        !          1467:        avgu            average unique values
        !          1468:        sumu            sum unique values
        !          1469: .fi
        !          1470: 
        !          1471: It's interesting to note that minu, maxu, and anyu are
        !          1472: not needed.
        !          1473: Their values would be the same whether duplicates were
        !          1474: removed or not.
        !          1475: 
        !          1476: The correct query to find the number of managers is:
        !          1477: 
        !          1478: .nf
        !          1479: * retrieve (bosses=countu(e.manager))
        !          1480: * \\g
        !          1481: Executing . . .
        !          1482: 
        !          1483: 
        !          1484: |bosses       |
        !          1485: |-------------|
        !          1486: |            9|
        !          1487: |-------------|
        !          1488: 
        !          1489: continue
        !          1490: *
        !          1491: 
        !          1492: .fi
        !          1493: Another aggregate facility supported by INGRES is called aggregate functions.
        !          1494: Aggregate functions group
        !          1495: data into categories and perform separate aggregations on each category.
        !          1496: 
        !          1497: For example,
        !          1498: what if you wanted to retrieve each employee,
        !          1499: and the average salary paid to employees with the same manager?
        !          1500: That query would be:
        !          1501: .nf
        !          1502: 
        !          1503: * retrieve (e.name,manageravg=avg(e.salary by e.manager))
        !          1504: * \\g
        !          1505: Executing . . .
        !          1506: 
        !          1507: 
        !          1508: |name                |manageravg|
        !          1509: |-------------------------------|
        !          1510: |Jones, Tim          | 11117.555|
        !          1511: |Thomas, Tom         | 11117.555|
        !          1512: |Edwards, Peter      | 11117.555|
        !          1513: |James, Mary         | 11117.555|
        !          1514: |Thompson, Bob       | 11117.555|
        !          1515: |Williams, Judy      | 11117.555|
        !          1516: |Smythe, Carol       | 11117.555|
        !          1517: |Hayes, Evelyn       | 11117.555|
        !          1518: |Ross, Stanley       | 11117.555|
        !          1519: |Smith, Paul         |  9687.000|
        !          1520: |Williams, Bruce     |  9687.000|
        !          1521: |Evans, Michael      |  6688.500|
        !          1522: |Bailey, Chas M.     |  6688.500|
        !          1523: |Collins, Joanne     |  7000.000|
        !          1524: |Bullock, J.D.       | 19533.500|
        !          1525: |Ross, Stuart        | 19533.500|
        !          1526: |Schmidt, Herman     | 10356.333|
        !          1527: |Wallace, Maggie J.  | 10356.333|
        !          1528: |Raveen, Lemont      | 10356.333|
        !          1529: |Choy, Wanda         | 12390.500|
        !          1530: |Ferro, Tony         | 12390.500|
        !          1531: |Zugnoni, Arthur A.  | 17727.666|
        !          1532: |Brunet, Paul C.     | 17727.666|
        !          1533: |Iwano, Masahiro     | 17727.666|
        !          1534: |Onstad, Richard     |  8779.000|
        !          1535: |-------------------------------|
        !          1536: 
        !          1537: continue
        !          1538: *
        !          1539: 
        !          1540: .fi
        !          1541: The first nine people all have the same manager and their
        !          1542: average salary is 11117.555.
        !          1543: The next two people have the same manager and their average
        !          1544: salary is 9687. etc.
        !          1545: 
        !          1546: Once again, if we wanted to see the same list
        !          1547: just for those employees over 50:
        !          1548: .nf
        !          1549: 
        !          1550: * retrieve (e.name,manageravg=avg(e.salary by e.manager))
        !          1551: * where 1977-e.birthdate > 50
        !          1552: * \\g
        !          1553: Executing . . .
        !          1554: 
        !          1555: 
        !          1556: |name                |manageravg|
        !          1557: |-------------------------------|
        !          1558: |James, Mary         | 11117.555|
        !          1559: |Bullock, J.D.       | 19533.500|
        !          1560: |-------------------------------|
        !          1561: 
        !          1562: continue
        !          1563: *
        !          1564: 
        !          1565: .fi
        !          1566: Aggregate functions (unlike simple aggregates)
        !          1567: are not completely local to themselves.
        !          1568: The domains upon which the data is grouped
        !          1569: (called the by-list) are logically
        !          1570: connected to the domains in the rest of the query.
        !          1571: 
        !          1572: In these last examples, the "e.manager" in
        !          1573: the by-list refers to the same tuple as
        !          1574: "e.name" in the target list.
        !          1575: 
        !          1576: If we wanted to compute the average salaries by
        !          1577: manager for only managers 33 and 199,
        !          1578: then the query would be:
        !          1579: .nf
        !          1580: 
        !          1581: * retrieve (e.name,manageravg=
        !          1582: * avg(e.salary by e.manager)
        !          1583: * where e.manager = 199 or e.manager = 33
        !          1584: * \\g
        !          1585: Executing . . .
        !          1586: 
        !          1587: 
        !          1588: |name                |manageravg|
        !          1589: |-------------------------------|
        !          1590: |Jones, Tim          | 11117.555|
        !          1591: |Thomas, Tom         | 11117.555|
        !          1592: |Edwards, Peter      | 11117.555|
        !          1593: |James, Mary         | 11117.555|
        !          1594: |Thompson, Bob       | 11117.555|
        !          1595: |Williams, Judy      | 11117.555|
        !          1596: |Smythe, Carol       | 11117.555|
        !          1597: |Hayes, Evelyn       | 11117.555|
        !          1598: |Ross, Stanley       | 11117.555|
        !          1599: |Smith, Paul         |  9687.000|
        !          1600: |Williams, Bruce     |  9687.000|
        !          1601: |-------------------------------|
        !          1602: 
        !          1603: continue
        !          1604: *
        !          1605: 
        !          1606: .fi
        !          1607: Suppose we wanted to find out
        !          1608: how many people work for each
        !          1609: manager, and in addition
        !          1610: wanted only to include those
        !          1611: employees who have worked at least seven years.
        !          1612: .nf
        !          1613: 
        !          1614: * retrieve (e.manager,people=count(e.name by e.manager where
        !          1615: * e.startdate < 1970))
        !          1616: * \\g
        !          1617: Executing . . .
        !          1618: 
        !          1619: 
        !          1620: |manage|people       |
        !          1621: |--------------------|
        !          1622: |   199|            8|
        !          1623: |    33|            2|
        !          1624: |    32|            0|
        !          1625: |    10|            0|
        !          1626: |     0|            2|
        !          1627: |    26|            2|
        !          1628: |    55|            1|
        !          1629: |   129|            2|
        !          1630: |    13|            0|
        !          1631: |--------------------|
        !          1632: 
        !          1633: continue
        !          1634: *
        !          1635: 
        !          1636: .fi
        !          1637: Notice that managers 32, 10, and 13 have
        !          1638: no employees who started
        !          1639: before 1970.  
        !          1640: Now suppose we want to know the average
        !          1641: salary for those employees.
        !          1642: Simply change "count" to "avg" and rerun the query.
        !          1643: .nf
        !          1644: 
        !          1645: * retrieve (e.manager,people=avg(e.salary by e.manager where
        !          1646: * e.startdate < 1970))
        !          1647: * \\g
        !          1648: Executing . . .
        !          1649: 
        !          1650: 
        !          1651: |manage|people    |
        !          1652: |-----------------|
        !          1653: |   199| 10882.250|
        !          1654: |    33| 22687.000|
        !          1655: |    32|     0.000|
        !          1656: |    10|     0.000|
        !          1657: |     0| 19533.500|
        !          1658: |    26|  9542.000|
        !          1659: |    55| 13621.000|
        !          1660: |   129| 18771.000|
        !          1661: |    13|     0.000|
        !          1662: |-----------------|
        !          1663: 
        !          1664: continue
        !          1665: *
        !          1666: 
        !          1667: .fi
        !          1668: Notice what INGRES does for
        !          1669: managers 32, 10 and 13.
        !          1670: The average salary for those manager employees
        !          1671: is actually undefined since there
        !          1672: are no employees who started before 1970.
        !          1673: INGRES always makes undefined values zero
        !          1674: in aggregates.
        !          1675: 
        !          1676: If you want to remove the zero values from
        !          1677: the output, a qualification can be added
        !          1678: to the query.  
        !          1679: The following query will find the average salaries
        !          1680: only for those which are greater than zero.
        !          1681: .nf
        !          1682: 
        !          1683: * retrieve (e.manager,people=avg(e.salary by e.manager where
        !          1684: * e.startdate < 1970))
        !          1685: * where avg(e.salary by e.manager where e.startdate < 1970) > 0
        !          1686: * \\g
        !          1687: Executing . . .
        !          1688: 
        !          1689: 
        !          1690: |manage|people    |
        !          1691: |-----------------|
        !          1692: |   199| 10882.250|
        !          1693: |    33| 22687.000|
        !          1694: |     0| 19533.500|
        !          1695: |    26|  9542.000|
        !          1696: |    55| 13621.000|
        !          1697: |   129| 18771.000|
        !          1698: |-----------------|
        !          1699: 
        !          1700: continue
        !          1701: * 
        !          1702: 
        !          1703: .fi
        !          1704: Up until now we have been retrieving results directly
        !          1705: onto the terminal.
        !          1706: You can also save results by retrieving them into a
        !          1707: new relation.
        !          1708: This is done by saying:
        !          1709: .nf
        !          1710: 
        !          1711:        retrieve into newrel ( ... )
        !          1712:        where . . .
        !          1713: 
        !          1714: .fi
        !          1715: The rules are exactly the same as for retrieves onto
        !          1716: the terminal.
        !          1717: INGRES will create the new relation with the
        !          1718: correct domains, and then put the results
        !          1719: of the query in the new relation.
        !          1720: 
        !          1721: For example, create a
        !          1722: new relation called "overpaid" which has only
        !          1723: those employees who make more than $8000:
        !          1724: .nf
        !          1725: 
        !          1726: * retrieve into overpaid (e.all)
        !          1727: * where e.salary > 8000
        !          1728: * print overpaid
        !          1729: * \\g
        !          1730: Executing . . .
        !          1731: 
        !          1732: 
        !          1733: overpaid relation
        !          1734: 
        !          1735: |number|name                |salary|manage|birthd|startd|
        !          1736: |-------------------------------------------------------|
        !          1737: |    10|Ross, Stanley       | 15908|   199|  1927|  1945|
        !          1738: |    11|Ross, Stuart        | 12067|     0|  1931|  1932|
        !          1739: |    13|Edwards, Peter      |  9000|   199|  1928|  1958|
        !          1740: |    26|Thompson, Bob       | 13000|   199|  1930|  1970|
        !          1741: |    32|Smythe, Carol       |  9050|   199|  1929|  1967|
        !          1742: |    33|Hayes, Evelyn       | 10100|   199|  1931|  1963|
        !          1743: |    37|Raveen, Lemont      | 11985|    26|  1950|  1974|
        !          1744: |    55|James, Mary         | 12000|   199|  1920|  1969|
        !          1745: |    98|Williams, Judy      |  9000|   199|  1935|  1969|
        !          1746: |   129|Thomas, Tom         | 10000|   199|  1941|  1962|
        !          1747: |   157|Jones, Tim          | 12000|   199|  1940|  1960|
        !          1748: |   199|Bullock, J.D.       | 27000|     0|  1920|  1920|
        !          1749: |   430|Brunet, Paul C.     | 17674|   129|  1938|  1959|
        !          1750: |   843|Schmidt, Herman     | 11204|    26|  1936|  1956|
        !          1751: |   994|Iwano, Masahiro     | 15641|   129|  1944|  1970|
        !          1752: |  1330|Onstad, Richard     |  8779|    13|  1952|  1971|
        !          1753: |  1523|Zugnoni, Arthur A.  | 19868|   129|  1928|  1949|
        !          1754: |  1639|Choy, Wanda         | 11160|    55|  1947|  1970|
        !          1755: |  4901|Bailey, Chas M.     |  8377|    32|  1956|  1975|
        !          1756: |  5119|Ferro, Tony         | 13621|    55|  1939|  1963|
        !          1757: |  5219|Williams, Bruce     | 13374|    33|  1944|  1959|
        !          1758: |-------------------------------------------------------|
        !          1759: 
        !          1760: continue
        !          1761: *
        !          1762: 
        !          1763: .fi
        !          1764: On a "retrieve into" nothing is printed.
        !          1765: We had to include a "print" command to see
        !          1766: the results.
        !          1767: Also, the relation name on a "retrieve into"
        !          1768: must not already exist.
        !          1769: For example, if we tried
        !          1770: the same query again:
        !          1771: .nf
        !          1772: 
        !          1773: * \\g
        !          1774: Executing . . .
        !          1775: 
        !          1776: 5102: CREATE: duplicate relation name overpaid
        !          1777: 
        !          1778: continue
        !          1779: *
        !          1780: 
        !          1781: .fi
        !          1782: There are two special features about a "retrieve into".
        !          1783: First, the result relation is automatically
        !          1784: sorted and any duplicate tuples are removed.
        !          1785: Second, the relation becomes part of
        !          1786: the data base and is owned by you.
        !          1787: If you don't want it to be saved you should
        !          1788: remember to destroy it.
        !          1789: The mechanism for destroying a relation will
        !          1790: be mentioned a bit later.
        !          1791: 
        !          1792: So far we have only retrieved data but never
        !          1793: changed it.
        !          1794: INGRES supports three update commands:
        !          1795: append, replace, and delete.
        !          1796: 
        !          1797: For example, to add "Tom Terrific" to the
        !          1798: list of overpaid employees and start him
        !          1799: off at $10000:
        !          1800: .nf
        !          1801: 
        !          1802: * append to overpaid(name = "Terrific, Tom",salary = 10000)
        !          1803: * \\g
        !          1804: Executing . . .
        !          1805: 
        !          1806: 
        !          1807: continue
        !          1808: *
        !          1809: 
        !          1810: .fi
        !          1811: Notice that we specified values for only two of the
        !          1812: six domains in "overpaid".
        !          1813: That is fine.
        !          1814: INGRES will automatically set numeric domains
        !          1815: to zero and character domains to blank, if
        !          1816: they are not specified.
        !          1817: 
        !          1818: Notice also that INGRES did not print anything after
        !          1819: the query.
        !          1820: This is true for all update commands.
        !          1821: 
        !          1822: Let's give everyone in overpaid a 10% raise.
        !          1823: To do this we want to replace o.salary by
        !          1824: 1.1 times its value.
        !          1825: Type the query:
        !          1826: .nf
        !          1827: 
        !          1828: * range of o is overpaid
        !          1829: * replace o(salary = o.salary * 1.1)
        !          1830: * \\g
        !          1831: Executing . . .
        !          1832: 
        !          1833: 
        !          1834: continue
        !          1835: *
        !          1836: 
        !          1837: .fi
        !          1838: While the append command requires that you give a
        !          1839: relation name (e.g. append to overpaid), the
        !          1840: replace and delete commands require a
        !          1841: tuple variable.
        !          1842: Note that the command is:
        !          1843: .nf
        !          1844: 
        !          1845:        replace o ( . . . )
        !          1846:                where . . .
        !          1847: 
        !          1848: and not:
        !          1849: 
        !          1850:        replace overpaid ( . . . )
        !          1851:                where . . .
        !          1852: 
        !          1853: .fi
        !          1854: Print the results of these
        !          1855: last two updates:
        !          1856: .nf
        !          1857: 
        !          1858: * print overpaid
        !          1859: * \\g
        !          1860: Executing . . .
        !          1861: 
        !          1862: 
        !          1863: overpaid relation
        !          1864: 
        !          1865: |number|name                |salary|manage|birthd|startd|
        !          1866: |-------------------------------------------------------|
        !          1867: |    10|Ross, Stanley       | 17498|   199|  1927|  1945|
        !          1868: |    11|Ross, Stuart        | 13273|     0|  1931|  1932|
        !          1869: |    13|Edwards, Peter      |  9899|   199|  1928|  1958|
        !          1870: |    26|Thompson, Bob       | 14299|   199|  1930|  1970|
        !          1871: |    32|Smythe, Carol       |  9954|   199|  1929|  1967|
        !          1872: |    33|Hayes, Evelyn       | 11109|   199|  1931|  1963|
        !          1873: |    37|Raveen, Lemont      | 13183|    26|  1950|  1974|
        !          1874: |    55|James, Mary         | 13199|   199|  1920|  1969|
        !          1875: |    98|Williams, Judy      |  9899|   199|  1935|  1969|
        !          1876: |   129|Thomas, Tom         | 10999|   199|  1941|  1962|
        !          1877: |   157|Jones, Tim          | 13199|   199|  1940|  1960|
        !          1878: |   199|Bullock, J.D.       | 29699|     0|  1920|  1920|
        !          1879: |   430|Brunet, Paul C.     | 19441|   129|  1938|  1959|
        !          1880: |   843|Schmidt, Herman     | 12324|    26|  1936|  1956|
        !          1881: |   994|Iwano, Masahiro     | 17205|   129|  1944|  1970|
        !          1882: |  1330|Onstad, Richard     |  9656|    13|  1952|  1971|
        !          1883: |  1523|Zugnoni, Arthur A.  | 21854|   129|  1928|  1949|
        !          1884: |  1639|Choy, Wanda         | 12275|    55|  1947|  1970|
        !          1885: |  4901|Bailey, Chas M.     |  9214|    32|  1956|  1975|
        !          1886: |  5119|Ferro, Tony         | 14983|    55|  1939|  1963|
        !          1887: |  5219|Williams, Bruce     | 14711|    33|  1944|  1959|
        !          1888: |     0|Terrific, Tom       | 11000|     0|     0|     0|
        !          1889: |-------------------------------------------------------|
        !          1890: 
        !          1891: continue
        !          1892: *
        !          1893: 
        !          1894: .fi
        !          1895: Let's fire whoever has the smallest salary:
        !          1896: .nf
        !          1897: 
        !          1898: * delete o where o.salary = min(o.salary) \\g
        !          1899: Executing . . .
        !          1900: 
        !          1901: 
        !          1902: continue
        !          1903: *
        !          1904: 
        !          1905: .fi
        !          1906: Notice that the delete command requires
        !          1907: a tuple variable (eg. delete o)
        !          1908: and not a relation name.
        !          1909: 
        !          1910: What if we wanted to know who makes more that
        !          1911: Tom Terrific?
        !          1912: The query to do this is very subtle.
        !          1913: First we use a new tuple variable called
        !          1914: "t" which ranges over overpaid,
        !          1915: and will be used to refer to Tom.
        !          1916: t.name must equal "Terrific, Tom".
        !          1917: Next, we use a tuple variable called "o"
        !          1918: which will scan the whole relation.
        !          1919: If we ever find an o.salary > t.salary
        !          1920: then o.name must make more than Tom.
        !          1921: 
        !          1922: The complete query is:
        !          1923: .nf
        !          1924: 
        !          1925: * range of t is overpaid
        !          1926: * retrieve (o.name, osal=o.salary, tomsal = t.salary)
        !          1927: * where o.salary > t.salary
        !          1928: * and t.name = "Terrific, Tom"
        !          1929: * \\g
        !          1930: * Executing . . .
        !          1931: 
        !          1932: 
        !          1933: |name                |osal  |tomsal|
        !          1934: |----------------------------------|
        !          1935: |Ross, Stanley       | 19247| 11000|
        !          1936: |Ross, Stuart        | 14600| 11000|
        !          1937: |Thompson, Bob       | 15728| 11000|
        !          1938: |Hayes, Evelyn       | 12219| 11000|
        !          1939: |Raveen, Lemont      | 14501| 11000|
        !          1940: |James, Mary         | 14518| 11000|
        !          1941: |Thomas, Tom         | 12098| 11000|
        !          1942: |Jones, Tim          | 14518| 11000|
        !          1943: |Bullock, J.D.       | 32668| 11000|
        !          1944: |Brunet, Paul C.     | 21385| 11000|
        !          1945: |Schmidt, Herman     | 13556| 11000|
        !          1946: |Iwano, Masahiro     | 18925| 11000|
        !          1947: |Zugnoni, Arthur A.  | 24039| 11000|
        !          1948: |Choy, Wanda         | 13502| 11000|
        !          1949: |Ferro, Tony         | 16481| 11000|
        !          1950: |Williams, Bruce     | 16182| 11000|
        !          1951: |----------------------------------|
        !          1952: 
        !          1953: continue
        !          1954: * 
        !          1955: 
        !          1956: .fi
        !          1957: If we wanted to give Tom Terrific $50 more
        !          1958: than anyone else, the query would be:
        !          1959: .nf
        !          1960: 
        !          1961: * replace o(salary = max(o.salary) + 50)
        !          1962: * where o.name = "Terrific, Tom"
        !          1963: * \\g
        !          1964: Executing . . .
        !          1965: 
        !          1966: 
        !          1967: continue
        !          1968: * 
        !          1969: 
        !          1970: .fi
        !          1971: Finally, to destroy a relation owned by
        !          1972: yourself, type the command:
        !          1973: .nf
        !          1974: 
        !          1975: * destroy overpaid
        !          1976: * \\g
        !          1977: Executing . . .
        !          1978: 
        !          1979: 
        !          1980: Continue
        !          1981: *
        !          1982: 
        !          1983: .fi
        !          1984: We are now ready to leave INGRES.
        !          1985: This is done either by typing
        !          1986: an end-of-file (control/d)
        !          1987: or more typically use the "\\q"
        !          1988: command:
        !          1989: .nf
        !          1990: 
        !          1991: * \\q
        !          1992: INGRES vers 6.1/0 logout
        !          1993: Tue Aug 30 14:55:20 1977
        !          1994: goodbye bob -- come again
        !          1995: .fi

This archive runs on limited infrastructure. Preserving old code on modern bandwidth. Automated agents are requested to crawl responsibly.