Fall Semester 2002

• Use simple and compound conditions in SQL.
• Use computed fields in SQL.
• Use built-in SQL functions.
• Use subqueries in SQL.
• Group records in SQL.
• Join tables using SQL.
• Perform union operations in SQL.
• Use SQL to update database data.
• Use an SQL query to create a table in a database

Key Terms

• aggregate function
• built-in function
• CHAR(n)
• command
• compound condition
• CREATE TABLE
• DATE
• DECIMAL(p,q)
• DELETE
• FROM clause
• GROUP BY clause
• HAVING clause
• INSERT
• INTEGER
• INTO clause
• key
• ORDER BY clause
• reserved word
• SELECT clause
• simple condition
• SMALLINT
• sort key
• SQL (Structured Query Language)
• subquery
• UPDATE
• WHERE clause

Chapter Outline

I.	Introduction
II.	Table Creation
III.	Simple Retrieval
IV.	Compound Conditions
V.	Computed Fields
VI.	Using Special Operators (LIKE and IN)
VII.	Sorting
VIII.	Built-In Functions
IX.	Nesting Queries
X.	Grouping
XI.	Joining Tables
XII.	Union
XIII.	Updating Tables
XIV.	Creating a Table from a Query
XV.	Summary of SQL Commands
XVI.	Summary
XVII.	Key Terms
XVIII.	Review Questions
XIX.	Premiere Products Exercises
XX.	Henry Books Case

Table Creation

Structured Query Language (SQL) is a data manipulation language that requires users to enter commands to obtain the desired result. You use the SQL command CREATE TABLE to describe the layout of a table. Following the word TABLE is the name of the table, and then the names and data types of the columns included in the table. Restrictions for naming tables and columns are: names cannot exceed 18 characters; names must start with a letter; names can contain only letters, numbers, and underscores (_); names cannot contain spaces. You must assign each field a data type to indicate what kind of data will be stored in the field. The following data types are common to most implementations of SQL and are described in the text: INTEGER, SMALLINT, DECIMAL (p,q), CHAR (n), and DATE.

Simple Retrieval

The basic form of an SQL command to retrieve data is SELECT-FROM-WHERE. Following the word SELECT, you list the names of the fields to display in the query result. After the word FROM, you list the table or tables that contain the data to display. The WHERE clause is optional and is used to list any conditions that apply to the data you want to retrieve. When you use the WHERE clause, the condition has the form: field name, comparison operator, and then either another field name or a value.

Compound Conditions

Compound conditions are formed by connecting two or more simple conditions using one or both of the following operators: AND and OR. You also can precede a single condition with the NOT operator to negate a condition.

Computed Fields

A computed (calculated) field is a field that is not in the database but whose value you can calculate based on an existing field or fields in the database. Computed fields can involve addition, subtraction, multiplication, and division.

Using Special Operators (LIKE and IN)

The LIKE operator is used in conditions that do not involve exact matches. You use wildcard symbols with the LIKE operator to find values that contain a certain collection of characters. In Access SQL, the asterisk (*) is the wildcard that represents any collection of characters. In some versions of SQL, the percent symbol (%) is used instead. To represent a single character, Access SQL uses the question mark (?) wildcard while other versions of SQL use the underscore (_). The IN operator provides a concise way of phrasing compound conditions.

Sorting

To sort table data, you use the ORDER BY clause. The field on which data is to be sorted is called a sort key, or simply a key. When data is sorted on more than one field, the more important key is called the major key (or the primary sort key) and the less important key is called the minor key. When two keys are used, the major key is listed first.

Built-In Functions

SQL includes built-in functions (aggregate functions) for Count, Sum, Avg, Max, and Min.

Nesting Queries

When you place one query inside of another query, the inner query is called a subquery. The subquery is evaluated first and then the outer query is evaluated.

Grouping

Grouping is the process of creating groups (or collections) of records that share some common characteristic. The number of groups depends on the number of different values for the selected field. The GROUP BY clause groups records, and the ORDER BY clause orders records in the desired order. To add a restriction to a group you use the HAVING clause.

Joining Tables

When you need data from more than one table, you must join the tables using rows in two tables that have identical values in matching fields. The SELECT clause indicates the fields to display; the FROM clause indicates the tables involved in the query; and the WHERE clause indicates any conditions that will restrict the data. When there is potential ambiguity when listing field names, you must qualify them using the format table name.field name (where the table name and field name are separated by a period).

Union

The union of two tables in SQL is a table containing all rows that are in either the first table, the second table, or both. Tables must be union-compatible, with the same number of fields with corresponding data types. Some implementations of SQL will remove duplicate rows in union operations between tables.

Updating Tables

The SQL command UPDATE allows you to change or update data. The INSERT INTO command is used to add data to the database. The DELETE command is used to remove data from the database.

Creating a Table from a Query

You can create a new table by including the INTO clause in a SELECT-FROM-WHERE statement.

Summary of SQL Commands

This section includes generic versions of SQL commands for every example in this chapter. Where commands are different in other SQL versions, both the Access SQL command and the generic SQL command are shown.

The importance of learning SQL

Most corporate database management systems, such as Oracle and Sybase use SQL.