5370 Quiz 6

What would the following view contain for values? Create view CustomerOrders as Select CustID, Count(*) as TotOrders, Sum(ordertotal) as Value From customer inner join sale on customer.customer_id = sale.customer_id;

A listing of the customer ID as well as the total number of orders and the total amount spent by the customer

A function has only input parameters but can return multiple values.

False

IF-THEN-ELSE logical processing cannot be accomplished within an SQL statement.

False

It is better not to have a result set identified before writing GROUP BY and HAVING clauses for a query.

False

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

False

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

False

There is a special operation in SQL to join a table to itself.

False

A base table is the underlying table that is used to create views.

True

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

True

A join in which the joining condition is based on equality between values in the common column is called an equi-join.

True

A procedure is run by calling it by its name.

True

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

True

Constraints are a special case of triggers.

True

Establishing IF-THEN-ELSE logical processing within an SQL statement can now be accomplished by using the CASE keyword in a statement.

True

Figuring out what attributes you want in your query before you write the query will help with query writing.

True

In order to find out what customers have not placed an order for a particular item, one might use the NOT qualifier along with the IN qualifier.

True

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

True

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

True

The following queries produce the same results. SELECT DISTINCT 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

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

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;

all rows of the Customer_T Table regardless of matches with the Order_T Table.

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;

all rows of the Order_T table regardless of matches with the Customer_T Table.

User-defined data types:

can have defined functions and methods.

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

derived table

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;

equi-join.

One major advantage of the outer join is that:

information is not lost.

The most commonly used form of join operation is the:

natural join.

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

security.

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

subquery

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

use a lot of self-joins.