9780321553577 pdf download

Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way.

Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions. All rights reserved. Publishers of technology books, eBooks, and videos for creative people. Add To My Wish List. Part of the Visual QuickStart Guide series. Book Sorry, this book is no longer in print.

Not for Sale. Watermarked eBook FAQ. More Information. Overview Pearson Education, Inc. Collection and Use of Information To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including: Questions and Inquiries For inquiries and questions, we collect the inquiry or question, together with name, contact details email address, phone number and mailing address and any other additional information voluntarily submitted to us through a Contact Us form or an email.

Surveys Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Contests and Drawings Occasionally, we may sponsor a contest or drawing. Newsletters If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email ask peachpit. Service Announcements On rare occasions it is necessary to send out a strictly service related announcement.

Customer Service We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form. Other Collection and Use of Information Application and System Logs Pearson automatically collects log data to help ensure the delivery, availability and security of this site.

Web Analytics Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site.

Cookies and Related Technologies This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Security Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children This site is not directed to children under the age of Marketing Pearson may send or direct marketing communications to users, provided that Pearson will not use personal information collected or processed as a K school service provider for the purpose of directed or targeted advertising. Such marketing is consistent with applicable law and Pearson's legal obligations.

Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing. Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn. Sale of Personal Information Pearson does not rent or sell personal information in exchange for any payment of money.

Supplemental Privacy Statement for California Residents California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. Sharing and Disclosure Pearson may disclose personal information, as follows: As required by law. Page Mail systems Page The anatomy of a mail message Page Mail philosophy Page Mail aliases Page sendmail: ringmaster of the electronic mail circus Page sendmail configuration Page Basic sendmail configuration primitives Page Fancier sendmail configuration primitives Page Configuration file examples Page Spam-related features in sendmail Page Security and sendmail Page sendmail statistics, testing, and debugging Page The Postfix mail system Page Troubleshooting a network Page ping: check to see if a host is alive Page traceroute: trace IP packets Page Packet sniffers Page Network management protocols Page SNMP agents Page Network management applications Page Seven common-sense rules of security Page How security is compromised Page Setuid programs Page Important file permissions Page Miscellaneous security issues Page Security power tools Page Cryptographic security tools Page Firewalls Page Sources of security information Page What to do when your site has been attacked Page Web hosting Page Web hosting basics Page HTTP server installation Page Virtual interfaces Page Caching and proxy servers Page Anonymous FTP server setup Page Usenet news Page Mini-glossary of printing terms Page Types of printers Page BSD printing Page System V printing Page Adding a printer Page LPRng Page Debugging printing problems Page Common printing software Page Printer philosophy Page Maintenance basics Page Maintenance contracts Page Board-handling lore Page Memory modules Page Preventive maintenance Page Environment Page Power Page Racks Page Tools Page What you can do to improve performance Page Factors that affect performance Page System performance checkup Page Help!

My system just got really slow! Page File and print sharing Page Secure terminal emulation with SSH Page X Windows emulators Page PC backups Page PC hardware tips Page Policy and procedure Page Legal issues Page Sysadmin surveys Page Scope of service Page Trouble-reporting systems Page Hiring, firing, and training Page War stories and ethics Page Localization and upgrades Page Local documentation Page Procurement Page Decommissioning hardware Page Software patents Page Organizations, conferences, and other resources Page Standards Page Sample documents Page init: the primordial process List the subset of titles in the result Figure 4.

To see the result of each comparison in Listing 4. Table 5. Operators in the same row have equal precedence. Associativity determines the order of evaluation in an expression when adjacent operators have equal precedence. SQL uses left-to-right associativity. You can use parentheses to override precedence and associativity rules Listing 5.

To run Listing 5. The third and fourth columns show how to use parentheses to override associativity rules. See Figure 5. Determining the Order of Evaluation Chapter 5 Concatenating Strings with Concatenating Strings with Use the operator to combine, or concatenate, strings.

Listing 5. Here, I need to convert sales from an integer to a string. Here, I need to convert pubdate from a datetime to a string. Each operand is a string expression such as a column that contains character strings, a string literal, or the result of an operation or function that returns a string Listings 5.

The efficient way to express the clause is: Title T12 published on Title T06 published on Title T07 published on Figure 5. To run Listings 5. Search your DBMS documentation for concatenation or conversion. Operators and Functions Listing 5. The alphabetic part of a publisher ID is the first character, and the remaining characters are the numeric part.

Figure 5. To extract a substring: Listing 5. Heydemark CO C. Kells NY Figure 5. Your DBMS implicitly might constrain start and length arguments that are too small or too large to sensible values. The substring function silently may replace a negative start with 1 or a too-long length with the length of string, for example. Search your DBMS documentation for substring or substr. Digits, punctuation, and whitespace are left unchanged. All the letters in the LIKE pattern must be uppercase for this query to work.

Your DBMS might provide other stringcasing functions to, say, invert case or convert strings to sentence or title case. Search your DBMS documentation for character functions or string functions. The characters show the extent of the trimmed strings. Both Figure 5. Your result will be either Figure 5. The CHAR 20 conversion shortens the title to make the result more readable.

Widening conversions always are allowed, but narrowing conversions can cause your DBMS to issue a warning or error. You can use Space number to add spaces to strings and Left string, length to truncate strings. Search your DBMS documentation for conversion, cast, or formatting functions. CASE makes no changes to the underlying data. The simple CASE expression compares an expression to a set of simple expressions to determine the result.

All expressions must be of the same type or must be implicitly convertible to the same type. First, value1 is compared. To run Listing 7. Corker E03 Listing 7. See Figure 7. That way, you can compare a column in the first instance of the table to a column in the second instance. As with all joins, your DBMS combines and returns rows of the table that satisfy the join condition. Salter Lord Copper e1. Salter e2.

Corker Mr. Listing 7. A join condition takes this form: Creating a Self-Join alias1. A common type of self-join compares a column in the first instance of the table to the same column in the second instance. This join condition lets you compare the values in a column to one another, as shown in the subsequent examples in this section.

Oracle 9i and later support JOIN syntax. Using a subquery, Listing 7. Adding a join condition retains only those rows in which the two authors differ Listing 7. The first row states that Sarah Buchman lives in the same state as Christian Kells, and the second row gives the same information.

Listing Listing 7. Subsequent sections explain the types of subqueries and their syntax and semantics. Suppose that you want to list the names of the publishers of biographies. The naive approach is to write two queries: one query to retrieve the IDs of all the biography publishers Listing 8.

Understanding Subqueries A better way is to use an inner join Listing 8. Another alternative is to use a subquery Listing 8. The subquery in Listing 8.

A subquery also is called an inner query, and the statement containing a subquery is called an outer query. In other words, an enclosed subquery is an inner query of an outer query.

Remember that a subquery can be nested in another subquery, so inner and outer are relative terms in statements with multiple nested subqueries. Listing 8. See Figure 8. Note that the inner query in Listing 8. You still must terminate the statement that contains the subquery with a semicolon.

A subquery returns an intermediate result that you never see, so sorting a subquery makes no sense. The SQL standard categorizes a subquery by the number of rows and columns it returns Table 8.

In all cases, the subquery also can return an empty table zero rows. This built-in limit typically exceeds the limit of human comprehension. Microsoft SQL Server, for example, allows 32 levels of nesting. Table 8.

Many subqueries can be formulated alternatively as joins. In fact, a subquery is a way to relate one table to another without actually doing a join. Because subqueries can be hard to use and debug, you might prefer to use joins, but you can pose some questions only as subqueries. In cases where you can use subqueries and joins interchangeably, you should test queries on your DBMS to see whether a performance difference exists between a statement that uses a subquery and a semantically equivalent version that uses a join.

Joins Listing 8. You always can express an inner join as a subquery, but not vice versa. This asymmetry occurs because inner joins are commutative; you can join tables A to B in either order and get the same answer. Subqueries lack this property.

For information about aggregate functions, see Chapter 6. Figure 8. A correlated subquery is used if a statement needs to process a table in the inner query for each row in the outer query. This section gives an example of a simple subquery and a correlated subquery and then describes how a DBMS executes each one.

Subsequent sections in this chapter contain more examples of each type of subquery. Simple subqueries A DBMS evaluates a simple subquery by evaluating the inner query once and substituting its result into the outer query. A simple subquery executes prior to, and independent of, its outer query.

Subqueries Listing 8. The inner query a simple subquery returns the cities of all the publishers Listing 8. Correlated subqueries offer a more powerful data-retrieval mechanism than simple subqueries do. In the context of correlated subqueries, these qualified named are called correlation variables. The correlation variable candidate. This process continues until all the candidate rows have been processed.

Subqueries In Listing 8. It needs a value for candidate. The column average. The average sales for a book type are calculated in the subquery by using the type of each book from the table in the outer query candidate. The subquery computes the average sales for this type and then compares it with a row in the table candidate. If the sales in the table candidate are greater than or equal to average sales for the type, that book is displayed in the result. The DBMS repeats this process until every row in the outer table candidate has been tested.

The book type in the first row of candidate is used in the subquery to compute average sales. Take the row for book T01, whose type is history, so the value in the column type in the first row of the table candidate is history. In effect, the subquery becomes: 2. Listings 8. Why do I say that a statement that uses a simple subquery probably will run faster than an equivalent statement that uses a correlated subquery when a correlated subquery clearly requires more work? MySQL 4. To run Listings 8.

This query probably will run slower than Listing 8. In statements that contain subqueries, column names are qualified implicitly by the table referenced in the FROM clause at the same nesting level. In Listing 8. Abatis Publishers Schadenfreude Press Figure 8. A subquery can hide a comparison to a null.

Consider the following two tables, each with one column. The first table is named table1: col Listing 8. This result is an empty table, which is correct logically but not what I expected. Why is the result empty this time? The solution requires some algebra. I can move the NOT outside the subquery condition without changing the meaning of Listing 8. Refer to the AND truth table Table 4. To fix Listing 8. Recall from Table 8.

The aggregate function AVG guarantees that each subquery returns a single value. For a more efficient way to implement this query, see the Tips in this section. See Listing You should qualify every column name explicitly in a subquery that contains a join to make it clear which table is referenced even when qualifiers are unnecessary.

Subqueries introduced with comparison operators often use aggregate functions to return a single value. No publisher named XXX exists, so the subquery returns an empty table zero rows.

The comparison evaluates to null, so the final result is empty. Again, the subquery returns a single value the average of all sales. The subquery calculates the highest royalty share for each book being considered for selection in the outer query. For each possible value of ta1, the DBMS evaluates the subquery and puts the row being considered in the result if the royalty share is less than the calculated maximum.

Comparing a Subquery Value Listing 8. For each possible value of t1, the DBMS evaluates the subquery and includes the row in the result if the price value in that row exceeds the calculated average. You also can use a subquery to generate the list.

A subquery that returns more than one column will cause an error. The DBMS evaluates this statement in two steps. First, the inner query returns the IDs of the publishers that have published biographies P01 and P Second, the DBMS substitutes these values into the outer query, which finds the names that go with the IDs in the table publishers.

Listing Listing 8. Finally, the outermost query uses the author IDs to find the names of the authors. To determine whether an author is a coauthor or the sole author of a book, examine his or her royalty share for the book.

If the royalty share is less than percent 1. The DBMS considers each row in the outer-query table authors to be a candidate for inclusion in the result. When the DBMS examines the first candidate row in authors, it sets the correlation variable a.

Chapter 8 Listing 8. The inner query returns the author IDs of sole authors, and the outer query compares these IDs with the IDs of the coauthors. You can rewrite Listing 8.

To run Listing 8. The subquery returns the same number of columns as there are in the list. The DBMS compares the values in corresponding columns. ALL means greater than every value in the subquery result. ALL means greater than every subquery value—that is, greater than the maximum value.

You might find this result to be counterintuitive. For example, the query Subqueries Listing 8. The ALL condition evaluates to true if all values in subquery satisfy the ALL condition or if the subquery result is empty has zero rows.

The inner query finds all the biography prices. The outer query inspects the lowest price in the list and determines whether each nonbiography is cheaper. The inner query uses a join to find the sales of each book by author A The outer query inspects the highest sales figure in the list and determines whether each book sold more copies. The inner query is evaluated once for each group defined in the outer query once for each type of book.

ANY means greater than at least one value in the subquery result. ANY means greater than at least one subquery value— that is, greater than the minimum value. If any at least one value in subquery satisfies the ANY condition, the condition evaluates to true.

The ANY condition is false if no value in subquery satisfies the condition or if subquery is empty has zero rows or contains all nulls. You can use IN to replicate Listing 8. The outer query inspects the highest price in the list and determines whether each nonbiography is cheaper.

Unlike the ALL comparison in Listing 8. The outer query inspects the lowest sales figure in the list and determines whether each book sold more copies. Again, unlike the ALL comparison in Listing 8.

I can replicate Listing 8. Listing specific column names is unnecessary, because EXISTS simply tests for the existence of rows that satisfy the subquery conditions; the actual values in the rows are irrelevant.

See Listing 8. Here, the first publisher is P01 Abatis Publishers. If so, Abatis Publishers is included in the final result. See Listing 9. This query is equivalent to Listing 8. The existence test in Listing 8.

I could argue that the result, Figure 8. Additionally, in Listing 8. Additionally, in Listings 8. For example, change Listing 8.

Each of the statements in Listing 8. The first two queries inner joins will run at the same speed as one another.

Of the third through sixth queries which use subqueries , the last one probably is the worst performer. The DBMS will stop processing the other subqueries as soon as it encounters a single matching value. But the subquery in the last statement has to count all the matching rows before it returns either true or false. Entire careers are devoted to solving these types of optimization problems.

A03 A04 A05 A06 Figure 8. DBMSs provide tools to let you measure the efficiency of queries. Tables 8. Performance tuning involves some platform-independent general principles, but the most effective tuning relies on the idiosyncrasies of the specific DBMS.

Tuning is beyond the scope of this book, but the internet has plenty of discussion groups and articles—search for tuning or performance or optimization together with the name of your DBMS. If you look up one of these books on Amazon. But whereas mathematical sets are unchanging, database sets are dynamic—they grow, shrink, and otherwise change over time. This operation differs from a join, which combines columns from two tables.

See Figure 9. The sort is applied to the final, combined result. Listing 9. Set Operations Listing 9. The number and the order of the columns must be identical in both statements, and the data types of corresponding columns must be compatible. Duplicate rows are eliminated from the result unless ALL is specified.

The AS clause in the first query names the column in the result. Chapter 9 Listing 9. Figure 9. The results are DBMS dependent. The second statement includes duplicates in the union of table1 and table2 but eliminates duplicates in the subsequent union with table3, so ALL has no effect on the final result of this statement. To run Listings 9.

To run Listing 9. Duplicate rows are eliminated from the result. Each of the following statements is equivalent to Listing 9. Unlike SELECT, which only accesses data, these statements change data, so your database administrator might need to grant you permission to run them. This section explains how to use those tools to display table definitions for the current database.

The osql and sqlcmd commands display a few pages that speed by. Figure To display table definitions in Oracle: Figure Type describe table; and then press Enter Figure Table To modify table definitions, see Chapter See Figure The number of values must equal the number of columns in table, and the values must be listed in the same sequence as the columns in table. This statement adds one row to table Listing The number of values must equal the number of columns in the column list, and the values must be listed in the same sequence as the column names.

The DBMS inserts each value into a column by using corresponding list positions. An omitted column is assigned its default value or null. This statement adds one row to table. Chapter 10 You can omit column names if you want to provide values for only some columns explicitly Listing The DBMS inserts nulls into the omitted columns automatically. The number of columns in the subquery result must equal the number of columns in table or in the column list.

The first column in the subquery result is used to populate the first column in table or column1, and so on. This statement adds zero or more rows to table. This statement inserts one row into publishers; see Figure Listing This statement inserts two rows into publishers; see Figure This statement inserts no rows into publishers because no publisher is named XXX; see Figure This statement has no effect on the target table.

For information about transactions, see Chapter The value returned by expr replaces the existing value in column.

This statement updates 13 rows; see Figure This statement updates three rows; see Figure This statement updates two rows; see Figure You can update values in a given table based on the values stored in another table. Each listing updates values in a different column or columns from those in the other listings.

The updated values in each column are shown in red. The updated values are shown in red. These operations informally are called upserts. To run Listing Then run the statements programmatically in a host language such as Visual Basic or C , using the result of the first statement as the input for the second statement.

To run Listings The changes are Listing Even if you remove all rows from a table, the table itself still exists. This statement deletes 13 rows; see Figure This statement deletes two rows; see Figure This statement deletes 12 rows; see Figure These statements modify database objects and data, so your database administrator might need to grant you permission to run them.

To a user or an SQL programmer, a database appears to be a collection of one or more tables and nothing but tables. Constraints define properties such as nullability, keys, and permissible values. An optional list of table constraints follows the final column definition.

By convention, I start each column definition and table constraint on its own line. Creating, Altering, and Dropping Tables Table Your DBMS uses these rules to enforce the integrity of information in the database automatically. Constraints come in two flavors: A column constraint is part of a column definition and imposes a condition on that column only. You must use a table constraint to include more than one column in a single constraint. If a primary key contains one column, for example, you can define it as either a column constraint or a table constraint.

If the primary key has two or more columns, you must use a table constraint. Constraint names are optional, but many SQL programmers and database designers name all constraints. Constraint names also appear in warnings, error messages, and logs, which is another good reason to name constraints yourself. Constraints names must be unique within a table. You must create at least one column.

The table name must be unique within the database, and each column name must be unique within the table. If the nullability constraint is omitted, the column accepts nulls. The database designer makes these types of decisions before creating a table.

Where omitted, the nullability constraint defaults to allow nulls. For the table authors created by Listing MySQL, for example, assigns a default value of zero to numeric, non-nullable columns without explicit defaults. The pubdate and contract defaults show that the defaults can be expressions more complex than plain literals. Where no default is specified, the DBMS inserts a null.

If no default is specified, NULL is assumed. In practice, you always should define a primary key for every table. The database designer picks one of the candidate keys to be the primary key. Listings This syntax shows the easiest way to create a simple primary key.

This restriction is called referential integrity. In practice, foreign-key constraints almost always are named explicitly. Poor design can lead to time-consuming routine queries, circular rules, tricky backup-and-restore operations, and psychotically ambitious cascading deletes. Updating a row in the foreign-key table.

Deleting a row in the foreign-key table. A referential-integrity check is unnecessary. Inserting a row into the parent table.

Updating a row in the parent table. Deleting a row from the parent table. This syntax shows the easiest way to create a simple foreign key. This syntax shows the preferred way to add foreign keys; you can use the names if you decide to change or delete the keys later.

Each foreign-key column is an individual key and not part of a single composite key. This action is the default. An ISBN is a unique, standardized identification number that marks a book unmistakably.

A simple unique constraint can be a column constraint or a table constraint; a composite unique constraint always is a table constraint. A unique constraint is similar to a primary-key constraint, except that a unique column can contain nulls and a table can have multiple unique columns. This syntax shows the easiest way to create a simple unique constraint. This syntax shows the preferred way to add a unique constraint; you can use the name if you decide to change or delete the constraint later.

Zero or more unique constraints are allowed in a table. The SQL standard allows any number of nulls in a nullable, unique column. So far, the only restrictions on an inserted value are that it have the proper data type, size, and range for its column. You can use check constraints to further limit the values that a column or set of columns accepts. Minimum or maximum values. Prevent sales of fewer than zero items, for example. Specific values.

A range of values. They differ in how they determine which values are allowed. A foreign-key constraint gets the list of valid values from another table, whereas a check constraint determines the valid values by using a logical Boolean expression.

SQL also lets you create temporary tables to use for working storage or intermediate results. A temporary table is a table that the DBMS empties automatically at the end of a session or transaction. Creating, Altering, and Dropping Tables Listing It dematerializes when your DBMS session ends.

LOCAL specifies that table is a local temporary table. It dematerializes when your DBMS session ends and all other tasks have stopped referencing it. In some cases, for example, a temporary table will hide, or occlude, the like-named base table until the temporary table is dropped. DBMSs vary in how they implement temporary tables with respect to their persistence, visibility, constraints, foreign keys referential integrity , indexes, and views; search your DBMS documentation for temporary tables.

As a last resort, you can re-create and repopulate the table in its desired state manually. To re-create and repopulate a table: 1. You also need to re-create any other properties that were dropped along with the old table, such as permissions and triggers.

Searching an entire table in a small database is trivial, but production database tables can have millions of rows. DBMSs provide a mechanism called an index that has the same purpose as its book or library counterpart: speeding data retrieval.

At a simplified level, an index is a sorted list in which every distinct value in an indexed column or set of columns is stored with the disk address physical location of the rows containing that value. Instead of reading an entire table to locate specific rows, the DBMS scans only the index for addresses to access directly.

Indexed searches typically are orders of magnitude faster than sequential searches, but some tradeoffs are involved, as explained in this chapter. In practice, index management is an iterative process.

Typically, only vital indexes are created along with the table.