Database Reference
In-Depth Information
Data extracts are Common
Before we continue, realize that the data extraction process described here is not just an
academic exercise. To the contrary, such extraction processes are realistic, common, and im-
portant BI system operations. Right now, hundreds of businesses worldwide are using their BI
systems to create extract databases just like the one created by Cape Codd.
In the next sections of this chapter, you will learn how to write SQL statements to process
the extracted data via ad hoc
SQL queries
, which is how SQL is used to “ask questions” about
the data in the database. This knowledge is exceedingly valuable and practical. Again, right
now, as you read this paragraph, hundreds of people are writing SQL to create information
from extracted data. The SQL you will learn in this chapter will be an essential asset to you as
a knowledge worker, application programmer, or database administrator. Invest the time to
learn SQL—the investment will pay great dividends later in your career.
SQL was developed by the IBM Corporation in the late 1970s. It was endorsed as a na-
tional standard by the
American National Standards Institute (ANSI)
in 1986 and by the
International Organization for Standardization (ISO)
(and no, that's not a typo—the ac-
ronym is
ISO,
not
IOS
!) in 1987. Subsequent versions of SQL were adopted in 1989 and 1992.
The 1992 version is sometimes referred to as SQL-92 and sometimes as ANSI-92 SQL. In 1999,
SQL:1999 (also referred to as SQL3), which incorporated some object-oriented concepts, was
released. This was followed by the release of SQL:2003 in 2003; SQL:2006 in 2006; SQL:2008
in 2008; and, most recently, SQL:2011 in 2011. Each of these added new features or extended
existing SQL features, the most important of which for us are the SQL standardization of the
INSTEAD OF trigger (SQL triggers are discussed in Chapter 7) in SQL:2008 and the support for
Extensible Markup Language (XML)
(XML is discussed in Chapter 11) added in SQL:2009.
Our discussions in this chapter and in Chapter 7 mostly focus on common language features
that have been in SQL since SQL-92, but does include some features from SQL:2003 and
SQL:2008. We discuss the SQL XML features in Chapter 11.
By The Way
Although there is an SQL
standard
, that does not mean the that SQL is
standardized
across DBMS products! Indeed, each DBMS implements
SQL in its own peculiar way, and you will have to learn the idiosyncrasies of the SQL
dialect your DBMS uses.
In this topic, we are using Microsoft's SQL Server 2012 SQL syntax, with some limited
discussion of the different SQL dialects. The Oracle Database 11
g
Release 2 SQL syntax is
used in Chapter 10B, and the MySQL SQL 5.6 SQL syntax is used in Chapter 10C.
SQL is not a complete programming language, like Java or C#. Instead, it is called a
data
sublanguage
because it has only those statements needed for creating and processing data-
base data and metadata. You can use SQL statements in many different ways. You can submit
them directly to the DBMS for processing. You can embed SQL statements into client/server
application programs. You can embed them into Web pages, and you can use them in report-
ing and data extraction programs. You also can execute SQL statements directly from Visual
Studio.NET and other development tools.
SQL statements are commonly divided into categories, five of which are of interest to us here:
Data definition language (DDL)
statements, which are used for creating tables,
relationships, and other structures
●
Data manipulation language (DML)
statements, which are used for querying,
inserting, modifying, and deleting data
●
SQL/Persistent stored modules (SQL/PSM)
statements, which extend SQL by
adding procedural programming capabilities, such as variables and flow-of-control
statements, that provide some programmability within the SQL framework.
●
Search WWH ::
Custom Search