SQL for the Real World
Don’t just learn “generic” SQL. Learn SQL to get results with the world’s top database platforms–Oracle for the enterprise and Microsoft Access for the desktop. Based on John Patrick’s hands-on SQL course at the University of California, Berkeley Extension, this book shows exactly how to retrieve the data you want, when you need it, in any application–from ad hoc reports to the data warehouse. Thoroughly updated for the newest versions of Oracle, Access, and the SQL standard, this book contains more exercises, techniques, and solutions than ever before. You’ll learn exactly how to write SQL queries that are easy to understand, verify, modify, and extend–even if you’ve never worked with databases before.
SQL Fundamentals,Third Edition, teaches you how to
- Build simple statements to retrieve, store, or modify data
- Craft complex queries that draw information from multiple tables
- Sort and summarize your data just the way you want it
- Create and edit your own tables
- Protect the integrity of your data
- Create more efficient, high-performance queries
- Work with unions, subqueries, self joins, cross joins, inner joins, and outer joins
- Use the Oracle Data Dictionary
About the Web Site
The accompanying Web site, https://www.box.com/shared/ylbckg2fn0 , contains all the SQL code and tables from the book, including the full databases for several versions of Access and code for building the corresponding Oracle databases. It also provides solutions to many of the book’s problems and an open area for discussions with the author and other readers.
Table of Contents:
Preface xxv
Chapter 1: Storing Information in Tables 1
Introduction 3
The Parts of a Table 9
Examples of Tables 19
Key Points 30
Chapter 2: Getting Information from a Table 31
The Select Statement 33
The Select Clause 37
The Where Clause 50
The Order By Clause 71
Key Points 80
Chapter 3: Compound Conditions in the Where Clause 83
Compound Conditions in the Where Clause 85
Constant Values 95
Punctuation Matters 102
Case Sensitivity 113
Three-Valued Logic 120
Error Messages 122
Some Exercises Solved for You 124
Key Points 131
Chapter 4: Saving Your Results 133
Saving Your Results in a New Table or View 135
Modifying the Data in a Table with SQL 151
Modifying the Data in a Table with the GUI 161
Restrictions on Modifying the Data in a Table 167
Key Points 170
Chapter 5: The Data Dictionary and Other Oracle Topics 171
Commit, Rollback, and Transactions 173
Modifying Data through a View 179
The SQL Commands Page in Oracle 192
Using the Oracle Data Dictionary – Part 1 195
Key Points 207
Chapter 6: Creating Your Own Tables 209
Creating Tables 211
Changing Tables 226
Tables with Duplicate Rows 236
Key Points 243
Chapter 7: Formats, Sequences, and Indexes 245
Formats 247
Sequences 257
Indexes 262
Using the Oracle Data Dictionary – Part 2 266
An Exercise Solved for You 278
Key Points 280
Chapter 8: Data Integrity 281
Constraints on One Table 283
Referential Integrity 289
The Delete Options and Update Options of RI 303
Variations of Referential Integrity 311
How to Code Constraints in a Create Table Statement 316
Key Points 319
Chapter 9: Row Functions 321
Introduction to Row Functions 323
Numeric Functions 334
Text Functions 340
Date Functions 350
Key Points 359
Chapter 10: Using Row Functions 361
Specialized Row Functions 363
Using the Documentation of Row Functions 372
Creating Patterns of Numbers and Dates 376
Key Points 397
Chapter 11: Summarizing Data 399
Introduction to the Column Functions 401
Maximum and Minimum 404
Count 411
Sum and Average 420
Other Topics 428
Key Points 434
Chapter 12: Controlling the Level of Summarization 435
Dividing a Table into Groups of Rows 437
Eliminating Some of the Summarized Data 459
Key Points 471
Chapter 13: Inner Joins 473
Introduction to Joins 475
Inner Joins of Two Tables 479
Variations of the Join Condition 495
Applications of Joins 504
Key Points 515
Chapter 14: Outer Joins 517
Introduction to Outer Joins 519
Applications of Outer Joins 534
Key Points 553
Chapter 15: Union and Union All 555
Union Basics 557
Unconventional Unions 573
Applications of a Union 577
Set Intersection and Set Difference in Oracle 590
Key Points 595
Chapter 16: Cross Joins, Self Joins, and CrossTab Queries 597
Cross Joins 599
Self Joins 613
CrossTab Queries in Access 624
CrossTab Queries in Oracle 645
Key Points 650
Chapter 17: Combining Tables in a Production Database 653
Methods of Joining Three or More Tables 655
Losing Information 660
Caring about the Efficiency of Your Computer 663
Standardizing the Way That Tables Are Joined 666
Key Points 671
Chapter 18: If-Then-Else, Parameter Queries, and Subqueries 673
If-Then-Else Logic 675
Parameter Queries 689
Subqueries 700
Applications of Subqueries 710
Older Features of Subqueries 714
Key Points 719
Chapter 19: The Multiuser Environment 721
Database Configurations 723
Operating in a Multiuser Environment 727
Security and Privileges 732
The Oracle Data Dictionary and the Multiuser Environment 736
Key Points 738
Chapter 20: The Design of SQL 739
Original SQL Design Objectives 741
Newer Interfaces 743
Typical Applications 748
Key Points 749
Appendix A: Oracle Is Free: How to Get Your Copy 751
Getting Current Information 752
Which Version of Oracle Should You Get? 752
System Requirements 753
Downloading Oracle from the Internet 753
Installing Oracle 754
Setup to Run the Examples in This Book 754
How to Stop Running Oracle 763
What to Do if Oracle Slows Down Your Computer 763
Appendix B: Quick Start with Oracle 765
Log in to Your Computer 766
Go to the Database Home Page 766
Log in to the Oracle Database 768
Go to the SQL Commands Page 768
Enter and Run an SQL Query 769
Optional: Print Your Query and the Results 770
Appendix C: Quick Start with Access 771
You May Use Access 2007, 2003, 2002, or 2000 772
How to Start Access 772
Entering an SQL Query 774
Dealing with Errors in Access 776
Printing from Access 777
Using the Access Trust Center 778
Appendix D: Diagram of the Lunches Database 783
Join Conditions 784
Data Validation Rules 785
Index 787
