SQL Fundamentals

832 Pages·2008·5.79 MB·english

by  John J. Patrick

S Q L
F
U N D A M E N T A L S
Third Edition
■■
John J. Patrick
Upper Saddle River, NJ • Boston • Indianapolis • San Francisco
New York • Toronto • Montreal • London • Munich • Paris • Madrid
Capetown• Sydney • Tokyo • Singapore • Mexico City
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the
designations have been printed with initial capital letters or in all capitals.
The author and publisher have taken care in the preparation of this book, but make no expressed or implied
warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental
or consequential damages in connection with or arising out of the use of the information or programs contained
herein.
The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases or special
sales, which may include electronic versions and/or custom covers and content particular to your business,
training goals, marketing focus, and branding interests. For more information, please contact:
U.S. Corporate and Government Sales
[phone]
[email protected]
For sales outside the United States, please contact:
International Sales
[email protected]
Visit us on the Web: informit.com/ph
Library of Congress Cataloging-in-Publication Data
Patrick, John J.
SQL fundamentals / John J. Patrick. — 3rd ed.
p. cm.
Includes indexes.
ISBN 978-0-13-712602-6 (pbk. : alk. paper) 1. SQL (Computer program
language) 2. Oracle. 3. Microsoft Access.
I. Title.
QA76.73.S67P38 2008
005.75'65—dc22
2008024745
Copyright © 2009 Pearson Education, Inc.
All rights reserved. Printed in the United States of America. This publication is protected by copyright, and
permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval
system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or
likewise. For information regarding permissions, write to:
Pearson Education, Inc.
Rights and Contracts Department
501 Boylston Street, Suite 900
Boston, MA 02116
Fax: [phone]
ISBN-13: 978-0-13-712602-6
ISBN-10: 0-13-712602-6
Text printed in the United States on recycled paper at Courier in Stoughton, Massachusetts.
First printing, August 2008
C G
ONTENTS AT A LANCE
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
1 Storing Information in Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
2 Getting Information from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31
3 Compound Conditions in the Where Clause. . . . . . . . . . . . . . . . . . . . . . . . . . .83
4 Saving Your Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133
5 The Data Dictionary and Other Oracle Topics . . . . . . . . . . . . . . . . . . . . . . . .171
6 Creating Your Own Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209
7 Formats, Sequences, and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245
8 Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .281
9 Row Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .321
10 Using Row Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .361
11 Summarizing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .399
12 Controlling the Level of Summarization . . . . . . . . . . . . . . . . . . . . . . . . . . . . .435
13 Inner Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .473
14 Outer Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .517
15 Union and Union All . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .555
16 Cross Joins, Self Joins, and CrossTab Queries . . . . . . . . . . . . . . . . . . . . . . . .597
17 Combining Tables in a Production Database . . . . . . . . . . . . . . . . . . . . . . . . .653
18 If-Then-Else, Parameter Queries, and Subqueries. . . . . . . . . . . . . . . . . . . . . .673
19 The Multiuser Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .721
20 The Design of SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .739
A Oracle Is Free: How to Get Your Copy . . . . . . . . . . . . . . . . . . . . . . . . . . . . 751
B Quick Start with Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
C Quick Start with Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 771
D Diagram of the Lunches Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 787
vii
C
ONTENTS
Preface xxv
How the Topics Are Presented xxvi
The Companion Web Site xxvii
Acknowledgments xxvii
Chapter 1
Storing Information in Tables . . . . . . . . . . . . . . . . . . . . .1
Introduction 3
1-1 What is SQL? 3
1-2 What is a relational database and why would you use one? 4
1-3 Why learn SQL? 6
1-4 What is in this book? 8
The Parts of a Table 9
1-5 Data is stored in tables 10
1-6 A row represents an object and the information about it 11
1-7 A column represents one type of information 12
1-8 A cell is the smallest part of a table 14
1-9 Each cell should express just one thing 15
1-10 Primary key columns identify each row 16
1-11 Most tables are tall and thin 18
ix
x CONTENTS
Examples of Tables 19
1-12 An example of a table in Oracle and Access 19
1-13 Some design decisions in the l_employees table 22
1-14 The Lunches database 23
Key Points 30
Chapter 2
Getting Information from a Table . . . . . . . . . . . . . . . . . 31
The Select Statement 33
2-1 The goal: Get a few columns and rows from a table 33
2-2 Overview of the select statement 34
The Select Clause 37
2-3 Overview of the select clause 37
2-4 Use a select clause to get a list of some of the columns 38
2-5 Use a select clause to get a list of all of the columns 41
2-6 Use a select clause to get the distinct values in one column 44
2-7 Use a select clause to get the distinct values in two columns 48
The Where Clause 50
2-8 Overview of the where clause 50
2-9 Using an Equal condition in the where clause 52
2-10 Using a Less Than condition in the where clause 56
2-11 Using a Not Equal condition in the where clause 58
2-12 Using the in condition in the where clause 61
2-13 Using the between condition in the where clause 63
2-14 Using the like condition in the where clause 66
2-15 Using the is null condition in the where clause 69
The Order By Clause 71
2-16 Overview of the order by clause 71
2-17 Sorting the rows by one column in ascending order 73
2-18 Sorting the rows by several columns in ascending order 75
2-19 Sorting the rows by several columns in various orders 77
2-20 The whole process so far 79
Key Points 80
CONTENTS xi
Chapter 3
Compound Conditions in the Where Clause . . . . . . . . . 83
Compound Conditions in the Where Clause 85
3-1 Using a compound condition in the where clause 85
3-2 Using not with in, between, like, and is null 88
3-3 The standard form of a complex condition in the where clause 90
3-4 A common mistake 92
Constant Values 95
3-5 Using a constant value in the select clause 96
3-6 Using a table of constants 98
Punctuation Matters 102
3-7 Punctuation you need to know right now 102
3-8 Punctuation reference section 106
Case Sensitivity 113
3-9 Case sensitivity in Oracle 113
3-10 The debate about case sensitivity in SQL 117
3-11 You have a choice 117
3-12 You can turn off case sensitivity in the Oracle SQL
Command Line environment 117
3-13 Case sensitivity in Access 118
Three-Valued Logic 120
3-14 SQL uses three-valued logic 120
Error Messages 122
3-15 Error messages are often wrong 122
Some Exercises Solved for You 124
3-16 Exercise 1 124
3-17 Exercise 2 127
3-18 Exercise 3 129
Key Points 131
Chapter 4
Saving Your Results. . . . . . . . . . . . . . . . . . . . . . . . . . .133
Saving Your Results in a New Table or View 135
4-1 Create a new table from the result of a select statement 135
4-2 Creating a new view from the results of a select statement 139
xii CONTENTS
4-3 Similarities between tables and views 142
4-4 Differences between tables and views 142
4-5 Deleting a table 143
4-6 Deleting a view 145
4-7 One view can be built on top of another view 146
4-8 Preventative delete 149
Modifying the Data in a Table with SQL 151
4-9 Adding one new row to a table 151
4-10 Adding many new rows to a table 154
4-11 Changing data in the rows already in a table 157
4-12 Deleting rows from a table 159
Modifying the Data in a Table with the GUI 161
4-13 Using the Oracle GUI to change data in a table 161
4-14 Using the Access GUI to change the data in a table 164
Restrictions on Modifying the Data in a Table 167
4-15 Constraints with insert, update, and delete 167
4-16 Security restrictions 169
Key Points 170
Chapter 5
The Data Dictionary and Other Oracle Topics . . . . . . 171
Commit, Rollback, and Transactions 173
5-1 The commit and rollback commands 173
5-2 The Autocommit option 174
5-3 Transactions 175
Modifying Data through a View 179
5-4 Changing data through a view 180
5-5 Example of changing data through a view 181
5-6 Views using With Check Option 189
The SQL Commands Page in Oracle 192
5-7 Overview of the SQL Commands page 192
5-8 The Autocommit option 194
5-9 The Explain option 194
Using the Oracle Data Dictionary — Part 1 195
5-10 Overview of the Data Dictionary 195
CONTENTS xiii
5-11 How to find the names of all the tables 197
5-12 How to find the names of all the views 199
5-13 How to find the select statement that defines a view 200
5-14 How to find the names of the columns in a table or view 201
5-15 How to find the primary key of a table 203
Key Points 207
Chapter 6
Creating Your Own Tables . . . . . . . . . . . . . . . . . . . . . 209
Creating Tables 211
6-1 The create table command 211
6-2 Datatypes in Oracle and Access 212
6-3 Text datatypes 217
6-4 Numeric datatypes 222
6-5 Date/time datatypes 222
6-6 Other datatypes 222
6-7 Putting data into a new table 223
6-8 Creating the l_employees table in Oracle 224
Changing Tables 226
6-9 Adding a primary key to a table 226
6-10 Changing the primary key of a table 228
6-11 Adding a new column to a table 229
6-12 Expanding the length of a column 231
6-13 Deleting a column from a table 232
6-14 Making other changes to tables 234
Tables with Duplicate Rows 236
6-15 The problem with duplicate rows 237
6-16 How to eliminate duplicate rows 239
6-17 How to distinguish between duplicate rows 240
Key Points 243
Chapter 7
Formats, Sequences, and Indexes. . . . . . . . . . . . . . . . 245
Formats 247
7-1 Formats of dates 247
xiv CONTENTS
7-2 Displaying formatted dates 249
7-3 Entering formatted dates 252
7-4 Other formats in Oracle 254
7-5 Formats in Access 254
Sequences 257
7-6 Creating a sequence in Oracle 257
7-7 Using sequences in Oracle 258
7-8 Sequences in Access 260
Indexes 262
7-9 Creating an index 263
7-10 The Optimizer 264
7-11 An example of how an index works 264
Using the Oracle Data Dictionary — Part 2 266
7-12 How to find information about the datatype of a column 266
7-13 How to find information about sequences 269
7-14 How to find information about indexes 271
7-15 How to find information about all your database objects 274
7-16 How to use the index of Data Dictionary tables 276
7-17 How to use the index of Data Dictionary columns 277
An Exercise Solved for You 278
7-18 Create a table of the days you want to celebrate 278
Key Points 280
Chapter 8
Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Constraints on One Table 283
8-1 A constraint keeps the data consistent 283
8-2 check constraints 283
8-3 unique constraints 285
8-4 not null constraints 287
8-5 primary key constraints 288
8-6 Restrictions on the datatype and length of fields 289
Referential Integrity 289
8-7 The concept of RI 290
8-8 An example of RI 291
CONTENTS xv
8-9 Inserts and updates to the data table prevented by RI 293
8-10 Inserts and updates to the data table allowed by RI 294
8-11 Updates and deletes to the lookup table prevented by RI 295
8-12 How to delete a code from the lookup table 296
8-13 How to change a code in the lookup table 298
8-14 RI as a relationship between the tables 299
8-15 Setting up RI in the Access GUI 300
The Delete Options and Update Options of RI 303
8-16 The three options for deletes and updates to the lookup table 303
8-17 The delete rule: set null 304
8-18 The delete rule: cascade 306
8-19 The update rule: cascade 308
Variations of Referential Integrity 311
8-20 The two meanings of primary key 311
8-21 Using two or more columns for the primary key 313
8-22 The lookup and data tables can be the same table 315
How to Code Constraints in a Create Table Statement 316
8-23 Constraints are often coded in the create table statement 316
Key Points 319
Chapter 9
Row Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .321
Introduction to Row Functions 323
9-1 Getting data directly from the beginning table 323
9-2 What is a row function? 324
9-3 An example of a row function in the select clause 327
9-4 An example of a row function used in
all the clauses of a select statement 329
9-5 Defining a row function as the first step 331
Numeric Functions 334
9-6 Functions on numbers 334
9-7 How to test a row function 336
9-8 Another way to test a numeric row function 337
Text Functions 340
9-9 Functions on text 340