book

SQL All-In-One For Dummies, 3rd Edition

by Allen G. Taylor

April 2019

Intermediate to advanced

768 pages

18h 44m

English

For Dummies

Read now

Unlock full access

About This BookFoolish AssumptionsConventions Used in This BookWhat You Don’t Have to ReadHow This Book Is OrganizedIcons Used in This BookWhere to Go from Here
Understanding Why Today’s Databases Are Better than Early DatabasesDatabases, Queries, and Database ApplicationsExamining Competing Database ModelsWhy the Relational Model Won
Capturing the Users’ Data ModelTranslating the Users’ Data Model to a Formal Entity-Relationship Model
Where SQL Came FromKnowing What SQL DoesThe ISO/IEC SQL StandardKnowing What SQL Does Not DoChoosing and Using an Available DBMS Implementation
Sets, Relations, Multisets, and TablesFunctional DependenciesKeysViewsUsersPrivilegesSchemasCatalogsConnections, Sessions, and TransactionsRoutinesPaths
Creating a Database with the Data Definition LanguageOperating on Data with the Data Manipulation Language (DML)Maintaining Security in the Data Control Language (DCL)
Executing SQL StatementsUsing Reserved Words CorrectlySQL’s Data TypesHandling Null ValuesApplying Constraints

The Components of a Database SystemThe System Development Life Cycle
Finding and Listening to Interested PartiesBuilding ConsensusBuilding a Relational ModelBeing Aware of the Danger of AnomaliesThe Database Integrity versus Performance Tradeoff
Designing a Sample DatabaseMaintaining IntegrityAvoiding Data CorruptionSpeeding Data RetrievalsWorking with IndexesReading SQL Server Execution Plans
First Things First: Planning Your DatabaseBuilding TablesSetting ConstraintsKeys and IndexesEnsuring Data Validity with DomainsEstablishing Relationships between TablesAltering Table StructureDeleting Tables
Entering Data ValuesWorking with FunctionsUsing Expressions
Finding Needles in Haystacks with the SELECT StatementModifying ClausesTuning Queries
What Is a Subquery?What Subqueries DoUsing Subqueries in INSERT, DELETE, and UPDATE StatementsTuning Considerations for Statements Containing Nested QueriesTuning Correlated Subqueries
UNIONINTERSECTEXCEPTJOINSON versus WHEREJoin Conditions and Clustering Indexes
Declaring a CursorOpening a CursorOperating on a Single RowClosing a Cursor
What Could Possibly Go Wrong?Taking Advantage of RAIDBacking Up Your SystemCoping with Internet ThreatsInstalling Layers of Protection
Reducing Data-Entry ErrorsCoping with Errors in Database DesignHandling Programming ErrorsSolving Concurrent-Operation ConflictsPassing the ACID Test: Atomicity, Consistency, Isolation, and DurabilityOperating with TransactionsGetting Familiar with LockingTuning LocksEnforcing Serializability with TimestampsTuning the Recovery System
Working with the SQL Data Control LanguageIdentifying Authorized UsersClassifying UsersGranting PrivilegesRevoking PrivilegesGranting RolesRevoking Roles
Identifying Error ConditionsGetting to Know SQLSTATEHandling ConditionsDealing with Execution Exceptions: The WHENEVER ClauseGetting More Information: The Diagnostics AreaExamining an Example Constraint ViolationAdding Constraints to an Existing TableInterpreting SQLSTATE InformationHandling Exceptions
Microsoft AccessMicrosoft SQL ServerIBM Db2Oracle 18cSQL AnywherePostgreSQLMySQL
Building an Application with SQL and a Procedural Language
Comparing SQL with Procedural LanguagesDifficulties in Combining SQL with a Procedural LanguageEmbedding SQL in an ApplicationUsing SQL Modules with an Application
Understanding the Client’s ProblemApproaching the ProblemDetermining the DeliverablesBuilding an Entity-Relationship ModelTransforming the ModelCreating TablesChanging Table StructureRemoving TablesDesigning the User Interface
Designing from the Top DownCoding from the Bottom UpTesting, Testing, Testing
Embedding SQL Statements in Your CodeIntroducing Compound StatementsFollowing the Flow of Control StatementsUsing Stored ProceduresWorking with TriggersUsing Stored FunctionsPassing Out PrivilegesUsing Stored Modules
Native DriversODBC and Its Major ComponentsWhat Happens When the Application Makes a Request
Introducing XMLKnowing the Parts of an XML DocumentUsing XML SchemaRelating SQL to XMLUsing the XML Data TypeMapping SQL to XMLOperating on XML Data with SQL FunctionsWorking with XML Predicates
Inserting XML Data into an SQL PseudotableCreating a Table to Hold XML DataUpdating XML DocumentsDiscovering Oracle’s Tools for Updating XML Data in a TableIntroducing Microsoft’s Tools for Updating XML Data in a Table
XQueryFLWOR ExpressionsXQuery versus SQL
Using JSON with SQLThe SQL/JSON Data ModelSQL/JSON FunctionsSQL/JSON Path Language
Analyzing the WorkloadConsidering the Physical DesignChoosing the Right IndexesTuning IndexesTuning QueriesTuning TransactionsSeparating User Interactions from TransactionsMinimizing Traffic between Application and ServerPrecompiling Frequently Used Queries
Surviving Failures with Minimum Data LossTuning the Recovery SystemTuning the Operating SystemMaximizing the Hardware You HaveAdding HardwareWorking in Multiprocessor Environments
Pinpointing the ProblemDetermining the Possible Causes of TroubleImplementing General Principles: A First Step Toward Improving PerformanceTracking Down BottlenecksAnalyzing Query EfficiencyManaging Resources Wisely

Content preview from SQL All-In-One For Dummies, 3rd Edition

Index

Symbols and Numerics

% (percent sign) wildcard character, 247
* (asterisk) wildcard character, 89, 240
/* */ comment pairs, 121
; (semicolon) terminator directive, 451
_ (underscore) wildcard character, 247
|| (concatenation operator), 229–230
= (equals operator), 286
1:1 relationships. See one-to-one relationships
1:N relationships. See one-to-many relationships
1NF (First Normal Form), 116–117, 159, 161, 170, 467
2NF (Second Normal Form), 159–160, 471
3NF (Third Normal Form), 159, 161, 163
4NF (Fourth Normal Form), 159
5NF (Fifth Normal Form), 159

A

ABS (absolute value) function, 226
abstract data types (ADTs), 119
.accdb format, 433
access modes, 380
access plans, 668–670
- access path, 668–669
- filtering selectively, 669
- join types, 670
access privileges, 72–73, 401–412, 507
- authorized users, 402–403
- classifying users, 404
- DCL statements, 401–402
- granting, 97, 404–410
  - for adding data, 406
  - with authorization IDs, 72
  - for changing data, 406
  - for defining new data types, 409
  - for deleting data, 406
  - for executing SQL statements, 409
  - for full access, 409
  - for granting privileges, 409–410
  - for referencing data in other tables, 406–408
  - for responding to events, 408
  - with roles, 72–73
  - syntax for, 404–405
  - for using certain database facilities, 408
  - for viewing data, 405–406
- overview, 404–405
- revoking, 98, 410–411
- roles, 402–403
  - creating, 403
  - destroying, 403
  - granting, 411–412
  - revoking, 412
- user identifiers, 402
ACID test
- atomicity, 378
- consistency, 378
- durability, ...