sql.sgml 66.8 KB
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 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148
<!-- $PostgreSQL: pgsql/doc/src/sgml/sql.sgml,v 1.42 2006/09/16 00:30:15 momjian Exp $ -->

 <chapter id="sql-intro">
  <title>SQL</title>

  <abstract>
   <para>
    This chapter introduces the mathematical concepts behind
    relational databases. It is not required reading, so if you bog
    down or want to get straight to some simple examples feel free to
    jump ahead to the next chapter and come back when you have more
    time and patience. This stuff is supposed to be fun!
   </para>

   <para>
    This material originally appeared as a part of
    Stefan Simkovics' Master's Thesis
    (<xref linkend="SIM98" endterm="SIM98">).
   </para>
  </abstract>

  <para>
   <acronym>SQL</acronym> has become the most popular relational query 
   language.
   The name <quote><acronym>SQL</acronym></quote> is an abbreviation for
   <firstterm>Structured Query Language</firstterm>. 
   In 1974 Donald Chamberlin and others defined the
   language SEQUEL (<firstterm>Structured English Query
    Language</firstterm>) at IBM
   Research. This language was first implemented in an IBM
   prototype called SEQUEL-XRM in 1974-75. In 1976-77 a revised version
   of SEQUEL called SEQUEL/2 was defined and the name was changed to
   <acronym>SQL</acronym>
   subsequently.
  </para>

  <para>
   A new prototype called System R was developed by IBM in 1977. System R
   implemented a large subset of SEQUEL/2 (now <acronym>SQL</acronym>)
   and a number of
   changes were made to <acronym>SQL</acronym> during the project.
   System R was installed in
   a number of user sites, both internal IBM sites and also some selected
   customer sites. Thanks to the success and acceptance of System R at
   those user sites IBM started to develop commercial products that
   implemented the <acronym>SQL</acronym> language based on the System
   R technology.
  </para>

  <para>
   Over the next years IBM and also a number of other vendors announced
   <acronym>SQL</acronym> products such as
   <productname>SQL/DS</productname> (IBM),
   <productname>DB2</productname> (IBM),
   <productname>ORACLE</productname> (Oracle Corp.),
   <productname>DG/SQL</productname> (Data General Corp.),
   and <productname>SYBASE</productname> (Sybase Inc.).
  </para>

  <para>
   <acronym>SQL</acronym> is also an official standard now. In 1982
   the American National
   Standards Institute (<acronym>ANSI</acronym>) chartered its
   Database Committee X3H2 to
   develop a proposal for a standard relational language. This proposal
   was ratified in 1986 and consisted essentially of the IBM dialect of
   <acronym>SQL</acronym>. In 1987 this <acronym>ANSI</acronym> 
   standard was also accepted as an international
   standard by the International Organization for Standardization
   (<acronym>ISO</acronym>).
   This original standard version of <acronym>SQL</acronym> is often
   referred to,
   informally, as <quote><abbrev>SQL/86</abbrev></quote>. In 1989 the original
   standard was extended
   and this new standard is often, again informally, referred to as
   <quote><abbrev>SQL/89</abbrev></quote>. Also in 1989, a related standard called
   <firstterm>Database Language Embedded <acronym>SQL</acronym></firstterm>
   (<acronym>ESQL</acronym>) was developed.
  </para>

  <para>
   The <acronym>ISO</acronym> and <acronym>ANSI</acronym> committees
   have been working for many years on the
   definition of a greatly expanded version of the original standard,
   referred to informally as <firstterm><acronym>SQL2</acronym></firstterm>
   or <firstterm><acronym>SQL/92</acronym></firstterm>. This version became a
   ratified standard - <quote>International Standard ISO/IEC 9075:1992,
   Database Language <acronym>SQL</acronym></quote> - in late 1992.
   <acronym>SQL/92</acronym> is the version
   normally meant when people refer to <quote>the <acronym>SQL</acronym>
   standard</quote>. A detailed
   description of <acronym>SQL/92</acronym> is given in 
   <xref linkend="DATE97" endterm="DATE97">. At the time of
   writing this document a new standard informally referred to
   as <firstterm><acronym>SQL3</acronym></firstterm>
   is under development. It is planned to make <acronym>SQL</acronym>
   a Turing-complete
   language, i.e. all computable queries (e.g. recursive queries) will be
   possible. This has now been completed as SQL:2003.
  </para>

  <sect1 id="rel-model">
   <title>The Relational Data Model</title>

  <para>
    As mentioned before, <acronym>SQL</acronym> is a relational
    language. That means it is
    based on the <firstterm>relational data model</firstterm>
    first published by E.F. Codd in
    1970. We will give a formal description of the relational model
    later (in
    <xref linkend="formal-notion" endterm="formal-notion">)
    but first we want to have a look at it from a more intuitive
    point of view.
  </para>

  <para>
    A <firstterm>relational database</firstterm> is a database that is 
    perceived by its
    users as a <firstterm>collection of tables</firstterm> (and
    nothing else but tables).
    A table consists of rows and columns where each row represents a
    record and each column represents an attribute of the records
    contained in the table.
    <xref linkend="supplier-fig" endterm="supplier-fig">
    shows an example of a database consisting of three tables:

    <itemizedlist>
     <listitem>
      <para>
       SUPPLIER is a table storing the number
       (SNO), the name (SNAME) and the city (CITY) of a supplier.
      </para>
     </listitem>

     <listitem>
      <para>
       PART is a table storing the number (PNO) the name (PNAME) and
       the price (PRICE) of a part.
      </para>
     </listitem>

     <listitem>
      <para>
       SELLS stores information about which part (PNO) is sold by which
       supplier (SNO). 
       It serves in a sense to connect the other two tables together.
      </para>
     </listitem>
    </itemizedlist>

    <example>
     <title id="supplier-fig">The Suppliers and Parts Database</title>
     <programlisting>
SUPPLIER:                   SELLS:
 SNO |  SNAME  |  CITY       SNO | PNO
----+---------+--------     -----+-----
 1  |  Smith  | London        1  |  1
 2  |  Jones  | Paris         1  |  2
 3  |  Adams  | Vienna        2  |  4
 4  |  Blake  | Rome          3  |  1
                              3  |  3
                              4  |  2
PART:                         4  |  3
 PNO |  PNAME  |  PRICE       4  |  4
----+---------+---------
 1  |  Screw  |   10
 2  |  Nut    |    8
 3  |  Bolt   |   15
 4  |  Cam    |   25
     </programlisting>
    </example>
   </para>

   <para>
    The tables PART and SUPPLIER may be regarded as
    <firstterm>entities</firstterm> and
    SELLS may be regarded as a <firstterm>relationship</firstterm>
    between a particular
    part and a particular supplier. 
   </para>

   <para>
    As we will see later, <acronym>SQL</acronym> operates on tables
    like the ones just
    defined but before that we will study the theory of the relational
    model.
   </para>
  </sect1>

  <sect1 id="relmodel-formal">
   <title id="formal-notion">Relational Data Model Formalities</title>

   <para>
    The mathematical concept underlying the relational model is the
    set-theoretic <firstterm>relation</firstterm> which is a subset of
    the Cartesian
    product of a list of domains. This set-theoretic relation gives
    the model its name (do not confuse it with the relationship from the
    <firstterm>Entity-Relationship model</firstterm>).
    Formally a domain is simply a set of
    values. For example the set of integers is a domain. Also the set of
    character strings of length 20 and the real numbers are examples of
    domains.
   </para>

   <para>
<!--
\begin{definition}
The <firstterm>Cartesian product</firstterm> of domains $D_{1},
    D_{2},\ldots, D_{k}$ written
\mbox{$D_{1} \times D_{2} \times \ldots \times D_{k}$} is the set of
all $k$-tuples $(v_{1},v_{2},\ldots,v_{k})$ such that \mbox{$v_{1} \in
D_{1}, v_{2} \in D_{2}, \ldots, v_{k} \in D_{k}$}.
\end{definition}
-->
    The <firstterm>Cartesian product</firstterm> of domains
    <parameter>D<subscript>1</subscript></parameter>,
    <parameter>D<subscript>2</subscript></parameter>,
    ...
    <parameter>D<subscript>k</subscript></parameter>,
    written
    <parameter>D<subscript>1</subscript></parameter> &times;
    <parameter>D<subscript>2</subscript></parameter> &times;
    ... &times;
    <parameter>D<subscript>k</subscript></parameter>
    is the set of all k-tuples
    <parameter>v<subscript>1</subscript></parameter>,
    <parameter>v<subscript>2</subscript></parameter>,
    ...
    <parameter>v<subscript>k</subscript></parameter>,
    such that
    <parameter>v<subscript>1</subscript></parameter> &isin; 
    <parameter>D<subscript>1</subscript></parameter>,
    <parameter>v<subscript>2</subscript></parameter> &isin; 
    <parameter>D<subscript>2</subscript></parameter>,
    ...
    <parameter>v<subscript>k</subscript></parameter> &isin; 
    <parameter>D<subscript>k</subscript></parameter>.
   </para>

   <para>
    For example, when we have
<!--
 $k=2$, $D_{1}=\{0,1\}$ and
$D_{2}=\{a,b,c\}$, then $D_{1} \times D_{2}$ is
$\{(0,a),(0,b),(0,c),(1,a),(1,b),(1,c)\}$.
-->
    <parameter>k</parameter>=2,
    <parameter>D<subscript>1</subscript></parameter>=<literal>{0,1}</literal> and
    <parameter>D<subscript>2</subscript></parameter>=<literal>{a,b,c}</literal> then
    <parameter>D<subscript>1</subscript></parameter> &times;
    <parameter>D<subscript>2</subscript></parameter> is
    <literal>{(0,a),(0,b),(0,c),(1,a),(1,b),(1,c)}</literal>.
   </para>

   <para>
<!--
\begin{definition}
A Relation is any subset of the Cartesian product of one or more
domains: $R \subseteq$ \mbox{$D_{1} \times D_{2} \times \ldots \times D_{k}$}
\end{definition}
-->
    A Relation is any subset of the Cartesian product of one or more
    domains: <parameter>R</parameter> &sube;
    <parameter>D<subscript>1</subscript></parameter> &times;
    <parameter>D<subscript>2</subscript></parameter> &times;
    ... &times;
    <parameter>D<subscript>k</subscript></parameter>.
   </para>

   <para>
    For example <literal>{(0,a),(0,b),(1,a)}</literal> is a relation;
    it is in fact a subset of
    <parameter>D<subscript>1</subscript></parameter> &times;
    <parameter>D<subscript>2</subscript></parameter>
    mentioned above.
   </para>

   <para>
    The members of a relation are called tuples. Each relation of some
    Cartesian product
    <parameter>D<subscript>1</subscript></parameter> &times;
    <parameter>D<subscript>2</subscript></parameter> &times;
    ... &times;
    <parameter>D<subscript>k</subscript></parameter>
    is said to have arity <literal>k</literal> and is therefore a set
    of <literal>k</literal>-tuples.
   </para>

   <para>
    A relation can be viewed as a table (as we already did, remember
    <xref linkend="supplier-fig" endterm="supplier-fig"> where
    every tuple is represented by a row and every column corresponds to
    one component of a tuple. Giving names (called attributes) to the
    columns leads to the definition of a 
    <firstterm>relation scheme</firstterm>.
   </para>

   <para>
<!--
\begin{definition}
A {\it relation scheme} $R$ is a finite set of attributes
\mbox{$\{A_{1},A_{2},\ldots,A_{k}\}$}. There is a domain $D_{i}$ for
each attribute $A_{i}, 1 \le i \le k$ where the values of the
attributes are taken from. We often write a relation scheme as
\mbox{$R(A_{1},A_{2},\ldots,A_{k})$}.
\end{definition}
-->
    A <firstterm>relation scheme</firstterm> <literal>R</literal> is a 
    finite set of attributes
    <parameter>A<subscript>1</subscript></parameter>,
    <parameter>A<subscript>2</subscript></parameter>,
    ...
    <parameter>A<subscript>k</subscript></parameter>.
    There is a domain
    <parameter>D<subscript>i</subscript></parameter>,
    for each attribute
    <parameter>A<subscript>i</subscript></parameter>,
    1 &lt;= <literal>i</literal> &lt;= <literal>k</literal>,
    where the values of the attributes are taken from. We often write
    a relation scheme as
    <literal>R(<parameter>A<subscript>1</subscript></parameter>,
    <parameter>A<subscript>2</subscript></parameter>,
    ...
    <parameter>A<subscript>k</subscript></parameter>)</literal>.

    <note>
     <para>
      A <firstterm>relation scheme</firstterm> is just a kind of template
      whereas a <firstterm>relation</firstterm> is an instance of a
      <firstterm>relation
       scheme</firstterm>. The relation consists of tuples (and can
      therefore be
      viewed as a table); not so the relation scheme.
     </para>
    </note>
   </para>

   <sect2>
    <title id="domains">Domains vs. Data Types</title>

    <para>
     We often talked about <firstterm>domains</firstterm>
     in the last section. Recall that a
     domain is, formally, just a set of values (e.g., the set of integers or
     the real numbers). In terms of database systems we often talk of
     <firstterm>data types</firstterm> instead of domains.
     When we define a table we have to make
     a decision about which attributes to include. Additionally we
     have to decide which kind of data is going to be stored as
     attribute values. For example the values of
     <classname>SNAME</classname> from the table
     <classname>SUPPLIER</classname> will be character strings,
     whereas <classname>SNO</classname> will store
     integers. We define this by assigning a data type to each
     attribute. The type of <classname>SNAME</classname> will be
     <type>VARCHAR(20)</type> (this is the <acronym>SQL</acronym> type
     for character strings of length &lt;= 20),
     the type of <classname>SNO</classname> will be
     <type>INTEGER</type>. With the assignment of a data type we also
     have selected
     a domain for an attribute. The domain of
     <classname>SNAME</classname> is the set of all
     character strings of length &lt;= 20,
     the domain of <classname>SNO</classname> is the set of
     all integer numbers.
    </para>
   </sect2>
  </sect1>

  <sect1 id="relmodel-oper">
   <title id="operations">Operations in the Relational Data Model</title>

   <para>
    In the previous section
    (<xref linkend="formal-notion" endterm="formal-notion">)
    we defined the mathematical notion of
    the relational model. Now we know how the data can be stored using a
    relational data model but we do not know what to do with all these
    tables to retrieve something from the database yet. For example somebody
    could ask for the names of all suppliers that sell the part
    'Screw'. Therefore two rather different kinds of notations for
    expressing operations on relations have been defined:

    <itemizedlist>
     <listitem>
      <para>
       The <firstterm>Relational Algebra</firstterm> which is an
       algebraic notation,
       where queries are expressed by applying specialized operators to the
       relations.
      </para>
     </listitem>

     <listitem>
      <para>
       The <firstterm>Relational Calculus</firstterm> which is a
       logical notation,
       where queries are expressed by formulating some logical restrictions
       that the tuples in the answer must satisfy.
      </para>
    </listitem>
    </itemizedlist>
   </para>

   <sect2>
    <title id="rel-alg">Relational Algebra</title>

    <para>
     The <firstterm>Relational Algebra</firstterm> was introduced by
     E. F. Codd in 1972. It consists of a set of operations on relations:

     <itemizedlist>
      <listitem>
       <para>
	SELECT (&sigma;): extracts <firstterm>tuples</firstterm> from
	a relation that
	satisfy a given restriction. Let <parameter>R</parameter> be a 
	table that contains an attribute
	<parameter>A</parameter>.
&sigma;<subscript>A=a</subscript>(R) = {t &isin; R &mid; t(A) = a}
	where <literal>t</literal> denotes a
	tuple of <parameter>R</parameter> and <literal>t(A)</literal>
	denotes the value of attribute <parameter>A</parameter> of
	tuple <literal>t</literal>.
       </para>
      </listitem>

      <listitem>
       <para>
	PROJECT (&pi;): extracts specified
	<firstterm>attributes</firstterm> (columns) from a
	relation. Let <classname>R</classname> be a relation
	that contains an attribute <classname>X</classname>.
	&pi;<subscript>X</subscript>(<classname>R</classname>) = {t(X) &mid; t &isin; <classname>R</classname>},
	where <literal>t</literal>(<classname>X</classname>) denotes the value of
	attribute <classname>X</classname> of tuple <literal>t</literal>.
       </para>
      </listitem>

      <listitem>
       <para>
	PRODUCT (&times;): builds the Cartesian product of two
	relations. Let <classname>R</classname> be a table with arity
	<literal>k</literal><subscript>1</subscript> and let
	<classname>S</classname> be a table with
	arity <literal>k</literal><subscript>2</subscript>.
	<classname>R</classname> &times; <classname>S</classname>
	is the set of all 
	<literal>k</literal><subscript>1</subscript>
	+ <literal>k</literal><subscript>2</subscript>-tuples
	whose first <literal>k</literal><subscript>1</subscript>
	components form a tuple in <classname>R</classname> and whose last
	<literal>k</literal><subscript>2</subscript> components form a
	tuple in <classname>S</classname>.
       </para>
      </listitem>

      <listitem>
       <para>
	UNION (&cup;): builds the set-theoretic union of two
	tables. Given the tables <classname>R</classname> and
	<classname>S</classname> (both must have the same arity),
	the union <classname>R</classname> &cup; <classname>S</classname>
	is the set of tuples that are in <classname>R</classname>
	or <classname>S</classname> or both.
       </para>
      </listitem>

      <listitem>
       <para>
	INTERSECT (&cap;): builds the set-theoretic intersection of two
	tables. Given the tables <classname>R</classname> and
	<classname>S</classname>,
	<classname>R</classname> &cap; <classname>S</classname> is the
	set of tuples
	that are in <classname>R</classname> and in
	<classname>S</classname>.
	We again require that <classname>R</classname> and 
	<classname>S</classname> have the
	same arity.
       </para>
      </listitem>

      <listitem>
       <para>
	DIFFERENCE (&minus; or &setmn;): builds the set difference of
	two tables. Let <classname>R</classname> and <classname>S</classname>
	again be two tables with the same
	arity. <classname>R</classname> - <classname>S</classname>
	is the set of tuples in <classname>R</classname> but not in
	<classname>S</classname>.
       </para>
      </listitem>

      <listitem>
       <para>
	JOIN (&prod;): connects two tables by their common
	attributes. Let <classname>R</classname> be a table with the 
	attributes <classname>A</classname>,<classname>B</classname> 
	and <classname>C</classname> and
	let <classname>S</classname> be a table with the attributes
	<classname>C</classname>,<classname>D</classname>
	and <classname>E</classname>. There is one
	attribute common to both relations,
	the attribute <classname>C</classname>. 
<!--
	<classname>R</classname> &prod; <classname>S</classname> =
	&pi;<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>(&sigma;<subscript><classname>R</classname>.<classname>C</classname>=<classname>S</classname>.<classname>C</classname></subscript>(<classname>R</classname> &times; <classname>S</classname>)).
-->
	R &prod; S = &pi;<subscript>R.A,R.B,R.C,S.D,S.E</subscript>(&sigma;<subscript>R.C=S.C</subscript>(R &times; S)).
	What are we doing here? We first calculate the Cartesian
	product
	<classname>R</classname> &times; <classname>S</classname>.
	Then we select those tuples whose values for the common
	attribute <classname>C</classname> are equal
	(&sigma;<subscript>R.C = S.C</subscript>).
	Now we have a table
	that contains the attribute <classname>C</classname>
	two times and we correct this by
	projecting out the duplicate column.
       </para>

       <example>
	<title id="join-example">An Inner Join</title>

	<para>
	 Let's have a look at the tables that are produced by evaluating the steps
	 necessary for a join.
	 Let the following two tables be given:

	 <programlisting>
R:                 S:
 A | B | C          C | D | E
---+---+---        ---+---+---
 1 | 2 | 3          3 | a | b
 4 | 5 | 6          6 | c | d
 7 | 8 | 9
	 </programlisting>
	</para>
       </example>

       <para>
	First we calculate the Cartesian product
	<classname>R</classname> &times; <classname>S</classname> and
	get:

	<programlisting>
R x S:
 A | B | R.C | S.C | D | E
---+---+-----+-----+---+---
 1 | 2 |  3  |  3  | a | b
 1 | 2 |  3  |  6  | c | d
 4 | 5 |  6  |  3  | a | b
 4 | 5 |  6  |  6  | c | d
 7 | 8 |  9  |  3  | a | b
 7 | 8 |  9  |  6  | c | d
	</programlisting>
       </para>

       <para>
	After the selection
	&sigma;<subscript>R.C=S.C</subscript>(R &times; S)
	we get:

	<programlisting>
 A | B | R.C | S.C | D | E
---+---+-----+-----+---+---
 1 | 2 |  3  |  3  | a | b
 4 | 5 |  6  |  6  | c | d
	</programlisting>
       </para>

       <para>
	To remove the duplicate column
	<classname>S</classname>.<classname>C</classname>
	we project it out by the following operation:
	&pi;<subscript>R.A,R.B,R.C,S.D,S.E</subscript>(&sigma;<subscript>R.C=S.C</subscript>(R &times; S))
	and get:

	<programlisting>
 A | B | C | D | E
---+---+---+---+---
 1 | 2 | 3 | a | b
 4 | 5 | 6 | c | d
	</programlisting>
       </para>
      </listitem>

      <listitem>
       <para>
	DIVIDE (&divide;): Let <classname>R</classname> be a table
	with the attributes A, B, C, and D and let
	<classname>S</classname> be a table with the attributes
	C and D.
	Then we define the division as:

	<programlisting>
R &divide; S = {t &mid; &forall; t<subscript>s</subscript> &isin; S &exist; t<subscript>r</subscript> &isin; R
	</programlisting>

	such that
t<subscript>r</subscript>(A,B)=t&and;t<subscript>r</subscript>(C,D)=t<subscript>s</subscript>}
	where
	t<subscript>r</subscript>(x,y)
	denotes a
	tuple of table <classname>R</classname> that consists only of
	the components <literal>x</literal> and <literal>y</literal>.
	Note that the tuple <literal>t</literal> only consists of the
	components <classname>A</classname> and
	<classname>B</classname> of relation <classname>R</classname>.
       </para>

       <para id="divide-example">
	Given the following tables

	<programlisting>
R:                    S:
 A | B | C | D         C | D
---+---+---+---       ---+---
 a | b | c | d         c | d
 a | b | e | f         e | f
 b | c | e | f
 e | d | c | d
 e | d | e | f
 a | b | d | e
	</programlisting>

	R &divide; S
	is derived as

	<programlisting>
 A | B
---+---
 a | b
 e | d
	</programlisting>
       </para>
      </listitem>
     </itemizedlist>
    </para>

    <para>
     For a more detailed description and definition of the relational
     algebra refer to [<xref linkend="ULL88" endterm="ULL88">] or
     [<xref linkend="DATE04" endterm="DATE04">].
    </para>

    <example>
     <title id="suppl-rel-alg">A Query Using Relational Algebra</title>
     <para>
      Recall that we formulated all those relational operators to be able to
      retrieve data from the database. Let's return to our example from 
      the previous
      section (<xref linkend="operations" endterm="operations">)
      where someone wanted to know the names of all
      suppliers that sell the part <literal>Screw</literal>. 
      This question can be answered
      using relational algebra by the following operation:

      <programlisting>
&pi;<subscript>SUPPLIER.SNAME</subscript>(&sigma;<subscript>PART.PNAME='Screw'</subscript>(SUPPLIER &prod; SELLS &prod; PART))
      </programlisting>
     </para>

     <para>
      We call such an operation a query. If we evaluate the above query
      against the our example tables
      (<xref linkend="supplier-fig" endterm="supplier-fig">)
      we will obtain the following result:

      <programlisting>
 SNAME
-------
 Smith
 Adams
      </programlisting>
     </para>
    </example>
   </sect2>

   <sect2 id="rel-calc">
    <title>Relational Calculus</title>

    <para>
     The relational calculus is based on the
     <firstterm>first order logic</firstterm>. There are
     two variants of the relational calculus:

     <itemizedlist>
      <listitem>
       <para>
	The <firstterm>Domain Relational Calculus</firstterm>
	(<acronym>DRC</acronym>), where variables
	stand for components (attributes) of the tuples.
       </para>
      </listitem>

      <listitem>
       <para>
	The <firstterm>Tuple Relational Calculus</firstterm>
	(<acronym>TRC</acronym>), where variables stand for tuples.
       </para>
      </listitem>
     </itemizedlist>
    </para>

    <para>
     We want to discuss the tuple relational calculus only because it is
     the one underlying the most relational languages. For a detailed
     discussion on <acronym>DRC</acronym> (and also
     <acronym>TRC</acronym>) see
     <xref linkend="DATE04" endterm="DATE04">
     or
     <xref linkend="ULL88" endterm="ULL88">.
    </para>
   </sect2>

   <sect2>
    <title>Tuple Relational Calculus</title>

    <para>
     The queries used in <acronym>TRC</acronym> are of the following
     form:

     <programlisting>
x(A) &mid; F(x)
     </programlisting>

     where <literal>x</literal> is a tuple variable
     <classname>A</classname> is a set of attributes and <literal>F</literal> is a
     formula. The resulting relation consists of all tuples
     <literal>t(A)</literal> that satisfy <literal>F(t)</literal>.
    </para>

    <para>
     If we want to answer the question from example
     <xref linkend="suppl-rel-alg" endterm="suppl-rel-alg">
     using <acronym>TRC</acronym> we formulate the following query:

     <programlisting>
{x(SNAME) &mid; x &isin; SUPPLIER &and;
    &exist; y &isin; SELLS &exist; z &isin; PART (y(SNO)=x(SNO) &and;
    z(PNO)=y(PNO) &and;
    z(PNAME)='Screw')}
     </programlisting>
    </para>

    <para>
     Evaluating the query against the tables from
     <xref linkend="supplier-fig" endterm="supplier-fig">
     again leads to the same result
     as in
     <xref linkend="suppl-rel-alg" endterm="suppl-rel-alg">.
    </para>
   </sect2>

   <sect2 id="alg-vs-calc">
    <title>Relational Algebra vs. Relational Calculus</title>

    <para>
     The relational algebra and the relational calculus have the same
     <firstterm>expressive power</firstterm>; i.e. all queries that
     can be formulated using relational algebra can also be formulated 
     using the relational calculus and vice versa.
     This was first proved by E. F. Codd in
     1972. This proof is based on an algorithm (<quote>Codd's reduction
     algorithm</quote>) by which an arbitrary expression of the relational
     calculus can be reduced to a semantically equivalent expression of
     relational algebra. For a more detailed discussion on that refer to
     <xref linkend="DATE04" endterm="DATE04">
     and
     <xref linkend="ULL88" endterm="ULL88">.
    </para>

    <para>
     It is sometimes said that languages based on the relational
     calculus are <quote>higher level</quote> or <quote>more
     declarative</quote> than languages based on relational algebra
     because the algebra (partially) specifies the order of operations
     while the calculus leaves it to a compiler or interpreter to
     determine the most efficient order of evaluation.
    </para>
   </sect2>
  </sect1>

  <sect1 id="sql-language">
   <title>The <acronym>SQL</acronym> Language</title>

   <para>
    As is the case with most modern relational languages,
    <acronym>SQL</acronym> is based on the tuple
    relational calculus. As a result every query that can be formulated
    using the tuple relational calculus (or equivalently, relational
    algebra) can also be formulated using
    <acronym>SQL</acronym>. There are, however,
    capabilities beyond the scope of relational algebra or calculus. Here
    is a list of some additional features provided by
    <acronym>SQL</acronym> that are not
    part of relational algebra or calculus:

    <itemizedlist>
     <listitem>
      <para>
       Commands for insertion, deletion or modification of data.
      </para>
     </listitem>

     <listitem>
      <para>
       Arithmetic capability: In <acronym>SQL</acronym> it is possible
       to involve
       arithmetic operations as well as comparisons, e.g.

       <programlisting>
A &lt; B + 3.
       </programlisting>

       Note
       that + or other arithmetic operators appear neither in relational
       algebra nor in relational calculus.
      </para>
     </listitem>

     <listitem>
      <para>
       Assignment and Print Commands: It is possible to print a
       relation constructed by a query and to assign a computed relation to a
       relation name.
      </para>
     </listitem>

     <listitem>
      <para>
       Aggregate Functions: Operations such as
       <firstterm>average</firstterm>, <firstterm>sum</firstterm>,
       <firstterm>max</firstterm>, etc. can be applied to columns of a 
       relation to
       obtain a single quantity.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <sect2 id="select">
    <title id="select-title">Select</title>

    <para>
     The most often used command in <acronym>SQL</acronym> is the
     <command>SELECT</command> statement,
     used to retrieve data. The syntax is:

     <synopsis>
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
    * | <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
    [ FROM <replaceable class="PARAMETER">from_item</replaceable> [, ...] ]
    [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
    [ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
    [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
    [ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]
    [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
    [ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
    [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
     </synopsis>
    </para>

    <para>
     Now we will illustrate the complex syntax of the
     <command>SELECT</command> statement with various examples. The
     tables used for the examples are defined in <xref
     linkend="supplier-fig" endterm="supplier-fig">.
    </para>

    <sect3>
     <title>Simple Selects</title>

     <para>
      Here are some simple examples using a <command>SELECT</command> statement:

      <example>
       <title id="simple-query">Simple Query with Qualification</title>
       <para>
	To retrieve all tuples from table PART where the attribute PRICE is
	greater than 10 we formulate the following query:

	<programlisting>
SELECT * FROM PART
    WHERE PRICE &gt; 10;
	</programlisting>

	and get the table:

	<programlisting>
 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25
	</programlisting>
       </para>

       <para>
	Using <quote>*</quote> in the <command>SELECT</command> statement
	will deliver all attributes from the table. If we want to retrieve
	only the attributes PNAME and PRICE from table PART we use the
	statement:

	<programlisting>
SELECT PNAME, PRICE 
    FROM PART
    WHERE PRICE &gt; 10;
	</programlisting>

	In this case the result is:

	<programlisting>
                      PNAME  |  PRICE
                     --------+--------
                      Bolt   |   15
                      Cam    |   25
	</programlisting>

	Note that the <acronym>SQL</acronym> <command>SELECT</command>
	corresponds to the <quote>projection</quote> in relational algebra
	not to the <quote>selection</quote> (see <xref linkend="rel-alg"
	endterm="rel-alg"> for more details).
       </para>

       <para>
	The qualifications in the WHERE clause can also be logically connected
	using the keywords OR, AND, and NOT:

	<programlisting>
SELECT PNAME, PRICE 
    FROM PART
    WHERE PNAME = 'Bolt' AND
         (PRICE = 0 OR PRICE &lt;= 15);
	</programlisting>

	will lead to the result:

	<programlisting>
 PNAME  |  PRICE
--------+--------
 Bolt   |   15
	</programlisting>
       </para>

       <para>
	Arithmetic operations may be used in the target list and in the WHERE
	clause. For example if we want to know how much it would cost if we
	take two pieces of a part we could use the following query:

	<programlisting>
SELECT PNAME, PRICE * 2 AS DOUBLE
    FROM PART
    WHERE PRICE * 2 &lt; 50;
	</programlisting>

	and we get:

	<programlisting>
 PNAME  |  DOUBLE
--------+---------
 Screw  |    20
 Nut    |    16
 Bolt   |    30
	</programlisting>

	Note that the word DOUBLE after the keyword AS is the new title of the
	second column. This technique can be used for every element of the
	target list to assign a new title to the resulting
	column. This new title
	is often referred to as alias. The alias cannot be used throughout the
	rest of the query.
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Joins</title>

     <para id="simple-join">
      The following example shows how <firstterm>joins</firstterm> are
      realized in <acronym>SQL</acronym>.
     </para>

     <para>
      To join the three tables SUPPLIER, PART and SELLS over their common
      attributes we formulate the following statement:

      <programlisting>
SELECT S.SNAME, P.PNAME
    FROM SUPPLIER S, PART P, SELLS SE
    WHERE S.SNO = SE.SNO AND
          P.PNO = SE.PNO;
      </programlisting>

      and get the following table as a result:

      <programlisting>
 SNAME | PNAME
-------+-------
 Smith | Screw
 Smith | Nut
 Jones | Cam
 Adams | Screw
 Adams | Bolt
 Blake | Nut
 Blake | Bolt
 Blake | Cam
      </programlisting>
     </para>

     <para>
      In the FROM clause we introduced an alias name for every relation
      because there are common named attributes (SNO and PNO) among the
      relations. Now we can distinguish between the common named attributes
      by simply prefixing the attribute name with the alias name followed by
      a dot. The join is calculated in the same way as shown in 
      <xref linkend="join-example" endterm="join-example">.
      First the Cartesian product

      SUPPLIER &times; PART &times; SELLS

      is derived. Now only those tuples satisfying the
      conditions given in the WHERE clause are selected (i.e. the common
      named attributes have to be equal). Finally we project out all
      columns but S.SNAME and P.PNAME. 
     </para>

	 <para>
	 Another way to perform joins is to use the SQL JOIN syntax as follows:
	 <programlisting>
select sname, pname from supplier
	JOIN sells USING (sno)
	JOIN part USING (pno);
	</programlisting>
	giving again:
	<programlisting>
 sname | pname
-------+-------
 Smith | Screw
 Adams | Screw
 Smith | Nut
 Blake | Nut
 Adams | Bolt
 Blake | Bolt
 Jones | Cam
 Blake | Cam
(8 rows) 
	 </programlisting>
	 </para>

	 <para>
	 A joined table, created using JOIN syntax, is a table reference list
	 item that occurs in a FROM clause and before any WHERE, GROUP BY,
	 or HAVING clause.  Other table references, including table names or
	 other JOIN clauses, may be included in the FROM clause if separated
	 by commas.  JOINed tables are logically like any other
	 table listed in the FROM clause.
	 </para>

	 <para>
	  SQL JOINs come in two main types, CROSS JOINs (unqualified joins)
	  and <firstterm>qualified JOINs</>.  Qualified joins can be further
	  subdivided based on the way in which the <firstterm>join condition</>
	  is specified (ON, USING, or NATURAL) and the way in which it is
	  applied (INNER or OUTER join).
	 </para>

    <variablelist>
        <title>Join Types</title>
        <varlistentry>
            <term>CROSS JOIN</term>
	        <listitem>
			<cmdsynopsis>
			    <arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
			    <command> CROSS JOIN </command>
			    <arg choice="req"> <replaceable class="parameter">T2</replaceable> </arg>
            </cmdsynopsis>

			<para>
			A cross join takes two tables T1 and T2 having N and M rows
			respectively, and returns a joined table containing all
			N*M possible joined rows. For each row R1 of T1, each row
			R2 of T2 is joined with R1 to yield a joined table row JR
			consisting of all fields in R1 and R2. A CROSS JOIN is
			equivalent to an INNER JOIN ON TRUE.
			</para>
			</listitem>
		</varlistentry>

		<varlistentry>
		    <term>Qualified JOINs</term>
		    <listitem>

			<cmdsynopsis>
			<arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
			<arg choice="opt"> NATURAL </arg>
			<group choice="opt">
				<arg choice="opt"> INNER </arg>
				<arg>
				<group choice="req">
					<arg choice="plain"> LEFT </arg>
					<arg choice="plain"> RIGHT </arg>
					<arg choice="plain"> FULL </arg>
				</group>
				<arg choice="opt"> OUTER </arg>
      				</arg>
     			</group>
			<command> JOIN </command>
			<arg choice="req"> <replaceable class="parameter">T2</replaceable> </arg>
			<group choice="req">
				<arg> ON <replaceable>search condition</replaceable></arg>
				<arg> USING ( <replaceable>join column list</replaceable> ) </arg>
			</group>
			</cmdsynopsis>

			<para>
			A qualified JOIN must specify its join condition
			by providing one (and only one) of NATURAL, ON, or
			USING.  The ON clause
			takes a <replaceable>search condition</replaceable>,
			which is the same as in a WHERE clause.  The USING
			clause takes a comma-separated list of column names,
			which the joined tables must have in common, and joins
			the tables on equality of those columns.  NATURAL is
			shorthand for a USING clause that lists all the common
			column names of the two tables.  A side-effect of both
			USING and NATURAL is that only one copy of each joined
			column is emitted into the result table (compare the
			relational-algebra definition of JOIN, shown earlier).
			</para>

			<!-- begin join semantics -->
			<variablelist>
			<varlistentry>
				<term>
					<cmdsynopsis>
						<arg> INNER </arg>
						<command> JOIN </command>
					</cmdsynopsis>
				</term>
				<listitem>
				<para>
				For each row R1 of T1, the joined table has a row for each row
				in T2 that satisfies the join condition with R1. 
				</para>
				<tip>
				<para>
					The words INNER and OUTER are optional for all JOINs.
					INNER is the default.  LEFT, RIGHT, and FULL imply an
					OUTER JOIN.
					</para>
				</tip>
				</listitem>
    		</varlistentry>
			<varlistentry>
				<term>
					<cmdsynopsis>
						<arg choice="plain"> LEFT </arg>
						<arg> OUTER </arg>
						<command> JOIN </command>
					</cmdsynopsis>
     			</term>
				<listitem>
				<para>
                First, an INNER JOIN is performed.
                Then, for each row in T1 that does not satisfy the join
				condition with any row in T2, an additional joined row is
				returned with null fields in the columns from T2.
				</para>
				<tip>
					<para>
					The joined table unconditionally has a row for each row in T1.
					</para>
				</tip>
				</listitem>
			</varlistentry>
			<varlistentry>
				<term>
					<cmdsynopsis>
						<arg choice="plain"> RIGHT </arg>
						<arg> OUTER </arg>
						<command> JOIN </command>
      				</cmdsynopsis>
          		</term>
				<listitem>
				<para>
                First, an INNER JOIN is performed.
                Then, for each row in T2 that does not satisfy the join
				condition with any row in T1, an additional joined row is
				returned with null fields in the columns from T1.
				</para>
				<tip>
					<para>
					The joined table unconditionally has a row for each row in T2.
					</para>
				</tip>
				</listitem>
    		</varlistentry>
			<varlistentry>
				<term>
					<cmdsynopsis>
						<arg choice="plain"> FULL </arg>
						<arg> OUTER </arg>
						<command> JOIN </command>
      				</cmdsynopsis>
          		</term>
            	<listitem>
				<para>
                First, an INNER JOIN is performed.
                Then, for each row in T1 that does not satisfy the join
				condition with any row in T2, an additional joined row is
				returned with null fields in the columns from T2.
                Also, for each row in T2 that does not satisfy the join
				condition with any row in T1, an additional joined row is
				returned with null fields in the columns from T1.
				</para>
				<tip>
					<para>
        			The joined table unconditionally has a row for every row of T1
					and a row for every row of T2.
					</para>
				</tip>
				</listitem>
    		</varlistentry>
			</variablelist>
			<!-- end join semantics -->

			</listitem>
        </varlistentry>
	 </variablelist>

	 <para>
	 JOINs of all types can be chained together or nested where either or both of
	 <replaceable class="parameter">T1</replaceable> and
	 <replaceable class="parameter">T2</replaceable> may be JOINed tables.
	 Parenthesis can be used around JOIN clauses to control the order
	 of JOINs which are otherwise processed left to right.
	 </para>

    </sect3>

    <sect3>
     <title id="aggregates-tutorial">Aggregate Functions</title>

     <para>
      <acronym>SQL</acronym> provides aggregate functions such as AVG,
      COUNT, SUM, MIN, and MAX.  The argument(s) of an aggregate function
      are evaluated at each row that satisfies the WHERE
      clause, and the aggregate function is calculated over this set
      of input values.  Normally, an aggregate delivers a single
      result for a whole <command>SELECT</command> statement.  But if
      grouping is specified in the query, then a separate calculation
      is done over the rows of each group, and an aggregate result is
      delivered per group (see next section).

      <example>
       <title id="aggregates-example">Aggregates</title>

       <para>
	If we want to know the average cost of all parts in table PART we use
	the following query:

	<programlisting>
SELECT AVG(PRICE) AS AVG_PRICE
    FROM PART;
	</programlisting>
       </para>

       <para>
	The result is:

	<programlisting>
 AVG_PRICE
-----------
   14.5
	</programlisting>
       </para>

       <para>
	If we want to know how many parts are defined in table PART we use
	the statement:

	<programlisting>
SELECT COUNT(PNO)
    FROM PART;
	</programlisting>

	and get:

	<programlisting>
 COUNT
-------
   4
	</programlisting>

       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Aggregation by Groups</title>

     <para>
      <acronym>SQL</acronym> allows one to partition the tuples of a table
      into groups. Then the
      aggregate functions described above can be applied to the groups &mdash;
      i.e. the value of the aggregate function is no longer calculated over
      all the values of the specified column but over all values of a
      group. Thus the aggregate function is evaluated separately for every
      group.
     </para>

     <para>
      The partitioning of the tuples into groups is done by using the
      keywords <command>GROUP BY</command> followed by a list of
      attributes that define the
      groups. If we have 
      <command>GROUP BY A<subscript>1</subscript>, &tdot;, A<subscript>k</subscript></command>
      we partition
      the relation into groups, such that two tuples are in the same group
      if and only if they agree on all the attributes 
      A<subscript>1</subscript>, &tdot;, A<subscript>k</subscript>.

      <example>
       <title id="aggregates-groupby">Aggregates</title>
       <para>
	If we want to know how many parts are sold by every supplier we
	formulate the query:

	<programlisting>
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME;
	</programlisting>

	and get:

	<programlisting>
 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  2  | Jones |   1
  3  | Adams |   2
  4  | Blake |   3
	</programlisting>
       </para>

       <para>
	Now let's have a look of what is happening here.
	First the join of the
	tables SUPPLIER and SELLS is derived:

	<programlisting>
 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
   1   |  Smith  |   2
   2   |  Jones  |   4
   3   |  Adams  |   1
   3   |  Adams  |   3
   4   |  Blake  |   2
   4   |  Blake  |   3
   4   |  Blake  |   4
	</programlisting>
       </para>

       <para>
	Next we partition the tuples into groups by putting all tuples
	together that agree on both attributes S.SNO and S.SNAME:

	<programlisting>
 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
                 |   2
--------------------------
   2   |  Jones  |   4
--------------------------
   3   |  Adams  |   1
                 |   3
--------------------------
   4   |  Blake  |   2
                 |   3
                 |   4
	</programlisting>
       </para>

       <para>
	In our example we got four groups and now we can apply the aggregate
	function COUNT to every group leading to the final result of the query
	given above.
       </para>
      </example>
     </para>

     <para>
      Note that for a query using GROUP BY and aggregate
      functions to make sense, the target list can only refer directly to
      the attributes being grouped by.  Other attributes may only be used
      inside the arguments of aggregate functions.  Otherwise there would
      not be a unique value to associate with the other attributes.
     </para>

     <para>
      Also observe that it makes no sense to ask for an aggregate of
      an aggregate, e.g., AVG(MAX(sno)), because a
      <command>SELECT</command> only does one pass of grouping and
      aggregation.  You can get a result of this kind by using a
      temporary table or a sub-SELECT in the FROM clause to do the
      first level of aggregation.
     </para>
    </sect3>

    <sect3>
     <title>Having</title>

     <para>
      The HAVING clause works much like the WHERE clause and is used to
      consider only those groups satisfying the qualification given in the
      HAVING clause.  Essentially, WHERE filters out unwanted input rows
      before grouping and aggregation are done, whereas HAVING filters out
      unwanted group rows post-GROUP.  Therefore, WHERE cannot refer to the
      results of aggregate functions.  On the other hand, there's no point
      in writing a HAVING condition that doesn't involve an aggregate
      function!  If your condition doesn't involve aggregates, you might
      as well write it in WHERE, and thereby avoid the computation of
      aggregates for groups that you're just going to throw away anyway.

      <example>
       <title id="having-example">Having</title>

       <para>
	If we want only those suppliers selling more than one part we use the
	query:

	<programlisting>
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME
    HAVING COUNT(SE.PNO) &gt; 1;
	</programlisting>

	and get:

	<programlisting>
 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  3  | Adams |   2
  4  | Blake |   3
	</programlisting>
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Subqueries</title>

     <para>
      In the WHERE and HAVING clauses the use of subqueries (subselects) is
      allowed in every place where a value is expected. In this case the
      value must be derived by evaluating the subquery first. The usage of
      subqueries extends the expressive power of
      <acronym>SQL</acronym>.

      <example>
       <title id="subselect-example">Subselect</title>

       <para>
	If we want to know all parts having a greater price than the part
	named 'Screw' we use the query:

	<programlisting>
SELECT * 
    FROM PART 
    WHERE PRICE &gt; (SELECT PRICE FROM PART
                   WHERE PNAME='Screw');
	</programlisting>
       </para>

       <para>
	The result is:

	<programlisting>
 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25
	</programlisting>
       </para>

       <para>
	When we look at the above query we can see the keyword
	<command>SELECT</command> two times. The first one at the
	beginning of the query - we will refer to it as outer
	<command>SELECT</command> - and the one in the WHERE clause which
	begins a nested query - we will refer to it as inner
	<command>SELECT</command>. For every tuple of the outer
	<command>SELECT</command> the inner <command>SELECT</command> has
	to be evaluated. After every evaluation we know the price of the
	tuple named 'Screw' and we can check if the price of the actual
	tuple is greater.  (Actually, in this example the inner query need
	only be evaluated once, since it does not depend on the state of
	the outer query.)
       </para>

       <para>
	If we want to know all suppliers that do not sell any part 
	(e.g. to be able to remove these suppliers from the database) we use:

	<programlisting>
SELECT * 
    FROM SUPPLIER S
    WHERE NOT EXISTS
        (SELECT * FROM SELLS SE
         WHERE SE.SNO = S.SNO);
	</programlisting>
       </para>

       <para>
	In our example the result will be empty because every supplier
	sells at least one part. Note that we use S.SNO from the outer
	<command>SELECT</command> within the WHERE clause of the inner
	<command>SELECT</command>. Here the subquery must be evaluated
	afresh for each tuple from the outer query, i.e. the value for
	S.SNO is always taken from the current tuple of the outer
	<command>SELECT</command>.
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Subqueries in FROM</title>

     <para>
      A somewhat different way of using subqueries is to put them in the
      FROM clause.  This is a useful feature because a subquery of this
      kind can output multiple columns and rows, whereas a subquery used
      in an expression must deliver just a single result.  It also lets
      us get more than one round of grouping/aggregation without resorting
      to a temporary table.

      <example>
       <title id="subselect-in-from-example">Subselect in FROM</title>

       <para>
        If we want to know the highest average part price among all our
	suppliers, we can't write MAX(AVG(PRICE)), but we can write:

	<programlisting>
SELECT MAX(subtable.avgprice)
    FROM (SELECT AVG(P.PRICE) AS avgprice
          FROM SUPPLIER S, PART P, SELLS SE
          WHERE S.SNO = SE.SNO AND
                P.PNO = SE.PNO
          GROUP BY S.SNO) subtable;
	</programlisting>

	The subquery returns one row per supplier (because of its GROUP BY)
	and then we aggregate over those rows in the outer query.
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Union, Intersect, Except</title>

     <para>
      These operations calculate the union, intersection and set theoretic
      difference of the tuples derived by two subqueries.

      <example>
       <title id="union-example">Union, Intersect, Except</title>

       <para>
	The following query is an example for UNION:

	<programlisting>
SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Jones'
UNION
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Adams';    
	</programlisting>

gives the result:

	<programlisting>
 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna
	</programlisting>
       </para>

       <para>
	Here is an example for INTERSECT:

	<programlisting>
SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO &gt; 1
INTERSECT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO &lt; 3;
	</programlisting>

	gives the result:

	<programlisting>
 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
	</programlisting>

	The only tuple returned by both parts of the query is the one having SNO=2.
       </para>

       <para>
	Finally an example for EXCEPT:

	<programlisting>
SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO &gt; 1
EXCEPT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO &gt; 3;
	</programlisting>

	gives the result:

	<programlisting>
 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna
	</programlisting>
       </para>
      </example>
     </para>
    </sect3>
   </sect2>

   <sect2 id="datadef">
    <title>Data Definition</title>

    <para>
     There is a set of commands used for data definition included in the
     <acronym>SQL</acronym> language. 
    </para>

    <sect3 id="create">
     <title id="create-title">Create Table</title>

     <para>
      The most fundamental command for data definition is the
      one that creates a new relation (a new table). The syntax of the
      <command>CREATE TABLE</command> command is:

      <synopsis>
CREATE TABLE <replaceable class="parameter">table_name</replaceable>
    (<replaceable class="parameter">name_of_attr_1</replaceable> <replaceable class="parameter">type_of_attr_1</replaceable>
     [, <replaceable class="parameter">name_of_attr_2</replaceable> <replaceable class="parameter">type_of_attr_2</replaceable> 
     [, ...]]);
      </synopsis>

      <example>
       <title id="table-create">Table Creation</title>

       <para>
	To create the tables defined in
	<xref linkend="supplier-fig" endterm="supplier-fig"> the
	following <acronym>SQL</acronym> statements are used:

	<programlisting>
CREATE TABLE SUPPLIER
    (SNO   INTEGER,
     SNAME VARCHAR(20),
     CITY  VARCHAR(20));
     </programlisting>

     <programlisting>
CREATE TABLE PART
    (PNO   INTEGER,
     PNAME VARCHAR(20),
     PRICE DECIMAL(4 , 2));
     </programlisting>

     <programlisting>
CREATE TABLE SELLS
    (SNO INTEGER,
     PNO INTEGER);
	</programlisting>
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Data Types in <acronym>SQL</acronym></title>

     <para>
      The following is a list of some data types that are supported by 
      <acronym>SQL</acronym>:

      <itemizedlist>
       <listitem>
	<para>
	 INTEGER: signed fullword binary integer (31 bits precision).
	</para>
       </listitem>

       <listitem>
	<para>
	 SMALLINT: signed halfword binary integer (15 bits precision).
	</para>
       </listitem>

       <listitem>
	<para>
	 DECIMAL (<replaceable class="parameter">p</replaceable>[,<replaceable class="parameter">q</replaceable>]):
	 signed packed decimal number of up to
	 <replaceable class="parameter">p</replaceable>
	 digits, with
	 <replaceable class="parameter">q</replaceable>
	 digits to the right of the decimal point.
	 If <replaceable class="parameter">q</replaceable>
	 is omitted it is assumed to be 0.
	</para>
       </listitem>

       <listitem>
	<para>
	 FLOAT: signed doubleword floating point number.
	</para>
       </listitem>

       <listitem>
	<para>
	 VARCHAR(<replaceable class="parameter">n</replaceable>):
	 varying length character string of maximum length
	 <replaceable class="parameter">n</replaceable>.
	</para>
       </listitem>

       <listitem>
	<para>
	 CHAR(<replaceable class="parameter">n</replaceable>):
	 fixed length character string of length
	 <replaceable class="parameter">n</replaceable>.
	</para>
       </listitem>

      </itemizedlist>
     </para>
    </sect3>

    <sect3>
     <title>Create Index</title>

     <para>
      Indexes are used to speed up access to a relation. If a relation <classname>R</classname>
      has an index on attribute <classname>A</classname> then we can
      retrieve all tuples <replaceable>t</replaceable>
      having
      <replaceable>t</replaceable>(<classname>A</classname>) = <replaceable>a</replaceable>
      in time roughly proportional to the number of such
      tuples <replaceable>t</replaceable>
      rather than in time proportional to the size of <classname>R</classname>.
     </para>

     <para>
      To create an index in <acronym>SQL</acronym>
      the <command>CREATE INDEX</command> command is used. The syntax is:

      <programlisting>
CREATE INDEX <replaceable class="parameter">index_name</replaceable> 
    ON <replaceable class="parameter">table_name</replaceable> ( <replaceable class="parameter">name_of_attribute</replaceable> );
      </programlisting>
     </para>

     <para>
      <example>
       <title id="index-create">Create Index</title>

       <para>
	To create an index named I on attribute SNAME of relation SUPPLIER
	we use the following statement:

      <programlisting>
CREATE INDEX I ON SUPPLIER (SNAME);
      </programlisting>
     </para>

       <para>
	The created index is maintained automatically, i.e. whenever a new
	tuple is inserted into the relation SUPPLIER the index I is
	adapted. Note that the only changes a user can perceive when an
	index is present are increased speed for <command>SELECT</command>
	and decreases in speed of updates.
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Create View</title>

     <para>
      A view may be regarded as a <firstterm>virtual table</firstterm>,
      i.e. a table that
      does not <emphasis>physically</emphasis> exist in the database
      but looks to the user
      as if it does. By contrast, when we talk of a
      <firstterm>base table</firstterm> there is
      really a physically stored counterpart of each row of the table
      somewhere in the physical storage.
     </para>

     <para>
      Views do not have their own, physically separate, distinguishable
      stored data. Instead, the system stores the definition of the
      view (i.e. the rules about how to access physically stored base
      tables in order to materialize the view) somewhere in the system
      catalogs (see
      <xref linkend="tutorial-catalogs-title" endterm="tutorial-catalogs-title">). For a
      discussion on different techniques to implement views refer to
<!--
      section
      <xref linkend="view-impl" endterm="view-impl">.
-->
      <citetitle>SIM98</citetitle>.
     </para>

     <para>
      In <acronym>SQL</acronym> the <command>CREATE VIEW</command>
      command is used to define a view. The syntax
      is:

      <programlisting>
CREATE VIEW <replaceable class="parameter">view_name</replaceable>
    AS <replaceable class="parameter">select_stmt</replaceable>
      </programlisting>

      where <replaceable class="parameter">select_stmt</replaceable> 
      is a valid select statement as defined
      in <xref linkend="select-title" endterm="select-title">.
      Note that <replaceable class="parameter">select_stmt</replaceable> is
      not executed when the view is created. It is just stored in the 
      <firstterm>system catalogs</firstterm>
      and is executed whenever a query against the view is made.
     </para>

     <para>
      Let the following view definition be given (we use
      the tables from
      <xref linkend="supplier-fig" endterm="supplier-fig"> again):

      <programlisting>
CREATE VIEW London_Suppliers
    AS SELECT S.SNAME, P.PNAME
        FROM SUPPLIER S, PART P, SELLS SE
        WHERE S.SNO = SE.SNO AND
              P.PNO = SE.PNO AND
              S.CITY = 'London';
      </programlisting>
     </para>

     <para>
      Now we can use this <firstterm>virtual relation</firstterm>
      <classname>London_Suppliers</classname> as
      if it were another base table:

      <programlisting>
SELECT * FROM London_Suppliers
    WHERE PNAME = 'Screw';
      </programlisting>

      which will return the following table:

      <programlisting>
 SNAME | PNAME
-------+-------
 Smith | Screw                 
      </programlisting>
     </para>

     <para>
      To calculate this result the database system has to do a
      <emphasis>hidden</emphasis>
      access to the base tables SUPPLIER, SELLS and PART first. It
      does so by executing the query given in the view definition against
      those base tables. After that the additional qualifications
      (given in the
      query against the view) can be applied to obtain the resulting
      table.
     </para>
    </sect3>

    <sect3>
     <title>Drop Table, Drop Index, Drop View</title>

     <para>
      To destroy a table (including all tuples stored in that table) the
      <command>DROP TABLE</command> command is used:

      <programlisting>
DROP TABLE <replaceable class="parameter">table_name</replaceable>;
       </programlisting>
      </para>

     <para>
      To destroy the SUPPLIER table use the following statement:

      <programlisting>
DROP TABLE SUPPLIER;
      </programlisting>
     </para>

     <para>
      The <command>DROP INDEX</command> command is used to destroy an index:

      <programlisting>
DROP INDEX <replaceable class="parameter">index_name</replaceable>;
      </programlisting>
     </para>

     <para>
      Finally to destroy a given view use the command <command>DROP
      VIEW</command>:

      <programlisting>
DROP VIEW <replaceable class="parameter">view_name</replaceable>;
      </programlisting>
     </para>
    </sect3>
   </sect2>

   <sect2>
    <title>Data Manipulation</title>

    <sect3>
     <title>Insert Into</title>

     <para>
      Once a table is created (see
      <xref linkend="create-title" endterm="create-title">), it can be filled
      with tuples using the command <command>INSERT INTO</command>.
      The syntax is:

      <programlisting>
INSERT INTO <replaceable class="parameter">table_name</replaceable> (<replaceable class="parameter">name_of_attr_1</replaceable> 
    [, <replaceable class="parameter">name_of_attr_2</replaceable> [,...]])
    VALUES (<replaceable class="parameter">val_attr_1</replaceable> [, <replaceable class="parameter">val_attr_2</replaceable> [, ...]]);
      </programlisting>
     </para>

     <para>
      To insert the first tuple into the relation SUPPLIER (from
      <xref linkend="supplier-fig" endterm="supplier-fig">) we use the
      following statement:

      <programlisting>
INSERT INTO SUPPLIER (SNO, SNAME, CITY)
    VALUES (1, 'Smith', 'London');
      </programlisting>
     </para>

     <para>
      To insert the first tuple into the relation SELLS we use:

      <programlisting>
INSERT INTO SELLS (SNO, PNO)
    VALUES (1, 1);
      </programlisting>
     </para>
    </sect3>

    <sect3>
     <title>Update</title>

     <para>
      To change one or more attribute values of tuples in a relation the
      <command>UPDATE</command> command is used. The syntax is:

      <programlisting>
UPDATE <replaceable class="parameter">table_name</replaceable>
    SET <replaceable class="parameter">name_of_attr_1</replaceable> = <replaceable class="parameter">value_1</replaceable> 
        [, ... [, <replaceable class="parameter">name_of_attr_k</replaceable> = <replaceable class="parameter">value_k</replaceable>]]
    WHERE <replaceable class="parameter">condition</replaceable>;
      </programlisting>
     </para>

     <para>
      To change the value of attribute PRICE of the part 'Screw' in the
      relation PART we use:

      <programlisting>
UPDATE PART
    SET PRICE = 15
    WHERE PNAME = 'Screw';
      </programlisting>
     </para>

     <para>
      The new value of attribute PRICE of the tuple whose name is 'Screw' is
      now 15.
     </para>
    </sect3>

    <sect3>
     <title>Delete</title>

     <para>
      To delete a tuple from a particular table use the command DELETE
      FROM. The syntax is:

      <programlisting>
DELETE FROM <replaceable class="parameter">table_name</replaceable>
    WHERE <replaceable class="parameter">condition</replaceable>;
      </programlisting>
     </para>

     <para>
      To delete the supplier called 'Smith' of the table SUPPLIER the
      following statement is used:

      <programlisting>
DELETE FROM SUPPLIER
    WHERE SNAME = 'Smith';
      </programlisting>
     </para>
    </sect3>
   </sect2>

   <sect2 id="tutorial-catalogs">
    <title id="tutorial-catalogs-title">System Catalogs</title>

    <para>
     In every <acronym>SQL</acronym> database system
     <firstterm>system catalogs</firstterm> are used to keep
     track of which tables, views indexes etc. are defined in the
     database. These system catalogs can be queried as if they were normal
     relations. For example there is one catalog used for the definition of
     views. This catalog stores the query from the view definition. Whenever
     a query against a view is made, the system first gets the
     <firstterm>view definition query</firstterm> out of the catalog
     and materializes the view
     before proceeding with the user query (see
<!--
      section
      <xref linkend="view-impl" endterm="view-impl">.
    <citetitle>SIM98</citetitle>
-->
     <xref linkend="SIM98" endterm="SIM98">
     for a more detailed
     description). For more information about system catalogs refer to
     <xref linkend="DATE04" endterm="DATE04">.
    </para>
   </sect2>

   <sect2>
    <title>Embedded <acronym>SQL</acronym></title>

    <para>
     In this section we will sketch how <acronym>SQL</acronym> can be
     embedded into a host language (e.g. <literal>C</literal>).
     There are two main reasons why we want to use <acronym>SQL</acronym>
     from a host language:

     <itemizedlist>
      <listitem>
       <para>
	There are queries that cannot be formulated using pure <acronym>SQL</acronym>
	(i.e. recursive queries). To be able to perform such queries we need a
	host language with a greater expressive power than
	<acronym>SQL</acronym>.
       </para>
      </listitem>

      <listitem>
       <para>
	We simply want to access a database from some application that
	is written in the host language (e.g. a ticket reservation system
	with a graphical user interface is written in C and the information
	about which tickets are still left is stored in a database that can be
	accessed using embedded <acronym>SQL</acronym>).
       </para>
      </listitem>
     </itemizedlist>
    </para>

    <para>
     A program using embedded <acronym>SQL</acronym>
     in a host language consists of statements
     of the host language and of
     <firstterm>embedded <acronym>SQL</acronym></firstterm>
     (<acronym>ESQL</acronym>) statements. Every <acronym>ESQL</acronym>
     statement begins with the keywords <command>EXEC SQL</command>.
     The <acronym>ESQL</acronym> statements are
     transformed to statements of the host language
     by a <firstterm>precompiler</firstterm>
     (which usually inserts
     calls to library routines that perform the various <acronym>SQL</acronym>
     commands). 
    </para>

    <para>
     When we look at the examples throughout
     <xref linkend="select-title" endterm="select-title"> we
     realize that the result of the queries is very often a set of
     tuples. Most host languages are not designed to operate on sets so we
     need a mechanism to access every single tuple of the set of tuples
     returned by a SELECT statement. This mechanism can be provided by
     declaring a <firstterm>cursor</firstterm>.
     After that we can use the <command>FETCH</command> command to
     retrieve a tuple and set the cursor to the next tuple.
    </para>

    <para>
     For a detailed discussion on embedded <acronym>SQL</acronym>
     refer to
     <xref linkend="DATE97" endterm="DATE97">,
     <xref linkend="DATE04" endterm="DATE04">,
     or
     <xref linkend="ULL88" endterm="ULL88">.
    </para>
   </sect2>
  </sect1>
 </chapter>