Content deleted Content added
Mention the relational operations that match some of the SELECT clauses |
change source to syntaxhighlight |
||
Line 60:
The following example of a <code>SELECT</code> query returns a list of expensive books. The query retrieves all rows from the ''Book'' table in which the ''price'' column contains a value greater than 100.00. The result is sorted in ascending order by ''title''. The asterisk (*) in the ''select list'' indicates that all columns of the ''Book'' table should be included in the result set.
<
SELECT *
FROM Book
WHERE price > 100.00
ORDER BY title;
</syntaxhighlight>
The example below demonstrates a query of multiple tables, grouping, and aggregation, by returning a list of books and the number of authors associated with each book.
<
SELECT Book.title AS Title,
count(*) AS Authors
Line 76:
ON Book.isbn = Book_author.isbn
GROUP BY Book.title;
</syntaxhighlight>
Example output might resemble the following:
Line 89:
Under the precondition that ''isbn'' is the only common column name of the two tables and that a column named ''title'' only exists in the ''Book'' table, one could re-write the query above in the following form:
<
SELECT title,
count(*) AS Authors
Line 95:
NATURAL JOIN Book_author
GROUP BY title;
</syntaxhighlight>
However, many{{quantify|date=October 2015}} vendors either do not support this approach, or require certain column-naming conventions for natural joins to work effectively.
Line 101:
SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the ''select list'' to project data, as in the following example, which returns a list of books that cost more than 100.00 with an additional ''sales_tax'' column containing a sales tax figure calculated at 6% of the ''price''.
<
SELECT isbn,
title,
Line 109:
WHERE price > 100.00
ORDER BY title;
</syntaxhighlight>
=== Subqueries ===
Line 115:
Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function. A nested query is also known as a ''subquery''. While joins and other table operations provide computationally superior (i.e. faster) alternatives in many cases, the use of subqueries introduces a hierarchy in execution that can be useful or necessary. In the following example, the aggregation function <code>AVG</code> receives as input the result of a subquery:
<
SELECT isbn,
title,
Line 122:
WHERE price < (SELECT AVG(price) FROM Book)
ORDER BY title;
</syntaxhighlight>
A subquery can use values from the outer query, in which case it is known as a [[correlated subquery]].
Line 134:
In the following example, the SQL statement involves a join from the initial Books table to the Inline view "Sales". This inline view captures associated book sales information using the ISBN to join to the Books table. As a result, the inline view provides the result set with additional columns (the number of items sold and the company that sold the books):
<
SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm
FROM Book b
Line 141:
GROUP BY Company_Nm, ISBN) sales
ON sales.isbn = b.isbn
</syntaxhighlight>
== Examples ==
| |||