Tuesday, February 06, 2007

Review Topic: SQL, various comments 1

It appears that a foreign key may or may not have the same column name as a column in the original table. Most textbook examples use a different name. In my own past experiences with MySQL, I found using the same column name and qualifying with the table name worked fine.

The WHERE clause is not required in a SELECT query.

A SELECT with a GROUP BY followed by a HAVING can apply different levels of elimination. On an Invoice table, the SELECT could eliminate orders less than a certain $$ amount, and then the HAVING could eliminate groups (say by customer number) that don't meet a minimum number of orders. Imagine such a SELECT, for example, in reporting on salesmen who did or did not meet a quota. The HAVING clause is coded at the end of the statement.

In DB2, NULL is always the last value in a collating sequence. It is like "missing" in SAS. A NULL value for a column in a row does not count in a true or false determination. The AVG function does not include the count of rows with NULL values in the denominator (it does appear to be included in COUNT).

You can code IS NULL but you do not code "= NULL" because NULL is not a value.

It is common for SQL examinations to ask how many rows are returned by a query, so the rules for NULL are important.

In Microsoft Access, the RUNSQL function in the Macro facility appears to requre the creation of a new table to be accepted.

No comments: