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)
