CSE 581 Exam 1 Review

If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? LEN(LTRIM(RTRIM(CustomerAddress))) AS Solution

19

When you use weekday with the DATEPART function, it returns an integer that indicates the day of the week where

1=Sunday, 2=Monday, etc.

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

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM InvoicesGROUP BY VendorIDORDER 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, there will be how many rows in the result table?

5

If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed? DATEPART(month, RegistrationDate) AS Solution

8

To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what?

A Comma

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? WHERE (subquery)

A subquery cannot be introduced this way

Which keyword lets you control the number of rows that are returned by a query?

ALL, DISTINCT, TOP

When coding search conditions, you can use which keyword to create compound search conditions?

AND

Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values

AVG(InvoiceTotal)

Which of the following statements best describes what this INSERT statement does? INSERT INTO InvoiceArchive SELECT * FROM Invoices WHERE TermsID = 1;

Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table.

Which functions perform a calculation on the values of a column from selected rows?

Aggregate

The search condition of a WHERE clause consists of one or more ________

Boolean Expressions

When you use the SELECT INTO technique to create tables

C is the correct answer (a and b) A)only the column definitions and data are copied B)primary keys, foreign keys, and default values aren't retained C)a and b D)none of the above

Which function is typically used to insert control characters into a character string?

CHAR

If a string consists of one or more components, you can parse it into its individual components. To locate the characters that separate the components, you would use which function?

CHARINDEX

To locate the index of the first character of the first occurence of a substring within another string, you would use which function?

CHARINDEX

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM InvoicesGROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top5 ON Invoices.VendorID = Top5.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; In this query, the table named Top5 is coded as a

Common Table Expression (CTE)

All of the following values can be stored in a column that's defined as decimal(6,2), except

D is the Correct Answer A)-246.29 B)0 C)2479.95 D)32492.05

If you code a column list in an INSERT statement that includes a column that has a default value, which keyword can you code in the VALUES clause to use the default value?

DEFAULT

To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause?

DESC

By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?

DISTINCT

To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause?

DISTINCT

The varchar and nvarchar data types to store variable-length strings. Which of the statements below is true?

Data stored using these data types occupies only the number of bytes needed to store the string

SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID; 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

This join is coded using what syntax? SELECT VendorName, InvoiceDate FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

Explicit Syntax and SQL-92

Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows?

FETCH

A subquery can be coded in a WHERE, FROM, SELECT, or _____ Clause

HAVING

Which of the following statements is true?

Implicit data type conversion is performed when you mix values of different data types in an expression.

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 ______

Includes summary rows, adds summary rows for specified groups, and allows you to use additional sets of parentheses to create composite groups.

Which data types are used to store whole numbers?

Integer

Which statements are true about the ROUND function?

It returns the number rounded to the specified precision

The implicit syntax lets you combine _____

Join and Search Conditions Ex. SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name; Where explicit is: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Which function would you use to retrieve data from a subsequent row in a result set? And which function would you use to retrieve data from a previous row?

LEAD, LAG

After locating the characters that separate the components of a string you wish to parse, you can use which functions to extract the individual components?

LEFT, RIGHT, SUBSTRING, and LEN

Which function returns the string with any leading spaces removed?

LTRIM

Subqueries can be ________________ within other subqueries.

Nested

The order of precedence for the logical operators in a WHERE clause is _____

Not, And, Or

Which Clause Specifies the number of rows that should be skipped before rows are returned from the result set?

OFFSET

Which function would you use to calculate the rank of the values in a sorted set of values as a percent?

PERCENT_RANK

To override the order of precedence in an arithmetic expression, you can use

Parentheses

When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

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

A union combines the rows from two or more what?

SELECT Statements, Result Tables, and Queries

The six clauses of the SELECT statement must be coded in the following order:

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

Which function returns the specified number of characters from the string starting at the specified position?

SUBSTRING

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; 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

In the INSERT statement that follows, assume that all of the table and column names are spelled correctly, that none of the columns are identity columns, and that none of them have default values or accept null values. What's wrong with the statement? INSERT INTO InvoiceCopy( VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal,TermsID, InvoiceDate, InvoiceDueDate) VALUES(97, '456789', 8344.50, 0, 0, 1, '2016-08-01');

The number of items in the column list doesn't match the number in the VALUES list.

SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal

The number of rows in the right table that has matching records in the left table, plus the NULL values

SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal

The sum of the number of rows in Invoices and the number of rows in Vendors, minus the number of matching rows between the two tables.

Since the MERGE operation often involves updating existing rows and inserting new rows, the MERGE statement is sometimes referred to as what?

The upsert statement

When you code a DELETE statement for one or more rows, which clause specifies which row or rows are to be deleted?

WHERE

When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the _________________ clause specifies which row or rows are to be updated.

WHERE

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

You can't update _________

an identity column

The IIF function determines the value it returns based on what type of expression?

conditional

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM InvoicesGROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top5 ON Invoices.VendorID = Top5.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; When this query is executed, the result table will contain one row for ______

each vendor in the Top5 table

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

The integer and decimal data types are considered ________________ because their precision is exact.

exact numeric data types

Which uses the least amount of storage?

exam stored in a column type nchar(20) example stored in a column of type nchar(20) ex stored in a column of type nchar (20) They all use the same amount of storage

A CUBE operator is similar to the ROLLUP operator except that _____

it adds summary rows for every combination of groups

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM InvoicesGROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top5 ON Invoices.VendorID = Top5.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; In this query, the table named Top5 is used as part of a _____

join

The COALESCE function provides one way to substitute constant values for which values?

null

The GROUPING function lets you substitute another value for a/an ____________________ value when you use the ROLLUP or CUBE operator.

null

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 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; 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

The ranking functions make it easy to include a column in a result set that provides the sequential ranking number of each row within a ___________________________

partition

A full outer join includes rows that satisfy the join condition, plus

rows in both tables that don't satisfy the join condition.

SQL Server supports ________________ of the ANSI-standard data types

some, but not all

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

true

Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the datetime data type and which might include time values?

use the DatePart function to extract just the date from each datetime value

Which choice below will increase the storage capacity of a varchar column so it can store up to 2 gigabytes of data?

varchar (max)