10,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
A right-to-the-point guide on all the key topics of SQL programming
SQL Essentials For Dummies is your quick reference to all the core concepts of SQL—a valuable common standard language used in relational databases. This useful guide is straightforward—with no excess review, wordy explanations, or fluff—so you get what you need, fast. Great for a brush-up on the basics or as an everyday desk reference, this book is one you can rely on.
- Strengthen your understanding of the basics of SQL
- Review what you've already learned or pick up key skills
- Use SQL to create, manipulate, and control relational databases
- Jog your memory on the essentials as you work and get clear answers to your questions
Perfect for supplementing classroom learning, reviewing for a certification, and staying knowledgeable on the job, SQL Essentials For Dummies is the convenient, direct, and digestible reference you've been looking for.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 254
Veröffentlichungsjahr: 2024
Ähnliche
SQL Essentials For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “SQL Essentials For Dummies Cheat Sheet” in the Search box.
Table of Contents
Cover
Title Page
Copyright
Introduction
About This Book
Foolish Assumptions
Icons Used in This Book
Where to Go from Here
Chapter 1: Getting to Know SQL
Knowing What SQL Does
Knowing What SQL Does Not Do
Choosing and Using an Available RDBMS Implementation
Chapter 2: Creating a Database with SQL
First Things First: Planning Your Database
Building Tables
Setting Constraints
Working with Keys and Indexes
Ensuring Data Validity with Domains
Establishing Relationships between Tables
Altering Table Structure
Deleting Tables
Chapter 3: Drilling Down to the SQL Nitty-Gritty
SQL’s Data Types
Handling Null Values
Applying Constraints
Operating on Data with the Data Manipulation Language
Chapter 4: Values, Variables, Functions, and Expressions
Entering Data Values
Working with Functions
Using Expressions
Chapter 5: SELECT Statements and Modifying Clauses
Finding Needles in Haystacks with the SELECT Statement
Modifying Clauses
Chapter 6: Querying Multiple Tables with Subqueries
Introducing Subqueries
Using Subqueries in INSERT, DELETE, and UPDATE Statements
Chapter 7: Querying Multiple Tables with Relational Operators
UNION
INTERSECT
EXCEPT
JOINS
ON versus WHERE
Join Conditions and Clustering Indexes
Chapter 8: Cursors
Declaring a Cursor
Opening a Cursor
Operating on a Single Row
Closing a Cursor
Chapter 9: Assigning Access Privileges
Working with the SQL Data Control Language
Identifying Authorized Users
Classifying Users
Granting Privileges
Revoking Privileges
Granting Roles
Revoking Roles
Chapter 10: Ten Retrieval Tips
Verify the Database Structure
Try Queries on a Test Database
Double-Check Queries That Include Joins
Triple-Check Queries with Subselects
Summarize Data with GROUP BY
Watch GROUP BY Clause Restrictions
Use Parentheses with AND, OR, and NOT
Control Retrieval Privileges
Back Up Your Databases Regularly
Handle Error Conditions Gracefully
Index
About the Authors
Advertisement Page
Connect with Dummies
End User License Agreement
List of Tables
Chapter 2
TABLE 2-1 Tables for Honest Abe
Chapter 4
TABLE 4-1 Sample Literals of Various Data Types
TABLE 4-2 Photographic Paper Price List per 20 Sheets
TABLE 4-3 Sample Uses of the POSITION Statement
TABLE 4-4 Examples of String Value Expressions
Chapter 5
TABLE 5-1 SQL’s Comparison Predicates
TABLE 5-2 SQL’s LIKE Predicate
Chapter 6
TABLE 6-1 Ford Small-Block V8s, 1960–1980
TABLE 6-2 Chevy Small-Block V8s, 1960–1980
Chapter 7
TABLE 7-1 LOCATION
TABLE 7-2 DEPT
TABLE 7-3 EMPLOYEE
List of Illustrations
Chapter 1
FIGURE 1-1: A Microsoft Access 365 database window running the Northwind Trader...
FIGURE 1-2: The Design view of the qryProductOrders query.
FIGURE 1-3: The SQL view of the qryProductOrders query.
FIGURE 1-4: The results of the query to display all the data in the PRODUCTS ta...
Chapter 5
FIGURE 5-1: The result set for retrieval of sales for July 2011.
FIGURE 5-2: Average sales for each salesperson.
FIGURE 5-3: Total sales for each salesperson.
FIGURE 5-4: Total sales for all salespeople except Saraiva.
Chapter 6
FIGURE 6-1: Chevy muscle cars with horsepower to displacement ratios higher tha...
Guide
Cover
Table of Contents
Title Page
Copyright
Begin Reading
Index
About the Authors
Pages
iii
iv
1
2
3
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
SQL Essentials For Dummies®
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.
Media and software compilation copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2024948584
ISBN 978-1-394-29694-1 (pbk); ISBN 978-1-394-29696-5 (ebk); ISBN 978-1-394-29695-8 (ebk)
Introduction
SQL is the internationally recognized standard language for dealing with data in relational databases. Developed by IBM, SQL became an international standard in 1986. The standard was updated in 1989, 1992, 1999, 2003, 2008, 2011, 2016, and 2023. It continues to evolve and gain capability. Database vendors continually update their products to incorporate the new features of the ISO/IEC standard. (For the curious out there, ISO is the International Organization for Standardization, and IEC is the International Electrotechnical Commission.)
SQL isn’t a general-purpose language, such as C++ or Java. Instead, it’s strictly designed to deal with data in relational databases. With SQL, you can carry out all the following tasks:
Create a database, including all tables and relationships.
Fill database tables with data.
Change the data in database tables.
Delete data from database tables.
Retrieve specific information from database tables.
Grant and revoke access to database tables.
Protect database tables from corruption due to access conflicts or user mistakes.
About This Book
In this book, we cover how to use SQL to build and work with databases. Using SQL isn’t just about knowing the terms and keywords, it’s also about knowing when and how to use them to maximize performance of your application.
Here are some of the things you can do with this book:
Find out about the capabilities and limitations of SQL.
Discover how to develop reliable and maintainable database systems.
Create databases.
Speed database queries.
Control access to sensitive information.
Within this book, you may note that some web addresses break across two lines of text. If you’re reading this book in print and want to visit one of these web pages, simply key in the web address exactly as it’s noted in the text, pretending as though the line break doesn’t exist. If you’re reading this as an e-book, you’ve got it easy — just click the web address to be taken directly to the web page.
Foolish Assumptions
We know that this is a For Dummies book, but we don’t really expect that you’re a dummy. In fact, we assume that you’re a very smart person. After all, you decided to read this book, which is a sign of high intelligence indeed. Therefore, we assume that you may want to do a few things, such as re-create some of the examples in the book. You may even want to enter some SQL code and execute it. To do that, you need at the very least an SQL editor and more likely also a relational database management system (RDBMS) of some sort. Many choices are available, both proprietary and open source. We mention several of these products at various places throughout the book but don’t recommend any one in particular. Any product that complies with the ISO/IEC international SQL standard should be fine.
That said, take claims of ISO/IEC compliance with a grain of salt. No RDBMS available today is 100 percent compliant with the ISO/IEC SQL standard. For that reason, some of the code examples we give in this book may not work in the particular SQL implementation that you’re using. The code samples we use in this book are consistent with the international standard rather than with the syntax of any particular implementation, unless we specifically state that the code is for a particular implementation.
Icons Used in This Book
For Dummies books are known for those helpful icons that point you in the direction of really great information. This section briefly describes the icons used in this book.
The Tip icon points out helpful information that’s likely to make your job easier.
This icon marks a generally interesting and useful fact — something that you may want to remember for later use.
The Warning icon highlights lurking danger. When you see this icon, pay attention, and proceed with caution.
Where to Go from Here
If you’re brand-new to the database world, start out in Chapter 1. It explains why databases are useful, and walks through a few of the different popular database software packages available.
If you’re already familiar with database software packages, and you just want to dive into the nuts and bolts of working with databases, Chapters 2 through 5 cover all the things you’ll need to know to get a database up and running.
If you’re already an old hand at SQL and you want to dive into some more advanced topics, Chapters 6 through 8 cover the complicated world of database queries. There are plenty of ways to retrieve data from a database, but not all of them are efficient — especially if you have lots of data to work with!
Chapter 9 examines the dreaded security topic. If you’re just creating a database for your own use, feel free to skip this chapter, but if you work in an environment where lots of people are going to need access to your database, this chapter is a must. Knowing how to protect your data from prying eyes has become a hot topic these days, and it’s important to know just how to protect it.
Finally, Chapter 10 provides ten tips for improving your database experience when using SQL to retrieve data.
Chapter 1
Getting to Know SQL
IN THIS CHAPTER
Undertanding what SQL does
Getting clear on what SQL doesn’t do
Weighing your SQL implementation options
In the early days of the relational database management system (RDBMS), there was no standard language for performing relational operations on data. A number of companies came out with RDBMS products, and each had its own associated language. However, differences in syntax and functionality made it impossible for a person using the language of one RDBMS to operate on data that had been stored by another. The creation of SQL solved this problem, but SQL is a continually evolving language that changes with each official release (the most recent being in 2023). This chapter explores just what SQL is (and isn’t). It also takes a look at using SQL in some different database packages.
Knowing What SQL Does
SQL (pronounced ess cue el) is a software tool designed to deal with relational database data. It does far more than just execute queries. Yes, you can use it to retrieve the data you want from a database using a query. But you can also use SQL to create and destroy databases, as well as modify their structure. In addition, you can add, modify, and delete data with SQL. Even with all that capability, SQL is still considered only a data sublanguage, which means that it doesn’t have all the features of general-purpose programming languages such as C, C++, C#, or Java.
SQL is specifically designed for dealing with relational databases, so it doesn’t include a number of features needed for creating useful application programs. As a result, to create a complete application — one that handles queries, as well as provides access to a database — you have to write the code in one of the general-purpose languages and embed SQL statements within the program whenever it communicates with the database.
Knowing What SQL Does Not Do
Before we can tell you what SQL doesn’t do, we need to give you some background information. In the 1930s, computer scientist and mathematician Alan Turing defined a very simple machine that could perform any computation that could be performed by any computer imaginable, regardless of how big and complex. This simple machine has come to be known as a universal Turing machine. Any computer that can be shown to be equivalent to a universal Turing machine is said to be Turing-complete. All modern computers are Turing-complete. Similarly, a computer language capable of expressing any possible computation is said to be Turing-complete. Practically all popular languages, including C, C#, C++, BASIC, FORTRAN, COBOL, Pascal, Java, and many others, are Turing-complete. SQL, however, is not.
Because standard SQL is not Turing-complete, you can’t write an SQL program to perform a complex series of steps the way you can with a language such as C or Java. On the other hand, languages such as C and Java don’t have the data-manipulation capabilities that SQL has, so you can’t write a program with them that will efficiently operate on database data. There are several ways to solve this dilemma:
Combine the two types of language by embedding SQL statements within a program written in a host language such as C.
Have the C program make calls to SQL modules to perform data-manipulation functions.
Create a new language that includes SQL, but also incorporates those structures that would make the language Turing-complete. (This is essentially what Microsoft and Oracle have done with their versions of SQL.)
All three of these solutions are offered by various vendors.
Choosing and Using an Available RDBMS Implementation
SQL by itself isn’t all that useful — you need a platform that stores the data itself and uses SQL to create, read, update, and delete (often called CRUD) the data. This is where the RDBMS comes in.
The RDBMS is a program that stores data in a manner that makes it easy to retrieve the data as quickly as possible. Storing data in a typical file isn’t efficient, because in order to find a specific data item, the program would have to read through the entire file until it got to that data.
An RDBMS system uses various methods to store and index data so it can quickly find a specific data record, based on the SQL statement it’s processing. There are plenty of different RDBMS programs available these days, each with different features to help increase data retrieval performance. In the following sections, we fill you in on some of the more common RDBMS programs available today.
Microsoft Access
Microsoft Access is an entry-level RDBMS with which developers can build relatively small and simple databases and database applications. It’s designed for use by people with little or no training in database theory. You can build databases and database applications using Access, without ever seeing SQL. However, you can opt to use SQL in Access if you so choose.
Access runs under any of the Microsoft Windows operating systems, as well as Apple’s macOS, but not under Linux or any other non-Microsoft operating system.
To reach the SQL editor in Access, do the following:
Open a database that already has tables and at least one query defined.
A great place to start is with the Northwind Traders Starter Edition database provided as a free download with Access. The database includes a built-in mini-application that uses Access forms to help query and insert data. After you download the database, the application portion automatically starts, showing a form that asks you to create a user account for the application. After you log into the application, you see a database window that looks something like Figure 1-1, with the default Home tab visible. The icon at the left end of the Ribbon is the icon for Layout View, one of several available views. In this example, the pane on the left side of the window shows the different tables, forms, reports, queries, and scripts that have been created as part of the Northwind Traders Starter Edition database.
Click the Queries entry in the pane on the left, and then double-click the qryProductOrders query.
The default view shows the data that’s a result of the query.
To see how the query is constructed, click the View icon at the top, and then select Design View.
The Design View for the query is shown in Figure 1-2. At the top is a graphical representation of the tables involved in the query, and below that is a list of the data fields that are retrieved in the query.
Choose SQL View from the View drop-down menu.
Doing so shows the view displayed in Figure 1-3. It’s the SQL code generated in order to display the result of the Team Membership of Paper Authors query.
As you can see, it took a pretty complicated SQL statement to perform that Product Order query.
FIGURE 1-1: A Microsoft Access 365 database window running the Northwind Traders Starter Edition database.
FIGURE 1-2: The Design view of the qryProductOrders query.
FIGURE 1-3: The SQL view of the qryProductOrders query.
When you’re a true SQL master, you may want to enter a query directly using SQL, instead of going through the extra stage of using Access’s QBE facility. When you get to the SQL Editor, which is where you are right now, you can do just that. Step 8 shows you how.
Delete the SQL code currently in the SQL Editor pane and replace it with the query you want to execute.
For example, suppose you wanted to display all the rows and columns of the PRODUCTS table. The following SQL statement will do the trick:
SELECT * FROM PRODUCTS ;
Execute the SQL statement that you just entered by clicking on the big red exclamation point in the ribbon that says Run.
Doing so produces the result shown in Figure 1-4, back in Datasheet View. This is a listing of all the data records stored in the PRODUCTS table.
FIGURE 1-4: The results of the query to display all the data in the PRODUCTS table.
Don’t save your new query because it will replace the standard qryProductOrders query in the Northwind Traders Starter Edition database. Just exit out without saving your changes.
Microsoft SQL Server
Microsoft SQL Server is Microsoft’s entry into the enterprise database market. It runs only under one of the various Microsoft Windows operating systems. The latest version is SQL Server 2022. Unlike Microsoft Access, SQL Server requires a high level of expertise in order to use it at all. Users interact with SQL Server using Transact-SQL, also known as T-SQL. Additional functionality provides the developer with usability and performance advantages that Microsoft hopes will make SQL Server more attractive than its competitors. There is a free version of SQL Server 2022, called SQL Server 2022 Express Edition, that you may think of as SQL Server on training wheels. It’s fully functional, but the size of database it can operate on is limited.
IBM DB2
IBM DB2 is a flexible product that runs on Windows and Linux PCs on the low end all the way up to IBM’s largest mainframes. As you would expect for a DBMS that runs on big iron, it’s a full-featured product. As with Microsoft’s SQL Server, to use DB2 effectively, a developer must have received extensive training and considerable hands-on experience.
Oracle Database
Oracle Database is another DBMS that runs on PCs running the Windows, Linux, or macOS operating system, and also on very large, powerful computers.
SQL Developer is a free graphical tool that developers can use to enter and debug Oracle SQL code.
A free version of Oracle, called Oracle Database Express Edition, is available for download from the Oracle website (www.oracle.com/database/technologies/appdev/xe.html). It provides a convenient environment for learning Oracle. Migration to the full Oracle Database 11g product is smooth and easy when you’re ready to move into production mode. The enterprise-class edition of Oracle hosts some of the largest databases in use today. (The same can be said for DB2 and SQL Server.)
Sybase SQL Anywhere
Sybase SQL Anywhere is a high-capacity, high-performance DBMS compatible with databases originally built with Microsoft SQL Server, IBM DB2, Oracle, and MySQL, as well as a wide variety of popular application-development languages. It features a self-tuning query optimizer and dynamic cache sizing.
Tuning queries can make a big difference in their execution time. Tuning a query means making adjustments to it to make it run faster. Dynamic cache sizing means changing the size of the cache memory available to a query, based on the resources that the query needs to run as fast as possible. We talk about query tuning in Chapter 4.
MySQL
MySQL is the most widely used open-source DBMS. The defining feature of open-source software is that it’s freely available to anyone. After downloading it, you can modify it to meet your needs and even redistribute it, as long as you give attribution to its source.
One amazing feature of MySQL is that it offers multiple ways of storing and managing data, which are called storage engines. The most feature-rich of these is the InnoDB storage engine, which provides many of the advanced database features found in commercial databases such as Microsoft SQL Server.
Another popular storage engine is the MyISAM storage engine, which is particularly noted for its speed. Although it lacks many of the advanced features found in the InnoDB storage engine, the MyISAM storage engine is amazingly fast with simple data queries, making it a popular choice for web-based applications. The MySQL server runs under Windows and Linux, but not under IBM’s proprietary mainframe operating systems. MySQL is supported by a large and dedicated user community, which you can learn about at www.mysql.com.
PostgreSQL
PostgreSQL (pronounced POST gress CUE el) is another open-source DBMS, and it’s generally considered to be more robust than MySQL, and more capable of supporting large enterprise-wide applications. It’s also supported by an active user community. PostgreSQL runs under Linux, UNIX, Windows, and IBM’s z/OS mainframe operating system.
Chapter 2
Creating a Database with SQL
IN THIS CHAPTER
Building tables
Setting constraints
Establishing relationships between tables
Altering table structure
Deleting tables
SQL is functionally divided into three components: the Data Definition Language (DDL), the Data Manipulation Language (DML), and the Data Control Language (DCL). The DDL consists of three statements: CREATE, ALTER, and DROP. You can use these statements to create database objects (such as tables), change the structure of an existing object, or delete an object. After you’ve designed a database, the first step in bringing it into reality is to build a table with the help of the DDL. After you’ve built the tables, the next step is to fill them with data. That’s the job of the DML. As for the DCL, you call on it to help you preserve data integrity. In this chapter, we discuss the functions of the DDL.
First Things First: Planning Your Database
Before you can start constructing a database, you need to have a clear idea of the real-world or conceptual system that you’re modeling. Some aspects of the system are of primary importance. Other aspects are subsidiary to the ones you’ve identified as primary. Additional aspects may not be important at all, depending on what you’re using the database for. Based on these considerations, you’ll build a model of the system based on the data entities and their relationships (called an ER model), with primary aspects identified as entities and subsidiary aspects identified as attributes of those entities. Unimportant aspects don’t appear in the model at all.
After you’ve finalized your ER model, you can translate it into a normalized relational model. The relational model is your guide for creating database tables and establishing the relationships between them.
Building Tables
The fundamental object in a relational database is the table. Tables correspond directly to the relations in a normalized relational model. Table creation can be simple or quite involved. In either case, it’s accomplished with a CREATE TABLE statement.
For our database example, let’s imagine a local auto repair business located in the small town of Springfield, owned and operated by the fictional Abraham Hanks. Abe employs mechanics who perform repairs on the automobiles in the fleets of Abe’s corporate customers. All of Abe’s customers are corporations. Repair jobs are recorded in invoices, which include charges for parts and labor. Charges are itemized on separate lines on the invoices. The mechanics hold certifications in such specialty areas as brakes, transmissions, electrical systems, and engines. Abe buys parts from multiple suppliers. Multiple suppliers could potentially supply the same part.
Table 2-1 shows the tables (and their attributes) that correspond to the relational model we came up with for Ol’ Honest Abe.
You can construct the DDL statements required to build the database tables directly from the enumeration of tables and columns in Table 2-1, but first you should understand the important topic of keys, which we discuss in the next section.
TABLE 2-1 Tables for Honest Abe
Table
Column
CUSTOMER
CustomerID
CustomerName
StreetAddr
City
State
PostalCode
ContactName
ContactPhone
ContactEmail
MECHANIC
EmployeeID
FirstName
LastName
StreetAddr
City
State
PostalCode
JobTitle
CERTIFICATION
CertificationNo
CertName
Expires
INVOICE
InvoiceNo
Date
CustomerID
EmployeeID
Tax
TotalCharge
INVOICE_LINE
Invoice_Line_No
PartNo
UnitPrice
Quantity
Extended Price
LaborChargeCode
LABOR
LaborChargeCode
TaskDescription
StandardCharge
PART
PartNo
Name
Description
CostBasis
ListPrice
QuantityInStock
SUPPLIER
SupplierID
SupplierName
StreetAddr
City
State
PostalCode
ContactName
ContactPhone
ContactEmail
SUPPLIER_PART
SupplierID
PartNo
Locating table rows with keys
Keys are the main tool used to locate specific rows within a table. Without a key — that handy item that guarantees that a row in a table is not a duplicate of any other row in the table — ambiguities can arise. The row you want to retrieve may be indistinguishable from one or more other rows in the table, meaning you wouldn’t be able to tell which one was the right one.
