Overview

This title is designed for anyone who needs to use SQL Structured Query Language with any database management system that supports the language. The book is equally useful to readers who have had little exposure to SQL and those with some experience. The title is packed with tips and tricks, along with basic information on how SQL works.

"...covers proper structure of SQL queries, how to produce a desired result & minimize burden on SQL command processors & how to modify data & database ...

See more details below
SQL Clearly Explained

Available on NOOK devices and apps  
  • NOOK Devices
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK Study
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
$58.95
BN.com price

Overview

This title is designed for anyone who needs to use SQL Structured Query Language with any database management system that supports the language. The book is equally useful to readers who have had little exposure to SQL and those with some experience. The title is packed with tips and tricks, along with basic information on how SQL works.

"...covers proper structure of SQL queries, how to produce a desired result & minimize burden on SQL command processors & how to modify data & database structure for ease of use & increased efficiency.

Read More Show Less

Editorial Reviews

Booknews
SQL is a computer language designed to manipulate relational databases. This introduction defines syntax, explains how SQL works, and covers proper structure of queries, how to produce a desired result and minimize burden on command processors, and how to modify data and database structure for ease of use and increased efficiency. The sample database used for examples is available on the author's web site. Annotation c. by Book News, Inc., Portland, Or.
From the Publisher

"This book functions as a very good tutorial, taking you from the basics through expert topics in a very straightforward manner."--Bill Arledge, Consulting DB2 Product Manager

"SQL Clearly Explained is one of those rare books that delivers 100 percent what its title promises. If you need to use SQL on the job, and who doesn't, then this book should be on your desk."--Craig S. Mullins, DB2 Consultant and IBM Information Champion, Mullins Consulting, Inc.

"This book is of great value to SQL practitioners. It also makes an excellent college-level text."--Rudy Limeback, SQL Consultant, r937.com

Read More Show Less

Product Details

Meet the Author

Jan L. Harrington, the author of 30 books, including SQL, Clearly Explained (Academic Press), has been writing about databases since 1984. She is a professor and chair of the department of computer science and information systems at Marist College, where she teaches database design and management, object-oriented programming, data communications, and computer architecture.

Read More Show Less

Read an Excerpt


Chapter One

The Relational Data Model

You don't need to be a database designer to use SQL successfully. However, you do need to know a bit about how relational databases are structured and how to manipulate those structures. This chapter therefore will acquaint you with the basic elements of the relational data model and its terminology. We'll finish by looking at the design of the sample database used throughout this book.

Schemas and entities

A database is a place where we store data, but there is more to it than that: We also store information about the relationships between pieces of data. The organization of a database is a logical concept rather than a physical one. Yes, there are files that store the data in a database, but the physical structure of those files usually isn't a concern for those who use the data.

The software that organizes, stores, retrieves, and analyzes database data is known as a database management system (DBMS). It isolates the user from the physical data storage mechanisms and structures and lets the user work with data in terms of the logical structure of the data.

The overall logical plan of a database is known as a schema. A schema has two types of elements:

* Entities: An entity is something about which we store data, such as a customer or a product or an order for a product. Entities are described by pieces of data known as attributes. When we have a collection of data for all the attributes of an entity, we say we have an occurrence of the entity. Databases actually store occurrences of entities. Schemas show us what entities will be in the database and what attributes are used to represent those entities. * Relationships: Relationships define how entities interact. For example, a customer entity is typically related to many order entities. There are three types of relationships, all of which we will discuss shortly.

The most important thing to keep in mind is that a schema shows the logical plan for a database, what entities and relationships could possibly be stored. However, inside the real-world database, we have many occurrences of many entities, each represented by descriptive data. We may not have occurrences of every entity in the schema or we may have thousands (even hundreds of thousands) of occurrences of entities.

Relations and Tables

A relational database takes its name from the structure used to represent an entity: a two-dimensional table with special characteristics taken from mathematical set theory, where such a structure is known as a relation. To begin, let's look at the simple relation in Figure 1-1. At first glance, the relation looks like any table, but unlike other tables you may have encountered (for example, rectangular areas of spreadsheets), it has some very specific characteristics.

Columns and Rows

A relation is a two-dimensional table with no repeating groups. That means that if you look at the intersection of a column and a row, there will be only one value. What you see in Figure 1-2 is certainly a table, but it isn't a relation. Why? Because there are multiple values in some of the rows in the Children column. In contrast, Figure 1-1 is a legal relation.

Note: Although the official name of the two-dimensional "thing" we have been discussing is "relation," most people consider the word "table" to be synonymous and we will use both terms interchangeably throughout this book.

A relation has a name that is unique within its schema. Each column (or attribute) in a relation also has a name, but in this case, the name needs to be unique only within the table. In fact, as you will see shortly, there are times when you actually want to have columns with the same names in multiple tables.

In a well-designed relational database, each table represents an entity. We often document entities (and, as you will see, the relationships among them) in a diagram known as an entity-relationship diagram (ERD). There are many ways to draw ERDs, each of which can convey just about the same information. The particular style we'll be using in this book is known as the information engineering (IE) style. An entity is represented as a rectangle with its name in the top section and its attributes in the bottom, as you see in Figure 1-3.

A relation is both column-order independent and row-order independent. This mean that we can view the columns in any order and the rows in any order without losing the meaning of the data. The assumption is, however, that all the data in one row remain in that row.

Domains

Each column in a relation has a domain, an expression of the legal values for that column. In some cases, a domain is very specific. For example, if you are working with a column that stores the sizes of T-shirts, the entire domain might consist of the values S, M, L, XL, and XXL. Domains are more commonly, however, general data types, such as integer or date.

Once you assign a domain to a column, the DBMS will enforce that domain, rejecting any command that attempts to enter a value into the column that isn't from the domain. This is an example of a constraint on a relation, a rule to which the relation must adhere.

Primary Keys

Each row in a relation must have a unique value that identifies the row. This primary key is made up of the values in one or more columns (the smallest number of columns needed to enforce uniqueness). A table that stores information about an order, for example, would probably use the order number as its primary key.

People are particularly difficult to identify uniquely, so we often assign each person in a table an arbitrary number. If you look back at Figure 1-3, you will see that there is a customer_numb attribute, representing a number that will be simply given to each customer when a row for a new customer is entered into the table. The IE diagramming method places an asterisk in front of the column or columns that make up a primary key, just as is done in Figure 1-3.

Sometimes there is no single column that will uniquely identify each row in a table. As an example, consider the table in Figure 1-4 (dependents), which lists employees' dependent children. We can't use the employee number as the primary key because customer numbers repeat for each child an employee has, and many employees have more than one child. By the same token, the children's names and birthdates aren't unique. The solution is to consider the values in two columns as the primary key. In this case, the employee number and the child's name make the best primary key. Taken as a unit, the two values are unique in every row. A primary key made up of more than one column is known as a concatenated key.

Why are unique primary keys so important? Because they ensure that you can retrieve every piece of data that you put into a database. If primary keys aren't unique, a query will retrieve one or more rows with a value you specify, but you can't be certain which is the exact row you want unless you know something that identifies just that one row. In fact, you should be able to retrieve any single data value knowing three things: the name of the table, the name of the column, and the primary key of the row.

As you will see later in this book, you specify a table's primary key when you define the table to the DBMS. The DBMS will then enforce a constraint that requires unique primary key values.

Note: It is actually possible to create a table that has no primary key, but some DBMSs won't let you put any data in it.

Nulls

Sometimes you don't put data in some columns of some rows because you don't know the appropriate data values. The empty columns don't contain a zero or a blank. Instead, they contain a special indicator known as null, which means "unknown."

There are two important implications of the presence of nulls in a table. First, we can't allow nulls as all or part of a primary key. If there is only one row with null for a primary key, then the property of unique primary key values is preserved. The minute we introduce a second row with a null primary key, however, the primary keys are no longer unique. A DBMS will therefore ensure that all primary keys have values, a constraint known as entity integrity.

Secondly, nulls can affect the result of queries. Assume, for example, that you want to retrieve the names of all employees who have a salary of more than $100,000. For all employees that have a value in the salary column, the answer to "Is the salary more than $100,000" will be either "yes" or "no." But if the salary column contains null, the DBMS doesn't know the answer to the question; the result is "maybe."

We say that a DBMS operates using three-valued logic: yes, no, or maybe. The question that remains is what a DBMS should do when the answer to the question it is asking is "maybe." Should it retrieve rows with null or leave them out? The relational data model doesn't specify exactly what a DBMS should do, but does require the DBMS to act consistently—either always retrieve rows with nulls or always leave them out—and that the user be aware of what is happening. We'll deal with effect of nulls at various places throughout this book.

Base versus Virtual Tables

There are two primary types of tables with which you will be working when you use SQL. The tables that contain data that are stored in the database are known as base tables. However, the DBMS also uses several types of temporary tables that only exist in main memory. These are virtual tables and by definition they are not stored in the database. Most modern DBMS use several types of virtual tables, including views, temporary tables, and query result tables. If you want to keep the data in a virtual table, then those data must be inserted into a base table.

Representing Relationships

Along with data describing entities, a database must somehow represent relationships between entities. Prior to the relational data model, databases used data structures embedded in the data to show relationships. However, the relational data model relies on it data to show relationships.

Types of Relationships

There are three types of relationships between entities that we encounter in our database environments: one-to-one, one-to-many, and many-to-many.

One-to-One Relationship

A one-to-one relationship exists between two entities when an occurrence of entity A is related to zero or one occurrences of entity B and an occurrence of entity B is related to zero or one occurrences of entity A. Although the specific occurrences involved in the relationship may change over time, there is never more than one related occurrence at any given time. For example, a car and its engine have unique serial numbers. At any one time, an engine is installed in only one car; at the same time, a car has only one engine. The engine may be in no car or it can be moved from one car to another, but it can't be in more than one place at a time. By the same token, a car can have no engine or one engine. The specific engine may change over time but there is never more than one.

We include a relationship in an ERD by drawing a line between the rectangles for two related entities. The line ends identify the type of the relationship. In Figure 1-5 you can see the way in which we would diagram the one-to-one relationship between a car and its engine. The |0 at the end of the line means "zero or one."

If the relationship is required (mandatory), then the |0 at the end of the line changes to || (one and only one). We use mandatory relationships when we don't want an occurrence of an entity to be store in the database unless it is related to an occurrence of the entity at the other end of the relationship. For example, if we didn't want an engine in the database unless that engine was in a car, the end of the line next to the car entity would be ||.

(Continues...)

Read More Show Less

Table of Contents

SQL Clearly Explained


By Jan L. Harrington

MORGAN KAUFMANN

Copyright © 2010 Elsevier Inc.
All right reserved.

ISBN: 978-0-12-375698-5

Contents

Chapter 1: The Relational Data Model....................3
Chapter 2: Relational Algebra....................29
Chapter 3: Introduction to SQL....................65
Chapter 4: Simple SQL Retrieval....................77
Chapter 5: Retrieving Data From More Than One Table....................107
Chapter 6: Advanced Retrieval Operations....................131
Chapter 7: Working with Groups of Rows....................161
Chapter 8: Data Modification....................197
Chapter 9: Schemas and Tables....................211
Chapter 10: Views, Temporary Tables, CTEs, and Indexes....................237
Chapter 11: Keeping the Design Up to Date....................255
Chapter 12: Users and Access Rights....................263
Chapter 13: Users, Sessions, and Transaction Control....................273
Chapter 14: Writing and Executing SQL Routines and Modules—Triggers and Stored Procedures....................287
Chapter 15: Embedded SQL....................301
Chapter 16: Dynamic SQL....................323
Chapter 17: XML Support....................339
Chapter 18: The Object-Relational Data Model....................363
Chapter 19: Object-Relational Support....................401
Appendix A: Common Acronyms and Abbreviations....................419
Appendix B: SQLSTATE Return Codes....................421
Appendix C: SQL Syntax Summary....................433
Glossay....................445
Index....................455
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)