Page 213 - Encyclopedia of Business and Finance

eobf_D 7/5/06 2:59 PM Page 190

Databases

be applied to each row in the table. If a row satisfies the
Customers
customer_no name address phone condition, then it is selected, and the values in that row
1001 Jones 320 Main 555-8811 for the columns listed in the Select clause are included in
1002 Smith 401 Oak 555-8822 the result.
1003 Brown 211 Elm 555-8833
1004 Green 899 Maple 555-8844
Products Query: When have we sold product number 45 to customer 1002?
product_no description price
25 Ring 3.25 SQL solution: Select date
33 Gasket 1.23 From Sales
45 Shaft 4.55 Where product_no = 45 and customer_no = 1002
Result: date
Sales
3/11
sale_no date customer_no product_no 3/12
841 3/11 1002 45
842 3/12 1001 25
843 3/12 1002 45
844 3/13 1004 33
845 3/14 1003 25 In this example one can see that the condition in the
846 3/15 1002 33
Where clause includes the connector “and,” which indi-
cates that both conditions (product_no = 45 and cus-
tomer_no = 1002) must be fulfilled. In the sample
Suppose one wants to know the customer’s name for database there are two rows that satisfy this condition, and
sale number 845. Looking in the customer number col- the query’s result yields the dates from those two rows.
umn of the Sales table, one will see that it was customer The next query gives the SQL solution to the original
1003. Next, one refers to the Customers table and finds query discussed above.
customer 1003. Here one see the customer’s name is
Brown. So, Brown was the customer for sale number 845.

Query: What is the customer's name for sale number 845?
STRUCTURED QUERY LANGUAGE
SQL solution: Select name
The foregoing is a simple example of a database query. In
From Customers, Sales
a modern database, queries are expressed in a query lan- Where sale_no = 845 and Sales.customer_no =
guage, which requires a particular format that can be rec- Customers.customer_no
Result: Brown
ognized and interpreted by the DBMS. The standard
query language for relational databases, as adopted by the
American National Standards Institute (ANSI), is SQL,
which is generally understood to be an abbreviation for This query illustrates how one can query more than one
“structured query language.” Here are a few examples of table at once in SQL. First, one lists all tables needed to
queries expressed in SQL: answer the query. In this case then, one lists the Cus-
tomers and the Sales tables. Then in the Where clause,
one states two conditions:
Query: Which products have a price over $2?
sale_no = 845 and
SQL solution: Select product_no, description Sales.customer_no = Customers.customer_no
From Products
Where price > 2.00
The first condition indicates that the sale_no column
Result: product_no description
must have a value of 845. Because there is only one row in
25 Ring
45 Shaft the Sales table having that value, one has limited one’s
query to that single row. The second condition indicates
that one wants only that row in the Customers table
which has the same value for its customer_no column as
This query’s SQL solution illustrates the SQL format. the Sales row has for its customer_no column. This con-
In general, SQL “statements” have a Select “clause,” a dition then limits one’s result to the joining together of
From “clause,” and a Where “clause.” The Select clause
one row from the Sales table and one row from the Cus-
lists the columns that are to be shown in the result, the
tomers table. Finally, the Select clause,
From clause lists the database tables from which data are
to be taken, and the Where clause gives the condition to Select name

190 ENCYCLOPEDIA OF BUSINESS AND FINANCE, SECOND EDITION