Tuesday, February 13, 2007
SQL review -- more topics
Microsoft Access SQL supports the crosstab funtionality of converting rows to columns through the TRANSFORM and PIVOT parameters. Here is a web reference. This does not appear to be part of standard ANSI.
When you design a query in Access with the query wizard, and then go into View in the toolbar, you can look at the SQL statement that it generates. In some cases, punctuation is added to make it easier to read.
There are some conventions in Ansi SQL that Access does not support, such as using an AS parameter directly in a subsequent WHERE clause.
Access documentation claims that it re-engineers correlated subqueries for more efficient operation (rather than redoing the whole subordinate query multiple times) when possible.
More on Ansi:
Note the difference between a DROP TABLE, where all rows are lost and are not recoverable, and a DELETE FROM followed by a DROP. The DELETE backs up every record on the log.
A foreign key can refer to a primary key or to a unique constraint. There is no order to a constraint.
A subquery in an IN list may reference only one column.
A JOIN (inner or outer) is not a set operation. A MINUS is a valid set operation.
Ansi supports single quotes only.
NULLs cannot be a key value.
Remember in removing data that you can't delete a row that is referenced for a foreign key.