SQL All-in-One For Dummies. 4 Ed

Title Page....3

Copyright Page....4

Table of Contents....7

Introduction....25

About This Book....25

Foolish Assumptions....26

Icons Used in This Book....27

Beyond the Book....27

Where to Go from Here....28

Book 1 Getting Started with SQL....29

Chapter 1 Understanding Relational Databases....31

Understanding Why Today’s Databases Are Better than Early Databases....32

Irreducible complexity....32

Managing data with complicated programs....32

Managing data with simple programs....35

Which type of organization is better?....35

Databases, Queries, and Database Applications....36

Making data useful....36

Retrieving the data you want — and only the data you want....36

Examining Competing Database Models....38

Looking at the historical background of the competing models....38

The hierarchical database model....39

The network database model....43

The relational database model....45

Defining what makes a database relational....45

Protecting the definition of relational databases with Codd’s rules....47

Highlighting the relational database model’s inherent flexibility....48

The object-oriented database model....49

The object-relational database model....49

The nonrelational NoSQL model....49

Why the Relational Model Won....50

Chapter 2 Modeling a System....53

Capturing the Users’ Data Model....53

Identifying and interviewing stakeholders....54

Reconciling conflicting requirements....55

Obtaining stakeholder buy-in....55

Translating the Users’ Data Model to a Formal Entity-Relationship Model....56

Entity-Relationship modeling techniques....57

Entities....57

Attributes....58

Identifiers....58

Relationships....59

Drawing Entity-Relationship diagrams....62

Maximum cardinality....63

Minimum cardinality....63

Understanding advanced ER model concepts....65

Strong entities and weak entities....65

ID-dependent entities....67

Supertype and subtype entities....67

Incorporating business rules....69

A simple example of an ER model....69

A slightly more complex example....71

Problems with complex relationships....74

Simplifying relationships using normalization....75

Translating an ER model into a relational model....75

Chapter 3 Getting to Know SQL....77

Where SQL Came From....77

Knowing What SQL Does....78

The ISO/IEC SQL Standard....79

Knowing What SQL Does Not Do....79

Choosing and Using an Available DBMS Implementation....80

Microsoft Access....81

Microsoft SQL Server....85

IBM DB2....85

Oracle Database....86

Sybase SQL Anywhere....86

MySQL....86

PostgreSQL....87

Chapter 4 SQL and the Relational Model....89

Sets, Relations, Multisets, and Tables....90

Functional Dependencies....91

Keys....92

Views....93

Users....94

Privileges....94

Schemas....95

Catalogs....96

Connections, Sessions, and Transactions....96

Routines....97

Paths....97

Chapter 5 Knowing the Major Components of SQL....99

Creating a Database with the Data Definition Language....99

The containment hierarchy....100

Creating tables....101

Specifying columns....101

Creating other objects....102

Views....102

Schemas....107

Domains....108

Modifying tables....109

Removing tables and other objects....109

Operating on Data with the Data Manipulation Language (DML)....110

Retrieving data from a database....110

Adding data to a table....111

Adding data the dull and boring way (typing it in)....112

Adding incomplete records....113

Adding data in the fastest and most efficient way: Bypassing typing altogether....113

Updating data in a table....114

Deleting data from a table....117

Updating views doesn’t make sense....118

Maintaining Security in the Data Control Language (DCL)....119

Granting access privileges....119

Revoking access privileges....120

Preserving database integrity with transactions....120

Chapter 6 Drilling Down to the SQL Nitty-Gritty....123

Executing SQL Statements....123

Interactive SQL....124

Challenges to combining SQL with a host language....125

Embedded SQL....125

Module language....128

Using Reserved Words Correctly....129

SQL’s Data Types....129

Exact numerics....130

INTEGER....131

SMALLINT....131

BIGINT....131

NUMERIC....132

DECIMAL....132

DECFLOAT....132

Approximate numerics....132

REAL....133

DOUBLE PRECISION....133

FLOAT....134

Character strings....134

CHARACTER....134

CHARACTER VARYING....135

CHARACTER LARGE OBJECT (CLOB)....135

NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT....136

Binary strings....136

BINARY....136

BINARY VARYING....137

BINARY LARGE OBJECT (BLOB)....137

Booleans....137

Datetimes....137

DATE....137

TIME WITHOUT TIME ZONE....138

TIME WITH TIME ZONE....138

TIMESTAMP WITHOUT TIME ZONE....139

TIMESTAMP WITH TIME ZONE....139

Intervals....139

XML type....140

ROW type....140

Collection types....142

ARRAY....142

Multiset....143

REF types....143

JSON types....143

User-defined types....144

Distinct types....144

Structured types....145

Data type summary....148

Handling Null Values....149

Applying Constraints....150

Column constraints....150

NOT NULL....150

UNIQUE....151

CHECK....151

Table constraints....152

Foreign key constraints....153

Assertions....155

Book 2 Developing Relational Databases....157

Chapter 1 System Development Overview....159

The Components of a Database System....160

The database....160

The database engine....161

The DBMS front end....161

The database application....161

The user....162

The System Development Life Cycle....162

Definition phase....163

Requirements phase....164

The users’ data model....165

Statement of Requirements....165

Evaluation phase....166

Determining project scope....167

Reassessing feasibility....168

Documenting the Evaluation phase....168

Design phase....168

Designing the database....169

The database application....170

Documenting the Design phase....170

Implementation phase....171

Final Documentation and Testing phase....172

Testing the system with sample data....172

Finalizing the documentation....173

Delivering the results (and celebrating)....173

Maintenance phase....174

Chapter 2 Building a Database Model....175

Finding and Listening to Interested Parties....176

Your immediate supervisor....176

The users....177

The standards organization....177

Upper management....178

Building Consensus....178

Gauging what people want....180

Arriving at a consensus....180

Building a Relational Model....180

Reviewing the three database traditions....181

Knowing what a relation is....182

Functional dependencies....182

Keys....183

Being Aware of the Danger of Anomalies....184

Eliminating anomalies....185

Examining the higher normal forms....188

The Database Integrity versus Performance Tradeoff....190

Chapter 3 Balancing Performance and Correctness....193

Designing a Sample Database....194

The ER model for Honest Abe’s....194

Converting an ER model into a relational model....196

Normalizing a relational model....196

Handling binary relationships....198

A sample conversion....203

Maintaining Integrity....206

Entity integrity....206

Domain integrity....207

Referential integrity....208

Avoiding Data Corruption....210

Speeding Data Retrievals....211

Hierarchical storage....212

Full table scans....213

Working with Indexes....213

Creating the right indexes....214

Indexes and the ANSI/ISO standard....214

Index costs....215

Query type dictates the best index....215

Point query....215

Multipoint query....215

Range query....216

Prefix match query....216

Extremal query....216

Ordering query....216

Grouping query....217

Equi-join query....217

Data structures used for indexes....217

Indexes, sparse and dense....218

Index clustering....219

Composite indexes....219

Index effect on join performance....220

Table size as an indexing consideration....220

Indexes versus full table scans....221

Reading SQL Server Execution Plans....221

Robust execution plans....221

A sample database....222

A typical query....223

The execution plan....224

Chapter 4 Creating a Database with SQL....227

First Things First: Planning Your Database....227

Building Tables....228

Locating table rows with keys....228

Using the CREATE TABLE statement....231

Setting Constraints....233

Column constraints....233

Table constraints....234

Keys and Indexes....234

Ensuring Data Validity with Domains....234

Establishing Relationships between Tables....235

Altering Table Structure....239

Deleting Tables....239

Book 3 Writing SQL Queries....241

Chapter 1 Values, Variables, Functions, and Expressions....243

Entering Data Values....243

Row values have multiple parts....244

Identifying values in a column....244

Literal values don’t change....244

Variables vary....246

Special variables hold specific values....246

Working with Functions....247

Summarizing data with set functions....247

COUNT....248

AVG....249

MAX....249

MIN....249

SUM....250

LISTAGG....250

ANY_VALUE....250

Dissecting data with value functions....251

String value functions....251

Numeric value functions....255

Datetime value functions....260

Polymorphic table functions....261

Using Expressions....261

Numeric value expressions....261

String value expressions....262

Datetime value expressions....262

Interval value expressions....263

Boolean value expressions....264

Array value expressions....265

Conditional value expressions....265

Handling different cases....265

The NULLIF special CASE....267

Bypassing null values with COALESCE....268

Converting data types with a CAST expression....268

Casting one SQL data type to another....269

Using CAST to overcome data type incompatibilities between SQL and its host language....269

Row value expressions....270

Chapter 2 SELECT Statements and Modifying Clauses....273

Finding Needles in Haystacks withthe SELECT Statement....273

Modifying Clauses....274

FROM clauses....274

WHERE clauses....275

Comparison predicates....277

BETWEEN....278

IN and NOT IN....279

LIKE and NOT LIKE....281

NULL....282

ALL, SOME, and ANY....283

EXISTS....286

UNIQUE....287

DISTINCT....287

OVERLAPS....288

MATCH....288

The MATCH predicate and referential integrity....290

Logical connectives....292

GROUP BY clauses....294

HAVING clauses....297

ORDER BY clauses....298

Tuning Queries....300

SELECT DISTINCT....301

Temporary tables....304

The ORDER BY clause....309

The HAVING clause....313

The OR logical connective....317

Chapter 3 Querying Multiple Tables with Subqueries....319

What Is a Subquery?....319

What Subqueries Do....320

Subqueries that return multiple values....320

Subqueries that retrieve rows satisfying a condition....320

Subqueries that retrieve rows that don’t satisfy a condition....321

Subqueries that return a single value....322

Quantified subqueries return a single value....325

Correlated subqueries....328

Using a subquery as an existence test....328

Introducing a correlated subquery with the IN keyword....329

Introducing a correlated subquery with a comparison operator....330

Correlated subqueries in a HAVING clause....332

Using Subqueries in INSERT, DELETE, and UPDATE Statements....333

Tuning Considerations for Statements Containing Nested Queries....336

Tuning Correlated Subqueries....342

Chapter 4 Querying Multiple Tables with Relational Operators....347

UNION....348

UNION ALL....350

UNION CORRESPONDING....350

INTERSECT....351

EXCEPT....353

JOINS....354

Cartesian product or cross join....354

Equi-join....356

Natural join....358

Condition join....359

Column-name join....359

Inner join....361

Outer join....361

Left outer join....361

Right outer join....364

Full outer join....364

ON versus WHERE....365

Join Conditions and Clustering Indexes....366

Chapter 5 Cursors....369

Declaring a Cursor....370

The query expression....371

Ordering the query result set....371

Updating table rows....373

Sensitive versus insensitive cursors....373

Scrolling a cursor....375

Holding a cursor....375

Declaring a result set cursor....375

Opening a Cursor....376

Operating on a Single Row....377

FETCH syntax....378

Absolute versus relative fetches....379

Deleting a row....379

Updating a row....379

Closing a Cursor....380

Book 4 Securing Your Data....381

Chapter 1 Protecting Against Hardware Failure and External Threats....383

What Could Possibly Go Wrong?....384

Equipment failure....384

Platform instability....386

Database design flaws....387

Data-entry errors....387

Operator error....387

Taking Advantage of RAID....387

Striping....388

RAID levels....390

RAID 0....390

RAID 1....391

RAID 5....391

RAID 10....391

Backing Up Your System....392

Preparation for the worst....392

Full or incremental backup....393

Frequency....393

Backup maintenance....393

Coping with Internet Threats....394

Viruses....394

Trojan horses....396

Worms....397

Denial-of-service attacks....398

Ransomware....398

SQL injection attacks....398

Chipping away at your wall of protection....398

Understanding SQL injection....399

Using a GET parameter....399

Recognizing unsafe configurations....407

Finding vulnerabilities on your site....407

Phishing scams....411

Zombie spambots....411

Installing Layers of Protection....412

Network-layer firewalls....412

Application-layer firewalls....412

Antivirus software....412

Vulnerabilities, exploits, and patches....412

Education....413

Alertness....413

Chapter 2 Protecting Against User Errors and Conflicts....415

Reducing Data-Entry Errors....416

Data types: The first line of defense....416

Constraints: The second line of defense....416

Sharp-eyed humans: The third line of defense....417

Coping with Errors in Database Design....417

Handling Programming Errors....418

Solving Concurrent-Operation Conflicts....418

Passing the ACID Test: Atomicity, Consistency, Isolation, and Durability....420

Operating with Transactions....421

Using the SET TRANSACTION statement....421

Starting a transaction....422

Access modes....422

Isolation levels....422

Committing a transaction....425

Rolling back a transaction....425

Why roll back a transaction?....426

The log file....426

The write-ahead log protocol....427

Checkpoints....428

Implementing deferrable constraints....428

Getting Familiar with Locking....433

Two-phase locking....433

Granularity....434

Deadlock....434

Tuning Locks....435

Measuring performance with throughput....436

Eliminating unneeded locks....436

Shortening transactions....436

Weakening isolation levels (ver-r-ry carefully)....437

Controlling lock granularity....438

Scheduling DDL statements correctly....438

Partitioning insertions....438

Cooling hot spots....439

Tuning the deadlock interval....439

Enforcing Serializability with Timestamps....439

Tuning the Recovery System....442

Chapter 3 Assigning Access Privileges....443

Working with the SQL Data Control Language....443

Identifying Authorized Users....444

Understanding user identifiers....444

Getting familiar with roles....444

Creating roles....445

Destroying roles....445

Classifying Users....446

Granting Privileges....446

Looking at data....447

Deleting data....448

Adding data....448

Changing data....448

Referencing data in another table....449

Using certain database facilities....450

Responding to an event....451

Defining new data types....451

Executing an SQL statement....451

Doing it all....451

Passing on the power....452

Revoking Privileges....452

Granting Roles....454

Revoking Roles....454

Chapter 4 Error Handling....455

Identifying Error Conditions....456

Getting to Know SQLSTATE....456

Handling Conditions....458

Handler declarations....459

Handler actions and handler effects....459

Conditions that aren’t handled....461

Dealing with Execution Exceptions: The WHENEVER Clause....461

Getting More Information: The Diagnostics Area....462

The diagnostics header area....463

The diagnostics detail area....464

Examining an Example Constraint Violation....466

Adding Constraints to an Existing Table....468

Interpreting SQLSTATE Information....468

Handling Exceptions....469

Book 5 Programming with SQL....471

Chapter 1 Database Development Environments....473

Microsoft Access....473

The Jet engine....474

DAO....474

ADO....474

ODBC....475

OLE DB....475

Files with the .mdb extension....475

The Access Database Engine....475

Microsoft SQL Server....475

IBM Db2....476

Oracle 23c....477

SQL Anywhere....477

PostgreSQL....477

MySQL....478

Chapter 2 Interfacing SQL to a Procedural Language....479

Building an Application with SQL and a Procedural Language....479

Access and VBA....480

The ADOdb library....480

The ADOX library....481

Other libraries....481

SQL Server and the .NET languages....481

MySQL and C++.NET or C#....482

MySQL and C....482

MySQL and Perl....483

MySQL and Python....483

MySQL and PHP....483

MySQL and Java....483

Oracle SQL and Java....483

Db2 and Java....484

Chapter 3 Using SQL in an Application Program....485

Comparing SQL with Procedural Languages....486

Classic procedural languages....486

Object-oriented procedural languages....487

Nonprocedural languages....487

Difficulties in Combining SQL with a Procedural Language....488

Challenges of using SQL with a classical procedural language....488

Contrasting operating modes....489

Data type incompatibilities....489

Challenges of using SQL with an object-oriented procedural language....489

Embedding SQL in an Application....490

Embedding SQL in an Oracle Pro*C application....491

Declaring host variables....493

Converting data types....493

Embedding SQL in a Java application....494

Using SQL in a Perl application....494

Embedding SQL in a PHP application....495

Using SQL with a Visual Basic .NET application....495

Using SQL with other .NET languages....496

Using SQL Modules with an Application....496

Module declarations....497

Module procedures....498

Modules in Oracle....498

Chapter 4 Designing a Sample Application....501

Understanding the Client’s Problem....502

Approaching the Problem....502

Interviewing the stakeholders....502

Drafting a detailed statement of requirements....503

Following up with a proposal....503

Determining the Deliverables....504

Finding out what’s needed now and later....505

Planning for organization growth....505

Greater database needs....505

Increased need for data security....506

Growth in the example scenario....506

Nailing down project scope....506

Building an Entity-Relationship Model....507

Determining what the entities are....508

Relating the entities to one another....508

Relationships....509

Maximum cardinality....509

Minimum cardinality....509

Business rules....510

Transforming the Model....511

Eliminating any many-to-many relationships....511

Normalizing the ER model....514

Creating Tables....515

Changing Table Structure....519

Removing Tables....519

Designing the User Interface....520

Chapter 5 Building an Application....521

Designing from the Top Down....521

Determining what the application should include....522

Designing the user interface....522

Connecting the user interface to the database....523

Coding from the Bottom Up....525

Preparing to build the application....525

Creating the database....526

Filling database tables with sample data....527

Creating the application’s building blocks....533

Developing screen forms....533

Developing reports....533

Gluing everything together....534

Testing, Testing, Testing....534

Fixing the bugs....535

Turning naive users loose....535

Bringing on the hackers....535

Fixing the newly found bugs....535

Retesting everything one last time....536

Chapter 6 Understanding SQL’s Procedural Capabilities....537

Embedding SQL Statements in Your Code....538

Introducing Compound Statements....538

Atomicity....539

Variables....540

Cursors....540

Assignment....541

Following the Flow of Control Statements....541

IF . . . THEN . . . ELSE . . . END IF....542

CASE . . . END CASE....542

Simple CASE statement....542

Searched CASE statement....543

LOOP . . . END LOOP....544

LEAVE....544

WHILE . . . DO . . . END WHILE....545

REPEAT . . . UNTIL . . . END REPEAT....545

FOR . . . DO . . . END FOR....546

ITERATE....546

Using Stored Procedures....547

Working with Triggers....548

Trigger events....549

Trigger action time....550

Triggered actions....550

Triggered SQL statement....550

Using Stored Functions....551

Passing Out Privileges....552

Using Stored Modules....552

Chapter 7 Connecting SQL to a Remote Database....555

Native Drivers....556

ODBC and Its Major Components....557

Application....558

Driver manager....559

Drivers....559

Data sources....561

What Happens When the Application Makes a Request....561

Using handles to identify objects....562

Following the six stages of an ODBC operation....562

Stage 1: The application allocates environment and connection handles in the driver manager....563

Stage 2: The driver manager finds the appropriate driver....564

Stage 3: The driver manager loads the driver....564

Stage 4: The driver manager allocates environment and connection handles in the driver....564

Stage 5: The driver manager connects to the data source through the driver....564

Stage 6: The data source (finally) executes an SQL statement....565

Book 6 Working with Advanced Data Types in SQL: XML, JSON, and PGQ....569

Chapter 1 Using XML with SQL....571

Introducing XML....572

Knowing the Parts of an XML Document....573

XML declaration....573

Elements....574

Nested elements....574

The document element....574

Empty elements....574

Attributes....575

Entity references....576

Numeric character references....577

Using XML Schema....577

Relating SQL to XML....578

Using the XML Data Type....579

When to use the XML type....579

When not to use the XML type....580

Mapping SQL to XML....581

Mapping character sets to XML....581

Mapping identifiers to XML....581

Mapping data types to XML....582

Mapping nonpredefined data types to XML....583

DOMAIN....583

DISTINCT UDT....584

ROW....585

ARRAY....586

MULTISET....587

Mapping tables to XML....588

Handling null values....588

Creating an XML schema for an SQL table....589

Operating on XML Data with SQL Functions....590

XMLELEMENT....591

XMLFOREST....591

XMLCONCAT....592

XMLAGG....592

XMLCOMMENT....593

XMLPARSE....593

XMLPI....594

XMLQUERY....594

XMLCAST....595

Working with XML Predicates....595

DOCUMENT....595

CONTENT....596

XMLEXISTS....596

VALID....596

Chapter 2 Storing XML Data in SQL Tables....599

Inserting XML Data into an SQL Pseudotable....599

Creating a Table to Hold XML Data....601

Updating XML Documents....602

Discovering Oracle’s Tools for Updating XML Data in a Table....603

APPENDCHILDXML....603

INSERTCHILDXML....604

INSERTXMLBEFORE....605

DELETEXML....606

UPDATEXML....607

Introducing Microsoft’s Tools for Updating XML Data in a Table....608

Inserting data into a table using OPENXML....608

Using updategrams to map data into database tables....609

Using an updategram namespace and keywords....609

Specifying a mapping schema....611

Implicit mapping....611

Explicit mapping....613

Elementcentric mapping....618

Attributecentric mapping....619

Mixed elementcentric and attributecentric mapping....619

Schemas that allow null values....620

Chapter 3 Retrieving Data from XML Documents....623

XQuery....624

Where XQuery came from....624

What XQuery requires....625

XQuery functionality....625

Usage scenarios....626

FLWOR Expressions....631

The for clause....632

The let clause....633

The where clause....634

The order by clause....635

The return clause....635

XQuery versus SQL....636

Comparing XQuery’s FLWOR expression with SQL’s SELECT expression....637

Relating XQuery data types to SQL data types....637

Chapter 4 Using JSON with SQL....641

Using JSON with SQL....641

The SQL/JSON Data Model....642

SQL/JSON items....642

SQL/JSON scalar....643

SQL/JSON null....643

SQL/JSON array....643

SQL/JSON object....643

SQL/JSON sequences....643

Parsing JSON....644

Serializing JSON....644

SQL/JSON Functions....644

Query functions....644

JSON value expression....645

Path expression....645

Passing clause....645

JSON output clause....646

JSON_EXISTS....646

JSON_VALUE....647

JSON_QUERY....648

JSON_TABLE....649

Constructor functions....650

JSON_OBJECT....650

JSON_OBJECTAGG....651

JSON_ARRAY....651

JSON_ARRAYAGG....652

IS JSON predicate....652

JSON nulls and SQL nulls....653

SQL/JSON Path Language....653

SQL:2023 JSON Enhancements....653

The JSON data type....654

Additional functions for the JSON data type....655

Chapter 5 Exploring Property Graph Queries....657

What Are Property Graph Queries?....657

Looking at node and edge properties....658

Connecting nodes by multiple edges....658

Using edges to connect a node to itself....659

Following paths with SQL queries....659

Examining SQL/PGQ....659

Working with SQL/PGQ....661

Building the property graph tables....661

Adding data to node and edge tables....664

Querying data in graph tables....666

Book 7 Optimizing Your Database....669

Chapter 1 Tuning the Database....671

Analyzing the Workload....672

Considering the Physical Design....673

Choosing the Right Indexes....674

Avoiding unnecessary indexes....674

Choosing a column to index....675

Using multicolumn indexes....676

Clustering indexes....676

Choosing an index type....678

Weighing the cost of index maintenance....678

Using composite indexes....678

Tuning Indexes....679

Tuning Queries....680

Tuning Transactions....681

Separating User Interactions from Transactions....681

Minimizing Traffic between Application and Server....682

Precompiling Frequently Used Queries....682

Chapter 2 Tuning the Environment....683

Surviving Failures with Minimum Data Loss....684

What happens to transactions when no failure occurs?....684

What happens when a failure occurs and a transaction is still active?....685

Tuning the Recovery System....685

Volatile and nonvolatile memory....686

Memory system hierarchy....687

Putting logs and transactions on different disks....688

Hard disk drive construction....689

Hard disk drive performance considerations....689

Tuning write operations....691

Performing database dumps....692

Setting checkpoints....693

Optimizing batch transactions....694

Tuning the Operating System....694

Scheduling threads....694

Context switching....696

Round-robin scheduling....696

Priority-based scheduling....696

Priority inversion....697

Deadlock....698

Determining database buffer size....698

Tuning the page usage factor....699

Maximizing the Hardware You Have....699

Optimizing the placement of code and data on hard disks....700

Tuning the page replacement algorithm....700

Tuning the disk controller cache....701

Adding Hardware....701

Faster processor....702

More RAM....702

Faster hard disks....702

More hard disks....703

Solid State Disk (SSD)....703

RAID arrays....703

Working in Multiprocessor Environments....704

Chapter 3 Finding and Eliminating Performance Bottlenecks....705

Pinpointing the Problem....706

Slow query....706

Slow update....706

Determining the Possible Causes of Trouble....707

Problems with indexes....707

B+ tree indexes....707

Index pluses and minuses....707

Index-only queries....708

Full table scans versus indexed table access....708

Pitfalls in communication....709

ODBC/JDBC versus native drivers....709

Locking and client performance....710

Application development tools making suboptimal decisions....710

Determining whether hardware is robust enough and configured properly....711

Implementing General Principles: A First Step Toward Improving Performance....711

Avoid direct user interaction....711

Examine the application/database interaction....712

Don’t ask for columns that you don’t need....712

Don’t use cursors unless you absolutely have to....713

Precompiled queries....713

Tracking Down Bottlenecks....713

Isolating performance problems....714

Performing a top-down analysis....714

DBMS operations....714

Hardware....715

Partitioning....716

Locating hotspots....717

Analyzing Query Efficiency....717

Using query analyzers....717

The Database Engine Tuning Advisor....721

SQL Server Profiler....723

The Oracle Tuning Advisor....725

Finding problem queries....725

Analyzing a query’s access plan....726

Examining a query’s execution profile....728

Managing Resources Wisely....729

The disk subsystem....729

The database buffer manager....730

The logging subsystem....731

The locking subsystem....731

Book 8 Appendixes....733

Appendix A SQL:2023 Reserved Words....735

Appendix B Glossary....745

Index....755

EULA....803

SQL All-in-One For Dummies has everything you need to get started with the SQL programming language, and then to level up your skill with advanced applications. This relational database coding language is one of the most used languages in professional software development. And, as it becomes ever more important to take control of data, there’s no end in sight to the need for SQL know-how. You can take your career to the next level with this guide to creating databases, accessing and editing data, protecting data from corruption, and integrating SQL with other languages in a programming environment. Become a SQL guru and turn the page on the next chapter of your coding career.

• Get 7 mini-books in one, covering basic SQL, database development, and advanced SQL concepts
• Read clear explanations of SQL code and learn to write complex queries
• Discover how to apply SQL in real-world situations to gain control over large datasets
• Enjoy a thorough reference to common tasks and issues in SQL development

This Dummies All-in-One guide is for all SQL users―from beginners to more experienced programmers. Find the info and the examples you need to reach the next stage in your SQL journey.