Question 1

A named set of SQL statements that are considered when a data modification occurs are called:

Accepted Answer

The named set of SQL statements that are considered when a data modification occurs are called triggers. Stored procedures are a named set of SQL statements that are executed when called, treatments and trapdoors are not common terms in SQL.

Question 2

In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query.

Accepted Answer

A correlated subquery is a type of subquery where the inner query depends on data from the outer query. The inner query will be executed repeatedly depending on the number of data in the outer query.

Question 3

The outer join syntax does not apply easily to a join condition of more than ________ tables.

Accepted Answer

The answer of The outer join syntax does not apply...

Question 4

What results would the following SQL statement produce? select owner, table_name
From dba_tables
Where table_name = 'CUSTOMER';

Accepted Answer

The SQL statement is selecting the owner and table_name from the dba_tables where the table_name is 'CUSTOMER'. This means that the query will return a listing of the owner of the table with the name 'CUSTOMER', not the actual customers within that table.

Question 5

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

Accepted Answer

A subquery is a query that is embedded within the WHERE or HAVING clause of another query. It is used to retrieve data or perform a calculation that will be used in comparison with the data from the main query. The other options (master query, superquery, and multi-query) are not commonly used terms in SQL.

Question 6

In which of the following situations would one have to use an outer join in order to obtain the desired results?

Accepted Answer

In situation C, an outer join is necessary to include all customers, even those who did not place an order during the most recent month. An inner join would only return customers who had placed an order during the month. Situations A and B can be accomplished with an inner join. Situation D is incorrect, as there are situations that may require an outer join.

Question 7

If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle?

Accepted Answer

The DBA_TABLES data dictionary view provides information about all tables in the database, including their owner, name, creation date, and number of rows. It is the appropriate view to use when describing all tables in the database. The other options (DBA_TAB_PRIVS, DBA_TAB_COMMENTS, and DBA_TABLE_LABEL) provide information about table privileges, comments, and labels, respectively, but do not provide a comprehensive listing of all tables in the database.

Question 8

SQL-invoked routines can be:

Accepted Answer

SQL-invoked routines can be functions or procedures. They are used to perform specific tasks within the database system. They can be called from within SQL queries or other database routines. The other options mentioned (DDL statement, DCL statement, and contained within a CREATE statement) are not necessarily related to SQL-invoked routines.

Question 9

The ________ DBA view shows information about all users of the database in Oracle.

Accepted Answer

The DBA_USERS view shows information about all users of the database in Oracle, including their account status, password information, and privileges. The USERS view only shows information about the current user, while the DBA_VIEWS and DBA_INDEXES views show information about views and indexes, respectively.

Question 10

SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query.

Accepted Answer

The described technique is called a subquery, which involves placing an inner query within the WHERE or HAVING clause of an outer query. Grouping and joining are other SQL techniques, but they do not apply to this specific scenario. Union combines data from multiple tables, but it is not relevant to the described technique.

Question 11

A join in which rows that do not have matching values in common columns are still included in the result table is called a(n):

Accepted Answer

An outer join includes all the rows from both the tables, even if there are no matching values in common columns. Therefore, option C, outer join, is the best choice. Option A, natural join, only includes the matching rows based on common columns, so it is not the best choice. Option B, equi-join, is similar to natural join in that it only includes the matching rows based on common columns, so it is not the best choice. Option D, union join, is not a valid type of join.

Question 12

All of the following are part of the coding structure for triggers EXCEPT:

Accepted Answer

The coding structure for triggers typically includes an event that triggers the trigger to activate, a condition that specifies when the trigger should activate, and an action that is performed when the trigger is activated. However, "selection" is not typically a part of this coding structure.

Question 13

One major advantage of the outer join is that:

Accepted Answer

The outer join ensures that information from both tables is included in the results, even if there is no matching data in one of the tables. This means that no data is lost in the query results.

Question 14

________ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows.

Accepted Answer

The EXISTS operator is used to check whether a subquery returns any row. If the subquery returns at least one row, the EXISTS condition is true, and the outer query executes. Therefore, the EXISTS operator would return TRUE if the subquery returns an intermediate result table with one or more rows.

Question 15

While triggers run automatically, ________ do not and have to be called.

Accepted Answer

Routines are functions or procedures that perform a specific task when called. They do not run automatically like triggers, but instead require an explicit call to execute. Trapdoors are not related to database operations, and selects and updates are types of SQL statements, not programmable routines.

Question 16

The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState,
CustomerPostalCode
FROM Customer_T
WHERE Customer_T.CustomerID =
(SELECT Order_T.CustomerID
FROM Order_T
WHERE OrderID = 1008);

Accepted Answer

The query uses a subquery to select the CustomerID from the Order_T table where OrderID is 1008, and then uses that result to select specific fields from the Customer_T table. This is a simple example of a subquery, where the result of the inner query is used as a filter for the outer query.

Question 17

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

Accepted Answer

Correlated subqueries use the result of the outer query to determine the processing of the inner query. This means that the inner query is executed for each row returned by the outer query, using the values from the current row to filter the results. This can lead to slower performance compared to other types of subqueries, but it can be necessary in certain situations where you need to reference data from both the outer and inner queries.

Question 18

An operation to join a table to itself is called a(n):

Accepted Answer

A self-join is when a table is joined to itself, and it can be useful for querying data that has a hierarchical structure or for comparing rows within the same table. The other options are not applicable to this scenario.

Question 19

A type of join where a table is joined to itself is called a(n):

Accepted Answer

A self-join is where a table is joined to itself. This is useful when analyzing hierarchical data, such as an organizational chart, where each record in the table contains a reference to the parent record in the same table. By performing a self-join, it is possible to retrieve all the child records for a given parent record.

Question 20

EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows.

Accepted Answer

The EXISTS operator in SQL returns a Boolean value of TRUE or FALSE. If the subquery returns an intermediate result table that contains one or more rows, then the EXISTS operator evaluates to TRUE. Therefore, the correct choice is C.

Quiz 7: Advanced SQL