Database programming exam 1 review part3

Which uses the least amount of storage?

'ex' stored in a column of type varchar(20)

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

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

32492.05

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

AND

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.

Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function

CAST (InvoiceAmount AS varchar)

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.

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

You can use the DELETE statement to delete one or more rows from the table you name in the ________________ clause.

DELETE

Write the code for a DELETE statement that deletes every row in the Vendors table

DELETE Vendors;

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

Which of the following statements is true?

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

Which statements are true about the ROUND function?

It returns the number rounded to the specified precision

Which function returns the string with any leading spaces removed?

LTRIM

Write the code for an ORDER BY clause that sorts a table into numeric sequence by the data in the PartCode column if that column contains whole numbers that are stored with the varchar data type.

ORDER BY CAST (PartCode AS int)

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

SUBSTRING

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.

You use the UPDATE statement to modify one or more rows in the table named in the ________________ clause.

UPDATE

To code a/an ________________ value as a literal, precede the value with the character N

Unicode

____________________ characters can be used to encode most of the characters in most of the languages of the world.

Unicode

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

You specify the conditions that must be met for a row to be deleted in the which clause?

WHERE

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

a comma

If you omit the WHERE clause from a DELETE statement

all rows in the table will be deleted

The char and nchar data types are used to store fixed-length strings. Which of the statements below is true? a. They are typically used to define columns that have a fixed number of characters b. The char(2) data type will always contain two characters c. all of the above d. Data stored using these data types occupies the same number of bytes regardless of the actual length of the string.

all the above

You can't update

an identity column

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

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

conditional

When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is ________________ a new table.

copied into

To store a date value without storing a time value, you can use the

date 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 data types are used to store whole numbers?

integer

You can use a ________________ in the ________________ clause if you need to specify column values or search conditions other than the one named in the UPDATE clause.

join, FROM

When you code a column list in an INSERT statement, you can omit identify columns, columns that have default values, and columns that allow __________ values.

null

The _____________________ of a decimal value indicates the total number of digits that can be stored in a decimal data type.

precision

In an UPDATE statement, the WHERE clause will

specifiy the condition a row must meet to be updated

To insert several rows selected from another table into a table, you can code an INSERT statement with a/an ______________ in place of the VALUES clause.

subquery

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

Which uses the least amount of storage?

they all use the same amount of storage

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

true

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

varchar(max)