Summary

Third edition of the hands-on, real-world guide to mastering SQL through using the world's top two databases: Oracle and Microsoft Access.

Author Biography

John J. Patrick is a computer consultant and software developer specializing in relational database technology. He teaches SQL Fundamentals and other database courses at the University of California, Berkeley Extension, and has more than twenty years of experience in software development at Bank of America, Chevron, Kaiser Permanente, and other leading firms.

Preface	p. xxv
Storing Information in Tables	p. 1
Introduction	p. 3
The Parts of a Table	p. 9
Examples of Tables	p. 19
Key Points	p. 30
Getting Information from a Table	p. 31
The Select Statement	p. 33
The Select Clause	p. 37
The Where Clause	p. 50
The Order By Clause	p. 71
Key Points	p. 80
Compound Conditions in the Where Clause	p. 83
Compound Conditions in the Where Clause	p. 85
Constant Values	p. 95
Punctuation Matters	p. 102
Case Sensitivity	p. 113
Three-Valued Logic	p. 120
Error Messages	p. 122
Some Exercises Solved for You	p. 124
Key Points	p. 131
Saving Your Results	p. 133
Saving Your Results in a New Table or View	p. 135
Modifying the Data in a Table with SQL	p. 151
Modifying the Data in a Table with the GUI	p. 161
Restrictions on Modifying the Data in a Table	p. 167
Key Points	p. 170
The Data Dictionary and Other Oracle Topics	p. 171
Commit, Rollback, and Transactions	p. 173
Modifying Data through a View	p. 179
The SQL Commands Page in Oracle	p. 192
Using the Oracle Data Dictionary
	p. 195
Key Points	p. 207
Creating Your Own Tables	p. 209
Creating Tables	p. 211
Changing Tables	p. 226
Tables with Duplicate Rows	p. 236
Key Points	p. 243
Formats, Sequences, and Indexes	p. 245
Formats	p. 247
Sequences	p. 257
Indexes	p. 262
Using the Oracle Data Dictionary
	p. 266
An Exercise Solved for You	p. 278
Key Points	p. 280
Data Integrity	p. 281
Constraints on One Table	p. 283
Referential Integrity	p. 289
The Delete Options and Update Options of RI	p. 303
Variations of Referential Integrity	p. 311
How to Code Constraints in a Create Table Statement	p. 316
Key Points	p. 319
Row Functions	p. 321
Introduction to Row Functions	p. 323
Numeric Functions	p. 334
Text Functions	p. 340
Date Functions	p. 350
Key Points	p. 359
Using Row Functions	p. 361
Specialized Row Functions	p. 363
Using the Documentation of Row Functions	p. 372
Creating Patterns of Numbers and Dates	p. 376
Key Points	p. 397
Summarizing Data	p. 399
Introduction to the Column Functions	p. 401
Maximum and Minimum	p. 404
Count	p. 411
Sum and Average	p. 420
Other Topics	p. 428
Key Points	p. 434
Controlling the Level of Summarization	p. 435
Dividing a Table into Groups of Rows	p. 437
Eliminating Some of the Summarized Data	p. 459
Key Points	p. 471
Inner Joins	p. 473
Introduction to Joins	p. 475
Inner Joins of Two Tables	p. 479
Variations of the Join Condition	p. 495
Applications of Joins	p. 504
Key Points	p. 515
Outer Joins	p. 517
Introduction to Outer Joins	p. 519
Applications of Outer Joins	p. 534
Key Points	p. 553
Union and Union All	p. 555
Union Basics	p. 557
Unconventional Unions	p. 573
Applications of a Union	p. 577
Set Intersection and Set Difference in Oracle	p. 590
Key Points	p. 595
Cross Joins, Self Joins, and Cross Tab Queries	p. 597
Cross Joins	p. 599
Self Joins	p. 613
Cross Tab Queries in Access	p. 624
Cross Tab Queries in Oracle	p. 645
Key Points	p. 650
Combining Tables in a Production Database	p. 653
Methods of Joining Three or More Tables	p. 655
Losing Information	p. 660
Caring about the Efficiency of Your Computer	p. 663
Standardizing the Way That Tables Are Joined	p. 666
Key Points	p. 671
If-Then-Else, Parameter Queries, and Subqueries	p. 673
If-Then-Else Logic	p. 675
Table of Contents provided by Publisher. All Rights Reserved.

Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Excerpts

SQL is one of the most important computer languages. It is the language of databases. Whenever you search for the information you need in a large library of information, the code that performs the search is likely to be using SQL. Many applications in which you share information to coordinate with other people also use SQL.It is used in more than 100 software products, and new ones are being added all the time. This book shows you how to get the most out of the databases you use. It explains how to use SQL to solve practical problems, using the most widely used SQL products, Oracle and Microsoft Access. Oracle and Access are both widely used, easily available, and run on personal computers. By learning these two products in detail, you will have all the basic skills to use any of the many products based on SQL. How the Topics Are PresentedThis book uses an informal conversational style to take you on a tour of SQL topics. Oracle and Access are placed side by side doing the same tasks, so you can see their similarities and differences. Most topics are illustrated with an example of SQL code. I have intentionally kept the tables small in these examples, which makes them easy to check and understand.Each example of SQL code begins by setting a task. Then the SQL code is given that performs that task. Whenever possible, I wrote the SQL code so that it works in both Oracle and Access. However, sometimes I could not do that, so I wrote one version of SQL code for Oracle and a different version for Access.To make this book easier to read, each example of SQL shows the beginning and ending data table(s). This allows you to check that you understand what the SQL is doing. I have tried to make these examples small so they are easy to check.Each example is often followed by notes to explain any subtle points about the SQL code or the data tables.Finally, I give you a problem to solve to check your understanding of the topic. You can decide if you want to do these problems or not. Usually they are fairly easy and require only a small modification of the SQL code in the example. If you decide to do a problem, the Web site will allow you to determine if your solution is correct.Each example of SQL code in this book is designed to be independent and stand on its own, without needing any changes performed in previous sections. This allows you to skip around in the book and read the sections in any order you want. Some people may want to read the book from beginning to end, but it is not necessary to do this.Be sure to look at the appendices for practical tips on how to run Oracle and Access. The database files and the code for all the examples are available from the Web site. In several places throughout this book, I have expressed opinions about computer technology, something that many other technical books avoid doing. These opinions are my own and I take full responsibility for them. I also reserve the right to change my mind. If I do so, I will put my revised opinion, and the reasons that have caused me to change my thinking, on the Web site for this book. The Companion Web SiteThe companion Web site for this book is a Google group called "sqlfun." The group Web address is: http://groups.google.com/group/sqlfunYou can also send e-mail to me at: sqlfun@gmail.comThis Web site contains: Oracle SQL code to build all the data tables used in this book. Access databases with all the data tables used in this book. Databases are available for several versions of Access. Ways to check your answers to problems in the book. A list of corrections, if there are any. An open area for discussions, your comments, and