SQL Fundamentals

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 over 20 years of experience in software development at Bank of America, Chevron, Kaiser Permanente, and other leading firms.

Preface.


Acknowledgments.


1. Storing Information in Tables.


Introduction. The Parts of a Table. Examples of Tables. Summary.



2. Getting Information from a Table.


The Select Statement. The Select Clause. The Where Clause. The Order By Clause. Summary. Exercises.



3. Compound Conditions in the Where Clause.


Compound Conditions in the Where Clause. Constant Values. Punctuation Matters. Some Exercises Solved for You. Summary. Exercises.



4. Saving Your Results.


Saving Your Results in a New Table or View. Modifying the Data in a Table. Summary. Exercises.



5. Modifying Data Through a View.


Transactions. Modifying Data Through a View. Oracle SQLplus. Finding Information About Tables and Views. Summary. Exercises.



6. Creating Your Own Tables.


Creating Tables. Changing Tables. Tables with Duplicate Rows. Loading a Large Amount of Data from a Flat File. Summary. Exercises.



7. Formats, Sequences, and Indexes.


Formats. Sequences. Indexes. Finding More Information in the Data Dictionary. An Exercise Solved for You. Summary. Exercises.



8. Data Integrity.


Referential Integrity. The Delete Options and Update Options of RI. Variations of Referential Integrity. Other Types of Constraints. Summary. Exercises.



9. Row Functions.


Introduction to Row Functions. Number Functions. Text Functions. Date Functions. Summary. Exercises.



10. Using Row Functions.


Other Functions. Using the Documentation of Row Functions. Creating Patterns of Numbers and Dates. Summary. Exercises.



11. Summarizing Data.


Introduction to the Column Functions. Maximum and Minimum. Count. Sum and Average. Other Topics. Summary. Exercises.



12. Controlling the Level of Summarization.


Dividing a Table into Groups of Rows and Summarizing Each Group. Eliminating Some of the Summarized Data. Restrictions on Summarization. Summary. Exercises.



13. Inner Joins.


Introduction to Joins. Inner Joins of Two Tables. Variations of the Join Condition. Applications of Joins. Summary. Exercises.



14. Outer Joins.


Introduction to Outer Joins. Applications of Outer Joins. Summary. Exercises.



15. Union and Union All.


Unions. Unconventional Unions. Applications of a Union. Set Intersection and Set Difference in Oracle. Summary. Exercises.



16. Cross Joins and Self Joins.


Cross Joins. Self Joins. Summary. Exercises.



17. Combining Tables in a Production Database.


Methods of Joining Three or More Tables. Losing Information. Caring About the Efficiency of Your Computer. Standardizing the Way That Tables Are Joined. Summary.



18. If-Then-Else and Subqueries.


If-Then-Else Logic. Parameter Queries. Subqueries. Applications of Subqueries. Older Features of Subqueries. Summary.



19. The Multiuser Environment.


Database Configurations. Operating in a Multiuser Environment. Security and Privileges. Several People Using the Same Table at the Same Time. Locks. The Data Dictionary and the Multiuser Environment. How to Create a Shared Application. Summary.



20. The Design of SQL.


Original SQL Design Objectives. Newer Interfaces. Typical Application. Summary.



Appendix A. Obtaining and Installing Oracle.


Getting Current Information. Which Product Should You Get? Downloading Oracle from the Internet. Ordering Oracle on a CD. Installing Oracle. Setting Up to Run the Examples in This Book. Setting Up a New Userid. Installing the Tables Used in This Book. How to Build the Tables and Assign Other Tablespace Names.



Appendix B. Tips on Using Oracle.


Starting Oracle. Entering a Query. Other Ways to Edit Your SQL Code. Error Messages Are Sometimes Correct. Using the Scrollbars. If Oracle Does Not Respond. Installing the LOGIN Script. Running All the SQL for a Chapter.



Appendix C. Tips on Using Access.


You Can Use Access 2000 or 2002. Starting Access. Entering an SQL Query. Dealing with Errors. Printing.



Appendix D. Diagram of the Lunches Database.


Join Conditions. Data Validation Rules.



Index.