SQL programming language

668 SQL All-in-One For Dummies, 2nd Edition constructors, UDT, 115 correlated subqueries CURRENT_ROLE special CONTACT table, 278, 284 with comparison value, 396 CONTACTINFO table, 530 operator, 285–287 CURRENT_TIME function, containment hierarchy, 74 def ned, 276 220 CONTENT predicate, 539 in HAVING clause, CURRENT_TIMESTAMP context switching, 602 287–288 function, 220 CONTINUE handler effect, with IN keyword, CURRENT_USER special 411, 413 284–285 variable, 210 contrasting operating tuning, 297–302 cursor declarations, modes, 440 using as existence test, module, 449 control statements 283–284 CURSOR_NAME f eld, FOR . . . DO . . . COUNT function, 211–212 416, 419 END FOR, 493 CPU registers, 594 cursors CASE . . . END CASE crackers, def ned, 347 closing, 333 searched CASE CREATE command, 44, 100 declaring statement, 490 CREATE DATABASE within compound simple CASE statement, statement, 96 statements, 488 489–490 CREATE DOMAIN declaring result set IF . . . THEN . . statement, 81, 199 cursors, 329 . ELSE . . . END CREATE FUNCTION holding cursors, 329 IF, 489 statement, 618 ordering query result ITERATE, 493 CREATE INDEX sets, 325–326 LEAVE, 491–492 statement, 188 overview, 324–325 LOOP . . . ENDLOOP, CREATE SCHEMA query expressions, 325 491 statement, 81 scrolling cursors, 328 overview, 488–489 CREATE statement, 75, sensitive versus REPEAT . . . 193, 383, 464, 474–475 insensitive cursors, UNTIL . . . CREATE TABLE 327–328 END REPEAT, 492 statement, 75, 194, updating table rows, WHILE . . . DO . . 196–197, 290, 543 327 . END WHILE, 492 CREATE TRIGGER def ned, 652 conventions used in statement, 496 improving performance book, 2 CREATE TYPE statement, of, 618 CONVERT string value 529 opening, 329–330 function, 215 CREATE VIEW statement, operating on single row converting data types, 76 absolute versus relative 445 cross joins (Cartesian fetches, 332 cooling hot spots, 389 products), 232, deleting rows, 332 Coordinated Universal 309–311 FETCH syntax, 331 Time (UTC), 108, 222 current user identif er, 396 updating rows, 332 CURRENT_DATE function, overview, 323–324 220, 330 2/24/11 3:47 PM 46_9780470929964-bindex.indd 668 46_9780470929964-bindex.indd 668 2/24/11 3:47 PM

Index 669 CUST_PROD1 view, 80 updating data in tables, with transactions, 93 CUST_PROD2 view, 80 87–90 overview, 395 CUSTOMER entity, 77, 78, updating views, 91 SQL*Module, 449 164 f lling tables with Data Def nition Language CUSTOMER relation, 172 adding blocks of rows (DDL) CUSTOMER table to tables, 478 database creation adding data to, 85 adding to selected containment hierarchy, building, 194 columns, 477 74 FROM clause, 232 entering with SQL, creating domains, EXISTS predicate, 243 476–477 81–82 indexes, 181 with forms, 476 creating schemas, MATCH predicate, 245 managing 80–81 modifying, 82 with complicated creating tables, 75, IN predicate, 237 programs, 10–12 464–465 relationships, 35 with simple programs, creating views, 76–80 customer XML 12–13 modifying tables, 82 document, 571, 573 placement of on hard overview, 73 disks, 606 removing objects, 82 D retrieving from XML specifying columns, 75 documents def ned, 652 DAO (Data Access FLWOR expressions, scheduling statements, Objects), 426 568–573 388–389 data overview, 561 SQL*Module, 449 access privileges XQuery, 562–568 data entry clerks, 85 adding data, 399 XQuery versus SQL, Data Manipulation changing data, 399–400 573–576 Language (DML) deleting data, 399 speeding retrieval of adding data to tables, referencing data in full table scans, 180 84–86 tables, 400–401 hierarchical storage, def ned, 652 viewing data, 399 179–180 deleting data from avoiding corruption of, Data Access Objects tables, 90 177–178 (DAO), 426 retrieving data from Data Manipulation Data Control Language databases, 83–84 Language (DCL) SQL*Module, 449 adding data to tables, def ned, 652 updating data in tables, 84–86 maintaining security in 87–90 deleting data from granting access updating views, 91 tables, 90 privileges, 92 data models, users’ retrieving data from revoking access def ned, 143 databases, 83–84 privileges, 93 overview, 29–30, 132 2/24/11 3:47 PM 46_9780470929964-bindex.indd 669 2/24/11 3:47 PM 46_9780470929964-bindex.indd 669

670 SQL All-in-One For Dummies, 2nd Edition data models, users’ data sublanguages TIME WITH TIME (continued) def ned, 52, 652 ZONE, 108–109, 117, stakeholders data types 209 building consensus approximate numeric TIME WITHOUT TIME among, 146–148 DOUBLE PRECISION, ZONE, 108, 117 identifying and 104, 116, 208 TIMESTAMP WITH interviewing, 30 FLOAT, 104, 116, 208 TIME ZONE, 109, 117, obtaining buy-ins, 31 overview, 103 209 overview, 143–144 REAL, 104, 116, 208, TIMESTAMP WITHOUT reconciling confl icting 228 TIME ZONE, 109, 117 requirements among, binary string def ned, 652 30–31 BINARY, 107, 116, 208 def ning new, 402 standards organization, BINARY LARGE exact numeric 145–146 OBJECT (BLOB), 101, BIGINT, 102, 116, 208 supervisor, 144 107, 117, 209 DECIMAL, 103, 114, 116, upper management, 146 BINARY VARYING, 208, 228 users, 144–145 107, 116 INTEGER, 85, 101–102, translating to formal BOOLEAN, 107, 117, 368 116, 208, 368 Entity-Relationship character string NUMERIC, 103, 116, 199, model CHARACTER (CHAR), 208, 368 advanced concepts, 75, 85, 105, 116, 208 SMALLINT, 102, 116, 41–45 CHARACTER LARGE 208, 420 complex relationships, OBJECT (CLOB), 101, incompatibilities, 50 105–106, 116, 209 440–441 drawing diagrams, CHARACTER VARYING, interval 38–41 105, 116, 208 INTERVAL, 222 examples of, 45–49 NATIONAL INTERVAL DAY, 117, modeling techniques, CHARACTER, 106, 208 209 32–37 NATIONAL CHARACTER overview, 109 simplifying LARGE OBJECT, 106 mapping to XML, relationships, 50 NATIONAL CHARACTER 527–531 translating to relational VARYING, 106, 209 nonpredef ned model, 50, 463–464 collection ARRAY, 111–112, 117, data redundancy, 652 ARRAY, 111–112, 117, 530–531 data sources 530–531 DISTINCT UDT, 529 def ned, 652 def ned, 652 DOMAIN, 527–528 ODBC operation stages, multisets, 112 MULTISET, 111–112, 511–513 overview, 111 117, 531 overview, 508 converting, 445 ROW, 110–111, 117, data structures datetime 529–530 diagrams of, 167 DATE, 108, 117, 209, 227 REF, 113, 117 used for indexes, overview, 107–108 184–185 2/24/11 3:47 PM 46_9780470929964-bindex.indd 670 46_9780470929964-bindex.indd 670 2/24/11 3:47 PM

Index 671 relating, 573–576 fl at f le systems versus, fl exibility of, 25 user-def ned 11 functional distinct types, 114 IBM DB2, 59 dependencies, 150 overview, 113 limitations of, 134 keys, 151 structured types, logical requests, 12 normalizing, 164–166 114–116 Microsoft Access, 55–59 overview, 20, 22, 26–27 XML, 110–111, 524–525 Microsoft SQL Server, 59 relations, 149–150 data values MySQL, 60 SQL and, 63–71 in columns, 208 object-relational, 441 success of, 26–27 literal, 208–209 Oracle Database, 59–60 database object owners, row, 207–208 PostgreSQL, 61 397 special variables, 210 queries, 14 database page buffer, 592, variables, 209–210 relational, 22 605 database administrators standards organizations, database publishing, 653 (DBAs) 145 database servers, 653 def ned, 397, 653 Sybase SQL Anywhere, databases platform instability, 339 60 avoiding data database applications. tracking down corruption, 177–178 See also applications bottlenecks, 619–620 benef ts of def ned, 54 database models irreducible complexity, design phase, 137 anomalies, 151–157 10 overview, 13–14, 129 def ned, 15 managing data database driver (DBD) hierarchical, 15–19, 654 with complicated level, 434 historical background of, programs, 10–12 database dumps, 392, 15–16 managing data with 598–599 integrity versus simple programs, Database Engine Tuning performance, 157–159 12–13 Advisor, 191–192, network, 20–21 overview, 9–10 626–631 nonrelational NoSQL, 26 types of organization, database engines, 128, 653 object-oriented, 15–16, 13 database interface (DBI) 25 buffer size, determining, level, 434 object-relational, 16, 604–605 database locks, 384 25–26 coding from bottom up, database management relational 474–475 systems (DBMS) building, 148–151 components of systems automatic clustered Codd’s rules, 22–25 of index maintenance, converting Entity- database applications, 321 Relationship model 129 def ned, 54, 653 to, 50, 163–164 database engines, 128 escape characters, 354 def ned, 15 databases, 128 def ning, 22 front end, 128–129 2/24/11 3:47 PM 46_9780470929964-bindex.indd 671 2/24/11 3:47 PM 46_9780470929964-bindex.indd 671

672 SQL All-in-One For Dummies, 2nd Edition databases, components of development query types, 182–184 systems of (continued) environments sparse and dense, overview, 127–128 IBM DB2, 428 185–186 users, 129 Microsoft Access, table sizes, 187 connecting to remote 425–427 integrity native drivers, 501–502 Microsoft SQL Server, domain, 175 ODBC components, 427 entity, 174–175 502–508 MySQL, 429 preserving with requests, 508–512 Oracle 11gR2, 428 transactions, 93 connecting user PostgreSQL, 429 referential, 176–177 interface to, 471–473 SQL Anywhere, 429 interaction with Data Def nition Language enterprise, def ned, 653 applications, 617–618 containment hierarchy, f lling tables personal, def ned, 653 74 adding blocks of rows queries and database creating domains, to tables, 478 applications, 13–14 81–82 adding data to selected retrieving data from, creating schemas, columns, 477 83–84 80–81 adding data with forms, speeding data retrieval creating tables, 75 476 full table scans, 180 creating views, 76–80 copying from foreign hierarchical storage, modifying tables, 82 data f les, 478 179–180 overview, 73 entering data with SQL, SQL removing objects, 82 476–477 altering table structure, specifying columns, 75 transferring all rows 202–203 def ned, 54, 652 between tables, building tables, 194–197 designing 478–479 deleting tables, 203 binary relationships, transferring selected domains, 199 166–170 columns and rows keys and indexes, 198 database applications, between tables, planning, 193 137 479–480 relationships between documenting Design indexes tables, 199–202 phase, 138 ANSI/ISO standard, 182 setting constraints, 198 Entity-Relationship clustering, 186 SQL Server execution model, 162–164 composite, 186–187 plans errors in, 340, 369 costs of, 182 Database Engine Tuning normalizing relational creating, 181 Advisor, 191–192 model, 164–166 data structures used overview, 190–191 overview, 136–137 for, 184–185 robust, 188–189 sample conversion, effect on join typical queries, 190 171–173 performance, 187 tuning full table scans versus, indexes, 582–587 187–188 2/24/11 3:47 PM 46_9780470929964-bindex.indd 672 46_9780470929964-bindex.indd 672 2/24/11 3:47 PM

Index 673 minimizing traff c DB2 DCL (Data Control between applications def ned, 653 Language) and servers, 589 Java and, 435 def ned, 652 overview, 579 DBAs (database maintaining security in physical design, administrators) granting access 580–581 def ned, 397, 653 privileges, 92 precompiling queries, platform instability, revoking access 589 339 privileges, 93 separating user DBD (database driver) with transactions, 93 interactions from level, 434 overview, 395 transactions, 589 DBI (database interface) SQL*Module, 449 tuning queries, 587–588 level, 434 DDL (Data Def nition tuning transactions, DBMS (database Language) 588 management systems) database creation workload, 580 automatic clustered containment hierarchy, using certain facilities, index maintenance, 74 401 321 creating domains, 81–82 workgroup, def ned, 653 def ned, 54, 653 creating schemas, data-entry errors escape characters, 354 80–81 constraints, 368 fl at f le systems versus, creating tables, 75, data types, 368 11 464–465 overview, 340 IBM DB2, 59 creating views, 76–80 DATE data type, 108, 117, limitations of, 134 modifying tables, 82 209, 227 logical requests, 12 overview, 73 datetime data types Microsoft Access, 55–59 removing objects, 82 DATE, 108, 117, 209, 227 Microsoft SQL Server, 59 specifying columns, 75 overview, 107–108 MySQL, 60 def ned, 652 TIME WITH TIME object-relational, 441 scheduling statements, ZONE, 108–109, 117, Oracle Database, 59–60 388–389 209 PostgreSQL, 61 SQL*Module, 449 TIME WITHOUT TIME queries, 14 deadlock intervals, ZONE, 108, 117 relational, 22 389–390 TIMESTAMP WITH TIME standards organizations, deadlocks, 385–386, 604 ZONE, 109, 117, 209 145 DECIMAL data type, 103, TIMESTAMP WITHOUT Sybase SQL Anywhere, 114, 116, 208, 228 TIME ZONE, 109, 117 60 declarations datetime value tracking down Java-based embedded expressions, 222–223, bottlenecks, 619–620 SQL, 435 659 DBMS-based drivers, XML document, 519 datetime value functions, 505–507 DECLARE CURSOR 220–221 statement, 324–328, 330–331 2/24/11 3:47 PM 46_9780470929964-bindex.indd 673 2/24/11 3:47 PM 46_9780470929964-bindex.indd 673

674 SQL All-in-One For Dummies, 2nd Edition DECLARE SECTION roles, 397 detailed statement of statement, 445 rows, 332 requirements, 453 DECLARE statements, 420 tables, 203 determining declaring cursors deletion anomalies, 152, deliverables, 454–457 within compound 653 eliminating many-to- statements, 488 deliverables many relationships, holding cursors, 329 def ned, 130 460–463 ordering query result determining, 454–457 Entity-Relationship sets, 325–326 demilitarized zone (DMZ), model overview, 324–325 364 building, 457–460 query expressions, 325 denial-of-service attacks normalizing, 463–464 result set cursors, 329 def ned, 653 interviewing scrolling cursors, 328 overview, 351 stakeholders, 452 sensitive versus dense indexes, 185–186 planning for organization insensitive cursors, DEPARTMENT table, 583 growth, 455–456 327–328 dependency project scope, 456–457 updating table rows, 327 functional, 64–65, 150, proposals, 453–454 declaring host variables, 654 relational model, 444 transitive, 155, 658 460–464 DEFAULT keyword, 332 DEPT table, 382 removing tables, 468 default transactions, 372 DESC keyword, 326 understanding problems, DEFERRABLE constraints, descriptors, def ned, 653 451–452 379–383 Design phase, SDLC. designing databases Def nition phase, SDLC, See also designing binary relationships, 130–131 databases 166–170 degree-three database applications, database applications, relationships, 37 137 137 degree-two relationships, designing database, documenting Design 35–37 136–137 phase, 138 delegating responsibility, documenting, 138 Entity-Relationship 403 designing applications model DELETE statement, changing table structure, converting to relational 288–291, 308, 327, 496 467–468 model, 163–164 DELETEXML function, 545, connecting user overview, 162–163 547 interface to database, errors in, 340, 369 deleting 471–473 normalizing relational data contents, 470 model, 164–166 privilege of, 399 creating tables, 464–467 overview, 136–137 from tables, 90 designing user interface, sample conversion, records with 468, 470–471 171–173 updategrams, 552–553 2/24/11 3:47 PM 46_9780470929964-bindex.indd 674 46_9780470929964-bindex.indd 674 2/24/11 3:47 PM

Index 675 designing user interfaces, disk drive controller Evaluation phase, 136 468, 470–471 delay, 596 Final Documentation and detail areas, 414–417 disk mirroring, 344 Testing phase determinants, 64 disk subsystems, 637–638 delivering results, 140 development DISTINCT keyword, f nalizing environments 243–244, 255–259, 304 documentation, 140 IBM DB2, 428 DISTINCT UDT data type, testing systems Microsoft Access 529 with sample data, Access Database distinct user-def ned 139–140 Engine, 427 types, 114, 529 documents, XML. See also ADO, 426 distributed data Extensible Markup DAO, 426 processing, 653 Language (XML) f les with .mdb DKNF (Domain/Key parts of extension, 427 Normal Form), 153, attributes, 521 Jet engine, 426 156–158 declarations, 519 ODBC, 426 DLL (Dynamic Link elements, 520–521 OLE DB, 427 Library), 426 entity references, overview, 425–426 DML (Data Manipulation 521–522 Microsoft SQL Server, Language) numeric character 427 adding data to tables, references, 522 MySQL, 429 84–86 overview, 518–519 Oracle 11gR2, 428 def ned, 652 retrieving data from PostgreSQL, 429 deleting data from FLWOR expressions, SQL Anywhere, 429 tables, 90 568–573 diagnostics areas retrieving data from XQuery, 562–568, def ned, 653 databases, 83–84 573–576 detail, 414–417 SQL*Module, 449 updating header, 414–415 updating data in tables, Microsoft tools for, DIAGNOSTICS SIZE 87–90 549–560 clause, 414 updating views, 91 Oracle tools for, diagrams DMZ (demilitarized zone), 544–548 data structure, 167 364 overview, 543–544 maximum cardinality, 38 document element, 520 DOM (Document Object minimum cardinality, Document Object Model Model), 544 38–41 (DOM), 544 DOMAIN data type, disk controller cache, DOCUMENT predicate, 538, 527–528 tuning, 607 540 domain integrity, disk controllers, 620 Document Type Def nition 175, 654 Disk Defragmenter, (DTD), 522 Domain/Key Normal Microsoft Windows, documenting Form (DKNF), 153, 637 Design phase, 138 156–158 2/24/11 3:47 PM 46_9780470929964-bindex.indd 675 2/24/11 3:47 PM 46_9780470929964-bindex.indd 675

676 SQL All-in-One For Dummies, 2nd Edition domains E overview, 33 Data Def nition relating to one another Language, 81–82 business rules, 459–460 elementcentric mapping def ned, 156, 653 maximum cardinality, mixed attributecentric ensuring data validity 458 and, 558–559 with, 199 minimum cardinality, overview, 557–558 DOUBLE PRECISION data 458–459 elements type, 103–104, 116, 208 relationships, 457–458 document, 520 drawing diagrams strong, 41–42 empty, 520–521 maximum cardinality, 38 subtype, 43–44, 658 inserting into records, minimum cardinality, supertype, 43–44, 658 551–552 38–41 weak, 41–42 nested, 520 Download from Wow! eBook <www.wowebook.com> driver manager, 505, entity classes, 33 updating records from, 510–511, 654 entity instances, 33 552 drivers entity integrity, 174–175, ELSE clause, 225, 490 connection handles in, 654 embedded SQL 511 entity references, 521–522 def ned, 95 DBMS-based, 506–507 Entity-Relationship (ER) in Java applications, 445 def ned, 654 model in Oracle Pro*C f le-based, 506 advanced concepts applications, 442–445 DROP statement, 193, 401 business rules, 44–45 overview, 97–99, 441– DTD (Document Type ID-dependent entities, 442, 485 Def nition), 522 42–43 in Perl applications, 445 dumps, databases, 392, strong and weak in PHP applications, 598–599 entities, 41–42 445–446 durability, 372 supertype and subtype with Visual Basic .NET Dynamic Link Library entities, 43–44 applications, 446 (DLL), 426 building EMPLOYEE table, 35, 76, dynamic SQL determining entities, 246, 313, 382 danger of putting user 457 empty elements, 520–521 input directly in relating entities to one END keyword, 412, 486 statements, 356–357 another, 457–460 END-EXEC terminator def ned, 352 converting to relational directive, 444 dynamic string building, model engines, database, 128, 352–353 eliminating many-to- 653 DYNAMIC_FUNCTION f eld, many relationships, entities 414 460–463 def ned, 460 DYNAMIC_FUNCTION_ normalizing, 463–464 ID-dependent, 42–43 CODE f eld, 414 overview, 50, 163–164 2/24/11 3:47 PM 46_9780470929964-bindex.indd 676 46_9780470929964-bindex.indd 676 2/24/11 3:47 PM

Index 677 database design, supertype and subtype database design, 369 136–137 entities, 43–44 data-entry drawing diagrams building constraints, 368 maximum cardinality, determining entities, data types, 368 38 457 human checking, minimum cardinality, relating entities to one 368–369 38–41 another, 457–460 overview, 367–368 examples of, 45–49 converting to relational handling modeling techniques model adding constraints attributes, 33–34 eliminating many-to- to existing tables, entities, 33 many relationships, 418–419 identif ers, 34–35 460–463 condition handlers, overview, 32–33 normalizing, 463–464 410–412 relationships, 35–37 overview, 50, 163–164 diagnostics area, overview, 162–163 database design, 414–417 relationships 136–137 example constraint problems with drawing diagrams violation, 417–418 complex, 50 maximum cardinality, exception handling, simplifying using 38 420–421 normalization, 50 minimum cardinality, identifying error environment. See 38–41 conditions, 407–408 development examples of, 45–49 SQLSTATE status environments; tuning modeling techniques parameter, 408–410, the environment attributes, 33–34 419–420 environment handles, 508 entities, 33 WHENEVER clause, ENVIRONMENT_NAME identif ers, 34–35 412–413 f eld, 419 overview, 32–33 locking equality condition, 226 relationships, 35–37 cooling hot spots, 389 equi-join queries, 184 overview, 162–163 deadlocks, 385–386, equi-joins, 311–313 relationships 389–390 equipment failure, problems with eliminating unneeded 338–339 complex, 50 locks, 387 ER (Entity-Relationship) simplifying using granularity, 384–385, model normalization, 50 388 advanced concepts error messages, not giving overview, 383–384 business rules, 44–45 too much away in, partitioning insertions, ID-dependent entities, 357–358 389 42–43 errors scheduling DDL strong and weak ACID, 371–372 statements, 388–389 entities, 41–42 concurrent-operation shortening confl icts, 370–371 transactions, 387 2/24/11 3:47 PM 46_9780470929964-bindex.indd 677 2/24/11 3:47 PM 46_9780470929964-bindex.indd 677

678 SQL All-in-One For Dummies, 2nd Edition errors, locking (continued) SMALLINT, 102, 116, 208, with XSD schemas, throughput, 386 420 553–554 two-phase, 384 EXCEPT operator, 308 exploits, 364–365, 654 weakening isolation exception handling, expressions levels, 387–388 420–421 array value, 224 programming, 369–370 exclusive locks, 384 Boolean value, 224 recovery system, EXEC SQL directive, 99, CAST 392–393 442, 444 casting one SQL data timestamps, 390–392 executable statements, type to another, 228 transactions def ned, 435 overview, 227 committing, 376 Execute button, using to overcome data deferrable constraints, Management Studio type incompatibilities, 379–383 window, 624 228–229 rolling back, 376–379 EXECUTE privilege, 399, conditional value SET TRANSACTION 498 CASE, 225–226 statement, 372–373 executing statements COALESCE, 227 starting, 373–376 combining SQL with host NULLIF, 226–227 escape characters, 218, languages, 96–97 overview, 225 354–356 embedded SQL, 97–99 datetime value, 222–223 Euro type, 114 interactive SQL, 96 FLWOR Evaluation phase, SDLC module language, 99–100 for clause, 569–571 documenting, 136 privilege of, 402 let clause, 570–571 overview, 133–134 execution plans, 190–191 order by clause, project scope, 134–135 execution prof les, 636 571–572 reassessing feasibility, existence-dependent overview, 568–569 135 entities, 42 return clause, 569, events EXISTS predicate, 243, 572–573 responding to, 402 285 SELECT versus, 573 trigger, 496 EXIT handler effect, 411 where clause, 571 Events Selection tab, EXP numeric value interval value, 223 Trace Properties function, 219 numeric value, 221 dialog box, 631–632 EXPLAIN keyword, 259 queries, 325 exact numeric data types explicit mapping row value, 229 BIGINT, 102, 116, 208 creating mapping string value, 221–222 DECIMAL, 103, 114, 116, schema for tables Extensible Markup 208, 228 with parent–child Language (XML) INTEGER, 85, 101–102, relationships, 555–557 creating schemas for 116, 208, 368 creating updategrams SQL tables, 533–534 NUMERIC, 103, 116, 199, with XDR schemas, data types, 524–525 208, 368 554–555 def ned, 654 2/24/11 3:47 PM 46_9780470929964-bindex.indd 678 46_9780470929964-bindex.indd 678 2/24/11 3:47 PM

Index 679 document parts XQuery, 562–568, f nalizing attributes, 521 573–576 documentation, 140 declarations, 519 storing data in tables testing systems with elements, 519–521 creating tables to hold sample data, 139–140 entity references, XML data, 543 FINAL keyword, 115 521–522 inserting XML data into f rewalls numeric character SQL pseudotables, application-layer, 364 references, 522 541–543 def ned, 654 overview, 518–519 Microsoft updating network-layer, 364 mapping tools, 549–560 First Normal Form (1NF), character sets, 526 Oracle updating tools, 152, 155 data types, 527 544–548 fl at f le systems, 10–11, identif ers, 526–527 updating XML 74, 654 nonpredef ned data documents, 543–544 FLOAT data type, 103–104, types, 527–531 XML Schema, 522–523 116, 208 overview, 526 external routines, def ned, fl oating-point numbers, tables, 532–533 70 104 null values, 532–533 externally invoked FLOOR numeric value operating on data with routines, def ned, 70 function, 219 SQL functions EXTRACT numeric value FLWOR expressions XMLAGG, 535–536 function, 217, 524 for clause, 569–571 XMLCAST, 538 extremal queries, 183 let clause, 570–571 XMLCOMMENT, 536 order by clause, XMLCONCAT, 535 F 571–572 XMLELEMENT, 534 overview, 568–569 XMLFOREST, 534–535 FACULTY subtype, 44 return clause, 569, XMLPARSE, 536–537 FALSE type, 107, 118 572–573 XMLPI, 537 feasibility analysis, 131 SELECT versus, 573 XMLQUERY, 537–538 feature creep, 453 where clause, 571 overview, 517–518 FETCH statement, 325, FOR . . . DO . . . predicates 328, 331 END FOR statement, CONTENT, 539 f elds, def ned, 207 493 DOCUMENT, 538 Fifth Normal Form (5NF), for clause, 569–571 VALID, 539–540 153, 156 FOR EACH STATEMENT XMLEXISTS, 539 f le servers, def ned, 654 triggered action, 496 relating SQL to, 523–524 f le-based drivers, 505–506 foreign data f les, copying retrieving data from f lter ratio, 188 from, 478 documents Final Documentation and foreign keys FLWOR expressions, Testing phase, SDLC constraints of, 121–123 568–573 delivering results, 140 def ned, 654 2/24/11 3:47 PM 46_9780470929964-bindex.indd 679 2/24/11 3:47 PM 46_9780470929964-bindex.indd 679

680 SQL All-in-One For Dummies, 2nd Edition foreign keys (continued) set GMT (Greenwich Mean establishing combining with GROUP Time), 108, 222 relationships between BY clause, 250–251 GO TO option, 413 tables, 199 def ned, 651, 657 GRANT ALL PRIVILEGES MATCH predicate, 246 subqueries that return statement, 402 normalizing relational single values, 279–280 GRANT DELETE models, 166 summarizing data with, statement, 399 FOREIGN table, 490 211–213 GRANT INSERT ForeignOrDomestic stored, 497 statement, 399 procedure, 494, 498 value GRANT OPTION FOR forests, def ned, 654 datetime, 220–221 clause, 404 form-based data entry, numeric, 215–220 GRANT SELECT 476 string, 213–215 statement, 399 FOUR_STAR table, 400 GRANT statement, 92–93, Fourth Normal Form G 398, 403–405 (4NF), 152–153, 156 GRANT UPDATE FoxPro desktop General tab statement, 399, 403 databases, 506 Trace Properties dialog GRANTED BY clause, 404, FROM clause, 100, 231, 288 box, 631 405 front end, 128–129, 654 Tuning Advisor window, GRANTED BY CURRENT_ full backups, 346 626 USER clause, 405 full outer joins, 318–319 GET DIAGNOSTICS grantees, def ned, 397 FULL rules, 248 statement, 416, 418 granting access privileges full table scans, 180, GET parameter, dangers of adding data, 399 187–188 dynamic string building, all privileges, 402 functional dependency, 352–353 changing data, 399–400 64–65, 150, 654 error messages, 357–358 def ning new data types, functions escape characters, 402 operating on XML data 354–356 delegating responsibility with normal fl ow of execution, of, 403 XMLAGG, 535–536 358 deleting data, 399 XMLCAST, 538 putting user input executing SQL XMLCOMMENT, 536 directly in dynamic statements, 402 XMLCONCAT, 535 SQL statements, overview, 92, 398–399 XMLELEMENT, 534 356–357 referencing data in XMLFOREST, 534–535 types, 356 tables, 400–401 XMLPARSE, 536–537 GetProcAddress responding to events, XMLPI, 537 function, 510 402 XMLQUERY, 537–538 2/24/11 3:47 PM 46_9780470929964-bindex.indd 680 46_9780470929964-bindex.indd 680 2/24/11 3:47 PM

Index 681 using certain database processors, 608 hierarchical storage, facilities, 401 RAID arrays, 609 179–180 viewing data, 399 RAM, 608 high-water mark, 614 granting roles, 405 maximizing available holding cursors, 329 granularity of locks, disk controller cache, host languages, 384–385, 388 607 combining SQL with, Greenwich Mean Time page replacement 96–97 (GMT), 108, 222 algorithm, 606–607 host variables. See also GROUP BY clause, 232, placement of code and SQLSTATE status 250–253, 287 data on hard disks, parameter (host grouping queries, 184 606 variable) groups, def ned, 64 performance issues :comm, 443 disk controller, 620 declaring, 444 H hard disk drives, 620 def ned, 654 network, 621 :salary, 443 hackers, 483 processor, 620–621 statusn, 419–420 handles troubleshooting, 616 hot pages, def ned, 638 connection, in drivers, hardware failures, hot spots 511 preventing cooling, 389 identifying objects using, backups locating, 622 508–509 frequency of, 346 hot tables, def ned, 191 handling conditions. See full, 346 HP Scrawlr, 361 condition handlers incremental, 346 HP WebInspect, 361–362 handling errors. See maintenance, 347 Hypertext Markup errors preparation for, Language (HTML), hard disk drives 345–346 def ned, 654 construction of, 596 overview, 338–339 in memory system platform instability, I hierarchy, 180, 595 339–340 performance RAID IBM DB2, 59, 428 considerations, levels, 342, 344–345 IBM Rational AppScan, 596–597 striping, 341–343 361 placement of code and hash joins, 585, 636 icons used in book, 5 data on, 606 hash structures, 185 ID-dependent entities, speed of, 608–609 HAVING clause, 232, 42–43 troubleshooting, 620 252–253, 268–272, identif ers hard failures, 599–600 287–288, 375 entity, 164 hardware header area, 414–415 mapping, 526–527 adding hierarchical database overview, 34–35 hard disks, 608–609 model, 15–19, 654 overview, 607–608 2/24/11 3:47 PM 46_9780470929964-bindex.indd 681 2/24/11 3:47 PM 46_9780470929964-bindex.indd 681

682 SQL All-in-One For Dummies, 2nd Edition identify-constraint- def ned, 181, 655 integrity, database option component, dense, 185–186 domain, 175, 654 540 effect on join entity, 174–175, 654 IF . . . THEN . . . performance, 187 preserving with ELSE . . . END IF full table scans versus, transactions, 93 statement, 489 187–188, 614 referential, 176–177, 202, impedance mismatches, multicolumn, 583 246–248, 400, 657 117–118, 441 overview, 198 interactive SQL, 95–96 implementation, def ned, problems with, 612–614 interfaces. See user 655 query types interfaces Implementation phase, equi-join, 184 Internet SDLC, 138 extremal, 183 def ned, 655 implementers, def ned, grouping, 184 denial-of-service attacks, 409 multipoint, 182–183 351 implicit mapping ordering, 183–184 phishing scams, 362–363 deleting records with point, 182 SQL injection attacks updategrams, 552–553 pref x match, 183 f nding vulnerabilities inserting elements into range, 183 on sites, 359–362 record, 551–552 sparse, 185–186 GET parameter, 352–358 updating records from table size, 187 overview, 351–352 elements, 552 tuning, 586–587 recognizing unsafe IN keyword, 237, 277, inference testing, 360 conf gurations, 359 284–285 information schema, Trojan horses, 349–350 incremental backups, 346 def ned, 655 viruses, 348–349 Indexed Sequential Access inner joins, 315 worms, 350 Method (ISAM), 426 insensitive cursors, zombie spambots, 363 indexes 327–328 INTERSECT operator, ANSI/ISO standard, 182 INSERT INTO statement, 306–308 avoiding unnecessary, 290 intersection relation, 170 582 INSERT statement, 85–86, INTERVAL data type, 222 B+ tree, 185, 613 288–291, 412, 476–478, interval data types choosing columns for, 496 INTERVAL, 222 582–583 INSERTCHILDXML INTERVAL DAY, 117, 209 choosing type of, 585 function, 545–546 overview, 109 clustering, 186, 320–321, insertion anomalies, 152, INTERVAL DAY data type, 583–585 655 117, 209 composite, 186–187, 586 insertion partitioning, 389 interval value costs of, 182, 585–586 INSERTXMLBEFORE expressions, 223 creating, 181 function, 545–547 interviewing stakeholders, data structures used for, INTEGER data type, 85, 30, 452 184–185 101–102, 116, 208, 368 intranets, def ned, 655 2/24/11 3:47 PM 46_9780470929964-bindex.indd 682 46_9780470929964-bindex.indd 682 2/24/11 3:47 PM

Index 683 INVOICE entities, 77–78 Oracle SQL and, 435 left, 316–318 INVOICE relation, 167, 172 Java application right, 318 INVOICE table, 194, 232, programming overview, 308–309 237, 243 interface (API), 544 sort-merge, 636 INVOICE_LINE relation, Java-based DataBase 172 Connectivity (JDBC) K INVOICE_LINE table, 80, def ned, 435, 655 194 drivers, 615 keys InvoiceUpdateSchema. native drivers versus, candidate, 195 xml f le, 556 615 composite, 196, 652 IPX/SPX, 655 Java-based embedded creating databases with ISAM (Indexed Sequential SQL (SQLJ), 435 SQL, 198 Access Method), 426 JavaScript, def ned, 655 def ned, 156 ISO/IEC SQL standard, 53 JDBC (Java-based foreign isolation DataBase constraints of, 121–123 def ned, 372 Connectivity) def ned, 654 levels of def ned, 435, 655 establishing READ COMMITTED, drivers, 615 relationships between 375–376, 388 native drivers versus, tables, 199 READ UNCOMMITTED, 615 MATCH predicate, 246 373–374, 376 Jet engine, 426 normalizing relational REPEATABLE READ, join operation, 524 models, 166 375–376 joins identif ers and, 164 SERIALIZABLE, 373, choosing types of, locating table rows with, 375–376 635–636 195–196 weakening, 387–388 ON clause versus WHERE overview, 65–67, 151 IssueClosed node, 545 clause, 319 primary, 22, 196, 246, 656 ITEMS table, 564, 565 column-name, 314–315 items.xml f le, 565 condition, 313–314 L ITERATE statement, 493 cross, 309–311 def ned, 635–636, 655 L1 (Level 1) cache, 180, J equi-joins, 311–313 594 indexes and, 187, L2 (Level 2) cache, 180, Java 320–321 594 DB2 and, 435 inner, 315 L3 (Level 3) cache, 594 def ned, 655 natural, 313 LABOR relation, 172 embedding SQL in nested-loops, 636 LABOR table, 195 applications, 445 outer LANGUAGE clause, 100, MySQL and, 435 full, 318–319 448 2/24/11 3:47 PM 46_9780470929964-bindex.indd 683 2/24/11 3:47 PM 46_9780470929964-bindex.indd 683

684 SQL All-in-One For Dummies, 2nd Edition leaf nodes, 613 shortening mandatory stakeholder leaf subtypes, 115 transactions, 387 requirement category, LEAVE statement, 491–492 throughput, 386 31 left outer joins, 316–318 weakening isolation many-to-many (N:M) let clause, 570–571 levels, 387–388 relationship, 16, 36, Level 1 (L1) cache, 180, two-phase locking, 384 460–463 594 logical connectives mapping schemas Level 2 (L2) cache, 180, AND, 248–249 attributecentric 594 def ned, 655 mapping, 558 Level 3 (L3) cache, 594 NOT, 239, 249–250 elementcentric mapping, LICENSE table, 36, 39 OR, 249, 272–273 557–558 LIKE clause, 237–239 logical operations, explicit mapping Limit Tuning Time check def ned, 636 creating mapping box, Tuning Advisor logical requests, 12 schemas for tables window, 627 logs with parent-child LIPIDLEVEL table, 154 log f le, 377–378 relationships, 555–557 literals, 208–209 logging subsystem, 639 creating updategrams livelocks, 392 putting on different disks with XDR schemas, LN numeric value hard disk drive 554–555 function, 218–219 construction, 596 creating updategrams locks hard disk drive with XSD schemas, client performance and, performance 553–554 615–616 considerations, implicit mapping deadlocks, 385–386 596–597 deleting records with granularity, 384–385 write-ahead log protocol, updategrams, 552–553 overview, 383–384 378–379 inserting elements into resource management, LOOP . . . ENDLOOP records, 551–552 639 statement, 491 updating records from tuning LOWER string value elements, 552 controlling granularity, function, 214 mixed elementcentric 388 LRU algorithm, 607 and attributecentric cooling hot spots, 389 mapping, 558–559 deadlock intervals, M that allow null values, 389–390 559–560 eliminating unneeded, magnetic tape, 595 mapping SQL to XML 387 main memory, 180, 594 character sets, 526 partitioning insertions, Maintenance phase, SDLC, data types, 527 389 141 def ned, 655 scheduling DDL malware, 347, 655 identif ers, 526–527 statements, 388–389 MANAGER table, 228 2/24/11 3:47 PM 46_9780470929964-bindex.indd 684 2/24/11 3:47 PM 46_9780470929964-bindex.indd 684

Index 685 nonpredef ned data MEM-RES relation, 462 MIN function, 212 types MEM-RES table, 466 minimal projects, 147 ARRAY, 530–531 MESSAGE_LENGTH f eld, minimum cardinality, DISTINCT UDT, 529 415 38–41, 458–459 DOMAIN, 527–528 MESSAGE_OCTET_LENGTH MOD numeric value MULTISET, 531 f eld, 415 function, 218 ROW, 529–530 MESSAGE_TEXT f eld, 415, modeling database overview, 525 419 systems tables, 532 metadata, def ned, 655 def ned, 143 MATCH clause methods, def ned, 70 overview, 29–30, 132 overview, 245 Microsoft Access stakeholders predicate and referential Access Database Engine, building consensus integrity, 246–248 427 among, 146–148 MAX set function, 212, 298 ADO, 426 identifying and maximal supertypes, 115 ADOdb library, 432–433 interviewing, 30 maximum cardinality, 38, ADOX library, 433 obtaining buy-ins, 31 224, 458 DAO, 426 overview, 143–144 maximum projects, 147 database window, 56 reconciling confl icting .mdb f les, 427 f les with .mdb requirements among, mean time between extension, 427 30–31 failures (MTBF), 339 Jet engine, 426 standards organization, MECHANIC relation, 172 ODBC, 426 145–146 MECHANIC table, 194, 202 OLE DB, 427 supervisor, 144 medium projects, 147 overview, 55–59, 425–426 upper management, Member9Updategram. Microsoft FoxPro desktop 146 xml f le, 554 databases, 506 users, 144–145 MEMBERS element, Microsoft SQL Server translating to formal 552–553 .NET and, 433–434 Entity-Relationship MEMBERS entity, 463–464 execution plans model MEMBERS relation, Database Engine advanced concepts, 462–464 Tuning Advisor, 41–45 MEMBERS table, 177, 464, 191–192 complex relationships, 477, 479–480, 552 overview, 190–191 50 MembersUpdateSchema. robust, 188–189 drawing diagrams, xml f le, 554 typical queries, 190 38–41 memory overview, 59, 427 examples of, 45–49 hierarchical storage, 180 query analysis, 256–258 modeling techniques, system hierarchy, Microsoft SQL Server 32–37 594–595 Management Studio, simplifying volatile and nonvolatile, 623–624 relationships, 50 593–594 Microsoft SQL Server translating to relational Prof ler, 631–633 model, 50, 460–464 2/24/11 3:47 PM 46_9780470929964-bindex.indd 685 2/24/11 3:47 PM 46_9780470929964-bindex.indd 685

686 SQL All-in-One For Dummies, 2nd Edition modif cation anomalies SOME predicate, N def ned, 17, 290, 655 240–242 types of, 152 UNIQUE predicate, 243 NAMES ARE clause, 100, modify( ) method, 549 MODULE declaration, 100 448 modifying clauses, for module language, 95, namespaces, 550–551 SELECT statement 99–100, 655 NATIONAL CHARACTER FROM, 100, 232–233, 288 modules data type, 106, 208 GROUP BY, 232, 250–252, stored, 498–499 NATIONAL CHARACTER 287 using in applications LARGE OBJECT data HAVING, 232, 252–253, declarations, 447–448 type, 106 268–272, 287–288, 375 def ned, 655 NATIONAL CHARACTER ORDER BY, 232, 253–255, Oracle, 449 VARYING data type, 265–268, 325–326, 329 procedures, 448–449 106, 209 overview, 231 MORE f eld, 414 native drivers, 501–502, WHERE MTBF (mean time 615 ALL predicate, 240–242 between failures), 339 natural joins, 313 ANY predicate, 240–242 multicolumn indexes, 583 nested, def ned, 275 comparison predicates, multipoint queries, nested elements, 520 234 182–183 nested queries, 291–297, DISTINCT predicate, multiprocessor 656 244 environments, nested-loops joins, 636 EXISTS predicate, 243 609–610 .NET classes, 549 LIKE predicate, MULTISET data type, .NET languages, 433–434, 237–239 111–112, 117, 531 446 logical connectives, multisets, 63–64, 112 NetBEUI, 656 248–250 multitable views, 76 network database model MATCH predicate, mutator functions def ned, 15, 656 245–248 def ned, 656 overview, 20 NOT IN predicate, invoking, 115 network-layer f rewalls, 236–237 MySQL 364 NOT LIKE predicate, C and, 434 networks performance, 237–239 C# and, 434 621 NULL predicate, C++.NET and, 434 NEW keyword, 496 239–240 Java and, 435 New York Board of Trade OVERLAPS predicate, overview, 60, 429 (NYBOT), 345 244–245 Perl and, 434 nil option, 532, 534 BETWEEN predicate, PHP and, 434 nillable attribute, 559 235–236 query analysis, 258–259 N:M (many-to-many) IN predicate, 236–237 MySQL.Data.dll relationship, 16, 36, SIMILAR predicate, 239 connector, 434 460–463 2/24/11 3:47 PM 46_9780470929964-bindex.indd 686 2/24/11 3:47 PM 46_9780470929964-bindex.indd 686

Index 687 nonpredef ned data types NOT IN clause, 236–237 REAL, 104, 116, 208, 228 ARRAY, 111–112, 117, NOT LIKE clause, exact 530–531 237–239 BIGINT, 102, 116, 208 DISTINCT UDT, 529 NOT logical connective, DECIMAL, 103, 114, 116, DOMAIN, 527–528 239, 249–250 208, 228 MULTISET, 111–112, 117, NOT NULL constraint INTEGER, 75, 85, 531 adding incomplete 101–102, 116, 208, ROW, 110–111, 117, records to tables, 228, 368, 474 529–530 85–86 NUMERIC, 103, 116, 199, nonprocedural languages, entity integrity, 174 208, 228, 368 323, 439–440, 562 implementing deferrable SMALLINT, 102, 116, nonrelational NoSQL constraints, 379–380 208, 420 model, 26 overview, 119 numeric value nonrepeatable read NULL clause, 239–240 expressions, 221, 659 problems, 375 null values numeric value functions nonvolatile memory, adding incomplete ABS, 218 593–594 records to tables, 86 CARDINALITY, 218, 224 normal forms CASE expression, 225 CEILING (CEIL), 219 Boyce-Codd, 152, 156 handling, 117–118, CHARACTER_LENGTH, Domain/Key, 153, 532–533 217 156–158 mapping schemas that EXP, 219 Fifth, 153, 156 allow, 559–560 EXTRACT, 217, 524 First, 152, 155 NULLIF expression, FLOOR, 219 Fourth, 152–153, 156 226–227 LN, 218–219 overview, 152–157 tuning queries, 587 MOD, 218 Second, 152–155 XQuery functionality, OCTET_LENGTH, 217–218 Third, 152, 155, 157 563 POSITION, 216–217 normalization NULLIF expression, POWER, 219 def ned, 656 226–227 SQRT, 219 of Entity-Relationship NUMBER f eld, 414 WIDTH_BUCKET, 220 model, 463–464 numeric character NumVar variable, 356 of relational model, references, 522 NYBOT (New York Board 164–166 NUMERIC data type, 103, of Trade), 345 simplifying relationships 116, 199, 208, 368 using, 50 numeric data types O NOT DEFERRABLE approximate constraint, 380 DOUBLE PRECISION, Object Linking and NOT FINAL keyword, 115 104, 116, 208 Embedding Database NOT FOUND condition, FLOAT, 104, 116, 208 (OLE DB), 427, 513 411 overview, 103 2/24/11 3:47 PM 46_9780470929964-bindex.indd 687 2/24/11 3:47 PM 46_9780470929964-bindex.indd 687

688 SQL All-in-One For Dummies, 2nd Edition object-oriented database offl ine memory storage, operating systems, tuning model, 15–16, 25 180 determining database object-oriented OLD keyword, 496 buffer size, 604–605 procedural languages, OLE DB (Object Linking scheduling threads 439, 441 and Embedding context switching, 602 object-relational database Database), 427, 513 deadlocks, 604 model, 16, 25–26 ON clause, 319 overview, 601–602 object-relational DBMS, ON DELETE CASCADE priority inversion, 441 clause, 177, 466 603–604 objects ON DELETE NO ACTION priority-based creating clause, 465 scheduling, 603 domains, 81–82 one-to-many (1:N) round-robin scheduling, schemas, 80–81 relationship, 16, 35 603 views, 76–80 one-to-one (1:1) tuning page usage factor, def ned, 439, 656 relationship, 16, 35 605 identifying using Online Index operator errors, 340–341 handles, 508–509 Recommendations operators, relational removing, 82 check boxes, ON clause versus WHERE observer functions, 115 Advanced Tuning clause, 319 OCTET_LENGTH numeric Options dialog box, EXCEPT, 308 value function, 629 INTERSECT, 306–308 217–218 Open Database JOIN ODBC (Open Database Connectivity (ODBC) ON clause versus WHERE Connectivity) components of clause, 319 components of applications, 504 column-name joins, applications, 504 data sources, 508 314–315 data sources, 508 driver manager, 505 condition joins, driver manager, 505 drivers, 505–507 313–314 drivers, 505–507 overview, 502–504 cross joins, 309–311 overview, 502–504 def ned, 656 equi-joins, 311–313 def ned, 656 native drivers versus, inner joins, 315 native drivers versus, 507, 615 join conditions and 507, 615 operation stages, clustering indexes, operation stages, 510–513 320–321 510–513 overview, 426 natural joins, 313 overview, 426 open source software, 60 outer joins, 316–319 ODBC32.LIB import OPEN statement, 325, 330 overview, 308–309 library, 508 opening cursors, 329–330 UNION, 303–305, 319, 479 ODBC.INI f le, 511 OPENXML rowset provider, UNION ALL, 305 offensive_stats XML 549–550 UNION document, 538 operating modes, CORRESPONDING, 306 contrasting, 440 2/24/11 3:47 PM 46_9780470929964-bindex.indd 688 46_9780470929964-bindex.indd 688 2/24/11 3:47 PM

Index 689 optional stakeholder P disk controller, 620 requirement category, hard disk drives, 31 596–597, 620 page buffer, 592, 605 OR logical connective, isolating problems, 619 page locks, 385 249, 272–273 locking, 615–616 page replacement Oracle network, 621 algorithm, 606–607 def ned, 656 precompiled queries, page usage factor, 605 Java and, 435 618 pages, def ned, 185 native drivers, 501–502 processors, 620–621 PAPERS entity, 463 overview, 59–60, 428 troubleshooting, 616 PAPERS relation, 462 XML updating tools Performance Monitor, PAPERS table, 211, 466, APPENDCHILDXML, 545 Microsoft Windows, 471, 474 DELETEXML, 547 638 PARAMETER_MODE f eld, INSERTCHILDXML, 546 Perl 416 INSERTXMLBEFORE, applications, 445 PARAMETER_NAME f eld, 546–547 language of, 434 416 overview, 544–545 persistent storage, 593 PARAMETER_ORDINAL_ UPDATEXML, 548 PERSON table, 36, 39 POSITION f eld, 416 Oracle Pro*C applications phantom read, 375 parameters, def ned, 656 converting data types, phishing scams, 362–363, parent-child relationships, 445 656 creating mapping declaring host variables, PHP schemas for tables 444 applications, 445–446 with, 555–557 Oracle Tuning Advisor, language of, 434 PART relation, 172 633 physical operations, PARTIAL rules, 248 ORDER BY clause, 232, def ned, 636 partitioning 253–255, 265–268, PIP (Priority Inheritance insertions, 389 325–326, 329 Protocol), 604 overview, 621–622 order by clause, platforms PARTS table, 195, 276 571–572 def ned, 339 patches, 350, 364–365 ordering queries, 183–184 instability, 339–340 paths, def ned, 71 ORDERS table, 495 point queries, 182 PCP (Priority Ceiling outer joins POSITION numeric value Protocol), 604 full, 318–319 function, 216–217 performance issues function of, 315 positioned update application/database left, 316–318 operations, 332 interaction, 617–618 right, 318 POST command, 352 columns, 618 OVERLAPS clause, POSTAL relation, 464 cursors, 618 244–245 PostgreSQL, 61, 429 direct user interaction, OVERLAY string value POWER numeric value 617 function, 215 function, 219 2/24/11 3:47 PM 46_9780470929964-bindex.indd 689 2/24/11 3:47 PM 46_9780470929964-bindex.indd 689

690 SQL All-in-One For Dummies, 2nd Edition preamble, 449 responding to events, embedding SQL precision, def ned, 656 402 statements in code, precision arguments, 102 using certain database 485 precompiled queries, 589, facilities, 401 privileges, 498 618 viewing data, 399 stored functions, 497 predicates, def ned, 224, overview, 68 stored modules, 498–499 656 passing out, 498 stored procedures, 494 pref x match queries, 183 revoking, 93, 403–404 triggers PRIMARY KEY constraint, roles action times, 496 120, 175, 198, 418, 465 granting, 405 actions, 496 primary keys, 22, 196, 246, overview, 396–397 events, 496 656 revoking, 405 overview, 494–495 Priority Ceiling Protocol user identif ers, 396 SQL statements, 497 (PCP), 604 procedural capabilities procedural languages Priority Inheritance compound statements combining SQL with Protocol (PIP), 604 assignment, 488 Access and VBA, priority inversion, 603–604 atomicity, 487 432–433 priority-based scheduling, cursors, 488 classic, 440–441 603 overview, 486 DB2 and Java, 435 private attributes, 113 variables, 487–488 MySQL and C, 434 privileges, access control statements MySQL and C++.NET or classifying users, FOR . . . DO . . . C#, 434 397–398 END FOR, 493 MySQL and Java, 435 Data Control Language, CASE . . . END MySQL and Perl, 434 395 CASE, 489–490 MySQL and PHP, 434 granting IF . . . THEN . . object-oriented, 441 adding data, 399 . ELSE . . . END Oracle SQL and Java, changing data, 399–400 IF, 489 435 def ning new data ITERATE, 493 overview, 431 types, 402 LEAVE, 491–492 SQL Server and .NET delegating LOOP . . . languages, 433–434 responsibility of, 403 ENDLOOP, 491 comparing SQL to deleting data, 399 overview, 488–489 classic, 438 executing SQL REPEAT . . . nonprocedural, 439–440 statements, 402 UNTIL . . . object-oriented, 439 granting all privileges, END REPEAT, 492 overview, 437–438 402 WHILE . . . def ned, 96, 323, 656 overview, 92, 398–399 DO . . . END processors, 608, 620–621 referencing data in WHILE, 492 PRODUCT entity, 77 tables, 400–401 2/24/11 3:47 PM 46_9780470929964-bindex.indd 690 46_9780470929964-bindex.indd 690 2/24/11 3:47 PM

Index 691 PRODUCT table, 35, 80, improving overview, 255–256, 83, 84 performance, 618 587–588 PRODUCTS table, 276, 280 slow queries, 612 SELECT DISTINCT, programming errors, database applications 255–259 369–370 and, 13–14 temporary tables, project scope, 134–135 def ned, 83, 612, 656 259–264 PROJECT table, 66 equi-join, 184 proposals, 453–454 expressions, 325 R PROSPECTS table, 479, extremal, 183 480 grouping, 184 RAD (rapid application protected attributes, 113 HAVING clause, development) tool, pseudotables, 541–543 268–272 656 public attributes, 113 multipoint, 182–183 RAID (Redundant Array of PUBLIC keyword, 92 nested, 291–297, 656 Independent Disks) public users, 398 OR logical connective, arrays, 609 272–273 levels of Q ORDER BY clause, 0, 342, 344 265–268 1, 342, 344 quantif ed comparison ordering, 183–184 5, 342, 344–345 operators, 278 ordering result sets, 10, 342, 345 quantif ed subqueries, 325–326 rolling back 280–283 overview, 13–14 transactions, 377 queries. See also point, 182 spatial partitioning, 621 relational operators; precompiled, 589, 618 striping, 341–342 subqueries pref x match, 183 RAM, 593, 608 ad hoc, 84, 146 problems with range queries, 183 analyzers access plans, 634–636 rapid application Database Engine execution prof les, development (RAD) Tuning Advisor, 636 tool, 656 626–631 range, 183 RDBMS (relational DBMS), Oracle Tuning Advisor, SELECT DISTINCT, 22 633 256–259 READ COMMITTED overview, 622–626 temporary tables, isolation level, SQL Server Prof ler, 259–264 375–376, 388 631–633 tuning READ UNCOMMITTED bottlenecks and HAVING clause, isolation level, eff ciency analysis, 268–272 373–374, 376 622–633 OR logical connective, READ WRITE mode, 374 f nding problem 272–273 read/write time, 596 queries, 633–636 ORDER BY clause, REAL data type, 103–104, 265–268 116, 208, 228 2/24/11 3:47 PM 46_9780470929964-bindex.indd 691 2/24/11 3:47 PM 46_9780470929964-bindex.indd 691

692 SQL All-in-One For Dummies, 2nd Edition Recommendations tab, spatial partitioning, 621 paths, 71 Database Engine striping, 341–342 privileges, 68 Tuning Advisor REF data types, 113, 117 routines, 70 window, 629 reference types, def ned, schemas, 68–69 records 657 sets, relations, def ned, 656 REFERENCES constraint, multisets, and tables, deleting with 401, 418, 465 63–64 updategrams, 552–553 References dialog box, users, 68 incomplete, adding to Visual Basic Editor, views, 67 tables, 85–86 432 relational DBMS (RDBMS), inserting elements into, REFERENCING clause, 496 22 551–552 referencing data in tables, relational operators Download from Wow! eBook <www.wowebook.com> updating from elements, 400–401 EXCEPT, 308 552 referential integrity INTERSECT, 306–308 recovery systems, tuning def ned, 202, 400, 657 JOIN batch transactions, MATCH predicate and, ON clause versus WHERE 600–601 246–248 clause, 319 checkpoints, 599–600 overview, 176–177 column-name joins, database dumps, registers, def ned, 179 314–315 598–599 regression testing, 482 condition joins, memory system relational database model 313–314 hierarchy, 594–595 Codd’s rules, 22–25 cross joins, 309–311 overview, 392–393 converting Entity- equi-joins, 311–313 putting logs and Relationship model to, inner joins, 315 transactions on 50, 163–164 join conditions and different disks, def ned, 15, 22 clustering indexes, 595–597 fl exibility of, 25 320–321 volatile and nonvolatile functional dependencies, natural joins, 313 memory, 593–594 150 outer joins, 316–319 write operations, 598 keys, 151 overview, 308–309 Redundant Array of normalizing, 164–166 UNION, 303–305, 304, Independent Disks overview, 26–27 319, 479 (RAID) relations, 149–150 UNION ALL, 305 arrays, 609 SQL and UNION levels of catalogs, 69 CORRESPONDING, 0, 342, 344 connections, sessions, 306 1, 342, 344 and transactions, relations, relational model 5, 342, 344–345 69–70 def ned, 64, 148, 657 10, 342, 345 functional Implementation phase, rolling back transactions, dependencies, 64–65 138 377 keys, 65–67 overview, 149–150 2/24/11 3:47 PM 46_9780470929964-bindex.indd 692 46_9780470929964-bindex.indd 692 2/24/11 3:47 PM

Index 693 relationships RESEARCHTEAMS entity, granting, 405 binary, 166–170 463 revoking, 405 complex, 37, 50 RESEARCHTEAMS ROLLBACK statement, 93, degree-three, 37 relation, 462 379 degree-two, 35–37 RESEARCHTEAMS table, rolling back transactions many-to-many, 16, 36, 466, 471, 474 checkpoints, 379 460–463 reserved words log f le, 377–378 one-to-many, 16, 35 def ned, 657 overview, 376 one-to-one, 16, 35 using correctly, 100 reasons for, 377 overview, 457–458 RESIGNAL statement, 412, write-ahead log protocol, parent-child, creating 421 378–379 mapping schemas for resource management ROSTER table, 487 tables with, 555–557 database buffer rotational latency, 596 simplifying using manager, 638–639 round-robin scheduling, normalization, 50 disk subsystem, 637–638 603 relative fetches, 332 locking subsystem, 639 ROUTINE_CATALOG f eld, Reliability and logging subsystem, 639 416 Performance Monitor RESTRICT keyword, 401, ROUTINE_NAME f eld, 416 window, 638 404 ROUTINE_SCHEMA f eld, REPEAT . . . UNTIL result set cursors, 329 416 . . . END REPEAT Results tab, Management routines, 70 statement, 492 Studio window, 624 ROW data type, 110–111, REPEAT loop, 492 retesting, 483 117, 529–530 REPEATABLE READ retrieving rows row locks, 385 isolation level, that don’t satisfy row value expressions, 375–376 conditions, 277–278 229, 657 replacement algorithm that satisfy conditions, ROW_COUNT f eld, 414 tuning, 606–607 276–277 <row> element, 532, 573 reports, developing, 481 return clause, 569, row-level trigger, 496 requests 572–573 rows identifying objects using RETURNED_SQLSTATE blocks of, adding to handles, 508–509 f eld, 415, 420 tables, 478 logical, 12 REVOKE statement, 93, def ned, 657 ODBC operation stages, 404 locating with keys, 510–512 revoking 195–196 Requirements phase, access privileges, 93, operating cursors on SDLC 403–404 absolute versus overview, 131–132 roles, 405 relative fetches, 332 statement of right outer joins, 318 deleting rows, 332 requirements, 133 roles FETCH statement, 331 user data model, 132 creating, 397 updating rows, 332 destroying, 397 2/24/11 3:47 PM 46_9780470929964-bindex.indd 693 2/24/11 3:47 PM 46_9780470929964-bindex.indd 693

694 SQL All-in-One For Dummies, 2nd Edition rows (continued) schemas Maintenance phase, 141 retrieving containment hierarchy, overview, 129–130 that don’t satisfy 74 Requirements phase conditions, 277–278 creating, 80–81 overview, 131–132 that satisfy conditions, creating for tables, statement of 276–277 533–534 requirements, 133 searching for, 225 creating updategrams users’ data model, 132 transferring between with searched CASE statement, tables, 478–480 XDR schemas, 554–555 490 values of, 207–208 XSD schemas, 553–554 Second Normal Form rtm document, 522 def ned, 657 (2NF), 152–155 mapping, 551–560 security S overview, 68–69 ACID, 371–372 XML Schema, 522–523 backups :salary host variable, screen forms, 480–481 frequency of, 346 443 script kiddies, 349 full, 346 SALES_CLERK role, 396 scrolling cursors, 328 incremental, 346 SALES_ORDER table, 41 SDLC (System maintenance, 347 SALESPERSON table, 91 Development Life preparation for, Save Tuning Log check Cycle) 345–346 box, Tuning Advisor Def nition phase, 130–131 concurrent-operation window, 627 Design phase confl icts, 370–371 scale, def ned, 657 database applications, database design errors, scheduling DDL 137 369 statements, 388–389 designing database, data-entry errors scheduling threads 136–137 constraints, 368 context switching, 602 documenting, 138 data types, 368 deadlocks, 604 Evaluation phase human checking, overview, 601–602 documenting, 136 368–369 priority inversion, overview, 133–134 overview, 367–368 603–604 project scope, 134–135 Internet threats priority-based reassessing feasibility, denial-of-service scheduling, 603 135 attacks, 351 round-robin scheduling, Final Documentation and overview, 347 603 Testing phase phishing scams, SCHEMA clause, 100, 448 delivering results, 140 362–363 schema owner, def ned, f nalizing SQL injection attacks, 657 documentation, 140 351–362 SCHEMA_NAME f eld, 416, testing systems with Trojan horses, 349–350 419 sample data, 139–140 viruses, 348 Implementation phase, worms, 350 138 zombie spambots, 363 2/24/11 3:47 PM 46_9780470929964-bindex.indd 694 46_9780470929964-bindex.indd 694 2/24/11 3:47 PM

Index 695 layers of protection platform instability, HAVING, 232, 252–253, alertness, 365 339–340 287–288, 375 antivirus software, 364 programming errors, ORDER BY, 232, application-layer 369–370 253–255, 325–326, 329 f rewalls, 364 RAID overview, 231 education, 365 levels, 342, 344–345 WHERE, 234–250 exploits, 364–365 striping, 341–343 observer functions, 115 network-layer f rewalls, recovery system, overview, 231–232 364 392–393 poorly written, 616 overview, 363–364 timestamps, 390–392 reserved words, 100 patches, 364–365 transactions shredding, 561 vulnerabilities, 364–365 committing, 376 tuning queries locking deferrable constraints, HAVING clause, cooling hot spots, 389 379–383 268–272 deadlocks, 385–386, rolling back, 376–379 OR logical connective, 389–390 SET TRANSACTION 272–273 eliminating unneeded statement, 372–373 ORDER BY clause, locks, 387 starting, 373–376 265–268 granularity, 384–385, seek time, 596 overview, 255–256, 388 SELECT DISTINCT query 587–588 overview, 383–384 analysis provided by SELECT DISTINCT, partitioning insertions, MySQL 5, 258–259 255–259 389 analysis provided by temporary tables, scheduling DDL SQL Server 2008 R2, 259–264 statements, 388–389 256–258 UNION relational shortening overview, 255–256 operator, 479 transactions, 387 SELECT privilege viewing data, 399 throughput, 386 mapping tables to XML, XMLELEMENT operator, two-phase, 384 532 534 weakening isolation revoking privileges, 404 selective denormalization, levels, 387–388 SELECT statement 164 maintaining in Data access modes, 374 sensitive cursors, 327–328 Control Language CREATE VIEW SEQUEL (Structured granting access statement, 76 English Query privileges, 92 cursors, 323, 325–326 Language), 52, 657 preserving database Data Manipulation sequential prefetching, integrity with Language, 83 637 transactions, 93 FLWOR expressions serializability, 390–392 revoking access versus, 573 SERIALIZABLE isolation privileges, 93 modifying clauses for level, 373, 375–376 overview, 338–339 FROM, 100, 232–233, 288 server side, def ned, 69 GROUP BY, 232, 250– SERVER_NAME f eld, 415 252, 287 2/24/11 3:47 PM 46_9780470929964-bindex.indd 695 2/24/11 3:47 PM 46_9780470929964-bindex.indd 695

696 SQL All-in-One For Dummies, 2nd Edition SESSION_USER special signif cant stakeholder MySQL and PHP, 434 variable, 210 requirement category, object-oriented, 441 sessions, 69–70 31 Oracle SQL and Java, SET CONSTRAINTS SIMILAR keyword, 214, 435 DEFERRED statement, 239 overview, 431 382 simple CASE statement, SQL server and .NET SET CONSTRAINTS 489–490 languages, 433–434 IMMEDIATE single precision, 104 comparing to procedural statement, 382 single values, 278–280 languages set functions (aggregate single-table views, 76 classic, 438 functions) slow queries, 612 nonprocedural, 439–440 combining with GROUP slow updates, 612 object-oriented, 439 BY clause, 250–251 SMALLINT data type, 102, overview, 437–438 def ned, 651, 657 116, 208, 420 creating databases with subqueries that return social engineering, 349 altering table structure, single values, 279–280 soft failures, 600 202–203 summarizing data with SOME keyword, 240–242, building tables, 194–197 AVG, 212 280 deleting tables, 203 COUNT, 211–212 sort-merge joins, 636 domains, 199 MAX, 212 source type, 114 keys and indexes, 198 MIN, 212 sparse indexes, 185–186 overview, 193 overview, 211 spatial partitioning, 621 planning, 193 SUM, 213 special variables, 210 relationships between SET ROLE statement, 397 SPECIFIC_NAME f eld, 416 tables, 199–202 SET statement, 87, 497 specifying columns, 75 setting constraints, 198 set theory, 63 SPECIMEN entity, 50 DBMS implementations SET TRANSACTION SPECIMEN table, 50 IBM DB2, 59 statement, 372–373, spoof ng, 349 Microsoft Access, 55–59 414 SQL Microsoft SQL Server, sets, def ned, 63–64 casting one data type to 59 settling time, 596 another, 228 MySQL, 60 shared locks, 383 combining with Oracle Database, 59–60 shared-disk architecture, procedural languages overview, 54 610 Access and VBA, PostgreSQL, 61 shared-nothing 432–433 Sybase SQL Anywhere, architecture, 610 classic, 440–441 60 shortening transactions, DB2 and Java, 435 def ned, 657 387 MySQL and C, 434 dynamic, def ned, 657 Show All Events box, MySQL and C++.NET or embedded Trace Properties C#, 434 def ned, 657 dialog box, 632 MySQL and Java, 435 in Java applications, SIGNAL statement, 420 MySQL and Perl, 434 445 2/24/11 3:47 PM 46_9780470929964-bindex.indd 696 2/24/11 3:47 PM 46_9780470929964-bindex.indd 696

Index 697 in Oracle Pro*C SQL Inject Me Firefox add- SQLAllocHandle applications, 442–445 on, 361 function, 510–511 overview, 441–442 SQL injection attacks SQLConnect function, in Perl applications, def ned, 657 510 445 GET parameter SQLDriverConnect in PHP applications, dynamic string function, 510 445–446 building, 352–353 SQL/DS, 657 in Visual Basic .NET escape characters, SQLEXCEPTION applications, 446 354–356 condition, 411 entering data with, giving too much away SQLExecDirect 476–477 in error messages, function, 511 interactive, def ned, 657 357–358 SQL-invoked routines, 70 ISO/IEC SQL standard, 53 normal fl ow of SQLJ (Java-based origin of, 51–52 execution, 358 embedded SQL), 435 relational models and putting user input SQL-session user catalogs, 69 directly in dynamic identif er, 396 connections, 69–70 SQL statements, SQLSetConnectOption functional 356–357 function, 511 dependencies, 64–65 types, 356 SQLSTATE status keys, 65–67 overview, 351–352 parameter (host multisets, 63–64 recognizing unsafe variable) overview, 63 conf gurations, 359 compound statements, paths, 71 testing for vulnerabilities 486 privileges, 68 by inference, 360 def ned, 407 relations, 63–64 using testing tools, FETCH syntax, 331 routines, 70 360–362 interpreting information, schemas, 68–69 SQL modules 419–420 sessions, 69–70 declarations, 447–448 overview, 408–410 sets, 63–64 in Oracle, 449 SQLSTATE VALUE tables, 63–64 overview, 446–447 ‘xxyyy’ condition, transactions, 69–70 procedures, 448–449 411 users, 68 SQL pseudotables, SQLWARNING condition, views, 67 541–543 411 uses for, 52 SQL routines, 70 SQRT numeric value XQuery versus SQL Server. See Microsoft function, 219 FLWOR versus SELECT, SQL Server stakeholders 573 SQL Server Management building consensus relating data types, Studio, 623–624 among, 146–148 573–576 SQL Server Prof ler, identifying and SQL Anywhere, 429 631–633 interviewing, 30, 452 SQL_SUCCESS code, 511 obtaining buy-ins, 31 2/24/11 3:47 PM 46_9780470929964-bindex.indd 697 2/24/11 3:47 PM 46_9780470929964-bindex.indd 697

698 SQL All-in-One For Dummies, 2nd Edition stakeholders (continued) control string value expressions, overview, 143–144 FOR . . . DO . . . 221–222, 659 reconciling confl icting END FOR, 493 string value functions requirements among, CASE . . . END CONVERT, 215 30–31 CASE, 489–490 LOWER, 214 standards organization, IF . . . THEN . . OVERLAY, 215 145–146 . ELSE . . . END SUBSTRING (FROM), supervisor, 144 IF, 489 213–214 upper management, 146 ITERATE, 493 SUBSTRING users, 144–145 LEAVE, 491–492 (SIMILAR), 214 standards organizations, LOOP . . . TRANSLATE, 215 145–146 ENDLOOP, 491 TRIM, 215 START TRANSACTION overview, 488–489 UPPER, 214 statement, 373 REPEAT . . . stripes, def ned, 342 starting transactions UNTIL . . . striping, 341–342 access modes, 374 END REPEAT, 492 strong entities, 41–42 isolation levels WHILE . . . DO . . Structured English Query READ COMMITTED, 375 . END WHILE, 492 Language (SEQUEL), READ UNCOMMITTED, executing 52, 657 374 combining SQL with structured user-def ned REPEATABLE READ, host languages, 96–97 types 375 embedded SQL, 97–99 def ned, 658 SERIALIZABLE, interactive SQL, 96 example of, 115–116 375–376 module language, subtypes and overview, 373 99–100 supertypes, 115 statement handles, 508 privilege of, 402 STUDENT subtype, 44 statement-level trigger, putting user input STUDENTS table, 487 496 directly into, 356–357 SUBCLASS_ORIGIN f eld, statements. See also triggered, 497 415–417 names of specifi c statements of subqueries statements; SELECT requirements, 133, 453 correlated statement static SQL, def ned, 352 comparison operator, compound statusn host variable, 285–287 assignment, 488 419–420 def ned, 276 atomicity, 487 stored functions, 497 in HAVING clause, cursors, 488 stored modules, 498–499 287–288 def ned, 420 stored procedures IN keyword, 284–285 overview, 486 minimizing traff c tuning, 297–302 variables, 487–488 between application using as existence test, containing nested and server, 589 283–284 queries, 291–297 using, 494 2/24/11 3:47 PM 46_9780470929964-bindex.indd 698 2/24/11 3:47 PM 46_9780470929964-bindex.indd 698

Index 699 def ned, 658 Sybase SQL Anywhere, System Development Life INSERT, DELETE, and 60 Cycle (SDLC) UPDATE statements, <sync> block, 551 Def nition phase, 288–291 <sync> keyword, 551 130–131 overview, 275 system backups Design phase quantif ed, 280–283 frequency of, 346 database applications, retrieving rows full, 346 137 that don’t satisfy incremental, 346 designing database, conditions, 277–278 maintenance, 347 136–137 that satisfy conditions, preparation for, documenting, 138 276–277 345–346 Evaluation phase that return single values, system development documenting, 136 278–280 components of database overview, 133–134 tuning considerations systems project scope, 134–135 for statements database applications, reassessing feasibility, containing nested 129 135 queries, database engines, Final Documentation and 291–297 128 Testing phase SUBSTRING (FROM) databases, 128 delivering results, 140 string value function, front end, 128–129 f nalizing 213–214 overview, 127–128 documentation, 140 SUBSTRING (SIMILAR) users, 129 testing systems with string value function, System Development sample data, 139–140 214 Life Cycle Implementation phase, subsystems Def nition phase, 138 disk, 637–638 130–131 Maintenance phase, 141 locking, 639 Design phase, overview, 129–130 logging, 639 136–138 Requirements phase subtype entities Evaluation phase, overview, 131–132 def ned, 658 133–136 statement of overview, 43–44 Final Documentation requirements, 133 SUM function, 213 and Testing phase, users’ data model, 132 supertype entities 139–140 SYSTEM_USER special def ned, 658 Implementation phase, variable, 210 overview, 43–44 138 SUPPLIER relation, 172 Maintenance phase, T SUPPLIER table, 195 141 SUPPLIER_PART relation, overview, 129–130 table constraints, 170 Requirements phase, 120–121, 198, 465 SUPPLIER_PART table, 131–133 table locks, 385 195, 197 TABLE_NAME f eld, 416, 419 2/24/11 3:47 PM 46_9780470929964-bindex.indd 699 2/24/11 3:47 PM 46_9780470929964-bindex.indd 699

700 SQL All-in-One For Dummies, 2nd Edition tables. See also relational locating rows with keys, hackers, 483 operators; subqueries 195–196 overview, 481–482 adding constraints to, mapping, 532 retesting, 483 418–419 modifying, 82 testing phase. See Final adding data overview, 63–64 Documentation and blocks of rows to referencing data in, Testing phase, SDLC tables, 478 400–401 THEN clause, 490 bypassing typing, 86 relational database Third Normal Form (3NF), copying from foreign hierarchy, 69 152, 155, 157 data f les, 478 relationships between, threads, scheduling with forms, 476 199–202 context switching, 602 incomplete records, removing, 82, 468 deadlocks, 604 85–86 schemas, creating, overview, 601–602 to selected columns, 533–534 priority inversion, 477 searching for rows, 225 603–604 with SQL, 476–477 storing XML data in priority-based transferring all rows creating tables to hold scheduling, 603 between tables, XML data, 543 round-robin scheduling, 478–479 inserting XML data into 603 transferring selected SQL pseudotables, throughput, 386 columns and rows 541–543 tightly coupled between tables, updating XML architecture, 610 479–480 documents, 543–560 TIME WITH TIME ZONE typing, 84–85 temporary, 259–264 data type, 108–109, altering structure of, updating data in, 87–90 117, 209 202–203 updating rows, 327 TIME WITHOUT TIME building Tamper Data Firefox add- ZONE data type, 108, locating table rows with on, 360 117 keys, 195–196 TCP/IP (Transmission TIME(2) data type, 209 using CREATE TABLE Control Protocol/ TIMESTAMP WITH TIME statement, 196–197 Internet Protocol), 658 ZONE data type, 109, changing structure of, teleprocessing system, 117, 209 467–468 658 TIMESTAMP WITHOUT containment hierarchy, temporal locality, 606 TIME ZONE data type, 74 temporal partitioning, 622 109, 117 creating, 75, 464–467, 474 temporary tables, 259–264 TIMESTAMP(0) data type, def ned, 64, 658 TEST entity, 50 209 deleting, 203 TEST table, 50 timestamps, 390–392 deleting data from, 90 testing applications tired indexes, 587 full scans of, 187–188 beta testing, 482 Tools menu, Visual Basic indexes and size of, 187 f xing bugs, 482, 483 Editor, 432 2/24/11 3:47 PM 46_9780470929964-bindex.indd 700 46_9780470929964-bindex.indd 700 2/24/11 3:47 PM

Index 701 Trace Properties dialog TRANSACTIONS_ROLLED_ TRUE value, 107, 234 box, SQL Server BACK f eld, 414 tuning databases Management Studio, TRANSDETAIL table, 290 indexes 632 transforming models avoiding unnecessary, traff c, minimizing, 589 eliminating many-to- 582 TRANSACT table, 245 many relationships, choosing columns for, TRANSACTION_ACTIVE 460–463 582–583 f eld, 414 normalizing Entity- choosing types of, 585 transactions Relationship model, clustering, 583–585 committing, 376 463–464 composite, 586 deferrable constraints, transitive dependency, multicolumn, 583 379–383 155, 658 tuning, 586–587 def ned, 70, 371, 658 TRANSLATE string value weighing cost of failure of, 592 function, 215 maintenance, 585–586 overview, 69–70 translation table, def ned, minimizing traff c preserving database 658 between applications integrity with, 93 TRANSMASTER table, 287, and servers, 589 putting on different 288 overview, 579 disks Transmission Control physical design, 580–581 hard disk drive Protocol/Internet precompiling queries, construction, 596 Protocol (TCP/IP), 658 589 hard disk drive treestructured separating user performance, 596–597 organization, 16 interactions from overview, 595 triage, def ned, 31 transactions, 589 rolling back TRIGGER privilege, 399 tuning queries, 587–588 checkpoints, 379 TRIGGER_CATALOG f eld, tuning transactions, 588 log f le, 377–378 416 workload, 580 overview, 376 TRIGGER_NAME f eld, 416 tuning disk controller reasons for, 377 TRIGGER_SCHEMA f eld, cache, 607 write-ahead log 416 tuning indexes, 586–587 protocol, 378–379 triggers tuning locks separating user action times, 496 controlling lock interactions from, 589 actions, 496 granularity, 388 SET TRANSACTION def ned, 658 cooling hot spots, 389 statement, 372–373 events, 496 deadlock intervals, starting overview, 494–495 389–390 access modes, 374 SQL statements, 497 eliminating unneeded isolation levels, TRIM string value locks, 387 374–376 function, 215 partitioning insertions, TRANSACTIONS_ Trojan horses, 349–350, 389 COMMITTED f eld, 414 658 2/24/11 3:47 PM 46_9780470929964-bindex.indd 701 2/24/11 3:47 PM 46_9780470929964-bindex.indd 701

702 SQL All-in-One For Dummies, 2nd Edition tuning locks (continued) overview, 255–256 multiprocessor scheduling DDL temporary tables, environments, statements, 388–389 259–264 609–610 shortening transactions, tuning recovery systems operating system 387 memory system database buffer size, throughput, 386 hierarchy, 594–595 604–605 weakening isolation optimizing batch page usage factor, 605 levels, 387–388 transactions, 600–601 scheduling threads, tuning operating systems overview, 392–393 601–604 determining database performing database overview, 591 buffer size, 604–605 dumps, 598–599 recovery system scheduling threads putting logs and batch transactions, context switching, 602 transactions on 600–601 deadlocks, 604 different disks checkpoints, 599–600 overview, 601–602 hard disk drive database dumps, priority inversion, construction, 596 598–599 603–604 hard disk drive memory system priority-based performance, 596–597 hierarchy, 594–595 scheduling, 603 overview, 595 overview, 392–393 round-robin scheduling, setting checkpoints, putting logs and 603 599–600 transactions on tuning page usage factor, tuning write operations, different disks, 605 598 595–597 tuning page replacement volatile and nonvolatile volatile and nonvolatile algorithm, 606–607 memory, 593–594 memory, 593–594 tuning page usage factor, tuning the environment write operations, 598 605 adding hardware surviving failures, tuning queries hard disks, 608–609 592–593 HAVING clause, 268–272 overview, 607–608 tuning transactions, 588 OR logical connective, processors, 608 tuning write operations, 272–273 RAID arrays, 609 598 ORDER BY clause, RAM, 608 tuple stream, 570 265–268 maximizing hardware tuples, 64, 148 overview, 255–256, disk controller cache, two-phase locking, 384 587–588 607 typical queries, 190 SELECT DISTINCT optimizing placement typing analysis provided by of code and data on adding data to tables by, MySQL 5, 258–259 hard disks, 606 84–85 analysis provided by page replacement bypassing, 86 SQL Server 2008 R2, algorithm, 606–607 256–258 2/24/11 3:47 PM 46_9780470929964-bindex.indd 702 46_9780470929964-bindex.indd 702 2/24/11 3:47 PM

Index 703 U trigger events, 496 user errors. See also errors updating data in tables, concurrent-operation 87–89 confl icts, 370–371 UDFs (user-def ned workload analysis, 580 data-entry functions), 435 updategrams constraints, 368 UDTs (user-def ned types) creating data types, 368 def ned, 25, 658 with XDR schemas, human checking, distinct types, 114 554–555 368–369 overview, 113 with XSD schemas, overview, 367–368 structured types 553–554 programming, 369–370 example of, 115–116 def ned, 549 user identif ers, 396 subtypes and deleting records with, user interactions supertypes, 115 552–553 direct, avoiding, 617 XML data type, 524 mapping data into separating from UNDER privilege, 402 tables, 550 transactions, 589 UNDO handler effect, 411 namespaces and user interfaces UNION ALL operator, 305 keywords, 550–551 connecting to databases, UNION CORRESPONDING UPDATEXML function, 471–473 operator, 306 545, 548 designing, 468, 470–471 UNION DISTINCT updating user-def ned functions operator, 304 data, 87–90 (UDFs), 435 UNION operator, 303–305, records from elements, user-def ned types (UDTs) 319, 479 552 def ned, 25, 658 union-compatible tables, rows, 327, 332 distinct types, 114 303 views, 91 overview, 113 UNIQUE column XML documents structured types constraint, 119, Microsoft tools for, example of, 115–116 174–175, 197 549–560 subtypes and unique key, 246 Oracle tools for, supertypes, 115 UNIQUE predicate, 85–86, 544–548 XML data type, 524 243–244, 246, 248 overview, 543–544 USERINFO table, 355 universal Turing machine, updg namespace pref x, users, as part of database 53 550 system, 68, 129 UNKNOWN value, 107 upper management, 146 users’ data models unsafe conf gurations, 359 UPPER string value def ned, 143 UNTYPED type, 110 function, 214 overview, 29–30, 132 update anomalies, 658 USAGE privilege, 399, stakeholders update functions, 544 401–402 building consensus UPDATE statement USER DEFINED TYPE among, 146–148 datetime values, 330 type, 117 identifying and subqueries, 288–291 interviewing, 30 obtaining buy-ins, 31 2/24/11 3:47 PM 46_9780470929964-bindex.indd 703 2/24/11 3:47 PM 46_9780470929964-bindex.indd 703

704 SQL All-in-One For Dummies, 2nd Edition users’ data models, COALESCE, 227 TRANSLATE, 215 stakeholders (continued) def ned, 658 TRIM, 215 overview, 143–144 NULLIF, 226–227 UPPER, 214 reconciling confl icting overview, 224 ValueOutOfRange error, requirements among, datetime, 222–223 421 30–31 def ned, 659 VALUES keyword, 85, 477 standards organization, def ned, 658 VARBINARY (1) data 145–146 interval, 223 type, 208 supervisor, 144 numeric, 221 VARCHAR (CHARACTER upper management, 146 def ned, 659 VARYING) data type, users, 144–145 row, 229, 657 105, 116, 208 translating to formal string, 221–222 variables Entity-Relationship def ned, 659 host model value functions :comm, 443 advanced concepts, datetime, 220–221 declaring, 444 41–45 def ned, 211, 659 def ned, 654 complex relationships, numeric :salary, 443 50 ABS, 218 statusn, 419–420 drawing diagrams, CARDINALITY, 218, 224 overview, 209–210, 38–41 CEILING (CEIL), 219 487–488 examples of, 45–49 CHARACTER_LENGTH, special, 210 modeling techniques, 217 VBA (Visual Basic for 32–37 EXP, 219 Applications) simplifying EXTRACT, 217, 524 ADOdb library, 432–433 relationships, 50 FLOOR, 219 ADOX library, 433 translating to relational LN, 218–219 overview, 425 model, 50, 463–464 MOD, 218 :vcustid variable, 85–86 USERS table, 355, 564, 565 OCTET_LENGTH, vfname variable, 489 users.xml f le, 565 217–218 View drop-down menu, UTC (Coordinated POSITION, 216–217 Access, 56 Universal Time), 108, POWER, 219 viewing data, privilege of, 222 SQRT, 219 399 WIDTH_BUCKET, 220 views V string creating, 78–80 CONVERT, 215 def ned, 659 VALID predicate, 539–540 LOWER, 214 multitable, 77–78 value expressions OVERLAY, 215 overview, 67 array, 224 SUBSTRING (FROM), single-table, 76–77 Boolean, 107, 224 213–214 updating, 91 conditional SUBSTRING virtual tables, 659 CASE, 225–226 (SIMILAR), 214 2/24/11 3:47 PM 46_9780470929964-bindex.indd 704 46_9780470929964-bindex.indd 704 2/24/11 3:47 PM

Index 705 viruses logical connectives X antivirus software, 364 AND, 248–249 def ned, 659 NOT, 249–250 XDR schemas, 554–555 overview, 348–349 OR, 249 xdt:dayTimeDuration Visual Basic for MATCH predicate, data type, 575 Applications (VBA) 245–248 xdt:untyped data type, ADOdb library, 432–433 NOT IN predicate, 575 ADOX library, 433 236–237 dt:yearMonthDuration overview, 425 NOT LIKE predicate, data type, 575 Visual Basic .NET 237–239 XML (Extensible Markup applications, 446 NULL predicate, 239–240 Language) vmemid variable, 477 OVERLAPS predicate, creating schemas for volatile memory, 593–594 244–245 SQL tables, 533–534 vulnerabilities BETWEEN predicate, data types, 524–525 installing layers of 235–236 def ned, 654 protection against, IN predicate, 236–237 document parts 364–365 SIMILAR predicate, 239 attributes, 521 testing for SOME predicate, 240–242 declarations, 519 by inference, 360 UNIQUE predicate, 243 elements, 519–521 using testing tools, WHILE . . . DO . entity references, 360–362 . . END WHILE 521–522 statement, 492 numeric character W WHILE statement, 492–493 references, 522 WIDTH_BUCKET numeric overview, 518–519 weak entities, 41–42 value function, 220 mapping Web Developer Firefox WITH ADMIN OPTION character sets, 526 add-on, 360 clause, 405 data types, 527 WHEN clause, 490, 496 WITH GRANT OPTION identif ers, 526–527 WHENEVER clause, 407, clause, 403, 405 nonpredef ned data 412–413, 420 WITH RETURN syntax, 329 types, 527–531 where clause, 571 workloads, 580 overview, 526 WHERE clause World Wide Web, def ned, tables, 532–533 ALL predicate, 240–242 659 null values, 532–533 ANY predicate, 240–242 worms, 350, 659 operating on data with ON clause versus, 319 write locks, 384 SQL functions comparison predicates, write operations, 598 XMLAGG, 535–536 234 write-ahead log protocol, XMLCAST, 538 DISTINCT predicate, 378–379 XMLCOMMENT, 536 244 write-back protocol, 607 XMLCONCAT, 535 EXISTS predicate, 243 write-through protocol, XMLELEMENT, 534 LIKE predicate, 237–239 607 2/24/11 3:47 PM 46_9780470929964-bindex.indd 705 2/24/11 3:47 PM 46_9780470929964-bindex.indd 705

706 SQL All-in-One For Dummies, 2nd Edition XML, operating on data XMLCOMMENT function, 536 XSD schemas, 553–554 with SQL functions XMLCONCAT operator, 535 xs:date data type, 575 (continued) XML(CONTENT) subtype, xs:dateTime data type, XMLFOREST, 534–535 110 575 XMLPARSE, 536–537 XML(DOCUMENT) subtype, xs:decimal data type, XMLPI, 537 110 574 XMLQUERY, 537–538 XMLELEMENT operator, xs:double data type, 575 overview, 517–518 534 xs:duration data type, predicates XMLEXISTS predicate, 539 575 CONTENT, 539 XMLFOREST operator, xs:ENTITIES data type, DOCUMENT, 538 534–535 574 VALID, 539–540 XMLPARSE function, xs:ENTITY data type, 574 XMLEXISTS, 539 536–537 xs:float data type, 574 relating SQL to, 523–524 XMLPI function, 537 xs:gDay data type, 575 retrieving data from XMLQUERY function, xs:gMonth data type, 575 documents 537–538 xs:gMonthDay data type, FLWOR expressions, XMLSCHEMA type, 110 575 568–573 XML(SEQUENCE) subtype, xs:gYear data type, 575 XQuery, 562–568, 110 xs:gYearMonth data 573–576 XMLTABLE function, 543 type, 575 storing data in tables XQuery xs:hexBinary data type, creating tables to hold functionality of, 563–564 575 XML data, 543 origin of, 562 xs:ID data type, 574 inserting XML data into requirements, 562–563 xs:IDREF data type, 574 SQL pseudotables, SQL versus xs:IDREFS data type, 574 541–543 FLWOR versus SELECT, xsi:nil attribute, 559 Microsoft updating 573 xs:int data type, 574 tools, 549–560 relating data types, xs:integer data type, Oracle updating tools, 573–576 574 544–548 usage scenarios, 564–568 xs:language data type, updating XML xs:anySimpleType data 574 documents, 543–544 type, 575 xs:long data type, 574 XML Schema, 522–523 xs:anyType data type, xs:Name data type, 574 XML data types, 110–111, 575 xs:NCNAME data type, 574 524–525 xs:anyURI data type, 575 xs:negativeInteger XML valid xs:base64Binary data data type, 574 according-to type, 575 xs:NMTOKEN data type, clause, 540 xs:BOOLEAN data type, 574 XMLAGG function, 535–536 574 xs:NMTOKENS data type, XMLCAST function, 538 xs:byte data type, 574 574 2/24/11 3:47 PM 46_9780470929964-bindex.indd 706 2/24/11 3:47 PM 46_9780470929964-bindex.indd 706

Index 707 xs:nonNegative xs:short data type, 574 Y Integer data type, xs:string data type, 574 574 xs:time data type, 575 YTD_SALES table, 313 xs:nonPositive xs:token data type, 574 Integer data type, xs:unsignedByte data Z 574 type, 574 xs:normalizedString xs:unsignedInt data zero-day exploits, 365 data type, 574 type, 574 zombie spambots, 363, xs:NOTATION data type, xs:unsignedLong data 659 575 type, 574 xs:positiveInteger xs:unsignedShort data data type, 574 type, 574 Download from Wow! eBook <www.wowebook.com> xs:QName data type, 575 2/24/11 3:47 PM 46_9780470929964-bindex.indd 707 2/24/11 3:47 PM 46_9780470929964-bindex.indd 707

708 SQL All-in-One For Dummies, 2nd Edition 2/24/11 3:47 PM 46_9780470929964-bindex.indd 708 2/24/11 3:47 PM 46_9780470929964-bindex.indd 708

Apple & Macs Digital SLR Cameras & BlackBerry iPad For Dummies Computer Hardware Digital Photography Hobbies/General Chess For Dummies, 978-0-470-58027-1 For Dummies, Photography For Dummies, 2nd Edition 4th Edition 3rd Edition 978-0-7645-8404-6 iPhone For Dummies, 978-0-470-60700-8 978-0-470-46606-3 4th Edition Drawing 978-0-470-87870-5 Computers For Seniors Photoshop Elements 8 Cartoons & Comics For Dummies, For Dummies MacBook For Dummies, 3rd For Dummies 2nd Edition 978-0-470-52967-6 Edition 978-0-470-42683-8 978-0-470-53483-0 978-0-470-76918-8 Knitting For Dummies, PCs For Dummies, Gardening Mac OS X Snow Leopard For 2nd Edition Windows Gardening Basics Dummies 7 Edition For Dummies 978-0-470-28747-7 978-0-470-43543-4 978-0-470-46542-4 978-0-470-03749-2 Organizing Laptops For Dummies, Organic Gardening For Dummies Business 4th Edition For Dummies, 978-0-7645-5300-4 Bookkeeping For Dummies 978-0-470-57829-2 2nd Edition Su Doku For Dummies 978-0-7645-9848-7 978-0-470-43067-5 978-0-470-01892-7 Cooking & Entertaining Job Interviews For Dummies, Cooking Basics Green/Sustainable Home Improvement 3rd Edition For Dummies, Raising Chickens 978-0-470-17748-8 3rd Edition For Dummies Home Maintenance 978-0-7645-7206-7 978-0-470-46544-8 For Dummies, Resumes For Dummies, 2nd Edition 5th Edition Wine For Dummies, Green Cleaning 978-0-470-43063-7 978-0-470-08037-5 4th Edition For Dummies 978-0-470-04579-4 978-0-470-39106-8 Home Theater Starting an For Dummies, Online Business Diet & Nutrition Health 3rd Edition For Dummies, 978-0-470-41189-6 6th Edition Dieting For Dummies, Diabetes For Dummies, 978-0-470-60210-2 2nd Edition 3rd Edition Living the 978-0-7645-4149-0 978-0-470-27086-8 Country Lifestyle Stock Investing For Dummies, Nutrition For Dummies, Food Allergies All-in-One 3rd Edition 4th Edition For Dummies For Dummies 978-0-470-40114-9 978-0-471-79868-2 978-0-470-09584-3 978-0-470-43061-3 Successful Weight Training Living Gluten-Free Solar Power Your Home Time Management For Dummies, For Dummies, For Dummies, For Dummies 3rd Edition 2nd Edition 2nd Edition 978-0-470-29034-7 978-0-471-76845-6 978-0-470-58589-4 978-0-470-59678-4 Available wherever books are sold. For more information or to order direct: U.S. customers visit www.dummies.com or call 1-877-762-2974. U.K. customers visit www.wileyeurope.com or call (0) 1243 843291. Canadian customers visit www.wiley.ca or call 1-800-567-4797. 2/24/11 3:47 PM 47_9780470929964-badvert01.indd 709 2/24/11 3:47 PM 47_9780470929964-badvert01.indd 709

Internet Parenting For Dummies, Algebra I Baseball Blogging For Dummies, Math & Science Parenting & Education Sports 3rd Edition For Dummies, 2nd Edition For Dummies, 2nd Edition 978-0-7645-5418-6 3rd Edition 978-0-470-61996-4 978-0-470-55964-2 Type 1 Diabetes 978-0-7645-7537-2 eBay For Dummies, Biology For Dummies, For Dummies Basketball 6th Edition 2nd Edition 978-0-470-17811-9 For Dummies, 978-0-470-49741-8 978-0-470-59875-7 2nd Edition Facebook For Dummies, Calculus For Dummies Pets 978-0-7645-5248-9 3rd Edition 978-0-7645-2498-1 Cats For Dummies, 978-0-470-87804-0 2nd Edition Golf For Dummies, Chemistry For Dummies 978-0-7645-5275-5 3rd Edition Web Marketing 978-0-7645-5430-8 978-0-471-76871-5 For Dummies, Dog Training For Dummies, 2nd Edition Microsoft Office 3rd Edition Web Development 978-0-470-60029-0 978-0-470-37181-7 Excel 2010 For Dummies Web Design WordPress 978-0-470-48953-6 Puppies For Dummies, All-in-One 2nd Edition For Dummies, Office 2010 All-in-One 978-0-470-03717-1 For Dummies 3rd Edition For Dummies 978-0-470-41796-6 978-0-470-59274-8 978-0-470-49748-7 Religion & Inspiration Web Sites Office 2010 For Dummies, The Bible For Dummies Do-It-Yourself Language & Foreign Book + DVD Bundle 978-0-7645-5296-0 For Dummies, Language 978-0-470-62698-6 2nd Edition Catholicism For Dummies 978-0-470-56520-9 French For Dummies Word 2010 For Dummies 978-0-7645-5391-2 978-0-7645-5193-2 978-0-470-48772-3 Women in the Bible Windows 7 Italian Phrases For Dummies Music Windows 7 For Dummies 978-0-7645-8475-6 For Dummies 978-0-7645-7203-6 Guitar For Dummies, 2nd Edition Self-Help & Relationship 978-0-470-49743-2 Spanish For Dummies, 978-0-7645-9904-0 Windows 7 Anger Management 2nd Edition iPod & iTunes For For Dummies For Dummies, 978-0-470-87855-2 Dummies, 8th Edition 978-0-470-03715-7 Book + DVD Bundle Spanish 978-0-470-87871-2 978-0-470-52398-8 Overcoming Anxiety For Dummies, Piano Exercises For Dummies, Windows 7 All-in-One Audio Set For Dummies 2nd Edition For Dummies 978-0-470-09585-0 978-0-470-38765-8 978-0-470-57441-6 978-0-470-48763-1 Making Everything Easier! ™ 3rd Edition Making Everything Easier! ™ Making Everything Easier! ™ ™ Microsoft ® Facebook Ofce 2010 iPad ™ Windows 7 ® A LL -I N- O N E BOOKS Learn to: 8 BOOKS Learn to: Learn to: IN o y e z i l a W r u o s r n • Create a Profle, navigate the site, 1 • Set up your iPad, use the multitouch ART IS TK • eP with your own photos n i d o w 7 s d o t k s e p interface, and get connected and use privacy features • Find friends and post messages • Surf the Web, listen to music, watch TO BE INSERTED • pS e e u d p W n i d o w w s b h t i n i - t l i u • Common Office Tools videos, and download apps shortcuts • Word • Add applications and upload • Turn your iPad into a portable game • uC o t s m e z i W n i d o w w s n i n r a g o o t s l n y photos to your Facebook page • Outlook® console give the notices you want • PowerPoint® DURING • Build a fan page or get the word out • Excel® • M o v e your files from your old PC to a about an event • Access® Windows 7 computer • Publisher IN FULL COLOR! • Office 2010 — One Step Beyond ROUTING Peter Weverka Andy Rathbone Leah Pearlman Author of PowerPoint Edward C. Baig Author of all previous editions of Carolyn Abram All-in-One For Dummies Bob “Dr. Mac” LeVitus Windows For Dummies Available wherever books are sold. For more information or to order direct: U.S. customers visit www.dummies.com or call 1-877-762-2974. U.K. customers visit www.wileyeurope.com or call (0) 1243 843291. Canadian customers visit www.wiley.ca or call 1-800-567-4797. 2/24/11 3:47 PM 47_9780470929964-badvert01.indd 710 2/24/11 3:47 PM 47_9780470929964-badvert01.indd 710

Wherever you are in life, Dummies makes it easier. From fashion to Facebook , ® wine to Windows , and everything in between, ® Dummies makes it easier. Visit us at Dummies.com 2/24/11 3:47 PM 47_9780470929964-badvert01.indd 711 47_9780470929964-badvert01.indd 711 2/24/11 3:47 PM

Dummies products make life easier! DIY • Consumer Electronics • Crafts • Software • Cookware • Hobbies • Videos • Music • Games • and More! For more information, go to Dummies.com and search ® the store by category. 2/24/11 3:47 PM 47_9780470929964-badvert01.indd 712 47_9780470929964-badvert01.indd 712 2/24/11 3:47 PM

Get More and Do More at Dummies.com ® Start with FREE Cheat Sheets Cheat Sheets include • Checklists • Charts • Common Instructions • And Other Good Stuff! Mobile Apps To access the Cheat Sheet created specifically for this book, go to www.dummies.com/cheatsheet/sqlaio Get Smart at Dummies.com Dummies.com makes your life easier with 1,000s of answers on everything from removing wallpaper to using the latest version of Windows. Check out our • Videos • Illustrated Articles • Step-by-Step Instructions There’s a Dummies App for This and That Plus, each month you can win valuable prizes by entering our Dummies.com sweepstakes. * With more than 200 million books in print and over 1,600 unique titles, Dummies is a global leader in how-to information. Now Want a weekly dose of Dummies? Sign up for Newsletters on you can get the same great Dummies information in an App. With • Digital Photography topics such as Wine, Spanish, Digital Photography, Certification, • Microsoft Windows & Office and more, you’ll have instant access to the topics you need to • Personal Finance & Investing know in a format you can trust. • Health & Wellness • Computing, iPods & Cell Phones • eBay To get information on all our Dummies apps, visit the following: • Internet www.Dummies.com/go/mobile from your computer. • Food, Home & Garden www.Dummies.com/go/iphone/apps from your phone. Find out “HOW” at Dummies.com *Sweepstakes not currently available in all countries; visit Dummies.com for official rules.

Programming Languages/SQL Making Everything Easier! ™ 2nd Edition Want to be a database diva or SQL and Overview Database Relational SQL Queries Appendices Data Security SQL and XML Development Programming SQL Concepts Database Tuning the office’s information oracle? 2nd Edition Do it with SQL — here’s how! Open the book and find: SQL does one thing, and it does that better than any other • Details of how SQL works language: it handles data in relational databases. SQL lets SQL • SQL data types you create databases, manipulate the data in them, retrieve information, control who has access, and much more. Whether • Why the System Development Life Cycle matters you’re new to SQL or an old hand who wants to supercharge your database, find help in one of these minibooks! • Directions for structuring ALL - SQL queries ALL - I N - O N E • The overview — Books I and II give you the nitty-gritty about I SQL SQL, relational databases, and the System Development • Tips for protecting your database - N Life Cycle from user errors O • If you have queries — learn the fine points of making SQL • Sample applications you can use N E retrieve exactly what you want from the database and no more • Ways to track down and eliminate • Safety first — discover the tools SQL provides to protect your bottlenecks vital data and handle any errors that occur • Definitions of common SQL terms • Integrating SQL with other languages — understand how SQL works with Visual Basic , Java , C++, and XML ® ® • Turbocharge your database — fine-tune and optimize your databases to keep them performing optimally 8 BOOKS BOOKS IN Visit the companion Web site at www.dummies.com/go/ Go to Dummies.com ® 1 sqlaiofd2e to download a zip file that contains all the for videos, step-by-step examples, sample code used in this book how-to articles, or to shop! • SQL Concepts • Relational Database Development • SQL Queries • Data Security • SQL and Programming $39.99 US / $47.99 CN / £27.99 UK • SQL and XML Allen G. Taylor lectures nationally on databases, innovation, and ISBN 978-0-470-92996-4 • Database Tuning Overview entrepreneurship, and teaches database development internationally • Appendices through a leading online education provider. Among his more than 20 books are all editions of SQL For Dummies and Database Development For Dummies. Taylor Allen G. Taylor