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
