SQL for Database Construction Questions and Answers

Answer: A

Correct answer: change the table structure.

ALTER TABLE is a data definition statement: it adds or drops columns, changes data types and adds or removes constraints. Changing the data is the business of INSERT, UPDATE and DELETE, and confusing the two families is the error the distractors are built on.

Answer: C

Correct answer: to accomplish both of the above.

The same mechanism serves both purposes. Omitting columns a user does not need makes the result easier to read and to work with, and omitting columns a user should not see keeps salaries or identifiers out of reach. The user is granted rights on the view and none on the underlying table, so the restriction cannot be circumvented.

Answer: B

Correct answer: The CHECK constraint

CHECK attaches a condition to a column or a table that every row must satisfy, so values outside a range, outside a list, or inconsistent with another column in the same row are rejected at the DBMS rather than by the application. Enforcing it in the database means it holds however the data arrives.

Answer: C

Correct answer: ALTERNATE KEY

SQL has PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK. There is no ALTERNATE KEY constraint: an alternate key is a design concept, and it is enforced in SQL by declaring the column UNIQUE, which is exactly what an alternate key requires.

Answer: A

Correct answer: True

A view is defined by a SELECT, and a SELECT may compute columns, so a view can present totals, margins or converted units as though they were stored. The advantage is consistency: the expression is written once and everyone who uses the view gets the same definition.

Answer: C

Correct answer: ORDER BY.

SQL-92 forbids ORDER BY in a view definition, on the ground that a view is a relation and a relation has no inherent order; the ordering belongs to the query that reads the view. WHERE, GROUP BY and FROM are all permitted. Oracle relaxes the restriction, which is a departure from the standard rather than the standard itself.

Answer: A

Correct answer: System date

The three common forms are range checks, lists of permitted values, and comparisons between two columns of the same row. Comparing against the system date is not among them and is in fact problematic, since the condition would be evaluated afresh on every check and a row valid when inserted could become invalid with the passage of time.

Answer: B

Correct answer: False

A computed column has no counterpart in any base table, so there is no stored value for an update to change and the DBMS cannot work backwards from the result to the inputs. Such a view is read-only in that column, which is one of the standard restrictions on view updatability.

Answer: B

Correct answer: stored procedure

A stored procedure is a program held in the database and called by name, performing some common action on the data. It differs from a trigger in being invoked deliberately rather than fired by an event, and in belonging to the database rather than being attached to one table.

Answer: B

Correct answer: False

SQL-92 views may contain GROUP BY; a view that aggregates is perfectly legal, though it will not generally be updatable. What SQL-92 forbids in a view definition is ORDER BY, since a view is a relation and a relation has no order.