Consider the following code example:
SELECT VendorName, COUNT (*) AS NumberOfInvoices,
MAX (InvoiceTotal - PaymentTotal - CreditTotal) AS BalaneDue
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal - PaymentTotal - CreditTotal >
(SELECT AVG (InvoiceTotal - PaymentTotal- CreditTotal)
FROM Invoices)
GROUP BY VendorName
ORDER BY BalanceDue DESC;
When this query is executed, the NumberOfInvoices column for each row will show the number
answer
Of invoices for each vendor that have a larger balance due than the average balance due for all invoices
question
Consider the following code example:
WITH Top5 AS
(SELECT TOP 5 VendorID, AVG (InvoiceTotal) AS AvgInvoice
FROM Invoices
GROUP BY VendorID
ORDER BY AvgInvoice DESC)
SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice
FROM Invoices JOIN Top10
ON Invoices.VendorID = Top10.VendorID
GROUP BY Invoices.VendorID
ORDER BY LargestInvoice DESC;
When this query is executed, the result table will contain one row for
answer
Each vendor in the Top10 table
question
Subqueries can be ______ within other subqueries
answer
Nested
question
A correlated subquery is one that
answer
Is executed once for each row in the outer query
question
If introduced as follows, the subquery can return which of the values listed below?
WHERE InvoiceTotal > ALL (subquery)
answer
A column of one or more rows
question
If introduced as follows, the subquery can return which of the values listed below?
WHERE 2 < (subquery)
answer
a single value
question
If introduced as follows, the subquery can return which of the values listed below?
FROM (subquery)
answer
A table
question
Consider the following code example:
WITH Top5 AS
(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AVGInvoice
FROM Invoices
GROUP BY VendorID
ORDER BY AVGInvoice DESC)
SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice
FROM Invoices JOIN Top10
ON Invoices.VendorID = Top10.VendorID
GROUP BY Invoices.VendorID
ORDER BY LargestInvoice DESC;
In this query, the table namedn Top5 is used as part of a
answer
join
question
A subquery can be coded in a WHERE,FROM,SELECT,or ______ clause
answer
Having
question
A common table expression (CTE) creates a temporary _______ that can be used by a query that follows
answer
table
question
If introduced as follows, the subquery can return which of the values listed below?
SELECT (subquery)
answer
A single value
question
If introduced as follows, the subquery can return which of the values listed below?
WHERE (subquery)
answer
A subquery can't be introduced in this way
question
A subquery is a/an_____ statement that's coded within another SQL statement.
answer
SELECT
question
Consider the following code example:
WITH Top5 AS
(SELECT TOP 5 VendorID, AVG (InvoiceTotal) AS AvgInvoice
FROM Invoices
GROUP BY VendorID
ORDER BY AvgInvoice DESC)
SELECT Invoices.VendorID, MAX (Invoices.InvoiceTotal) AS LARGESTInvoice
FROM Invoices JOIN Top10
ON Invoices.VendorID = Top10.VendorID
GROUP BY Invoices.VendorID
ORDER BY LargestInvoice DESC;
When this query is executed, each row in the result table will show
answer
The largest invoice amount related to that row
question
Consider the following code example:
WITH Top5 AS
(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice
FROM Invoices
GROUP BY VendorID
ORDER BY AvgInvoice DESC)
SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice
FROM Invoices JOIN Top10
ON Invoices.VendorID = Top10.VendorID
GROUP BY Invoices.VendorID
ORDER BY LargestInvoices DESC;
When this query is executed, each row in the result table will show
answer
The largest invoice amount related to that row
question
Consider the following code example:
WITH Top5 AS
(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice
FROM Invoices
GROUP BY VendorID
ORDER BY AvgInvoice DESC)
SELECT Invoices.VendorID, MAX(Invoice.InvoiceTotal) AS LargestInvoice
FROM Invoices JOIN Top10
ON Invoices.VendorID = Top10.VendorID
GROUP BY Invoices.VendorID
ORDER BY LargestInvoice DESC;
In this query, the table named Top5 is coded as a
answer
Common table expression (CTE)
question
A subquery that's used in a WHERE or HAVING clause is called what?
answer
A subquery search condition
question
To test whether one or more rows are returned by a subquery, you can use which operator?
answer
Exists
question
In many cases, a subquery can be restated as a /an _______.
answer
Join
question
Consider the following code example:
SELECT VendorName, COUNT (*) AS NumberOfInvoices,
MAX (InvoiceTotal - PaymentTotal- CreditTotal) AS BalanceDue
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal- PaymentTotal- CreditTotal >
(SELECT AVG(InvoiceTotal-PaymentTotal- CreditTotal) FROM Invoices)
GROUP BY VendorName
ORDER BY BalanceDue DESC;
When this query is executed, the result set will contain
answer
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
question
Consider the following code example:
WITH Top AS
(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice
FROM Invoices
GROUP BY VendorID
ORDER BY AvgIncvoice DESC)
SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice
FROM Invoices JOIN Top10
ON Invoices.VendorID = Top10.VendorID
GROUP BY Invoices.VendorID
ORDER BY LargestInvoice DESC;
When this query is executed, there will be how many rows in the result table?
answer
5
question
If introduced as follows, the subquery can return which of the values listed below?
WHERE VendorID NOT IN (subquery)
answer
A column of one or more rows
question
If introduced as follows, the subquery can return which of the values listed below?
WHERE 2< (subquery)
answer
A single value
Haven't found what you need?
Search for quizzes and test answers now
Quizzes.studymoose.com uses cookies. By continuing you agree to our cookie policy
