MySQL chapter 08

data access model communicate with the database

A database driver is software that lets the data access model communicate with the database application program communicate with the database data access model communicate with the application program application program communicate with the data access model

unmatched rows from both the left and right tables

A full outer join returns unmatched rows from both the left and right tables rows in the left table that don't satisfy the join condition rows in the right table that don't satisfy the join condition the Cartesian product of the two tables

alias

A table _________________ can be used when you want to assign a temporary name to a table. alias widget module label

SELECT statements result tables queries result sets

A union combines the rows from two or more _______________________.

COUNT(*)

All of the aggregate functions ignore null values, except for the ______________________________ function. KEY HAVING ERR COUNT(*)

32492.05

All of the following values can be stored in a column that's defined as DECIMAL(6,2), except -246.29 0 2479.95 32492.05

a table

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) a single value a column of one or more rows a table a subquery can't be introduced in this way

a subquery can't be introduced in this way

If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) a single value a column of one or more rows a table a subquery can't be introduced in this way

a column of one or more rows

If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery) a single value a column of one or more rows a table a subquery can't be introduced in this way

a column of one or more rows

If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery) a single value a column of one or more rows a table a subquery can't be introduced in this way

use that alias to refer to the table throughout your query

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

WHERE

If you want to filter the rows that are returned by a SELECT statement, you must include a/an ______________ clause. SELECT TABLE WHERE GOTO

rows from the second table

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

when the same column names exist in both tables

In a join, column names need to be qualified only in inner joins when the code is confusing in outer joins when the same column names exist in both tables

equal

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

combines the result sets of two or more SELECT statements

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

integers

Numbers that don't include a decimal point are known as ____________________. integers real abstract fraction

dates only

The DATE data type can store dates only dates and times times only all of the above

time

The DATETIME data type includes not only the date, but also a ________________________. string value whole number time

mushrooms, sausage, peppers

The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type? small, medium, large yes, no, maybe mushrooms, sausage, peppers cash, credit, debit

CAST

The _____________________ function is an ANSI-standard function that you can use to perform an explicit conversion. BREAK FORMAT CAST CONVERT

yyyy-mm-dd

The default date format for MySQL is _________________. mm/dd/yy mon-dd-yyyy yyyy-mm-dd yy/dd/mm

Not, And, Or

The order of precedence for the logical operators in a WHERE clause is Or, And, Not And, Or, Not Not, And, Or Not, Or, And

a result set

The result of a SELECT statement is a stored procedure a calculated value a result set

CONCAT

To concatenate character strings, you use the _________________ function in a string expression. WHERE CONCAT >> STRING

DISTINCT

To prevent duplicate rows from being returned by a SELECT statement, you can code the _____________ keyword in the SELECT clause. DUPLICATE UNIQUE LAST DISTINCT

*

To return all of the columns from the base table, you can code the _______________ operator in the SELECT clause. * TOTAL ALL ?

implicit

When MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion. implicit complex transmutation switch

invoice_total IN (0, 1000)

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 IN (0, 1000) invoice_total <= 1000 NOT (invoice_total > 1000) invoice_total BETWEEN 0 AND 1000

payment_date IS NOT NULL AND invoice_total >= 500

When coded in a WHERE clause, which search condition will return invoices when payment_date isn't null and invoice_total is greater than or equal to $500? NOT (payment_date IS NULL AND invoice_total <= 500) payment_date IS NOT NULL OR invoice_total >= 500 payment_date IS NULL AND invoice_total > 500 payment_date IS NOT NULL AND invoice_total >= 500

null

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

The result sets must be derived from different tables.

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 corresponding columns in the result sets must have compatible data types. The result sets may or may not have any duplicate rows. The result sets must be derived from different tables.

column name, alias, expression, or column number

When you code an ORDER BY clause, you can specify a column name or alias only column name, alias, or expression only column name or expression only column name, alias, expression, or column number

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

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

you code a USING clause in addition to the ON clause

Which is not true about the USING keyword? you code a USING clause in addition to the ON clause the join must be an equijoin, meaning the equals operator is used to compare the two columns it can be used with inner or outer joins you use it to simplify the syntax for joining tables

WHERE vendor_name < 'D'

Which of the following WHERE clauses will return vendor names from A to C? WHERE vendor_name < 'D' WHERE vendor_name = 'D' WHERE vendor_name < 'C' WHERE vendor_name = D

invoice_total - credit_total - payment_total / 10

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 - payment_total) - credit_total) / 10 (invoice_total - payment_total - credit_total) / 10 invoice_total - credit_total - payment_total / 10 (invoice_total - (payment_total + credit_total)) * 0.10

is an older syntax that works with legacy code

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

they all use the same amount of storage

Which uses the least amount of storage? 'example' stored in a column of type CHAR(20) 'exam' stored in a column of type CHAR(20) 'ex' stored in a column of type CHAR(20) they all use the same amount of storage

'ex' stored in a column of type VARCHAR2(20)

Which uses the least amount of storage? 'example' stored in a column of type VARCHAR2(20) 'exam' stored in a column of type VARCHAR2(20) 'ex' stored in a column of type VARCHAR2(20) they all use the same amount of storage

select

You can combine inner and outer joins within a single ________________ statement. select where using xor

a union

You can simulate a full outer join by using a left outer join a union a self join the ON clause