![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to tutorial.nr CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [CSRG BSD Unix] / 43BSD / ingres / doc / other|
Return to tutorial.nr CVS log | Up to [CSRG BSD Unix] / 43BSD / ingres / doc / other |
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.