A. Kriegel | Discovering SQL. A Hands-on Guide For Beginners / SQL. Руководство для начинающих [2011] [PDF] [En]

CHAPTER 1: DROWNING IN DATA, DYING OF THIRST FOR KNOWLEDGE 1
Data Deluge and Informational Overload 2
Database Management Systems (DBMSs) 2
Storage Capacity 2
Number of Users 2
Security 2
Performance 3
Scalability 3
Costs 3
Recording Data 3
Oral Records 3
Pictures 4
Written Records 4
Printed Word 4
All of the Above 4
Analog versus Digital Data 4
To Store or Not to Store? 5
Relational Database Management Systems 6
IBM DB2 LUW 6
Oracle 7
Microsoft SQL Server 7
Microsoft Access 7
PostgreSQL 8
MySQL 8
HSQLDB and OpenOffi ce BASE 9
What Is SQL? 9
The SQL Standard 10
Dialects of SQL 10
Not the Only Game in Town 11
Let There Be Database! 11
Creating a Table 13
Getting the Data In: INSERT Statement 14
Give Me the World: SELECT Statement 16xvi
Good Riddance: the DELETE Statement 22
I Can Fix That: the UPDATE Statement 25

CHAPTER 2: BREAKING AND ENTERING STRUCTURED INFORMATION 29
A Really Brief Introduction to Data Modeling 29
Conceptual Modeling 30
Logical Modeling 30
Physical Modeling 31
Why Can’t Everything Be Text? 31
Character Data 32
Fixed Length and Variable Strings 32
Binary Strings 34
Character versus Special Files 35
Numeric Data 36
Exact Numbers 36
Approximate Numbers 38
Literals for the Number 39
Once Upon a Time: Date and Time Data Types 40
Binary Data 42
It’s a Bird, It’s a Plane, It’s … a NULL! 43
Much Ado About Nothing 43
None of the Above: More Data Types 46
BOOLEAN 46
BIT 46
XML Data Type 46
DDL, DML, and DQL: Components of SQL 47
Refactoring Database TABLE 47
DROP TABLE 48
CREATE TABLE 48
ALTER TABLE 49
Populating a Table with Diff erent Data Types 52
Implicit and Explicit Data Conversion 53
SELECT Statement Revisited 55
Selecting Literals, Functions, and Calculated Columns 55
Setting Vertical Limits 56
Alias: What’s in a Name? 56
Setting Horizontal Limits 58
DISTINCT 58
Get Organized: Marching Orders 59
ORDER BY 59
ASC and DESC 60
TOP and LIMIT 60
INSERT, UPDATE, and DELETE Revisited 61
INSERT 61
SELECT INTO 63
UPDATE 63
DELETE 65
TRUNCATE That Table! 66
SQL Operators: Agents of Change 67
Arithmetic and String Concatenation Operators 67
Comparison Operators 68
Logical Operators 69
ALL 70
ANY | SOME 70
BETWEEN <EXPRESSION> AND <EXPRESSION> 70
IN 71
EXISTS 72
LIKE 72
AND 74
NOT 75
OR 75
Assignment Operator 76
Bitwise Operators 76
Operator Precedence 77

CHAPTER 3: A THING YOU CAN RELATE TO — DESIGNING A RELATIONAL DATABASE 79
Entities and Attributes Revisited 80
Keys to the Kingdom: Primary and Foreign 81
Relationship Patterns 83
Domain Integrity 87
Am I Normal? Basics of Relational Database Design 89
Specifying Constraints 92
Selecting a Flavor For Your Data Model 93
Data Warehouses and Data Marts 93
Star and Snowfl ake Schemas 94
What Could and Does Go Wrong 94
Working with Multiple Tables 95
JOIN Syntax 95
UNION Operator 96
Dynamic SQL 97
Ultimate Flexibility, Potential Problems 99

CHAPTER 4: OVERCOMING THE LIMITATIONS OF SQL 103
In Numbers, Strength 104
Building Character 107
“X” Marks the Spot: Finding the Position of a Character in a String 112
CHARINDEX 113
CHAR 113
SUBSTRING 114
LENGTH 114
TRIM, LTRIM, and RTRIM 116
Date and Time Functions 117
What Time Is It? 117
Date Arithmetic 118
A Glimpse of Aggregate Functions 121
Conversion Functions 123
Conversion Between Diff erent Data Types 125
Conversion Between Diff erent Character Sets 125
Miscellaneous Functions 126
Making the CASE 127
SQL Procedural Extensions 129
Happy Parsing: Stored Procedures 131
User-Defi ned Functions (UDFs) 132
Why Use Procedural Extensions? 134
Performance and Network Traffi c 134
Database Security 134
Code Reusability 135

CHAPTER 5: GROUPING AND AGGREGATION 137
Aggregate SQL Functions Revisited 137
AVG() 137
COUNT() 139
MAX() 140
MIN() 141
SUM() 142
Eliminating Duplicate Data 143
GROUP BY: Where Your Data Belongs 144
GROUP BY with HAVING Clause 148
ORDER BY Clause: Sorting Query Output 149

CHAPTER 6: WHEN ONE IS NOT ENOUGH A QUERY WITHIN A QUERY 155
What You Don’t Know Might Help You 155
Subquery in the WHERE Clause 155
EXISTS Operator 156
ANY Operator 157
ALL Operator 157
Subquery in the SELECT List 158
Subquery in the FROM Clause 160
Subquery in the HAVING Clause 161
Subqueries with INSERT 163
Subqueries with UPDATE 165
Subqueries with DELETE 166
Correlated Query 167
How Deep the Rabbit Hole Goes: Nesting Subqueries 169
A Subquery or a JOIN? 170

CHAPTER 7: YOU BROKE IT; YOU FIX IT: COMBINING DATA SETS 173
Joins Revisited 173
INNER JOIN 175
N-way INNER JOIN 179
LEFT OUTER JOIN 182
RIGHT OUTER JOIN 184
FULL JOIN 185
Self JOIN: Looking Inside for an Answer 186
CROSS JOIN (aka Cartesian Product) 187
State of the UNION 189
A Point of VIEW 193
CREATE VIEW 194
ALTER VIEW 198
DROP VIEW 198
Updatable VIEW 198
WITH CHECK OPTION 200
Hierarchical Views 201
But Wait; There’s More! 203
INTERSECT 203
EXCEPT and MINUS 204

CHAPTER 8: WHAT ELSE IS THERE, AND WHY? 207
An INDEX for All Seasons 207
UNIQUE Index 209
CLUSTERED Index 209
An INDEX Destroyed 211
TABLE Revisited 211
VIEW Revisited 214
By Any Other Name: Aliases and Synonyms 214
Auto-Incremented Values 216
Identity Columns 217
Microsoft SQL Server 218
IBM DB2 220
PostgreSQL 221
MySQL 221
Microsoft Access 222
OpenOffi ce BASE with HSQLDB 222
Who Am I: Finding One’s IDENTITY 223
Sequences 224
Comparing Identity Columns and Sequences 227
Triggers 228
One Happy Family: Working in Heterogeneous Environments 229

CHAPTER 9: OPTIMIZING PERFORMANCE 231
Database Performance 231
Performance Benchmarks 231
Order of Optimization 233
Hardware Optimization 234
Operating System Tune-up 234
Optimizing RDBMSs 234
Optimizing Database/Schema 234
Application Optimization 236
SQL Optimization 237
RDBMS-Optimization 243
Oracle 10/11g 244
IBM DB2 LUW 9.7 244
Microsoft SQL Server 2008 245
PostgreSQL 245
MySQL 246
Desktop RDBMSs 247
Microsoft Access 247
OpenOffi ce BASE with HSQLDB Backend 248
Your DBA Is Your Friend 249

CHAPTER 10: MULTIUSER ENVIRONMENT 251
Sessions 251
Orphaned Sessions 254
Transactions 254
Understanding Locks 262
SQL Security 264
Basic Security Mechanisms 265
Defi ning a Database User 266
Managing Security with Privileges 268
Operating System Security Integration 272
INFORMATION_SCHEMA and SQL System Catalogs 279
Oracle Data Dictionary 281
IBM DB2 LUW System Catalogs 282
Microsoft SQL Server 2008 System Catalog 283

CHAPTER 11: WORKING WITH UNSTRUCTURED AND SEMISTRUCTURED DATA 287
SQL and XML 287
A Brief Introduction to XML 289
Formatted XML 290
DTD and Schema 290
Document Type Defi nition (DTD) 291
XML Schema Defi nition (XSD) 291
Namespaces 292
XML as a DataSource 294
Accessing XML Documents in an Application 294
XML Path Language: XPath 294
XML Query Language: XQuery 294
Encoding XML 294
Presenting XML Documents 296
XSL and XSLT 296
XML and RDBMSs 296
Implementation Details 299
Oracle 11g XML DB 302
IBM DB 9.7 pureXML 307
Microsoft SQL Server 311
PostgreSQL 9.0 316
MySQL 5.5 317
XML for RDBMS: Best Practices 318
All Bits Considered 320
What Would Google Do? 320
Getting Binary Data In and Out of the RDBMS Table 323
Best Practices for Binary Data 325
SQL and Text Documents 326

CHAPTER 12: NOT BY SQL ALONE 329
The Future Is Cloudy 329
Key/Value Pair 331
What in the World Is Hadoop? 334
Google’s BigTable, Base, and Fusion Tables 334
Amazon SimpleDB 336
MongoDB 337
Microsoft SQL Azure 338
SQL and Business Intelligence 339
OLAP Rules 340
ROLAP, MOLAP, and HOLAP 341
Oracle 11g 342
IBM DB2 342
Microsoft SQL Server 343
XML for Analysis (XMLA) 344
Elementary, My Dear Watson! 344
Column-Oriented DBMS 345
Object Databases 346
Object-Oriented Programming (OOP) Paradigm 346
Objects and Classes 346Object-Relational Mapping Frameworks 349
Hibernate/NHibernate 350
Microsoft LINQ and Entity Framework 350

APPENDIX A: INSTALLING THE LIBRARY DATABASE 353
Oracle 10g XE 354
Installing Library Sample Database with SQL*Plus 354
Installing with Oracle Web Interface 356
IBM DB2 9.7 Express-C 360
IBM Command Editor 360
IBM Command Window 362
Microsoft SQL Server 2008 Express 363
SQL Server Management Studio Express 363
PostgreSQL 9.0 365
Installing with pgAdmin III 366
MySQL 5.1 369
Installing with the MySQL CommandA-Line Utility 370
Microsoft Access 2007/2010 371
OpenOffice BASE 3.2 372

APPENDIX B: INSTALLING RDBMSS SOFTWARE 375

APPENDIX C: ACCESSING RDBMSS 377
Oracle 377
IBM DB2 377
Microsoft SQL Server 2008 377
MySQL 378
PostgreSQL 378
Microsoft Access 2007/2010 378
Open Offi ce BASE with HSQLDB 378

APPENDIX D: ACCESSING RDBMSS WITH THE SQUIRREL UNIVERSAL SQL CLIENT 379