Advanced SQL Questions and Answers

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.

Q: Subqueries can be nested multiple times.

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

Q: A correlated subquery is where the outer query depends on data from the inner query.

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

Q: ROLLBACK WORK asks SQL to empty the log file.

Correct answer: TrueROLLBACK WORK undoes the transaction by applying the before-images from the log and then discards them, so the log entries for that transaction are emptied. The database returns to the state it held when the transaction began.

Q: What type of join is needed when you wish to include rows that do not have matching values?

Correct answer: Outer joinAn outer join keeps the rows that have no match on the other side and fills the missing columns with nulls. Equi-joins and natural joins discard them, so a customer with no orders would simply disappear - which is precisely the case an outer join exists to report.

Q: There should be one condition within the WHERE clause for each pair of tables being joined.

Correct answer: TrueEach pair of tables joined needs a condition linking them, so joining n tables requires at least n minus one conditions. Omitting one leaves that pair unconstrained and produces a Cartesian product with the rest, which is why a query that suddenly returns an enormous result usually has a missing join condition.

Q: A function returns one value and has only output parameters.

Correct answer: FalseA function returns a single value and takes only input parameters, which is what allows it to be used within an expression like any built-in function. Output parameters belong to procedures, which is one of the two things distinguishing the two kinds of routine.

Q: The UNION clause is used to combine the output from multiple queries together into a single result table.

Correct answer: TrueUNION takes the results of two or more queries and stacks them into one table, removing duplicate rows unless UNION ALL is used. The queries must be union compatible - the same number of columns, with corresponding types - since otherwise the rows could not be stacked at all.

Q: The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T ;

Correct answer: Cartesian joinTwo tables are named in the FROM clause and no join condition is given, so every row of one is paired with every row of the other. The result has the product of the two row counts, which is almost never wanted - an accidental Cartesian product from a forgotten WHERE clause is the classic runaway query.

11.

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

12.

Embedded SQL is which of the following?

A.Hard-coded SQL statements in a program language such as Java.
B.The process of making an application capable of generating specific SQL code on the fly.
C.Hard-coded SQL statements in a procedure.
D.Hard-coded SQL statements in a trigger.

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.

Discuss in Forum

13.

Subqueries can be nested multiple times.

A.True
B.False

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.

Discuss in Forum

14.

A correlated subquery is where the outer query depends on data from the inner query.

A.True
B.False

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.

Discuss in Forum

15.

ROLLBACK WORK asks SQL to empty the log file.

A.True
B.False

Answer: A

Correct answer: True

ROLLBACK WORK undoes the transaction by applying the before-images from the log and then discards them, so the log entries for that transaction are emptied. The database returns to the state it held when the transaction began.

Discuss in Forum

16.

What type of join is needed when you wish to include rows that do not have matching values?

A.Equi-join
B.Natural join
C.Outer join
D.All of the above.

Answer: C

Correct answer: Outer join

An outer join keeps the rows that have no match on the other side and fills the missing columns with nulls. Equi-joins and natural joins discard them, so a customer with no orders would simply disappear - which is precisely the case an outer join exists to report.

Discuss in Forum

17.

There should be one condition within the WHERE clause for each pair of tables being joined.

A.True
B.False

Answer: A

Correct answer: True

Each pair of tables joined needs a condition linking them, so joining n tables requires at least n minus one conditions. Omitting one leaves that pair unconstrained and produces a Cartesian product with the rest, which is why a query that suddenly returns an enormous result usually has a missing join condition.

Discuss in Forum

18.

A function returns one value and has only output parameters.

A.True
B.False

Answer: B

Correct answer: False

A function returns a single value and takes only input parameters, which is what allows it to be used within an expression like any built-in function. Output parameters belong to procedures, which is one of the two things distinguishing the two kinds of routine.

Discuss in Forum

19.

The UNION clause is used to combine the output from multiple queries together into a single result table.

A.True
B.False

Answer: A

Correct answer: True

UNION takes the results of two or more queries and stacks them into one table, removing duplicate rows unless UNION ALL is used. The queries must be union compatible - the same number of columns, with corresponding types - since otherwise the rows could not be stacked at all.

Discuss in Forum

20.

The following SQL is which type of join: SELECT CUSTOMER_T. CUSTOMER_ID, ORDER_T. CUSTOMER_ID, NAME, ORDER_ID FROM CUSTOMER_T,ORDER_T ;

A.Equi-join
B.Natural join
C.Outer join
D.Cartesian join

Answer: D

Correct answer: Cartesian join

Two tables are named in the FROM clause and no join condition is given, so every row of one is paired with every row of the other. The result has the product of the two row counts, which is almost never wanted - an accidental Cartesian product from a forgotten WHERE clause is the classic runaway query.

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