Structured Query Language/Classic Track Print
Classic Track
The main drive behind a relational database is to increase accuracy by increasing the efficiency with which data is stored. For example, the names of each of the millions of people who immigrated to the United States through Ellis Island at the turn of the 20th century were recorded by hand on large sheets of paper; people from the city of London had their country of origin entered as England, or Great Britain, or United Kingdom, or U.K., or UK, or Engl., etc. Multiple ways of recording the same information leads to future confusion when there is a need to simply know how many people came from the country now known as the United Kingdom.
The modern solution to this problem is the database. A single entry is made for each country, for example, in a reference list that might be called the Country table. When someone needs to indicate the United Kingdom, he only has one choice available to him from the list: a single entry called "United Kingdom". In this example, "United Kingdom" is the unique representation of a country, and any further information about this country can use the same term from the same list to refer to the same country. For example, a list of telephone country codes and a list of European castles both need to refer to countries; by using the same Country table to provide this identical information to both of the new lists, we've established new relationships among different lists that only have one item in common: country. A relational database, therefore, is simply a collection of lists that share some common pieces of information.
Structured Query Language (SQL)
SQL, which is an abbreviation for Structured Query Language, is a language to request data from a database, to add, update, or remove data within a database, or to manipulate the metadata of the database.
SQL is a declarative language in which the expected result or operation is given without the specific details about how to accomplish the task. The steps required to execute SQL statements are handled transparently by the SQL database. Sometimes SQL is characterized as non-procedural because procedural languages generally require the details of the operations to be specified, such as opening and closing tables, loading and searching indexes, or flushing buffers and writing data to filesystems. Therefore, SQL is considered to be designed at a higher conceptual level of operation than procedural languages because the lower level logical and physical operations aren't specified and are determined by the SQL engine or server process that executes it.
Instructions are given in the form of statements, consisting of a specific SQL statement and additional parameters and operands that apply to that statement. SQL statements and their modifiers are based upon official SQL standards and certain extensions to that each database provider implements. Commonly used statements are grouped into the following categories:
- Data Query Language (DQL)
- SELECT - Used to retrieve certain records from one or more tables.
- Data Manipulation Language (DML)
- INSERT - Used to create a record.
- UPDATE - Used to change certain records.
- DELETE - Used to delete certain records.
- Data Definition Language (DDL)
- CREATE - Used to create a new table, a view of a table, or other object in database.
- ALTER - Used to modify an existing database object, such as a table.
- DROP - Used to delete an entire table, a view of a table or other object in the database.
- Data Control Language (DCL)
- GRANT - Used to give a privilege to someone.
- REVOKE - Used to take back privileges granted to someone.
Before learning SQL, relational databases have several concepts that are important to learn first. Databases store the data of an information system. We regroup data by groups of comparable data (all the employees, all the projects, all the offices...). For each group of comparable data, we create a table. This table is specially designed to suit this type of data (its attributes). For instance, a table named employee which stores all the employees would be designed like this:
| id_employee the primary key | an integer |
| firstname a column | a string of characters a column type |
| lastname | a string of characters |
| phone | 10 numbers |
| a string of characters | |
And the company employees would be stored like this:
| id_employee | firstname | lastname | phone | |
|---|---|---|---|---|
| 1 a column value | Big | BOSS | 936854270 | big.boss@company.com |
| 2 | John | DOE | 936854271 | john.doe@company.com |
| 3 | Linus | TORVALDS | 936854272 | linus.torvalds@company.com |
| 4 | Jimmy | WALES | 936854273 | jimmy.wales@company.com |
| 5 | Larry | PAGE | 936854274 | larry.page@company.com |
The data stored in a table is called entities. As a table is usually represented as an array, the data attributes (first name, last name...) are called columns and the records (the employees) are called rows. id_employee is a database specific technical identifier called a primary key. It is used to link the entities from a table to another. To do so, it must be unique for each row. A primary key is usually underlined. Any unique attribute (for instance, the mail) or group of attributes (for instance, the first name and last name) can be the table primary key but it is recommended to use an additional technical id (id_employee) for primary key.
Let's create a second table called project which stores the company projects:
|
| ||||||||||||||||||||||||
And the company projects would be stored like this:
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
id_project is the primary key of the project table and manager is a foreign key. A foreign key is a technical id which is equal to one of the primary keys stored in another table (here, the employee table). Doing this, the Google project is linked to the employee Larry PAGE. This link is called a relationship. A foreign key is usually preceded by a sharp. Note that several projects can point to a common manager so an employee can be the manager of several projects.
Now, we want to create, not a single link, but multiple links. So we create a junction table. A junction table is a table that isn't used to store data but links the entities of other tables. Let's create a table called members which links employees to project:
|
|
| ||||||||||||||||||||||||||||||
And the employees and the projects can be linked like this:
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
An employee can be associated to several projects (John DOE with Google and Wikipedia) and a project can be associated to several employees (Wikipedia with Jimmy, John and Jenny), which is impossible with just a foreign key. A junction table hasn't its own primary key. Its primary key is the couple of foreign keys, as this couple is unique. A junction table can link more than two entity tables by containing more columns.
Relationships
So let's list the different types of relationships:
- One to one,
- One to many (for instance, the manager of a project),
- Many to many (for instance, the members of the projects).
For each type of relationships, there is a way to link the entities :
- One to many relationship: create a foreign key from an entity table to the other,
- Many to many relationship: create a junction table,
- One to one relationship: just merge the two tables.
Now you know how to design a database schema and to put the data of your information system into it.
Data Query Language is used to extract data from the database. It doesn't modify any data in the database. It describes only one query: SELECT.
SQL data types
Each column has a type. Here are the standard SQL data types:
| Data type | Explanation | Allowed values | Example |
| VARCHAR(n) | A string with a maximum length of n | [0-9a-zA-Z]+{n} |
"foo" |
| CHAR(n) | A string with a fixed length of n | [0-9a-zA-Z]{n} |
"foo" |
| SMALLINT | A 16 bits signed integer | \-?[0-9]+ |
584 |
| INTEGER | A 32 bits signed integer | \-?[0-9]+ |
-8748 |
| FLOAT | A decimal floating point | \-?[0-9]+[\.[0-9]+]? |
48.96 |
| NUMBER(n,[d]) | A number with n digits (and d decimal digits if mentioned) | \-?[0-9]+[\.[0-9]+]? |
484.65 |
| DATE | A date (YYYY-MM-DD) | [0-9][0-9][0-9][0-9]\-[0-1][0-9]\-[0-3][0-9] |
2009-03-24 |
| TIME | A time period of sixty minutes; one twenty-fourth of a day | [0-2][0-9]\:[0-5][0-9]\:[0-5][0-9] |
11:24:56 |
| TIMESTAMP | A date and hour | [0-9]+ |
18648689595962 |
| BLOB | Any binary data | Any |
There is no boolean type. Integers are used instead.
SELECT query
The exhaustive syntax of the SELECT query is as follows:
SELECT[ ALL| DISTINCT] <column name>[[ AS] <alias>][,[ ALL| DISTINCT] <column name>[[ AS] <alias>]]*
FROM <table>[[ AS] <alias>|[[ FULL| LEFT| RIGHT] OUTER| INNER] JOIN <table> ON <expression>]
[, <table>[[ AS] <alias>|[[ FULL| LEFT| RIGHT] OUTER| INNER] JOIN <table> ON <expression>]]*
[WHERE <predicate>[{ AND| OR} <predicate>]*]
[GROUP BY <column name>[, <column name>]*
[HAVING <predicate>[{ AND| OR} <predicate>]]*]
]
[ORDER BY <column name>[ ASC| DESC][, <column name>[ ASC| DESC]]*]
[FETCH FIRST <count> ROWS ONLY];
First query
Let's create the table reunion with many columns:
| id_reunion | INTEGER |
| name | VARCHAR(20) |
| description | VARCHAR(255) |
| priority | CHAR(1) |
| planned | SMALLINT |
| date | DATE |
| hour | TIME |
| duration | INTEGER |
| # id_office | INTEGER |
| pdf_report | BLOB |
...and let's fill it:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
Let's do a simple query. The following query just returns the content of the reunion table:
- Query:
SELECT *
FROM reunion;
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |1 |Planning |We need to plan the project. |A |1 |2008-03-24 |10:30:00 |60 |35 |48644...846348 | |2 |Progress |What we have done. |C |1 |2008-05-12 |14:00:00 |30 |13 |9862...15676 | |3 |Change |What we need to change in the project. |B |1 |2008-06-03 |9:30:00 |90 |41 |34876...4846548 | |4 |Presentation |Presentation of the project. |D |0 |2008-09-11 |15:30:00 |120 |27 |NULL | |5 |Reporting |Explanation to the new beginner. |B |1 |2009-03-15 |14:00:00 |60 |7 |19739...37718 | |6 |Learning |A new software version has been installed. |B |1 |2009-09-21 |16:00:00 |120 |11 |785278...37528 | |----------------------------------------------------------------------------------------------------------------------------------------------------|
The form of the result depends on the client application. It can be returned as a text output (backend), a HTML page (thin client), a program object (middleware) etc... The statements, queries, clauses (SELECT, FROM...), instructions and operators are not case sensitive but they are commonly written in uppercase for readability.
The SELECT and FROM clauses are the two required clauses of a SELECT query:
- FROM : list the tables the query uses to return the data,
- SELECT : list the data to return.
WHERE clause
The WHERE clause doesn't influence the columns the query returns but the rows. It filters the rows applying predicates on it. A predicate specifies conditions that can be true or false. SQL can handle conditions whose result is unknown. For example, the following query returns the reunions which have a B priority level:
- Query:
SELECT *
FROM reunion
WHERE reunion.priority = 'B';
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |3 |Change |What we need to change in the project. |B |1 |2008-06-03 |9:30:00 |90 |41 |34876...4846548 | |5 |Reporting |Explanation to the new beginner. |B |1 |2009-03-15 |14:00:00 |60 |7 |19739...37718 | |6 |Learning |A new software version has been installed. |B |1 |2009-09-21 |16:00:00 |120 |11 |785278...37528 | |----------------------------------------------------------------------------------------------------------------------------------------------------|
The table name can be omitted if it is not ambiguous.
Predicate
Compared to the second operand, the first operand can be :
- equal :
= - different :
<> - lesser :
< - lesser or equal :
<= - greater :
> - greater or equal :
>=
The following query returns the reunions which have another priority level than B:
- Query:
SELECT *
FROM reunion
WHERE priority <> 'B';
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |1 |Planning |We need to plan the project. |A |1 |2008-03-24 |10:30:00 |60 |35 |48644...846348 | |2 |Progress |What we have done. |C |1 |2008-05-12 |14:00:00 |30 |13 |9862...15676 | |4 |Presentation |Presentation of the project. |D |0 |2008-09-11 |15:30:00 |120 |27 |NULL | |----------------------------------------------------------------------------------------------------------------------------------------------------|
Operators
The WHERE clause can have several conditions using the operators AND (all the conditions must be true) and OR (only one condition needs to be true). The operator OR is inclusive (several conditions can be true). The order of evaluation can be indicated with brackets. NOT inverts a condition. The following query returns the reunions which have a B priority level and last more than an hour or which take place on 2008/05/12:
- Query:
SELECT *
FROM reunion
WHERE (priority = 'B' AND NOT duration <= 60) OR date = '2008-05-12';
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |2 |Progress |What we have done. |C |1 |2008-05-12 |14:00:00 |30 |13 |9862...15676 | |3 |Change |What we need to change in the project. |B |1 |2008-06-03 |9:30:00 |90 |41 |34876...4846548 | |6 |Learning |A new software version has been installed. |B |1 |2009-09-21 |16:00:00 |120 |11 |785278...37528 | |----------------------------------------------------------------------------------------------------------------------------------------------------|
LIKE
LIKE allows simplified regular expression matching. It can be applied on the text columns (CHAR, VARCHAR,...).
- Alphanumerical characters only match identical text,
%is a wildcard that matches any text,_is a wildcard that matches any single character,
The following query returns the reunions which end with "ing" and which contain " the " in its description:
- Query:
SELECT *
FROM reunion
WHERE name LIKE '%ing' AND description LIKE '% the %';
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |1 |Planning |We need to plan the project. |A |1 |2008-03-24 |10:30:00 |60 |35 |48644...846348 | |5 |Reporting |Explanation to the new beginner. |B |1 |2009-03-15 |14:00:00 |60 |7 |19739...37718 | |----------------------------------------------------------------------------------------------------------------------------------------------------|
BETWEEN and IN
BETWEEN matches a range of values that can be numbers, dates or times. IN matches a list of allowed values. The following query returns the reunions which take place between 2008-04-01 and 2009-04-01 and have an A, B or D priority level:
- Query:
SELECT *
FROM reunion
WHERE date BETWEEN '2008-04-01' AND '2009-04-01' AND priority IN ('A', 'B', 'D');
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |3 |Change |What we need to change in the project. |B |1 |2008-06-03 |9:30:00 |90 |41 |34876...4846548 | |4 |Presentation |Presentation of the project. |D |0 |2008-09-11 |15:30:00 |120 |27 |NULL | |5 |Reporting |Explanation to the new beginner. |B |1 |2009-03-15 |14:00:00 |60 |7 |19739...37718 | |----------------------------------------------------------------------------------------------------------------------------------------------------|
EXISTS
EXISTS is usually used with a subselect. This predicate is true if the list (i.e. the result set of a subselect) is not empty. This keyword allows to filter the returned rows using data that are not directly associated to the returned rows (i.e. they are not joined, not linked, not related... to the returned rows) so you can not use junction in this case. For instance, we want to retrieve all the reunions for which there is at least one reunion two times longer:
- Query:
SELECT *
FROM reunion r1
WHERE EXISTS (
SELECT r2.id_reunion
FROM reunion r2
WHERE r2.duration = r1.duration * 2
);
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |1 |Planning |We need to plan the project. |A |1 |2008-03-24 |10:30:00 |60 |35 |48644...846348 | |2 |Progress |What we have done. |C |1 |2008-05-12 |14:00:00 |30 |13 |9862...15676 | |5 |Reporting |Explanation to the new beginner. |B |1 |2009-03-15 |14:00:00 |60 |7 |19739...37718 | |----------------------------------------------------------------------------------------------------------------------------------------------------|
The duration of another reunion is used in this query whereas there is no join, no link and no relationship between the two rows. This condition can not be done without EXISTS. Note that the subselect uses the alias r1 whereas this alias is defined in the main query.
EXISTS is also used to match a lack of data. Let's remember the employee table and the members table:
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
The following query returns the employees who are not linked to any project (i.e. the ones there is no relationship for them in the members table):
- Query:
SELECT *
FROM employees e
WHERE NOT EXISTS (
SELECT m.id_employee
FROM members m
WHERE m.id_employee = e.id_employee
);
- Result:
|------------------------------------------------------------------| |id_employee |firstname |lastname |phone |mail | |------------|----------|---------|----------|---------------------| |1 |Big |BOSS |936854270 |big.boss@company.com | |------------------------------------------------------------------|
IS NULL
IS NULL tests if a column is filled. It is often used for foreign key columns.
FROM clause
The FROM clause defines the tables that are used for the query but it can also join tables. A JOIN builds a super table with the columns of two tables to be used for the query. To explain what a join is, we consider two archaic tables without primary keys nor foreign keys:
|
| ||||||||||||||||||||||||||||||||
We want to associate values from columns of different tables matching values on a given column in each table.
FULL OUTER JOIN
A JOIN is made matching a column on a table to a column on the other table. After a FULL OUTER JOIN, for a given value (red), for a given row with this value on one table ([ red | 9999 ]), one row is created for each row that matches on the other table ([ red | OOOOOO ] and [ red | LLLLLL ]). If a value exists in only one table, then a row is created and is completed with NULL columns.
FROM table_1 FULL OUTER JOIN table_2 ON table_1.common_value = table_2.common_value
|
|
RIGHT OUTER JOIN
The RIGHT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the left table.
FROM table_1 RIGHT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value
|
|
LEFT OUTER JOIN
The LEFT OUTER JOIN is like the FULL OUTER JOIN but it doesn't create row for values that don't exist on the right table.
FROM table_1 LEFT OUTER JOIN table_2 ON table_1.common_value = table_2.common_value
|
|
INNER JOIN
The INNER JOIN is like the FULL OUTER JOIN but it creates row only for values that exist on both the left table and the right table.
FROM table_1 INNER JOIN table_2 ON table_1.common_value = table_2.common_value
|
|
Alias
The FROM clause can declare several tables, separated by , and aliases can be defined for table name with the keyword AS, which allows the user to make several joins with the same tables. The following query is equivalent to the INNER JOIN above:
- Query:
SELECT *
FROM table_1 AS t1, table_2 AS t2
WHERE t1.common_value = t2.common_value
The keyword AS can be omitted.
SELECT clause
The SELECT clause doesn't influence the data processed by the query but the data returned to the user. * return all the data processed after joining and filtering. Otherwise, the SELECT clause lists expressions separated by ,.
The expressions can be a table name, a table name and a column name separated by a dot or simply a column name if it is not ambiguous. The SELECT clause also allows evaluated expressions like addition, subtraction, concatenation, ... An expression can be followed by an alias with the keyword AS. The keyword AS can be omitted.
Here is an example:
- Query:
SELECT reunion.id_reunion, concat(name, ' : ', reunion.description) n, priority AS p, planned * 10 AS plan, duration + 10 AS reunion_length
FROM reunion;
- Result:
|-------------------------------------------------------------------------------------------| |id_reunion |n |p |plan |reunion_length | |-----------|------------------------------------------------------|--|-----|---------------| |1 |Planning : We need to plan the project. |A |10 |70 | |2 |Progress : What we have done. |C |10 |40 | |3 |Change : What we need to change in the project. |B |10 |100 | |4 |Presentation : Presentation of the project. |D |0 |130 | |5 |Reporting : Explanation to the new beginner. |B |10 |70 | |6 |Learning : A new software version has been install... |B |10 |130 | |-------------------------------------------------------------------------------------------|
The expressions can be also the following aggregation functions:
count(*): the count of rows returned,max(<column_name>): the greatest value of the column,min(<column_name>): the lowest value of the column.
Here is a new example:
- Query:
SELECT count(*) * 10 AS c, max(date) AS latest_date, min(reunion.date) oldest_date
FROM reunion;
- Result:
|-----------------------------| |c |latest_date |oldest_date | |---|------------|------------| |60 |2009-09-21 |2008-03-24 | |-----------------------------|
ORDER BY clause
The ORDER BY clause sorts the rows returned by the query by one or several columns. The sort is done with the first column mentioned. The second column is used to sort the rows which have the same value in the first column and so on. The keywords ASC or DESC can be added after each column. ASC indicates an ascending sort. DESC indicates a descending sort. Default is a descending sort. Let's do two simple requests, the first sorting by only one column and the second sorting by two columns:
- Query:
SELECT *
FROM reunion
ORDER BY priority ASC;
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |1 |Planning |We need to plan the project. |A |1 |2008-03-24 |10:30:00 |60 |35 |48644...846348 | |3 |Change |What we need to change in the project. |B |1 |2008-06-03 |9:30:00 |90 |41 |34876...4846548 | |5 |Reporting |Explanation to the new beginner. |B |1 |2009-03-15 |14:00:00 |60 |7 |19739...37718 | |6 |Learning |A new software version has been installed. |B |1 |2009-09-21 |16:00:00 |120 |11 |785278...37528 | |2 |Progress |What we have done. |C |1 |2008-05-12 |14:00:00 |30 |13 |9862...15676 | |4 |Presentation |Presentation of the project. |D |0 |2008-09-11 |15:30:00 |120 |27 |NULL | |----------------------------------------------------------------------------------------------------------------------------------------------------|
- Query:
SELECT *
FROM reunion
ORDER BY priority ASC, duration DESC;
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |1 |Planning |We need to plan the project. |A |1 |2008-03-24 |10:30:00 |60 |35 |48644...846348 | |6 |Learning |A new software version has been installed. |B |1 |2009-09-21 |16:00:00 |120 |11 |785278...37528 | |3 |Change |What we need to change in the project. |B |1 |2008-06-03 |9:30:00 |90 |41 |34876...4846548 | |5 |Reporting |Explanation to the new beginner. |B |1 |2009-03-15 |14:00:00 |60 |7 |19739...37718 | |2 |Progress |What we have done. |C |1 |2008-05-12 |14:00:00 |30 |13 |9862...15676 | |4 |Presentation |Presentation of the project. |D |0 |2008-09-11 |15:30:00 |120 |27 |NULL | |----------------------------------------------------------------------------------------------------------------------------------------------------|
GROUP BY clause
The GROUP BY clause is used for aggregation operations. It gathers the rows into groups, for instance, all the rows that have the same value in a given column. After gathering rows into groups, any aggregation operation is applied on each group instead of a unique big group of rows. As a consequence, an aggregation operation will return as many result as the number of groups. Groups can be formed with all the rows that have the same value for a given column or the same combination of values for several given columns. For instance, we want to know the number of reunions for each type of priority:
- Query:
SELECT count(*) as number, priority
FROM reunion
GROUP BY priority;
- Result:
|-----------------| |number |priority | |-------|---------| |1 |A | |3 |B | |1 |C | |1 |D | |-----------------|
Due to the GROUP BY clause, the aggregation function count(*) doesn't return a global count but a count for each priority level (A, B, C and D).
- Query:
SELECT count(*) as number, planned, duration
FROM reunion
GROUP BY planned, duration;
- Result:
|--------------------------| |number |planned |duration | |-------|--------|---------| |1 |0 |120 | |1 |1 |30 | |2 |1 |60 | |1 |1 |90 | |1 |1 |120 | |--------------------------|
Note that there are four groups with 1 for the column planned and there are two groups with 120 for the column duration. However, you can see that there is no group with the same combination of values from the two columns.
HAVING clause
The HAVING clause is used with the GROUP BY clause. The HAVING clause contains a predicate and removes from the returned rows the groups for which the predicate is false. For example, we want to retrieve only the priorities for which there are at least two reunions with the same priority level:
- Query:
SELECT priority
FROM reunion
GROUP BY priority
HAVING count(*) > 1;
- Result:
|---------| |priority | |---------| |B | |---------|
FETCH FIRST clause
The FETCH FIRST clause is used to limit the number of returned rows. Only the first rows are returned. The number of returned rows is the number indicated in the clause.
- Query:
SELECT *
FROM reunion
FETCH FIRST 4 ROWS ONLY;
- Result:
|----------------------------------------------------------------------------------------------------------------------------------------------------| |id_reunion |name |description |priority |planned |date |hour |duration |id_office |pdf_report | |-----------|-------------|-------------------------------------------|---------|--------|-----------|---------|---------|----------|----------------| |1 |Planning |We need to plan the project. |A |1 |2008-03-24 |10:30:00 |60 |35 |48644...846348 | |2 |Progress |What we have done. |C |1 |2008-05-12 |14:00:00 |30 |13 |9862...15676 | |3 |Change |What we need to change in the project. |B |1 |2008-06-03 |9:30:00 |90 |41 |34876...4846548 | |4 |Presentation |Presentation of the project. |D |0 |2008-09-11 |15:30:00 |120 |27 |NULL | |----------------------------------------------------------------------------------------------------------------------------------------------------|
This clause is often used not to return useless rows for test or to improve the performance.
Now you can explore all the data of an already existing database.
SQL Functions
- COUNT
- AVG
- MIN
- MAX
- SUM
SELECT '''COUNT(*)''' FROM reunion
---
- See also: [[1]]
Data Manipulation Language is used to modify the records in the database. It never modifies the schema of the database (table features, relationships, ...). It describes three statements: INSERT, UPDATE and DELETE.
INSERT statement
The exhaustive syntax of the INSERT statement is as follows:
INSERT INTO <table name>[ (<column name>[, <column name>]*)]
{
VALUES (<value>[, <value>]*)
|
SELECT [ALL | DISTINCT] <column name> [, [ALL | DISTINCT] <column name>]*
FROM <table> [[AS | =] <alias> | [[FULL | LEFT | RIGHT] OUTER | INNER] JOIN <table> ON <expression>]
[, <table> [[AS | =] <alias> | [[FULL | LEFT | RIGHT] OUTER | INNER] JOIN <table> ON <expression>]]*
[WHERE <predicate> [{AND | OR} <predicate>]*]
[GROUP BY <column name> [, <column name>]*
[HAVING <predicate> [{AND | OR} <predicate>]]*]
]
[ORDER BY <column name> [ASC | DESC] [, <column name> [ASC | DESC]]*]
[LIMIT <count>]
};
The INSERT statement is used to add new records (rows) in a table. For instance, we want to add a new reunion:
- Its primary key is 7,
- Its name is "Job interview",
- Its description is "Meeting with Mr. SPENCER",
- Its priority is B,
- Its planned,
- Its date is on October 28, 2009,
- Its hour is 18:30:00,
- Its duration is 30,
- Its office technical id is 23,
- There is no pdf report.
- The table before the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
- Query:
INSERT INTO reunion (id_reunion, name, description, priority, planned, date, hour, duration, id_office, pdf_report)
VALUES (7, 'Job interview', 'Meeting with Mr. SPENCER', B, 1, 2009-10-28, 18:30:00, 30, 23, NULL);
- The table after the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
The INTO clause contains the name of the table where the record needs to be inserted. It can be followed by a list of columns in brackets. The VALUES clause contains the values to insert in brackets. If the column names are omitted, the VALUES clause must contains as many values as the number of columns of the table. The values are inserted in the table columns in the same order that the order in which the columns have been declared in the table. If the column names are mentioned, there must be as many column names as values. The values are respectively inserted into the named columns. If a column in the table is omitted, a NULL value is inserted instead.
The VALUES clause can be replaced by an inner SELECT statement. In this case, the INSERT statement can insert several rows in the table. For example, we want to plan twice all the reunion with a B priority level, one year later:
- The table before the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
- Query:
INSERT INTO reunion (id_reunion, name, description, priority, planned, date, hour, duration, id_office)
SELECT id_reunion + max(id_reunion), name, description, priority, 1, date + 0001-00-00, hour, duration, id_office
FROM reunion
WHERE priority = 'B';
- The table after the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2010-10-28 | 18:30:00 | 30 | 23 | |
UPDATE statement
The exhaustive syntax of the UPDATE statement is as follows:
UPDATE <table name>
SET <column name> = <value>[, <column name> = <value>]*
WHERE <predicate> [{AND | OR} <predicate>]*;
The UPDATE statement is used to modify already existent records in a table. The UPDATE clause is followed by the table name in which the rows need to be changed. The SET clause is followed by couples of column name and value. The values will be inserted in the given columns. The WHERE clause contains predicates. If the predicates are true for an existent row, this row will be modified.
For instance, we want to change the date, the hour and the description of the reunion with id 14:
- The table before the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2010-10-28 | 18:30:00 | 30 | 23 | |
- Query:
UPDATE reunion
SET description = 'Meeting with Ms. JOHNSON', date = '2010-02-11', hour = '08:00:00'
WHERE id_reunion = '14';
- The table after the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Ms. JOHNSON | B | 1 | 2010-02-11 | 08:00:00 | 30 | 23 | |
DELETE statement
The exhaustive syntax of the DELETE statement is as follows:
DELETE FROM <table name>
[WHERE <predicate> [{AND | OR} <predicate>]*];
The DELETE statement is used to remove specific rows in a table with conditions. The FROM clause is followed by the table name in which the rows need to be removed. The WHERE clause contains predicates. If the predicates are true for an row, this row will be removed. If the predicates are false for all the rows, the statement do nothing. A DELETE statement without WHERE clause empties the table.
For example, we want to remove all the reunions that last two hours:
- The table before the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Ms. JOHNSON | B | 1 | 2010-02-11 | 08:00:00 | 30 | 23 | |
- Query:
DELETE FROM reunion
WHERE duration = 120;
- The table after the statement:
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 14 | Job interview | Meeting with Ms. JOHNSON | B | 1 | 2010-02-11 | 08:00:00 | 30 | 23 | |
Now you can use an already existing database schema to handle your own data.
Data Definition Language is used to modify the schema of the database. It will never impact the user rights for the database. Otherwise, it can erase records in some tables. It describes three statements: CREATE, ALTER and DROP.
CREATE statement
The exhaustive syntax of the CREATE statement for the tables is as follows:
CREATE TABLE <table name>
(<column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>][, <column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>]]*
[,[ CONSTRAINT <constraint name>]
{
PRIMARY KEY (<column name>[, <column name>]*)
|
UNIQUE ([VALUE|<column name>[, <column name>]*])
|
FOREIGN KEY (<column name>[, <column name>]*) REFERENCES <table name> (<column name>[, <column name>]*)[ ON DELETE CASCADE][ ON UPDATE CASCADE]
|
CHECK (<predicate>[{ AND| OR} <predicate>]*)
}
]*
);
The CREATE statement is used to create a new table with no record. Let's create the table office. The records in the office table will contain a technical id, the name of the office, a description, the number of available places, the availability and the date for the next office security control:
- Query:
CREATE TABLE office
(
id_office INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(255),
place_number INTEGER NOT NULL,
available SMALLINT NOT NULL DEFAULT 1,
next_inspection DATE NOT NULL
);
- The table after the statement:
| id_office | INTEGER |
| name | VARCHAR(20) |
| description | VARCHAR(255) |
| place_number | INTEGER |
| available | SMALLINT |
| next_inspection | DATE |
Now the table office can be used and filled as the tables reunion, employee, project and members:
| id_office | name | description | place_number | available | next_inspection |
|---|---|---|---|---|---|
| 1 | Show room | 100 | 1 | 2011-03-24 | |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 |
| 5 | Reunion room | 20 | 1 | 2010-05-12 | |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 |
The statement starts with CREATE TABLE, to indicate that what we want to create is a table. It's followed by the name of the table (i.e. office). The name of the table is followed by parentheses which describe all the columns of the table. The descriptions of the columns are separated by a comma. Each description contains the column name (for instance, id_office), the column type (INTEGER, VARCHAR, CHAR, DATE, etc...), an optional nullability information (nothing to indicate that the column can be null or NOT NULL to indicate that the column can't be null) and the optional keyword DEFAULT followed by a default value or the optional keyword PRIMARY KEY to indicate that the column is a primary key. If no default value is defined, NULL is the default value. If NOT NULL is defined, the column can't have NULL as default value.
You can see that the column id_office has been defined as a primary key, the column description can be null and the column available has 1 as default value.
ALTER statement
The exhaustive syntax of the ALTER statement for the tables is as follows:
ALTER TABLE <table name>
{
ADD[ COLUMN] <column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>]
|
ALTER[ COLUMN] <column name>[ SET DEFAULT <default option>| DROP DEFAULT]
|
DROP[ COLUMN] <column name>
|
ADD[ CONSTRAINT <constraint name>]
{
PRIMARY KEY (<column name>[, <column name>]*)
|
UNIQUE ([VALUE|<column name>[, <column name>]*])
|
FOREIGN KEY (<column name>[, <column name>]*) REFERENCES <table name> (<column name>[, <column name>]*)[ ON DELETE CASCADE][ ON UPDATE CASCADE]
|
CHECK (<predicate>[{ AND| OR} <predicate>]*)
}
|
DROP CONSTRAINT <constraint name>
};
The ALTER statement is used to modify a table. It can be used on a table with records in it.
ADD CONSTRAINT clause
This clause allows to add a constraint on the table as it could be done at the table creation time. Let's add a unicity constraint on both the name and the description of the office:
- Query:
ALTER TABLE office ADD CONSTRAINT unique_name_and_description UNIQUE (name, description);
Now we can not insert a row with the same name and description of an already existing row and we can not update a row with the same name and description of another row. However, we can insert a row with only the same name or only the same description.
DROP CONSTRAINT clause
This clause allows to remove an existing constraint on the table by its name. Let's remove the preceding unicity constraint on both the name and the description of the office:
- Query:
ALTER TABLE office DROP CONSTRAINT unique_name_and_description;
Now we can insert a row with the same name and description of an already existing row and we can update a row with the same name and description of another row once again.
ADD COLUMN clause
Let's add a new column has_video_projector to indicate if we can project a slideshow:
- The table before the statement:
| id_office | name | description | place_number | available | next_inspection |
|---|---|---|---|---|---|
| 1 | Show room | 100 | 1 | 2011-03-24 | |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 |
| 5 | Reunion room | 20 | 1 | 2010-05-12 | |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 |
- Query:
ALTER TABLE office ADD has_video_projector SMALLINT DEFAULT 0;
- The table after the statement:
| id_office | name | description | place_number | available | next_inspection | has_video_projector |
|---|---|---|---|---|---|---|
| 1 | Show room | 100 | 1 | 2011-03-24 | 0 | |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 | 0 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 | 0 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 | 0 |
| 5 | Reunion room | 20 | 1 | 2010-05-12 | 0 | |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 | 0 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 | 0 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 | 0 |
The column has_video_projector has been added at the end. The column has been filled with the default value.
DROP COLUMN clause
Now let's remove the column next_inspection:
- The table before the statement:
| id_office | name | description | place_number | available | next_inspection | has_video_projector |
|---|---|---|---|---|---|---|
| 1 | Show room | 100 | 1 | 2011-03-24 | 0 | |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 | 0 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 | 0 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 | 0 |
| 5 | Reunion room | 20 | 1 | 2010-05-12 | 0 | |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 | 0 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 | 0 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 | 0 |
- Query:
ALTER TABLE office DROP COLUMN next_inspection;
- The table after the statement:
| id_office | name | description | place_number | available | has_video_projector | |
|---|---|---|---|---|---|---|
| 1 | Show room | 100 | 1 | 0 | ||
| 2 | Big room | The biggest room. | 200 | 1 | 0 | |
| 3 | Open space | The developer open space. | 50 | 1 | 0 | |
| 4 | Hall | The entrance. | 20 | 1 | 0 | |
| 5 | Reunion room | 20 | 1 | 0 | ||
| 6 | Actual office | This office is under construction. | 5 | 0 | 0 | |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 0 | |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 0 | |
The column next_inspection has been removed. If you want to remove a column, you need to remove any constraint applied on it (for instance, you could not remove the name or the description column if there is still the unique_name_and_description unicity constraint).
DROP TABLE statement
The DROP TABLE statement is used to remove a table altogether, its content (data) as well as its definition.
DROP TABLE <table name>;
Read more: DROP (Wikipedia)
TRUNCATE statement
TRUNCATE quickly removes all data from a table without changing the table's structure, typically bypassing a number of integrity-enforcing and logging mechanisms.
The statement is logically (though not physically) equivalent to the DELETE statement without a WHERE clause. Therefore it's not part of the Data Definition Language (DDL); it's part of the Data Manipulation Language (DML). We describe it here because DROP/DELETE/TRUNCATE are often confused with each other.
TRUNCATE TABLE <table_name>;
Data Control Language is used to modify the user rights for the database. It describes two statements: GRANT and REVOKE.
GRANT statement
The exhaustive syntax of the GRANT statement is as follows:
GRANT
{
ALL PRIVILEGES ON[ TABLE] <table or view name>
|
{SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
[, {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
|
USAGE ON
{DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
|
REFERENCES <column name>[, <column name>]* ON <table name>
}
[,
{
ALL PRIVILEGES ON[ TABLE] <table or view name>
|
{SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
[ ,{SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
|
USAGE ON
{DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
|
REFERENCES <column name>[, <column name>]* ON <table name>
}
]* TO {PUBLIC|<user name>}[, {PUBLIC|<user name>}]*[ WITH GRANT OPTION];
The GRANT statement is used to give a privilege to someone. Any SQL operations are done using a user name. The user name are created by the database management system.
 DBA |
 Developer 1 |
Developer 2 |
Developer 3 |
 User 1 |
User 2 |
The privileges apply on the tables (i.e. employee, office, etc...), the views, their columns, the domain, the collation, the charset and the translation.
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
The privileges can allow to process SELECT ("s"), INSERT ("i"), UPDATE ("u") and DELETE ("d") statements (not CREATE, ALTER or DROP statements). Let's say that only the DataBase Administrator has the privileges.
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
For each privilege ("s", "i", "u" and "d"), there is also a meta-privilege ("S", "I", "U" and "D") : a user can send a privilege to another user. Let's say that only the DataBase Administrator has the meta-privileges.
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
The DBA wants to allow DEVELOPER_1 to select columns on the table employee:
|
GRANT SELECT ON employee TO DEVELOPER_1;
|
The rights for DEVELOPER_1 have changed:
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
SELECT indicates that we want to sent the SELECT privilege. The keyword ON followed by employee indicates that the privilege applies on the table employee. The keyword TO followed by DEVELOPER_1 indicates that the privilege has been sent to DEVELOPER_1.
The DBA wants to allow DEVELOPER_2 and DEVELOPER_3 to insert, update and delete rows on the table office:
|
GRANT INSERT, UPDATE, DELETE ON office TO DEVELOPER_2 DEVELOPER_3;
|
The rights for DEVELOPER_2 and DEVELOPER_3 have changed:
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
Whereas you can send several privileges on a table to several users at once, you can't send privileges on several tables at once. If you want to send all the privileges (SELECT, INSERT, UPDATE and DELETE), you can replace the list of privileges by the keywords ALL PRIVILEGES.
Now, the DBA wants to allow USER_1 to insert on the table employee and allow him to send this privilege to other users:
|
GRANT INSERT ON employee TO USER_1 WITH GRANT OPTION;
|
The rights for USER_1 have changed:
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
The keyword WITH GRANT OPTION indicates that we want to send privileges with the meta-privileges to the user. Now, USER_1 can send the SELECT privilege on the table employee to the other users. Let's say that USER_1 wants to allow anyone to process SELECT on the table employee:
|
GRANT SELECT ON TABLE employee TO PUBLIC;
|
The rights of all the users have changed:
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
The keyword PUBLIC indicates that we want to send privileges to all the users and the new future ones.
Let's say that DEVELOPER_3 tries to allow USER_2 to insert records into the table office:
|
GRANT INSERT ON TABLE office TO USER_2;
|
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
The operation has been refused because DEVELOPER_3 hasn't enough privileges.
Sending privileges on columns
You can send privileges on columns only (only for INSERT and UPDATE):
GRANT INSERT (name, description) ON TABLE office TO USER_2;
GRANT UPDATE (id_office, name) ON TABLE office TO USER_2;
For INSERT, make all the columns that the user can't fill have default values, are automatically generated or are filled by a trigger before the insertion. Otherwise, the privilege is just useless.
REVOKE statement
The exhaustive syntax of the REVOKE statement is as follows:
REVOKE[ GRANT OPTION FOR]
{
ALL PRIVILEGES ON[ TABLE] <table or view name>
|
{SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
[, {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
|
USAGE ON
{DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
|
REFERENCES <column name>[, <column name>]* ON <table name>
}
[,
{
{SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
[, {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
|
USAGE ON
{DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
|
REFERENCES <column name>[, <column name>]* ON <table name>
}
]* FROM {PUBLIC|<user name>}[, {PUBLIC|<user name>}]*[ RESTRICT| CASCADE]
The REVOKE statement is used to take back privileges granted to someone. This revocation may be more complicated than you expect. To completely remove a privilege to a user, this privilege must be taken back by all the users that have sent the privilege.
For instance, the DBA wants to remove the INSERT and DELETE privileges on the table employee to DEVELOPER_2 and DEVELOPER_3:
|
REVOKE INSERT, DELETE ON TABLE office FROM DEVELOPER_2, DEVELOPER_3;
|
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
If you remove a privilege to a user who was also sent the related meta-privilege (for example, SELECT privilege to USER_1), the operation also removes the meta-privilege. To remove only meta-privileges, add the keywords GRANT OPTION FOR:
|
REVOKE GRANT OPTION FOR SELECT ON TABLE employee FROM USER_1;
|
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
| ... | ... | ... | ... | ... | ... |
Now you can administrate a database.
Result set column
It is not recommended to use * in a SELECT clause, due to performance issues. You should only return columns you want to use. As a consequence, you should replace any count(*) by a count on one column only.
Avoid the value expressions
Whenever it is possible, avoid the use of value expressions in the WHERE clause like this:
SELECT id_reunion
FROM reunion
WHERE duration - 60 <= 0;
It forces the rDBMS to compute the value for each line, which is very expensive. You should rather compute yourself the values with literals (0 + 60 in this case):
SELECT id_reunion
FROM reunion
WHERE duration <= 60;
Index
If you often select records sorting or filtering by a given column, you may add an index on this column. The database behavior should not change. The index may make query faster. However, don't add useless indexes as it makes insertion a little bit slower.
The exhaustive syntax of the CREATE statement for the indexes is as follows:
CREATE[ UNIQUE] INDEX <index name> ON <table name> (<column name>[, <column name>]*);
The keyword UNIQUE indicates that all the group of values in the columns must be distinct.
The return code of programms calling the IBM database DB2 is stored in the field SQLCODE of its SQLCA area. You can find a detailed explanation of SQLCODE's meaning at: DB2 return codes.
During the initial phase of the SQL standard SQLCODE was a part of it. But over time it was replaced by the more detailed SQLSTATE.
License
GNU Free Documentation License
As of July 15, 2009 Wikibooks has moved to a dual-licensing system that supersedes the previous GFDL only licensing. In short, this means that text licensed under the GFDL only can no longer be imported to Wikibooks, retroactive to 1 November 2008. Additionally, Wikibooks text might or might not now be exportable under the GFDL depending on whether or not any content was added and not removed since July 15. |
Version 1.3, 3 November 2008 Copyright (C) 2000, 2001, 2002, 2007, 2008 Free Software Foundation, Inc. <http://fsf.org/>
Everyone is permitted to copy and distribute verbatim copies of this license document, but changing it is not allowed.
0. PREAMBLE
The purpose of this License is to make a manual, textbook, or other functional and useful document "free" in the sense of freedom: to assure everyone the effective freedom to copy and redistribute it, with or without modifying it, either commercially or noncommercially. Secondarily, this License preserves for the author and publisher a way to get credit for their work, while not being considered responsible for modifications made by others.
This License is a kind of "copyleft", which means that derivative works of the document must themselves be free in the same sense. It complements the GNU General Public License, which is a copyleft license designed for free software.
We have designed this License in order to use it for manuals for free software, because free software needs free documentation: a free program should come with manuals providing the same freedoms that the software does. But this License is not limited to software manuals; it can be used for any textual work, regardless of subject matter or whether it is published as a printed book. We recommend this License principally for works whose purpose is instruction or reference.
1. APPLICABILITY AND DEFINITIONS
This License applies to any manual or other work, in any medium, that contains a notice placed by the copyright holder saying it can be distributed under the terms of this License. Such a notice grants a world-wide, royalty-free license, unlimited in duration, to use that work under the conditions stated herein. The "Document", below, refers to any such manual or work. Any member of the public is a licensee, and is addressed as "you". You accept the license if you copy, modify or distribute the work in a way requiring permission under copyright law.
A "Modified Version" of the Document means any work containing the Document or a portion of it, either copied verbatim, or with modifications and/or translated into another language.
A "Secondary Section" is a named appendix or a front-matter section of the Document that deals exclusively with the relationship of the publishers or authors of the Document to the Document's overall subject (or to related matters) and contains nothing that could fall directly within that overall subject. (Thus, if the Document is in part a textbook of mathematics, a Secondary Section may not explain any mathematics.) The relationship could be a matter of historical connection with the subject or with related matters, or of legal, commercial, philosophical, ethical or political position regarding them.
The "Invariant Sections" are certain Secondary Sections whose titles are designated, as being those of Invariant Sections, in the notice that says that the Document is released under this License. If a section does not fit the above definition of Secondary then it is not allowed to be designated as Invariant. The Document may contain zero Invariant Sections. If the Document does not identify any Invariant Sections then there are none.
The "Cover Texts" are certain short passages of text that are listed, as Front-Cover Texts or Back-Cover Texts, in the notice that says that the Document is released under this License. A Front-Cover Text may be at most 5 words, and a Back-Cover Text may be at most 25 words.
A "Transparent" copy of the Document means a machine-readable copy, represented in a format whose specification is available to the general public, that is suitable for revising the document straightforwardly with generic text editors or (for images composed of pixels) generic paint programs or (for drawings) some widely available drawing editor, and that is suitable for input to text formatters or for automatic translation to a variety of formats suitable for input to text formatters. A copy made in an otherwise Transparent file format whose markup, or absence of markup, has been arranged to thwart or discourage subsequent modification by readers is not Transparent. An image format is not Transparent if used for any substantial amount of text. A copy that is not "Transparent" is called "Opaque".
Examples of suitable formats for Transparent copies include plain ASCII without markup, Texinfo input format, LaTeX input format, SGML or XML using a publicly available DTD, and standard-conforming simple HTML, PostScript or PDF designed for human modification. Examples of transparent image formats include PNG, XCF and JPG. Opaque formats include proprietary formats that can be read and edited only by proprietary word processors, SGML or XML for which the DTD and/or processing tools are not generally available, and the machine-generated HTML, PostScript or PDF produced by some word processors for output purposes only.
The "Title Page" means, for a printed book, the title page itself, plus such following pages as are needed to hold, legibly, the material this License requires to appear in the title page. For works in formats which do not have any title page as such, "Title Page" means the text near the most prominent appearance of the work's title, preceding the beginning of the body of the text.
The "publisher" means any person or entity that distributes copies of the Document to the public.
A section "Entitled XYZ" means a named subunit of the Document whose title either is precisely XYZ or contains XYZ in parentheses following text that translates XYZ in another language. (Here XYZ stands for a specific section name mentioned below, such as "Acknowledgements", "Dedications", "Endorsements", or "History".) To "Preserve the Title" of such a section when you modify the Document means that it remains a section "Entitled XYZ" according to this definition.
The Document may include Warranty Disclaimers next to the notice which states that this License applies to the Document. These Warranty Disclaimers are considered to be included by reference in this License, but only as regards disclaiming warranties: any other implication that these Warranty Disclaimers may have is void and has no effect on the meaning of this License.
2. VERBATIM COPYING
You may copy and distribute the Document in any medium, either commercially or noncommercially, provided that this License, the copyright notices, and the license notice saying this License applies to the Document are reproduced in all copies, and that you add no other conditions whatsoever to those of this License. You may not use technical measures to obstruct or control the reading or further copying of the copies you make or distribute. However, you may accept compensation in exchange for copies. If you distribute a large enough number of copies you must also follow the conditions in section 3.
You may also lend copies, under the same conditions stated above, and you may publicly display copies.
3. COPYING IN QUANTITY
If you publish printed copies (or copies in media that commonly have printed covers) of the Document, numbering more than 100, and the Document's license notice requires Cover Texts, you must enclose the copies in covers that carry, clearly and legibly, all these Cover Texts: Front-Cover Texts on the front cover, and Back-Cover Texts on the back cover. Both covers must also clearly and legibly identify you as the publisher of these copies. The front cover must present the full title with all words of the title equally prominent and visible. You may add other material on the covers in addition. Copying with changes limited to the covers, as long as they preserve the title of the Document and satisfy these conditions, can be treated as verbatim copying in other respects.
If the required texts for either cover are too voluminous to fit legibly, you should put the first ones listed (as many as fit reasonably) on the actual cover, and continue the rest onto adjacent pages.
If you publish or distribute Opaque copies of the Document numbering more than 100, you must either include a machine-readable Transparent copy along with each Opaque copy, or state in or with each Opaque copy a computer-network location from which the general network-using public has access to download using public-standard network protocols a complete Transparent copy of the Document, free of added material. If you use the latter option, you must take reasonably prudent steps, when you begin distribution of Opaque copies in quantity, to ensure that this Transparent copy will remain thus accessible at the stated location until at least one year after the last time you distribute an Opaque copy (directly or through your agents or retailers) of that edition to the public.
It is requested, but not required, that you contact the authors of the Document well before redistributing any large number of copies, to give them a chance to provide you with an updated version of the Document.
4. MODIFICATIONS
You may copy and distribute a Modified Version of the Document under the conditions of sections 2 and 3 above, provided that you release the Modified Version under precisely this License, with the Modified Version filling the role of the Document, thus licensing distribution and modification of the Modified Version to whoever possesses a copy of it. In addition, you must do these things in the Modified Version:
- Use in the Title Page (and on the covers, if any) a title distinct from that of the Document, and from those of previous versions (which should, if there were any, be listed in the History section of the Document). You may use the same title as a previous version if the original publisher of that version gives permission.
- List on the Title Page, as authors, one or more persons or entities responsible for authorship of the modifications in the Modified Version, together with at least five of the principal authors of the Document (all of its principal authors, if it has fewer than five), unless they release you from this requirement.
- State on the Title page the name of the publisher of the Modified Version, as the publisher.
- Preserve all the copyright notices of the Document.
- Add an appropriate copyright notice for your modifications adjacent to the other copyright notices.
- Include, immediately after the copyright notices, a license notice giving the public permission to use the Modified Version under the terms of this License, in the form shown in the Addendum below.
- Preserve in that license notice the full lists of Invariant Sections and required Cover Texts given in the Document's license notice.
- Include an unaltered copy of this License.
- Preserve the section Entitled "History", Preserve its Title, and add to it an item stating at least the title, year, new authors, and publisher of the Modified Version as given on the Title Page. If there is no section Entitled "History" in the Document, create one stating the title, year, authors, and publisher of the Document as given on its Title Page, then add an item describing the Modified Version as stated in the previous sentence.
- Preserve the network location, if any, given in the Document for public access to a Transparent copy of the Document, and likewise the network locations given in the Document for previous versions it was based on. These may be placed in the "History" section. You may omit a network location for a work that was published at least four years before the Document itself, or if the original publisher of the version it refers to gives permission.
- For any section Entitled "Acknowledgements" or "Dedications", Preserve the Title of the section, and preserve in the section all the substance and tone of each of the contributor acknowledgements and/or dedications given therein.
- Preserve all the Invariant Sections of the Document, unaltered in their text and in their titles. Section numbers or the equivalent are not considered part of the section titles.
- Delete any section Entitled "Endorsements". Such a section may not be included in the Modified version.
- Do not retitle any existing section to be Entitled "Endorsements" or to conflict in title with any Invariant Section.
- Preserve any Warranty Disclaimers.
If the Modified Version includes new front-matter sections or appendices that qualify as Secondary Sections and contain no material copied from the Document, you may at your option designate some or all of these sections as invariant. To do this, add their titles to the list of Invariant Sections in the Modified Version's license notice. These titles must be distinct from any other section titles.
You may add a section Entitled "Endorsements", provided it contains nothing but endorsements of your Modified Version by various parties—for example, statements of peer review or that the text has been approved by an organization as the authoritative definition of a standard.
You may add a passage of up to five words as a Front-Cover Text, and a passage of up to 25 words as a Back-Cover Text, to the end of the list of Cover Texts in the Modified Version. Only one passage of Front-Cover Text and one of Back-Cover Text may be added by (or through arrangements made by) any one entity. If the Document already includes a cover text for the same cover, previously added by you or by arrangement made by the same entity you are acting on behalf of, you may not add another; but you may replace the old one, on explicit permission from the previous publisher that added the old one.
The author(s) and publisher(s) of the Document do not by this License give permission to use their names for publicity for or to assert or imply endorsement of any Modified Version.
5. COMBINING DOCUMENTS
You may combine the Document with other documents released under this License, under the terms defined in section 4 above for modified versions, provided that you include in the combination all of the Invariant Sections of all of the original documents, unmodified, and list them all as Invariant Sections of your combined work in its license notice, and that you preserve all their Warranty Disclaimers.
The combined work need only contain one copy of this License, and multiple identical Invariant Sections may be replaced with a single copy. If there are multiple Invariant Sections with the same name but different contents, make the title of each such section unique by adding at the end of it, in parentheses, the name of the original author or publisher of that section if known, or else a unique number. Make the same adjustment to the section titles in the list of Invariant Sections in the license notice of the combined work.
In the combination, you must combine any sections Entitled "History" in the various original documents, forming one section Entitled "History"; likewise combine any sections Entitled "Acknowledgements", and any sections Entitled "Dedications". You must delete all sections Entitled "Endorsements".
6. COLLECTIONS OF DOCUMENTS
You may make a collection consisting of the Document and other documents released under this License, and replace the individual copies of this License in the various documents with a single copy that is included in the collection, provided that you follow the rules of this License for verbatim copying of each of the documents in all other respects.
You may extract a single document from such a collection, and distribute it individually under this License, provided you insert a copy of this License into the extracted document, and follow this License in all other respects regarding verbatim copying of that document.
7. AGGREGATION WITH INDEPENDENT WORKS
A compilation of the Document or its derivatives with other separate and independent documents or works, in or on a volume of a storage or distribution medium, is called an "aggregate" if the copyright resulting from the compilation is not used to limit the legal rights of the compilation's users beyond what the individual works permit. When the Document is included in an aggregate, this License does not apply to the other works in the aggregate which are not themselves derivative works of the Document.
If the Cover Text requirement of section 3 is applicable to these copies of the Document, then if the Document is less than one half of the entire aggregate, the Document's Cover Texts may be placed on covers that bracket the Document within the aggregate, or the electronic equivalent of covers if the Document is in electronic form. Otherwise they must appear on printed covers that bracket the whole aggregate.
8. TRANSLATION
Translation is considered a kind of modification, so you may distribute translations of the Document under the terms of section 4. Replacing Invariant Sections with translations requires special permission from their copyright holders, but you may include translations of some or all Invariant Sections in addition to the original versions of these Invariant Sections. You may include a translation of this License, and all the license notices in the Document, and any Warranty Disclaimers, provided that you also include the original English version of this License and the original versions of those notices and disclaimers. In case of a disagreement between the translation and the original version of this License or a notice or disclaimer, the original version will prevail.
If a section in the Document is Entitled "Acknowledgements", "Dedications", or "History", the requirement (section 4) to Preserve its Title (section 1) will typically require changing the actual title.
9. TERMINATION
You may not copy, modify, sublicense, or distribute the Document except as expressly provided under this License. Any attempt otherwise to copy, modify, sublicense, or distribute it is void, and will automatically terminate your rights under this License.
However, if you cease all violation of this License, then your license from a particular copyright holder is reinstated (a) provisionally, unless and until the copyright holder explicitly and finally terminates your license, and (b) permanently, if the copyright holder fails to notify you of the violation by some reasonable means prior to 60 days after the cessation.
Moreover, your license from a particular copyright holder is reinstated permanently if the copyright holder notifies you of the violation by some reasonable means, this is the first time you have received notice of violation of this License (for any work) from that copyright holder, and you cure the violation prior to 30 days after your receipt of the notice.
Termination of your rights under this section does not terminate the licenses of parties who have received copies or rights from you under this License. If your rights have been terminated and not permanently reinstated, receipt of a copy of some or all of the same material does not give you any rights to use it.
10. FUTURE REVISIONS OF THIS LICENSE
The Free Software Foundation may publish new, revised versions of the GNU Free Documentation License from time to time. Such new versions will be similar in spirit to the present version, but may differ in detail to address new problems or concerns. See http://www.gnu.org/copyleft/.
Each version of the License is given a distinguishing version number. If the Document specifies that a particular numbered version of this License "or any later version" applies to it, you have the option of following the terms and conditions either of that specified version or of any later version that has been published (not as a draft) by the Free Software Foundation. If the Document does not specify a version number of this License, you may choose any version ever published (not as a draft) by the Free Software Foundation. If the Document specifies that a proxy can decide which future versions of this License can be used, that proxy's public statement of acceptance of a version permanently authorizes you to choose that version for the Document.
11. RELICENSING
"Massive Multiauthor Collaboration Site" (or "MMC Site") means any World Wide Web server that publishes copyrightable works and also provides prominent facilities for anybody to edit those works. A public wiki that anybody can edit is an example of such a server. A "Massive Multiauthor Collaboration" (or "MMC") contained in the site means any set of copyrightable works thus published on the MMC site.
"CC-BY-SA" means the Creative Commons Attribution-Share Alike 3.0 license published by Creative Commons Corporation, a not-for-profit corporation with a principal place of business in San Francisco, California, as well as future copyleft versions of that license published by that same organization.
"Incorporate" means to publish or republish a Document, in whole or in part, as part of another Document.
An MMC is "eligible for relicensing" if it is licensed under this License, and if all works that were first published under this License somewhere other than this MMC, and subsequently incorporated in whole or in part into the MMC, (1) had no cover texts or invariant sections, and (2) were thus incorporated prior to November 1, 2008.
The operator of an MMC Site may republish an MMC contained in the site under CC-BY-SA on the same site at any time before August 1, 2009, provided the MMC is eligible for relicensing.
How to use this License for your documents
To use this License in a document you have written, include a copy of the License in the document and put the following copyright and license notices just after the title page:
- Copyright (c) YEAR YOUR NAME.
- Permission is granted to copy, distribute and/or modify this document
- under the terms of the GNU Free Documentation License, Version 1.3
- or any later version published by the Free Software Foundation;
- with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.
- A copy of the license is included in the section entitled "GNU
- Free Documentation License".
If you have Invariant Sections, Front-Cover Texts and Back-Cover Texts, replace the "with...Texts." line with this:
- with the Invariant Sections being LIST THEIR TITLES, with the
- Front-Cover Texts being LIST, and with the Back-Cover Texts being LIST.
If you have Invariant Sections without Cover Texts, or some other combination of the three, merge those two alternatives to suit the situation.
If your document contains nontrivial examples of program code, we recommend releasing these examples in parallel under your choice of free software license, such as the GNU General Public License, to permit their use in free software.