Example 3-12: Aggregate Functions
In reviewing the Example 3-11 results, you probably noticed that a lot of rows were
returned—352 to be exact (you may get a different number of rows if you use a ver
-
sion other than Microsoft Access 2000). Also, there are many rows for each cus
-
tomer. Not only do customers have many orders, but also each order can have many
rows. All the details are here, but at a glance, it is difficult to easily get a sense of the
total amount that each customer has ordered from Northwind. What we really need
80
Databases Demystified
Figure 3-21 Example 3-11, “Multiple Joins; Calculated Columns” (top) and the query
results (bottom)
P:\010Comp\DeMYST\364-9\ch03.vp
Monday, February 09, 2004 8:42:48 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 3 Forms-Based Database Queries
81
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 3
to do is sum up the ExtPrice column for each customer. In relational databases, this is
done with the SUM function.
A function is a special type of program that returns a single value each time it is in
-
voked, named for the mathematical concept of a function. Because we will use the
function to operate on a column, it will be invoked for each row and therefore return
a single value for each row the query handles. Sometimes the term column function
is used to remind us that the function is being applied to a table or view column. An
example of an ordinary column function is ROUND, which can be used to round
numbers in various ways. Special classes of functions that combine multiple rows

Monday, February 09, 2004 8:42:48 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
82
Databases Demystified
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 3
ask for some rows to be aggregated and others not. To make matters worse, the re
-
sulting error message is rather cryptic. Small wonder that we often hear aggregate
functions called “aggravating” functions. Remember this rule: Whenever a query in
-
cludes an aggregate function, then
every column in the query results must either be
formed using an aggregate function or be named in the GROUP BY column list. In
Microsoft Access, the Totals button on the toolbar toggles (hides and exposes) a line
called Total on the Query View panel. It is the total line that permits us to specify
aggregate functions and groupings for our query.
To create the Example 3-12 query from the Example 3-11 query, follow these steps:
1. Remove the UnitPrice and Quantity columns by clicking in the small gray
rectangle above the field name and pressing
DELETE
.
2. Change the label on the ExtPrice column to TotalOrders. This column name
will make more sense in the results.
3. Click the toolbar’s Totals button (the one with the Greek letter Sigma on it)
to expose the Total line in the query specification. By default, each column
will initially have “Group By” specified on that line.
4. In the TotalOrders column, click in the Total line and use the pull-down
menu to select the Sum function.

(on the thin and slanted part) and select option 2.
CHAPTER 3 Forms-Based Database Queries
83
Figure 3-22 Example 3-12, “Aggregate Functions” (top), and the query results
(bottom)
P:\010Comp\DeMYST\364-9\ch03.vp
Monday, February 09, 2004 8:42:48 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
5. Select the EmployeeID, FirstName, and LastName columns from the
Employees table.
6. Select the LastName column from the Employees_1 table. This is the
manager’s last name.
7. Give the manager’s last name column a label of “Manager.”
The completed panel and query results are shown in Figure 3-23.
84
Databases Demystified
Figure 3-23 Example 3-13, “Self-Joins” (top), and the query results (bottom)
P:\010Comp\DeMYST\364-9\ch03.vp
Monday, February 09, 2004 8:42:49 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Hopefully, you have enjoyed this introduction to Microsoft Access queries. We have
only scratched the surface in these examples, and there is much more to be learned from
experience and experimentation. For example, once a query is saved in the Microsoft
Access database, it can be included in other queries. There is no firm limit to how many
levels of abstraction you can build using this method, and you will find that breaking
queries into parts helps simplify the most complex ones you will encounter.

85
P:\010Comp\DeMYST\364-9\ch03.vp
Monday, February 09, 2004 8:42:49 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
86
Databases Demystified
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 3
c. Can never be recursive relationships
d. Are inherited in queries as table joins
e. Can be one-to-many, one-to-one, or many-to-many
5. The Microsoft Access Show Table dialog box:
a. Lists all tables in the database and allows for the metadata about tables
to be added, changed, and deleted
b. Lists only tables stored in the database
c. Lists tables and/or queries stored in the database
d. Lists only queries stored the database
e. Provides the ability to show (display) or hide (not display) tables
6. A column in the results of a Microsoft Access query can be formed from:
a. A table column
b. A view column
c. A constant
d. A calculation
e. Anything for which a formula may be composed
7. When a query with no criteria included is executed, the result is
a. An error message
b. No rows being displayed
c. All the rows in the table being displayed
d. A Cartesian product

to a column of another table or view
b. May be inherited from the metadata defined on the Relationships panel
c. May be altered to define left, right, and full outer joins
d. Can cause a Cartesian product if defined incorrectly
e. Will cause a Cartesian product if not defined between two tables or
views in the query
12. When an outer join is used, column data from tables (or views) where no
matching rows were found:
a. Displays as zero for numeric column types
b. Displays as blank for character column types
c. Displays in gray
d. Displays the text “NULL”
e. Is set to the NULL value
13. An aggregate function:
a. Combines data from multiple columns together
b. Combines data from multiple rows together
c. May be applied to table columns but not to calculated columns
d. Is a special type of database query function
e. Requires that every column in a query be either an aggregate function or
named in the GROUP BY list for the query
14. Common aggregate functions include
a. AVG
b. COUNT
c. ROUND
d. SUM
e. MIX
15. Self-joins:
a. Can never produce a Cartesian product because the two data sources
come from the same table
b. Always produce a Cartesian product

e. Provide a way to generate SQL statements
19. When a column is deleted from a Microsoft Access query:
a. The column is only removed from the current query.
b. The column is removed from all queries that reference it.
c. The column is removed from the table and all queries that reference it.
d. An error message is displayed if the column is used in any other queries.
e. The column remains in the query but is marked so the column data will
not be displayed in the query results.
20. A Cartesian product:
a. Results when a join between two tables in a query is not defined
b. Results when a join between two tables in a query is incorrectly defined
c. Results whenever a table is joined to itself
d. Results when each row in one table is joined to every row in another
e. Can never happen in a Microsoft Access query
88
Databases Demystified
P:\010Comp\DeMYST\364-9\ch03.vp
Monday, February 09, 2004 8:42:49 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER
4
Introduction
to SQL
This chapter introduces SQL, which has become the universal language for rela
-
tional databases in that nearly every DBMS in modern use supports it. The reason for
this wide acceptance is clearly the time and effort that went into the development of
language features and standards, making SQL highly portable across different

ing, Oracle will not run an SQL statement unless it ends with a semicolon or a slash
(the slash being an Oracle extension to the standard). Beyond those restrictions, the
language is freeform, with one or more spaces separating language elements, and
line breaks permitted between any two elements (but not in the middle of elements).
SQL statements may be divided into the following categories:
•
Data Query Language (DQL) Statements that query the database but do
not alter any data or database objects. This category contains the SELECT
statement. Not all vendors make a distinction here; many lump DQL into
DML, as defined next.
•
Data Manipulation Language (DML) Statements that modify data
stored in database objects (that is, tables). This category contains the INSERT,
UPDATE, and DELETE statements.
•
Data Definition Language (DDL) Statements that create and modify
database objects. Whereas DML and DQL work with the data in the database
objects, DDL works with the database objects themselves. Said another way,
DDL manages the data containers whereas DML manages the data inside
the containers. This category includes the CREATE, ALTER and DROP
statements.
•
Data Control Language (DCL) Statements that manage privileges that
database users have regarding the database objects. This category includes
the GRANT and REVOKE statements.
Representative statements in each of these categories are presented in the sections
that follow. But first, we’ll cover a little bit of the history of the language.
The History of SQL
The forerunner of SQL, which was called QUEL, first emerged in the specifications
for System/R, IBM’s experimental relational database, in the late 1970s. However,

language extensions to make SQL computationally complete (adding looping,
branching, and case constructs). Only a few vendors have implemented significant
components of the SQL3 standard—Oracle being one of them.
Nearly every vendor has added extensions to SQL, partly because they wanted to
differentiate their products, and partly because market demands pressed them into
implementing features before there were standards for them. One case in point is
support for the DATE and TIMESTAMP data types. Dates are highly important in
business data processing, but the developers of the original RDBMS products were
computer scientists and academics, not business computing specialists, so such a
need was unanticipated. As a result, the early SQL dialects did not have any special
support for dates. As commercial products emerged, vendors responded to pressure
from their biggest customers by hurriedly adding support for dates. Unfortunately,
this led to each doing so in their own way. Whenever you migrate SQL statements
from one vendor to another, beware of the SQL dialect differences. SQL is highly
compatible and portable across vendor products, but complete database systems can
seldom be moved without some adjustments.
Getting Started with Oracle SQL
Oracle provides two different client tools for managing the formation and execution
of SQL statements and the presentation of results: SQL Plus and the SQL Plus
Worksheet. We call these client tools because they normally run on the database
user’s workstation and are capable of connecting remotely to databases that run on
P:\010Comp\DeMYST\364-9\ch04.vp
Monday, February 09, 2004 9:03:16 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
92
Databases Demystified
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 4
other computer systems, which are often shared servers. It is not unusual for the cli

Oracle HR sample schema, enter HR into the Username field and then supply the
password and host string you obtained from your DBA. The host string helps SQL
Plus find the database if it is running on a remote computer system; it is normally not
needed if you are running SQL Plus on the same computer that is running the data
-
base. After SQL Plus has connected to the database, a window similar to the one
shown here is displayed.
P:\010Comp\DeMYST\364-9\ch04.vp
Monday, February 09, 2004 9:03:16 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Note that if you installed Oracle yourself, the demonstration accounts, such as
HR, are usually locked during the installation as a security precaution. You will have
to connect to the database as the SYSTEM user and do the following:
1. Unlock the HR database user account with this SQL command:
ALTER USER HR ACCOUNT UNLOCK;
2. Change the HR database user password with this SQL command (the password
has been set to HRPASS here, but you may use any password you wish):
ALTER USER HR IDENTIFIED BY HRPASS;
SQL statements and SQL Plus commands may be entered at the SQL> prompt.
Results display after each command, and the screen scrolls as needed. SQL Plus
commands help configure SQL Plus, such as setting the width of lines on the screen
and the number of lines displayed per page of output. Other SQL Plus commands
control the format of the output of SQL statements, such as setting page titles, for-
matting columns, and adding subtotals to reports. SQL Plus commands are beyond
the scope of this book, but they may be found in the SQL Plus User’s Guide and Ref-
erence manual available (along with most other Oracle manuals) on the Oracle
Technology Network website (http://otn.oracle.com).
One very useful SQL Plus command we will look at, however, is the DESCRIBE

Figure 4-2 SQL Plus window with wrapped lines
P:\010Comp\DeMYST\364-9\ch04.vp
Monday, February 09, 2004 9:03:17 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Oracle8i. When SQL Plus Worksheet is started from the Windows Start menu, the
login window appears, as shown here:
The Username and Password fields should be familiar from the SQL Plus discus
-
sion, and the Connect String field from SQL Plus is now called Service instead. The
Connect As field is for use by DBAs who require a special role (a named set of privi
-
leges) when they connect.
Once connected, the SQL Plus Worksheet panel appears, as shown in Figure 4-4.
SQL statements may be typed in the upper window, and the results are shown in the
lower window. The icons in the toolbar at the top of the left margin provide various
control functions, including disconnecting from the database, executing the current
SQL statement, scrolling back and forth through a history of recent statements, and
accessing the help facility.
CHAPTER 4 Introduction to SQL
95
Figure 4-3 SQL Plus window, command-line version
P:\010Comp\DeMYST\364-9\ch04.vp
Monday, February 09, 2004 9:03:17 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The SQL Plus Worksheet panel is used for the presentation of the examples that
follow because of its superior formatting of query results.

names, the DESCRIBE command (already introduced) can be used on each
to show more information about the table definitions. Figure 4-5 shows an
example of selecting everything from the USER_TABLES view.
The SQL SELECT statement, shown in Figure 4-5, is described in more
detail a little further along in this chapter.
•
USER_VIEWS Contains one row of information for each view in the
user schema, containing, among other things, the name of the view and
the text of the SQL statement that forms the view.
CHAPTER 4 Introduction to SQL
97
Figure 4-5 Selecting from the USER_TABLES view
P:\010Comp\DeMYST\364-9\ch04.vp
Monday, February 09, 2004 9:03:17 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
98
Databases Demystified
Demystified / Databases Demystified / Oppel/ 225364-9 / Chapter 4
Viewing Database Objects Using
Oracle Enterprise Manager
For those less inclined to type SQL commands, Oracle provides a GUI tool known as
Oracle Enterprise Manager (OEM). Other RDBMS vendors provide similar tools, such
as the Enterprise Manager tool that comes with Sybase and Microsoft SQL Server.
The Oracle Enterprise Manager Console can be started from the Windows Start
menu, by choosing Start | Programs | Oracle - OraHome92 | Enterprise Manager
Console.
Once started, OEM presents a window asking whether it should be launched in
standalone mode or if instead you wish to log in to the Oracle management server.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
8. Click the plus sign (+) next to Tables to expand the list of tables in the
HR schema.
9. Click the EMPLOYEES table to display its description in the right panel.
OEM is so full of features that describing them in detail would take an entire book
of at least this size. The feature you will be most interested in is the hierarchical tree
of databases and database objects that appears in the column along the left margin of
the panel. Expanding the Schema item shows all the schemas in the database (each
Oracle database user gets their own schema). Expanding any schema shows the ob
-
ject types available in that schema. Expanding any object type (as we did with the
Tables type) shows a list of objects of that type in the selected schema, and clicking
or expanding any individual object shows more information about that object (as we
did by clicking the EMPLOYEES table object).
You’ve seen a little bit of the SQL SELECT statement so far. In the next section
we take a detailed look at SQL.
CHAPTER 4 Introduction to SQL
99
Figure 4-6 Oracle Enterprise Manager Console
P:\010Comp\DeMYST\364-9\ch04.vp
Monday, February 09, 2004 9:03:18 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.