DBA-120: Module 1-10 example questions

USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; DECLARE @Total money; SET @Total = 0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC; IF @InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); (Refer to code example 14-2.) What is the maximum value of the @Total variable?

200,000.00

If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? CASE WHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2) WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2) ELSE 0 END AS Solution

225.00

If ExpirationDate contains a value that's equivalent to June 2, 2016 and the GetDate function returns a value that's equivalent to July 17, 2016, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution

45

Which of the following statements calls the stored procedure and passes the values '2015-12-01' and 122 to its input parameters? CREATE PROC spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar;

EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2015-12-01';

Which of the following statements calls the following stored procedure, passes the value '2015-12-01' to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal? CREATE PROC spInvoiceTotal2 @DateVar smalldatetime, @InvoiceTotal money OUTPUT AS SELECT @InvoiceTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar; (Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position.)

EXEC spInvoiceTotal2 '2015-12-01', @MyInvoiceTotal OUTPUT;

USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; (Refer to code example 14-1.) If the current date is 03/15/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?

Earliest past due date: 02/09/16

USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; (Refer to code example 14-1.) If the current date is 04/04/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?

Earliest past due date: 02/09/16 Latest past due date: 03/20/16

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

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

USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; DECLARE @Total money; SET @Total = 0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC; IF @InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); (Refer to code example 14-2.) What can cause the WHILE loop in this script to end other than the expression on the statement becoming true?

When the value of the @InvoiceTotal variable becomes less than 1000

USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; DECLARE @Total money; SET @Total = 0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC; IF @InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); (Refer to code example 14-2.) When does the expression on the WHILE statement in this script cause the loop to end?

When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000

SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it a. all of the above CorrectCorrect b. includes summary rows c. allows you to use additional sets of parentheses to create composite groups d. adds summary rows for specified groups

all of the above

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; (Please refer to code example 6-2.) In this query, the table named Top5 is coded as a

common table expression (CTE)

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; (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 Vendors ON 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

If FirstName contains "Edward" and LastName contains "Williams", what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution

ewilliam

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

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. none of the above b. the number of rows in the Vendors table c. the number of rows in the Invoices table d. the number of rows in the Invoices table plus the number of rows in the Vendors table

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

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

If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaludate to? ISNUMERIC(ZipCode) AS Solution

true

The statement CREATE VIEW Example1 AS SELECT VendorName, SUM(InvoiceTotal) AS SumOfInvoices FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID GROUP BY VendorName ORDER BY VendorName;

will fail because the ORDER BY clause isn't allowed in this view

The statement CREATE VIEW Example2 AS SELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate) FROM Invoices;

will fail because the second column isn't named