CSCI B340 Final Exam

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? CASEWHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2)WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2)ELSE 0 END AS Solution

225.00

What would 7,800,000,000 be using scientific notation?

7.8 X 109 or 7.8E+9

The first character of a regular identifier can be

any of the above

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

column

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

conditional

Which identifier can't be used in a SQL statement?

email addresses

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

By default, what kind of index does the CREATE INDEX statement create?

non clustered

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

precision

You can set a collation for all but one of the following. Which one?

table

An at sign (@) at the beginning of an identifier indicates

that the identifier is a local variable or parameter

When you define a foreign key constraint, you can specify all but one of the following. Which one is it?

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 string uses the least amount of storage when stored in a column of type varchar(20)?

'ex'

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

19

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

32492.05

If ExpirationDate contains a value that's equivalent to June 2, 2020 and the GetDate function returns a value that's equivalent to July 17, 2020, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution

45

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

6

Which of the following casts a decimal field named InvoiceAmount to a varchar datatype?

CAST (InvoiceAmount AS varchar)

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. Which function would you use to locate the characters that separate the components?

CHARINDEX

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

CHARINDEX

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

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

Each of the following column attributes is a column constraint, except

DEFAULT

Given that the varchar and nvarchar data types are used 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 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, which functions would you to extract the individual components?

LEFT, RIGHT, SUBSTRING, and LEN

Which function returns the string with any leading spaces removed?

LTRIM

Which of the following are common encodings used by SQL Server?

Latin1

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

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)

Which column or columns in each table should be defined as the primary key?

Orders: OrderID OrderLineItems: OrderID and OrderSequenceProducts: ProductID

When you create a script for creating a database, which keyword do you use to signal the end of a batch and cause all the statements in the batch to be executed?

GO

A collation can affect all of the following except one. Which one?

How character data is concatenated

Which column or columns in each table are foreign keys?

Orders: none OrderLineItems: OrderID and ProductIDProducts: none

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

PATINDEX

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

PERCENT_RANK

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

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

SUBSTRING

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

The column is frequently updated.

Which of the following is true about ASCII characters?

The number of bytes depends on the encoding

Which of the following is true about supplementary characters?

They always require four 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

The UTF-8 encoding can be used with

a and b only

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

a log file

To enforce referential integrity, the database can

a or b

What do you use to uniquely identify each row in a table?

a primary key

What can you use to generate a series of integer values that can be used by more than one table?

a sequence

To be in the first normal form, each cell in a table must contain

a single, scalar value

Given that the char and nchar data types are used to store fixed-length strings, which of the statements below is true?

all of the above

To be in the third normal form,

all of the above

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

all of the above

Which of the following statements about the SPARSE attribute is true?

all of the above

You use data definition language (DDL) to create, modify, and delete the ________________ of a database.

all of the above

A table can contain information about two or more entities in

an unnormalized data structure

What kind of constraint enforces referential integrity between tables?

both a and b

Which of the following are common character sets used by SQL Server?

both a and c

A regular identifier

can contain a number sign (#)

What kind of constraint limits the values that can be stored in a column?

check constraint

After you identify and subdivide all of the data elements for a database, you should group them by the entities with which they're associated. The entities will become the tables of the database, and the elements will be come the

columns

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

concatenating

When you use the CREATE TABLE statement to create a table, you can also define the attributes and ______________ for the columns.

constraints

The CREATE TABLE statement

creates a new table in the current database

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

In SQL Server, what do you typically use to enforce referential integrity?

declarative referential integrity

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

What is a type of nonclustered index that includes a WHERE clause?

filtered index

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

Which data types are used to store whole numbers?

integer

If two tables have a many-to-many relationship, you need to define a _____________ table that relates their records.

linking

To normalize a data structure, what do you apply in sequence?

normal forms

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

The most common type of relationship between two tables is called what?

one-to-many relationship

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

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

To maintain ________________, if you delete a row in a primary key table, you must also delete any related rows in foreign key tables.

referential integrity

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

scientific notation

An index improves performance when SQL Server

searches a table

The real data type can be used to store a _________________, which provides for numbers with up to 7 significant digits.

single-precision number

SQL Server supports ________________ of the ANSI-standard data types

some, but not all

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

table

The UTF-16 encoding can be used with

the nchar data type

When you identify the data elements in a new database, you typically subdivide data elements 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

Typically, what form do most database designers consider a database structure to be normalized?

third

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)

When you code a script for creating a database,

you need to create the referred to tables before you create the tables that refer to them

Which statement is used to modify the structure of an existing table?

ALTER TABLE