Assessment section 2

If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed?

19

Which function would you use to locate the index of the first character of the first occurrence of a pattern within another string?

PATINDEX

Which statement about the ROUND function is false?

You can code a -1 for the function to always round down

A SQL Server database consists of two files: a database file and

a log file

The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and

a log file

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

adds all rows in the Invoices table that have 1 in TermsID column to the InvoiceArchive table

If you omit the WHERE clause from a DELETE statement

all rows in the table will be deleted

You can't update

an identity column

A table can contain info about two or more entities in

an unnormalized data structure

If you have the files for an existing SQL Server database, the easiest way to create the database is to _____ those files to the database server.

attach

If you have the files for an existing SQL Server database, you can create the database by _____ those files to the database server.

attaching

Regarding the char and nchar data types, which of the statements below is false?

char and nchar can automatically resize themselves to accommodate larger strings

If you assign a value with one data type to a column with another data type, SQL Server converts the value to the data type of the column using what?

implicit conversion

When you use an UPDATE statement, you can code a subquery anywhere except

in the ORDER BY clause to sort by a subset of the columns

Which data type is only used to store whole numbers?

int

When you identify a column as the primary key, the column

is forced to contain a unique value for each row

In an UPDATE statement, you can use a ______ in the FROM clause if you need to specify column values or search conditions that depend on data from a table other than the one named in the UPDATE clause.

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 _______ value when you use the ROLLUP or CUBE operator.

null

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

null

What is the most common type of relationship between two tables?

one-to-many

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

precision

What do you typically use to relate two tables that have a one-to-one relationship?

primary keys

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This..

reduces redundancy and makes maintenance easier

When you use Management Studio to create a foreign key constraint, you specify the relationship between two tables as well as any rules for enforcing what?

referential integrity

To express the value of a floating-point number, you can use

scientific notation

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

Check constraints you create using DDL can be defined at the...

table & column level

Which of the following is not a good guideline for deciding when to create an index for a column?

the column is frequently updated

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 Invoices (VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, Credit Total, TermsID, InvoiceDate, InvoiceDueDate) VALUES (97, '456789', 8344.50, 0, 0, 1, '2023-06-01');

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

When you identify the data elements in a new database, you typically subdivide them into..

the smallest practical components

Which string uses the least amount of storage when stored in a column of type nchar(20)?

they all use the same amount of storage

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 you use Management Studio to create a database. MS generates and runs the ______ statements that are necessary to create the database.

DDL

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

Which of the following statements deletes every row in the Vendors table?

DELETE Vendors;

You can't use DDL to create, modify, and delete the ____ of a database.

Data

Which of the following does not violate the referential integrity of a database?

Deleting a row in a foreign key table without deleting the related row in the related primary key table

Which of the following is not a requirement for a third normal form?

Each non-primary key column must be a foreign key

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

true

When you use Management Studio to create a check constraint, you can specify whether you want the constraint enforced for insert or _____ operations.

update

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

varchar(max)

When you create a script, which keyword do you use to signal the end of a batch and cause them to be executed?

GO

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

Which function returns the string with any leading spaces removed?

LTRIM

If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, which is the default setting?

NULL

When you define a foreign key constraint, what can you specify?

No action, Deletion, and Updating

Which ORDER BY clause 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)

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

32482.05 (more than 6 characters)

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

AND

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

CHAR

Which function would you use to concatenate two or more values into a single string, separated by a delimiter?

CONCAT_WS

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 both the CONVERT and TRY_CONVERT functions?

CONVERT returns an error if the expression can't be converted, but TRY_CONVERT returns a NULL value.

Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily _____ it.

Restore

What attribute can you use to optimize storage when coding the definition for a column that will contain a high percentage of null values?

SPARSE

When you set the compatibility level of SQL Server 2019, you make sure it is compatible with a specific version of

SQL server

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

SUBSTRING

Whenever you use Management Studio to create, alter, or delete database objects, you can save the ____ that it used for doing that.

Script

You can set a collation for what?

Server, Database, Column

To model a database on a real-world system, you typically represent each real-world entity as a

Table

When you define a foreign key constraint, you can specify all but which of the following?

That the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table

Which of the following is true about ASCII characters?

The number of bytes required for storage depends on the encoding

Which of the following is true about Unicode's supplementary characters?

They always require 4 bytes of storage

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

Unicode

____ characters can be used with the default collation to encode most of the characters in most of the world's writing systems.

Unicode

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 datetime2 data type and which might include time values?

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

In a DELETE statement, which clause do you use to specify the conditions that must be met for a row to be deleted?

WHERE

When you code a table-level check constraint, the constraint can refer to data in more than one

column

After you identify and subdivide all of the data elements for a database, the entities become the tables of the database, and the elements become the

columns

When you subdivide a data element, you can easily rebuild it when necessary by _____ the individual components.

concatenating

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

copied into

Regarding the varchar and nvarchar data types, 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 data type can you use to store a date value without storing a time value?

date

Which data type can you use to store a date that includes a time zone offset?

datetimeoffset

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

Which string uses the least amount of storage when stored in a column of type varchar(20)?

ex

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

exact numeric data types

Given an action which would violate referential integrity, which of the following would not enforce ref int?

execute the requested action and mark any orphans in related tables

What is the type of non-clustered index that includes a WHERE clause?

filtered index

Which of the following is not a reason to write you own SQL statements?

hand-written runs faster than machine-generated