Db Final

Front 1

expressions coded in the HAVING clause:

Back 1

can use either aggregate search conditions or non-aggregate search conditions

Front 2

Assuming that all the table and column names are spelled correctly, what's wrong with the INSERT statement that follows?

INSERT INTO InvoiceCopy

(VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal,

TermsID, InvoiceDate, InvoiceDueDate)

VALUES

(97, '456789', 8344.50, 0, 0, 1, '2006-08-01')

Back 2

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

Front 3

In order to insert several rows into a table with an INSERT statement, you code a subquery in place of the VALUES clause.

Back 3

True

Front 4

If introduced as follows, the subquery can return which of the values listed below?

WHERE 2 < (subquery)

Back 4

a single value

Front 5

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?

Back 5

InvoiceTotal IN (0,1000)

Front 6

If you define a column as an identity column,

Back 6

a number is generated for that column whenever a row is added to the table

Front 7

By default, all duplicate values are included in the aggregate calculation, unless you specify the DISTINCT keyword

Back 7

True

Front 8

Which of the statements below best describes the result set returned by this SELECT statement?

SELECT VendorID,

SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2

FROM Invoices

WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0

GROUP BY VendorID

Back 8

The total unpaid balance due for each VendorID

Front 9

Which of the following recommendations won’t improve the readability of your SQL statements?

Back 9

Use comments to describe what each statement does.

Front 10

A correlated subquery is one that

Back 10

is executed once for each row in the outer query

Front 11

Code example 6-1

SELECT VendorName, COUNT(*) AS NumberOfInvoices,
MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal - PaymentTotal - CreditTotal >
(SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)
FROM Invoices)
GROUP BY VendorName
ORDER 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

Back 11

of invoices for each vendor that have a larger balance due than the average balance due for all invoices

Front 12

If introduced as follows, the subquery can return which of the values listed below?
SELECT (subquery)

Back 12

a single value

Front 13

A null value is:

Back 13

A value in a column that is unknown

Front 14

The six clauses of the SELECT statement must be coded in the correct order or it causes a syntax error.

Back 14

True

Front 15

Code example 5-1

SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceAvg
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE VendorState < 'e'
GROUP BY VendorState, VendorCity, VendorName
HAVING SUM(InvoiceTotal) > 500
ORDER BY VendorState, VendorCity, VendorName;

The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it

Back 15

All of the above.

Front 16

Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?

Back 16

InvoiceTotal - CreditTotal - PaymentTotal / 10

Front 17

If you wanted to retrieve information from a column or row from a database table you would use the _____ statement.

Back 17

SELECT

Front 18

Referring to the following code, when this query is executed, the result table will contain one row for

WITH Top10 AS

(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice

FROM Invoices

GROUP BY VendorID

ORDER BY AvgInvoice DESC)

SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice

FROM Invoices JOIN Top10

ON Invoices.VendorID = Top10.VendorID

GROUP BY Invoices.VendorID

ORDER BY LargestInvoice DESC

Back 18

each vendor in the Top10 table

Front 19

Which of the following types of statements isn’t an action query?

Back 19

Select

Front 20

In a cross join, all of the rows in the first table are joined with all of the

Back 20

rows in the second table

Front 21

Which column or columns in each table are foreign keys?

Back 21

Orders: None; OrderLineItems: OrderID and ProductID; Products: None

Front 22

Which of the following statements best describes what this INSERT statement does?

INSERT INTO InvoiceArchive

SELECT *

FROM Invoices

WHERE TermsID = 1

Back 22

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

Front 23

You do not have to apply the normal forms in sequence to normalize a data structure

Back 23

False

Front 24

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

Back 24

True

Front 25

The date/time data types can store

Back 25

all of the above

Front 26

The following code will cast a decimal field named InvoiceAmount to a varchar datatype using the CAST function:

CAST(InvoiceAmount AS varchar)

Back 26

True

Front 27

Which uses the least amount of storage?

Back 27

they all use the same amount of storage

Front 28

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

Back 28

The column is frequently updated

Front 29

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

Back 29

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.

Front 30

If RegistrationDate contains a value that’s equivalent to August 10, 2012, what will the Solution column contain when this code is executed?
DATEPART(month, RegistrationDate)

Back 30

8

Front 31

In order to insert several rows into a table with an INSERT statement, you code a subquery in place of the VALUES clause.

Back 31

True

Front 32

When you code an UPDATE statement for one or more rows the ______ clause specifies which row or rows are to be updated.

Back 32

WHERE

Front 33

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

Back 33

32492.05

Front 34

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

Back 34

reduces redundancy and makes maintenance easier

Front 35

Using Figure 3 from the Figures Document:
Which column or columns in each table should be defined as the primary key?

Back 35

Orders: OrderID; OrderLineItems: OrderID and OrderSequence; Products: ProductID

Front 36

Referential integrity is the idea that if you delete a row in a primary key table, you must also delete any related rows in foreign key tables.

Back 36

True

Front 37

The COALESCE function provides one way to substitute constant values for null values.

Back 37

True

Front 38

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

Back 38

45

Front 39

When you use the CREATE DATABASE statement to create a table you do not have to define the attributes and constraints for the columns of the table.

Back 39

False

Front 40

When you code an UPDATE statement for one or more rows the _______ clause specifies the new data for the specified columns

Back 40

SET

Front 41

Which uses the least amount of storage? (varchar)

Back 41

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

Front 42

Which uses the least amount of storage? (nchar)

Back 42

they all use the same amount of storage

Front 43

Foreach type of action query, a table can have

Back 43

multipleAFTER triggers and one INSTEAD OF trigger

Front 44

Thescope of a derived table is limited to

Back 44

thestatement in which it’s defined

Front 45

Usingthe following code: If the current date is 08/04/06, the earliest invoice duedate for invoices with unpaid balances is 06/09/06, and the latest invoice duedate for invoices with unpaid balances is 07/20/06, what will be printed bythis script?

USE AP

DECLARE @Date1 smalldatetime

DECLARE @Date2 smalldatetime

SELECT @Date1 = MIN(InvoiceDueDate), @Date2 =MAX(InvoiceDueDate)

FROM Invoices

WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0

IF @Date1 < GETDATE()

IF @Date2 < GETDATE()

BEGIN

PRINT 'Earliest past due date: ' + CONVERT(varchar,@Date1, 1)

PRINT 'Latest past due date: ' + CONVERT(varchar,@Date2, 1)

END

ELSE

PRINT 'Earliest past due date: ' + CONVERT(varchar,@Date1, 1)

ELSE

PRINT 'No invoices past due'

Back 45

Earliestpast due date: 06/09/06
Latest past due date: 07/20/06

Front 46

Datavalidation is the process of

Back 46

preventingerrors due to invalid data

Front 47

To return a message to a client, you use the SCALAR statement

Back 47

false

Front 48

The WITH ENCRYPTION clause of the CREATE VIEW statement

Back 48

prevents users from seeing the code that defines the view

Front 49

Data validation is the process of

Back 49

preventing errors due to invalid data

Front 50

You can invoke a table-valued user-defined function

Back 50

anywhere you'd refer to a table or a view

Front 51

Each of the following statements about triggers is true except for one. Which one is it?

Back 51

A trigger can have more than one batch.

Front 52

Given the following statements that declare a local variable and sets its value, which of the following will cause an error?

DECLARE @Example1 varchar(128)

SET @Example1 = 'Invoices'

Back 52

SELECT * FROM @Example1

Front 53

Parameters for stored procedures can be optional without a default value.

Back 53

False

Front 54

Which of the following statements can be coded in a batch with other statements?

Back 54

CREATE TABLE

Front 55

Code example 14-1
USE AP;
DECLARE @Date1 smalldatetime;
DECLARE @Date2 smalldatetime;
SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate)
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
IF @Date1 < GETDATE()
IF @Date2 < GETDATE()
BEGIN
PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1);
PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1);
END;
ELSE
PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1);
ELSE
PRINT 'No invoices past due';

(Refer to code example 14-1.) If the current date is 03/15/12, the earliest invoice due date for invoices with unpaid balances is 02/09/12, and the latest invoice due date for invoices with unpaid balances is 03/20/12, what will be printed by this script?

Back 55

Earliest past due date: 02/09/12

Front 56

When passing a list of parameters to a stored procedure by name, you can omit optional parameters by

Back 56

omitting the parameter name and value from the list

Front 57

Using the following code: What is the maximum value of the @Total variable?

USE AP

SELECT * INTO #InvoiceCopy FROM Invoices

DECLARE @InvoiceID int, @InvoiceTotal money

DECLARE @Total money

SET @Total = 0

WHILE @Total + (SELECT TOP 1 InvoiceTotal

FROM #InvoiceCopy

ORDER BY InvoiceTotal DESC) <= 200000

BEGIN

SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal

FROM #InvoiceCopy

ORDER BY InvoiceTotal DESC

IF @InvoiceTotal < 1000

BREAK

ELSE

BEGIN

SET @Total = @Total + @InvoiceTotal

DELETE FROM #InvoiceCopy

WHERE InvoiceID = @InvoiceID

END

END

PRINT 'Total: $' + CONVERT(varchar, @Total, 1)

Back 57

200,000.00

Front 58

You typically use the return value of a stored procedure to

Back 58

indicate to the calling program whether the stored procedure completed successfully

Front 59

You can use the WHERE CURRENT OF clause to

Back 59

update or delete rows through a cursor

Front 60

To manually raise an error within a stored procedure you use the RAISERROR statement.

Back 60

True

Front 61

Using the following code: What can cause the WHILE loop in this script to end other than the expression on the statement becoming true?

USE AP

SELECT * INTO #InvoiceCopy FROM Invoices

DECLARE @InvoiceID int, @InvoiceTotal money

DECLARE @Total money

SET @Total = 0

WHILE @Total + (SELECT TOP 1 InvoiceTotal

FROM #InvoiceCopy

ORDER BY InvoiceTotal DESC) <= 200000

BEGIN

SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal

FROM #InvoiceCopy

ORDER BY InvoiceTotal DESC

IF @InvoiceTotal < 1000

BREAK

ELSE

BEGIN

SET @Total = @Total + @InvoiceTotal

DELETE FROM #InvoiceCopy

WHERE InvoiceID = @InvoiceID

END

END

PRINT 'Total: $' + CONVERT(varchar, @Total, 1)

Back 61

When the value of the @InvoiceTotal variable becomes less than 1000

Front 62

System stored procedures

Back 62

perform standard tasks on the current database

are stored in the Master database

and can change with each version of SQL Server

Front 63

How would you code the INSTEAD OF clause for a trigger that’s fired whenever a view is deleted?

Back 63

INSTEAD OF DROP_VIEW

Front 64

A user-defined function

Back 64

can return a single scalar value or a single table value

Front 65

Which of the following statements can be coded in a batch with other statements?

Back 65

CREATE TABLE

Front 66

Parameters for stored procedures and functions can be of any valid SQL Server data type except:

Back 66

table

Front 67

Code example 14-2
USE AP;

SELECT * INTO #InvoiceCopy FROM Invoices;

DECLARE @InvoiceID int, @InvoiceTotal money;
DECLARE @Total money;
SET @Total = 0;

WHILE @Total + (SELECT TOP 1 InvoiceTotal
FROM #InvoiceCopy
ORDER BY InvoiceTotal DESC) <= 200000
BEGIN
SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal
FROM #InvoiceCopy
ORDER BY InvoiceTotal DESC;
IF @InvoiceTotal < 1000
BREAK;
ELSE
BEGIN
SET @Total = @Total + @InvoiceTotal;
DELETE FROM #InvoiceCopy
WHERE InvoiceID = @InvoiceID;
END;
END;

PRINT 'Total: $' + CONVERT(varchar, @Total, 1);

(Refer to code example 14-2.) When does the expression on the WHILE statement in this script cause the loop to end?

Back 67

When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000

Front 68

How would you code the AFTER clause for a trigger that's fired when a view is deleted?

Back 68

AFTER OF DROP_VIEW

Front 69

How would you code the ON clause for a trigger that's fired after a table is deleted from the current database (assume that database_name is the name of the current database)?

Back 69

ON DATABASE

Front 70

When you code a SELECT statement, you must code the four main clauses in the following order

Back 70

SELECT, FROM, WHERE, ORDER BY

Front 71

If written as follows, the subquery can return which of the values listed below?

WHERE InvoiceTotal > ALL (Subquery)

Back 71

a column of one or more rows

Front 72

Referring to the following code, when this query is executed, each row in the result table will show

WITH Top10 AS

(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice

FROM Invoices

GROUP BY VendorID

ORDER BY AvgInvoice DESC)

SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice

FROM Invoices JOIN Top10

ON Invoices.VendorID = Top10.VendorID

GROUP BY Invoices.VendorID

ORDER BY LargestInvoice DESC

Back 72

the largest invoice amount related to that row

Front 73

To create a new table by using a SELECT statement you code the _____ clause.

Back 73

INTO

Front 74

If introduced as follows, the subquery can return which of the values listed below?
WHERE (subquery)

Back 74

a subquery can’t be introduced in this way

Front 75

To sort the records that are retrieved by a SELECT statement in descending sequence by InvoiceTotal you code _________ after ORDER BY InvoiceTotal

Back 75

DESC

Front 76

When you code a union with the INTERSECT keyword to combine two result sets, the union

Back 76

includes only rows that occur in both result sets

Front 77

True or False: the following code example shows the use of a correlated table expression

WITH Top10 AS

(SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice

FROM Invoices

GROUP BY VendorID

ORDER BY AvgInvoice DESC)

SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice

FROM Invoices JOIN Top10

ON Invoices.VendorID = Top10.VendorID

GROUP BY Invoices.VendorID

ORDER BY LargestInvoice DESC

Back 77

True

Front 78

Using the following code example:

SELECT VendorName AS Vendor, InvoiceDate AS Date
FROM Vendors AS V JOIN Invoices AS I
ON V.VendorID = I.VendorID

This type of join is called a/an _________ join.

Back 78

INNER

Front 79

You can use the OVER clause with an aggregate function to

Back 79

include the rows used to calculate the summary in the result set

Front 80

If you define a column as an identity column,

Back 80

a number is generated for that column whenever a row is added to the table

Front 81

Code example 4-2

SELECT VendorName, InvoiceNumber
FROM Invoices LEFT JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID;

(Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal

Back 81

none of the above

Front 82

Which of the following functions do not ignore null values?

Back 82

COUNT()

Front 83

Code example 6-1

SELECT VendorName, COUNT(*) AS NumberOfInvoices,
MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal - PaymentTotal - CreditTotal >
(SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)
FROM Invoices)
GROUP BY VendorName
ORDER BY BalanceDue DESC;

(Please refer to code example 6-1.) When this query is executed, the result set will contain

Back 83

one row for each vendor that shows the largest balance due for any of the vendor’s invoices, but only if that balance due is larger than the average balance due for all invoices

Front 84

The data type that is assigned to a column defines the type of information that can be stored there.

Back 84

True

Front 85

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax

Back 85

can be used for more than two tables

Front 86

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?

Back 86

TOP 10 InvoiceNumber

Front 87

When you code a column list into the INTO clause of an INSERT statement you can't include an identity column.

Back 87

True

Front 88

The order of precedence for the logical operators in a WHERE clause is

Back 88

Not, And, Or

Front 89

Code example 4-2

SELECT VendorName, InvoiceNumber
FROM Invoices LEFT JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID;

(Refer to code example 4-2.) The total number of rows returned by this query must equal

Back 89

the number of rows in the Invoices table

Front 90

You don't ever need to code a right outer join because:

Back 90

right outer joins can be converted to left outer joins

Front 91

The most common type of JOIN is:

Back 91

inner join

Front 92

The six clauses of the SELECT statement must be coded in the following order:

Back 92

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Front 93

To test whether one or more rows are returned by a subquery you can use the HAVING operator.

Back 93

False

Front 94

Although this query runs as coded, it contains this logical error:

SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,

SUM(InvoiceTotal) AS InvoiceAvg

FROM Invoices JOIN Vendors

ON Invoices.VendorID = Vendors.VendorID

WHERE VendorState < 'e'

GROUP BY VendorState, VendorCity, VendorName

HAVING SUM(InvoiceTotal) > 500

ORDER BY VendorState, VendorCity, VendorName

Back 94

The column name in the fifth column in the result set doesn't match the data

Front 95

If you define a column as an identity column,

Back 95

a number is generated for that column whenever a row is added to the table

Front 96

Which of the statements below best describes the result set returned by this SELECT statement?
SELECT VendorID,
SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
GROUP BY VendorID;

Back 96

The total unpaid balance due for each VendorID

Front 97

Using the following code example:

SELECT VendorName AS Vendor, InvoiceDate AS Date
FROM Vendors AS V JOIN Invoices AS I
ON V.VendorID = I.VendorID

The column name for the second column in the result set will be:

Back 97

Date

Front 98

To concatenate character strings, you use the __________ operator in a string expression.

Back 98

+

Front 99

The statements that work with the objects in the database is called:

Back 99

Data Definition Language

Front 100

If you want to filter the result set that's returned by a SELECT statement, you must include a/an ____________ clause.

Back 100

WHERE

Front 101

A full outer join includes rows that satisfy the join condition, plus

Back 101

rows in both tables that don't satisfy the join condition

Front 102

Code example 5-1

SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceAvg
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE VendorState < 'e'
GROUP BY VendorState, VendorCity, VendorName
HAVING SUM(InvoiceTotal) > 500
ORDER 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

Back 102

each vendor with invoice totals over $500

Front 103

If you wanted to retrieve information from a column or row from a database table you would use the _____ statement.

Back 103

SELECT

Front 104

The key in a table that shares a relationship with a primary key in another table

Back 104

A foreign key is

Front 105

Using the following code example:

SELECT VendorName AS Vendor, InvoiceDate AS Date
FROM Vendors AS V JOIN Invoices AS I
ON V.VendorID = I.VendorID

This type of join is called a/an _________ join

Back 105

INNER

Front 106

If introduced as follows, the subquery can return which of the values listed below?
WHERE VendorID NOT IN (subquery)

Back 106

a column of one or more rows

Front 107

If written as follows, the subquery can return which of the values listed below?

SELECT (subquery)

Back 107

a single value

Front 108

The interface between an application program and the DBMS is usually provided by the

Back 108

data access API

Front 109

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?

Back 109

InvoiceTotal IN (0,1000)

Front 110

Which ORDER BY clause will cause 10 rows to be retrieved from the result set, starting with the 20th row?

Back 110

ORDER BY InvoiceTotal DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS

Front 111

In a join, column names need to be qualified only

Back 111

when the same names are used in both tables

Front 112

Which of these is an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values.

Back 112

AVG(InvoiceTotal)

Front 113

Using the following code example:

SELECT VendorName AS Vendor, InvoiceDate AS Date
FROM Vendors AS V JOIN Invoices AS I
ON V.VendorID = I.VendorID

This join is code using which syntax?

Back 113

explicit SQL-92

Front 114

If you define a column with a default value, that value is used whenever a row

Back 114

that doesn’t include a value for that column is added to the table

Front 115

Referring to the following code, when this query is executed, the result set will contain

SELECT VendorName, COUNT(*) AS NumberOfInvoices,

MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue

FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

WHERE InvoiceTotal - PaymentTotal - CreditTotal >

(SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)

FROM Invoices)

GROUP BY VendorName

ORDER BY BalanceDue DESC

Back 115

one row for each vendor that shows the largest balance due for any vendor's invoices, but only if that balance due is larger than the average balance due for all invoices.

Front 116

Using the following code example:

SELECT VendorName, InvoiceNumber
FROM Invoices LEFT JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID

If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal:

Back 116

None of the above

Front 117

When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?

Back 117

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

Front 118

The processing that’s done by the DBMS is typically referred to as

Back 118

back-end processing

Front 119

When you need to code multiple conditions in a join, its best to

Back 119

code only join conditions in the ON clause

Front 120

Code example 4-2

SELECT VendorName, InvoiceNumber
FROM Invoices LEFT JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID;

(Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal

Back 120

none of the above

Front 121

Code example 5-1

SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty,
SUM(InvoiceTotal) AS InvoiceAvg
FROM Invoices JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE VendorState < 'e'
GROUP BY VendorState, VendorCity, VendorName
HAVING SUM(InvoiceTotal) > 500
ORDER BY VendorState, VendorCity, VendorName;

The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it

Back 121

All of the above.

Front 122

Which of the following is not a valid column alias name

Back 122

Total Sales

Front 123

The ______ clause of the SELECT statement names the table that contains the data to be retrieved.

Back 123

FROM

Front 124

When you code a union that combines two result sets, which of the following is not true?

Back 124

The result sets must be derived from different tables.

Front 125

A hierarchical database provides only for parent/child relationships

Back 125

True

Front 126

If you assign a correlation name to one table in a join,

Back 126

you have to use the name for the table

Front 127

Which of the statements below best describes the result set returned by this SELECT statement?
SELECT VendorState, COUNT(*) AS Column2
FROM Vendors
GROUP BY VendorState
HAVING COUNT(*) > 1;

Back 127

The number of vendors in each state having more than one vendor

Front 128

A join statement retrieves data from two or more tables into a single result set.

Back 128

True

Front 129

The ______ is the computer that stores the file and databases of the system.

Back 129

Server

Front 130

Which of the following statements best describes what this DELETE statement does?
DELETE Vendors

Back 130

Deletes every row in the Vendors table

Front 131

Which of the following types of SQL statements isn’t a DML statement?

Back 131

CreateTable

Front 132

Expressions coded in the WHERE clause

Back 132

can use non-aggregate search conditions but can’t use aggregate search conditions

Front 133

When you code an ORDER BY clause, you can specify a:

Back 133

column name, alias, expression or column number

Front 134

Assuming that all of the table and column names are spelled correctly, what’s wrong with the INSERT statement that follows?
INSERT INTO InvoiceCopy
(VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal,
TermsID, InvoiceDate, InvoiceDueDate)
VALUES
(97, '456789', 8344.50, 0, 0, 1, '2006-08-01');

Back 134

The number of items in the column list doesn’t match the number in the VALUES list.

Front 135

ANSI stands for:

Back 135

American National Standards Institute

Front 136

If introduced as follows, the subquery can return which of the values listed below?

FROM (subquery)

Back 136

a table

Front 137

A subquery can only be coded from a WHERE, FROM or SELECT clause

Back 137

False

Front 138

A ______ consists of a SELECT statement that is stored with the database and can be managed independently of the applications that use them.

Back 138

View

Front 139

The result of a SELECT statement is called:

Back 139

result set

Front 140

A subquery is a SELECT statement that is coded within another SQL statement

Back 140

True

Front 141

A statement that would create a new database would look something like:

Back 141

CREATE DATABASE PH_TEST;

Front 142

Tables in relational database can be related to other tables by which relationship?

Back 142

All of the above

Front 143

To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like

Back 143

commands, connections, and data readers

Front 144

Which of the following statements is true about the CONVERT and TRY_CONVERT functions?

Back 144

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

Front 145

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

Back 145

True

Front 146

To modify the structure of an existing table, you use the ______ statement.

Back 146

ALTER TABLE

Front 147

Using Figure 1 from the Figure Document:
Which of the diagrams best represents the relationship between a table of customers and a table of orders placed by customers?

Back 147

B

Front 148

To be in the third normal form,

Back 148

all of the above

Front 149

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

Back 149

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

Front 150

The difference between the CAST function and the CONVERT function is that the _______ function accepts an optional style that lets you specify a format for the result.

Back 150

CONVERT

Front 151

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

Back 151

GO

Front 152

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

Back 152

True

Front 153

Which of the following diagrams best represents the relationship between a table of students and a table of classes for which the student is registered?

Back 153

Front 154

A constraint that limits the values that can be stored in a column is called a/an _____ constraint.

Back 154

check

Front 155

Column lists of an INSERT statement cannot omit default values or columns that allow nulls.

Back 155

False

Front 156

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

Back 156

DEFAULT

Front 157

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

Back 157

225.00

Front 158

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

Back 158

ewilliam

Front 159

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

Back 159

True

Front 160

Which of the diagrams best represents the relationship between a table of students and a table of classes for which the student is registered?

Back 160

C

Front 161

The most common type of relationship between two tables is a many-to-many

Back 161

False

Front 162

Typically, most database designers consider a database structure normalized if it's in the _______ normal form.

Back 162

Third

Front 163

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?

Back 163

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

Front 164

Unless you specify otherwise, the CREATE INDEX statement creates a clustered index for the specified column or columns.

Back 164

False

Front 165

A constraint that limits the values that can be stored in a column is called a reference constraint

Back 165

False

Front 166

When you create a script for creating a database,

Back 166

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

Front 167

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

Back 167

a single, scalar value

Front 168

Which of the following statements is true?

Back 168

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

Front 169

You use DDL to create, modify and delete the objects of a database

Back 169

True

Front 170

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

Back 170

the smallest practical components

Front 171

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

Back 171

date data type

Front 172

A constraint that enforces referential integrity between tables is called a reference constraint

Back 172

True

Front 173

The CREATE TABLE statement

Back 173

creates a new table in the current database

Front 174

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

Back 174

NULL

Front 175

If ZipCode is a varchar column that contains the value 93702, what will the Solution column contain when this code is executed?
ISNUMERIC(ZipCode)

Back 175

1

Front 176

To enforce referential integrity, the database can:

Back 176

A or B

Front 177

If CustomerAddress contains “ 178 E Center Street ”, what will the Solution column contain when this code is executed?
LEN(LTRIM(RTRIM(CustomerAddress)))

Back 177

19

Front 178

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

Back 178

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

Front 179

Which data type is used to store whole numbers?

Back 179

integer

Front 180

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:

Back 180

reduces redundancy and makes maintenance easier

Front 181

An index is designed to improve performance when SQL Server does a search or a join based on a specific value in the indexed column

Back 181

True

Front 182

The WITH SCHEMABINDING clause of the CREATE VIEW statement

Back 182

all of the above

Front 183

A series of SQL statements you can store in a file is called a script

Back 183

True

Front 184

The statement
CREATE VIEW Example4
AS
SELECT *
FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

Back 184

will fail because the SELECT statement returns two columns named VendorID

Front 185

The scope of a derived table is limited to:

Back 185

the statement in which it's defined

Front 186

If you want to prevent users from examining the SQL code that defines a procedure, function or trigger, you code the CREATE statement with the HIDE option.

Back 186

False

Front 187

To repeatedly execute a statement or set of statements, you code a WHILE statement.

Back 187

True

Front 188

The scope of a temporary table is limited to:

Back 188

the database session in which it's defined

Front 189

The scope of a local variable is limited to:

Back 189

the batch in which it's defined

Front 190

If you delete a stored procedure, function, or trigger and then create it again

Back 190

you delete the security permissions assigned to the object

Front 191

Stored procedures execute faster than an equivalent SQL script because stored procedures are precompiled.

Back 191

True

Front 192

If the current date is 04/04/12, the earliest invoice due date for invoices with unpaid balances is 02/09/12, and the latest invoice due date for invoices with unpaid balances is 03/20/12, what will be printed by this script?

Back 192

Earliest past due date: 02/09/12
Latest past due date: 03/20/12

Front 193

Using the following code: If the current date is 08/04/06, the earliest invoice due date for invoices with unpaid balances is 06/09/06, and the latest invoice due date for invoices with unpaid balances is 07/20/06, what will be printed by this script?

USE AP

DECLARE @Date1 smalldatetime

DECLARE @Date2 smalldatetime

SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate)

FROM Invoices

WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0

IF @Date1 < GETDATE()

IF @Date2 < GETDATE()

BEGIN

PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1)

PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1)

END

ELSE

PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1)

ELSE

PRINT 'No invoices past due'

Back 193

Earliest past due date: 06/09/06
Latest past due date: 07/20/06

Front 194

The statement
CREATE VIEW Example2
AS
SELECT InvoiceNumber,
DATEDIFF(day,InvoiceDate,InvoiceDueDate)
FROM Invoices;

Back 194

will fail because the second column isn’t named

Front 195

When you store a BLOB in a database column with FILESTREAM storage, which of the following statements is not true?

Back 195

The BLOB must be smaller than 2GB.

Front 196

To work with the data in a column that uses FILESTREAM storage from a .NET application, you must use all but one of the following. Which one is it?

Back 196

a SafeFileHandle object

Front 197

For each type of action query, a table can have

Back 197

multiple AFTER triggers and one INSTEAD OF trigger

Front 198

The statement
CREATE VIEW Example1
AS
SELECT VendorName, SUM(InvoiceTotal) AS SumOfInvoices
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName
ORDER BY VendorName;

Back 198

will fail because the ORDER BY clause isn’t allowed in this view

Front 199

If a user fetches a row through a cursor with the SCROLL_LOCKS concurrency option and a second user tries to fetch the same row,

Back 199

the second user can fetch the row but can’t update the row through the cursor until the first user has fetched a different row

Front 200

What SQL Server function can you use to generate a globally unique value?

Back 200

NEWID

Front 201

Which of the following statements will fail to fetch a row?

Back 201

FETCH RELATIVE FROM Ex_Cursor;

Front 202

Given the following statements that declare a local variable and set its value, which of the following will cause an error?

Back 202

SELECT *
FROM @Example1;

Front 203

The statement
CREATE VIEW Example3
AS
SELECT *
FROM Invoices;

Back 203

will create an updatable view

Front 204

Each of the following statements about triggers is true except for one. Which one is it?

Back 204

A trigger can have more than one batch.

Front 205

To control the flow of execution based on a true/false condition, you code a/an:

Back 205

All of the above

Front 206

Each of the following is a benefit provided by using views except for one. Which one is it?

Back 206

You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.

Front 207

The intersection of a row and a column is commonly called a/an _______________.

Back 207

cell

Front 208

To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.

Back 208

DISTINCT

Front 209

The most common type of relationship between two tables is a/an ___________________ relationship.

Back 209

one-to-many

Front 210

When you code a DELETE statement for one or more rows, the _________________ clause specifies which row or rows are to be deleted.

Back 210

WHERE

Front 211

The three main hardware components of a client/server system are the clients, the server, and the __________________________________.

Back 211

network

Front 212

When you use the implicit syntax for coding inner joins, the join conditions are coded in the ____________ clause.

Back 212

WHERE

Front 213

Insert, Update, and Delete statements can be referred to as ______________ queries.

Back 213

action

Front 214

The ________________ clause of the SELECT statement names the table that contains the data to be retrieved.

Back 214

FROM

Front 215

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

Back 215

comma

Front 216

To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.

Back 216

LIKE

Front 217

The MERGE statement is sometimes referred to as the _________________ statement.

Back 217

upsert

Front 218

Write an aggregate expression to find the oldest date in the InvoiceDate column:

Back 218

MIN(InvoiceDate)

Front 219

A common table expression creates a temporary _____________ that can be used by a query that follows the CTE.

Back 219

table

Front 220

A union combines the rows from two or more _______________________.

Back 220

SELECT Statements

Front 221

If you want to join all of the rows in two tables whether or not they are matched, you use a/an _______________ join.

Back 221

full outer

Front 222

Write an aggregate expression to find the VendorName column that’s last in alphabetical order:
_________________________.

Back 222

MAX(VendorName)

Front 223

Write an aggregate expression for the number of unique values in the VendorID column:
_________________________.

Back 223

COUNT(DISTINCT VendorID)

Front 224

When you code a column list in the INTO clause of an INSERT statement, you can’t include a/an _____________________ column.

Back 224

identity

Front 225

In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.

Back 225

equals

Front 226

SQL statements that define the tables in a database are referred to as _______________ statements.

Back 226

DDL

Front 227

To test whether one or more rows are returned by a subquery, you can use the ______________ operator.

Back 227

EXISTS

Front 228

___________________ names can be used when you want to assign a temporary name to a table.

Back 228

Correlation

Front 229

Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values:

Back 229

SELECT AVG(InvoiceTotal) FROM Invoices

Front 230

Write an aggregate expression to find the VendorName column that's last in alphabetical order:

Back 230

SELECT MAX(VendorName) FROM Vendors

Front 231

Write an aggregate expression to find the oldest date in the InvoiceDate column:

Back 231

SELECT MIN(InvoiceDate) FROM Invoices

Front 232

Write a query that returns the number of rows in a table named Vendors and puts it in a column named NumRows

Back 232

SELECT COUNT(*) as NumRows FROM Vendors

Front 233

Write an aggregate expression for the number of unique values in the VendorID column:

Back 233

SELECT COUNT(DISTINCT VendorID) FROM Vendors

Front 234

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.

Back 234

ORDER BY CAST(PartCode As int)

Front 235

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

Back 235

DELETE Vendors

Front 236

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

Back 236

CAST(InvoiceAmount AS varchar)

Front 237

The IIF function determines the value it returns based on a/an ____________________ expression.

Back 237

Boolean

Front 238

The most common type of relationship between two tables is called a/an _______________ relationship.

Back 238

one-to-many

Front 239

The analytic functions provide a way to perform calculations on ____________________ result sets.

Back 239

ordered

Front 240

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

Back 240

CAST(InvoiceAmount AS varchar)

Front 241

A constraint that limits the values that can be stored in a column is called a/an ______________________________ constraint.

Back 241

check

Front 242

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

Back 242

referential integrity

Front 243

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

Back 243

linking

Front 244

You use DDL to create, modify, and delete the ___________________________ of a database.

Back 244

objects

Front 245

You can use the ____________________ data type to store a date that includes a time zone offset.

Back 245

datetimeoffset

Front 246

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

Back 246

log

Front 247

To create a new table by using a SELECT statement you code the _______ clause

Back 247

INTO

Front 248

The rows in a table are kept in the sequence that’s based on its __________________________ index.

Back 248

clustered

Front 249

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

Back 249

column

Front 250

The ___________ data types are used to store whole numbers.

Back 250

Integer

Front 251

A constraint that enforces referential integrity between tables is called a/an ______________________________ constraint.

Back 251

foreign key

Front 252

____________________ characters can be used to encode the characters that are used in languages throughout the world.

Back 252

Unicode

Front 253

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

Back 253

null

Front 254

Typically, most database designers consider a database structure normalized if it’s in the ________________________ normal form.

Back 254

third

Front 255

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

Back 255

precision