Advanced SQL Questions and Answers

Answer: A

Correct answer: True

Holding the code in the database means one copy serves every application that reaches the data, so a rule cannot be implemented differently in two places or omitted from one. That central administration is the principal argument for putting logic into triggers and routines rather than into each application.

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.

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.

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.

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.

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.

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.

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.

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.

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.