1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>SELECT(SQL) manual page</TITLE>
</HEAD>
<BODY>
<A HREF="sql.html">SQL Reference Contents</A>
<H2><A NAME="sect0" HREF="#toc0">NAME </A></H2>
select - retrieve instances from a class
<H2><A NAME="sect1" HREF="#toc1">SYNOPSIS </A></H2>
<B>select </B> [distinct]
<BR>
expression1 [<B>as </B> attr_name-1] <BR>
{, expression-1 [<B>as </B> attr_name-i]}
<BR>
[<B>into </B> <B>table </B> classname] <BR>
[<B>from </B> from-list] <BR>
[<B>where </B> where-clause]
<BR>
[<B>group by </B> attr_name1 {, attr_name-i....}] <BR>
[<B>order by </B> attr_name1
[<B>asc </B> | <B>desc </B>] [<B>using op1 </B>] {, attr_namei...}] <BR>
[<B>union {all} select </B> ...] <BR>
<P>
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION </A></H2>
<B>Select</B> will get all instances which satisfy the qualification,
<I>qual</I>, compute the value of each element in the target list, and <A HREF="either.1.html">either
(1)</A>
return them to an application program through one of two different
kinds of portals <A HREF="or.2.html">or (2)</A>
store them in a new class. <P>
If into table class
name is specified, the result of the query will be stored in a new class
with the indicated name. <P>
The <B>order by</B> clause allows a user to specify that
he wishes the instances sorted according to the corresponding operator.
This operator must be a binary one returning a boolean. Multiple sort
fields are allowed and are applied from left to right. <P>
The target list
specifies the fields to be retrieved. Each <I>attr_name</I> specifies the desired
attribute or portion of an array attribute. Thus, each <I>attr_name</I> takes
the form class_name.att_name <BR>
or, if the user only desires part of an array,
-- <BR>
--Specify a lower and upper index for each dimension <BR>
--(i.e., clip a range
of array elements) <BR>
-- <BR>
class_name.att_name[lIndex-1:uIndex-1]..[lIndex-i:uIndex-i]
<BR>
<P>
-- <BR>
--Specify an exact array element <BR>
-- <BR>
class_name.att_name[uIndex-1]..[uIndex-i]
<BR>
where each <I>lIndex</I> or <I>uIndex</I> is an integer constant. <P>
When you retrieve
an attribute which is of a complex type, the behavior of the system depends
on whether you used "nested dots" to project out attributes of the complex
type or not. See the examples below. <P>
You must have read access to a class
to read its values (see <I>grant/<A HREF="revoke.l.html">revoke</I>(l)</A>
.
<H2><A NAME="sect3" HREF="#toc3">EXAMPLES </A></H2>
-- <BR>
--Find all employees
who make more than their manager <BR>
-- <BR>
select e.name <BR>
from emp e, emp m
<BR>
where e.mgr = m.name <BR>
and e.sal > m.sal <BR>
-- <BR>
--Retrieve all fields for
those employees who make <BR>
--more than the average salary <BR>
-- <BR>
select avg(sal)
as ave <BR>
into table avgsal from emp; <BR>
-- <BR>
--Retrieve all employee names
in sorted order <BR>
-- <BR>
select distinct name <BR>
from emp <BR>
order by name
using < <BR>
-- <BR>
--Retrieve all employee names that were valid on 1/7/85 <BR>
--in sorted
order <BR>
-- <BR>
selec name <BR>
from emp['January 7 1985'] e <BR>
order by name using
< <BR>
-- <BR>
--Construct a new class, raise, containing 1.1 <BR>
--times all employee's
salaries <BR>
-- <BR>
select 1.1 * emp.salary as salary <BR>
into tables raise <BR>
from emp <BR>
<H2><A NAME="sect4" HREF="#toc4">SEE ALSO </A></H2>
<A HREF="insert.l.html">insert(l)</A>
, <A HREF="close.l.html">close(l)</A>
, create <A HREF="table.l.html">table(l)</A>
, <A HREF="fetch.l.html">fetch(l)</A>
,
<A HREF="update.l.html">update(l)</A>
.
<H2><A NAME="sect5" HREF="#toc5">BUGS </A></H2>
<B>Select into</B> does not delete duplicates. <P>
If the backend
crashes in the course of executing a <B>select into</B>, the class file will
remain on disk. It can be safely removed by the database DBA, but a subsequent
<B>select into</B> to the same name will fail with a cryptic error message about
`BlockExtend'. <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">NAME</A></LI>
<LI><A NAME="toc1" HREF="#sect1">SYNOPSIS</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">EXAMPLES</A></LI>
<LI><A NAME="toc4" HREF="#sect4">SEE ALSO</A></LI>
<LI><A NAME="toc5" HREF="#sect5">BUGS</A></LI>
</UL>
</BODY></HTML>
|
