ITD 256 Final Exam Review (Questions from Quiz 2)

Which of the following is the wildcard operator in SQL statements? a) < > b) = c) * d) &

*

What will be returned when the following SQL query is executed? Select driver_no, count(*) as num_deliveries from deliveries group by driver_no having count(*) > 2; a) A listing of the number of deliveries greater than 2 b) A listing of all drivers c) A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries d) A listing of all drivers who made more than 2 deliveries

A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries

What will be returned when the following SQL statement is executed? Select driver_no,count(*) as num_deliveries from deliveries group by driver_no; a) An error message b) A listing of each driver as well as the number of deliveries that he or she has made c) A count of all of the deliveries made by all drivers d) A listing of all drivers, sorted by driver number

A listing of each driver as well as the number of deliveries that he or she has made

What will be returned when the following SQL statement is executed? Select driver_no, count(*) as num_deliveries from deliveries where state = 'MA' group by driver_no; a) A count of all of the deliveries made to state = 'MA' by all drivers b) A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state c) An error message will be generated d) A listing of all drivers who made deliveries to state = 'MA', sorted by driver number

A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state

What results would the following SQL statement produce? select owner, table_name from dba_tables where table_name = 'CUSTOMER'; a) A listing of the owner of the customer table as well as customers b) A listing of the owner of the customer table c) A listing of all customers in the customer table d) An error message

A listing of the owner of the customer table

________ use the result of the outer query to determine the processing of the inner query. a) Subqueries b) Inner subqueries c) Correlated subqueries d) Outer subqueries

Correlated subqueries

INSERT INTO is an example of ________ code. a) DDL b) DCL c) DML d) TIO

DML

_______ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows. a) Exists b) Extents c) In d) Having

Exists

What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best" a) Selects all the fields from the Customer table for each row with a customer labeled "Best" b) Selects all the fields from the Customer table for each row with a customer labeled "*" c) Selects fields with a "*" in them from the Customer table d) Selects the "*" field from the Customer table for each row with a customer labeled "Best"

Selects all the fields from the Customer table for each row with a customer labeled "Best"

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); a) Correlated subquery. b) FULL OUTER JOIN. c) Subquery. d) JOIN.

Subquery

What result set is returned from the following query? Select Customer_Name, telephone from customers where city in ('Boston','New York','Denver'); a) The Customer_Name and telephone of all customers living in Boston and New York and Denver b) The Customer_Name and telephone of all customers living in either Boston, New York or Denver c) The Customer_Name of all customers living in Boston, New York or Denver d) The Customer_Name and telephone of all customers

The Customer_Name and telephone of all customers living in either Boston, New York or Denver

What result set will the following query return? Select Item_No, description from item where weight > 100 and weight < 200; a) The Item_No and description for all items weighing less than 100 b) The Item_No for all items weighing between 101 and 199 c) The Item_No for all items weighing more than 200 d) The Item_No and description for all items weighing between 101 and 199

The Item_No and description for all items weighing between 101 and 199

What result set will the following query return? Select Item_No from Order_V where quantity > 10; a) The Order_Id of all orders that had more than one item b) The Order_Id of all orders that had more than 10 items c) The Item_No of all orders that had 10 or more items d) The Item_No of all orders that had more than 10 items

The Item_No of all orders that had more than 10 items

What result will the following SQL statement produce? Select Avg(standard_price) as average from Product_V; a) The average of all products in Product_V b) The average Standard_Price of all products in Product_V c) The average price of all products d) The mode of all prices

The average Standard_Price of all products in Product_V

What will result from the following SQL Select statement? Select min(Product_Description) from Product_V; a) The minimum value of Product_Description will be displayed. b) SELECT @ will be displayed. c) An error message will be generated. d) The first product description alphabetically in Product_V will be shown.

The first product description alphabetically in Product_V will be shown

What results will be produced by the following SQL query? Select sum(standard_price) as Total_Price from Product_V where Product_Type = 'WOOD'; a) The total price of all products b) The Standard_Price of the first wood product in the table c) The Standard_Price of any wood product in the table d) The total price of all products that are of type wood

The total price of all products that are of type wood

In an SQL statement, which of the following parts states the conditions for row selection? a) Where b) Select c) Group By d) From

Where

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) only rows that don't match both Customer_T and Order_T Tables. c) all rows of the Customer_T Table regardless of matches with the Order_T Table. d) only rows that match both Customer_T and Order_T Table

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; a) all rows of the Customer_T Table regardless of matches with the Order_T Table. b) only rows that don't match both Customer_T and Order_T Tables. c) all rows of the Order_T Table regardless of matches with the Customer_T Table. d) only rows that match both Customer_T and Order_T Tables.

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

A join operation: a) is used to combine indexing operations. b) causes two tables with a common domain to be combined into a single table or view. c) brings together data from two different fields. d) 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.

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

correlated

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) subquery. b) Right Outer JOIN. c) equi-join. d) Full Outer JOIN.

equi-join.

The most commonly used form of join operation is the: a) equi-join. b) union join. c) natural join. d) outer join.

natural join.

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) only rows that match both Customer_T and Order_T Tables. b) only rows that don't match both Customer_T and Order_T Tables. c) all rows of the Customer_T Table regardless of matches with the Order_T Table. d) all rows of the Order_T Table regardless of matches with the Customer_T Table.

only rows that match both Customer_T and Order_T Tables.

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

subquery