![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to quel.nr CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [CSRG BSD Unix] / 42BSD / ingres / doc / quel|
Return to quel.nr CVS log | Up to [CSRG BSD Unix] / 42BSD / ingres / doc / quel |
1.1 root 1: .th QUEL QUEL 2/23/79
2: .ds QU \s-2QUEL\s0
3: .sh NAME
4: quel \- \fBQUE\fPry \fBL\fPanguage for \*(II
5: .sh DESCRIPTION
6: The following is a description of the general syntax
7: of
8: .nh
9: \*(QU.
10: .hy
11: Individual
12: \*(QU
13: statements and commands
14: are treated separately in the document;
15: this section describes the syntactic classes from which the constituent
16: parts of
17: \*(QU
18: statements are drawn.
19: .s1
20: 1. Comments
21: .s2
22: A comment is an arbitrary sequence of characters
23: bounded on the left by
24: ``/\*(**''
25: and on the right by
26: ``\*(**/'':
27: .s3
28: /\*(** This is a comment \*(**/
29: .s1
30: 2. Names
31: .s2
32: Names in
33: \*(QU
34: are sequences of no more than 12 alphanumeric
35: characters, starting with an alphabetic. Underscore (_) is considered
36: an alphabetic.
37: All upper-case alphabetics
38: appearing anywhere except in strings are automatically
39: and silently mapped into their
40: lower-case counterparts.
41: .s1
42: 3. Keywords
43: .s2
44: The following identifiers are reserved for use as keywords and
45: may not be used otherwise:
46: .s3
47: .ft B
48: .if n .ta 5 25 45
49: .if t .ta 0.5i 2.5i 4.5i
50: .de xx
51: \t\\$1\t\\$2\t\\$3
52: .br
53: ..
54: .xx abs all and
55: .xx any append ascii
56: .xx at atan avg
57: .xx avgu by concat
58: .xx copy cos count
59: .xx countu create define
60: .xx delete destroy exp
61: .xx float4 float8 from
62: .xx gamma help in
63: .xx index int1 int2
64: .xx int4 integrity into
65: .xx is log max
66: .xx min mod modify
67: .xx not of on
68: .xx onto or permit
69: .xx print range replace
70: .xx retrieve save sin
71: .xx sqrt sum sumu
72: .xx to unique until
73: .xx view where
74: .ft
75: .dt
76: .s1
77: 4. Constants
78: .s2
79: There are three types of constants,
80: corresponding to the three data types available in
81: \*(QU
82: for data storage.
83: .s1
84: 4.1. String constants
85: .s2
86: Strings in
87: \*(QU
88: are sequences of no more than 255 arbitrary
89: ASCII characters bounded by
90: double quotes ( " " ). Upper case alphabetics within strings
91: are accepted literally.
92: Also, in order to imbed quotes
93: within strings, it is necessary to prefix them with `\e' .
94: The same convention applies to `\e' itself.
95: .s3
96: Only printing characters are allowed within strings.
97: Non-printing characters (i.e. control characters)
98: are converted to blanks.
99: .s1
100: 4.2. Integer constants
101: .s2
102: .br
103: Integer constants in
104: \*(QU
105: range from \*-2,147,483,647
106: to +2,147,483,647.
107: Integer constants beyond that range will be converted to floating point.
108: If the integer is greater than 32,767 or less than \*-32,767
109: then it will be left as a two byte integer.
110: Otherwise it is converted to a four byte integer.
111: .s1
112: 4.3. Floating point constants
113: .s2
114: Floating constants consist of an integer part, a decimal point, and
115: a fraction part or scientific notation
116: of the following format:
117: .s3
118: {<dig>} [.<dig>] [e\*vE [+\*v\*-] {<dig>}]
119: .s3
120: Where <dig> is a digit, [] represents zero or one,
121: {} represents zero or more, and | represents alternation.
122: An exponent with a missing mantissa has a mantissa
123: of 1 inserted.
124: There may be no extra characters embedded in the string.
125: Floating constants
126: are taken to be double-precision quantities with a range of
127: approximately
128: .if n -10**38 to +10**38
129: .if t \*-10\x'-0.2v'\u\s-3\&38\s0\d to 10\u\x'-0.2v'\s-3\&38\s0\d
130: and a precision of 17 decimal digits.
131: .s1
132: 5. Attributes
133: .s2
134: An attribute is a construction of the form:
135: .s3
136: variable.domain
137: .s3
138: .it Variable
139: identifies a particular relation and can be thought of
140: as standing for the rows or tuples of that relation.
141: A variable is associated with a relation by means of a
142: .it range
143: statement.
144: .it Domain
145: is the name of one of the columns of the relation
146: over which the variable ranges.
147: Together they make up an attribute, which represents
148: values of the named domain.
149: .s1
150: 6. Arithmetic operators
151: .s2
152: Arithmetic operators take numeric type expressions as operands.
153: Unary operators group right to left; binary operators group
154: left to right. The operators (in order of descending
155: precedence) are:
156: .s3
157: .nf
158: +,\*- (unary) plus, minus
159: \*(**\*(** exponentiation
160: \*(**,/ multiplication, division
161: +,\*- (binary) addition, subtraction
162: .dt
163: .fi
164: .i0
165: .s3
166: Parentheses may be used for arbitrary grouping.
167: Arithmetic overflow and divide by zero are
168: not checked on integer operations.
169: Floating point operations are checked for
170: overflow, underflow, and divide by zero only
171: if the appropriate machine hardware exists
172: and has been enabled.
173: .s1
174: 7. Expressions (a_expr)
175: .s2
176: An expression is one of the following:
177: .s3
178: .nf
179: .if t .in +0.5i
180: .if n .in +5
181: constant
182: attribute
183: functional expression
184: aggregate or aggregate function
185: a combination of numeric expressions and arithmetic operators
186: .i0
187: .fi
188: .s3
189: For the purposes of this document,
190: an arbitrary expression will be
191: refered to by the name
192: .it a_expr.
193: .s1
194: 8. Formats
195: .s2
196: Every
197: .it a_expr
198: has a format
199: denoted by
200: a letter (\c
201: .bd c,
202: .bd i,
203: or
204: .bd f,
205: for character, integer, or floating data
206: types respectively) and a number indicating the number of bytes
207: of storage occupied.
208: Formats currently supported are listed below.
209: The ranges of numeric types are indicated in parentheses.
210: .s3
211: .lp +20 15
212: c1 \- c255 character data of length 1\-255 characters
213: .lp +20 15
214: i1 1-byte integer (\*-128 to +127)
215: .lp +20 15
216: i2 2-byte integer (\*-32768 to +32767)
217: .lp +20 15
218: i4 4-byte integer (\*-2,147,483,648 to +2,147,483,647)
219: .lp +20 15
220: .if n f4 4-byte floating (\*-10**38 to +10**38,
221: .if t f4 4-byte floating (\*-10\x'-0.2v'\u\s-3\&38\s0\d to +10\x'-0.2v'\u\s-3\&38\s0\d,
222: 7 decimal digit precision)
223: .lp +20 15
224: .if n f4 8-byte floating (\*-10**38 to +10**38,
225: .if t f4 8-byte floating (\*-10\u\x'-0.2v'\s-3\&38\s0\d to +10\u\x'-0.2v'\s-3\&38\s0\d,
226: 17 decimal digit precision)
227: .i0
228: .s3
229: One numeric format can be converted to
230: or substituted for any other numeric format.
231: .s1
232: 9. Type Conversion.
233: .s2
234: When operating on two numeric domains of
235: different types,
236: \*(II converts as necessary to make the
237: types identical.
238: .s3
239: When operating on an integer and a floating
240: point number,
241: the integer is converted to a floating point
242: number before the operation.
243: When operating on two integers of different
244: sizes, the smaller is converted to
245: the size of the larger.
246: When operating on two floating point number
247: of different size,
248: the larger is converted to the smaller.
249: .s3
250: The following table summarizes the possible combinations:
251: .s3
252: .dt
253: .if n .in +4
254: .if t .in +1i
255: .nf
256: i1 i2 i4 f4 f8
257: .s3
258: i1 \- i1 i2 i4 f4 f8
259: i2 \- i2 i2 i4 f4 f8
260: i4 \- i4 i4 i4 f4 f8
261: f4 \- f4 f4 f4 f4 f4
262: f8 \- f8 f8 f8 f4 f8
263: .dt
264: .i0
265: .fi
266: .s3
267: \*(II provides five type conversion
268: operators specifically for
269: overriding the default actions.
270: The operators are:
271: .s3
272: .nf
273: .in +4
274: .if n .ta 18
275: .if t .ta 1.4i
276: int1(a_expr) result type i1
277: int2(a_expr) result type i2
278: int4(a_expr) result type i4
279: float4(a_expr) result type f4
280: float8(a_expr) result type f8
281: .dt
282: .fi
283: .i0
284: .s3
285: The type conversion operators convert their argument
286: a_expr to the requested type.
287: .it A_expr
288: can be anything including character.
289: If a character value cannot be converted,
290: an error occures and processing is halted.
291: This can happen only if the syntax of the
292: character value is incorrect.
293: .s3
294: Overflow is not checked on conversion.
295: .s1
296: 10. Target_list
297: .s2
298: .br
299: A target list is a parenthesized, comma separated list of one
300: or more elements , each of which must be of one of the following
301: forms:
302: .s3
303: a)
304: .it result_attname
305: .bd is
306: .it a_expr
307: .s3
308: .it Result_attname
309: is the name of the attribute to be created (or an
310: already existing attribute name in the case of update statements.)
311: The equal sign (``='') may be used
312: interchangeably with
313: .bd is.
314: In the case where
315: .it a_expr
316: is anything other than
317: a single attribute, this form
318: must be used to assign a result
319: name to the expression.
320: .s3
321: b)
322: .it attribute
323: .s3
324: In the case of a
325: .it retrieve,
326: the resultant domain
327: will acquire the same name as that of the attribute being retrieved.
328: In the case of update statements
329: (\c
330: .it "append, replace\c"
331: ),
332: the relation being updated must have
333: a domain with exactly that name.
334: .s3
335: Inside the target list the keyword
336: .bd all
337: can be used to represent all domains.
338: For example:
339: .if n .in +5
340: .if t .in +0.5i
341: .s3
342: range of e is employee
343: .br
344: retrieve (e.all) where e.salary > 10000
345: .i0
346: .s3
347: will retrieve all domains of employee for
348: those tuples which satisfy the qualification.
349: .bd All
350: can be used in the target list of
351: a
352: .it retrieve
353: or an
354: .bd append.
355: The domains will be inserted in their
356: ``create'' order, that is,
357: the same order they were listed in the
358: .it create
359: statement.
360: .s1
361: 11. Comparison operators
362: .s2
363: Comparison operators take arbitrary expressions as operands.
364: .s3
365: .dt
366: .nf
367: < (less than)
368: <= (less than or equal)
369: > (greater than)
370: >= (greater than or equal)
371: = (equal to)
372: != (not equal to)
373: .fi
374: .s3
375: They are all of equal precedence.
376: When comparisons are made on
377: character attributes, all blanks are ignored.
378: .s1
379: 12. Logical operators
380: .s2
381: Logical operators take clauses as operands and
382: group left-to-right:
383: .s3
384: .nf
385: not (logical not; negation)
386: and (logical and; conjunction)
387: or (logical or; disjunction)
388: .fi
389: .i0
390: .dt
391: .s3
392: .bd Not
393: has the highest precedence of the three.
394: .bd And
395: and
396: .bd or
397: have equal precedence.
398: Parentheses may be used for arbitrary grouping.
399: .s1
400: 13. Qualification (qual)
401: .s2
402: A
403: .it qualification
404: consists of any number of clauses connected
405: by logical operators.
406: A clause is a pair of expressions connected by a comparison operator:
407: .s3
408: .dt
409: a_expr comparison_operator a_expr
410: .s3
411: Parentheses may be used for arbitrary
412: grouping.
413: A qualification may thus be:
414: .s3
415: .in +4
416: .it clause
417: .br
418: .bd not
419: .it qual
420: .br
421: .it qual
422: .bd or
423: .it qual
424: .br
425: .it qual
426: .bd and
427: .it qual
428: .br
429: (
430: .it qual
431: )
432: .i0
433: .s1
434: 14. Functional expressions
435: .s2
436: A
437: .it "functional expression"
438: consists of a function name followed
439: by a parenthesized (list of) operand(s).
440: Functional expressions can be nested to any level.
441: In the following list of functions supported (\c
442: .it n\c
443: )
444: represents an arbitrary numeric type expression.
445: The format of the result is indicated on the right.
446: .s3
447: .if n .ta 10 25
448: .if n .in 24
449: .if t .ta 1.0i 2.5i
450: .if t .in 2.5i
451: .de xx
452: .lp +20 15
453: \fB\\$1(\fI\\$2\fB)\fR \-\t\c
454: ..
455: .xx abs n
456: same as
457: .it n
458: (absolute value)
459: .xx ascii n
460: character string (converts numeric to character)
461: .xx atan n
462: f8 (arctangent)
463: .xx concat a,b
464: character (character concatenation. See 16.2)
465: .xx cos n
466: f8 (cosine)
467: .xx exp n
468: f8 (exponential of
469: .it n\c
470: )
471: .xx gamma n
472: f8 (log gamma)
473: .xx log n
474: f8 (natural logarithm)
475: .xx mod n,b
476: same as
477: .it b
478: (\c
479: .it n
480: modulo
481: .it "b. n"
482: and
483: .it b
484: must be i1, i2, or i4)
485: .xx sin n
486: f8 (sine)
487: .xx sqrt n
488: f8 (square root)
489: .dt
490: .i0
491: .s1
492: 15. Aggregate expressions
493: .s2
494: Aggregate expressions provide a way to aggregate a computed expression
495: over a set of tuples.
496: .s1
497: 15.1. Aggregation operators
498: .s2
499: The definitions
500: of the aggregates are listed below.
501: .s3
502: .de xx
503: .lp +20 15
504: \fB\\$1\fP \-\t\c
505: ..
506: .xx count
507: (i4) count of occurrences
508: .xx countu
509: (i4) count of unique occurrences
510: .xx sum
511: summation
512: .xx sumu
513: summation of unique values
514: .xx avg
515: (f8) average (sum/count)
516: .xx avgu
517: (f8) unique average (sumu/countu)
518: .xx max
519: maximum
520: .xx min
521: minimum
522: .xx any
523: (i2) value is 1 if any tuples satisfy
524: the qualification, else it is 0
525: .dt
526: .i0
527: .s1
528: 15.2. Simple aggregate
529: .s2
530: .it "\taggregation_operator"
531: (\c
532: .it a_expr
533: [
534: .bd where
535: .it qual
536: ] )
537: .dt
538: .s3
539: A simple aggregate evaluates to a single scalar value.
540: .it A_expr
541: is aggregated over the set of tuples satisfying
542: the qualification (or all tuples in the range of the expression if
543: no qualification is present).
544: Operators
545: .it sum
546: and
547: .it avg
548: require numeric type
549: .it a_expr;
550: .it "count, any, max"
551: and
552: .it min
553: permit a character type attribute as well as
554: numeric type
555: .it a_expr.
556: .s3
557: .ul 1
558: Simple aggregates are completely local.
559: That is, they are logically removed from the
560: query, processed separately, and replaced
561: by their scalar value.
562: .s1
563: 15.3. ``\c
564: .bd any\c
565: \&''
566: aggregate
567: .s2
568: It is sometimes useful to know if any tuples satisfy a
569: particular qualification.
570: One way of doing this is by using the aggregate
571: .it count
572: and checking whether the return is zero or non-zero.
573: Using
574: .it any
575: instead of
576: .it count
577: is more efficient since
578: processing is stopped, if possible, the first time a tuple
579: satisfies a qualification.
580: .s3
581: .it Any
582: returns 1 if the qualification is true
583: and 0 otherwise.
584: .s1
585: 15.4. Aggregate functions
586: .s2
587: .it "\taggregation_operator"
588: (\c
589: .it a_expr
590: .bd by
591: .it by_domain
592: .br
593: .it "\t\t{, by_domain}"
594: [
595: .bd where
596: .it qual
597: ] )
598: .s3
599: Aggregate functions are extensions of simple aggregates.
600: The
601: .it by
602: operator groups (i.e. partitions) the set of qualifying tuples by
603: .it by_domain
604: values.
605: For more than one
606: .it by_domain,
607: the values which are grouped by are the concatenation
608: of individual
609: .it by_domain
610: values.
611: .it A_expr
612: is as in simple aggregates.
613: The aggregate function evaluates to a set of aggregate results,
614: one for each partition into which the set of qualifying
615: tuples has been grouped.
616: The aggregate value used during evaluation of the query is the
617: value associated with the partition into which
618: the tuple currently being processed would fall.
619: .s3
620: Unlike simple aggregates, aggregate functions
621: are not completely local.
622: The
623: .it by_list,
624: which differentiates
625: aggregate functions from simple aggregates,
626: is global
627: to the query.
628: Domains in the
629: .it by_list
630: are automatically linked to the
631: other domains in the query which are
632: in the same relation.
633: .s3
634: Example:
635: .ft B
636: .nf
637: /\*(** retrieve the average salary for the employees
638: working for each manager \*(**/
639: range of e is employee
640: retrieve (e.manager, avesal=avg(e.salary by e.manager))
641: .fi
642: .i0
643: .ft
644: .s1
645: 15.5 Aggregates on Unique Values.
646: .s2
647: It is occasionally necessary to aggregate on
648: unique values of an expression.
649: The
650: .it avgu\c
651: ,
652: .it sumu\c
653: , and
654: .it countu
655: aggregates
656: all remove duplicate values before
657: performing the aggregation.
658: For example:
659: .s3
660: count(e.manager)
661: .s3
662: would tell you how many occurrences of
663: .it "e.manager"
664: exist.
665: But
666: .s3
667: countu(e.manager)
668: .s3
669: would tell you how many unique values of
670: .it "e.manager"
671: exist.
672: .s1
673: 16. Special character operators
674: .s2
675: There are three special features which are particular
676: to character domains.
677: .s1
678: 16.1 Pattern matching characters
679: .s2
680: There are four characters which take
681: on special meaning when used in character constants
682: (strings):
683: .s3
684: .de xx
685: .lp +10 6
686: \\$1\t\c
687: ..
688: .xx \*(**
689: matches any string of zero or more characters.
690: .xx ?
691: matches any single character.
692: .xx [..]
693: matches any of characters in the brackets.
694: .i0
695: .s3
696: These characters can be used in any combination to
697: form a variety of tests.
698: For example:
699: .s3
700: .lp +25 20
701: where e.name = "\*(**" \- matches any name.
702: .lp +25 20
703: where e.name = "E\*(**" \- matches any name starting with "E".
704: .lp +25 20
705: where e.name = "\*(**ein" \- matches all names ending with "ein"
706: .lp +25 20
707: where e.name = "\*(**[aeiou]\*(**" \- matches any name with at least one vowel.
708: .lp +25 20
709: where e.name = "Allman?" \- matches any seven character name starting with "Allman".
710: .lp +25 20
711: where e.name = "[A\-J]\*(**" \- matches any name starting with A,B,..,J.
712: .i0
713: .s3
714: The special meaning of the pattern matching characters
715: can be disabled by preceding them with a `\e'.
716: Thus ``\e\*(**'' refers to the character ``\*(**''.
717: When the special characters appear in the target
718: list they must be escaped.
719: For example:
720: .s3
721: .dt
722: title = "\e\*(**\e\*(**\e\*(** ingres \e\*(**\e\*(**\e\*(**"
723: .s3
724: is the correct way to assign the string
725: ``\*(**\*(**\*(** ingres \*(**\*(**\*(**'' to the domain ``title''.
726: .s1
727: 16.2 Concatenation
728: .s2
729: There is a concatenation operator which can
730: form one character string from two.
731: Its syntax is ``concat(field1, field2)''.
732: The size of the new character string is the
733: sum of the sizes of the original two.
734: Trailing blanks are trimmed from the first
735: field, the second field is concatenated
736: and the remainder is blank padded. The result is never
737: trimmed to 0 length, however.
738: Concat can be arbitrarily nested inside other
739: concats. For example:
740: .s3
741: .dt
742: name = concat(concat(x.lastname, ","), x.firstname)
743: .s3
744: will concatenate
745: x.lastname with a comma and
746: then concatenate x.firstname to that.
747: .s1
748: 16.3 Ascii (numeric to character translation)
749: .s2
750: The
751: .it ascii
752: function can be used to convert a
753: numeric field to its character representation.
754: This can be useful when it is desired to compare
755: a numeric value with a character value.
756: For example:
757: .nf
758: .s3
759: .dt
760: retrieve ( ... )
761: where x.chardomain = ascii(x.numdomain)
762: .fi
763: .s3
764: .it Ascii
765: can be applied to a character value.
766: The result is simply the character value unchanged.
767: The numeric conversion formats are determined by
768: the printing formats (see ingres(unix)).
769: .sh "SEE ALSO"
770: append(quel),
771: delete(quel),
772: range(quel),
773: replace(quel),
774: retrieve(quel),
775: ingres(unix)
776: .sh BUGS
777: The maximum number of variables which can
778: appear in one query is 10.
779: .s3
780: Numeric overflow, underflow, and divide by zero
781: are not detected.
782: .s3
783: When converting between numeric types, overflow is
784: not checked.
This archive runs on limited infrastructure. Preserving old code on modern bandwidth. Automated agents are requested to crawl responsibly.