2
$PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.33.4.1 2005-01-22 23:05:50 momjian Exp $
5
<chapter id="sql-intro">
10
This chapter introduces the mathematical concepts behind
11
relational databases. It is not required reading, so if you bog
12
down or want to get straight to some simple examples feel free to
13
jump ahead to the next chapter and come back when you have more
14
time and patience. This stuff is supposed to be fun!
18
This material originally appeared as a part of
19
Stefan Simkovics' Master's Thesis
20
(<xref linkend="SIM98" endterm="SIM98">).
25
<acronym>SQL</acronym> has become the most popular relational query
27
The name <quote><acronym>SQL</acronym></quote> is an abbreviation for
28
<firstterm>Structured Query Language</firstterm>.
29
In 1974 Donald Chamberlin and others defined the
30
language SEQUEL (<firstterm>Structured English Query
31
Language</firstterm>) at IBM
32
Research. This language was first implemented in an IBM
33
prototype called SEQUEL-XRM in 1974-75. In 1976-77 a revised version
34
of SEQUEL called SEQUEL/2 was defined and the name was changed to
35
<acronym>SQL</acronym>
40
A new prototype called System R was developed by IBM in 1977. System R
41
implemented a large subset of SEQUEL/2 (now <acronym>SQL</acronym>)
43
changes were made to <acronym>SQL</acronym> during the project.
44
System R was installed in
45
a number of user sites, both internal IBM sites and also some selected
46
customer sites. Thanks to the success and acceptance of System R at
47
those user sites IBM started to develop commercial products that
48
implemented the <acronym>SQL</acronym> language based on the System
53
Over the next years IBM and also a number of other vendors announced
54
<acronym>SQL</acronym> products such as
55
<productname>SQL/DS</productname> (IBM),
56
<productname>DB2</productname> (IBM),
57
<productname>ORACLE</productname> (Oracle Corp.),
58
<productname>DG/SQL</productname> (Data General Corp.),
59
and <productname>SYBASE</productname> (Sybase Inc.).
63
<acronym>SQL</acronym> is also an official standard now. In 1982
65
Standards Institute (<acronym>ANSI</acronym>) chartered its
66
Database Committee X3H2 to
67
develop a proposal for a standard relational language. This proposal
68
was ratified in 1986 and consisted essentially of the IBM dialect of
69
<acronym>SQL</acronym>. In 1987 this <acronym>ANSI</acronym>
70
standard was also accepted as an international
71
standard by the International Organization for Standardization
72
(<acronym>ISO</acronym>).
73
This original standard version of <acronym>SQL</acronym> is often
75
informally, as <quote><abbrev>SQL/86</abbrev></quote>. In 1989 the original
77
and this new standard is often, again informally, referred to as
78
<quote><abbrev>SQL/89</abbrev></quote>. Also in 1989, a related standard called
79
<firstterm>Database Language Embedded <acronym>SQL</acronym></firstterm>
80
(<acronym>ESQL</acronym>) was developed.
84
The <acronym>ISO</acronym> and <acronym>ANSI</acronym> committees
85
have been working for many years on the
86
definition of a greatly expanded version of the original standard,
87
referred to informally as <firstterm><acronym>SQL2</acronym></firstterm>
88
or <firstterm><acronym>SQL/92</acronym></firstterm>. This version became a
89
ratified standard - <quote>International Standard ISO/IEC 9075:1992,
90
Database Language <acronym>SQL</acronym></quote> - in late 1992.
91
<acronym>SQL/92</acronym> is the version
92
normally meant when people refer to <quote>the <acronym>SQL</acronym>
93
standard</quote>. A detailed
94
description of <acronym>SQL/92</acronym> is given in
95
<xref linkend="DATE97" endterm="DATE97">. At the time of
96
writing this document a new standard informally referred to
97
as <firstterm><acronym>SQL3</acronym></firstterm>
98
is under development. It is planned to make <acronym>SQL</acronym>
100
language, i.e. all computable queries (e.g. recursive queries) will be
101
possible. This is a very complex task and therefore the completion of
102
the new standard can not be expected before 1999.
105
<sect1 id="rel-model">
106
<title>The Relational Data Model</title>
109
As mentioned before, <acronym>SQL</acronym> is a relational
110
language. That means it is
111
based on the <firstterm>relational data model</firstterm>
112
first published by E.F. Codd in
113
1970. We will give a formal description of the relational model
115
<xref linkend="formal-notion" endterm="formal-notion">)
116
but first we want to have a look at it from a more intuitive
121
A <firstterm>relational database</firstterm> is a database that is
123
users as a <firstterm>collection of tables</firstterm> (and
124
nothing else but tables).
125
A table consists of rows and columns where each row represents a
126
record and each column represents an attribute of the records
127
contained in the table.
128
<xref linkend="supplier-fig" endterm="supplier-fig">
129
shows an example of a database consisting of three tables:
134
SUPPLIER is a table storing the number
135
(SNO), the name (SNAME) and the city (CITY) of a supplier.
141
PART is a table storing the number (PNO) the name (PNAME) and
142
the price (PRICE) of a part.
148
SELLS stores information about which part (PNO) is sold by which
150
It serves in a sense to connect the other two tables together.
156
<title id="supplier-fig">The Suppliers and Parts Database</title>
159
SNO | SNAME | CITY SNO | PNO
160
----+---------+-------- -----+-----
161
1 | Smith | London 1 | 1
162
2 | Jones | Paris 1 | 2
163
3 | Adams | Vienna 2 | 4
164
4 | Blake | Rome 3 | 1
168
PNO | PNAME | PRICE 4 | 4
169
----+---------+---------
179
The tables PART and SUPPLIER may be regarded as
180
<firstterm>entities</firstterm> and
181
SELLS may be regarded as a <firstterm>relationship</firstterm>
183
part and a particular supplier.
187
As we will see later, <acronym>SQL</acronym> operates on tables
189
defined but before that we will study the theory of the relational
194
<sect1 id="relmodel-formal">
195
<title id="formal-notion">Relational Data Model Formalities</title>
198
The mathematical concept underlying the relational model is the
199
set-theoretic <firstterm>relation</firstterm> which is a subset of
201
product of a list of domains. This set-theoretic relation gives
202
the model its name (do not confuse it with the relationship from the
203
<firstterm>Entity-Relationship model</firstterm>).
204
Formally a domain is simply a set of
205
values. For example the set of integers is a domain. Also the set of
206
character strings of length 20 and the real numbers are examples of
213
The <firstterm>Cartesian product</firstterm> of domains $D_{1},
214
D_{2},\ldots, D_{k}$ written
215
\mbox{$D_{1} \times D_{2} \times \ldots \times D_{k}$} is the set of
216
all $k$-tuples $(v_{1},v_{2},\ldots,v_{k})$ such that \mbox{$v_{1} \in
217
D_{1}, v_{2} \in D_{2}, \ldots, v_{k} \in D_{k}$}.
220
The <firstterm>Cartesian product</firstterm> of domains
221
<parameter>D<subscript>1</subscript></parameter>,
222
<parameter>D<subscript>2</subscript></parameter>,
224
<parameter>D<subscript>k</subscript></parameter>,
226
<parameter>D<subscript>1</subscript></parameter> ×
227
<parameter>D<subscript>2</subscript></parameter> ×
229
<parameter>D<subscript>k</subscript></parameter>
230
is the set of all k-tuples
231
<parameter>v<subscript>1</subscript></parameter>,
232
<parameter>v<subscript>2</subscript></parameter>,
234
<parameter>v<subscript>k</subscript></parameter>,
236
<parameter>v<subscript>1</subscript></parameter> ∈
237
<parameter>D<subscript>1</subscript></parameter>,
238
<parameter>v<subscript>2</subscript></parameter> ∈
239
<parameter>D<subscript>2</subscript></parameter>,
241
<parameter>v<subscript>k</subscript></parameter> ∈
242
<parameter>D<subscript>k</subscript></parameter>.
246
For example, when we have
248
$k=2$, $D_{1}=\{0,1\}$ and
249
$D_{2}=\{a,b,c\}$, then $D_{1} \times D_{2}$ is
250
$\{(0,a),(0,b),(0,c),(1,a),(1,b),(1,c)\}$.
252
<parameter>k</parameter>=2,
253
<parameter>D<subscript>1</subscript></parameter>=<literal>{0,1}</literal> and
254
<parameter>D<subscript>2</subscript></parameter>=<literal>{a,b,c}</literal> then
255
<parameter>D<subscript>1</subscript></parameter> ×
256
<parameter>D<subscript>2</subscript></parameter> is
257
<literal>{(0,a),(0,b),(0,c),(1,a),(1,b),(1,c)}</literal>.
263
A Relation is any subset of the Cartesian product of one or more
264
domains: $R \subseteq$ \mbox{$D_{1} \times D_{2} \times \ldots \times D_{k}$}
267
A Relation is any subset of the Cartesian product of one or more
268
domains: <parameter>R</parameter> ⊆
269
<parameter>D<subscript>1</subscript></parameter> ×
270
<parameter>D<subscript>2</subscript></parameter> ×
272
<parameter>D<subscript>k</subscript></parameter>.
276
For example <literal>{(0,a),(0,b),(1,a)}</literal> is a relation;
277
it is in fact a subset of
278
<parameter>D<subscript>1</subscript></parameter> ×
279
<parameter>D<subscript>2</subscript></parameter>
284
The members of a relation are called tuples. Each relation of some
286
<parameter>D<subscript>1</subscript></parameter> ×
287
<parameter>D<subscript>2</subscript></parameter> ×
289
<parameter>D<subscript>k</subscript></parameter>
290
is said to have arity <literal>k</literal> and is therefore a set
291
of <literal>k</literal>-tuples.
295
A relation can be viewed as a table (as we already did, remember
296
<xref linkend="supplier-fig" endterm="supplier-fig"> where
297
every tuple is represented by a row and every column corresponds to
298
one component of a tuple. Giving names (called attributes) to the
299
columns leads to the definition of a
300
<firstterm>relation scheme</firstterm>.
306
A {\it relation scheme} $R$ is a finite set of attributes
307
\mbox{$\{A_{1},A_{2},\ldots,A_{k}\}$}. There is a domain $D_{i}$ for
308
each attribute $A_{i}, 1 \le i \le k$ where the values of the
309
attributes are taken from. We often write a relation scheme as
310
\mbox{$R(A_{1},A_{2},\ldots,A_{k})$}.
313
A <firstterm>relation scheme</firstterm> <literal>R</literal> is a
314
finite set of attributes
315
<parameter>A<subscript>1</subscript></parameter>,
316
<parameter>A<subscript>2</subscript></parameter>,
318
<parameter>A<subscript>k</subscript></parameter>.
320
<parameter>D<subscript>i</subscript></parameter>,
322
<parameter>A<subscript>i</subscript></parameter>,
323
1 <= <literal>i</literal> <= <literal>k</literal>,
324
where the values of the attributes are taken from. We often write
326
<literal>R(<parameter>A<subscript>1</subscript></parameter>,
327
<parameter>A<subscript>2</subscript></parameter>,
329
<parameter>A<subscript>k</subscript></parameter>)</literal>.
333
A <firstterm>relation scheme</firstterm> is just a kind of template
334
whereas a <firstterm>relation</firstterm> is an instance of a
336
scheme</firstterm>. The relation consists of tuples (and can
338
viewed as a table); not so the relation scheme.
344
<title id="domains">Domains vs. Data Types</title>
347
We often talked about <firstterm>domains</firstterm>
348
in the last section. Recall that a
349
domain is, formally, just a set of values (e.g., the set of integers or
350
the real numbers). In terms of database systems we often talk of
351
<firstterm>data types</firstterm> instead of domains.
352
When we define a table we have to make
353
a decision about which attributes to include. Additionally we
354
have to decide which kind of data is going to be stored as
355
attribute values. For example the values of
356
<classname>SNAME</classname> from the table
357
<classname>SUPPLIER</classname> will be character strings,
358
whereas <classname>SNO</classname> will store
359
integers. We define this by assigning a data type to each
360
attribute. The type of <classname>SNAME</classname> will be
361
<type>VARCHAR(20)</type> (this is the <acronym>SQL</acronym> type
362
for character strings of length <= 20),
363
the type of <classname>SNO</classname> will be
364
<type>INTEGER</type>. With the assignment of a data type we also
366
a domain for an attribute. The domain of
367
<classname>SNAME</classname> is the set of all
368
character strings of length <= 20,
369
the domain of <classname>SNO</classname> is the set of
375
<sect1 id="relmodel-oper">
376
<title id="operations">Operations in the Relational Data Model</title>
379
In the previous section
380
(<xref linkend="formal-notion" endterm="formal-notion">)
381
we defined the mathematical notion of
382
the relational model. Now we know how the data can be stored using a
383
relational data model but we do not know what to do with all these
384
tables to retrieve something from the database yet. For example somebody
385
could ask for the names of all suppliers that sell the part
386
'Screw'. Therefore two rather different kinds of notations for
387
expressing operations on relations have been defined:
392
The <firstterm>Relational Algebra</firstterm> which is an
394
where queries are expressed by applying specialized operators to the
401
The <firstterm>Relational Calculus</firstterm> which is a
403
where queries are expressed by formulating some logical restrictions
404
that the tuples in the answer must satisfy.
411
<title id="rel-alg">Relational Algebra</title>
414
The <firstterm>Relational Algebra</firstterm> was introduced by
415
E. F. Codd in 1972. It consists of a set of operations on relations:
420
SELECT (σ): extracts <firstterm>tuples</firstterm> from
422
satisfy a given restriction. Let <parameter>R</parameter> be a
423
table that contains an attribute
424
<parameter>A</parameter>.
425
σ<subscript>A=a</subscript>(R) = {t ∈ R ∣ t(A) = a}
426
where <literal>t</literal> denotes a
427
tuple of <parameter>R</parameter> and <literal>t(A)</literal>
428
denotes the value of attribute <parameter>A</parameter> of
429
tuple <literal>t</literal>.
435
PROJECT (π): extracts specified
436
<firstterm>attributes</firstterm> (columns) from a
437
relation. Let <classname>R</classname> be a relation
438
that contains an attribute <classname>X</classname>.
439
π<subscript>X</subscript>(<classname>R</classname>) = {t(X) ∣ t ∈ <classname>R</classname>},
440
where <literal>t</literal>(<classname>X</classname>) denotes the value of
441
attribute <classname>X</classname> of tuple <literal>t</literal>.
447
PRODUCT (×): builds the Cartesian product of two
448
relations. Let <classname>R</classname> be a table with arity
449
<literal>k</literal><subscript>1</subscript> and let
450
<classname>S</classname> be a table with
451
arity <literal>k</literal><subscript>2</subscript>.
452
<classname>R</classname> × <classname>S</classname>
454
<literal>k</literal><subscript>1</subscript>
455
+ <literal>k</literal><subscript>2</subscript>-tuples
456
whose first <literal>k</literal><subscript>1</subscript>
457
components form a tuple in <classname>R</classname> and whose last
458
<literal>k</literal><subscript>2</subscript> components form a
459
tuple in <classname>S</classname>.
465
UNION (∪): builds the set-theoretic union of two
466
tables. Given the tables <classname>R</classname> and
467
<classname>S</classname> (both must have the same arity),
468
the union <classname>R</classname> ∪ <classname>S</classname>
469
is the set of tuples that are in <classname>R</classname>
470
or <classname>S</classname> or both.
476
INTERSECT (∩): builds the set-theoretic intersection of two
477
tables. Given the tables <classname>R</classname> and
478
<classname>S</classname>,
479
<classname>R</classname> ∩ <classname>S</classname> is the
481
that are in <classname>R</classname> and in
482
<classname>S</classname>.
483
We again require that <classname>R</classname> and
484
<classname>S</classname> have the
491
DIFFERENCE (− or ∖): builds the set difference of
492
two tables. Let <classname>R</classname> and <classname>S</classname>
493
again be two tables with the same
494
arity. <classname>R</classname> - <classname>S</classname>
495
is the set of tuples in <classname>R</classname> but not in
496
<classname>S</classname>.
502
JOIN (∏): connects two tables by their common
503
attributes. Let <classname>R</classname> be a table with the
504
attributes <classname>A</classname>,<classname>B</classname>
505
and <classname>C</classname> and
506
let <classname>S</classname> be a table with the attributes
507
<classname>C</classname>,<classname>D</classname>
508
and <classname>E</classname>. There is one
509
attribute common to both relations,
510
the attribute <classname>C</classname>.
512
<classname>R</classname> ∏ <classname>S</classname> =
513
π<subscript><classname>R</classname>.<classname>A</classname>,<classname>R</classname>.<classname>B</classname>,<classname>R</classname>.<classname>C</classname>,<classname>S</classname>.<classname>D</classname>,<classname>S</classname>.<classname>E</classname></subscript>(σ<subscript><classname>R</classname>.<classname>C</classname>=<classname>S</classname>.<classname>C</classname></subscript>(<classname>R</classname> × <classname>S</classname>)).
515
R ∏ S = π<subscript>R.A,R.B,R.C,S.D,S.E</subscript>(σ<subscript>R.C=S.C</subscript>(R × S)).
516
What are we doing here? We first calculate the Cartesian
518
<classname>R</classname> × <classname>S</classname>.
519
Then we select those tuples whose values for the common
520
attribute <classname>C</classname> are equal
521
(σ<subscript>R.C = S.C</subscript>).
523
that contains the attribute <classname>C</classname>
524
two times and we correct this by
525
projecting out the duplicate column.
529
<title id="join-example">An Inner Join</title>
532
Let's have a look at the tables that are produced by evaluating the steps
533
necessary for a join.
534
Let the following two tables be given:
539
---+---+--- ---+---+---
548
First we calculate the Cartesian product
549
<classname>R</classname> × <classname>S</classname> and
554
A | B | R.C | S.C | D | E
555
---+---+-----+-----+---+---
556
1 | 2 | 3 | 3 | a | b
557
1 | 2 | 3 | 6 | c | d
558
4 | 5 | 6 | 3 | a | b
559
4 | 5 | 6 | 6 | c | d
560
7 | 8 | 9 | 3 | a | b
561
7 | 8 | 9 | 6 | c | d
567
σ<subscript>R.C=S.C</subscript>(R × S)
571
A | B | R.C | S.C | D | E
572
---+---+-----+-----+---+---
573
1 | 2 | 3 | 3 | a | b
574
4 | 5 | 6 | 6 | c | d
579
To remove the duplicate column
580
<classname>S</classname>.<classname>C</classname>
581
we project it out by the following operation:
582
π<subscript>R.A,R.B,R.C,S.D,S.E</subscript>(σ<subscript>R.C=S.C</subscript>(R × S))
596
DIVIDE (÷): Let <classname>R</classname> be a table
597
with the attributes A, B, C, and D and let
598
<classname>S</classname> be a table with the attributes
600
Then we define the division as:
603
R ÷ S = {t ∣ ∀ t<subscript>s</subscript> ∈ S ∃ t<subscript>r</subscript> ∈ R
607
t<subscript>r</subscript>(A,B)=t∧t<subscript>r</subscript>(C,D)=t<subscript>s</subscript>}
609
t<subscript>r</subscript>(x,y)
611
tuple of table <classname>R</classname> that consists only of
612
the components <literal>x</literal> and <literal>y</literal>.
613
Note that the tuple <literal>t</literal> only consists of the
614
components <classname>A</classname> and
615
<classname>B</classname> of relation <classname>R</classname>.
618
<para id="divide-example">
619
Given the following tables
624
---+---+---+--- ---+---
648
For a more detailed description and definition of the relational
649
algebra refer to [<xref linkend="ULL88" endterm="ULL88">] or
650
[<xref linkend="DATE94" endterm="DATE94">].
654
<title id="suppl-rel-alg">A Query Using Relational Algebra</title>
656
Recall that we formulated all those relational operators to be able to
657
retrieve data from the database. Let's return to our example from
659
section (<xref linkend="operations" endterm="operations">)
660
where someone wanted to know the names of all
661
suppliers that sell the part <literal>Screw</literal>.
662
This question can be answered
663
using relational algebra by the following operation:
666
π<subscript>SUPPLIER.SNAME</subscript>(σ<subscript>PART.PNAME='Screw'</subscript>(SUPPLIER ∏ SELLS ∏ PART))
671
We call such an operation a query. If we evaluate the above query
672
against the our example tables
673
(<xref linkend="supplier-fig" endterm="supplier-fig">)
674
we will obtain the following result:
686
<sect2 id="rel-calc">
687
<title>Relational Calculus</title>
690
The relational calculus is based on the
691
<firstterm>first order logic</firstterm>. There are
692
two variants of the relational calculus:
697
The <firstterm>Domain Relational Calculus</firstterm>
698
(<acronym>DRC</acronym>), where variables
699
stand for components (attributes) of the tuples.
705
The <firstterm>Tuple Relational Calculus</firstterm>
706
(<acronym>TRC</acronym>), where variables stand for tuples.
713
We want to discuss the tuple relational calculus only because it is
714
the one underlying the most relational languages. For a detailed
715
discussion on <acronym>DRC</acronym> (and also
716
<acronym>TRC</acronym>) see
717
<xref linkend="DATE94" endterm="DATE94">
719
<xref linkend="ULL88" endterm="ULL88">.
724
<title>Tuple Relational Calculus</title>
727
The queries used in <acronym>TRC</acronym> are of the following
734
where <literal>x</literal> is a tuple variable
735
<classname>A</classname> is a set of attributes and <literal>F</literal> is a
736
formula. The resulting relation consists of all tuples
737
<literal>t(A)</literal> that satisfy <literal>F(t)</literal>.
741
If we want to answer the question from example
742
<xref linkend="suppl-rel-alg" endterm="suppl-rel-alg">
743
using <acronym>TRC</acronym> we formulate the following query:
746
{x(SNAME) ∣ x ∈ SUPPLIER ∧
747
∃ y ∈ SELLS ∃ z ∈ PART (y(SNO)=x(SNO) ∧
754
Evaluating the query against the tables from
755
<xref linkend="supplier-fig" endterm="supplier-fig">
756
again leads to the same result
758
<xref linkend="suppl-rel-alg" endterm="suppl-rel-alg">.
762
<sect2 id="alg-vs-calc">
763
<title>Relational Algebra vs. Relational Calculus</title>
766
The relational algebra and the relational calculus have the same
767
<firstterm>expressive power</firstterm>; i.e. all queries that
768
can be formulated using relational algebra can also be formulated
769
using the relational calculus and vice versa.
770
This was first proved by E. F. Codd in
771
1972. This proof is based on an algorithm (<quote>Codd's reduction
772
algorithm</quote>) by which an arbitrary expression of the relational
773
calculus can be reduced to a semantically equivalent expression of
774
relational algebra. For a more detailed discussion on that refer to
775
<xref linkend="DATE94" endterm="DATE94">
777
<xref linkend="ULL88" endterm="ULL88">.
781
It is sometimes said that languages based on the relational
782
calculus are <quote>higher level</quote> or <quote>more
783
declarative</quote> than languages based on relational algebra
784
because the algebra (partially) specifies the order of operations
785
while the calculus leaves it to a compiler or interpreter to
786
determine the most efficient order of evaluation.
791
<sect1 id="sql-language">
792
<title>The <acronym>SQL</acronym> Language</title>
795
As is the case with most modern relational languages,
796
<acronym>SQL</acronym> is based on the tuple
797
relational calculus. As a result every query that can be formulated
798
using the tuple relational calculus (or equivalently, relational
799
algebra) can also be formulated using
800
<acronym>SQL</acronym>. There are, however,
801
capabilities beyond the scope of relational algebra or calculus. Here
802
is a list of some additional features provided by
803
<acronym>SQL</acronym> that are not
804
part of relational algebra or calculus:
809
Commands for insertion, deletion or modification of data.
815
Arithmetic capability: In <acronym>SQL</acronym> it is possible
817
arithmetic operations as well as comparisons, e.g.
824
that + or other arithmetic operators appear neither in relational
825
algebra nor in relational calculus.
831
Assignment and Print Commands: It is possible to print a
832
relation constructed by a query and to assign a computed relation to a
839
Aggregate Functions: Operations such as
840
<firstterm>average</firstterm>, <firstterm>sum</firstterm>,
841
<firstterm>max</firstterm>, etc. can be applied to columns of a
843
obtain a single quantity.
850
<title id="select-title">Select</title>
853
The most often used command in <acronym>SQL</acronym> is the
854
<command>SELECT</command> statement,
855
used to retrieve data. The syntax is:
858
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
859
* | <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
860
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
861
[ FROM <replaceable class="PARAMETER">from_item</replaceable> [, ...] ]
862
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
863
[ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
864
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
865
[ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
866
[ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]
867
[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
868
[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
869
[ FOR UPDATE [ OF <replaceable class="PARAMETER">class_name</replaceable> [, ...] ] ]
874
Now we will illustrate the complex syntax of the
875
<command>SELECT</command> statement with various examples. The
876
tables used for the examples are defined in <xref
877
linkend="supplier-fig" endterm="supplier-fig">.
881
<title>Simple Selects</title>
884
Here are some simple examples using a <command>SELECT</command> statement:
887
<title id="simple-query">Simple Query with Qualification</title>
889
To retrieve all tuples from table PART where the attribute PRICE is
890
greater than 10 we formulate the following query:
901
-----+---------+--------
908
Using <quote>*</quote> in the <command>SELECT</command> statement
909
will deliver all attributes from the table. If we want to retrieve
910
only the attributes PNAME and PRICE from table PART we use the
919
In this case the result is:
928
Note that the <acronym>SQL</acronym> <command>SELECT</command>
929
corresponds to the <quote>projection</quote> in relational algebra
930
not to the <quote>selection</quote> (see <xref linkend="rel-alg"
931
endterm="rel-alg"> for more details).
935
The qualifications in the WHERE clause can also be logically connected
936
using the keywords OR, AND, and NOT:
941
WHERE PNAME = 'Bolt' AND
942
(PRICE = 0 OR PRICE <= 15);
945
will lead to the result:
955
Arithmetic operations may be used in the target list and in the WHERE
956
clause. For example if we want to know how much it would cost if we
957
take two pieces of a part we could use the following query:
960
SELECT PNAME, PRICE * 2 AS DOUBLE
962
WHERE PRICE * 2 < 50;
975
Note that the word DOUBLE after the keyword AS is the new title of the
976
second column. This technique can be used for every element of the
977
target list to assign a new title to the resulting
978
column. This new title
979
is often referred to as alias. The alias cannot be used throughout the
989
<para id="simple-join">
990
The following example shows how <firstterm>joins</firstterm> are
991
realized in <acronym>SQL</acronym>.
995
To join the three tables SUPPLIER, PART and SELLS over their common
996
attributes we formulate the following statement:
999
SELECT S.SNAME, P.PNAME
1000
FROM SUPPLIER S, PART P, SELLS SE
1001
WHERE S.SNO = SE.SNO AND
1005
and get the following table as a result:
1022
In the FROM clause we introduced an alias name for every relation
1023
because there are common named attributes (SNO and PNO) among the
1024
relations. Now we can distinguish between the common named attributes
1025
by simply prefixing the attribute name with the alias name followed by
1026
a dot. The join is calculated in the same way as shown in
1027
<xref linkend="join-example" endterm="join-example">.
1028
First the Cartesian product
1030
SUPPLIER × PART × SELLS
1032
is derived. Now only those tuples satisfying the
1033
conditions given in the WHERE clause are selected (i.e. the common
1034
named attributes have to be equal). Finally we project out all
1035
columns but S.SNAME and P.PNAME.
1039
Another way to perform joins is to use the SQL JOIN syntax as follows:
1041
select sname, pname from supplier
1042
JOIN sells USING (sno)
1043
JOIN part USING (pno);
1062
A joined table, created using JOIN syntax, is a table reference list
1063
item that occurs in a FROM clause and before any WHERE, GROUP BY,
1064
or HAVING clause. Other table references, including table names or
1065
other JOIN clauses, may be included in the FROM clause if separated
1066
by commas. JOINed tables are logically like any other
1067
table listed in the FROM clause.
1071
SQL JOINs come in two main types, CROSS JOINs (unqualified joins)
1072
and <firstterm>qualified JOINs</>. Qualified joins can be further
1073
subdivided based on the way in which the <firstterm>join condition</>
1074
is specified (ON, USING, or NATURAL) and the way in which it is
1075
applied (INNER or OUTER join).
1079
<title>Join Types</title>
1081
<term>CROSS JOIN</term>
1084
<arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
1085
<command> CROSS JOIN </command>
1086
<arg choice="req"> <replaceable class="parameter">T2</replaceable> </arg>
1090
A cross join takes two tables T1 and T2 having N and M rows
1091
respectively, and returns a joined table containing all
1092
N*M possible joined rows. For each row R1 of T1, each row
1093
R2 of T2 is joined with R1 to yield a joined table row JR
1094
consisting of all fields in R1 and R2. A CROSS JOIN is
1095
equivalent to an INNER JOIN ON TRUE.
1101
<term>Qualified JOINs</term>
1105
<arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
1106
<arg choice="opt"> NATURAL </arg>
1107
<group choice="opt">
1108
<arg choice="opt"> INNER </arg>
1110
<group choice="req">
1111
<arg choice="plain"> LEFT </arg>
1112
<arg choice="plain"> RIGHT </arg>
1113
<arg choice="plain"> FULL </arg>
1115
<arg choice="opt"> OUTER </arg>
1118
<command> JOIN </command>
1119
<arg choice="req"> <replaceable class="parameter">T2</replaceable> </arg>
1120
<group choice="req">
1121
<arg> ON <replaceable>search condition</replaceable></arg>
1122
<arg> USING ( <replaceable>join column list</replaceable> ) </arg>
1127
A qualified JOIN must specify its join condition
1128
by providing one (and only one) of NATURAL, ON, or
1129
USING. The ON clause
1130
takes a <replaceable>search condition</replaceable>,
1131
which is the same as in a WHERE clause. The USING
1132
clause takes a comma-separated list of column names,
1133
which the joined tables must have in common, and joins
1134
the tables on equality of those columns. NATURAL is
1135
shorthand for a USING clause that lists all the common
1136
column names of the two tables. A side-effect of both
1137
USING and NATURAL is that only one copy of each joined
1138
column is emitted into the result table (compare the
1139
relational-algebra definition of JOIN, shown earlier).
1142
<!-- begin join semantics -->
1148
<command> JOIN </command>
1153
For each row R1 of T1, the joined table has a row for each row
1154
in T2 that satisfies the join condition with R1.
1158
The words INNER and OUTER are optional for all JOINs.
1159
INNER is the default. LEFT, RIGHT, and FULL imply an
1168
<arg choice="plain"> LEFT </arg>
1170
<command> JOIN </command>
1175
First, an INNER JOIN is performed.
1176
Then, for each row in T1 that does not satisfy the join
1177
condition with any row in T2, an additional joined row is
1178
returned with null fields in the columns from T2.
1182
The joined table unconditionally has a row for each row in T1.
1190
<arg choice="plain"> RIGHT </arg>
1192
<command> JOIN </command>
1197
First, an INNER JOIN is performed.
1198
Then, for each row in T2 that does not satisfy the join
1199
condition with any row in T1, an additional joined row is
1200
returned with null fields in the columns from T1.
1204
The joined table unconditionally has a row for each row in T2.
1212
<arg choice="plain"> FULL </arg>
1214
<command> JOIN </command>
1219
First, an INNER JOIN is performed.
1220
Then, for each row in T1 that does not satisfy the join
1221
condition with any row in T2, an additional joined row is
1222
returned with null fields in the columns from T2.
1223
Also, for each row in T2 that does not satisfy the join
1224
condition with any row in T1, an additional joined row is
1225
returned with null fields in the columns from T1.
1229
The joined table unconditionally has a row for every row of T1
1230
and a row for every row of T2.
1236
<!-- end join semantics -->
1243
JOINs of all types can be chained together or nested where either or both of
1244
<replaceable class="parameter">T1</replaceable> and
1245
<replaceable class="parameter">T2</replaceable> may be JOINed tables.
1246
Parenthesis can be used around JOIN clauses to control the order
1247
of JOINs which are otherwise processed left to right.
1253
<title id="aggregates-tutorial">Aggregate Operators</title>
1256
<acronym>SQL</acronym> provides aggregate operators (e.g. AVG,
1257
COUNT, SUM, MIN, MAX) that take an expression as argument. The
1258
expression is evaluated at each row that satisfies the WHERE
1259
clause, and the aggregate operator is calculated over this set
1260
of input values. Normally, an aggregate delivers a single
1261
result for a whole <command>SELECT</command> statement. But if
1262
grouping is specified in the query, then a separate calculation
1263
is done over the rows of each group, and an aggregate result is
1264
delivered per group (see next section).
1267
<title id="aggregates-example">Aggregates</title>
1270
If we want to know the average cost of all parts in table PART we use
1271
the following query:
1274
SELECT AVG(PRICE) AS AVG_PRICE
1290
If we want to know how many parts are defined in table PART we use
1312
<title>Aggregation by Groups</title>
1315
<acronym>SQL</acronym> allows one to partition the tuples of a table
1316
into groups. Then the
1317
aggregate operators described above can be applied to the groups —
1318
i.e. the value of the aggregate operator is no longer calculated over
1319
all the values of the specified column but over all values of a
1320
group. Thus the aggregate operator is evaluated separately for every
1325
The partitioning of the tuples into groups is done by using the
1326
keywords <command>GROUP BY</command> followed by a list of
1327
attributes that define the
1329
<command>GROUP BY A<subscript>1</subscript>, ⃛, A<subscript>k</subscript></command>
1331
the relation into groups, such that two tuples are in the same group
1332
if and only if they agree on all the attributes
1333
A<subscript>1</subscript>, ⃛, A<subscript>k</subscript>.
1336
<title id="aggregates-groupby">Aggregates</title>
1338
If we want to know how many parts are sold by every supplier we
1339
formulate the query:
1342
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
1343
FROM SUPPLIER S, SELLS SE
1344
WHERE S.SNO = SE.SNO
1345
GROUP BY S.SNO, S.SNAME;
1352
-----+-------+-------
1361
Now let's have a look of what is happening here.
1362
First the join of the
1363
tables SUPPLIER and SELLS is derived:
1366
S.SNO | S.SNAME | SE.PNO
1367
-------+---------+--------
1380
Next we partition the tuples into groups by putting all tuples
1381
together that agree on both attributes S.SNO and S.SNAME:
1384
S.SNO | S.SNAME | SE.PNO
1385
-------+---------+--------
1388
--------------------------
1390
--------------------------
1393
--------------------------
1401
In our example we got four groups and now we can apply the aggregate
1402
operator COUNT to every group leading to the final result of the query
1409
Note that for a query using GROUP BY and aggregate
1410
operators to make sense the target list can only refer directly to
1411
the attributes being grouped by. Other attributes may only be used
1412
inside the argument of an aggregate function. Otherwise there would
1413
not be a unique value to associate with the other attributes.
1417
Also observe that it makes no sense to ask for an aggregate of
1418
an aggregate, e.g., AVG(MAX(sno)), because a
1419
<command>SELECT</command> only does one pass of grouping and
1420
aggregation. You can get a result of this kind by using a
1421
temporary table or a sub-SELECT in the FROM clause to do the
1422
first level of aggregation.
1427
<title>Having</title>
1430
The HAVING clause works much like the WHERE clause and is used to
1431
consider only those groups satisfying the qualification given in the
1432
HAVING clause. Essentially, WHERE filters out unwanted input rows
1433
before grouping and aggregation are done, whereas HAVING filters out
1434
unwanted group rows post-GROUP. Therefore, WHERE cannot refer to the
1435
results of aggregate functions. On the other hand, there's no point
1436
in writing a HAVING condition that doesn't involve an aggregate
1437
function! If your condition doesn't involve aggregates, you might
1438
as well write it in WHERE, and thereby avoid the computation of
1439
aggregates for groups that you're just going to throw away anyway.
1442
<title id="having-example">Having</title>
1445
If we want only those suppliers selling more than one part we use the
1449
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
1450
FROM SUPPLIER S, SELLS SE
1451
WHERE S.SNO = SE.SNO
1452
GROUP BY S.SNO, S.SNAME
1453
HAVING COUNT(SE.PNO) > 1;
1460
-----+-------+-------
1471
<title>Subqueries</title>
1474
In the WHERE and HAVING clauses the use of subqueries (subselects) is
1475
allowed in every place where a value is expected. In this case the
1476
value must be derived by evaluating the subquery first. The usage of
1477
subqueries extends the expressive power of
1478
<acronym>SQL</acronym>.
1481
<title id="subselect-example">Subselect</title>
1484
If we want to know all parts having a greater price than the part
1485
named 'Screw' we use the query:
1490
WHERE PRICE > (SELECT PRICE FROM PART
1491
WHERE PNAME='Screw');
1500
-----+---------+--------
1507
When we look at the above query we can see the keyword
1508
<command>SELECT</command> two times. The first one at the
1509
beginning of the query - we will refer to it as outer
1510
<command>SELECT</command> - and the one in the WHERE clause which
1511
begins a nested query - we will refer to it as inner
1512
<command>SELECT</command>. For every tuple of the outer
1513
<command>SELECT</command> the inner <command>SELECT</command> has
1514
to be evaluated. After every evaluation we know the price of the
1515
tuple named 'Screw' and we can check if the price of the actual
1516
tuple is greater. (Actually, in this example the inner query need
1517
only be evaluated once, since it does not depend on the state of
1522
If we want to know all suppliers that do not sell any part
1523
(e.g. to be able to remove these suppliers from the database) we use:
1529
(SELECT * FROM SELLS SE
1530
WHERE SE.SNO = S.SNO);
1535
In our example the result will be empty because every supplier
1536
sells at least one part. Note that we use S.SNO from the outer
1537
<command>SELECT</command> within the WHERE clause of the inner
1538
<command>SELECT</command>. Here the subquery must be evaluated
1539
afresh for each tuple from the outer query, i.e. the value for
1540
S.SNO is always taken from the current tuple of the outer
1541
<command>SELECT</command>.
1548
<title>Subqueries in FROM</title>
1551
A somewhat different way of using subqueries is to put them in the
1552
FROM clause. This is a useful feature because a subquery of this
1553
kind can output multiple columns and rows, whereas a subquery used
1554
in an expression must deliver just a single result. It also lets
1555
us get more than one round of grouping/aggregation without resorting
1556
to a temporary table.
1559
<title id="subselect-in-from-example">Subselect in FROM</title>
1562
If we want to know the highest average part price among all our
1563
suppliers, we can't write MAX(AVG(PRICE)), but we can write:
1566
SELECT MAX(subtable.avgprice)
1567
FROM (SELECT AVG(P.PRICE) AS avgprice
1568
FROM SUPPLIER S, PART P, SELLS SE
1569
WHERE S.SNO = SE.SNO AND
1571
GROUP BY S.SNO) subtable;
1574
The subquery returns one row per supplier (because of its GROUP BY)
1575
and then we aggregate over those rows in the outer query.
1582
<title>Union, Intersect, Except</title>
1585
These operations calculate the union, intersection and set theoretic
1586
difference of the tuples derived by two subqueries.
1589
<title id="union-example">Union, Intersect, Except</title>
1592
The following query is an example for UNION:
1595
SELECT S.SNO, S.SNAME, S.CITY
1597
WHERE S.SNAME = 'Jones'
1599
SELECT S.SNO, S.SNAME, S.CITY
1601
WHERE S.SNAME = 'Adams';
1608
-----+-------+--------
1615
Here is an example for INTERSECT:
1618
SELECT S.SNO, S.SNAME, S.CITY
1622
SELECT S.SNO, S.SNAME, S.CITY
1631
-----+-------+--------
1635
The only tuple returned by both parts of the query is the one having SNO=2.
1639
Finally an example for EXCEPT:
1642
SELECT S.SNO, S.SNAME, S.CITY
1646
SELECT S.SNO, S.SNAME, S.CITY
1655
-----+-------+--------
1665
<sect2 id="datadef">
1666
<title>Data Definition</title>
1669
There is a set of commands used for data definition included in the
1670
<acronym>SQL</acronym> language.
1674
<title id="create-title">Create Table</title>
1677
The most fundamental command for data definition is the
1678
one that creates a new relation (a new table). The syntax of the
1679
<command>CREATE TABLE</command> command is:
1682
CREATE TABLE <replaceable class="parameter">table_name</replaceable>
1683
(<replaceable class="parameter">name_of_attr_1</replaceable> <replaceable class="parameter">type_of_attr_1</replaceable>
1684
[, <replaceable class="parameter">name_of_attr_2</replaceable> <replaceable class="parameter">type_of_attr_2</replaceable>
1689
<title id="table-create">Table Creation</title>
1692
To create the tables defined in
1693
<xref linkend="supplier-fig" endterm="supplier-fig"> the
1694
following <acronym>SQL</acronym> statements are used:
1697
CREATE TABLE SUPPLIER
1707
PRICE DECIMAL(4 , 2));
1721
<title>Data Types in <acronym>SQL</acronym></title>
1724
The following is a list of some data types that are supported by
1725
<acronym>SQL</acronym>:
1730
INTEGER: signed fullword binary integer (31 bits precision).
1736
SMALLINT: signed halfword binary integer (15 bits precision).
1742
DECIMAL (<replaceable class="parameter">p</replaceable>[,<replaceable class="parameter">q</replaceable>]):
1743
signed packed decimal number of up to
1744
<replaceable class="parameter">p</replaceable>
1746
<replaceable class="parameter">q</replaceable>
1747
digits to the right of the decimal point.
1748
If <replaceable class="parameter">q</replaceable>
1749
is omitted it is assumed to be 0.
1755
FLOAT: signed doubleword floating point number.
1761
VARCHAR(<replaceable class="parameter">n</replaceable>):
1762
varying length character string of maximum length
1763
<replaceable class="parameter">n</replaceable>.
1769
CHAR(<replaceable class="parameter">n</replaceable>):
1770
fixed length character string of length
1771
<replaceable class="parameter">n</replaceable>.
1780
<title>Create Index</title>
1783
Indexes are used to speed up access to a relation. If a relation <classname>R</classname>
1784
has an index on attribute <classname>A</classname> then we can
1785
retrieve all tuples <replaceable>t</replaceable>
1787
<replaceable>t</replaceable>(<classname>A</classname>) = <replaceable>a</replaceable>
1788
in time roughly proportional to the number of such
1789
tuples <replaceable>t</replaceable>
1790
rather than in time proportional to the size of <classname>R</classname>.
1794
To create an index in <acronym>SQL</acronym>
1795
the <command>CREATE INDEX</command> command is used. The syntax is:
1798
CREATE INDEX <replaceable class="parameter">index_name</replaceable>
1799
ON <replaceable class="parameter">table_name</replaceable> ( <replaceable class="parameter">name_of_attribute</replaceable> );
1805
<title id="index-create">Create Index</title>
1808
To create an index named I on attribute SNAME of relation SUPPLIER
1809
we use the following statement:
1812
CREATE INDEX I ON SUPPLIER (SNAME);
1817
The created index is maintained automatically, i.e. whenever a new
1818
tuple is inserted into the relation SUPPLIER the index I is
1819
adapted. Note that the only changes a user can perceive when an
1820
index is present are increased speed for <command>SELECT</command>
1821
and decreases in speed of updates.
1828
<title>Create View</title>
1831
A view may be regarded as a <firstterm>virtual table</firstterm>,
1833
does not <emphasis>physically</emphasis> exist in the database
1834
but looks to the user
1835
as if it does. By contrast, when we talk of a
1836
<firstterm>base table</firstterm> there is
1837
really a physically stored counterpart of each row of the table
1838
somewhere in the physical storage.
1842
Views do not have their own, physically separate, distinguishable
1843
stored data. Instead, the system stores the definition of the
1844
view (i.e. the rules about how to access physically stored base
1845
tables in order to materialize the view) somewhere in the system
1847
<xref linkend="tutorial-catalogs-title" endterm="tutorial-catalogs-title">). For a
1848
discussion on different techniques to implement views refer to
1851
<xref linkend="view-impl" endterm="view-impl">.
1853
<citetitle>SIM98</citetitle>.
1857
In <acronym>SQL</acronym> the <command>CREATE VIEW</command>
1858
command is used to define a view. The syntax
1862
CREATE VIEW <replaceable class="parameter">view_name</replaceable>
1863
AS <replaceable class="parameter">select_stmt</replaceable>
1866
where <replaceable class="parameter">select_stmt</replaceable>
1867
is a valid select statement as defined
1868
in <xref linkend="select-title" endterm="select-title">.
1869
Note that <replaceable class="parameter">select_stmt</replaceable> is
1870
not executed when the view is created. It is just stored in the
1871
<firstterm>system catalogs</firstterm>
1872
and is executed whenever a query against the view is made.
1876
Let the following view definition be given (we use
1878
<xref linkend="supplier-fig" endterm="supplier-fig"> again):
1881
CREATE VIEW London_Suppliers
1882
AS SELECT S.SNAME, P.PNAME
1883
FROM SUPPLIER S, PART P, SELLS SE
1884
WHERE S.SNO = SE.SNO AND
1891
Now we can use this <firstterm>virtual relation</firstterm>
1892
<classname>London_Suppliers</classname> as
1893
if it were another base table:
1896
SELECT * FROM London_Suppliers
1897
WHERE PNAME = 'Screw';
1900
which will return the following table:
1910
To calculate this result the database system has to do a
1911
<emphasis>hidden</emphasis>
1912
access to the base tables SUPPLIER, SELLS and PART first. It
1913
does so by executing the query given in the view definition against
1914
those base tables. After that the additional qualifications
1916
query against the view) can be applied to obtain the resulting
1922
<title>Drop Table, Drop Index, Drop View</title>
1925
To destroy a table (including all tuples stored in that table) the
1926
<command>DROP TABLE</command> command is used:
1929
DROP TABLE <replaceable class="parameter">table_name</replaceable>;
1934
To destroy the SUPPLIER table use the following statement:
1937
DROP TABLE SUPPLIER;
1942
The <command>DROP INDEX</command> command is used to destroy an index:
1945
DROP INDEX <replaceable class="parameter">index_name</replaceable>;
1950
Finally to destroy a given view use the command <command>DROP
1954
DROP VIEW <replaceable class="parameter">view_name</replaceable>;
1961
<title>Data Manipulation</title>
1964
<title>Insert Into</title>
1967
Once a table is created (see
1968
<xref linkend="create-title" endterm="create-title">), it can be filled
1969
with tuples using the command <command>INSERT INTO</command>.
1973
INSERT INTO <replaceable class="parameter">table_name</replaceable> (<replaceable class="parameter">name_of_attr_1</replaceable>
1974
[, <replaceable class="parameter">name_of_attr_2</replaceable> [,...]])
1975
VALUES (<replaceable class="parameter">val_attr_1</replaceable> [, <replaceable class="parameter">val_attr_2</replaceable> [, ...]]);
1980
To insert the first tuple into the relation SUPPLIER (from
1981
<xref linkend="supplier-fig" endterm="supplier-fig">) we use the
1982
following statement:
1985
INSERT INTO SUPPLIER (SNO, SNAME, CITY)
1986
VALUES (1, 'Smith', 'London');
1991
To insert the first tuple into the relation SELLS we use:
1994
INSERT INTO SELLS (SNO, PNO)
2001
<title>Update</title>
2004
To change one or more attribute values of tuples in a relation the
2005
<command>UPDATE</command> command is used. The syntax is:
2008
UPDATE <replaceable class="parameter">table_name</replaceable>
2009
SET <replaceable class="parameter">name_of_attr_1</replaceable> = <replaceable class="parameter">value_1</replaceable>
2010
[, ... [, <replaceable class="parameter">name_of_attr_k</replaceable> = <replaceable class="parameter">value_k</replaceable>]]
2011
WHERE <replaceable class="parameter">condition</replaceable>;
2016
To change the value of attribute PRICE of the part 'Screw' in the
2017
relation PART we use:
2022
WHERE PNAME = 'Screw';
2027
The new value of attribute PRICE of the tuple whose name is 'Screw' is
2033
<title>Delete</title>
2036
To delete a tuple from a particular table use the command DELETE
2037
FROM. The syntax is:
2040
DELETE FROM <replaceable class="parameter">table_name</replaceable>
2041
WHERE <replaceable class="parameter">condition</replaceable>;
2046
To delete the supplier called 'Smith' of the table SUPPLIER the
2047
following statement is used:
2050
DELETE FROM SUPPLIER
2051
WHERE SNAME = 'Smith';
2057
<sect2 id="tutorial-catalogs">
2058
<title id="tutorial-catalogs-title">System Catalogs</title>
2061
In every <acronym>SQL</acronym> database system
2062
<firstterm>system catalogs</firstterm> are used to keep
2063
track of which tables, views indexes etc. are defined in the
2064
database. These system catalogs can be queried as if they were normal
2065
relations. For example there is one catalog used for the definition of
2066
views. This catalog stores the query from the view definition. Whenever
2067
a query against a view is made, the system first gets the
2068
<firstterm>view definition query</firstterm> out of the catalog
2069
and materializes the view
2070
before proceeding with the user query (see
2073
<xref linkend="view-impl" endterm="view-impl">.
2074
<citetitle>SIM98</citetitle>
2076
<xref linkend="SIM98" endterm="SIM98">
2078
description). For more information about system catalogs refer to
2079
<xref linkend="DATE94" endterm="DATE94">.
2084
<title>Embedded <acronym>SQL</acronym></title>
2087
In this section we will sketch how <acronym>SQL</acronym> can be
2088
embedded into a host language (e.g. <literal>C</literal>).
2089
There are two main reasons why we want to use <acronym>SQL</acronym>
2090
from a host language:
2095
There are queries that cannot be formulated using pure <acronym>SQL</acronym>
2096
(i.e. recursive queries). To be able to perform such queries we need a
2097
host language with a greater expressive power than
2098
<acronym>SQL</acronym>.
2104
We simply want to access a database from some application that
2105
is written in the host language (e.g. a ticket reservation system
2106
with a graphical user interface is written in C and the information
2107
about which tickets are still left is stored in a database that can be
2108
accessed using embedded <acronym>SQL</acronym>).
2115
A program using embedded <acronym>SQL</acronym>
2116
in a host language consists of statements
2117
of the host language and of
2118
<firstterm>embedded <acronym>SQL</acronym></firstterm>
2119
(<acronym>ESQL</acronym>) statements. Every <acronym>ESQL</acronym>
2120
statement begins with the keywords <command>EXEC SQL</command>.
2121
The <acronym>ESQL</acronym> statements are
2122
transformed to statements of the host language
2123
by a <firstterm>precompiler</firstterm>
2124
(which usually inserts
2125
calls to library routines that perform the various <acronym>SQL</acronym>
2130
When we look at the examples throughout
2131
<xref linkend="select-title" endterm="select-title"> we
2132
realize that the result of the queries is very often a set of
2133
tuples. Most host languages are not designed to operate on sets so we
2134
need a mechanism to access every single tuple of the set of tuples
2135
returned by a SELECT statement. This mechanism can be provided by
2136
declaring a <firstterm>cursor</firstterm>.
2137
After that we can use the <command>FETCH</command> command to
2138
retrieve a tuple and set the cursor to the next tuple.
2142
For a detailed discussion on embedded <acronym>SQL</acronym>
2144
<xref linkend="DATE97" endterm="DATE97">,
2145
<xref linkend="DATE94" endterm="DATE94">,
2147
<xref linkend="ULL88" endterm="ULL88">.
2153
<!-- Keep this comment at the end of the file
2158
sgml-minimize-attributes:nil
2159
sgml-always-quote-attributes:t
2162
sgml-parent-document:nil
2163
sgml-default-dtd-file:"./reference.ced"
2164
sgml-exposed-tags:nil
2165
sgml-local-catalogs:("/usr/lib/sgml/catalog")
2166
sgml-local-ecat-files:nil
added
removed
doc/src/sgml/sql.sgml
