Question 1

A type of join between three tables is called a(n):
A) ternary join.
B) self-join.
C) unnatural join.
D) pinned join.

Accepted Answer

A ternary join involves joining three tables together in a database query.

Question 2

The following code would include: 
SELECT Customer_T.CustomerID, Order_T.CustomerID,
CustomerName, OrderID
FROM Customer_T, Order_T
WHERE Customer_T.CustomerID = Order_T. CustomerID;

A) all rows of the Order_T Table regardless of matches with the Customer_T Table.
B) all rows of the Customer_T Table regardless of matches with the Order_T Table.
C) only rows that match both Customer_T and Order_T Tables.
D) only rows that don't match both Customer_T and Order_T Tables.

Accepted Answer

This SQL query performs an inner join between Customer_T and Order_T tables, returning only the rows where CustomerID matches in both tables.

Question 3

The most commonly used form of join operation is the:
A) outer join.
B) union join.
C) equi-join.
D) natural join.

Accepted Answer

D

Question 4

A join operation:
A) brings together data from two different fields.
B) causes two tables with a common domain to be combined into a single table or view.
C) causes two disparate tables to be combined into a single table or view.
D) is used to combine indexing operations.

Accepted Answer

A join operation combines two tables with a common domain into a single table or view, allowing for data from both tables to be used together.

Question 5

The following code would include: 
SELECT Customer_T.CustomerID,CustomerName, OrderID
FROM Customer_T LEFT OUTER JOIN Order_T ON
Customer_T.CustomerID = Order_T.CustomerID;

A) all rows of the Order_T Table regardless of matches with the Customer_T Table.
B) all rows of the Customer_T Table regardless of matches with the Order_T Table.
C) only rows that match both Customer_T and Order_T Tables.
D) only rows that don't match both Customer_T and Order_T Tables.

Accepted Answer

A LEFT OUTER JOIN returns all rows from the left table (Customer_T) and the matched rows from the right table (Order_T). If there is no match, NULLs are returned for columns from the right table.

Question 6

A join in which the joining condition is based on equality between values in the common columns is called a(n):
A) equi-join.
B) unilateral join.
C) natural join.
D) both A and C.

Accepted Answer

The answer of A join in which the joining condition...

Question 7

One major advantage of the outer join is that:
A) information is easily accessible.
B) information is not lost.
C) the query is easier to write.
D) information's data type changes.

Accepted Answer

Information is not lost in an outer join because it includes rows from both tables, even if there is no matching data in the other table.

Question 8

The UNION clause is used to:
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.

Accepted Answer

The UNION clause is used to combine the output from multiple queries into a single result table, including all the rows from each query.

Question 9

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):
A) natural join.
B) equi-join.
C) outer join.
D) union join.

Accepted Answer

An outer join includes rows that do not have matching values in common columns by filling in NULLs for the missing matches.

Question 10

A ________ is a temporary table used in the FROM clause of an SQL query.
A) correlated subquery
B) derived table
C) view table
D) trigger

Accepted Answer

A derived table is a temporary table created in the FROM clause of an SQL query, allowing for complex queries and transformations within a single query.

Question 11

The ________ operator is used to combine the output from multiple queries into a single result table.
A) INTERSECT
B) DIVIDE
C) COLLATE
D) UNION

Accepted Answer

The UNION operator is used to combine the results of two or more SELECT queries into a single result set, including all the rows that belong to all queries in the union.

Question 12

All of the following are guidelines for better query design EXCEPT:
A) understand how indexes are used in query processing.
B) use a lot of self-joins.
C) write simple queries.
D) retrieve only the data that you need.

Accepted Answer

Using a lot of self-joins can complicate queries and potentially degrade performance, which is not a guideline for better query design.

Question 13

In which of the following situations would one have to use an outer join in order to obtain the desired results?
A) A report is desired that lists all customers who placed an order.
B) A report is desired that lists all customers and the total of their orders.
C) A report is desired that lists all customers and the total of their orders during the most recent month, and includes customers who did not place an order during the month (their total will be zero).
D) There is never a situation that requires only an outer join.

Accepted Answer

An outer join is needed in this case to include customers who did not place an order during the month, ensuring they appear in the report with a total of zero.

Question 14

Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by:
A) using the if-then-else construct.
B) using the immediate if statement.
C) using the CASE key word in a statement.
D) using a subquery.

Accepted Answer

The CASE keyword in SQL is used to implement IF-THEN-ELSE logic within a query, allowing for conditional expressions.

Question 15

The outer join syntax does not apply easily to a join condition of more than ________ tables.
A) two
B) three
C) four
D) five

Accepted Answer

The outer join syntax is primarily designed for joining two tables, and it becomes more complex and less intuitive when applied to join conditions involving more than two tables.

Question 16

In order for two queries to be UNION-compatible, they must:
A) both have the same number of lines in their SQL statements.
B) both output compatible data types for each column and return the same number of rows.
C) both return at least one row.
D) both return exactly one row.

Accepted Answer

For two queries to be UNION-compatible, they must have the same number of columns with compatible data types in each corresponding column. The number of rows returned does not need to be the same.

Question 17

The following code would include: 
SELECT Customer_T.CustomerID,CustomerName, OrderID
FROM Customer_T RIGHT OUTER JOIN Order_T ON
Customer_T.CustomerID = Order_T.CustomerID;

A) all rows of the Order_T Table regardless of matches with the Customer_T Table.
B) all rows of the Customer_T Table regardless of matches with the Order_T Table.
C) only rows that match both Customer_T and Order_T Tables.
D) only rows that don't match both Customer_T and Order_T Tables.

Accepted Answer

The RIGHT OUTER JOIN returns all rows from the right table (Order_T), and the matched rows from the left table (Customer_T). If there is no match, NULL values are returned for columns from the left table.

Question 18

The following code is an example of a(n): 
SELECT Customer_T.CustomerID, Order_T.CustomerID,
CustomerName, OrderID
FROM Customer_T, Order_T
WHERE Customer_T.CustomerID = Order_T.CustomerID;

A) equi-join.
B) subquery.
C) Full Outer JOIN.
D) Right Outer JOIN.

Accepted Answer

This SQL query is an example of an equi-join because it joins two tables, Customer_T and Order_T, based on the equality condition Customer_T.CustomerID = Order_T.CustomerID.

Question 19

An operation to join a table to itself is called a(n):
A) sufficient-join.
B) inner join.
C) outer join.
D) self-join.

Accepted Answer

A self-join is when a table is joined with itself to compare rows within the same table.

Question 20

A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n):
A) equi-join.
B) natural join.
C) multivariate join.
D) inner join.

Accepted Answer

A natural join is a type of join that automatically matches columns between tables with the same name and removes duplicate columns from the result.

Quiz 6: Advanced SQL

A type of join between three tables is called a(n) :

The following code would include: SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;

The most commonly used form of join operation is the:

A join operation:

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;

A join in which the joining condition is based on equality between values in the common columns is called a(n) :

One major advantage of the outer join is that:

The UNION clause is used to:

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) :

A ________ is a temporary table used in the FROM clause of an SQL query.

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

All of the following are guidelines for better query design EXCEPT:

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

Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by:

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

In order for two queries to be UNION-compatible, they must:

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T RIGHT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;

The following code is an example of a(n) : SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID;

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

A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n) :