SQL programming Chap. 5-8 – Flashcards

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

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

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

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.

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.

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

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

Expressions coded in the HAVING clause

Expressions coded in the WHERE clause

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

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

Code example 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 (Please refer to code example) When this summary query is executed, the result set will contain one summary row for

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

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

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

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

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

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

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

Code example 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 (Please refer to code example 7-1.) When this query is executed, the result set will contain

Code example 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 (Please refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number

Code example 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 (Please refer to code example 7-1.) When this query is executed, the rows will be sorted by

Code example 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 (Please refer to code example 7-2.) When this query is executed, the result table will contain one row for

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

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

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

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

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

Which uses the least amount of storage?

The DATE data type can store

The default date format for MySQL is _________________.

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

Scalar Function

Aggregate Function

Column Functions

summary query

subquery

Four ways to introduce a subquery

Nested Subquery

Correlated subquery

Uncorrelated subquery

Inline View

Comment

pseudocode

Implicit conversion

explicit conversion