Logo

Go on. Explore!

lvl 0 • 0 xp

lvl 1

HomeMarketDecksInterests
MySQL 2nd Edition Chapter 07

MySQL 2nd Edition Chapter 07

memorize.aimemorize.ai (lvl 286)
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%
(0)
(0)
(0)
11 cards
()
Section 1

Preview this deck

B

Front

1 / 11

SQL
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

7 years ago

Date created

Mar 1, 2020

Cards (11)

Section 1

(11 cards)

B

Front

Code example 6-1 -------------------------------------------------------- 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 6-1.) When this query is executed, the result set will contain =================================================== A) one row for each invoice that has a larger balance due than the average balance due for all invoices B) 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 C) one row for the invoice with the largest balance due for each vendor D) one row for each invoice for each vendor that has a larger balance due than the average balance due for all invoices

Back

C

Front

Code example 6-1 -------------------------------------------------------- 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 6-1.) When this query is executed, the number_of_invoices for each row will show the number =================================================== A) of invoices in the Invoices table B) of invoices for each vendor C) of invoices for each vendor that have a larger balance due than the average balance due for all invoices D) 1

Back

value

Front

A subquery can return a single __________, a list of values, or a table of values.

Back

B

Front

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

Back

alias

Front

When you code a subquery in a FROM clause, you must assign a/an ______________________ to it.

Back

C

Front

Code example 6-1 -------------------------------------------------------- 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 6-1.) When this query is executed, the rows will be sorted by =================================================== A) invoice_id B) vendor_id and then by balance_due in descending sequence C) balance_due in descending sequence D) vendor_id

Back

SELECT

Front

A subquery is a/an ____________________ statement that's coded within another SQL statement.

Back

C

Front

Code example 6-2 -------------------------------------------------------- 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 6-2.) When this query is executed, there will be one row =================================================== A) for each vendor B) for each vendor with a maximum invoice total that's greater than 100 C) for each vendor with an average invoice total that's greater than 100 D) for each invoice with an invoice total that's greater than the average invoice total for the vendor and also greater than 100

Back

HAVING

Front

A subquery can be coded in a WHERE, ______________, FROM, or SELECT clause.

Back

join

Front

In many cases, a subquery can be restated as a/an ______________.

Back

inline

Front

When you code a subquery in a FROM clause, it returns a result set that can be referred to as an _____________________________ view.

Back