Database programming exam 1 review part2
WITH Top10 AS ( SELECT TOP 10 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; (Please refer to code example 6-2.) When this query is executed, there will be how many rows in the result table?
10
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
Which functions perform a calculation on the values of a column from selected rows?
Aggregate
All of the aggregate functions ignore null values, except for which function?
COUNT(*)
Write an aggregate expression for the number of entries in the VendorName column, including null values
COUNT(*)
Write an aggregate expression for the number of unique values in the VendorID column
COUNT(DISTINCT VendorID)
___________________ names can be used when you want to assign a temporary name to a table.
Correlation
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?
DISTINCT
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) The column name for the second column in the result set will be?
Date
To test whether one or more rows are returned by a subquery, you can use which operator?
EXISTS
Correlation names are temporary table names assigned in which clause?
FROM
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.
HAVING
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above
LR None of the above
Write an aggregate expression to find the latest date in the InvoiceDate column
MAX(InvoiceDate)
Write an aggregate expression to find the VendorName column that's last in alphabetical order
MAX(VendorName)
When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding which operator?
ROLLUP
A subquery is a/an ______________ statement that's coded within another SQL statement.
SELECT
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN VendorsON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) 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
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorState, COUNT(*) AS Column2 FROM Vendors GROUP BY VendorState HAVING COUNT(*) > 1;
The number of vendors in each state having 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 VendorID, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2 FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 GROUP BY VendorID;
The total unpaid balance due for each VendorID
When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause?
WHERE
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause?
WHERE
A search condition in the ________________ clause is applied before the rows are grouped while a search condition in the _________________ clause isn't applied until after the grouping.
WHERE, HAVING
If introduced as follows, the subquery can return which of the values listed below? WHERE InvoiceTotal > ALL (subquery)
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery)
a column of one or more rows
A join that joins a table with itself is called
a self-join
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)
a single value
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 (subquery)
a subquery can't be introduced in this way
A subquery that's used in a WHERE or HAVING clause is called what?
a subquery search condition
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)
a table
A union combines the rows from two or more what? a. SELECT statements b. result tables c. queries d. all of the above
all the above
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This join is coded using what syntax? a. implicit b. explicit c. SQL-92 d. both b and c
both b and c
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
When you need to code multiple conditions in a join, it's best to
code only join conditions in the ON clause
WITH Top10 AS ( SELECT TOP 10 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; (Please refer to code example 6-2.) In this query, the table named Top10 is coded as a
common table expression (CTE)
WITH Top10 AS ( SELECT TOP 10 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; (Please refer to code example 6-2.) When this query is executed, the result table will contain one row for
each vendor in the Top10 table
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN VendorsON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) When this summary query is executed, the result set will contain one summary row for
each vendor with invoice totals over $500
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
foreign
You can use the OVER clause with an aggregate function to
include the rows used to calculate the summary in the result set
When you code a union with the INTERSECT keyword to combine two result sets, the union
includes only rows that occur in both result sets
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This type of join is called a/an
inner join
A correlated subquery is one that
is executed once for each row in the outer query
The CUBE operator is similar to the ROLLUP operator except that
it adds summary rows for every combination of groups
In many cases, a subquery can be restated as a/an ______________.
join
WITH Top10 AS ( SELECT TOP 10 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; (Please refer to code example 6-2.) In this query, the table named Top10 is used as part of a
join
If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use what kind of join?
left outer
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax
lets you combine the join and seearch conditions
Subqueries can be ________________ within other subqueries.
nested
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table d. none of the above
none of the above
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; (Please refer to code example 6-1.) When this query is executed, the NumberOfInvoices column 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
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; (Please refer to code example 6-1.) When this query is executed, the result set will contain
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
A full outer join includes rows that satisfy the join condition, plus
rows in both tables that don't satisfy the join condition
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
A SELECT statement that includes aggregate functions is often called a/an ________________ query.
summary
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.
table
WITH Top10 AS ( SELECT TOP 10 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; (Please refer to code example 6-2.) When this query is executed, each row in the result table will show
the largest invoice amount related to that row
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) The total number of rows returned by this query must equal
the number of rows in the Invoices table
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) The name "v" is known as a? a. placeholder b. correlation name c. table alias d. both b and c
v both b and c
In a join, column names only need to be qualified where?
when the same names are used in both tables
If you assign a correlation name to one table in a join,
you have to use that name for the table in the query