SQL for Database Construction Questions and Answers

Answer: A

Correct answer: True

CREATE VIEW gives the view a name and the SELECT that defines it, and nothing but that definition is stored. The rows are produced when the view is queried, which is what keeps it current and what makes it a virtual table rather than a copy.

Answer: C

Correct answer: both of the above can be hidden by an SQL view.

A view can restrict which columns and which rows a user sees, which serves security and simplicity at once; and because the view is defined by a SELECT, the joins, subqueries and computations in that statement are hidden behind a name. Users then query the view as though it were a simple table.

Answer: C

Correct answer: To accomplish both of the above.

Both reasons matter and the second is the stronger. Putting the expression in the view saves every user from writing it, and more importantly ensures they all use the same one - so a margin or a total means the same thing wherever it is quoted, and a change to the definition takes effect everywhere at once.

Answer: D

Correct answer: All of the above are uses for SQL views.

All three are standard uses and they often coincide. Restricting columns hides sensitive data and simplifies the result; restricting rows limits what a user may see; and wrapping a complicated statement behind a name means a user can query it as a simple table without knowing the joins beneath.

Answer: A

Correct answer: True

An insert through a view supplies values only for the columns the view exposes, so a required column omitted from the view would be left null in the base table and the insert would fail. The view can therefore be used for reading and for updating existing rows, but not for inserting new ones.

Answer: B

Correct answer: False

A stored procedure is by definition stored and executed in the database; distributing it to clients would defeat both of its advantages, the reduction in network traffic and the single point of maintenance. What can be distributed is a call to it, which is all the client needs to know.

Answer: A

Correct answer: True

The JOIN ... ON syntax expresses the join condition explicitly and supports INNER, LEFT, RIGHT and FULL OUTER forms. It is preferred to putting the condition in the WHERE clause because the join and the filtering are then visibly separate, and because an outer join cannot be written the older way at all.

Answer: B

Correct answer: False

A trigger is attached to a particular table or view, not to the database as a whole, and it fires on an event affecting that object. A program belonging to the database and called explicitly is a stored procedure, and keeping the two apart matters because they are invoked in quite different ways.

Answer: B

Correct answer: False

No view has data of its own, updatable or not. A view stores only its defining SELECT, and an update through it is translated into an update on the underlying tables. That is precisely why updatability is restricted: the DBMS must be able to identify the row of the base table to which the change applies.

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.