Chapter 7 Notes for Module 5

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.

How are errors created and what can be done to fix them?

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.

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).

Why 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.

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.

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.

What is 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.

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.

Why is it 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!).

What is a recursive join?

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