Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

7 Quiz

memorize.ai (lvl 286)

(0)

21 cards

()

Section 1

Preview this deck

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

Front

1 / 21

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (21)

Section 1

(21 cards)

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

Front

SELECT

Back

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

Front

HAVING

Back

You can use the BLANK keyword in a WHERE clause to test whether a condition is true for one or more of the values returned by the subquery.

Front

ANY

Back

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

Front

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

Back

To test whether one or more rows are returned by a subquery, you can use the BLANK operator.

Front

EXISTS

Back

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

Front

a column of one or more rows

Back

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

Front

inline

Back

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

Front

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

Back

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

Front

alias

Back

Code example 7-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 7-2.) When this query is executed, the result table will contain one row for each vendor each invoice in the table named ia each vendor in the table named ia each invoice

Front

each vendor in the table named ia

Back

Code example 7-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 7-2.) When this query is executed, each row in the result table will show the largest invoice total related to that row, but only if it's larger than the average for all invoices the largest invoice total related to that row the average invoice total related to that row, but only if it's greater than 100 the average invoice total related to that row

Front

the largest invoice total related to that row

Back

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

Front

a table

Back

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

Front

a single value

Back

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 a column of one or more rows a table a single value

Front

a subquery can't be introduced in this way

Back

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

Front

a single value

Back

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

Front

value

Back

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

Front

balance_due in descending sequence

Back

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

Front

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

Back

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

Front

JOIN

Back

A correlated subquery is a subquery that is executed once for each BLANK in the main query.

Front

row

Back

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

Front

a column of one or more rows

Back