Which of the following statements is true concerning routines and triggers?
Answer: A
Correct answer: Both consist of procedural code.
Both are blocks of procedural code stored in the database. What separates them is how they are invoked: a routine must be called explicitly, whereas a trigger fires by itself when its event occurs. Neither option describing invocation can therefore be true of both.
Please log in or register to report this question.
When AUTOCOMMIT is set on, changes will be made automatically at the end of each SQL statement.
Answer: A
Correct answer: True
With AUTOCOMMIT on, each statement is its own transaction and is committed as soon as it completes. Multi-statement transactions therefore require it to be turned off, or an explicit transaction to be begun, so that the statements are committed together by COMMIT or undone together by ROLLBACK.
Please log in or register to report this question.
Triggers are stored blocks of code that have to be called in order to operate.
Answer: B
Correct answer: False
A trigger fires by itself when its event occurs, which is the whole point of it: no application can bypass the rule by forgetting to call something. Stored blocks that must be called are procedures and functions, and the statement has attributed their behaviour to triggers.
Please log in or register to report this question.
With the UNION clause, each query involved must output the same number of columns, and they must be UNION compatible.
Answer: A
Correct answer: True
The requirement has two parts and both are needed. The queries must produce the same number of columns, and the corresponding columns must be of compatible types - which together is what union compatibility means. Without it the rows could not be stacked into one result at all.
Please log in or register to report this question.
Which of the following is true concerning systems information in an RDBMS?
Answer: D
Correct answer: All of the above.
The catalogue is itself held as tables, so it can be queried with ordinary SELECT statements - which is how a developer discovers what columns a table has. It is maintained by the system in response to DDL, so it is not directly updatable; altering it by hand would leave the database inconsistent with itself.
Please log in or register to report this question.
The joining technique is useful when data from several relations are to be retrieved and displayed and the relationships are not necessarily nested.
Answer: A
Correct answer: True
Joining is the natural technique when several relations must contribute columns to one result and the relationships between them are of equal standing. A subquery suits a genuinely nested question - find the customers whose orders exceed some computed figure - where one result feeds another.
Please log in or register to report this question.
Outer join is the same as equi-join, except one of the duplicate columns is eliminated in the result table.
Answer: B
Correct answer: False
Eliminating the duplicate column is what distinguishes the natural join from the equi-join. An outer join does something else entirely: it keeps the rows that have no match on the other side, filling their missing columns with nulls. The statement has attached one join’s definition to another’s name.
Please log in or register to report this question.
Embedded SQL is which of the following?
Answer: A
Correct answer: Hard-coded SQL statements in a program language such as Java.
Embedded SQL is written into the source of a host program and fixed at compile time. It contrasts with dynamic SQL, which the program builds as a string while running - more flexible, but unable to be checked in advance and open to injection if the string is built from input.
Please log in or register to report this question.
Subqueries can be nested multiple times.
Answer: A
Correct answer: True
A subquery may itself contain a subquery, to whatever depth the problem requires and the product permits. Depth is limited in practice by readability rather than by the language, and a deeply nested query is often clearer rewritten with joins or with a common table expression.
Please log in or register to report this question.
A correlated subquery is where the outer query depends on data from the inner query.
Answer: B
Correct answer: False
The dependence runs the other way. In a correlated subquery the inner query refers to a column of the outer, so it must be evaluated afresh for each row the outer query considers. That is what makes it correlated, and what makes it potentially expensive.
Please log in or register to report this question.
