Chapter 4
Basic SQL
The SQL language may be considered one of the major reasons for the
commercial success of relational databases. Because it became a standard for
relational databases, users were less concerned about migrating their database
applications from other types of database systems—for example, network or
hierarchical systems—to relational systems. This is because even if the users
became dissatisfied with the particular rela-tional DBMS product they were
using, converting to another relational DBMS product was not expected to be too
expensive and time-consuming because both systems followed the same language
standards. In practice, of course, there are many differences between various
commercial relational DBMS packages. However, if the user is diligent in using
only those features that are part of the standard, and if both relational
systems faithfully support the standard, then conversion between the two
systems should be much simplified. Another advantage of having such a standard
is that users may write statements in a database application program that can
access data stored in two or more relational DBMSs without having to change the
database sublanguage (SQL) if both relational DBMSs support standard SQL.
This chapter presents the main features of the SQL standard for commercial rela-tional DBMSs, whereas
Chapter 3 presented the most important concepts underly-ing the formal relational data model. In Chapter
6 (Sections 6.1 through 6.5) we shall discuss the relational algebra operations, which are very important for under-standing
the types of requests that may be specified on a relational database. They are
also important for query processing and optimization in a relational DBMS, as
we shall see in Chapter 19. However, the relational algebra operations are
consid-ered to be too technical for most commercial DBMS users because a query
in rela-tional algebra is written as a sequence of operations that, when
executed, produces the required result. Hence, the user must specify how—that
is, in what order—to execute the
query operations. On the other hand, the SQL language provides a higher-level declarative language interface, so the
user only specifies what the result
is to be, leaving the actual optimization and decisions on how to execute the
query to the DBMS. Although SQL includes some features from relational algebra,
it is based to a greater extent on the tuple
relational calculus, which we describe in Section 6.6. However, the SQL
syntax is more user-friendly than either of the two formal languages.
The name SQL is presently
expanded as Structured Query Language. Originally, SQL was called SEQUEL
(Structured English QUEry Language) and was designed and implemented at IBM
Research as the interface for an experimental relational database system called
SYSTEM R. SQL is now the standard language for commer-cial relational DBMSs. A
joint effort by the American National Standards Institute (ANSI) and the
International Standards Organization (ISO) has led to a standard version of SQL
(ANSI 1986), called SQL-86 or SQL1. A revised and much expanded standard called
SQL-92 (also referred to as SQL2) was subsequently developed. The next standard
that is well-recognized is SQL:1999, which started out as SQL3. Two later
updates to the standard are SQL:2003 and SQL:2006, which added XML fea-tures
(see Chapter 12) among other updates to the language. Another update in 2008
incorporated more object database features in SQL (see Chapter 11). We will try
to cover the latest version of SQL as much as possible.
SQL is a comprehensive database language: It has statements for data definitions, queries, and updates. Hence, it is both a DDL and a DML. In addition, it has facili-ties for defining views on the database, for specifying security and authorization, for defining integrity constraints, and for specifying transaction controls. It also has rules for embedding SQL statements into a general-purpose programming language such as Java, COBOL, or C/C++.
The later SQL standards (starting with SQL:1999) are divided into a core
specifica-tion plus specialized extensions.
The core is supposed to be implemented by all RDBMS vendors that are SQL
compliant. The extensions can be implemented as optional modules to be
purchased independently for specific database applications such as data mining,
spatial data, temporal data, data warehousing, online analytical processing
(OLAP), multimedia data, and so on.
Because SQL is very important (and quite large), we devote two chapters
to its features. In this chapter, Section 4.1 describes the SQL DDL commands
for creating schemas and tables, and gives an overview of the basic data types
in SQL. Section 4.2 presents how basic constraints such as key and referential
integrity are specified. Section 4.3 describes the basic SQL constructs for
specifying retrieval queries, and Section 4.4 describes the SQL commands for
insertion, deletion, and data updates.
In Chapter 5, we will describe more complex SQL retrieval queries, as
well as the ALTER
commands for changing the schema. We will also
describe the
CREATE ASSERTION statement, which allows the
specification of more general constraints on the
database. We also introduce the concept of triggers, which is presented in more
detail in Chapter 26 and we will describe the SQL facility for defining views
on the database in Chapter 5. Views are also called virtual or derived tables
because they present the user with what appear to be tables; however, the
information in those tables is derived from previously defined tables.
Section 4.5 lists some SQL features that are presented in other chapters
of the book; these include transaction control in Chapter 21,
security/authorization in Chapter 24, active databases (triggers) in Chapter
26, object-oriented features in Chapter 11, and online analytical processing
(OLAP) features in Chapter 29. Section 4.6 summarizes the chapter. Chapters 13
and 14 discuss the various database programming techniques for programming with
SQL.
Related Topics
Privacy Policy, Terms and Conditions, DMCA Policy and Compliant
Copyright © 2018-2023 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.