Cover image for SQL fundamentals
SQL fundamentals
Patrick, John J.
Personal Author:
Publication Information:
Upper Saddle River, N.J. : Prentice Hall PTR, [1999]

Physical Description:
xiii, 517 pages : illustrations ; 24 cm + 1 computer optical disc (4 3/4 in.)
General Note:
Includes index.

CD-ROM contains sample databases and code.
Format :


Call Number
Material Type
Home Location
Central Library QA76.73.S67 P38 1999 Book and Software Set Central Closed Stacks

On Order



Dont just learn generic SQL Learn SQL to get results with the worlds top database platforms: Oracle for the enterprise and Microsoft Access for the desktop Based on John Patricks hands-on SQL course for IT pros at the University of California, Berkeley, 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. SQL Fundamentals covers all this and more: * Simple queries that retrieve information from a single table * Creating tables and views to divide a complex process into a series of single steps * Summarizing your data * Inner joins, outer joins, unions, self joins, cross joins, and subqueries * Writing SQL queries that are easily understood, verified, modified, and extended Want better access to business data? Want to make your data more accessible to others? SQL Fundamentals delivers the practical techniques and examples you need - on the database platforms you already use If you want to fully use the information in your database, then read this book CD-ROM INCLUDED The accompanying CD-ROM contains a library of Access databases and the code for building Oracle databases. All the tables use

Author Notes

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



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 Presented This 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 Site The companion Web site for this book is a Google group called "sqlfun." The group Web address is: You can also send e-mail to me at: This 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 questions you want me to answer. I invite you to come visit the Web site! Excerpted from SQL Fundamentals by John J. Patrick All rights reserved by the original copyright owners. Excerpts are provided for display purposes only and may not be reproduced, reprinted or distributed without the written permission of the publisher.

Table of Contents

Prefacep. xv
Chapter 1 Storing Information in Tablesp. 1
Introductionp. 2
The parts of a tablep. 10
Examples of tablesp. 24
Oracle and Accessp. 32
Chapter 2 Getting Information from a Tablep. 45
The Select statementp. 46
The Select clausep. 50
The Where clausep. 60
The Order By clausep. 82
Other techniquesp. 88
The Whole Processp. 90
Summaryp. 96
Exercisesp. 97
Chapter 3 Saving Your Resultsp. 107
Saving your results in a new table or viewp. 108
Modifying the data in a tablep. 122
Modifying data through a viewp. 132
Finding information about tables and viewsp. 142
Summaryp. 154
Exercisesp. 155
Chapter 4 Creating Your Own Tablesp. 163
Creating tablesp. 164
Changing tablesp. 170
Finding more information about tables in the Data Dictionaryp. 182
Formatsp. 190
Summaryp. 196
Exercisesp. 197
Chapter 5 Row Functionsp. 205
Introduction to row functionsp. 206
Number functionsp. 214
Text functionsp. 218
Date functionsp. 224
Other functionsp. 228
Creating patterns of numbers and datesp. 238
Summaryp. 248
Exercisesp. 249
Chapter 6 Summarizing Datap. 259
Introduction to the column functionsp. 260
Dividing a table into groups of rows and summarizing each groupp. 286
Solutions to some problemsp. 306
Summaryp. 310
Exercisesp. 311
Chapter 7 Inner Joinsp. 315
Introduction to joinsp. 316
Inner joins of two tablesp. 320
Variations of the join conditionp. 332
Inner joins of three or more tablesp. 338
The new syntax for an inner joinp. 342
Other issuesp. 348
Summaryp. 358
Exercisesp. 359
Chapter 8 Outer Joins and Unionsp. 365
Outer joinsp. 366
Unionsp. 384
Applications of unions (divide and conquer)p. 396
Set Intersection and Set Difference in Oraclep. 408
Summaryp. 412
Exercisesp. 413
Chapter 9 Self Joins, Cross Joins, and Subqueriesp. 419
Self joinsp. 420
Cross joinsp. 436
Subqueriesp. 444
Summaryp. 456
Exercisesp. 457
Chapter 10 Advanced Queriesp. 463
The Decode and IIF functionsp. 464
Using the environment in which SQL runsp. 476
Spreadsheet reportsp. 492
Summaryp. 508
Indexp. 509

Google Preview