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