Final exam

The WITH ENCRYPTION clause of the CREATE VIEW statement prevents users from seeing the code that defines the view prevents users from using the view without the appropriate authorization causes the data that's returned by the view to be encrypted prevents users from modifying the view

prevents users from seeing the code that defines the view

Unlike other database objects, when you invoke a user-defined function you must always include the ______________________________.

schema name

A SELECT statement that includes aggregate functions is often called a/an _____________________________ query.

summary

All of the system objects that define a database are stored in the ______________________________.

system catalog

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

table

Parameters for stored procedures and functions can be of any valid SQL Server data type except date/time numeric table xml

table

Unless a database system supports declarative referential integrity, the only way to enforce referential integrity is to use ______________________________.

triggers

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 includes all rows that occur in both result sets if the primary keys are the same excludes rows from the first result set if they also occur in the second result set excludes rows from the second result set if they also occur in the first result set

Correct! includes only rows that occur in both result sets

Which of the following types of SQL statements isn't a DML statement? Select Update CreateTable Insert

CreateTable

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

DDL

Code a statement that creates a table variable named @TestTable._____________________________________________________________________

DECLARE @TestTable table

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

DELETE FROM Vendors

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

DISTINCT

To delete an existing view, you use the ______________________________ statement.

DROP VIEW

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 ______________________________ option.

ENCRYPTION

A parameter passes a value from the calling program to the stored procedure, unless you code the ______________________________ keyword.

OUTPUT

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? PaymentDate IS NOT NULL OR InvoiceTotal >= 500 NOT (PaymentDate IS NULL AND InvoiceTotal <= 500) PaymentDate IS NOT NULL AND InvoiceTotal >= 500 PaymentDate IS NULL AND InvoiceTotal > 500

PaymentDate IS NOT NULL AND InvoiceTotal >= 500

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

RAISERROR

Code a statement that returns the value of a variable named @InvoiceCount from a stored procedure named spInvoiceCount.

RETURN @InvoiceCount

A union combines the rows from two or more _______________________.

Result sets

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

WHERE

To repeatedly execute a statement or set of statements, you code a/an ______________________________ statement.

WHILE

One way to examine the system objects that define a database is to use the ______________________________ views.

catalog

To make a parameter for a stored procedure optional, you assign it a/an ______________________________.

default value

A correlated subquery is one that is executed once for each row in the outer query uses correlation names for the tables in the outer query uses correlation names for the tables in the subquery uses correlation names for one or more of the tables in a join

is executed once for each row in the outer query

If you use the Management Studio to create a database, the Studio will automatically create a database file plus a ______________________ file.

log

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

objects

To override the order of precedence in an arithmetic expression, you can use __________________.

parentheses

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

precision

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

precompiled

To return all of the columns from the base table, you can code the ___________ operator in the SELECT clause.

*

Which of the following types of statements isn't an action query? Delete Insert Update Select

Select

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

Write an aggregate expression for the number of entries in the VendorName column, including null values:

COUNT(*)

Code a statement that calls the following stored procedure and passes the values '2011-12-01' and 122 to its input parameters. Pass the parameters by name in the reverse order that they're declared in the procedure. CREATE PROC spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar;

EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01';

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

EXISTS

A view is a/an ______________________________ statement that's stored as an object in the database.

SELECT

To execute Transact-SQL scripts from a command line, you can use the ______________________________ utility.

SQLCMD

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

Third

A table that's used to create a view is called a/an ______________________________ table.

base

A user-defined function can return multiple scalar values or a single table value can return a single scalar value or a single table value can return multiple scalar values or multiple table values can't accept input parameters

can return a single scalar value or a single table value

A user-defined function can return multiple scalar values or multiple table values can return a single scalar value or a single table value can return multiple scalar values or a single table value can't accept input parameters

can return a single scalar value or a single table value

Expressions coded in the HAVING clause can use aggregate search conditions but can't use non-aggregate search conditions can refer to any column in the base tables can use non-aggregate search conditions but can't use aggregate search conditions can use either aggregate search conditions or non-aggregate search conditions

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

You typically use the return value of a stored procedure to indicate to the calling program whether the stored procedure completed successfully return a zero value receive an input parameter from the calling program return an output parameter to the calling program

indicate to the calling program whether the stored

In a join, column names need to be qualified only in inner joins in outer joins when the code is confusing when the same names are used in both tables

when the same names are used in both tables

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

32492.05

If ExpirationDate contains a value that's equivalent to November 2, 2008 and the GetDate function returns a value that's equivalent to December 17, 2008, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution 45 1 30 15

45

Given the following column values, what will the value of the column containing this function be? [LoanBal] = 21000 [YearlyInc] = 35000 [Exc] = 3 IIF([LoanBal] > 21500, IIF([YearlyInc] > 34000, [Exc] * 500, [Exc] * 1000), 5000)

5000

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

ALTER TABLE

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. Adds all of the rows in the Invoices table to the InvoiceArchive table and sets the TermsID column to 1 in each row. Adds one row from the Invoices table to the InvoiceArchive table. Updates all of the rows in the InvoiceArchive table that have 1 in the TermsID column to the rows in the Invoices table.

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

The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it Only includes summary rows. All of the above. Only adds summary rows for specified groups. Allows you to use additional sets of parentheses to create composite groups.

All of the above.

To be in the third normal form, each non-key column must depend only on the primary key All of the answers listed. the non-key columns must not contain repeating values each non-key column must depend on the entire primary key

All of the answers listed.

If you want to use the Management Studio to modify the data for a table, you can right-click on the table and select the Select Top 1000 Rows command Edit Top 200 Rows command Design command View Top 100 Rows command

Edit Top 200 Rows command

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

FROM

To divide a script into multiple batches, you use the ______________________________ command.

GO

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.

GO

A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.

HAVING

Code a statement that tests if the database named TestDB exists._____________________________________________________________________

IF DB_ID('TestDB') IS NOT NULL

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

INTO

Which of the following statements is true? Implicit data type conversion is performed any time you mix values of different data types in an expression. Values are implicitly converted from the data type with higher precedence to the data type with lower precedence. Every data type can be implicitly converted to every other data type. You can convert a data type implicitly by using either the CAST or the CONVERT function.

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

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? NOT (InvoiceTotal > 1000) InvoiceTotal <= 1000 InvoiceTotal IN (0,1000) InvoiceTotal BETWEEN 0 AND 1000

InvoiceTotal IN (0,1000)

In many cases, a subquery can be restated as a/an ________________.

JOIN

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

LIKE

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 a/an _______________ join.

Left Outer

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

MAX(VendorName)

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

MIN(InvoiceDate)

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

NULL

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: _____________________________________________________

ORDER BY CAST(PartCode As int)

When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding the ___________________________ operator.

ROLLUP

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

You can use the Object Dependencies dialog box of the Management Studio to do all but one of the following. Which one is it? Review the tables that a specific table depends on Review the stored procedures and views that depend on a specific table Review the stored procedures and views that a specific table depends on Review the tables that depend on a specific table

Review the stored procedures and views that a specific table depends on

The six clauses of the SELECT statement must be coded in the following order: SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING

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

When you code a SELECT statement, you must code the four main clauses in the following order SELECT, WHERE, ORDER BY, FROM SELECT, FROM, WHERE, ORDER BY SELECT, ORDER BY, FROM, WHERE SELECT, FROM, ORDER BY, WHERE

SELECT, FROM, WHERE, ORDER BY

When you use the Management Studio to create a database, including its tables and indexes, the Studio actually generates and runs the _________________________ statements that are necessary to create the database.

SQL

The ________________________________________ is a graphical user interface for working with the objects in a SQL Server database.

Sql server management studio

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers? TOP 10 PERCENT WITH TIES InvoiceNumber TOP 10 PERCENT InvoiceNumber TOP 10 InvoiceNumber TOP InvoiceNumber, InvoiceTotal

TOP 10 InvoiceNumber

Which of the following is not a good guideline for deciding when to create an index for a column? The column is frequently used in search conditions or joins. The column contains a large number of unique values. The column is frequently updated. The column is a foreign key.

The column is frequently updated.

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 The total amount invoiced by each VendorID The total of paid invoices for each VendorID The total unpaid balance due for each VendorID The unpaid balance for each invoice

The total unpaid balance due for each VendorID

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

WHERE

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

WHERE

Each of the following is a benefit provided by using views except for one. Which one is it? You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view. You can simplify data retrieval by hiding multiple join conditions. You can provide secure access to data by creating views that provide access only to certain columns or rows. You can create custom views to accommodate different needs.

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 InvoiceTotal > ALL (subquery) a subquery can't be introduced in this way a single value a column of one or more rows a table

a column of one or more rows

If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery) a single value a table a subquery can't be introduced in this way a column of one or more rows

a column of one or more rows

If you define a column as an identity column, you can't use the column as a primary key column you must provide a unique numeric value for that column whenever a row is added to the table you must also define the column with a default value a number is generated for that column whenever a row is added to the table

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

If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) a single value a subquery can't be introduced in this way a table a column of one or more rows

a single value

Expressions coded in the WHERE clause can use aggregate search conditions but can't use non-aggregate search conditions can use non-aggregate search conditions but can't use aggregate search conditions can use either aggregate search conditions or non-aggregate search conditions must refer to columns in the SELECT clause

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

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

check

The CREATE TABLE statement creates a new table in the current database All of the answers listed. creates a new table in the specified database specifies attributes for an existing table

creates a new table in the current database

To store a date value without storing a time value, you can use the datetime2 data type datetime data type time data type date data type

date data type

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 inserting a new row into a table with a foreign key that doesn't match a primary key in the related table updating a foreign key with a value that doesn't match a primary key in the related table updating a primary key in a primary key table without also updating the foreign keys for the related rows in all related tables

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

A subquery coded within a FROM clause is also known as a derived table coffee table join table correlated table

derived table

A database __________________ is a schematic drawing that shows you the relationships between the tables you're working with.

diagram

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 ewilliams EWilliams EWilliam

ewilliam

In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.

foreign

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

full outer

The WITH CHECK option of the CREATE VIEW statement prevents users from using the view without the appropriate authorization prevents an update from being performed through the view if it causes a row to no longer be included in the view prevents rows from being inserted through the view prevents rows from being deleted through the view

prevents an update from being performed through the view if it causes a row to no longer be included in the view

Within the Management Studio, you can build a SQL statement without having to write your own code by using the ____________________________________.

query designer

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

referential integrity

You don't ever need to code a right outer join because left outer joins are easier to code left outer joins are just as efficient right outer joins are less efficient right outer joins can be converted to left outer joins

right outer joins can be converted to left outer joins

In a cross join, all of the rows in the first table are joined with all of the distinct rows in the second table unmatched rows in the second table matched rows in the second table rows in the second table

rows in the second table

A local variable that can store a single value is called a/an ______________________________ variable.

scalar

A series of SQL statements that you can store in a file is called a/an ______________________________.

script

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

script

If you define a column with a default value, that value is used whenever a row that doesn't include a value for that column is added to the table with a zero value for that column is added to the table is added to the table in the table is updated

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

The scope of a local variable is limited to the statement in which it's defined the database session in which it's defined the batch in which it's defined the script in which it's defined

the batch in which it's defined

Code example: SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID The total number of rows returned by this query must equal the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above the number of rows in the Invoices table the number of rows in the Vendors table

the number of rows in the Invoices table

The scope of a derived table is limited to the batch in which it's defined the script in which it's defined the database session in which it's defined the statement in which it's defined

the statement in which it's defined

To handle errors caused by one or more SQL statements, you can use the ______________________________ statement.

try/catch

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

unicode

When you use Windows authentication to connect to a database, SQL Server requires that you use a special login name and password for your authorization lets you access the database without authorization as long as you're logged on to Windows uses both Windows and SQL Server login names and passwords for authorization uses the login name and password that you use for your PC to authorize your connection

uses the login name and password that you use for your PC to authorize your connection

The statement CREATE VIEW Example1ASSELECT VendorName, SUM(InvoiceTotal) AS SumOfInvoicesFROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorIDGROUP BY VendorNameORDER BY VendorName will fail because the column alias SumOfInvoices is invalid will succeed will fail because the GROUP BY clause isn't allowed in this view will fail because the ORDER BY clause isn't allowed in this view

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

The statement CREATE VIEW Example2ASSELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate)FROM Invoices will create a read-only view because of a calculated value will fail because the second column isn't named will create an updatable view will create a view through which you can delete rows, but not insert or update rows

will fail because the second column isn't named