Advanced SQL Questions and Answers

Q: Which of the following is true concerning triggers?

Correct answer: They have an event, condition, and action.The three parts define a trigger: the event that fires it, an optional condition restricting when it acts, and the action it performs. It is created with SQL, applies to every route into the database rather than to particular applications, and can perfectly well cause another trigger to fire.

Q: The code to create triggers and routines is stored in only one location and is administered centrally.

Correct answer: TrueHolding 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.

Q: Which of the following statements is true concerning routines and triggers?

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.

Q: When AUTOCOMMIT is set on, changes will be made automatically at the end of each SQL statement.

Correct answer: TrueWith 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.

Q: Triggers are stored blocks of code that have to be called in order to operate.

Correct answer: FalseA 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.

Q: With the UNION clause, each query involved must output the same number of columns, and they must be UNION compatible.

Correct answer: TrueThe 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.

Q: Which of the following is true concerning systems information in an RDBMS?

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.

Q: The joining technique is useful when data from several relations are to be retrieved and displayed and the relationships are not necessarily nested.

Correct answer: TrueJoining 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.

Q: Outer join is the same as equi-join, except one of the duplicate columns is eliminated in the result table.

Correct answer: FalseEliminating 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.

Which of the following is true concerning triggers?

A.You do not create them with SQL.
B.They execute against only some applications that access a database.
C.They have an event, condition, and action.
D.They cannot cascade (cause another trigger to fire).

Answer: C

Correct answer: They have an event, condition, and action.

The three parts define a trigger: the event that fires it, an optional condition restricting when it acts, and the action it performs. It is created with SQL, applies to every route into the database rather than to particular applications, and can perfectly well cause another trigger to fire.

Discuss in Forum

A UNION query is which of the following?

A.Combines the output from no more than two queries and must include the same number of columns.
B.Combines the output from no more than two queries and does not include the same number of columns.
C.Combines the output from multiple queries and must include the same number of columns.
D.Combines the output from multiple queries and does not include the same number of columns.

Answer: C

Correct answer: Combines the output from multiple queries and must include the same number of columns.

UNION stacks the result sets, so the queries must agree in the number of columns and in the types of the corresponding ones - the union compatibility requirement. There is no limit of two: any number of SELECTs may be chained with UNION.

Discuss in Forum

The code to create triggers and routines is stored in only one location and is administered centrally.

A.True
B.False

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.

Discuss in Forum

Which of the following statements is true concerning routines and triggers?

A.Both consist of procedural code.
B.Both have to be called to operate.
C.Both run automatically.
D.Both are limited to a single SQL statement.

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.

Discuss in Forum

When AUTOCOMMIT is set on, changes will be made automatically at the end of each SQL statement.

A.True
B.False

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.

Discuss in Forum

Triggers are stored blocks of code that have to be called in order to operate.

A.True
B.False

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.

Discuss in Forum

With the UNION clause, each query involved must output the same number of columns, and they must be UNION compatible.

A.True
B.False

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.

Discuss in Forum

Which of the following is true concerning systems information in an RDBMS?

A.RDBMS store database definition information in system-created tables.
B.This information can be accessed using SQL.
C.This information often cannot be updated by a user.
D.All of the above.

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.

Discuss in Forum

The joining technique is useful when data from several relations are to be retrieved and displayed and the relationships are not necessarily nested.

A.True
B.False

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.

Discuss in Forum

10.

Outer join is the same as equi-join, except one of the duplicate columns is eliminated in the result table.

A.True
B.False

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.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Advanced SQL Questions and Answers || Advanced SQL MCQ Questions and Answers || Advanced SQL GK Questions and Answers || Advanced SQL GK MCQ Questions || Advanced SQL Multiple Choice Questions and Answers || Advanced SQL GK || GK on Advanced SQL || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Advanced SQL Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications