Database Reference
In-Depth Information
Wow! That was a full chapter!
Structured Query Language (SQL) was developed by
IBM and has been endorsed by the ANSI SQL-92 and follow-
ing standards. SQL is a data sublanguage that can be embed-
ded into full programming languages or submitted directly to
the DBMS. Knowing SQL is critical for knowledge workers,
application programmers, and database administrators.
All DBMS products process SQL. Microsoft Access hides
SQL, but Microsoft SQL Server, Oracle Database, and MySQL
require that you use it.
We are primarily interested in five categories of SQL
statements: DML, DDL, SQL/PSM statements, TCL, and
DCL. DML statements include statements for querying data
and for inserting, updating, and deleting data. This chap-
ter addresses only DML query statements. Additional DML
statements, DDL and SQL/PSM are discussed in Chapter 7.
TCL and DCL are discussed in Chapter 9.
The examples in this chapter are based on three tables ex-
tracted from the operational database at Cape Codd Outdoor
Sports. Such database extracts are common and important.
Sample data for the three tables is shown in Figure 2-5.
The basic structure of an SQL query statement is
SELECT/FROM/WHERE. The columns to be selected are
listed after SELECT, the table(s) to process is listed after
FROM, and any restrictions on data values are listed after
WHERE. In a WHERE clause, character and date data values
must be enclosed in single quotes. Numeric data need not be
enclosed in quotes. You can submit SQL statements directly
to Microsoft Access, Microsoft SQL Server, Oracle Database,
and MySQL, as described in this chapter.
This chapter explained the use of the following SQL
clauses: SELECT, FROM, WHERE, ORDER BY, GROUP BY,
and HAVING. This chapter explained the use of the following
SQL keywords: DISTINCT, DESC, ASC, AND, OR, IN, NOT IN,
BETWEEN, LIKE, % (* for Microsoft Access), _ (? for Microsoft
Access), SUM, AVG, MIN, MAX, COUNT, and AS. You should
know how to mix and match these features to obtain the re-
sults you want. By default, the WHERE clause is applied before
the HAVING clause.
You can query multiple tables using subqueries and
joins. Subqueries are nested queries that use the SQL key-
words IN and NOT IN. An SQL SELECT expression is placed
inside parentheses. Using a subquery, you can display data
from the top table only. A join is created by specifying multi-
ple table names in the FROM clause. An SQL WHERE clause
is used to obtain an equijoin. In most cases, equijoins are the
most sensible option. Joins can display data from multiple
tables. In Chapter 8, you will learn another type of subquery
that can perform work that is not possible with joins.
Some people believe the JOIN ON syntax is an easier
form of join. Rows that have no match in the join condi-
tion are dropped from the join results when using a regular,
or INNER, join. To keep such rows, use a LEFT OUTER or
RIGHT OUTER join rather than an INNER join.
/* and */
ad-hoc queries
American National Standards Institute (ANSI)
AV G
business intelligence (BI) systems
correlated subquery
COUNT
CRUD
data control language (DCL)
data definition language (DDL)
data manipulation language (DML)
data mart
data sublanguage
data warehouse
data warehouse DBMS
equijoin
Extensible Markup Language (XML)
Extract, Transform, and Load (ETL) system
graphical user interface (GUI)
inner join
International Organization for
Standardization (ISO)
join
join operation
joining the two tables
MAX
Microsoft Access asterisk (*) wildcard
character
Microsoft Access question mark (?)
wildcard character
Search WWH ::
Custom Search