CSE581 Exam 1
You can use what data type to store a date that includes a time zone offset?
datetimeoffset
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)
a table
In a join, column names only need to be qualified where?
when the same names are used in both tables
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
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.
You can use the OVER clause with an aggregate function to
include the rows used to calculate the summary in the result set
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
Which data types are used to store whole numbers?
Integer
Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?
InvoiceTotal - CreditTotal - PaymentTotal / 10
Which statements are true about the ROUND function?
It returns the number rounded to the specified precision
Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 20th row?
ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS
Which function would you use to calculate the rank of the values in a sorted set of values as a percent?
PERCENT_RANK
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
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
The _____________________ of a decimal value indicates the total number of digits that can be stored in a decimal data type.
Precision
What uniquely identifies each row in a table?
Primary key
Within the Management Studio, you can build a SQL statement without having to write your own code by using the ________________.
Query Designer
Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it.
Restore
A relational database consists of one or more what?
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
Which of the following is not a valid column alias name?
Total Sales
What SQL dialect does Microsoft SQL Server use?
Transact-SQL
If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaludate to? ISNUMERIC(ZipCode) AS Solution True or False
True
TRUE OR FALSE Implicit data type conversion is performed when you mix values of different data types in an expression.
True
Which of the following recommendations won't improve the readability of your SQL statements?
Use comments to describe what each statement does.
When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause?
WHERE
You specify the conditions that must be met for a row to be deleted in the which clause?
WHERE
If you define a column as an identity column,
a number is generated for that column whenever a row is added to the table
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
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what?
Comma
The IIF function determines the value it returns based on what type of expression?
Conditional
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
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.
table
If you define a column with a default value, that value is used whenever a row
that doesn't include a value for that column is added to the table
Which choice below will increase the storage capacity of a varchar column so it can store up to 2 gigabytes of data?
varchar(max)
A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with.
diagram
Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL.
dialect/variant
Which function returns the specified number of characters from the string starting at the specified position?
SUBSTRING
SQL Server supports ________________ of the ANSI-standard data types
Some, but not all
When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?
TOP 10 InvoiceNumber
To return all of the columns from the base table, which wildcard character do you include in the SELECT clause?
*
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 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?
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
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)
A single value
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
If you use ________________ in the select list, you must name the column since that name is used in the definition of the new table.
Calculated values
Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function
CAST (InvoiceAmount AS varchar)
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
The difference between the CAST function and the CONVERT function is that the ________________ function accepts an optional style argument that lets you specify a format for the result
CONVERT
Which of the following statements is true about the CONVERT and TRY_CONVERT functions?
CONVERT returns an error if the expression can't be converted, but TRY_CONVERT returns a NULL value.
Which of the following is not a SQL DML statement?
CreateTable
Write the code for a DELETE statement that deletes every row in the Vendors table
DELETE Vendors;
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
SQL statements that define the tables in a database are referred to as ________________ statements.
Data Definition Language (DDL)
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
If you try to move a database file that's attached to a server, you'll get an error message that indicates the file is in use. To get around this, you need to ________________ the database from the server.
Detach
If you want to use the Management Studio to modify the data for a table, you can right-click on the table and select what?
Edit Top 200 Rows command
Correlation names are temporary table names assigned in which clause?
FROM
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 used as part of a
Join
What can you use to combine data from two or more tables into a single result set?
Join
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
Write an aggregate expression to find the latest date in the InvoiceDate column
MAX(InvoiceDate)
The three main hardware components of a client/server system are the clients, the server, and the ________________.
Network
Which clause specifies the number of rows that should be skipped before rows are returned from the result set?
OFFSET
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
When using the Query Designer, where is the generated SQL statement displayed?
SQL Pane
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.) 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.
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
Insert, Update, and Delete statements can be referred to as ________________ queries.
action
If you omit the WHERE clause from a DELETE statement
all rows in the table will be deleted
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
When you code an ORDER BY clause, you can specify a
column name, alias, expression, or column number
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)
When a column in a table is defined, what determines the kind of data it can store?
data type
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 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 search conditions
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, 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
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
When you use Windows authentication to connect to a database, SQL Server
uses the login name and password that you use for your PC to authorize your connection
