database final

Which of the following isn't a valid column alias?

"Total Sales"

Which uses the least amount of storage? var

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

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))

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

-45

If you TRUNCATE the number 17.99, you get a value of:

17

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

32492.05

If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery)

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 column of one or more rows

The result of a SELECT statement is

a result set

If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)

a single value

If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)

a single value

To be in the first normal form, each cell in a table must contain

a single, scalar value

If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)

a subquery can't be introduced in this way

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)

a table

You can simulate a full outer join by using

a union

To enforce referential integrity for a delete operation, a MySQL database can

a, b, or c

Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause

all of the above

Which code returns the date in the format Friday, April 20th, 2015?

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

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

Delete from Invoices_Copy

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

LIMIT 7, 4

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

LOB

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

Not, And, Or

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

Null

Which of the following types of statements does not modify the data in a table?

SELECT

Which of the following is not a good guideline for deciding when to create an index for a column?

The column is frequently updated.

SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state < 'e' GROUP BY vendor_state, vendor_city, vendor_name HAVING SUM(invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name Although this query runs as coded, it contains this logical error:

The column name for the fifth column in the result set doesn't match the data.

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

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

When you code a union that combines two result sets, which of the following is not true?

The result sets must be derived from different tables.

Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_id, SUM(invoice_total - payment_total - credit_total) AS column_2 FROM invoices WHERE invoice_total - payment_total - credit_total > 0 GROUP BY vendor_id

The total unpaid balance due for each vendor_id

Which is not true about indexes?

They can help speed up insert, update and delete operations on columns that are updated frequently.

The default storage engine for MySQL 5.5 and later. This engine supports foreign keys and transactions

InnoDB

Which of the following statements best describes what this INSERT statement does? INSERT INTO invoices_copy SELECT * FROM invoices WHERE terms_id = 1

Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table.

Which of the following is true about creating indexes?

All of the above

Which is true when you define a column as the primary key?

All of the above.

The interface between an application program and the DBMS is usually provided by the

data access API

A database driver is software that lets the

data access model communicate with the database

The DATE data type can store

dates only

Which of the following does not violate the referential integrity of a database?

deleting a row in a foreign key table without deleting the related row in the related primary key table

To be in the third normal form,

each non-key column must depend only on the primary key

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

integers

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

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)

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?

payment_date IS NOT NULL AND invoice_total >= 500

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 known as ____________________.

real numbers

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This

reduces redundancy and makes maintenance easier

In a cross join, all of the rows in the first table are joined with all of the

rows from the second table

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.

subquery

If you define a column with a default value, that value is used whenever a row

that doesn't include a value for that column is added to the table

When you define a foreign key constraint, you can determine all but one of the following. Which one is it?

that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table

When you define a column with the DEFAULT attribute:

the default value is used if another value isn't specified when a row is added to the database.

When you use the USING keyword for a 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

the largest invoice total related to that row

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

the number of rows in the Invoices table

When you identify the data elements in a new database, you typically subdivide data elements into

the smallest practical components

Which uses the least amount of storage? char

they all use the same amount of storage

The utf8 character set uses how many bytes per character?

three

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

time

When a column in a table is defined, it is given a data ________________ that determines what kind of data it can store.

type

A full outer join returns

unmatched rows from both the left and right tables

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

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?

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

When coded in a WHERE clause, which of the following would not return rows for vendors in the cities of San Diego and Santa Ana?

vendor_city REGEXP 'NA$'

In a join, column names need to be qualified only

when the same column names exist in both tables

Which is not true about the USING keyword?

you code a USING clause in addition to the ON clause

When you create a script for creating a database:

you need to create the referred to tables before you create the tables that refer to them

The default date format for MySQL is _________________.

yyyy-mm-dd

The processing that's done by the DBMS is typically referred to as

back-end processing

Expressions coded in the HAVING clause

can use either aggregate search conditions or non-aggregate search conditions

Expressions coded in the WHERE clause

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

In the collation, utf8_spanish_ci, the ci stands for:

case-insensitive

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union

combines the result sets of two or more SELECT statements

Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements?

By default, the changes are automatically committed to the database.

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

CAST

The __________ function returns the smallest integer that is greater than or equal to the number.

CEILING

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

The _________ function concatenates a string with the specified separator string added in between

CONCAT_WS

Which of the following types of SQL statements is not a DML statement?

CREATE TABLE

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

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 six clauses of the SELECT statement must be coded in the following order:

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 delete all data from a table without deleting the definition for the table, you use the

TRUNCATE TABLE

Which of the following recommendations won't improve the readability of your SQL statements?

Use comments to describe what each statement does

Which of the following WHERE clauses will return vendor names from A to C?

WHERE vendor_name < 'D'

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

Where

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 rows will be sorted by

balance_due in descending sequence

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, the result table will contain one row for

each vendor in the table named ia

SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state < 'e' GROUP BY vendor_state, vendor_city, vendor_name HAVING SUM(invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name When this summary query is executed, the result set will contain one summary row for

each vendor with invoice totals over $500

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

ewilliam

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, there will be one row

for each vendor with an average invoice total that's greater than 100

To relate one table to another, a/an _______________________ in one table is used to point to the primary key in another table.

foreign key

Which feature does the MyISAM storage engine not support?

foreign keys

You can't code which one of the following as part of a CREATE TABLE statement

function-based indexes

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

implicit

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

invoices, but only if that balance due is larger than the average balance due for all invoices

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax

is an older syntax that works with legacy code

Denormalization typically results in all but one of the following. Which one is it?

more complicated SQL coding

The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type?

mushrooms, sausage, peppers

SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal

none of the above

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 number_of_invoices for each row will show the number

of invoices for each vendor that have a larger balance due than the average balance due for all invoices

The latin1 character set uses how many bytes per character?

one