Introduction to Structured Query Language (SQL) Chapter 7 Answers to Review Questions

Chapter 7

Introduction to Structured Query Language (SQL)

NOTE

Several points are worth emphasizing:

· We have provided the SQL scripts for both chapters 7 and 8. These scripts are intended to

facilitate the flow of the material presented to the class. However, given the comments made

by our students, the scripts should not replace the manual typing of the SQL commands by

students. Some students learn SQL better when they have a chance to type their own

commands and get the feedback provided by their errors. We recommend that the students use

their lab time to practice the commands manually.

· Because this chapter focuses on learning SQL, we recommend that you use the Microsoft

Access SQL window to type SQL queries. Using this approach, you will be able to

demonstrate the interoperability of standard SQL. For example, you can cut and paste the same

SQL command from the SQL query window in Microsoft Access, to Oracle SQL * Plus and to

MS SQL Query Analyzer. This approach achieves two objectives:

Ø It demonstrates that adhering to the SQL standard means that most of the SQL code

will be portable among DBMSes.

Ø It also demonstrates that even a widely accepted SQL standard is sometimes

implemented with slight distinctions by different vendors. For example, the treatment

of date formats in Microsoft Access and Oracle is slightly different.

Answers to Review Questions

1. In a SELECT query, what is the difference between a WHERE clause and a HAVING clause?

Both a WHERE clause and a HAVING clause can be used to eliminate rows from the results of a

query. The differences are 1) the WHERE clause eliminates rows before any grouping for aggregate

functions occurs while the HAVING clause eliminates groups after the grouping has been done, and

2) the WHERE clause cannot contain an aggregate function but the HAVING clause can.

2. Explain why the following command would create an error, and what changes could be made

to fix the error.

SELECT V_CODE, SUM(P_QOH) FROM PRODUCT;

The command would generate an error because an aggregate function is applied to the P_QOH

attribute but V_CODE is neither in an aggregate function or in a GROUP BY. This can be fixed by

either 1) placing V_CODE in an appropriate aggregate function based on the data that is being

requested by the user, 2) adding a GROUP BY clause to group by values of V_CODE (i.e. GROUP

BY V_CODE), 3) removing the V_CODE attribute from the SELECT clause, or 4) removing the

Sum aggregate function from P_QOH. Which of these solutions is most appropriate depends on the

question that the query was intended to answer.

240

Chapter 7 An Introduction to Structured Query Language (SQL)

3. What type of integrity is enforced when a primary key is declared?

Creating a primary key constraint enforces entity integrity (i.e. no part of the primary key can

contain a null and the primary key values must be unique).

4. Explain why it might be more appropriate to declare an attribute that contains only digits as a

character data type instead of a numeric data type.

An attribute that contains only digits may be properly defined as character data when the values are

nominal; that is, the values do not have numerical significance but serve only as labels such as ZIP

codes and telephone numbers. One easy test is to consider whether or not a leading zero should be

retained. For the ZIP code 03133, the leading zero should be retained; therefore, it is appropriate to

define it as character data. For the quantity on hand of 120, we would not expect to retain a leading

zero such as 0120; therefore, it is appropriate to define the quantity on hand as a numeric data type.

5. What is the difference between a column constraint and a table constraint?

A column constraint can refer to only the attribute with which it is specified. A table constraint can

refer to any attributes in the table.

6. What are “referential constraint actions”?

Referential constraint actions, such as ON DELETE CASCADE, are default actions that the DBMS

should take when a DML command would result in a referential integrity constraint violation.

Without referential constraint actions, DML commands that would result in a violation of referential

integrity will fail with an error indicating that the referential integrity constrain cannot be violated.

Referential constraint actions can allow the DML command to successfully complete while making

the designated changes to the related records to maintain referential integrity.

7. Rewrite the following WHERE clause without the use of the IN special operator.

WHERE V_STATE IN (‘TN’, ‘FL’, ‘GA’)

WHERE V_STATE = 'TN' OR V_STATE = 'FL' OR V_STATE = 'GA'

Notice that each criteria must be complete (i.e. attribute-operator-value).

8. Explain the difference between an ORDER BY clause and a GROUP BY clause.

An ORDER BY clause has no impact on which rows are returned by the query, it simply sorts those

rows into the specified order. A GROUP BY clause does impact the rows that are returned by the

query. A GROUP BY clause gathers rows into collections that can be acted on by aggregate

functions.

9. Explain why the two following commands produce different results.

SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT;

241

Chapter 7 An Introduction to Structured Query Language (SQL)

SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;

The difference is in the order of operations. The first command executes the Count function to count

the number of values in V_CODE (say the count returns "14" for example) including duplicate

values, and then the Distinct keyword only allows one count of that value to be displayed (only one

row with the value "14" appears as the result). The second command applies the Distinct keyword to

the V_CODEs before the count is taken so only unique values are counted.

10. What is the difference between the COUNT aggregate function and the SUM aggregate

function?

COUNT returns the number of values without regard to what the values are. SUM adds the values

together and can only be applied to numeric values.

11. Explain why it would be preferable to use a DATE data type to store date data instead of a

character data type.

The DATE data type uses numeric values based on the Julian calendar to store dates. This makes

date arithmetic such as adding and subtracting days or fractions of days possible (as well as

numerous special date-oriented functions discussed in the next chapter!).

12. What is a recursive join?

A recursive join is a join in which a table is joined to itself.

Problem Solutions

O n l i n e C o n t e n t

Problems 1 – 25 are based on the Ch07_ConstructCo database located in the Premium Website. This database

is stored in Microsoft Access format. If you use another DBMS such as Oracle, SQL Server, MySQL, or DB2, use

its import utilities to import the Access database contents. The Premium Website provides Oracle and SQL script

files.

The Ch07_ConstructCo database stores data for a consulting company that tracks all charges to

projects. The charges are based on the hours each employee works on each project. The structure and

contents of the Ch07_ConstructCo database are shown in Figure P7.1.

Figure P7.1 Structure and contents of the Ch07_ConstructCo database