# Structured Query Language/Print version

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/Structured_Query_Language

# About the Book
## It's a Translation and a Guide

This Wikibook introduces the programming language SQL as defined by ISO/IEC. The standard is — similar to most standard publications — fairly technical and neither easy to read nor understandable. Therefore there is a demand for a text document explaining the key features of the language. That is what this wikibook strives to do: we want to present an easily readable and understandable introduction for everyone interested in the topic.
Manuals and white papers from database vendors are mainly focused on the technical aspects of their product. As they want to set themselves apart from each other, they tend to emphasize those aspects which go beyond the SQL standard and the products of other vendors. This is contrary to the Wikibook's approach: we want to emphasize the common aspects.
The main audience of this Wikibook are people who want to learn the language, either as beginners or as persons with existing knowledge and some degree of experience looking for a recapitulation.

## What this Wikibook is not

First of all, the Wikibook is not a reference manual for the syntax of standard SQL or any of its implementations. Reference manuals usually consist of definitions and explanations for those definitions. By contrast, the Wikibook tries to present concepts and basic commands through textual descriptions and examples. Of course, some syntax will be demonstrated. On some pages, there are additional hints about small differences between the standard and particular implementations.
The Wikibook is also not a complete tutorial. First, its focus is the standard and not any concrete implementation. When learning a computer language it is necessary to work with it and experience it personally. Hence, a concrete implementation is needed. And most of them differ more or less from the standard. Second, the Wikibook is far away from reflecting the complete standard, e.g. the central part of the standard consists of about 18 MB text in more than 1,400 pages. But you can use the Wikibook as a companion for learning SQL.

## How to proceed

For everyone new to SQL, it will be necessary to study the chapters and pages from beginning to end. For persons who have some experience with SQL or are interested in a specific aspect, it is possible to navigate directly to any page.
Knowledge about any other computer language is not necessary, but it will be helpful.
The Wikibook consists of descriptions, definitions, and examples. It should be read with care. Furthermore, it is absolutely necessary to do some experiments with data and data structures personally. Hence, access to a concrete database system, where you can do read-only and read-write tests, is necessary. For those tests, you can use our example database or individually defined tables and data.

## Conventions

The elements of the language SQL are case-insensitive, e.g., it makes no difference whether you write SELECT ..., Select ..., select ... or any combination of upper and lower case characters like SeLecT. For readability reasons, the Wikibook uses the convention that all language keywords are written in upper case letters and all names of user objects e.g., table and column names, are written in lower case letters.
We will write short SQL commands within one row.
For longer commands spawning multiple lines we use a tabular format.

# Database Management Systems (DBMS)
### Historical Context

One of the original scopes of computer applications was storing large amounts of data on mass storage devices and retrieving them at a later point in time. Over time user requirements increased to include not only sequential access but also random access to data records, concurrent access by parallel (writing) processes, recovery after hardware and software failures, high performance, scalability, etc. In the 1970s and 1980s, the science and computer industries developed techniques to fulfill those requests.

### What makes up a Database Management System?

Basic bricks for efficient data storage - and for this reason for all Database Management Systems (DBMS) - are implementations of fast read and write access algorithms to data located in central memory and mass storage devices like routines for B-trees, Index Sequential Access Method (ISAM), other indexing techniques as well as buffering of dirty and non-dirty blocks. These algorithms are not unique to DBMS. They also apply to file systems, some programming languages, operating systems, application servers, and much more.
In addition to the appropriation of these routines, a DBMS guarantees compliance with the ACID paradigm. This compliance means that in a multi-user environment all changes to data within one transaction are:
    Atomic: all changes take place or none.
    Consistent: changes transform the database from one valid state to another valid state.
    Isolated: transactions of different users working at the same time will not affect each other.
    Durable: the database retains committed changes even if the system crashes afterward.

### Classification of DBMS Design

A distinction between the following generations of DBMS design and implementation can be made:
* Hierarchical DBMS: Data structures are designed in a hierarchical parent/child model where every child has exactly one parent (except the root structure, which has no parent). The result is that the data is modeled and stored as a tree. Child rows are physically stored directly after the owning parent row. So there is no need to store the parent's ID or something like it within the child row (XML realizes a similar approach). If an application processes data in exactly this hierarchical way, it is speedy and efficient. But if it's necessary to process data in a sequence that deviates from this order, access is less efficient. Furthermore, hierarchical DBMSs do not provide the modeling of n:m relations. Another fault is that there is no possibility to navigate directly to data stored in lower levels. You must first navigate over the given hierarchy before reaching that data.
    The best-known hierarchical DBMS is IMS from IBM.
* Network DBMS: The network model designs data structures as a complex network with links from one or more parent nodes to one or more child nodes. Even cycles are possible. There is no need for a single root node. In general, the terms parent node and child node lose their hierarchical meaning and may be referred to as link source and link destination. Since those links are realized as physical links within the database, applications that follow the links show good performance.
* Relational DBMS: The relational model designs data structures as relations (tables) with attributes (columns) and the relationship between those relations. Definitions in this model are expressed in a purely declarative way, not predetermining any implementation issues like links from one relation to another or a certain sequence of rows in the database. Relationships are based purely upon content. At runtime, all linking and joining is done by evaluating the actual data values, e.g.: ... WHERE employee.department_id = department.id .... The consequence is that - except for explicit foreign keys - there is no meaning of a parent/child or owner/member denotation. Relationships in this model do not have any direction.
    The relational model and SQL are based on the mathematical theory of relational algebra.
    During the 1980s and 1990s, proprietary and open-source DBMS's based on the relational design paradigm established themselves as market leaders.
* Object-oriented DBMS: Nowadays, most applications are written in an object-oriented programming language (OOP). If, in such cases, the underlying DBMS belongs to the class of relational DBMS, the so-called object-relational impedance mismatch arises. That is to say, in contrast to the application language, pure relational DBMS (prDBMS) does not support central concepts of OOP:
       Type system: OOPs do not only know primitive data types. As a central concept of their language, they offer the facility to define classes with complex internal structures. The classes are built on primitive types, system classes, references to other or the same class. prDBMS knows only predefined types. Secondary prDBMS insists in first normal form, which means that attributes must be scalar. In OOPs they may be sets, lists or arrays of the desired type.
       Inheritance: Classes of OOPs may inherit attributes and methods from their superclass. This concept is not known to prDBMS.
       Polymorphism: The runtime system can decide via late binding which one of a group of methods with the same name and parameter types will be called. This concept is not known by prDBMS.
       Encapsulation: Data and access methods to data are stored within the same class. It is not possible to access the data directly - the only way is using the access methods of the class. This concept is not known to prDBMS.
    Object-oriented DBMS are designed to overcome the gap between prDBMS and OOP. At their peak, they reached a weak market position in the mid and late 1990s. Afterward, some of their concepts were incorporated into the SQL standard as well as rDBMS implementations.
* NoSQL: The term NoSQL stands for the emerging group of DBMS which differs from others in central concepts:
** They do not necessarily support all aspects of the ACID paradigm.
** The data must not necessarily be structured according to any schema.
** Their goal is to support fault-tolerance, distributed data, and vast volume, see also: CAP theorem.
** Implementations differ widely in storing techniques: you can see key-value stores, document-oriented databases, graph-oriented databases, and more.
** They do not offer an SQL interface.
** Some products: MongoDB, Firebase Realtime Database (Google), Cloud Firestore (Google)
* NewSQL: This class of DBMS seeks to provide the same scalable performance as NoSQL systems while maintaining the ACID paradigm, the relational model, and the SQL interface. They try to reach scalability by eschewing heavyweight recovery or concurrency control.

# Relational DBMS (rDBMS)
## The Theory

A relational DBMS is an implementation of data stores according to the design rules of the relational model. This approach allows operations on the data according to the relational algebra like projections, selections, joins, set operations (union, difference, intersection, ...), and more. Together with Boolean algebra (and, or, not, exists, ...) and other mathematical concepts, relational algebra builds up a complete mathematical system with basic operations, complex operations, and transformation rules between the operations. Neither a DBA nor an application programmer needs to know the relational algebra. But it is helpful to know that your rDBMS is based on this mathematical foundation - and that it has the freedom to transform queries into several forms.

## The Data Model

The relational model designs data structures as relations (tables) with attributes (columns) and the relationship between those relations. The information about one entity of the real world is stored within one row of a table. However, the term one entity of the real world must be used with care. It may be that our intellect identifies a machine like a single airplane in this vein. Depending on the information requirements, it may be sufficient to put all of the information into one row of a table airplane. But in many cases, it is necessary to break the entity into its pieces and model the pieces as discrete entities, including the relationship to the whole thing. If, for example, information about every single seat within the airplane is needed, a second table seat and some way of joining seats to airplanes will be required.
This way of breaking up information about real entities into a complex data model depends highly on the information requirements of the business concept. Additionally, there are some formal requirements independent of any application: the resulting data model should conform to a so-called normal form. Normally these data models consist of a great number of tables and relationships between them. Such models will not predetermine their use by applications; they are strictly descriptive and will not restrict access to the data in any way.

## Some more Basics

Operations within databases must have the ability to act not only on single rows, but also on sets of rows. Relational algebra offers this possibility. Therefore languages based on relational algebra, e.g.: SQL, offer a powerful syntax to manipulate lots of data within one single command.
As operations within relational algebra may be replaced by different but logically equivalent operations, a language based on relational algebra should not predetermine how its syntax is mapped to operations (the execution plan). The language should describe what should be done and not how to do it. Note: This choice of operations does not concern the use or neglect of indices.
As described before the relational model tends to break up objects into sub-objects. In this and in other cases it is often necessary to collect associated information from a bunch of tables into one information unit. How is this possible without links between participating tables and rows? The answer is: All joining is done based on the values which are actually stored in the attributes. The rDBMS must make its own decisions about how to reach all concerned rows: whether to read all potentially affected rows and ignore those which are irrelevant (full table scan) or, to use some kind of index and read-only those which match the criteria. This value-based approach allows even the use of operators other than the equal-operator, e.g.:
This command will join all "gift" records to all "box" records with a larger "extent" (whatever "extent" means).

# SQL: A Language for Working with rDBMS
## History

As outlined above, rDBMS acts on the data with operations of relational algebra like projections, selections, joins, set operations (union, except and intersect) and more. The operations of relational algebra are denoted in a mathematical language that is highly formal and hard to understand for end-users and - possibly also - for many software engineers. Therefore, rDBMS offers a layer above relational algebra that is easy to understand but can be mapped to the underlying relational operations. Since the 1970s, we have seen some languages doing this job; one of them was SQL - another example was QUEL. In the early 1980s (after a rename from its original name SEQUEL due to trademark problems), SQL achieved market dominance. And in 1986, SQL was standardized for the first time. The current version is SQL 2023.

## Characteristics

The tokens and syntax of SQL are modeled on English common speech to keep the access barrier as small as possible. An SQL command like UPDATE employee SET salary = 2000 WHERE id = 511; is not far away from the sentence "Change employee's salary to 2000 for the employee with id 511."
The keywords of SQL can be expressed in any combination of upper and lower case characters, i.e. the keywords are case insensitive. It makes no difference whether UPDATE, update, Update, UpDate, or any other combination of upper and lower case characters is written in SQL code.
Next, SQL is a descriptive language, not a procedural one. It does not proscribe all aspects of the relational operations (which operation, their order, ...), which are generated from the given SQL statement. The rDBMS has the freedom to generate more than one execution plan from a statement. It may compare several generated execution plans with each other and run the one it thinks is best in a given situation. Additionally, the programmer is freed from considering all the details of data access, e.g.: Which one of a set of WHERE criteria should be evaluated first if they are combined with AND?
Despite the above simplifications, SQL is very powerful. It allows the manipulation of a set of data records with a single statement. UPDATE employee SET salary = salary * 1.1 WHERE salary < 2000; will affect all employee records with an actual salary smaller than 2000. Potentially, there may be thousands of those records, only a few or even zero. The operation may also depend on data already present in the database; the statement SET salary = salary * 1.1 leads to an increase of the salaries by 10%, which may be 120 for one employee and 500 for another one.
The designer of SQL tried to define the language elements orthogonally to each other. Among other things, this refers to the fact that any language element may be used in all positions of a statement where the result of that element may be used directly. E.g.: If you have a function power, which takes two numbers and returns another number, you can use this function in all positions where numbers are allowed. The following statements are syntactically correct (if you have defined the function power ) - and lead to the same resulting rows.
Another example of orthogonality is the use of subqueries within UPDATE, INSERT, DELETE, or inside another SELECT statement.
However, SQL is not free of redundancy. Often there are several possible formulations to express the same situation.
This is a very simple example. In complex statements, there may be the choice between joins, subqueries, and the exists predicate.

## Fundamentals

Core SQL consists of statements. Statements consist of keywords, operators, values, names of system- and user-objects or functions. Statements are concluded by a semicolon. In the statement SELECT salary FROM employee WHERE id < 100; the tokens SELECT, FROM and WHERE are keywords. salary, employee, and id are object names, the "<" sign is an operator, and "100" is a value.
The SQL standard arranges statements into nine groups:
    "The main classes of SQL-statements are:
       SQL-schema statements; these may have a persistent effect on the set of schemas.
       SQL-data statements; some of these, the SQL-data change statements, may have a persistent effect on SQL data.
       SQL-transaction statements; except for the <commit statement>, these, and the following classes, have no effects that persist when an SQL-session is terminated.
       SQL-control statements.
       SQL-connection statements.
       SQL-session statements.
       SQL-diagnostics statements.
       SQL-dynamic statements.
       SQL embedded exception declaration."
This detailed grouping is unusual in everyday speech. A typical alternative is to organize SQL statements into the following groups:
    Data Definition Language (DDL): Managing the structure of database objects (CREATE/ALTER/DROP tables, views, columns, ...).
    Data Query Language (DQL): Retrieval of data with the statement SELECT. This group has only one statement.
    Data Manipulation Language (DML): Changing of data with the statements INSERT, UPDATE, MERGE, DELETE, COMMIT, ROLLBACK, and SAVEPOINT.
    Data Control Language (DCL): Managing access rights (GRANT, REVOKE).

## Turing completeness

Core SQL, as described above, is not Turing complete. It misses conditional branches, variables, subroutines. But the standard, as well as most implementations, offers an extension to fulfill the demand for Turing completeness. In 'Part 4: Persistent Stored Modules (SQL/PSM)' of the standard, there are definitions for IF-, CASE-, LOOP-, assignment- and other statements. The existing implementations of this part have different names, different syntax, and also a different scope of operation: PL/SQL in Oracle, SQL/PL in DB2, Transact-SQL, or T-SQL in SQL Server and Sybase, PL/pgSQL in Postgres and simply 'stored procedures' in MySQL.

# SQL: The Standard ISO IEC 9075 and various Implementations
## Benefit of Standardization

Like most other standards, the primary purpose of SQL is portability. Usually, software designers and application developers structure and solve problems in layers. Every abstraction level is realized in its own component or sub-component: presentation to end-user, business logic, data access, data storage, net, and operation system demands are typical representatives of such components. They are organized as a stack and every layer offers an interface to the upper layers to use its functionality. If one of those components is realized by two different providers and both offer the same interface (as an API, Web-Service, language specification, ...), it is possible to exchange them without changing the layers which are based on them. In essence, the software industry needs stable interfaces at the top of essential layers to avoid dependence on a single provider. SQL acts as such an interface to relational database systems.
If an application uses only those SQL commands which are defined within standard SQL, it should be possible to exchange the underlying rDBMS with a different one without changing the source code of the application. In practice, this is a hard job, because concrete implementations offer numerous additional features and software engineers love to use them.
A second aspect is the conservation of know-how. If a student learns SQL, he is in a position to develop applications that are based on an arbitrary database system. The situation is comparable with any other popular programming language. If one learns Java or C-Sharp, he can develop applications of any kind running on a lot of different hardware systems and even different hardware architectures.

## Limits

Database systems consist of many components. Access to the data is an essential element but not the only component. Other components include: throughput optimization, physical design, backup, distributed databases, replication, 7x24 availability, ... . Standard SQL is focused mainly on data access and ignores typical DBA tasks. Even the CREATE INDEX statement as a widely used optimization strategy is not part of the standard. Nevertheless, the standard fills thousands of pages. But most of the DBA's daily work is highly specialized to every concrete implementation and must be done differently when switching to a different rDBMS. Mainly application developers benefit from SQL.

## The Standardization Process

The standardization process is organized in two levels. The first level acts in a national context. Interested companies, universities and persons of one country work within their national standardization organization like ANSI, Deutsches Institut für Normung (DIN) or British Standards Institution (BSI), where every member has one vote. The second level is the international stage. The national organizations are members of ISO, respectively IEC. In case of SQL there is a common committee of ISO and IEC named Joint Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange, where every national body has one vote. This committee approves the standard under the name ISO/IEC 9075-n:yyyy, where n is the part number and yyyy is the year of publication. The ten parts of the standard are described in short here.
If the committee releases a new version, this may concern only some of the ten parts. So it is possible that the yyyy denomination differs from part to part. Core SQL is defined mainly by the second part: ISO/IEC 9075-2:yyyy Part 2: Foundation (SQL/Foundation) - but it also contains some features of other parts.
Note: The API JDBC is part of Java SE and Java EE but not part of the SQL standard.
A second, closely related standard complements the standard: ISO/IEC 13249-n:yyyy SQL Multimedia and Application Packages, which is developed by the same organizations and committee. This publication defines interfaces and packages based on SQL. They focus on particular kinds of applications: text, pictures, data mining, and spatial data applications.

## Verification of Conformance to the Standard

Until 1996 the National Institute of Standards and Technology (NIST) certified the compliance of the SQL implementation of rDBMS with the SQL standard. As NIST abandon this work, nowadays, vendors self-certify the compliance of their product. They must declare the degree of conformance in a special appendix of their documentation. This documentation may be voluminous as the standard defines not only a set of base features - called Core SQL:yyyy - but also a lot of additional features an implementation may conform to or not.

## Implementations

To fulfill their clients' demands, all major vendors of rDBMS offers - among other data access ways - the language SQL within their product. The implementations cover Core SQL, a bunch of additional standardized features, and a huge number of additional, not standardized features. The access to standardized features may use the regular syntax or an implementation-specific syntax. In essence, SQL is the clamp holding everything together, but typically there are a lot of detours around the official language.

# Language Elements

SQL consists of statements that start with a keyword like SELECT, DELETE or CREATE and terminate with a semicolon. Their elements are case-insensitive except for fixed character string values like 'Mr. Brown'.
* Clauses: Statements are subdivided into clauses. The most popular one is the WHERE clause.
* Predicates: Predicates specify conditions that can be evaluated to a boolean value. E.g.: a boolean comparison, BETWEEN, LIKE, IS NULL, IN, SOME/ANY, ALL, EXISTS.
* Expressions: Expressions are numeric or string values by itself, or the result of arithmetic or concatenation operators, or the result of functions.
* Object names: Names of database objects like tables, views, columns, functions.
* Values: Numeric or string values.
* Arithmetic operators: The plus sign, minus sign, asterisk and solidus (+, –, * and /) specify addition, subtraction, multiplication, and division.
* Concatenation operator: The '||' sign specifies the concatenation of character strings.
* Comparison operators: The equals operator, not equals operator, less than operator, greater than operator, less than or equals operator, greater than or equals operator ( =, <>, <, >, <=, >= ) compares values and expressions.
* Boolean operators: AND, OR, NOT combines boolean values.

# Learning by Doing

When learning SQL (or any other programming language), it is not sufficient to read books or listen to lectures. It's absolutely necessary that one does exercises - prescribed exercises as well as own made-up tests. In the case of SQL, one needs access to a DBMS installation, where he can create tables, store, retrieve and delete data, and so on.
This page offers hints and links to some popular DBMS. In most cases, one can download the system for test purposes or use a free community edition. Some of them offer an online version so that there is no need for any local installation. Instead, such systems can be used in the cloud.
Often, but not always, a DBMS consists of more than the pure database engine. To be able to formulate SQL commands easily, we additionally need an interactive access to the database engine. Different client programs and IDEs provide this. They offer interactive access, and in many cases, they are part of the downloads. (In some cases, there are several different clients from the same producer.) At the same time, there are client programs and IDEs from other companies or organizations which offer only an interactive access but no DBMS. Such clients often support a lot of different DBMS.

## Derby

http://db.apache.org/derby/

## Firebird

http://www.firebirdsql.org/

## IBM DB2

http://www-01.ibm.com/software/data/db2/linux-unix-windows/

## IBM Informix

http://www-01.ibm.com/software/data/informix/

## MariaDB

https://mariadb.org/

## MS SQL Server

http://www.microsoft.com/en/server-cloud/products/sql-server/default.aspx

## MySQL

DBMS: http://dev.mysql.com/downloads/ IDE for administration and SQL-tests: http://dev.mysql.com/downloads/workbench/

## Oracle

The Oracle database engine is available in 4 editions: Enterprise Edition (EE), Standard Edition (SE), Standard Edition One (SE One), and Express Edition (XE). The last-mentioned is the community edition and is sufficient for this course. http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.
SQL-Developer is an IDE with an Eclipse-like look-and-feel and offers access to the database engine. http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/
In the context of Oracles application builder APEX (APplication EXpress), there is a cloud solution consisting of a database engine plus APEX. https://apex.oracle.com/. Among a lot of other things, it offers an SQL workshop where everybody can execute his own SQL commands for testing purposes. On the other hand, APEX can be downloaded separately and installed into any of the above editions except for the Express Edition.

## PostgreSQL

http://www.postgresql.org/

## SQLite

http://www.sqlite.org/

## Online Access

SQL Fiddle offers an online access to following implementations: MySQL, PostgreSQL, MS SQL Server, Oracle, and SQLite.

# Snippets

This page offers SQL examples concerning different topics. You can copy/past the examples according to your needs.

## Create Table
### Data Types

    More Details

### Constraints

    More Details

### Foreign Key

    More Details

## Alter Table

    More Details
Concerning columns.
Concerning complete table.

## Drop Table

    More Details

## Select
### Basic Syntax

    More Details

### Case

    More Details

### Grouping

    More Details

### Join

    More Details

### Subquery

    More Details

### Set operations

    More Details

### Rollup/Cube

    More Details

### Window functions

    More Details

### Recursions

    More Details

## Insert

    More Details

## Update

    More Details

## Merge

    More Details

## Delete

    More Details

## Truncate

    More Details

# Create a simple Table
## More than a Spreadsheet

Let's start with a simple example. Suppose we want to collect information about people - their name, place of birth and some more items. In the beginning we might consider to collect this data in a simple spreadsheet. But what if we grow to a successful company and have to handle millions of those data items? Could a spreadsheet deal with this huge amount of information? Could several employees or programs simultaneously insert new data, delete or change it? Of course not. And this is one of the noteworthy advantages of a Database Management System (DBMS) over a spreadsheet program: we can imagine the structure of a table as a simple spreadsheet - but the access to it is internally organized in a way that huge amounts of data can be accessed by a lot of users at the same time.
In summary, it can be said that one can imagine a table as a spreadsheet optimized for bulk data and concurrent access.

## Conceive the Structure

To keep control and to ensure good performance, tables are subject to a few strict rules. Every table column has a fixed name, and the values ​​of each column must be of the same data type. Furthermore, it is highly recommended - though not compulsory - that each row can be identified by a unique value. The column in which this identifying value resides is called the Primary Key. In this Wikibook, we always name it id. But everybody is free to choose a different name. Furthermore, we may use the concatenation of more than one column as the Primary Key.
At the beginning we have to decide the following questions:
* What data about persons (in this first example) do we want to save? Of course, there is a lot of information about persons (e.g., eye color, zodiacal sign, ...), but every application needs only some of them. We have to decide which ones are of interest in our concrete context.
* What names do we assign to the selected data? Each identified datum goes into a column of the table, which needs to have a name.
* Of what type are the data? All data values within one column must be of the same kind. We cannot put an arbitrary string into a column of data type DATE.
In our example, we decide to save the first name, last name, date, and place of birth, social security number, and the person's weight. Obviously date of birth is of data type DATE, the weight is a number, and all others are some kind of strings. For strings, there is a distinction between those that have a fixed length and those in which the length usually varies greatly from row to row. The former is named CHAR(<n>), where <n> is the fixed length, and the others VARCHAR(<n>), where <n> is the maximum length.

## Fasten Decisions

The decisions previously taken must be expressed in a machine-understandable language. This language is SQL, which acts as the interface between end-users - or special programs - and the DBMS.
We choose person as the name of the table, which consists of seven columns. The id column is assigned the role of the Primary Key. We can store exclusively digits in the columns id and weight, strings of a length up to 50 characters in firstname, lastname and place_of_birth, dates in date_of_birth and a string of exactly eleven characters in ssn. The phrase NOT NULL is part of the definition of id, firstname, lastname and weight. This means that in every row, there must be a value for those four columns. Storing no value in any of those columns is not possible - but the 8-character-string 'no value' or the digit '0' are allowed because they are values. Or to say it the other way round: it is possible to omit the values of date_of_birth, place_of_birth and ssn.
The definition of a Primary Key is called a 'constraint' (later on, we will get to know more kinds of constraints). Every constraint should have a name - it's person_pk in this example.

## The Result

After execution of the above 'CREATE TABLE' command, the DBMS has created an object that one can imagine similar to the following Wiki-table:
       idfirstnamelastnamedate_of_birthplace_of_birthssnweight
This Wiki-table shows 4 lines. The first line represents the names of the columns - not values! The following 3 lines are for demonstration purposes only. But in the database table, there is currently no single row! It is completely empty, no rows at all, no values at all! The only thing that exists in the database is the structure of the table.

## Back to Start

Maybe we want to delete the table one day. To do so, we can use the DROP command. It removes the table totally: all data and the complete structure are thrown away.
Don't confuse the DROP command with the DELETE command, which we present on the next page. The DELETE command removes only rows - possibly all of them. However, the table itself, which holds the definition of the structure, is retained.

# Handle Data

As shown in the previous page, we now have an empty table named person. What can we do with such a table? Just use it like a bag! Store things in it, look into it to check the existence of things, modify things in it or throw things out of it. These are the four essential operations, which concerns data in tables:
* INSERT: put some data into the table
* SELECT: retrieve data from the table
* UPDATE: modify data, which exists in the table
* DELETE: remove data from the table.
For each of these four operations, there is a SQL command. It starts with a keyword and runs up to a terminating semicolon. This rule applies to all SQL commands: They are introduced by a keyword and terminated by a semicolon. In the middle, there may be more keywords as well as object names and values.

## Store new Data with INSERT Command

When storing new data in rows of a table, we must name all affected objects and values: the table name (there may be a lot of tables within the database), the column names and the values. All this is embedded within some keywords so that the SQL compiler can recognize the tokens and their meaning. In general, the syntax for a simple INSERT is
Here is an example
When the DBMS recognizes the keywords INSERT INTO and VALUES, it knows what to do: it creates a new row in the table and puts the given values into the named columns. In the above example, the command is followed by a second one: COMMIT confirms the INSERT operation as well as the other writing operations UPDATE and DELETE. (We will learn much more about COMMIT and its counterpart ROLLBACK in a later chapter.)
    A short comment about the format of the value for date_of_birth: There is no unique format for dates honored all over the world. Peoples use different formats depending on their cultural habits. For our purpose, we decide to represent dates in the hierarchical format defined in ISO 8601. It may be possible that your local database installation use a different format so that you are forced to either modify our examples or to modify the default date format of your database installation.
Now we will put some more rows into our table. To do so, we use a variation of the above syntax. It is possible to omit the list of column names if the list of values correlates precisely with the number, order, and data type of the columns used in the original CREATE TABLE statement.
    Hint: The practice of omitting the list of column names is not recommended for real applications! Table structures change over time, e.g. someone may add new columns to the table. In this case, unexpected side effects may occur in applications.

## Retrieve Data with SELECT Command

Now our table should contain five rows. Can we be sure about that? How can we check whether everything worked well and the rows and values exist really? To do so, we need a command which shows us the actual content of the table. It is the SELECT command with the following general syntax
As with the INSERT command, you may omit some parts. The simplest example is
The asterisk character '*' indicates 'all columns'. In the result, the DBMS should deliver all five rows, each with the seven values we used previously with the INSERT command.
In the following examples, we add the currently missing clauses of the general syntax - one after the other.
Add a list of some or all columnnames
The DBMS should deliver the two columns firstname and lastname of all five rows.
Add a search condition
The DBMS should deliver the three columns id, firstname and lastname of three rows.
Add a sort instruction
The DBMS should deliver the four columns id, firstname, lastname and date_of_birth of three rows in the ascending order of date_of_birth.

## Modify Data with UPDATE Command

If we want to change the values of some columns in some rows we can do so by using the UPDATE command. The general syntax for a simple UPDATE is:
Values are assigned to the named columns. Unmentioned columns keep unchanged. The search_condition acts in the same way as in the SELECT command. It restricts the coverage of the command to rows, which satisfy the criteria. If the WHERE keyword and the search_condition are omitted, all rows of the table are affected. It is possible to specify search_conditions, which hit no rows. In this case, no rows are updated - and no error or exception occurs.
Change one column of one row
The first name of Mr. de Winter changes to James Walker, whereas all his other values keep unchanged. Also, all other rows keep unchanged. Please verify this with a SELECT command.
Change one column of multiple rows
The <search_condition> isn't restricted to the Primary Key column. We can specify any other column. And the comparison operator isn't restricted to the equal sign. We can use different operators - they solely have to match the data type of the column.
In this example, we change the firstname of four rows with a single command. If there is a table with millions of rows we can change all of them using one single command.
Change two columns of one row
The two values are changed with one single command.

## Remove data with DELETE Command

The DELETE command removes complete rows from the table. As the rows are removed as a whole, there is no need to specify any columnname. The semantics of the <search_condition> is the same as with SELECT and UPDATE.
Delete one row
The row of James de Winter is removed from the table.
Delete many rows
All remained rows are deleted as we have omitted the <search_condition>. The table is empty, but it still exists.
No rows affected
This command will remove no row as there is no row with id equals to 99. But the syntax and the execution within the DBMS are still perfect. No exception is thrown. The command terminates without any error message or error code.

## Summary

The INSERT and DELETE commands affect rows in their entirety. INSERT puts a complete new row into a table (unmentioned columns remain empty), and DELETE removes entire rows. In contrast, SELECT and UPDATE affect only those columns that are mentioned in the command; unmentioned columns are unaffected.
The INSERT command (in the simple version of this page) has no <search_condition> and therefore handles exactly one row. The three other commands may affect zero, one, or more rows depending on the evaluation of their <search_condition>.

# Example Database Structure

First of all a database is a collection of data. These data are organized in tables as shown in the example person. In addition, there are many other kinds of objects in the DBMS: views, functions, procedures, indices, rights and many others. Initially we focus on tables and present four of them. They serve as the foundation for our Wikibook. Other kind of objects will be given later.
We try to keep everything as simple as possible. Nevertheless, this minimalistic set of four tables demonstrates a 1:n as well as a n:m relationship.

## person

The person table holds information about fictitious persons; see: Create a simple Table.

## contact

The contact table holds information about the contact data of some persons. One could consider to store this contact information in additional columns of the person table: one column for email, one for icq, and so on. We decided against it for some serious reasons.
* Missing values: A lot of people do not have most of those contact values respectively we don't know the values. Hereinafter the table will look like a sparse matrix.
* Multiplicities: Other people have more than one email address or multiple phone numbers. Shall we define a lot of columns email_1, email_2, ... ? What is the upper limit? Standard SQL does not offer something like an 'array of values' for columns (some implementations do).
* Future Extensions: Someday, there will be one or more contact types that are unknown today. Then we have to modify the table.
We can deal with all these situations in an uncomplicated way, when the contact data goes to its own table. The only special thing is bringing persons together with their contact data. This task will be managed by the column person_id of table contact. It holds the same value as the Primary Key of the allocated person.
The general statement is that we do have one information unit (person) to which potentially multiple information units of the same type (contact) belongs to. We call this togetherness a relationship - in this case a 1:m relationship (also known as a one to many relationship). Whenever we encounter such a situation, we store the values, which may occur more than once, in a separate table together with the id of the first table.

## hobby

People usually pursue one or more hobbies. Concerning multiplicity, we have the same problems as before with contact. So we need a separate table for hobbies.
You may have noticed that there is no column for the corresponding person. Why this? With hobbies, we have an additional problem: It's not just that one person pursues multiple hobbies. At the same time, multiple persons pursue the same hobby.
We call this kind of togetherness a n:m relationship. It can be designed by creating a third table between the two original tables. The third table holds the ids of the first and second table. So one can decide which person pursues which hobby. In our example, this 'table-in-the-middle' is person_hobby and will be defined next.

## person_hobby

Every row of the table holds one id from person and one from hobby. This is the technique of how the information of persons and hobbies are joined together.

## Visualisation of the Structure

After execution of the above commands, your database should contain four tables (without any data). The tables and their relationship to each other may be visualized in a so-called Entity Relationship Diagram. On the left side there is the 1:n relationship between person and contact and on the right side the n:m relationship between person and hobby with its 'table-in-the-middle' person_hobby.

# Example Database Data

rDBMS offers different ways to put data into their storage: from CSV files, Excel files, product-specific binary files, via several API's or special gateways to other databases respectively database systems and some more technics. So there is a wide range of - non standardized - possibilities to bring data into our system. Because we are speaking about SQL, we use the standardized INSERT command to do the job. It is available on all systems.
We use only a small amount of data because we want to keep things simple. Sometimes one needs a high number of rows to do performance tests. For this purpose, we show a special INSERT command at the end of this page, which exponentially inflates your table.

## person

Please note that the format of DATEs may depend on your local environment. Furthermore, SQLite uses a different syntax for the implicit conversion from string to DATE.

## Grow up

For realistic performance tests, we need a vast amount of data. The few number of rows in our example database does not meet this criteria. How can we generate test data and store it in a table? There are different possibilities: FOR loops in a procedure, (pseudo-) recursive calls, importing external data in a system-specific fashion, and some more.
Because we are dealing with SQL, we introduce an INSERT command, which is portable across all rDBMS. Although it has a simple syntax, it is very powerful. With every execution, it will double the number of rows. Suppose there is 1 row in a table. After the first execution, there will be a second row in the table. At first glance, this sounds boring. But after 10 executions there are more than a thousand rows, after 20 executions there are more than a million, and we suspect that only a few installations can execute it more than 30 times.
The command is an INSERT in combination with a (Sub-)SELECT. The SELECT retrieves all rows of the table because there is no WHERE clause. This is the reason for the doubling. The mandatory columns firstname and lastname keeps unchanged. We ignore optional columns. Only the primary key id is computed. The new value is the sum of the old value plus the highest available id when starting the command.
Some more remarks:
* max(id) is determined only once per execution! This illustrates an essential aspect of rDBMS: At a conceptual level, the database has a particular state before execution of a command and a new state after its execution. Commands are atomic operations moving the database from one state to another - they run entirely or not a bit! Both, the SELECT and the inner SELECT with the max(id), act on the initial state. They never see the result or an intermediate result of the INSERT. Otherwise, the INSERT would never end.
* If we wish to observe the process of growing, we can add a column to the table to store max(id) with each iteration.
* The computation of the new id may be omitted if the DBMS supports AUTOINCREMENT columns.
* For performance tests, it may be helpful to store some random data in one or more columns.

# SELECT: Fundamentals

The SELECT command retrieves data from one or more tables or views. It generally consists of the following language elements:
With the exception of the first two elements all others are optional. The sequence of language elements is mandatory. At certain places within the command there may start new SELECT commands - in a recursive manner.

## Projection (specify resulting columns)

In the projection part of the SELECT command, you specify a list of columns, operations working on columns, functions, fixed values, or new SELECT commands.
The DBMS will retrieve ten rows, each of which consists of four columns.
We can mix the sequence of columns in any order or retrieve them several times.
The asterisk '*' is an abbreviation for the list of all columns.
For numeric columns, we can apply the usual numeric operators +, -, * and /. There are also many predefined functions depending on the data type: power, sqrt, modulo, string functions, date functions.

### Uniqueness via keyword DISTINCT

It is possible to compact the result in the sense of unique values by using the keyword DISTINCT. In this case, all resulting rows, which would be identical, will be compressed to one row. In other words: duplicates are eliminated - just like in set theory.

### Aliases for Column names

Sometimes we want to give resulting columns more descriptive names. We can do so by choosing an alias within the projection. This alias is the new name within the result set. GUIs show the alias as the column label.

### Functions

There are predefined functions for use in projections (and at some other positions). The most frequently used are:
   * count(<columnname>|'*'): Counts the number of resulting rows.
   * max(<columnname>): The highest value in <column> of the resultset. Also applicable on strings.
   * min(<columnname>): The lowest value in <column> of the resultset. Also applicable on strings.
   * sum(<columnname>): The sum of all values in a numeric column.
   * avg(<columnname>): The average of a numeric column.
   * concat(<columnname_1>, <columnname_2>): The concatenation of two columns. Alternatively the function may be expressed by the '||' operator: <columnname_1> || <columnname_2>
Standard SQL and every DBMS offers many more functions.
We must differentiate between those functions which return one value per row like concat and those which return only one row per complete resultset like max. The former one may be mixed in any combination with column names, as shown in the very first example of this page. With the later ones, there exists a problem: If we mix them with a regular column name, the DBMS recognises a contradiction in the query. On the one hand it should retrieve precisely one value (in one row), and on the other hand, it should retrieve a lot of values (in a lot of rows). The reaction of DBMS differs from vendor to vendor. Some throw an error message at runtime - in accordance with the SQL standard -, others deliver suspicious results.

### SELECT within SELECT

If we really want to see the result of a result-set-oriented-function in combination with columns of more than one row, we can start a very new SELECT on a location where - in simple cases - a column name occurs. This second SELECT is an absolutely independent command. Be careful: It will be executed for every resulting row of the first SELECT!

## Table Names

The Keyword FROM is used to specify the table on which the command will work. This table name can be used as an identifier. In the first simple examples prefixing column names with the table name identifier can be used but isn't required. In the later more complex command, the table name identifier is a needed feature.

## Restriction (specify resulting rows)

In the WHERE clause, we specify some 'search conditions' which are among the named table(s) or view(s). The evaluation of this criteria is - mostly - one of the first things during the execution of a SELECT command. Before any row can be sorted or displayed, it must meet the conditions in the clause.
If we omit the clause, all rows of the table are retrieved. Else the number of rows will be reduced according to the specified criteria. If we specify 'weight < 70', for example, only those rows are retrieved where the weight column stores a value less than 70. The restrictions act on rows of tables by evaluating column values (sometimes they act on other things like the existence of rows, but for the moment we focus on basic principles). As a result, we can imagine that the evaluation of the 'where clause' produces a list of rows. This list of rows will be processed in further steps like sorting, grouping, or displaying certain columns (projection).

### Comparisons

We compare variables, constant values, and results of function calls with each other in the same way as we would do in different programming languages. The only difference is that we use column names instead of variables. The comparison operators must match the given data types they have to operate on. The result of the comparison is a boolean value. If it is 'true', the according row will be processed furthermore. Some examples:
   * 'weight = 70' compares the column 'weight' with the constant value '70' whether the column is equal to the constant value.
   * '70 = weight': same as before.
   * 'firstname = lastname' compares two columns - each of the same row - for equality. Names like 'Frederic Frederic' evaluate to true.
   * 'firstname < lastname' is a fair comparison of two columns according to the lexical order of strings.
   * 'LENGTH(firstname) < 5' compares the result of a function call to the constant value '5'. The function LENGTH operates on strings and returns a number.

### Boolean Logic

Often we want to specify more than a single search criterion, e.g., are there people born in San Francisco with lastname Baker? To do this, we specify every necessary comparison independent from the next one and join them together with the boolean operators AND respectively OR.
The result of a comparison is a boolean. It may be toggled between 'true' and 'false' by the unary operator NOT.
The precedence of comparisons and boolean logic is as follows:
   * all comparisons
   * NOT operator
   * AND operator
   * OR operator
Two abbreviations
Sometimes we shorten the syntax by using the BETWEEN keyword. It defines a lower and upper limit and is primarily used for numeric and date values, but also applicable to strings.
For the comparison of a column or function with several values, we can use the short IN expression.

### FETCH: Pick and Choose certain Rows

Sometimes we are not interested in all resulting rows, e.g.: we may want to see only the first 3 or 10 rows. This can be achieved with the OFFSET and FETCH clauses. OFFSET specifies the number of rows to be skipped (counting from the beginning of the result set), and FETCH specifies the number of rows, after which the delivery of rows shall stop.
Please notice that the OFFSET and FETCH clauses are separate parts of the SELECT command. Some implementations handle this functionality as part of the WHERE clause or with different keywords (ROWNUM, START, SKIP, LIMIT).
The functionality of OFFSET and FETCH can be achieved likewise by window functions with their more general syntax.

## Grouping

We will offer the GROUP BY clause in combination with the HAVING clause in a later chapter.

## Sorting

The DBMS is free to deliver the resulting rows in an arbitrary order. Rows may be returned in the order of the Primary Key, in the chronological order they are stored into the database, in the order of a B-tree organized internal key, or even in random order. Concerning the sequence of delivered rows, the DBMS may do what it wants to do. Don't expect anything.
If we expect a particular order of rows, we must express our wishes explicitly. We can do this in the ORDER BY clause. There we specify a list of column names in combination with an option for ascending or descending sorting.
In the above result, there are two rows with identical values in the column weight. As this situation leads to random results, we have the possibility to specify more columns. These following columns are processed only for those rows with identical values in all previous columns.
In the ORDER BY clause, we can specify any column of the processed table. We are not limited to the ones which are returned by the projection.

## Combine the Language Elements

Only the first two elements of the SELECT command are mandatory: the part up to the first table (or view) name. All others are optional. If we also specify the optional ones, their predetermined sequence must be kept in mind. But they are combinable according to our needs.

## Further Information

There is more information about the additional options for the SELECT command.
* Join Operation
* Grouping
* IS NULL Predicate
* Predefined Functions
* Set Operations
* Case Expression
* Like Predicate
* Subquery

## Exercises

Show hobbyname and remark from the hobby table.
Show hobbyname and remark from the hobby table. Order the result by hobbyname.
Show hobbyname and remark from the hobby table. Choose 'Hobby' as first columnname and 'Short_Description_of_Hobby' as second columnname.
Show firstname and lastname of persons born in San Francisco.
Show all information items of persons with lastname 'de Winter'.
How many rows are stored in the contact table?
How many E-Mails are stored in the contact table?
What is the mean weight of persons born in San Francisco?
Find persons born after 1979-12-31, which weigh more than / less than 50 kg.
Find persons born in Birmingham, Mumbai, Shanghai or Athens in the order of their firstname.
Find persons born in Birmingham, Mumbai, Shanghai or Athens within the 21. century.
Find persons born between Dallas and Richland ('between' not in the sense of a geographic area but of the lexical order of citynames)
Which kind of contacts are stored in the contact table? (Only one row per value.)
How many different kind of contacts are stored in the contact table? (Hint: Count the rows of above query.)
Show contact_type, contact_value and a string of the form 'total number of contacts: <x>', where <x> is the quantity of all existing contacts.

# SELECT: Join Operation

Data should be stored in such a way that no redundant information exists in the database. For example, if our database includes groups of people who, in each case, all pursue the same hobby, then we would rather avoid repeatedly storing the same static details about a given hobby; namely in every record about one of the hobby's enthusiasts. Likewise, we would rather avoid repeatedly storing the same detailed information about an individual hobbyist, namely in every record about one of that person's hobbies. Instead, we create independent person and hobby tables and point from one to the other. This technique for grouping data in separate, redundancy-free tables is called database normalization. Such separation also tends to simplify the logic and enhance the flexibility of assembling precisely the items needed for a given purpose. This assembly is accomplished through the 'JOIN' operation.

## The Idea

In our example database, there are two tables: person and contact. The contact table contains the column person_id, which correlates with the Primary-Key column id of the person table. By evaluating the column values, we can join contacts and persons together.
| ID | LASTNAME | FIRSTNAME | ... |
| 1 | Goldstein | Larry | ... |
| 2 | Burton | Tom | ... |
| 3 | Hamilton | Lisa | ... |
| 4 | Goldstein | Kim | ... |
| ID | PERSON_ID | CONTACT_TYPE | CONTACT_VALUE |
| 1 | 1 | fixed line | 555-0100 |
| 2 | 1 | email | larry.goldstein@acme.xx |
| 3 | 1 | email | lg@my_company.xx |
| 4 | 1 | icq | 12111 |
| 5 | 4 | fixed line | 5550101 |
| 6 | 4 | mobile | 10123444444 |
Joined (virtual) table, created out of person and contact
| P.ID | P.LASTNAME | P.FIRSTNAME | ... | C.ID | C.PERSON_ID | C.CONTACT_TYPE | C.CONTACT_VALUE |
| 1 | Goldstein | Larry | ... | 1 | 1 | fixed line | 555-0100 |
| 1 | Goldstein | Larry | ... | 2 | 1 | email | larry.goldstein@acme.xx |
| 1 | Goldstein | Larry | ... | 3 | 1 | email | lg@my_company.xx |
| 1 | Goldstein | Larry | ... | 4 | 1 | icq | 12111 |
| 2 | Burton | Tom | ... | ? | ? | ? | ? |
| 3 | Hamilton | Lisa | ... | ? | ? | ? | ? |
| 4 | Goldstein | Kim | ... | 5 | 4 | fixed line | 5550101 |
| 4 | Goldstein | Kim | ... | 6 | 4 | mobile | 10123444444 |
So, Larry Goldstein, that exists only once in the stored person table, is now listed four times in the joined, virtual table – each time, in combination with one of his four contact items. The same applies to Kim Goldstein and his two contact items.
But what is going on with Tom Burton and Lisa Hamilton, whose contact information is not available? We may have some trouble attempting to join their person data with their non-existent contact information. For the moment, we have flagged the situation with question marks. A detailed explanation of how to transform the problem into a solution appears later on this page.

## The Basic Syntax

Obviously it's necessary to specify two things with the JOIN operation
   * the names of the relevant tables
   * the names of the relevant columns
The basic syntax extends the SELECT command with these two elements
Let's make a first attempt.
One of the table names is referenced after the FROM keyword (as previously), and the other one after the new keyword, JOIN, which (no surprise here) instructs the DBMS to perform a join operation. Next, the ON keyword introduces the column names together with a comparison operator (or a general condition, as you will see later). The column names are prefixed with the respective aliases of the table names, p and c. This is necessary because columns with identical names (like id) may exist in multiple tables.
When the DBMS executes the command, it delivers 'something' that contains all the columns from both tables, including the two id columns from their respective (person and contact) tables. The result contains nine rows, one per existing combination of person and contact; viz., due to the 'ON' expression, person records without any corresponding contact records will not appear in the result.
The delivered 'something' looks like a new table; in fact, it has the same structure, behavior, and data as a table. If it is created from a view or as the result of a subselection, we can even perform new SELECTs on it. But there is an important difference between this and a table: Its assembled data is not stored in the DBMS as such; rather, the data is computed at run time from the values of real tables, and only held in temporary memory while the DBMS is running your program.
This key feature – assembling complex information from simple tables – is made possible by means of the two simple keywords, JOIN and ON. As you will see also, the syntax can be extended to build very complex queries, such that you can add many additional refinements to the specification of your join criteria.
It can sometimes be confusing when results don't match your intentions. If this happens, try to simplify your query, as shown here. Confusion often results from the fact that the JOIN syntax itself may become quite complicated. Moreover, joining can be combined with all of the other syntactic elements of the SELECT command, which also may lead to a lack of clarity.
The combination of the join syntax with other language elements is shown in the following examples.

## Four Join Types

Earlier on this page, we saw an example of a join result wherein some rows contained person names, but no contact information – instead showing a question mark in that latter column. If the basic syntax of the JOIN operation had been used, those (question-mark) rows would have been filtered out. That (basic syntax with exclusive result) is known as an INNER join. There are also three different kinds of OUTER joins. The results of an OUTER join will contain not only all the full-data rows that an INNER join's results would, but also partial-data rows, i.e., those where no data was found in one or both of the two stored tables; thus, they're called LEFT OUTER, RIGHT OUTER and FULL OUTER joins.
So we can widen the basic JOIN syntax to the four options:
   * [INNER] JOIN
   * LEFT [OUTER] JOIN
   * RIGHT [OUTER] JOIN
   * FULL [OUTER] JOIN
Keywords surrounded by [ ] are optional. The parser infers OUTER from LEFT, RIGHT or FULL, and a plain (i.e., basic-syntax) JOIN defaults to INNER.

### Inner Join

The inner join is probably the most commonly used of the four types. As we have seen, it results in precisely those rows that exactly match the criterion following the ON. Below is an example showing how to create a list of persons and their contacts.
What is most significant is that records for persons without any contact information are not part of the result.

### Left (outer) Join

Sometimes we need a little more; for example, we might want a list of all person records, to include any contact-information records that may also be available for that person. Note how this differs from the example above: this time, the results will contain all person records, even those for persons who have no contact-information record(s).
In those cases where the contact information is unavailable, the DBMS will supplant it with the 'null value' or with the 'null special marker' (not to be confused with the string (-type) 'null value' or 'null' nor with binary 0. Nonetheless, implementation details aren't important here. The null special marker will be discussed in a later chapter).
In summary, the left (outer) join is an inner join, plus one row for each left-side match without a counterpart on the right side.
Consider the word 'left'. It refers to the left side of the formula, "FROM <table_1> LEFT JOIN <table_2>", or more specifically, the table denoted on the left side (here: table_1); indicating that every row of that table will be represented at least once in the result, whether a corresponding record is found in the right-side table (here: table_2) or not.
Another example:
What's the difference? We've changed the order of the table names. Note that we're still using a LEFT join, but because contact is now the "left" referent (the object in the FROM clause), contact data will now be considered as being of primary importance; therefore, all the contact rows will appear in the result - along with any corresponding information that may exist in the person table. As it happens, in the database we're using, every contact record corresponds to a person record so, in this case, it works out that the results are equivalent to what they'd have been if we'd used an inner join. Yet they're different from those of the previous left-join example.

### Right (outer) Join

The right join obeys the same rules as the left join, but in reverse. Now, every record from the table referenced in the join clause will appear in the result, including those that have no corresponding record in the other table. Again, the DBMS supplies each empty right-column cell with the null special marker. The only difference is that the evaluation sequence of tables is carried out in reverse or, in other words, with the roles of the two tables swapped.

### Full (outer) Join

A full join retrieves every row of both the left table and the right table, regardless of whether a corresponding record exists in the respective opposite table.
Given table_1 and table_2 below,
| ID | X |
| 1 | 11 |
| 2 | 12 |
| 3 | 13 |
| ID | TABLE_1_ID | Y |
| 1 | 1 | 21 |
| 2 | 5 | 22 |
the full join:
will yield:
| T1.ID | T1.X | T2.ID | T2.TABLE_1_ID | T2.Y |
| 1 | 11 | 1 | 1 | 21 |
| 2 | 12 | null | null | null |
| 3 | 13 | null | null | null |
| null | null | 2 | 5 | 22 |
These results contain the (single) matching row, plus a row each for all the other records of both of the original tables. As each of these other rows represent data found in only one of the tables, they are each missing some data, so the cells representative of that missing data contain the null special marker.
Note: The full join is not supported by all DBMS. Nevertheless, because it isn't an atomic operation, it is always possible to create the desired result by a combination of multiple SELECTs with SET operations.

## Cartesian Product (Cross Join)

With inner joins it is possible to omit the ON. SQL interprets this as a - syntactically correct - request to combine every record of the left table with every record of the right table. It will return a large number of rows: the product of the row counts of the two tables.
This particular kind of an inner join is called a Cartesian product or CROSS JOIN. The Cartesian product is an elementary operation of relational algebra, which is the foundation for all rDBMS implementations.
Be careful then; if you unintentionally omit the ON term, the result will be much larger than expected. If, for example, the first table contains 10,000 records, and the second one 20,000 records, the output will contain 200 million rows.

## The n:m Situation

How can we create a list of persons and their hobbies? Remember: one person may run many hobbies and several persons may run the same hobby. So there is no direct connection from persons to hobbies. Between the two tables, we have created a third one person_hobby. It holds the id of persons as well as the id of hobbies.
We have to 'walk' from person to person_hobby and from there to hobby.
Please note that no column of the table person_hobby goes to the result. This table acts only during intermediate execution steps. Even its column id is not of interest.
Some people do not perform a hobby. As we performed an INNER JOIN they are not part of the above list. If we want to see in the list also persons without hobbies, we must do what we have done before: use LEFT OUTER JOINs instead of INNER JOINs.
Hint: If necessary, we can combine every kind of join with every other kind of join in every desired sequence, e.g.: LEFT OUTER with FULL OUTER with INNER ... .

## More Details

Criteria for join operations are not restricted to the usual formulation:
First, we can use any column, not only primary key and foreign key columns. In one of the above examples, we used the lastname for a join. Lastname is of type character and has no meaning of any key. To avoid poor performance, some DBMS restrict the use of columns to those having an index.
Second, the comparator is not restricted to the equal sign. We can use any meaningful operator, for example, the 'greater than' for numeric values.
Third, we can use an arbitrary function.

## Exercises

Show first- and lastname plus icq number for persons having an icq number
Show first- and lastname plus ICQ number plus fixed line number for persons having an ICQ number AND a fixed line. You need to join the contact table twice.
Show first- and lastname plus (if present) the ICQ number for ALL persons
Create a list which contains ALL hobbies plus according persons (if present)
Is it possible that one of the three outer joins contains fewer rows than the corresponding inner join?

# SELECT: Grouping

In this chapter we will leave the level of individual rows and describe statements that refer to groups of rows. In the context of SQL such 'row-groups' (or sets of rows) are built by the GROUP BY clause and further processed by the HAVING clause.

## Constitute Groups

First we must establish criteria for filtering rows by groups. To do so we use the content of one or more columns of the involved table(s). If the values are identical, the rows belong to the same group. Consider the lastname in table person. In our small example we can insinuate that persons with same lastname form a family. So to see information about families we should use this column as the grouping criterion. This grouping allows us to ask questions concerning whole families, such as 'Which families are there?', 'How many families exists?', 'How many persons are in each family?'. All of these are questions about the whole group (which means the family), not about single rows (which means the person).
In the SQL syntax, the criterion is specified after the keyword GROUP BY and consists of one or more column names.
Our concrete example about families looks like this:
The query retrieves seven 'family names' out of the ten rows. There are several persons with lastname 'Goldstein' or 'de Winter'.
We can retrieve the same seven 'family names' by applying the keyword DISTINCT in a SELECT without GROUP BY.
What makes the difference? The DISTINCT keyword is limited to remove duplicate values. It can not initiate computations on other rows and columns of the result set. In contrast, the GROUP BY additionally arranges the intermediate received rows as several groups and offers the possibility to get information about each of these groups. It is even the case that within these groups all columns are available, not only the 'criterion'-column. To confirm this statement about 'all' columns, we use weight, which is not the 'criterion'-column.
The result shows the seven family names - as seen before - plus the average weight of every family. The weight of individual persons is not shown. (In groups with precisely one person the average weight of the group is, of course, identical to the single person's weight.)

### Grouping over multiple columns

If necessary, we can define the grouping over more than one column. In this case, we can imagine the concatenation of the columns as the grouping rule.

## Inspect Groups

After we have defined groups with the GROUP BY keyword, we can select more information about each of them, e.g.: how many persons (rows) exist within each family (group of rows)?
We see that in our small example database there is one family with three members, another with two members and all others consist of exactly 1 member.
What is going on behind the scene during the execution of the command?
   * All ten rows of table person are retrieved (in the above command, there is no WHERE clause).
   * The rows are arranged into seven groups according to the value of column lastname.
   * Every group with all of its rows is passed to the SELECT clause.
   * The SELECT builds one resulting row for every received group (in 'real world' databases each of the groups may contain thousands of rows).
In step 4 exactly one resulting row is generated per group. Because the SELECT creates only one resulting row per group, it is not possible to show values of such columns which may differ from row to row, e.g., the firstname. The SELECT can only show such values of which it is ensured that they are identical within all rows of the group: the 'criterion'-column.
Nevertheless, we can get information about the non-criterion-columns. But this information is more generalized. The DBMS offers a special group of functions which build one value out of a set of rows. Consider the avg function, which computes the arithmetic mean of numerical values. This function receives a column name and operates on a set of rows. If our command in question contains a GROUP BY clause, the avg function does compute one value per group - not one value per all rows as usual. So it is possible to show the result of such functions together with the values of the 'criterion'-column.
Here is an - incomplete - list of such functions: count, max, min, sum, avg. Not all functions are of that kind, e.g. the function concat, which concatenates two strings, operates on single rows and creates one value per row.

## Focus on Desired Groups

You know the WHERE clause. It defines which rows of a table will be part of the result set. The HAVING clause has the same meaning at the group-level. It determines which groups will be part of the result set.
We retrieve exclusively families with more than 1 members:
All families with one member are no longer part of the result.
In a second example we focus on such groups which satisfies a criterion on column firstname. Consider that firstname is not the grouping-column.
The result shows the five families Baker, de Winter, Goldstein, Rich, and Stefanos (but not the row(s) with the long firstname).
Please note that this result is very different from the similar question to persons whose firstname has more than four characters:
Where is the additional row coming from? In the family de Winter, there are two persons with a firstname longer than four characters: James and Victor. Because in the command without GROUP BY we select for persons and not for families, both rows are displayed individually.
In summary, we can say that the HAVING clause decides which groups are part of the result set and which are not.

## The Overall Picture

The GROUP BY and HAVING clauses are part of the SELECT command, and we can combine them with any other clauses of the SELECT as desired. Only the order of the clauses is obligatory.
As mentioned, the WHERE clause works on the row-level, whereas the HAVING clause works on the group-level. First, the WHERE is evaluated, next the GROUP BY, next the HAVING, next the ORDER BY, and at the end the SELECT. Every step is based on the results of the previous one.
Finally we offer two additional examples:

## Exercises

Are there persons born on the same day in the same city? Hint: group over both criteria
Categorise persons according to the formula: 'round (weight / 10)': 10 to 19 kg --> 1, 20 to 29 kg --> 2, ... How much persons exist in each category?
Which contact type is used in which frequency in table contact?
Restrict previous result to contact types which occurs more than once.
Are there persons performing more than 2 hobbies? Hint: check table person_hobby.
Are there persons performing only one hobby?
Are there persons performing no hobby?

# SELECT: IS NULL Predicate

When creating new rows, it may occur that we don't know the value of one or more columns.
Let's assume that we want to store information about banking accounts, and for one of those accounts we don't know the balance. What can we do? There are several possibilities:
   * Reject the whole row with all other information like account number, dispositional credit, interest rate, ... . Not very attractive.
   * Store a default value instead of the value we currently don't know. But there are cases where it is impossible to define a default value because every value is possible, e.g., a bank account of '0' or '-1' is not unusual.
   * Store a flag that signals that no value is stored. This approach is similar to the Not-a-Number technique.
Relational DBMS uses the last mentioned technique, and the sense of the flag is 'there is no value stored'. Sometimes people say 'The NULL value is stored' or 'The NULL special marker is stored'.

## Extension of Boolean Logic

Assume there is a table for banking accounts, and some of its rows hold the NULL special marker in the column balance. Do those rows fulfill at least one of the two WHERE conditions 'balance >= 0' or 'balance <= 0'? No. It is not possible to decide whether these conditions are true or false! Honestly, we must admit that we don't know an answer in our usual true/false logic because we don't know a value for balance. We are forced to extend the range of boolean values with a third one, which we call unknown. The two conditions above evaluate neither true nor false; both evaluate to 'unknown' for rows where balance holds the NULL special marker.
In a later stage, we need definitions for the boolean operators NOT, AND, OR, and EQUAL when true/false interact with unknown. You find the definitions here.

## Retrieve the NULL Special Marker

Within every SELECT command, such rows become part of the resulting rows, in which the WHERE condition evaluates to true. If it evaluates to false or unknown, the row will be rejected. As all WHERE conditions like the above 'balance >= 0' - and also their negation - evaluates to unknown for missing balance values, there is preliminary no way to retrieve them.
To overcome this lack, SQL contains the particular phrase 'IS NULL'. The wording 'balance IS NULL' evaluates to true for precisely the rows with a missing value in balance.
We must use exactly this wording. The use of any arithmetic operator like >, <=, !=, ... will not retrieve rows with the NULL special marker. The same holds true even for the condition '(balance = 0) OR NOT (balance = 0)', which is a tautology in conventional true/false logic. Besides this IS NULL predicate, there is no other way to retrieve the NULL special marker - without one simple but not helpful exception: if you omit the WHERE condition, all rows of the table are retrieved, with and without NULL special marker in any column.
That's all! Dealing with NULL special marker and the 3-value-logic might sound strange if you first met this topic. But as the IS NULL predicate always evaluates to true or false, everything works as usual afterward. We can use all other elements of the SELECT command (boolean logic, join, having, order by, ...) in the same way we have done so far.

## Some Examples

Our test database does not contain the NULL special marker. Nevertheless, we have met the situation during the explanation of OUTER joins. OUTER joins create resulting rows where some columns contain the NULL special marker. We must consider this possibility if we deal with the results of such subselects.
There are two other ways to generate the NULL special marker.
   * INSERT or UPDATE command with the explicit notion of the NULL special marker. In this case, the SQL keyword null is used as a representative for the NULL special marker.
   * INSERT command without using all columns. The omitted columns will get the NULL special marker - or a default, if one is defined.
To demonstrate this and to create some examples for the following exercises, we put one row into the person table with some columns left empty.
Next we show the use of the UPDATE command in combination with the key word NULL
Restore the original state of the example database.

## Coalesce and Similar Functions

In the context of the NULL special marker it is often the case that we have to retrieve rows with no value (the NULL special marker) or a default value such as 0 or blank. In such cases, the WHERE condition looks something like this: "... WHERE (col IS NULL OR col = 0) ...". To keep source code simpler, the SQL standard defines a function coalesce(<expression_1>, <expression_2>). If the first argument, which normally is the name of a column, is not NULL, the function evaluates to this argument - else to the second argument.
Example:
The function name coalesce results from the fact that the function accepts an arbitrary number of parameters and recursively evaluates them. If parameter n results in a real value, it evaluates to this parameter, else the function calls itself without the n-th parameter. coalesce(expression_1, expression_2, expression_3) evaluates to expression_1, if expression_1 is not NULL, else to expression_2, if expression_2 is not NULL, else to expression_3.
The SQL standard defines another function nullif(<expression_1>, <expression_2>). It evaluates to NULL, if the two expressions are equal - and it evaluates to the first expression, if they differ from each other.
Different vendors offers some more functions like isnull, ifnull or nvl to support handling of NULL values. The meanings of these functions are vendor specific.

## Exercises

Insert a new hobby 'Snowshoeing' without a remark.
Find a second solution for the above question without using the key word 'null'. (First delete row 10.)
Retrieve all hobbies without a remark.
How many hobbies are exemplified with a remark?
Change row 10 of hobby so that hobbyname contains the string 'NULL' and remark contains 'Name of hobby not known'.
a) Retrieve the row where hobbyname is 'NULL'. b) Retrieve the row where remark is 'Name of hobby not known'.
How many hobbies have a hobby name?

# SELECT: Predefined Functions

There are two groups of predefined functions:
   * aggregate functions. They work on a set of rows, which means they receive one value for each row of a set of rows and returns one value for the whole set. If they are called in the context of a GROUP BY clause, they are called once per group, else once for all rows.
   * scalar functions. They work on single rows, which means they receive one value of a single row and returns one value for each of them.

## Aggregate functions

They work on a set of rows and return one single value like the number of rows, the highest or lowest value, the standard deviation, etc. The most important aggregate functions are:
| Signatur | Semantic |
| COUNT(*) | The number of rows |
| COUNT(<column name>) | The number of rows where <column name> contains a value (IS NOT NULL). The elimination of rows with the NULL special marker in the considered column applies to all aggregate functions. |
| MIN(<column name>) | Lowest value. In the case of strings according to the sequence of characters. |
| MAX(<column name>) | Highest value. In the case of strings according to the sequence of characters. |
| SUM(<column name>) | Sum of all values |
| AVG(<column name>) | Arithmetic mean |
As an example we retrieve the maximum weight of all persons:
A Word of Caution Aggregate functions result in one value for a set of rows. Therefore it is not possible to use them together with 'normal' columns in the projection (the part behind SELECT keyword). If we specify, for example,
we try to instruct the DBMS to show a lot of rows containing the lastname simultaneously with one value. This is a contradiction and the system will throw an exception. We can use a lot of aggregate functions within one projection but we are not allowed to use them together with 'normal' columns.
Grouping If we use aggregate functions in the context of commands containing a GROUP BY, the aggregate functions are called once per group.
In such cases the GROUP BY column(s) may be displayed as it is impossible that they change within the group.

### The NULL special marker

If a row contains no value (it holds the NULL special marker) in the named column, the row is not part of the computation.

### ALL vs. DISTINCT

The complete signatures of the functions are a little more detailed. We can prepend the column name with one of the two key words ALL or DISTINCT. If we specify ALL, which is the default, every value is part of the computation, else only those, which are distinct from each other.
function_name ([ALL|DISTINCT]<column name>) COUNT (DISTINCT weight) -- as an example

### Hint

The standard defines some more aggregate functions to compute statistical measures. Also the keywords ANY, EVERY and SOME formally are defined as aggregate functions. We will discuss them on a separate page.

## Scalar functions

Scalar functions act on a 'per row basis'. They are called once per row and they return one value per call. Often they are grouped according to the data types they act on:
   * String functions
       SUBSTRING(<column name> FROM <pos> FOR <len>) returns a string starting at position <pos> (first character counts '1') in the length of <len>.
       UPPER(<column name>) returns the uppercase equivalent of the column value.
       LOWER(<column name>) returns the lowercase equivalent of the column value.
       CHARACTER_LENGTH(<column name>) returns the length of the column value.
       TRIM(<column name>) returns the column value without leading and trailing spaces.
       TRIM(LEADING FROM <column name>) returns the column value without leading spaces.
       TRIM(TRAILING FROM <column name>) returns the column value without trailing spaces.
   * Numeric functions
       SQRT(<column name>) returns the square root of the column value.
       ABS(<column name>) returns the absolute value of the column value.
       MOD(<column name>, <divisor>) returns the remaining of column value divided by divisor.
       others: FLOOR, CEIL, POWER, EXP, LN.
   * Date, Time & Interval functions
       EXTRACT(month FROM date_of_birth) returns the month of column date_of_birth.
   * build-in functions. They do not have any input parameter.
       CURRENT_DATE returns the current date.
       CURRENT_TIME returns the current time.
There is another wikibook where those functions are shown in detail. The data type of the return value is not always identical to the type of the input, e.g. 'character_length' receives a string and returns a number.
Here is an example with some scalar functions:

## Exercises

What is the hightest id used so far in the hobby table?
Which lastname will occur first in an ordered list?
Are there aggregate functions where it makes no difference to use the ALL or the DISTINCT key word?
Show persons with a short firstname (up to 4 characters).
Show firstname, lastname and the number of characters for the concatenated string. Find two different solutions. You may use the character_length function to compute the length of strings and the concat function to concatenate strings.

# SELECT: Set Operations

Tables, views, and results of SELECT commands are in somewhat similar to sets of set theory. In this comparison the elements of sets correspond to rows of tables, views, and SELECT results. The differences between set theory and the itemized SQL constructs are:
   * Sets of set theory do not allow duplicates, whereas SQL allows duplicates. (Even different rows of one table may be identical as there is no duty to use the concept of primary keys.) In the following, we use the term multiset when we speak about sets in SQL where duplicates are possible.
   * Sets of set theory and multisets are not ordered. But for the result of a SELECT command, we can enforce an ordering through the optional ORDER BY clause.
The comparison between set theory and SQL goes even further. In SQL, we have operations which act on multisets in the sense of set theory: The SQL operations UNION, INTERSECT, and EXCEPT (some name it MINUS) process intermediate multisets generated by differents SELECT commands. The operations expect the multisets are of the same type. This means mainly that they must have the same number of columns. Also, their data type should correlate, but this is not mandatory. If they do not, the DBMS will cast them to a common data type - if possible.

## UNION

The UNION operation pushes the result of several SELECT commands together. The result of the UNION contains those values, which are in the first or the second intermediate result.
This is a single SQL command. It consists of two SELECTs and one UNION operation. The SELECTs are evaluated first. Afterward, their results are pushed together to one single result. In our example, the result contains all lastnames and firstnames in a single column (our example may be of limited help in praxis, it's only a demonstration for the UNION).
DISTINCT / ALL If we examine the result carefully, we will notice that it consists only of 17 values. The table person contains ten rows so that we probably expect twenty values in the result. If we perform the 'SELECT firstname ...' and 'SELECT lastname ...' as separate commands without the UNION, we receive for both commands ten values. The explanation for the three missing values is the UNION command. By default, UNION removes duplicates. Therefore some of the intermediate values are skipped. If we want to obtain the duplicate values we have to modify the UNION operation. Its behavior can be changed with one of the two keywords DISTINCT or ALL. DISTINCT is the default, and it removes duplicate values as we have seen before. ALL will retain all values, including duplicates.
A hint for Oracle users: The use of the keyword DISTINCT, which is the default, is not accepted by Oracle. Omit it.
General hint In most cases, the UNION combines SELECT commands on different tables or different columns of the same table. SELECT commands on the same column of a single table usually use the WHERE clause in combination with boolean logic.

## INTERSECT

The INTERSECT operation evaluates to those values, which are in both intermediate results, in the first as well as in the second.
A hint to MySQL users: MySQL (5.5) does not support INTERSECT operation. But as it is not an elementary operation, there are workarounds.

## EXCEPT

The EXCEPT operation evaluates to those values, which are in the first intermediate result but not in the second.
A hint to MySQL users: MySQL (5.5) does not support the EXCEPT operation. But as it is not an elementary operation, there are workarounds. A hint to Oracle users: Oracle uses the keyword MINUS instead of EXCEPT.

## Order By

We can combine set operations with all other elements of SELECT command, in particular with ORDER BY and GROUP BY. But this may lead to some uncertainties. Therefore, we would like to explain some of the details below.
To which part of the command belongs the ORDER BY? To the first SELECT, the second SELECT, or the result of the UNION? The SQL rules determine that set operations are evaluated before ORDER BY clauses (as always parenthesis can change the order of evaluation). Therefore the ORDER BY sorts the final result and not any of the intermediate results.
We rearrange the example in the hope that things get clear.
First, the two SELECTS are evaluated, afterward the UNION. This intermediate result gets the name 't'. 't' is ordered.
Often one would like the rows from the first SELECT to be ordered independent from the rows of the second SELECT. We can do this by adding a virtual column to the result of each SELECT statement and using the virtual columns in the ORDER BY.

## Group By

With the GROUP BY clause, things are a little more complicated than with ORDER BY. The GROUP BY refers to the last SELECT or - to say it the other way round - to the SELECT of its direct level.

## Exercises

Show the lowest, highest and mean weight as a) 3 values of 1 row and b) 1 value in 3 rows.
Extend the previous 3-rows-solution to meet two additional criteria: a) consider only persons born in San Francisco and b) add a virtual column to show 'Min', 'Max' and 'Avg' according to the correlating numeric values.
Extend the previous solution to order the result: the minimum value first, followed by the average and then the highest value.
Create a list of lastnames for persons with a weight greater than 70 kg together with
all e-mail values (one value per row). There is no concordance between lastnames and e-mails. (This example is not very helpfull for praxis, but instructive.)
In the previous example the lastname 'de Winter' is shown only once. But there are more than one persons of the family with a weight greater than 70 kg. Why? Extend the previous solution to show as much resulting rows as hits to the criteria.

# SELECT: Case Expression

Sometimes it's necessary to translate stored values (or values to be stored) from one representation to another. Suppose there is a column status with legal values from 0 to 9, but the end-users should receive strings that explain the meaning of the numeric values in short, eg.: 'ordered', 'delivered', 'back delivery', 'out of stock', ... . The recommended way to do this is a separate table where the numeric values maps to the explanatory strings. Notwithstanding this, application developers may favor a solution within an application server.
The CASE expression, which is shown on this page, is a technique for solving the described situation as part of a SELECT, INSERT or UPDATE command as well as solving additional problems. As part of the language, it's a powerful term that can be applied at plenty of places within SQL commands. On this page, we focus on its use together with the SELECT command. The strategy and syntax for CASE within INSERT and UPDATE are equivalent and are presented over there. In comparison with the recommended technique of a separate table for the translation, the CASE expression is much more flexible (which is not an advantage in all cases).

## Two Examples

The CASE expression is introduced with its keyword CASE and runs up to the END keyword. In this first example, it specifies a column name and a series of WHEN/THEN clauses with an optional ELSE clause. The WHEN/THEN clauses are compared and evaluated against the values of the named column, one after the other. If none of them hits, the ELSE clause applies. If there is no ELSE clause and none of the WHEN/THEN clauses hit, the NULL special marker will be applied.
The comparison between the values of the column and the fixed values within the WHEN/THEN clause is done solely by "=" (equals). This is a good starting point, but real applications need more than that. Therefore there is a variant of the CASE.
The crucial point is the direct succession of the two keywords CASE and WHEN. There is no column name between them. In this variant, there must be a complete expression, which evaluates to one of the 3-value-logic terms true, false or unknown, between each WHEN and THEN. Now it is possible to use all the comparisons and boolean operators as they are known by the WHERE clause. It is even possible to compare different columns or function calls with each other.

## Syntax

There are the two variants simple case and searched case.
The simple case is limited to one column and the use of the equal operator, whereas the searched case may evaluate arbitrary columns of the (intermediate) result with arbitrary operators, functions or predicates.

## Typical Use Cases

The use of CASE expressions is not limited to projections (the column list between SELECT and FROM). As the clause evaluates to a value, it can be applied as a substitution for values at several places within SQL commands. In the following, we offer some examples.
ORDER BY clause
Sort contact values in the order: all fixed lines, all mobile phones, all emails, all icq's. Within each group sort over the contact values.
In the next example, persons are ordered by weight classes, within the classes by their name.
WHERE clause
Within the WHERE clauses, there may occur fixed values or column names. CASE expressions can be used as a substitution for them. In the example, persons receive a discount on their weight depending on their place of birth (consider it as a theoretical example). Thus Mr. Goldstein, with its 95 kg, counts only with 76 kg and is not part of the result set.

## Exercises

Show firstname, lastname and the gender of all persons. Consider Larry, Tom, James, John, Elias, Yorgos, Victor as 'male', Lisa as 'female' and all others as 'unknown gender'. Use a simple case expression.
Use a searched case expression to solve the previous question.
Show firstname, lastname and a classification of all persons. Classify persons according to the length of their firstname. Call the class 'short name' if character_length(firstname) < 4, 'medium length' if < 6, 'long name' else.

# SELECT: Subquery

A subquery is a complete SELECT command which is used within another SELECT, UPDATE, INSERT or DELETE command. The only difference to a simple SELECT is, that it is enclosed in parenthesis.

## Classification

Depending on the type of the created result there are three classes of subqueries:
   * Scalar Value Subquery: The subquery returns one single value, e.g: (SELECT max(weight) FROM person).
   * Row Subquery: The subquery returns one single row of one or more values, e.g: (SELECT min(weight), max(weight) FROM person).
   * Table Subquery: The subquery returns a list of rows, which is a table, e.g: (SELECT lastname, weight FROM person). For the classification, it makes no difference whether the resulting list contains zero, one, or more rows. The demarcation between a table subquery and a row subquery is that potentially more than one row may occur.
Every type can be used on all positions where the type it stands for may occur: the scalar value subquery where a single value may occur, the row subquery where a single row may occur and the table subquery where a table may occur. Additionally, table subqueries may occur as an argument of an EXISTS, IN, SOME, ANY or ALL predicate.
Independent from this classification subqueries may be correlated subqueries or non-correlated subqueries. Correlated subqueries have a correlation to the surrounding query by the fact that they use values from the surrounding query within the subquery. Non-correlated subqueries are independent of the surrounding query. This distinction is shown in detail in the next chapter but applies also to the other two subquery classes.
Because correlated subqueries use values, which are determined by the surrounding query and may change from row to row, the subquery is executed - conceptually - as often as resulting rows of the surrounding query exist. This might lead to performance problems. Nevertheless correlated subqueries are an often used construct. In many cases, there are equivalent constructs which use a JOIN. Which one shows the better performance depends highly on the DBMS, and the number of involved rows, the existence of indices, and a lot more variables.

## Scalar Value Subquery

The first example creates a list of lastnames, weights and the average weight of all persons.
Because the subquery uses the avg function, the SQL compiler knows that it will return exactly one single value. Therefore it's type is Scalar Value Subquery and can be used on positions where scalar values may occur, e.g. in the list between SELECT and FROM.
In the next example, the subquery is used as a deputy for a value within the WHERE clause.
Both examples use the table person twice. One can also use different tables. There is no dependency between the table name in the subquery and in the surrounding query. This applies to all classes of correlated and non-correlated subqueries. The subqueries may retrieve any value from any other table, e.g. the number of contacts.
These first two examples show non-correlated subqueries, which means, that the subqueries are independent from the queries in which they are embedded. They are executed only once.
But often an application faces a situation, where the subquery must use values from the outside query (similar to subroutines which uses parameters). This kind of subquery is called a correlated subquery. As an example, the next query lists persons together with the average weight of their family.
The subselect gets one row of the surrounding SELECT after the next as an parameter with the name 'p'. Within the subselect all columns of the row 'p' are known and may be used. Here the family name from the outside row is used in the subquery to find all persons within the family and the average weight of the family members.
Be careful: Correlated subqueries are executed once per row of the surrounding query. Therefore they are much more costly than non-correlated subqueries. There might exist an equivalent solution using JOIN or GROUP BY which works with better performance. The query optimizer of the DBMS internally might rearrange the given subquery into one of the equivalent forms. But this does not work in all cases.
The distinction between correlated and non-correlated subqueries is universal. It applies also to the other subquery classes.

## Row Subquery

This example retrieves one or more persons, whose firstname is the lowest (in the sense of the lexical order) of all firstnames and whose lastname is the lowest of all lastnames. Because of the AND condition it might be the case that no person is found.
Within the subquery, the lowest first- and lastnames are retrieved. The use of the min function guarantees that not more than one row with two columns will arise - therefore it is a row subquery. In the surrounding query, this intermediate result is compared with each row of the complete table person or - if present - an index is used.
It is fortunate that the command retrieves a row. In most cases, the lowest first- and lastname results from different persons. But also in those cases, the command is syntactically correct and will not throw any exception.
In the next example, persons with the lowest first- and lastnames within every family are retrieved. To do so, it is necessary to use a correlated row subquery.
Again, there are the two incarnations of table person, one with the alias name 'p' in the surrounding query and one with the alias name 'sq' in the subquery. The subquery is called once per resulting row of the surrounding query, because the 'p.lastname' may change with every row of 'p'.
Within every family there is at least one person which achieves the condition - it is also conceivable that several persons achieve the condition.

## Table Subquery

The next example retrieves persons who have a contact. The class of the subquery is: non-correlated table subquery (used as a condition in the IN predicate).
The subquery creates multiple rows with one column for each of them. This constitutes a new, intermediate table. Therefore this example is a table subquery.
The IN operator is able to act on this intermediate table. In contrast, it is not possible to use operators like '=' or '>' on this kind of intermediate result. In this case, the SQL compiler will recognize a syntax error.
The next example is an extension of the first one. It adds a correlation criterion between the query and the subquery by requesting the lastname within an email-address.
The last comparison after the AND is a little bit complex. It uses the functions CONCAT and UPPER as well as the predicate LIKE, but this is not of interest for the actual topic 'subquery'. The important part is that the subquery refers to 'p.lastname' of the surrounding query. Only Mr. Goldstein meets the criterion that his e-mail address contains his lastname when the two columns are compared case-insensitive.
Remark: CONCAT concatenates two strings. UPPER converts a string to the upper-case. LIKE in combination with the '%' sign looks for one string within another.
Next, there is an example where a non-correlated table subquery is object to a join operation.

## Another Example

The example shows a solution to a common problem. Sometimes there are rows describing an outdated stage of entities. Those rows - for one logical entity - differ from each other in some columns and there is an additional column version to track the time flow.
Here is the example table booking and its data.
The problem is to retrieve all actual rows, which are those with the highest version number within each booking. Bookings are considered to be the same if they have the same booking_number.
The first solution uses a non-correlated table subquery.
The subquery creates a list of booking numbers together with their highest version. This list is used by the surrounding query to retrieve the required rows with all its columns.
The second solution uses a correlated scalar value subquery.
The surrounding query retrieves all rows of the table. For each of them, it calls the subquery, which retrieves the highest version within this booking_number. In most cases, this highest version differs from the version of the actual row and because of the '=' operator those rows are not part of the result. Only those, whose version is equal to the value determined in the subquery (and whose booking_number is the same as those used in the subquery) are part of the final result.
A variation of the introducing question may be to retrieve only historical rows (all versions except the highest one) for one special booking.
The surrounding query restricts the rows to those of one special booking. The subquery is called only for those rows.
It's easy to run into pitfalls:
The above query returns all versions of booking 4711 including the actual one! To get the expected result, it's necessary to 'link' the surrounding query and the subquery together.

## Exercises

Find the booking with the most versions.
Find all bookings with are canceled (in the latest version).
Create a list of all persons. For each person include the number of persons born in the same city as the person.
Create a list of all persons together with the number of their contact information.
Create a list of all persons together with the number of their e-mail-addresses.
Create a list of all persons together with the number of their contact information. (Same question as above.) Replace the subquery by a JOIN construct.
For which persons there are NO contact information?

# Views

Often users and applications request information in a form which differs from the structure of existing tables. To achieve those requests the SELECT command offers plenty of possibilities: projections, joins, group by clause, and so on. If there are always the same requests, which is the case particularly for applications, or if the table structure intentionally should be hidden from the application-level, views can be defined. Furthermore, the access rights to views may be different from those to tables.
Views look like a table. They have columns of a certain data type, which can be retrieved in the same way as columns of a table. But views are only definitions, they don't have data of its own! Their data is always the data of a table or is based on another view. A view is a different sight to the stored data or somewhat like a predefined SELECT.

## Create a View

One creates a view by specifying its name, optionally column names, and especially the SELECT command on which the view is based. Within this SELECT all elements are allowed in the same way as in a standalone SELECT command. If no column names are specified the column names of the SELECT are used.

## Examples and Explanations
### Example 1: Hide Columns

As a first example here is the view person_view_1 which contains all but id and ssn columns of table person. Users who have the right to read from this view but not from the table person don't have access to id and ssn.
As indicated in the above 'order by' example it is possible to use all columns of the view (but not all of the table!) within any part of SELECTs to the view: in the projection, the WHERE, ORDER BY, GROUP BY and HAVING clauses, in function calls and so on.

### Example 2: Rename Columns

Next, there is a renaming of a column. The column name lastname of the table will be familyname in the view.

### Example 3: Apply WHERE Condition

Not only columns can be hidden in a view. It's also possible to hide complete rows, because the view definition may contain a WHERE clause.
This view contains only persons born in San Francisco or Richland. All other persons are hidden. Therefore the following SELECT retrieves nothing although there are persons in the table which fulfill the condition.

### Example 4: Use Functions

This example uses the sum function.
In this example, the elaborated construct 'GROUP BY / HAVING' is hidden from users and applications.

### Example 5: Join

Views can contain columns from several tables by using JOIN commands. The following example view contains the name of persons in combination with the available contact information. As an INNER JOIN is used, some persons occur multiple, others not at all.
The columns person.id an contact.person_id are used during the definition of the view. But they are not part of the projection and hence not available for SELECTs to the view.
Hint: The syntax and semantic of join operations is explained on a separate page.

## Some more Hints

Within a CREATE VIEW statement, one may use more elements of the regular SELECT statement than it is shown on this page, especially: SET operations, recursive definitions, CASE expressions, ORDER BY, and so on.
If there is an ORDER BY clause within the CREATE VIEW and another one in a SELECT to this view, the later one overwrites the former.

## Write Access via Views

In some cases, but not in general, it should be possible to change data (UPDATE, INSERT or DELETE command) in a table by accessing it via a view. Assume, as a counterexample, that one wants to change the column count_of_members of person_view_4 to a different value. What shall the DBMS do? The column is subject to an aggregate function which counts the number of existing rows in the underlying table. Shall it add some more random values into new rows respectively shall it delete random rows to satisfy the new value of count_of_members? Of course not!
On the other hand, a very simple view like 'CREATE VIEW person_0 AS SELECT * from person;', which is a 1:1 copy of the original table, should be manageable by the DBMS. Where is the borderline between updateable and non-updateable views? The SQL standard does not define it. But the concrete SQL implementations offer limited write-access to views based on their own rules. Sometimes these rules are very fixed, in other cases they consist of flexible techniques like 'INSTEAD OF' triggers to give programmers the chance to implement their own rules.
Here are some general rules which may be part of the implementors fixed rules to define, which views are update-able in this sense:
   * The view definition is based on one and only one table. It includes the Primary Key of this underlying table.
   * The view definition must not use any aggregate function.
   * The view definition must not have any DISTINCT-, GROUP BY- or HAVING-clause.
   * The view definition must not have any JOIN, SUBQUERY, SET operation, EXISTS or NOT EXISTS predicate.
   * Many implementations have a keyword which can be used to force a view to be read-only, even if technically it doesn't have to be.
If it is possible to use the UPDATE, INSERT or DELETE command to a view, the syntax is the same as with tables.

## Clean up the Example Database

The DROP VIEW statement deletes a view definition. In doing so the data of the underlying table(s) is not affected.
Don't confuse the DROP command (definitions) with the DELETE command (data)!

## Exercises

Create a view 'hobby_view_1' which contains all columns of table 'hobby' except 'id'. Rename column 'remark' to 'explanation'. Create two different solutions.
Create a view 'hobby_view_2' with the same criteria as in the previous example. The only difference is that the length of the explanation column is limited to 30 character. Hint: use the function substr(<column name>, 1, 30) to determine the first 30 characters - this is not part of the SQL standard but works in many implementations.
Create a view 'contact_view_3' which contains all rows of table contact with the exception of the 'icq' rows. Count the number of the view rows and compare it with the number of rows in the table 'contact'.
Create a view 'contact_view_4' which contains one row per contact type with its notation and the number of occurrences. Afterwards select those which occur more than once.
Create a view 'person_view_6' which contains first- and lastname of persons plus the number of persons with the same name as the person itself (family name). Hint: the solution uses a correlated subquery.
Clean up the example database.

# INSERT 1

Hint: Be carefull and deactivate AUTOCOMMIT.
The INSERT command stores one or more new rows into one table. The content of the new rows consists of either fixed values or results from a SELECT, which is evaluated at runtime. So there are two different syntaxes to do the job.

## Static Insert

Following the tablename, we can list the affected columns and after the keyword 'VALUES' state one or more lists of values to insert. Each list of values represents one new row. The lists of columns and values have to be in accordance such that the quantity of list entries is identical and their data types correlate.
We can choose any order of columns, but column names and values should be in concordance.
We can omit unnecessary columns.
Clean up your table.

## Dynamic Insert

Unlike in the above paragraph, we can insert values that are not fixed but dynamic such that they are evaluated at runtime from any table, a function, or a computation. Even the number of new rows can be dynamic. All this is done in a subselect which replaces the VALUE clause. We explain this technique at page Advanced Insert. The other rules concerning number and sequence of columns or omitting values retain their validity.

## Exercises

Insert a new row for Mr. Peter Hufington with its body weight of 67 kg. He is born in LA.

# INSERT 2

Hint: Be carefull and deactivate AUTOCOMMIT.
The basic syntax and semantic of the INSERT command is described on the page INSERT. There are examples of how to insert single rows with fixed values into a table. The present page describes how to dynamize the command by the use of subqueries.

## Evaluate Values at Runtime

First, the values to be inserted may be evaluated in a relatively strict way by reading the system time or other (quasi) constants.
Next, the values to be inserted may be evaluated by a scalar value subquery. This means, that single values may be computed at runtime based on the rows of the same or another table.

## Evaluate Rows at Runtime

Similar to the previous example of a single scalar value from a scalar value subquery, one can use a table subquery to get several rows and insert them into the specified table within one INSERT command. This version is able to insert thousands of rows with one single statement. In addition to its dynamic nature, it saves all but one round-trip between the application and the DBMS and therefore is much faster than a lot of single row-based INSERTs.
The syntax is changed such that a complete subquery replaces the keyword 'VALUES' with its list of values (often named 'subselect'), which starts with the keyword 'SELECT'. Of course, the number and type of the selected columns must correlate with the number and type of the columns in the specified column list that follows the 'INSERT INTO' keyword. Within the subquery, the complete power of the SELECT statement may be used: JOIN, WHERE, GROUP BY, ORDER BY, and especially other subqueries in a recursive manner. There is a wide range of use cases: create rows with increased version numbers, with percentage increased salary, with the actual timestamp, fixed values from rows of the same or another table, ... .
The technique shown at Structured Query Language/Example Database Data, which multiplies existing data, e.g., for testing purpose, is based on such table subqueries.

## Exercises

Insert a new person with id 1301, firstname 'Mr. Mean', lastname is the lowest lastname (in the sense of the character encoding, use min function). Its weight is the average weight of all persons (use avg function).
Insert one additional person per family (=lastname) with firstname 'An extraordinary family member', lastname is the family name. Incorporate only the rows from the original example database with id <= 10.
Clean up your database.

# UPDATE 1

Hint: Be careful and deactivate AUTOCOMMIT.
The UPDATE command modifies one or more existing rows.
Values are assigned to the named columns. Unmentioned columns remain unchanged. The search_condition acts in the same way as in the SELECT command. It restricts the coverage of the command to those rows that satisfy the specified criteria. If the WHERE keyword and the search_condition are omitted, all rows of the table are affected. It is possible to specify search_conditions which hit no rows. In this case, no rows are updated - and no error or exception occurs.
As shown previously in the introductory page, the UPDATE command is very flexible. Within one command , we can choose an arbitrary combination of the two features:
* change one or more columns
* change zero, one or more rows - depending on the search_condition.
Additionally, values can have a dynamic nature like system date, timestamp, or timezone - depending on your implementation.
For a recapitulation here is an example:

## Further Information

We present some more information about additional opportunities of the UPDATE command here.

## Exercises

Change column remark of row Fishing in table hobby to: 'Catching fishes with or without tools.'.
Change column remark of all rows in table hobby to: 'A creative leisure activity.'. Check the success of the command without COMMIT-ting. Revert the changes.

# UPDATE 2

Hint: Be careful and deactivate AUTOCOMMIT.
The page in hand offers two additional techniques as an extension to the UPDATE command shown on one of the previous pages:
   * Computing values, which are assigned to a column, at runtime.
   * Using complex subqueries as search conditions in the WHERE clause.

## Evaluate Values at Runtime

The values which are assigned to a column may be computed by a correlated or non-correlated scalar value subquery on the involved table or another one. There are many use cases where this technique is utilized: Increase values linear or in percentage, use values from the same or another table, ... . The situation is similar to that described on the page about the INSERT command.
The subquery may use the values of the row, which is currently updated. In the next example, persons receive the mean weight of their family. To compute this mean weight, it is necessary to use the column 'lastname' of the actual processed row.

## Subqueries in WHERE Clause

The WHERE clause determines which rows of a table are involved by the UPDATE command. This WHERE clause has the same syntax and semantic as the WHERE clause of the SELECT or DELETE command. It may contain complex combinations of boolean operators, predicates like ANY, ALL or EXISTS, and recursively subqueries as described in SELECT: Subquery.
The command performs an UPDATE in the table person, but the affected rows are identified by a subquery in table contact. This technique of grabbing information from other tables offers very flexible strategies to modify the data.
It is no error to select 0 rows in the subquery. In this case, the DBMS executes the UPDATE command as usual and throws no exception. (The same holds true for subqueries in SELECT or DELETE statements.)

## Exercises

Assign the firstname 'Short firstname' to all persons which have a firstname with less than 5 characters.
Assign the firstname 'No hobby' to all persons which have no hobby.
Assign the firstname 'Sportsman' to all persons performing one of the hobbies 'Underwater Diving' or 'Yoga'.

# DELETE 1

Hint: Be careful and deactivate AUTOCOMMIT.
The DELETE command removes rows from a table.
The syntax is straightforward as we do not need to specify any column name - rows are deleted as a whole and not partly. As usual, the search condition specifies the criterion which identifies the affected rows. It can involve zero, one, or more rows. If we omit the WHERE keyword and the search condition all rows are affected.

## Example

The information about Mr. Burton was deleted and restored again.

## Further Information

We present some more information about the DELETE command here. There are also some comments to the interconnection with the TRUNCATE command.

## Exercises

Delete the hobby 'Yoga'.
Delete all relations between persons and hobbies. Check result. Restore all rows.

# DELETE 2

Hint: Be carefull and deactivate AUTOCOMMIT.
Because the DELETE command deletes rows as a whole and not partly, the syntax is straightforward. Its structure was shown on a previous page. The page on hand offers only one addition: The WHERE clause isn't limited to simple conditions like 'id = 10' but may contain a subquery. This gives the command much more flexibility.
The use of subqueries as part of a DELETE command is identical to its use within an UPDATE or SELECT command.
There is another command for the deletion of rows. The TRUNCATE command is very similar to DELETE. TRUNCATE deletes all rows of a table and shows better performance. But it has no mechanism to choose individual rows.

## Example

The example command deletes contact information from persons who are born in San Francisco.
Correlated subqueries in combination with DELETE commands, are not supported by all implementations.
It often happens that the DBMS rejects DELETE commands because Foreign Key constraints will be violated during its execution. E.g.: if the command tries to delete a person to whom a contact or hobby information is known, the command fails (as a whole). To overcome such situations, there are different strategies:
   * Delete all dependent rows prior to the intended row.
   * Define the Foreign Key constraint as DEFERRED (it will be check not before COMMIT) and delete the depending rows before or after the intended one.
   * Define the Foreign Key constraint as CASCADE. In this case, the depending rows will be deleted automatically.

## Exercise

Delete hobby information for family Goldstein.

# MERGE

Hint: Be carefull and deactivate AUTOCOMMIT.
In many cases, applications want to store rows in the database without knowing whether these rows previously exist in the database or not. If the rows exist, they must use the UPDATE command, if not, the INSERT command. To do so, the following construct is often used:
This situation is unpleasant in many ways:
* There are two roundtrips between application and DBMS, either SELECT + INSERT or SELECT + UPDATE.
* The application must transfer one row after the other. A 'bulk storing' is not possible because the evaluation of the criterion which decides between INSERT and UPDATE may lead to different results from row to row.
* The syntax is spread across three SQL statements. This is error-prone.
To overcome the disadvantages, the SQL standard defines a MERGE command, which contains the complete code shown above in one single statement. The MERGE performs an INSERT or an UPDATE depending on the existence of individual rows at the target table.

# SQLSTATE

Programs using a database API that conforms to the SQL standard receive an indication about the success or failure of the call. This return code - which is called SQLSTATE - consists of 5 bytes. They are divided into two parts: the first and second byte contains a class and the following three a subclass. Each class belongs to one of four categories: "S" denotes "Success" (class 00), "W" denotes "Warning" (class 01), "N" denotes "No data" (class 02) and "X" denotes "Exception" (all other classes).
The values of SQLSTATE are published and explained in Wikipedia.

# Glossary

| ACID | An acronym for the four properties atomicity, consistency, isolation and durability. Any transaction must conform to them. Atomicity means that either all or no data modification will take place. Consistency ensures that transactions transform the database from one valid state to another valid state. Isolation requires that transactions will not affect each other, even if they run at the same time. Durability means that the modifications will keep into the database even if the system crashes immediately after the transaction. q.v.: ACID |
| Attribute | A set of properties (name, datatype, size, ...) used to characterize the data items of entities. A group of attributes constructs an entity-type (or table), i.e.: all values of a certain column must conform to the same attributes. Attributes are optionally complemented by constraints. |
| Block | Aggregation of one or more physical blocks of a mass device. Usually, a block contains numerous rows of one or more tables. Sometimes one row is distributed across several blocks. q.v.: dirty block |
| Clause | A certain language element as part of a statement. E.g.: the WHERE clause defines search criteria. |
| Column | A set of values of a single table which resides on the same position within its rows. |
| Constraint | Similar to attributes, constraints define rules at a higher level, data items must conform to. E.g.: nullability, primary and foreign key, uniqueness, default value, user-defined criteria like STATUS < 10. |
| Cursor | A cursor is a mechanism by which the rows of a table may be acted on (e.g., returned to a host programming language) one at a time. |
| Database | A set of tables. Those tables contain user data and the Data Dictionary. |
| Database Management System (DBMS) | A set of computer programs that controls the creation, maintenance and usage of the database. q.v.: DBMS |
| Data Dictionary (DD) | A set of predefined tables where the DBMS stores information about all user defined objects (tables, views, constraints, ...). |
| Data Control Language (DCL) | A class of statements which defines the access rights to data, e.g: GRANT ..., REVOKE, ... . |
| Data Definition Language (DDL) | A class of statements which defines logical and physical design of a database, e.g.: CREATE TABLE ... . |
| Data Manipulation Language (DML) | A class of statements which retrieves and manipulates data, e.g.: SELECT ..., INSERT ..., UPDATE ..., DELETE ..., COMMIT, ROLLBACK. |
| Dirty Block | A block whose content has changed in memory, but is still not written to disc. |
| Entity | An identifiable object like an employee or a department. An entity is an instance of an entity-type. Usually there are many instances of a certain entity-type. Every entity is stored in one row. Entities of same entity-type are stored in rows of the same table. So entities are a logical construct and rows a physical implementation. |
| Entity-type | A group of attributes describing the structure of entities. As entities of same entity-type are stored in rows of the same table it can be said, that an entity-type describes a table. (Many people tend to use the term entity as a synonym for entity-type.) |
| Expression | A certain language element as part of a statement. It can produce either scalar values or a table. |
| Foreign key | A value used to reference a primary key. It can point to any primary key in the database, whether in its own table (e.g.: bill of materials) or another table. It can point to its own row. |
| Index | An index is a construct containing copies of original values and backreferences to their original rows. Its purpose is the provision of a fast access to the original data. To achieve this, an index contains some kind of collocation. Remark: Indexes are not part of the SQL standard. Nevertheless, they are part of nearly every DBMS. |
| Junction table | If more than one row of table T1 refers to more than one row of table T2 (many-to-many relationship) you need an intermediate table to store this relationship. The rows of the intermediate table contain the primary keys of T1 and T2 as values. q.v.: Junction_table |
| Normalization | Tables should conform to special rules - namely First-, Second- and Third-Normal Form. The process of rearranging columns over tables is called normalization. |
| NULL | If no value is stored in the column of a row, the standard says, that the null value is stored. As this null value is a flag and not a real value, we use the term null marker within this wikibook. The null marker is used to indicate the absence of any data. For example, it makes a difference whether a temperature is measured and stored as 0 degrees or whether the temperature is not measured and hence not stored. One consequence of the existence of the null marker is that SQL must know not only the boolean values TRUE and FALSE but also a third one: UNKNOWN. |
| Predicate | A language element which specifies a non-arithmetic condition. E.g: [NOT] IN, [NOT] LIKE, IS [NOT] NULL, [NOT] EXISTS, ANY, ... . |
| Primary key | A value or a set of values used to identify a single row uniquely. |
| Query | An often used statement, which retrieves data from the database. It is introduced by the keyword SELECT and usually contains a predicate. |
| Relationship | A reference between two different or the same entity. References are not implemented as links. They base upon the values of the entities. |
| Relational Model | A method (and a mathematical theory) to model data as tables (relations), the relationships among each other and all operations on the data. |
| Row | One record in a table containing information about one single entity. A row has exactly one value for each of its columns - in accordance with First Normal Form. This value may be NULL. |
| Statement | A single command which is executed by the DBMS. There are 3 main classes of statements: DML, DDL and DCL. |
| Table (=Relation) | A set of rows of a certain entity-type, i.e. all rows of a certain table have the same structure. |
| Three Valued Logic (3VL) | SQL knows three boolean values: TRUE, FALSE and UNKNOWN. See: NULL. q.v.: trivalent, ternary or three-valued logic (3VL). |
| Transaction | A logical unit of work consisting of one or more modifications to the database. The ACID criterium must be achieved. A transaction is either saved by the COMMIT statement or completely canceled by the ROLLBACK statement. |
| Value | Implementation of a single data item within a certain column of a certain row. (You can think of a cell within a spreadsheet.) |
| View | A virtual table containing only its definition and no real data. The definition consists of a query to one or more real tables or views. Queries to the view are processed as queries to the underlying real tables. |