IS 441 Ch 6

An SQL query that implements an outer join will return rows that do not have matching values in common columns. True False

True

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

causes two tables with a common domain to be combined into a single table or view.

What results would the following SQL statement produce? SELECT Owner, Table Name FROM dba_tables Where Table_name= 'Customer' A listing of the owner of the customer table as well as customers A listing of all customers in the customer table An error message A listing of the owner of the customer table

A listing of the owner of the customer table

________ use the result of the outer query to determine the processing of the inner query. Correlated subqueries Outer subqueries Subqueries Inner subqueries

Correlated subqueries

The Following query will execute without errors SELECT customer.customer_name, Salesman.sales_quota FROM Customer WHERE Customer.salesman_id = (SELECT Salesman_id Where Lname = 'SMITH') True False

False

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

False

Which of the following questions is answered by the SQL statement?Select Count (Product_Description) from Product_T; How many different columns named "Product_Description" are there in table Product_T? How many products are in the table Product_T? How many characters are in the field name "Product_Description"? How many products have product descriptions in the Product Table?

How many products have product descriptions in the Product Table?

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): multivariate join. inner join. natural join. equi-join.

Natural Join

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

Outer Join

An operation to join a table to itself is called a: sufficient-join. self-join. inner join. outer join.

Self join

A correlated subquery is executed once for each iteration through the outer loop. True False

True

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

True

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

True

The following Queries produce the same results SELECT Customer_name, Customer_city FROM Customer, Salesman WHERE Customer.salesman_id = Salesman.salesman_ID and salesman.Lname = 'Smith' SELECT Customer_name, Customer_city FROM customer Where customer.salesman_id = (SELECT Salesman_ID FROM salesman Where Lname = 'Smith') True False

True

When EXISTS or NOT EXISTS is used in a subquery, the select list of the subquery will usually just select all columns as a placeholder because it doesn't matter which columns are returned. True False

True

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

Two

The ________ clause is used to combine the output from multiple queries into a single result table. INTERSECT UNION COLLATE DIVIDE

Union

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

both output compatible data types for each column and return the same number of rows.

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

use a lot of self joins

A type of query that is placed within a WHERE or HAVING clause of another query is called a: multi-query. superquery. master query. subquery.

subquery

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

subquery

The following SQL statement is an example of a correlated subquery SELECT first_name, Last_name, Toral_sales from salesman s1 Where total_sales > all (select total_sales from Salesman S2 Where S1 salesman_id ! = s2salesman_id)

true

In which of the following situations would one have to use an outer join in order to obtain the desired results? There is never a situation that requires only an outer join. A report is desired that lists all customers and the total of their orders. 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). A report is desired that lists all customers who placed an order.

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

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

Both Equi Join and natural join

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

Correlated