Question 1

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 that are of type wood
B) The total price of all products
C) The standard_price of the first wood product in the table
D) The standard_price of any wood product in the table

Accepted Answer

The query uses the "sum" function to add up the "standard_price" column of all the products with "product_type" as "WOOD". Therefore, the result will be the total price of all products that are of type wood.

Question 2

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 all drivers who made more than 2 deliveries as well as a count of the number of deliveries
B) A listing of all drivers
C) A listing of the number of deliveries greater than 2
D) A listing of all drivers who made more than 2 deliveries

Accepted Answer

The answer of What will be returned when the following...

Question 3

Which of the following is the wildcard operator in SQL statements?
A) < >
B) *
C) =
D) &

Accepted Answer

The wildcard operator in SQL statements is the asterisk (*), which represents any character or set of characters. This is often used in conjunction with the LIKE keyword to search for specific patterns in data. Option A (< >) is the not equal to operator, option C (=) is the equal to operator, and option D (&) is not even a valid SQL operator.

Question 4

To eliminate duplicate rows in a query,the ________ qualifier is used in the SQL Select command.
A) alter
B) distinct
C) check
D) specific

Accepted Answer

The "distinct" qualifier is used in the SQL Select command to eliminate duplicate rows in a query. This qualifier ensures that each row returned by the query is unique, removing any duplicate rows that may otherwise appear in the query result.

Question 5

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) A listing of all drivers, sorted by driver number
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) None of the above

Accepted Answer

The SQL statement is grouping the deliveries by driver number and counting the number of deliveries made by each driver. Therefore, the result will be a listing of each driver along with the number of deliveries they made.

Question 6

To get all the customers from Hawaii sorted together,which of the following would be used?
A) Order By
B) Group By
C) Having
D) Sort

Accepted Answer

The answer of To get all the customers from Hawaii...

Question 7

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) An error message will be generated.
C) The first product description alphabetically in product_v will be shown.
D) None of the above.

Accepted Answer

The SELECT statement with MIN aggregate function will return the minimum value of the product_description column, which in this case would be the first product description alphabetically in the table. Therefore, option C is correct.

Question 8

A single value returned from an SQL query that includes an aggregate function is called a(n):
A) agate.
B) scalar aggregate.
C) vector aggregate.
D) summation.

Accepted Answer

A scalar aggregate is a single value returned from an SQL query that includes an aggregate function. The other options (agate, vector aggregate, summation) do not accurately describe this concept.

Question 9

Which of the following finds all groups meeting stated conditions?
A) Select
B) Where
C) Having
D) Find

Accepted Answer

The answer of Which of the following finds all groups...

Question 10

Which of the following counts ONLY rows that contain a value?
A) Count
B) Count(*)
C) Tally(*)
D) Checknum

Accepted Answer

The COUNT function counts only the rows that contain a value in the specified column or columns. The COUNT(*) function counts all rows, including those with NULL values. Tally(*) and Checknum are not valid SQL functions.

Question 11

Which of the following can produce scalar and vector aggregates?
A) Order By
B) Group By
C) Having
D) Sort

Accepted Answer

Only Group By can produce both scalar and vector aggregates. Scalar aggregates return a single value, such as the total sum of the values in a column, while vector aggregates return a value for each group, such as the average value of a column for each group. Order By and Sort only affect the order of the result set, and Having only filters groups based on a condition.

Question 12

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 listing of all drivers who made deliveries to state = 'MA', sorted by driver number
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) A count of all of the deliveries made to state = 'MA' by all drivers.
D) None of the above.

Accepted Answer

The SQL statement is using aggregation functions and grouping by driver number. The result will be a listing of each driver who made deliveries to state = 'MA', along with the number of deliveries that each driver has made to that state.

Question 13

A ________ view is materialized when referenced.
A) virtual
B) dynamic
C) materialized
D) base

Accepted Answer

The answer of A ________ view is materialized when referenced.
A)...

Question 14

What result set will the following query return?
Select item_no
From order_v
Where quantity > 10;
A) The item_no of all orders that had more than 10 items
B) The order_id of all orders that had more than one item
C) The order_id of all orders that had more than 10 items
D) The item_no of all orders that had 10 or more items

Accepted Answer

The query selects item_no from order_v where the quantity is greater than 10, which matches option A.

Question 15

Which of the following will produce the minimum of all standard prices?
A) Select standard_price from product_v where standard_price = min;
B) Select min(standard_price) from product_v;
C) Select standard_price from min(product_v);
D) Select min(standard_price) from product_v where standard_price = min(standard_price);

Accepted Answer

Option B uses the aggregate function MIN() to directly retrieve the minimum value of the standard_price column from the product_v table. This produces the minimum of all the standard prices. Option A attempts to compare the standard_price column to the minimum value, which may not give the intended result. Option C is invalid SQL syntax and would result in a syntax error. Option D uses the WHERE clause to compare the standard price to the minimum value, but this is unnecessary because we can simply use the MIN() aggregate function to retrieve the minimum value directly.

Question 16

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 and description for all items weighing between 101 and 199
D) The item_no for all items weighing more than 200

Accepted Answer

The query selects item numbers and descriptions from the table "item" where the weight is greater than 100 and less than 200, so the result set will consist of the item numbers and corresponding descriptions for all items with weights between 101 and 199.

Question 17

Multiple values returned from an SQL query that includes an aggregate function are called:
A) vector aggregates.
B) scalar aggregates.
C) agates.
D) summations.

Accepted Answer

The answer of Multiple values returned from an SQL query...

Question 18

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
B) The customer_name and telephone of all customers living in either Boston, New York or Denver
C) The customer_name and telephone of all customers living in Boston and New York and Denver
D) The customer_name of all customers living in Boston, New York or Denver

Accepted Answer

The query is selecting customer_name and telephone from a table called customers, and filtering by the city column, only returning customers who live in either Boston, New York, or Denver.

Question 19

A view may not be updated directly if it contains:
A) the distinct keyword.
B) derived columns and expressions in the select clause.
C) uses the group by or having clause.
D) all of the above.

Accepted Answer

- The distinct keyword in a view can prevent direct update as it creates a virtual table that may not correspond directly to an underlying table.
- Derived columns and expressions in the select clause can also make it difficult to determine how to update the view and underlying table, making direct update impossible.
- Similarly, the use of group by or having clause can make it difficult to update the view and underlying table directly. Therefore, all of the above factors can prevent direct update of a view.

Question 20

Which of the following is true of the order in which SQL statements are evaluated?
A) The select clause is always processed first.
B) The select clause is always processed last.
C) The select clause is processed before the order by clause.
D) The group by clause is processed before the where clause.

Accepted Answer

The select clause is processed before the order by clause because the select clause determines the columns that will be returned in the result set, while the order by clause determines the order in which the result set will be presented. It is important to note that the order in which other clauses such as where and group by are processed may vary depending on the specific query and its requirements.

Quiz 6: Introduction to SQL

What results will be produced by the following SQL query? Select sum(standard_price) as total_price From product_v Where product_type = 'WOOD';

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;

Which of the following is the wildcard operator in SQL statements?

To eliminate duplicate rows in a query,the ________ qualifier is used in the SQL Select command.

What will be returned when the following SQL statement is executed? Select driver_no,count(*) as num_deliveries From deliveries Group by driver_no;

To get all the customers from Hawaii sorted together,which of the following would be used?

What will result from the following SQL Select statement? Select min(product_description) From product_v;

A single value returned from an SQL query that includes an aggregate function is called a(n) :

Which of the following finds all groups meeting stated conditions?

Which of the following counts ONLY rows that contain a value?

Which of the following can produce scalar and vector aggregates?

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 ________ view is materialized when referenced.

What result set will the following query return? Select item_no From order_v Where quantity > 10;

Which of the following will produce the minimum of all standard prices?

What result set will the following query return? Select item_no,description From item Where weight > 100 and weight < 200;

Multiple values returned from an SQL query that includes an aggregate function are called:

What result set is returned from the following query? Select customer_name,telephone From customers Where city in ('Boston','New York','Denver') ;

A view may not be updated directly if it contains:

Which of the following is true of the order in which SQL statements are evaluated?