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