Chapter 3: The Relational Model 2: SQL

CHAR(n)

The SQL data type for character data.

SQL (Structured Query Langauge)

A very popular relational data definition and manipulation language that is used in many relational DBMSs.

How do you write a compound condition in an SQL query? When is a compound condition true?

A compound condition is formed by connecting two or more simple conditions using one or both of the following operators: AND and OR. You can also precede a single condition with the NOT operator to negate a condition. When you connect simple conditions using the AND operator, all the simple conditions must be true for the compound condition to be true. When you connect simple conditions using the OR operator, the compound condition will be true whenever any of the simple conditions are true.

simple condition

A condition that involves only a single field and a single value.

reserved word

A word that is part of the SQL language.

command

An instruction by a user that directs a database to perform a certain action.

subquery

In SQL, a query that appears within another query.

compound condition

See compound criteria. (Two simple criteria (conditions) in a query that are combined with the AND or OR operators.)

INTO clause

The SQL clause that inserts values into a table. An INTO clause consists of the word INTO followed by the name of the table to insert the values into.

INSERT

The SQL command to add new data to a table. After the words INSERT INTO, you list the name of the table, followed by the word VALUES. Then you list the values for each of the columns in parentheses.

DELETE

The SQL command used to delete a table. The word DELETE is followed by a FROM clause identifying the table. Use a WHERE clause to specify a condition. Any records satisfying the condition will be deleted.

CREATE TABLE

The SQL command used to describe the layout of a table. The word TABLE is followed by the name of the table to be created and then by the names and data types of the columns (fields) that comprise the table.

UPDATE

The SQL command used to make changes to existing table data. After the word UPDATE, you indicate the table to be updated. After the word SET, you indicate the field to be changed, followed by an equals sign and the new value. Finally, you can include a condition in the WHERE clause, in which case, only the records that satisfy the condition will be changed.

DATE

The SQL data type for date data.

DECIMAL(p,q)

The SQL data type for decimal data.

SMALLINT

The SQL data type for integer data for small integers.

INTEGER

The SQL data type for integer data.

GROUP BY clause

The part of an SQL SELECT com- mand that indicates grouping.

ORDER BY clause

The part of an SQL SELECT command that indicates a sort order.

SELECT clause

The part of an SQL SELECT command that indicates the columns to be included in the query results.

WHERE clause

The part of an SQL SELECT command that indicates the condition rows must satisfy to be displayed in the query results.

FROM clause

The part of an SQL SELECT command that indicates the tables in the query.

HAVING clause

The part of an SQL SELECT command that restricts the groups to be displayed.

Describe the three update commands in SQL.

The update commands in SQL are INSERT, which inserts new rows in a table; UPDATE, which changes all the rows in a table that satisfy some condition; and DELETE, which deletes all the rows in a table that satisfy some condition.

Describe the process of creating a table in SQL and the different data types you can use for fields.

To create a table in SQL, use a CREATE TABLE command. The word TABLE is followed by the name of the table to be created and then by the names and data types of the columns (fields) that make up the table. The data types you can use are: INTEGER (large negative and positive whole numbers), SMALLINT (whole numbers from -32,768 to 32,767), DECIMAL (numbers that have a decimal part), CHAR (alphanumeric strings), and DATE (date values)

How do you group data in SQL? When you group data in SQL, are there any restrictions on the items that you can include in the SELECT clause? Explain.

To group data in SQL, include the words GROUP BY followed by the field or fields on which the data is to be grouped in the query results. If you group data, you can only include the fields on which you are grouping or statistics in the SELECT clause.

How do you qualify the name of a field in an SQL query? When is it necessary to do so?

To qualify the name of a field in an SQL query, precede the field with the name of the table to which it belongs, followed by a period. It is necessary to qualify a field if the field name occurs in more than one of the tables listed in the FROM clause.

How do you use the LIKE and IN operators in an SQL query?

To use the LIKE or IN operators in an SQL query, include them in the WHERE clause. A charac- ter string containing one or more wildcards is included after the word LIKE. The word IN is fol- lowed by a list of values.

What are the SQL built-in functions? How do you use them in an SQL query?

Use an SQL built-in function (COUNT, SUM, AVG, MAX, and MIN) by including it in the SELECT clause followed by the name of the field to which it applies.