Although we refer to the SQL language as a “query language,” it can domuch
more than just query a database. It can define the structure of the data, modify
data in the database, and specify security constraints.
IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the early 1970s. The Sequel language has evolved since then,
and its name has changed to SQL (Structured Query Language).
In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) published an SQL standard, called
SQL-86. ANSIpublishedanextendedstandard for SQL, SQL-89, in 1989. The next version
of the standardwas SQL-92 standard, followed by SQL:1999, SQL:2003, SQL:2006,
and most recently SQL:2008. (查看原文)
When writing queries, you should be careful to include appropriate where
clause conditions. If you omit the where clause condition in the preceding SQL
query, it would output the Cartesian product, which could be a huge relation.
For the example instructor relation in Figure 2.1 and the example teaches relation
in Figure 2.7, their Cartesian product has 12 ∗ 13 = 156 tuples — more than we
can show in the text! To make matters worse, suppose we have a more realistic
number of instructors thanwe show in our sample relations in the figures, say 200
instructors. Let’s assume each instructor teaches 3 courses, so we have 600 tuples
in the teaches relation. Then the above iterative process generates 200 ∗ 600 = 120,000 tuples in the result. (查看原文)
The from clause by itself defines a Cartesian product of the relations listed
in the clause. It is defined formally in terms of set theory, but is perhaps best
understood as an iterative process that generates tuples for the result relation of
the from clause.
for each tuple t1 in relation r1
for each tuple t2 in relation r2
. . .
for each tuple tm in relation rm
Concatenate t1, t2, . . . , tm into a single tuple t
Add t into the result relation (查看原文)
In contrast,
the attributes name and building appear in only one of the relations, and therefore
do not need to be prefixed by the relation name.
• The select clause is used to list the attributes desired in the result of a query.
• The from clause is a list of the relations to be accessed in the evaluation of
the query.
• The where clause is a predicate involving attributes of the relation in the
from clause.
A typical SQL query has the form
select A1, A2, . . . , An
from r1, r2, . . . , rm
where P;
Each Ai represents an attribute, and each ri a relation. P is a predicate. If the where
clause is omitted, the predicate P is true. (查看原文)
In preceding chapters,we have emphasized the higher-level models of a database.
For example, at the conceptual or logical level, we viewed the database, in the relational
model, as a collection of tables. Indeed, the logical model of the database
is the correct level for database users to focus on. This is because the goal of a
database system is to simplify and facilitate access to data; users of the system
should not be burdened unnecessarily with the physical details of the implementation
of the system.
In this chapter, however, as well as in Chapters 11, 12, and 13, we probe below
the higher levels as we describe various methods for implementing the data
models and languages presented in preceding chapters. We start with characteristics
of the underlying storage media, such as disk and... (查看原文)
3.7 Aggregate Functions
Aggregate functions are functions that take a collection (a set or multiset) of values
as input and return a single value. SQL offers five built-in aggregate functions:
• Average: avg
• Minimum: min
• Maximum: max
• Total: sum
• Count: count
If we do want to eliminate duplicates, we use the keyword
distinct in the aggregate expression.
select count (distinct ID)
from teaches
where semester = ’Spring’ and year = 2010;
We use the aggregate function count frequently to count the number of tuples
in a relation. The notation for this function in SQL is count (*). Thus, to find the
number of tuples in the course relation, we write
select count (*)
from course;
Group By
In other words, any attribute that is not
present in the group by clause must appear only inside ... (查看原文)
3.7.3 The Having Clause
1. As was the case for queries without aggregation, the from clause is first
evaluated to get a relation.
2. If a where clause is present, the predicate in the where clause is applied on
the result relation of the from clause.
3. Tuples satisfying the where predicate are then placed into groups by the
group by clause if it is present. If the group by clause is absent, the entire
set of tuples satisfying the where predicate is treated as being in one group.
4. The having clause, if it is present, is applied to each group; the groups that
do not satisfy the having clause predicate are removed.
5. The select clause uses the remaining groups to generate tuples of the result
of the query, applying the aggregate functions to get a single result tuple for
each group.... (查看原文)
As database systems were applied to a wider range of applications, such as computer-aided design and geographical information systems, limitations imposed by the relational model emerged as an obstacle. The solution was the introduction of object-based databases, which allow one to deal with complex data types.
Complex application domains require correspondingly complex data types, such as nested record structures,multivalued attributes, and inheritance,which are supported by traditional programming languages.Such features are in fact supported in the E-R and
extended E-R notations, but had to be translated to simpler SQL data types.
Differences between the type system of the database and the typesystem of the programming language make data storage and retrieval more complicated, and need ... (查看原文)
The typical user or programmer of an information-retrieval system thinks of the database in terms of books having sets of authors, as the non-1NF design models. The 4NF design requires queries to join multiple relations, whereas the non-1NF design makes many types of queries easier. (查看原文)
Before SQL:1999, the SQL type system consisted of a fairly simple set of predefined types. SQL:1999 added an extensive type system to SQL, allowing structured types
and type inheritance. (查看原文)
The motivation for using concurrent execution in a database is essentially the same as the motivation for using multiprogramming in an operating system. (查看原文)
In general,a row in a table represents a relationship among a set of values. Since a table is a collection of such relationships, there is a close correspondence between the concept of table and the mathematical concept of relation, from which the relational data model takes its name. In mathematical terminology, a tuple is simply a sequenc(or list) of values. A relationship between n values is reprsented mathematically by a n-tuple of values, i.e. ,a tuple with n values,which corresponds to a row in a table. (查看原文)
The null value is a special value that signifies that the value is unknown or does not exist........We shall see later that null values cause a number of difficulties when we accessor update the database,and thus should be eliminated if at all possible.We shallassume null values are absent initially,and in Section 3.6 we describe the effectof nulls on different operations. (查看原文)
Formally,the unique test on a relation is defined to fail if and only if the relation contains two tuples t₁ and t₂ such that t₁=t₂. Since the test t₁=t₂ fails if any of the fields of t₁ or t₂ are null, it is possible for unique to be true even if there are multiple copies of a tuple,as long as at least one of the attributes of the tuple is null. (查看原文)
the SQL standard says that the sum operator should ignore null values in its input.
In general, aggregate functions treat nulls according to the following rule: Allaggregate functions except count (*) ignore null values in their input collection. As a result of null values being ignored, the collection of values may be empty. The count of an empty collection is defined to be 0, and all other aggregate operations
return a value of null when applied on an empty collection. The effect of null values on some of the more complicated SQL constructs can be subtle.
A Boolean data type that can take values true, false, and unknown, was introduced in SQL:1999. The aggregate functions some and every, which mean exactly what you would intuitively expect, can be applied on a collection of Boolean v... (查看原文)
As with insert and delete, a nested select within an update statement may reference the relation that is being updated. As before, SQL first tests all tuples in the relation to see whether they should be updated, and carries out the updates afterward. (查看原文)
