CIS 295 Final exam
To concatenate character strings in a string expression, which operator do you use?
+
An at sign (@) at the beginning of an identifier indicates
. that the identifier is a local variable or parameter
All of the following values can be stored in a column that's defined as decimal(6,2), except
32492.05
A union combines the rows from two or more what?
ALL OF THE ABOVE
A combination of column names and operators that evaluate to a single value is called
AN EXPRESSION
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
Which of the following statements best describes what this INSERT statement does? INSERT INTO InvoiceArchiveSELECT *FROM InvoicesWHERE TermsID = 1;
Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table.
Which functions perform a calculation on the values of a column from selected rows?
Aggregate
Which statement is used to modify the structure of an existing table?
Alter tables
The search condition of a WHERE clause consists of one or more
Boolean expressions
Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function
CAST (InvoiceAmount AS varchar)
One way to examine the system objects that define a database is to use which views?
CATALOG
Which function is typically used to insert control characters into a character string?
CHAR
Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.
COLUMN ALIAS
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.
When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is ________________ a new table
COPIED INTO
___________________ names can be used when you want to assign a temporary name to a table.
CORRELATION
Write an aggregate expression for the number of unique values in the VendorID column
COUNT(DISTINCT VendorID)
TRY...CATCH
CREATE TABLE
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
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause?
DISTINCT
What statement do you use to execute a dynamic SQL statement?
EXEC
Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows?
FETCH
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table
FOREIGN
Correlation names are temporary table names assigned in which clause?
FROM
What statement can you use to divide a script into multiple batches?
GO
Which statement can you use to control the flow of execution based on a true/false condition?
IF...ELSE
Which data types are used to store whole numbers?
INTEGER
If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use what kind of join?
LEFT OUTER
Write an aggregate expression to find the latest date in the InvoiceDate column
MAX(InvoiceDate)
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
The COALESCE function provides one way to substitute constant values for which values?
NULL
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 order of precedence for the logical operators in a WHERE clause is
Not, And, Or
Which clause specifies the number of rows that should be skipped before rows are returned from the result set?
OFFSET
A view is a/an ________________ statement that's stored as an object in the database.
SELECT
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Code a statement that assigns the value "Test" to a scalar variable named @Name that's declared with the varchar data type.
SET @Name = 'Test'
Which function returns the specified number of characters from the string starting at the specified position?
SUBSTRING
If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaludate to? ISNUMERIC(ZipCode) AS Solution
TRUE
Which statement can you use to handle errors caused by one or more SQL statements?
TRY...CATCH
Which of the following is not a good guideline for deciding when to create an index for a column?
The column is frequently updated.
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.
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
If you want to filter the result set that's returned by a SELECT statement, you must include which clause?
WHERE
When you code a DELETE statement for one or more rows, which clause specifies which row or rows are to be deleted?
WHERE
A search condition in the ________________ clause is applied before the rows are grouped while a search condition in the _________________ clause isn't applied until after the grouping
WHERE, HAVING
Each of the following is a benefit provided by using views except for one. Which one?
You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)
a single value
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
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)
a table
System stored procedures
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
If you omit the WHERE clause from a DELETE statement
all rows in the table will be deleted
The first character of an identifier must be
any of the above
SELECT VendorName AS Vendor, InvoiceDate AS DateFROM Vendors AS v JOIN Invoices AS iON v.VendorID = i.VendorID; (Refer to code example 4-1.) The name "v" is known as a?
both correlation name and table alias
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
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
The SELECT statement for a view
can use the ORDER BY clause if it also uses the TOP clause
When you need to code multiple conditions in a join, it's best to
code only join conditions in the ON clause
When you code a table-level check constraint, the constraint can refer to data in more than one
column
When you code an ORDER BY clause, you can specify a
column name, alias, expression, or column number
By default
columns in a view are given the same names as the columns in the base tables
When you subdivide a data element, you can easily rebuild it when necessary by ________________ the individual components.
concatenating
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
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,SUM(InvoiceTotal) AS InvoiceAvgFROM Invoices JOIN VendorsON Invoices.VendorID = Vendors.VendorIDWHERE VendorState < 'e'GROUP BY VendorState, VendorCity, VendorNameHAVING SUM(InvoiceTotal) > 500ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) When this summary query is executed, the result set will contain one summary row for
each vendor with invoice totals over $500
Which identifier can't be used in a SQL statement?
email addresses
The integer and decimal data types are considered ________________ because their precision is exact.
exact numeric data types
You can't update
identity column
When you code a union with the INTERSECT keyword to combine two result sets, the union
includes only rows that occur in both result sets
A correlated subquery is one that
is executed once for each row in the outer query
The CUBE operator is similar to the ROLLUP operator except that
it adds summary rows for every combination of groups
The WITH SCHEMABINDING clause
it both protects a view by binding it to the database structure and prevents underlying base tables from being deleted or modified in any way that affects the view
WITH Top10 AS(SELECT TOP 10 VendorID, AVG(InvoiceTotal) AS AvgInvoiceFROM InvoicesGROUP BY VendorIDORDER BY AvgInvoice DESC)SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoiceFROM Invoices JOIN Top10ON Invoices.VendorID = Top10.VendorIDGROUP BY Invoices.VendorIDORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top10 is used as part of a
join
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 seearch conditions
To normalize a data structure, what do you apply in sequence?
normal forms
SELECT VendorName, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDueFROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorIDWHERE InvoiceTotal - PaymentTotal - CreditTotal >(SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)FROM Invoices)GROUP BY VendorNameORDER BY BalanceDue DESC; (Please refer to code example 6-1.) When this query is executed, the NumberOfInvoices column for each row will show the number
of invoices for each vendor that have a larger balance due than the average balance due for all invoices
Stored procedures execute faster than an equivalent SQL script because stored procedures are what?
precompiled
Data validation is the process of
preventing errors due to invalid data
The WITH ENCRYPTION clause
prevents other users from examining the SELECT statement on which the view is based
The WITH ENCRYPTION clause of the CREATE VIEW statement
prevents users from seeing the code that defines the view
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
An index improves performance when SQL Server ______________________
searches a table
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)
single value
In an UPDATE statement, the WHERE clause will
specifiy the condition a row must meet to be updated
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.
table
When coding a definition for a column that will contain a high percentage of null values, what attribute can you use to optimize the storage?
the SPARSE attribute
The scope of a temporary table is limited to what?
the database session in which it's defined
When you identify the data elements in a new database, you typically subdivide data elements into
the smallest practical components
The scope of a derived table is limited to what?
the statement in which it's defined
WITH Top10 AS(SELECT TOP 10 VendorID, AVG(InvoiceTotal) AS AvgInvoiceFROM InvoicesGROUP BY VendorIDORDER BY AvgInvoice DESC)SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoiceFROM Invoices JOIN Top10ON Invoices.VendorID = Top10.VendorIDGROUP BY Invoices.VendorIDORDER 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?
unknown
The statement CREATE VIEW Example4ASSELECT *FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
will fail because the SELECT statement returns two columns named VendorID
The statement CREATE VIEW Example2ASSELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate) FROM Invoices;
will fail because the second column isn't named
