SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK DISCOVERING SQL
SUMMARY
-
Items Found: 632
- 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 16
- 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
AND 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
- Benefi ts and Drawbacks 202
- 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-Specifi c 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 346
- Object-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
- OpenOffi ce 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
- 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 16
- 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
AND 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
- Benefi ts and Drawbacks 202
- 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-Specifi c 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 346
- Object-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
- OpenOffi ce 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
