SQL Test 2

To return all of the columns from the base table, you can code the ________________ operator in the SELECT clause.

*

In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.

= (equals)

Write an aggregate expression to find the oldest date in the invoice_date column.

MIN(invoice_date)

Write an aggregate expression to find the oldest date in the invoice_date column: _________________________.

MIN(invoice_date)

Write an aggregate expression to find the oldest date in the invoice_date column:_________________________.

MIN(invoice_date)

The order of precedence for the logical operators in a WHERE clause is

NOT, AND, OR

Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union: 1. is coded in the ORDER BY clause 2. combines columns from the same table 3. combines the result sets of two or more SELECT statements 4. by default, does not eliminate duplicate rows

combines the result sets of two or more SELECT statements

SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id This type of join is called a/an __________________ join.

inner

The ____ data types can store large, unstructured data such as text, images, sound, and video.

large object

SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id The total number of rows returned by this query must equal: 1. the number of rows in the Invoices table 2. the number of rows in the Vendors table 3. the number of rows in the Invoices table plus the number of rows in the Vendors table 4. none of the above

the number of rows in the Invoices table

Given the tables passenger and reservation what is the output of the following query? SELECT passenger.pid FROM passenger WHERE passenger.pid > 3 UNION ALL SELECT reservation.pid FROM reservation ORDER BY pid pid pname age 1 john wayne 27 2 mary popins 22 3 paul newman 45 4 al pacino 30 5 yohan croif 65 reservation rid pid class 100 1 b 101 1 a 102 1 a 103 2 c 104 3 a 105 4 b 106 4 a

1 1 1 2 3 4 4 4 5

Write an aggregate expression to calculate the average value of the invoice_total column, excluding null values.

AVG(invoice_total)

You can use the ________ phrase to test whether an expression falls within a range of values.

BETWEEN

By default, the INSERT, UPDATE, and DELETE statements that you issue don't make permanent changes to an Oracle database until you issue a ____ statement.

COMMIT

To concatenate character strings, you use the ___________________ function in a string expression.

CONCAT

All of the aggregate functions ignore null values, except for the ____ function.

COUNT(*)

All of the aggregate functions ignore null values, except for the ______________________________ function.

COUNT(ALL)

Write an aggregate expression for the number of unique values in the vendor_id column: _________________________.

COUNT(DISTINCT vendor_id)

Write an aggregate expression for the number of unique values in the vendor_id column:_________________________.

COUNT(DISTINCT vendor_id)

Write an aggregate expression for the number of unique values in the vendor_id column.

COUNT(DISTINCT( vendor_id)

Write an aggregate expression for the number of entries in the vendor_name column.

COUNT(vendor_name)

If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the ____ keyword in the VALUES clause of the INSERT statement.

DEFAULT

If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the ____________________ keyword in the VALUES clause of the INSERT statement.

DEFAULT

By default, MySQL automatically commits changes to the database immediately after each INSERT, UPDATE, and ______________ statement that you issue.

DELETE

Write the code for a DELETE statement that deletes every row in the Invoices_Copy table:_______________________________.

DELETE * FROM Invoices_Copy

Write the code for a DELETE statement that deletes every row in the Invoices_Copy table: _______________________________________________________________

DELETE FROM Invoices_Copy

Write the code for a DELETE statement that deletes every row in the Invoices_Copy table.

DELETE FROM invoices_copy

To sort the rows that are retrieved by a SELECT statement in descending sequence by invoice_total, you code this ORDER BY clause: ORDER BY invoice_total ________________

DESC

By default, all duplicate values are included in the aggregate calculation, unless you specify the ____ keyword.

DISTINCT

To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.

DISTINCT

The ________________ clause of the SELECT statement specifies the table that contains the data.

FROM

The ________________ clause groups the rows of a result set based on one or more columns or expressions.

GROUP BY

SELECT vendor_name, invoice_dateFROM vendors v JOIN invoices iON v.vendor_id = i.vendor_id (Refer to code example 4-1.) This type of join is called a/an __________________ join.

INNER

If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use a/an _______________ join.

LEFT OUTER

You use the _________ operators to retrieve rows that match a string pattern.

LIKE/REGEXP

You can use the _________ clause to limit the number of rows returned by the SELECT statement.

LIMIT

Which of the following would return a maximum of 7 rows, starting with the 4th row?

LIMIT 7, 4

Write an aggregate expression to find the vendor_name column that's last in alphabetical order.

MAX(vendor_name)

Write an aggregate expression to find the vendor_name column that's last in alphabetical order: _________________________.

MAX(vendor_name)

Write an aggregate expression to find the vendor_name column that's last in alphabetical order:_________________________.

MAX(vendor_name)

When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values.

NULL

To store a number that has a fixed number of decimal places with an Oracle data type (not one of the ANSI standard synonyms), you use the ____ data type.

NUMBER

To store an integer with an Oracle data type (not one of the ANSI standard synonyms), you use the ____ data type.

NUMBER

Select the result that would be obtained from the following code: SELECT region, SUM(area) FROM bbc WHERE SUM(area) > 20000 GROUP BY region Table-A Europe 29196 Table-B Europe 29196 South Asia 810234 Middle East 2400000 Table-C Europe Asia-Pacific North America

No result due to invalid use of the WHERE function

When you use the explicit syntax for coding joins, the join conditions are coded in the ____________ clause.

ON

The ________ clause specifies how you want the rows in the result set sorted.

ORDER BY

When yo use the WITH ROLLUP operator, you can't use the ____________ clause.

ORDER BY

When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding the ____ operator.

ROLLUP

You can combine inner and outer joins within a single _______________ statement.

SELECT

You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____ statement.

SELECT

You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement.

SELECT

You can use aggregate functions in which clauses?

SELECT and HAVING

The six clauses of the SELECT statement must be coded in the following order:

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

The six clauses of the SELECT statement must be coded in the following order: 1. SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING 2. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY 3. SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY 4. SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

To convert numeric values to formatted numbers, you can use the ____ function.

TO_CHAR

Assuming that all of the table and column names are spelled correctly, what's wrong with the INSERT statement that follows? INSERT INTO invoices (vendor_id, invoice_number, invoice_total, payment_total, credit_total, terms_id, invoice_date, invoice_due_date) VALUES (97, '456789', 8344.50, 0, 0, 1, '2012-08-31')

The number of items in the column list doesn't match the number in the VALUES list.

Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1 1. The duplicate vendors from each state 2. The number of vendors in each state that have more than one vendor 3. The names of the vendors in each state 4. The number of vendors in each state

The number of vendors in each state that have more than one vendor

When you code a union that combines two result sets, which of the following is not true? Each result set must have the same number of columns. The result sets may or may not have any duplicate rows. The corresponding columns in the result sets must have compatible data types. The result sets must be derived from different tables.

The result sets must be derived from different tables.

The __________ clause is used to filter the rows in the base table so that only those rows that match the search condition are included in the result set.

WHERE

When you code a DELETE statement for one or more rows, the ____ clause specifies which row or rows are to be deleted.

WHERE

When you code a DELETE statement for one or more rows, the _________________ clause specifies which row or rows are to be deleted.

WHERE

When you code an UPDATE statement for one or more rows, the SET clause names the columns to be updated and the values to be assigned to those columns, and the ______________________ clause specifies the conditions a row must meet to be updated.

WHERE

When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the ____ clause specifies which row or rows are to be updated.

WHERE

When you use the implicit syntax for coding joins, the join conditions are coded in the ____________ clause.

WHERE

Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_state, COUNT(ALL) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(ALL) > 1 a. The duplicate vendors from each state b. The number of vendors in each state that has more than one vendor c. The names of the vendors in each state d. The number of vendors in each state

b. The number of vendors in each state that has more than one vendor

In a cross join, all of the rows in the first table are joined with all of the a. distinct rows in the second table b. rows from the second table c. unmatched columns in the second table d. matched rows in the second table

b. rows from the second table

When you use the USING keyword for a join, a. the join can't be done on more than one column b. the join must be based on a column or columns that have the same name in both tables c. the join must be an inner join d. the join can't be an outer join

b. the join must be based on a column or columns that have the same name in both tables

SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC This output will be sorted by: 1. balance_due in descending sequence 2. invoice_id 3. vendor_id 4. vendor_id and then by balance_due in descen

balance_due in descending sequence

Expressions coded in the WHERE clause: a. can use aggregate search conditions but can't use non-aggregate search conditions b. can use either aggregate search conditions or non-aggregate search conditions c. can use non-aggregate search conditions but can't use aggregate search conditions d. must refer to columns in the SELECT clause

c. can use non-aggregate search conditions but can't use aggregate search conditions

Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union: a. is coded in the ORDER BY clause b. combines columns from the same table c. combines the result sets of two or more SELECT statements d. by default, does not eliminate duplicate rows

c. combines the result sets of two or more SELECT statements

Expressions coded in the WHERE clause 1. can use aggregate search conditions but can't use non-aggregate search conditions 2. can use either aggregate search conditions or non-aggregate search conditions 3. can use non-aggregate search conditions but can't use aggregate search conditions 4. must refer to columns in the SELECT clause

can use non-aggregate search conditions but can't use aggregate search conditions

Assuming that all of the table and column names are spelled correctly, what's wrong with the INSERT statement that follows?INSERT INTO invoices(vendor_id, invoice_number, invoice_total, payment_total, credit_total,terms_id, invoice_date, invoice_due_date) VALUES(97, '456789', 8344.50, 0, 0, 1, '2012-08-31'). a. There are too many items in the column list. b. The column names in the column list are in the wrong sequence. c. There are too few items in the VALUES list. d. The number of items in th

d. The number of items in the column list doesn't match the number in the VALUES list.

If you assign an alias to one table in a join, you have to a. assign them to all of the tables b. qualify every column name in the query c. qualify all of the column names for that table d. use that alias to refer to the table throughout your query

d. use that alias to refer to the table throughout your query

In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.

foreign

A natural join combines tables based on columns in the two tables that ______________________.

have the same name

Which of the following expressions does not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total? invoice_total - credit_total - payment_total / 10 (invoice_total - payment_total - credit_total) / 10 (invoice_total - (payment_total + credit_total)) * 0.10 ((invoice_total - payment_total) - credit_total) / 10

invoice_total - credit_total - payment_total / 10

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less? invoice_total <= 1000 NOT (invoice_total > 1000) invoice_total IN (0, 1000) invoice_total BETWEEN 0 AND 1000

invoice_total IN (0, 1000)

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax: a. is easier to read and understand b. lets you separate the join and search conditions c. is an older syntax that works with legacy code d. lets you combine inner and outer joins

lets you separate the join and search conditions

When you code a column list in an INSERT statement, you can omit columns with degault values and columns that allow ____ values.

null

SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC When this query is executed, the result set will contain: 1. one row for the invoice with the largest balance due for each vendor 2. one row f

one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices

The ____ of a numeric data type indicates the total number of digits that can be stored in the data type.

precision

In a cross join, all of the rows in the first table are joined with all of the: 1. distinct rows in the second table 2. rows from the second table 3. unmatched columns in the second table 4. matched rows in the second table

rows from the second table

To insert several rows into a table with an INSERT statement, you code a/an ____ in place of the VALUES clause.

subquery

A SELECT statement that includes aggregate functions is often called a/an ____ query.

summary

A subquery factoring clause creates a temporary ____ that can be used by a query that is coded after the subquery factoring clause.

table

When you use the USING keyword for a join: 1. the join can't be done on more than one column 2. the join must be based on a column or columns that have the same name in both tables 3. the join must be an inner join 4. the join can't be an outer join

the join must be based on a column or columns that have the same name in both tables

SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC When this query is executed, each row in the result table will show: 1. the largest invoice total related to that row, but only if it's larger than the average for all invoic

the largest invoice total related to that row

The DATE data type includes not only the date, but also a ____.

time

If you assign an alias to one table in a join, you have to: 1. assign them to all of the tables 2. qualify every column name in the query 3. qualify all of the column names for that table 4. use that alias to refer to the table throughout your query

use that alias to refer to the table throughout your query

In a join, column names need to be qualified only

when the same column names exist in both tables

A ____________ clause can refer to any column in the base table, but a ____________ clause can only refer to a column included in the SELECT statement.

WHERE, HAVING

You can use the ______________________ operator to add one or more summary rows to a result set that uses grouping and aggregates.

WITH ROLLUP

SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id The total number of rows returned by this query must equal: a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above

a. the number of rows in the Invoices table

A summary query is a SELECT statement with one or more ____________________.

aggregate functions

__________________ also called column functions, perform a calculation on the values in a set of selected rows.

aggregate functions

A table ___________________ can be used when you want to assign a temporary name to a table.

alias

SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id The "v" in this example is known as a/an ____________________________________.

alias

SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id The "v" in this example is known as a/an ____________________________________.

alias