What is an SQL virtual table that is constructed from other tables?
Answer: B
Correct answer: A view
A view is virtual: nothing is stored but the SELECT that defines it, and the rows are produced when it is queried. That is what makes it always current and what distinguishes it from a table, which holds data, and from query results, which are a one-off answer rather than a named object.
Please log in or register to report this question.
When using the SQL INSERT statement:
Answer: D
Correct answer: rows can either be inserted into a table one at a time or in groups.
INSERT takes either a VALUES clause for a single row or a SELECT in place of it, in which case every row the query returns is inserted. The second form is how a table is populated in bulk from another, and it is why loading data does not require a row-by-row loop.
Please log in or register to report this question.
What is not an advantage of stored procedures?
Answer: D
Correct answer: Increased network traffic
Stored procedures reduce network traffic, since only the call and its parameters cross the network instead of the statements and their intermediate results. The other three are genuine advantages: permissions can be granted on the procedure rather than the tables, the plan is compiled and reused, and the code is written once and shared.
Please log in or register to report this question.
A reason for using an SQL view to hide columns is:
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.
Please log in or register to report this question.
Which of the following is an SQL trigger supported by Oracle?
Answer: D
Correct answer: All of the above.
Oracle supports BEFORE, AFTER and INSTEAD OF triggers. The BEFORE trigger is the one that distinguishes it from SQL Server, which has only the other two, and it is convenient for validating or altering values before they are written.
Please log in or register to report this question.
The SQL ALTER statement can be used to:
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.
Please log in or register to report this question.
What SQL structure is used to limit column values of a table?
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.
Please log in or register to report this question.
Which is NOT one of the most common types of SQL CHECK constraints?
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.
Please log in or register to report this question.
What is an advantage of placing computations in SQL views?
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.
Please log in or register to report this question.
Views constructed from SQL SELECT statements that conform to the SQL-92 standard may not contain:
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.
Please log in or register to report this question.
