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