CIT111 - STUDY SET

If expiration_date contains a value that's equivalent to November 2, 2011 and the SYSDATE function returns a value that's equivalent to December 17, 2011, what will the exp_days column contain when this code is executed? expiration_date - SYSDATE() AS exp_days A. -45 B. -1.5 C. 45 D. 1.5

A. -45

The DATE data type can store A. dates only B. dates and times C. times only D. all of the above

A. dates only

If you TRUNCATE the number 17.99, you get a value of: A. 18 B. 17 C. 20 D. 17.5

B. 17

Which of the following statements best describes what this SELECT statement does? SELECT invoice_number, invoice_date, CASE WHEN (SYSDATE() - invoice_date) >= 30 AND (SYSDATE() - invoice_date) < 60 THEN invoice_total ELSE 0 END AS "30-60", CASE WHEN (SYSDATE() - invoice_date) >= 60 AND (SYSDATE() - invoice_date) < 90 THEN invoice_total ELSE 0 END AS "60-90", CASE WHEN (SYSDATE() - invoice_date) > 90 THEN invoice_total ELSE 0 END AS "Over 90" FROM invoices A. Displays three columns for each invoice with a value in the third column that indicates how many days have elapsed between the invoice date and the current date. B. Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date. C. Displays three columns for each invoice with one of these messages in the third column: 30-60, 60-90, or Over 90. D. Displays five columns for each invoice with a message in one of the last three columns based on how many days have elapsed between the invoice date and the current date.

B. Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date.

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

BLOB

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

C. 'ex' stored in a column of type CHAR(20)

The _________ function concatenates a string with the specified separator string added in between. A. LTRIM B. CONCAT C. CONCAT_WS D. SUBSTRING_INDEX

C. CONCAT_WS

Which code returns the date in the format Friday, April 20th, 2015? A. DATE_FORMAT('2014-04-20', '%W, %M %D, %Y') B. DATE_FORMAT('2015-04-20', '%m/%d/%y') C. DATE_FORMAT('2015-04-20', '%W, %M %D, %Y') D. DATE_FORMAT('2015-04-20', '%e-%b-%y')

C. DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')

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

C. mushrooms, sausage, peppers

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

C. yyyy-mm-dd

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

CAST

Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type:

CAST(invoice_date AS DATETIME)

The _________________ data type is used for fixed-length strings, which use the same amount of storage for each value regardless of the actual length of the string.

CHAR

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

D. 32492.05

The __________ function returns the smallest integer that is greater than or equal to the number. A. FLOOR B. TRUNCATE C. SIGN D. CEILING

D. CEILING

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. The column names in the column list are in the wrong sequence. B. There are too few items in the column list. C. There are too many items in the VALUES list. D. The number of items in the column list doesn't match the number in the VALUES list.

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

Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause A. use the CAST function to convert the column values to numbers B. add zeros to the column values using + 0 C. use the LPAD function to pad the numbers with leading zeros D. all of the above

D. all of the above

If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(LEFT(first_name,1) + LEFT(last_name,7)) AS solution A. EWilliams B. EWilliam C. ewilliams D. ewilliam

D. ewilliam

Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the DATETIME data type? A. use the MONTH, DAYOFMONTH, and YEAR functions in the WHERE clause to search for just those components B. search for a range of dates that are greater than or equal to the date you're looking for, and less than the date that follows the date you're looking for C. use the DATE_FORMAT function in the WHERE clause to return a formatted string that only contains the month, day, and year D. use the DATE_ADD function to remove the time values from the dates in each column

D. use the DATE_ADD function to remove the time values from the dates in each column

You use the ______________ to add a specified number of date parts to a date.

DATE_ADD

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

The __________ function lets you test an expression and return one value if the expression is true and another value if the expression is false.

IF

Numbers that don't include a decimal point are known as ____________________.

INT

Use the _____________ function to find a character in a string.

LOCATE

If an error occurs, each of the numeric functions returns a/an ____________ value.

NULL

The COALESCE function provides one way to substitute other values for __________________________ values.

NULL

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

NULL

Write the code for a SELECT statement that uses the ROUND function to return the payment_total column with 1 decimal digit.

ROUND(payment_total , 1) (don't forget the space

To insert rows selected from one or more tables into another table with an INSERT statement, you code a/an ___________________________ in place of the VALUES clause.

SELECT

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

SELECT

To format TIME values, you use the _______________ function.

TIME_FORMAT

The ___________ function returns the string with any leading spaces removed.

TRIM

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 MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion.

implicit

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

precision

Numbers that include a decimal point are knows as ____________________.

real numbers

The DATETIME data type includes not only the date, but also a ________________________.

time