Question 1

SQL statements can be included in another language, such as C or Java.

Accepted Answer

A) True 
 B)False

Question 2

All of the following are advantages of SQL-invoked routines EXCEPT:

Accepted Answer

A)   flexibility.
B)   efficiency.
C)   sharability.
D)   security.E) A) and C)
F) B) and C)

Question 3

User-defined transactions can improve system performance because:

Accepted Answer

A)   transactions are processed as sets, reducing system overhead.
B)   transactions are mapped to SQL statements.
C)   speed is decreased due to query optimization.
D)   all triggers delete rows.E) None of the above
F) C) and D) A

Question 4

What is a derived table? When is it used? Can you describe any situations where you would have to use it over a subquery in the WHERE clause?

Accepted Answer

A derived table can be created by placin...

Question 5

Using an outer join produces this information: rows that do not have matching values in common columns are not included in the result table.

Accepted Answer

A) True 
 B)False

Question 6

A transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid.

Accepted Answer

A) True 
 B)False

Question 7

A type of query that is placed within a WHERE or HAVING clause of another query is called a:

Accepted Answer

A)   master query.
B)   subquery.
C)   superquery.
D)   multi-query.E) A) and D)
F) None of the above

Question 8

When is it better to use a subquery over using a join?

Accepted Answer

Often, a subquery and join will return t...

Question 9

Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions are called:

Accepted Answer

A)   stored procedures.
B)   Persistent Stored Modules.
C)   flow control modules.
D)   global triggers.E) B) and D)
F) B) and C)

Question 10

A procedure is run by calling it by its name.

Accepted Answer

A) True 
 B)False

Question 11

The following SQL statement is an example of a correlated subquery.
select first_name, last_name, total_sales
from salesman s1
where total_sales > all
(select total_sales from salesman s2
where s1.salesman_id != s2.salesman_id);

Accepted Answer

A) True 
 B)False

Question 12

The UNION clause is used to:

Accepted Answer

A)   combine the output from multiple queries into a single result table.
B)   join two tables together to form one table.
C)   find all rows that do not match in two tables.
D)   find all rows that are in one table, but not the other.E) None of the above
F) B) and C)

Question 13

A new set of analytical functions added in SQL:2008 is referred to as:

Accepted Answer

A)   OLAF functions.
B)   MOLAP functions.
C)   average functions.
D)   OLAP functions.E) A) and C)
F) A) and B) D

Question 14

A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated.

Accepted Answer

A) True 
 B)False  True

Question 15

________ use the result of the outer query to determine the processing of the inner query.

Accepted Answer

A)   Correlated subqueries
B)   Outer subqueries
C)   Inner subqueries
D)   SubqueriesE) B) and C)
F) A) and D)

Question 16

Joining tables or using a subquery may produce the same result.

Accepted Answer

A) True 
 B)False

Question 17

RDBMSs store database definition information in system-created tables which can be considered a data dictionary.

Accepted Answer

A) True 
 B)False

Question 18

The ________ operator is used to combine the output from multiple queries into a single result table.

Accepted Answer

A)   INTERSECT
B)   DIVIDE
C)   COLLATE
D)   UNIONE) B) and C)
F) All of the above

Question 19

All of the following are new data types added in SQL:2008 EXCEPT:

Accepted Answer

A)   BIGINT.
B)   BIT.
C)   MULTISET.
D)   XML.E) B) and C)
F) All of the above

Question 20

The MERGE command:

Accepted Answer

A)   allows one to combine the INSERT and UPDATE operations.
B)   allows one to combine the INSERT and DELETE operations.
C)   joins 2 tables together.
D)   is always a single table operation.E) A) and B)
F) A) and C)

Exam 7: Advanced SQL

SQL statements can be included in another language, such as C or Java.

Correct AnswerverifiedShow Answer

All of the following are advantages of SQL-invoked routines EXCEPT:

Correct AnswerverifiedShow Answer

User-defined transactions can improve system performance because:

Correct AnswerverifiedA

What is a derived table? When is it used? Can you describe any situations where you would have to use it over a subquery in the WHERE clause?

Correct AnswerverifiedA derived table can be created by placin...View AnswerShow Answer

Using an outer join produces this information: rows that do not have matching values in common columns are not included in the result table.

Correct AnswerverifiedShow Answer

A transaction is the complete set of closely related update commands that must all be done, or none of them done, for the database to remain valid.

Correct AnswerverifiedShow Answer

A type of query that is placed within a WHERE or HAVING clause of another query is called a:

Correct AnswerverifiedShow Answer

When is it better to use a subquery over using a join?

Correct AnswerverifiedOften, a subquery and join will return t...View AnswerShow Answer

Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions are called:

Correct AnswerverifiedShow Answer

A procedure is run by calling it by its name.

Correct AnswerverifiedShow Answer

The following SQL statement is an example of a correlated subquery. select first_name, last_name, total_sales from salesman s1 where total_sales > all (select total_sales from salesman s2 where s1.salesman_id != s2.salesman_id);

Correct AnswerverifiedShow Answer

The UNION clause is used to:

Correct AnswerverifiedShow Answer

A new set of analytical functions added in SQL:2008 is referred to as:

Correct AnswerverifiedD

A natural join is the same as an equi-join, except that it is performed over matching columns that have been defined with the same name, and one of the duplicate columns is eliminated.

Correct AnswerverifiedTrue

________ use the result of the outer query to determine the processing of the inner query.

Correct AnswerverifiedShow Answer

Joining tables or using a subquery may produce the same result.

Correct AnswerverifiedShow Answer

RDBMSs store database definition information in system-created tables which can be considered a data dictionary.

Correct AnswerverifiedShow Answer

The ________ operator is used to combine the output from multiple queries into a single result table.

Correct AnswerverifiedShow Answer

All of the following are new data types added in SQL:2008 EXCEPT:

Correct AnswerverifiedShow Answer

The MERGE command:

Correct AnswerverifiedShow Answer

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified
A

Correct Answer
verified
A derived table can be created by placin...
View Answer

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified
Often, a subquery and join will return t...
View Answer

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified
D

Correct Answer
verified
True

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified

Correct Answer
verified