Study guide hehehehe
To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause?
DISTINCT
Which function can you use to return the path to the binary file stream?
PathName
To code a/an ________________ value as a literal, precede the value with the character N
Unicode
To modify an existing view, you use which statement?
ALTER VIEW
By default,
columns in a view are given the same names as the columns in the base tables
What do you use to uniquely identify each row in a table?
a primary key
By default, FILESTREAM storage is ________________ for the server.
disabled
Typically, what form do most database designers consider a database structure to be normalized?
third
If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? LEN(LTRIM(RTRIM(CustomerAddress))) AS Solution
19
When you use weekday with the DATEPART function, it returns an integer that indicates the day of the week where
1=Sunday, 2=Monday, etc.
If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? CASE WHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2) WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2) ELSE 0 END AS Solution
225.00
All of the following values can be stored in a column that's defined as decimal(6,2), except
32492.05
Which of the following is the default transaction isolation level for SQL Server?
READ COMMITTED
Which of the following statements returns the value of a variable named @InvoiceCount?
RETURN @InvoiceCount;
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 unpaid balance due for each VendorID
You use the UPDATE statement to modify one or more rows in the table named in the ________________ clause.
UPDATE
Code a statement that changes the database context to a database named TestDB.
USE TestDB;
All of the following statements about application roles are true except for one. Which one?
Unlike a standard database role, an application role can contain only one member.
Which of the following recommendations won't improve the readability of your SQL statements?
Use comments to describe what each statement does.
When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the _________________ clause specifies which row or rows are to be updated.
WHERE
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause?
WHERE
Which data type do you use to define a column that contains a GUID?
a primary key
If introduced as follows, the subquery can return which of the values listed below?
a single value
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)
a subquery can't be introduced in this way
A subquery that's used in a WHERE or HAVING clause is called what?
a subquery search condition
With the ALTER LOGIN statement, you can
a. change the name for a login ID Correctb. all of the above c. change the default database or language d. enable or disable a login ID
A view
a. consists only of the rows and columns specified in its CREATE VIEW statement Correctb. all of the above c. doesn't store any data itself d. is like a virtual table
To be in the third normal form,
a. each non-key column must depend only on the primary key Correctb. all of the above c. each non-key column must depend on the entire primary key d. the non-key columns must not contain repeating values
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
a. includes summary rows b. adds summary rows for specified groups c. allows you to use additional sets of parentheses to create composite groups Correctd. all of the above
You can code views that
a. join tables Correctb. all of the above c. summarize data d. use subqueries and functions
Concurrency is:
a. only a problem when data is being modified, not when two or more transactions simply read the same data b. more of a problem on larger systems with more users and more transactions Correctc. all of the above d. the ability of a system to support two or more transactions working with the same data at the same time
The WITH SCHEMABINDING clause of the CREATE VIEW statement
a. protects the view by binding it to the database schema b. prevents the tables that the view is based on from being deleted c. prevents the tables that the view is based on from being modified in a way that affects the view Correct d. all of the above
What kind of constraint enforces referential integrity between tables?
a. reference constraint b. none of the above c. foreign key constraint Correctd. both a and b
To enforce referential integrity, the database can
a. return an error instead of doing the requested action b. do the requested action and do the related changes to the related tables c. do the requested action and mark any orphans in related tables Correctd. a or b e. a or c
To delete an existing view, you use which statement?
DROP VIEW
Which statement can you use to control the flow of execution based on a true/false condition?
IF...ELSE
To normalize a data structure, what do you apply in sequence?
normal forms
A full outer join includes rows that satisfy the join condition, plus
rows in both tables that don't satisfy the join condition
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
A series of SQL statements that you can store in a file is called a
script
An index improves performance when SQL Server ______________________
searches a table
An index improves performance when SQL Server ______________________.
searches a table
The entities that can be secured on a server are called ________________.
securables
The highest level at which you can grant permissions is the ________________ level.
server
SQL Server supports ________________ of the ANSI-standard data types
some, but not all
In an UPDATE statement, the WHERE clause will
specifiy the condition a row must meet to be updated
All of the system objects that define a database are stored in what?
system catalog
To model a database on a real-world system, you typically represent each real-world entity as a
table
The scope of a temporary table is limited to what?
the database session in which it's defined
Which choice below will increase the storage capacity of a varchar column so it can store up to 2 gigabytes of data?
varchar(max)
In a join, column names only need to be qualified where?
when the same names are used in both tables
The statement CREATE VIEW Example2 AS SELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate) FROM Invoices;
will fail because the second column isn't named
To run a SELECT statement from an application program, you store the statement in the ________________ object for the database connection.
command
To store and manage the databases of the client/server system, each server requires what?
database management system (DBMS)
You can use what data type to store a date that includes a time zone offset?
datetimeoffset
In SQL Server, what do you typically use to enforce referential integrity?
declarative referential integrity
You can use the OVER clause with an aggregate function to
include the rows used to calculate the summary in the result set
Which data types are used to store whole numbers?
integer
The CUBE operator is similar to the ROLLUP operator except that
it adds summary rows for every combination of groups
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);
200,000.00
A join that joins a table with itself is called
a self-join
Code a statement that tests whether the database named TestDB exists.
IF DB_ID ('TestDB') IS NOT NULL
Which of the following statements best describes what this INSERT statement does? INSERT INTO InvoiceArchive SELECT * FROM Invoices WHERE TermsID = 1;
Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table.
Which functions perform a calculation on the values of a column from selected rows?
Aggregate
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
Data validation is the process of
preventing errors due to invalid data
The WITH CHECK option of the CREATE VIEW statement
prevents an update from being performed through the view if it causes a row to no longer be included in the view
The WITH ENCRYPTION clause
prevents other users from examining the SELECT statement on which the view is based
The WITH ENCRYPTION clause of the CREATE VIEW statement
prevents users from seeing the code that defines the view
What do you call a local variable that can store a single value?
scalar
Unlike most database objects, when you invoke a user-defined function, you must always preface the name of the function with the
schema name
A relational database consists of one or more what?
tables
An at sign (@) at the beginning of an identifier indicates
that the identifier is a local variable or parameter
When coding a definition for a column that will contain a high percentage of null values, what attribute can you use to optimize the storage?
the SPARSE attribute
A lost update occurs when
two transactions select the same row and then update the row based on the values originally selected
You can store a BLOB value in a column of which data type?
varbinary(max)
If you delete a stored procedure, function, or trigger and then create it again
you delete the security permissions assigned to the object
If you assign a correlation name to one table in a join,
you have to use that name for the table in the query
When you create a script for creating a database,
you need to create the referred to tables before you create the tables that refer to them
A ________________ is a value that's unique within the current database and other networked versions of the database around the world.
GUID (globally unique identifer)
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.
HAVING
Write an aggregate expression to find the latest date in the InvoiceDate column
MAX(InvoiceDate)
Write an aggregate expression to find the VendorName column that's last in alphabetical order
MAX(VendorName)
Which option can you use to make SQL Server prompt the user for a new password the first time the login ID is used?
MUST_CHANGE
What SQL Server function can you use to generate a globally unique value?
NEWID
How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?
ON DATABASE
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)
Figure 10-1 nar001-1.jpg (Refer to figure 10-1.) Which column or columns in each table are foreign ke
OrderLineItems: OrderID and ProductID Products: none
Which function would you use to calculate the rank of the values in a sorted set of values as a percent?
PERCENT_RANK
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 AND InvoiceTotal >= 500
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 AND InvoiceTotal >= 500
To define a column that contains a GUID, you specify which property for the column?
ROWGUIDCOL
To log on to SQL Server using your SQL Server login ID, you use ________________ authentication
SQL Server
To view the code that's generated for the view, you would use what?
SQL pane
If you want to filter the result set that's returned by a SELECT statement, you must include which clause?
WHERE
When you code a DELETE statement for one or more rows, which clause specifies which row or rows are to be deleted?
WHERE
You can use the DELETE statement to delete one or more rows from the table you name in the ________________ clause.
WHERE
Which statement can you use to repeatedly execute a statement or set of statements?
WHILE
A string expression can consist of
a. one or more literal values b. one or more character columns c. a combination of character columns and literal values Correctd. all of the above
When you use Transact-SQL, you can store procedural code in
a. scripts b. user-defined functions Correctc. all of the above d. stored procedures
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 FULL keyword, the total number of rows that are returned must equal
a. the number of rows in the Invoices table b. the number of rows in the Vendors table c. the number of rows in the Invoices table plus the number of rows in the Vendors table Correctd. none of the above
If you've declared save points, the COMMIT TRAN statement
commits the entire transaction
To make a parameter for a stored procedure optional, what do you assign to it?
default value
Before you can delete a server role, you must
delete all of its members
Which of the following does not violate the referential integrity of a database?
deleting a row in a foreign key table without deleting the related row in the related primary key table
What kind of lock only allows one transaction to access a resource?
exclusive
Before you can use FILESTREAM storage, you must use the CREATE DATABASE statement to create a _____________ to provide for FILESTREAM storage.
file group
What is a type of nonclustered index that includes a WHERE clause?
filtered index
Users who are involved in the administration of the server are typically assigned to one of the ________________ roles that are built into SQL Server.
fixed server
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
foreign
The relative amount of data that's included in a resource can be referred to as the resource's
granularity
If you assign a value with one data type to a column with another data type, SQL Server converts the value to the data type of the column using what?
implicit conversion
You typically use the return value of a stored procedure to
indicate to the calling program whether the stored procedure completed successfully
Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This type of join is called a/an
inner join
Which is not one of the four types of concurrency problems?
integrity reads
This indicates that SQL Server plans to obtain a shared lock or an exclusive lock on a finer-grain resource.
intent lock
One of the drawbacks of using the SERIALIZABLE isolation level is
it can cause severe performance problems
When you use the SELECT INTO technique to create tables
only the column definitions and data are copied AND primary keys, foreign keys, and default values aren't retained
To override the order of precedence in an arithmetic expression, you can use
parentheses
The ranking functions make it easy to include a column in a result set that provides the sequential ranking number of each row within a ___________________________.
partition
One way to work with binary data is to use a varchar column to store a string value that acts as a _______________ to a binary file.
pointer
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 what?
precompiled
To retrieve or update the data in a database, the client sends a ________________ to the database.
query
What do you call a local variable that can store a single value?
reduces redundancy and makes maintenance easier
To maintain ________________, if you delete a row in a primary key table, you must also delete any related rows in foreign key tables.
referential integrity
The real data type can be used to store a _________________, which provides for numbers with up to 7 significant digits.
single-precision number
To insert several rows selected from another table into a table, you can code an INSERT statement with a/an ______________ in place of the VALUES clause.
subquery
A SELECT statement that includes aggregate functions is often called a/an ________________ query.
summary
This is typically modeled after a real-world entity, such as an invoice or a vendor.
table
When you define a foreign key constraint, you can specify all but one of the following. Which one is it?
that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table
Where would you go to create, modify, or delete logins using the Management Studio?
the Security folder for the server
The WITH CHECK OPTION clause
the conversion of several finer-grained locks to a single coarse-grain lock
By default, SQL Server is in autocommit mode, this means
unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction
In a/an ________________, a table can contain information about two or more entities.
unnormalized data structure
Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the datetime data type and which might include time values?
use the DatePart function to extract just the date from each datetime value
What uniquely identifies each row in a table?
primary key
After you create a schema, you can create any object within that schema by
qualifying the object name with the schema name
To store a date value without storing a time value, you can use the
date data type
If neither of two transactions can be committed because they each have a lock on a resource needed by the other, it's called a/an
deadlock
What do you typically use to relate two tables that have a one-to-one relationship?
primary keys
Which of the following types of statements isn't an action query?
Select
Which statement can you use to manually raise an error within a stored procedure?
THROW
When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?
TOP 10 InvoiceNumber
Which statement can you use to handle errors caused by one or more SQL statements?
TRY...CATCH
The first character of an identifier must be
any of the above
You can invoke a table-valued user-defined function
anywhere you'd refer to a table or a view
SQL Server's lock manager always tries to lock resources
at the highest possible granularity
To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table
. foreign key
What would 7,800,000,000 be using scientific notation?
7.8 X 109 or 7.8E+9
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
Which statement is true about the DENY and REVOKE statements?
A denied permission can't be granted by role membership, but a revoked permission can
Each of the following statements about triggers is true except for one. Which one?
A trigger can have more than one batch.
When coding search conditions, you can use which keyword to create compound search conditions?
AND
The search condition of a WHERE clause consists of one or more
Boolean expressions
Each of the outcomes listed below is a result of executing the following script except for one. Which one? CREATE ROLE ExampleRole; ALTER ROLE db_datareader ADD MEMBER ExampleRole; GRANT INSERT,UPDATE ON Vendors TO ExampleRole; DENY INSERT ON Vendors TO ASmith; ALTER ROLE ExampleRole ADD MEMBER ASmith;
By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.
All of the aggregate functions ignore null values, except for which function?
COUNT(*)
___________________ names can be used when you want to assign a temporary name to a table.
Correlation
Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) The column name for the second column in the result set will be?
Date
To select the columns for a view, you would use what?
Diagram pane
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
Which of the following statements executes a stored procedure named spInvoiceCount and stores its return value in a variable named @InvoiceCount? Assume that the @InvoiceCount variable has already been declared and that the stored procedure doesn't accept any parameters.
EXEC @InvoiceCount = spInvoiceCount;
Which of the following statements calls the following stored procedure, passes the value '2015-12-01' to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal? CREATE PROC spInvoiceTotal2 @DateVar smalldatetime, @InvoiceTotal money OUTPUT AS SELECT @InvoiceTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar; (Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position.)
EXEC spInvoiceTotal2 '2015-12-01', @MyInvoiceTotal OUTPUT;
To test whether one or more rows are returned by a subquery, you can use which operator?
EXISTS
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/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?
Earliest past due date: 02/09/16
After locating the characters that separate the components of a string you wish to parse, you can use which functions to extract the individual components?
LEFT, RIGHT, SUBSTRING, and LEN
Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?
LIKE
Which function returns the string with any leading spaces removed?
LTRIM
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 04/04/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?
Latest past due date: 03/20/16
A subquery is a/an ______________ statement that's coded within another SQL statement.
SELECT
A view is a/an ________________ statement that's stored as an object in the database.
SELECT
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, FROM, WHERE, ORDER BY
Which of the following is the most restrictive transaction isolation level?
SERIALIZABLE
Code a statement that assigns the value "Test" to a scalar variable named @Name that's declared with the varchar data type.
SET @Name = 'Test';
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 column name for the fifth column in the result set doesn't match the data
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.) Although this query runs as coded, it contains this logical error:
The column name for the fifth column in the result set doesn't match the data
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;
The number of vendors in each state having more than one vendor
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.) What can cause the WHILE loop in this script to end other than the expression on the statement becoming true?
When the value of the @InvoiceTotal variable becomes less than 1000
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?
When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000
What should you use to retrieve binary data?
a SELECT statement just as you would for other types of data
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery)
a column of one or more rows
Which of the following is true about a transaction?
a. By default, each SQL statement is treated as a separate transaction. b. It is a group of database operations that are combined into a logical unit. c. You can combine any number of SQL statements into a single transaction. Correctd. all of the above
Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 20th row?
a. ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS
Figure 10-1 nar001-1.jpg (Refer to figure 10-1.) Which column or columns in each table should be defined as the primary key?
a. Orders: OrderID OrderLineItems: OrderID and OrderSequence Products: ProductID b. Orders: OrderID OrderLineItems: OrderID, OrderSequence, and ProductID Products: ProductID and ProductName c. Orders: OrderID OrderLineItems: OrderID Products: ProductID d. Orders: OrderID and OrderDate OrderLineItems: OrderID and OrderSequence Products: ProductID
Which keyword lets you control the number of rows that are returned by a query?
a. TOP b. ALL c. DISTINCT Correctd. all of the above
The dbcreator role
a. allows new members to be added to the role b. lets members create, alter, and drop databases c. is intended for those users who need to be able to work with database objects Correctd. all of the above
The first character of an identifier must be
a. an underscore (_) b. an at sign (@) c. a letter as defined by the Unicode Standard 2.0 Correctd. any of the above
Insert, Update, and Delete statements can be referred to as ________________ queries.
action
When you identify a column as the primary key, the column
all of the above
You can code a subquery
all of the above
If you omit the WHERE clause from a DELETE statement
all rows in the table will be deleted
You can use the GRANT statement to give users permission to use each of the following items except for one. Which one?
all the objects in a database
You can't update
an identity column
When you code an ORDER BY clause, you can specify a
column name, alias, expression, or column number
A table that's used to create a view is called a what?
base
A BLOB can be used to store large amounts of what kind of data?
binary
You should use FILESTREAM storage when
both a and b
Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) The name "v" is known as a?
both correlation name and table alias
Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This join is coded using what syntax?
both explicit and SQL-92
After you identify and subdivide all of the data elements for a database, you should group them by the entities with which they're associated. The entities will become the tables of the database, and the elements will be come the
columns
Which utility can you use to execute Transact-SQL scripts from a command line?
can use non-aggregate search conditions but can't use aggregate search conditions
The SELECT statement for a view
can use the ORDER BY clause if it also uses the TOP clause
One way to examine the system objects that define a database is to use which views?
catalog
What kind of constraint limits the values that can be stored in a column?
check constraint
When you need to code multiple conditions in a join, it's best to
code only join conditions in the ON clause
When you code a table-level check constraint, the constraint can refer to data in more than one
column
Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.
column alias
When you subdivide a data element, you can easily rebuild it when necessary by ________________ the individual components.
concatenating
When you use the CREATE TABLE statement to create a table, you can also define the attributes and ______________ for the columns.
constraints
The char and nchar data types are used to store fixed-length strings. Which of the statements below is true?
coorect a. all of the above b. Data stored using these data types occupies the same number of bytes regardless of the actual length of the string. c. They are typically used to define columns that have a fixed number of characters d. The char(2) data type will always contain two characters
When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is ________________ a new table.
copied into
In the View Designer, you can
correct a all of the above b. specify the selection criteria and sort order for a view c. display the results of a view d. edit the design of an existing view
A user who's granted the REFERENCES object permission can do what?
create objects that refer to the object
The interface between an application program and the DBMS is usually provided by the
data access API
A view is a SELECT statement that is stored with the ________________.
database
Code example 6-2 WITH Top10 AS (SELECT TOP 10 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; (Please refer to code example 6-2.) When this query is executed, the result table will contain one row for
each vendor in the Top10 table
Code example 6-2 WITH Top10 AS (SELECT TOP 10 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; (Please refer to code example 6-2.) When this query is executed, the result table will contain one row for
each vendor in the Top10 table
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
each vendor with invoice totals over $500
If FirstName contains "Edward" and LastName contains "Williams", what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution
ewilliam
The integer and decimal data types are considered ________________ because their precision is exact
exact numeric data types
The integer and decimal data types are considered ________________ because their precision is exact.
exact numeric data types
Code example 6-2 WITH Top10 AS (SELECT TOP 10 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; (Please refer to code example 6-2.) In this query, the table named Top10 is used as part of a
join
In many cases, a subquery can be restated as a/an ______________.
join
What can you use to combine data from two or more tables into a single result set?
join
You can use a ________________ in the ________________ clause if you need to specify column values or search conditions other than the one named in the UPDATE clause.
join, FROM
If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use what kind of join?
left outer
SQL Server automatically avoids some concurrency problems by using what?
locks
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
of invoices for each vendor that have a larger balance due than the average balance due for all invoices
When passing a list of parameters to a stored procedure by name, you can omit optional parameters by
omitting the parameter name and value from the list
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
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
The most common type of relationship between two tables is called what?
one-to-many relationship
WITH Top10 AS (SELECT TOP 10 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; Please refer to code example 6-2.) When this query is executed, each row in the result table will show
the largest invoice amount related to that row
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
the number of rows in the Invoices table
When you identify the data elements in a new database, you typically subdivide data elements into
the smallest practical components
The scope of a derived table is limited to what?
the statement in which it's defined
Since the MERGE operation often involves updating existing rows and inserting new rows, the MERGE statement is sometimes referred to as what?
the upsert statement
Which uses the least amount of storage?
they all use the same amount of storage
If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaludate to? ISNUMERIC(ZipCode) AS Solution
true
What SQL dialect does Microsoft SQL Server use?
Transact-SQL
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)
a single value
To be in the first normal form, each cell in a table must contain
a single, scalar value
To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like
commands, connections, and data readers
If @@TRANCOUNT is equal to 1, then the COMMIT TRAN statement
decrements @@TRANCOUNT and commits the transaction
If a string consists of one or more components, you can parse it into its individual components. To locate the characters that separate the components, you would use which function?
CHARINDEX
To locate the index of the first character of the first occurence of a substring within another string, you would use which function?
CHARINDEX
The difference between the CAST function and the CONVERT function is that the ________________ function accepts an optional style argument that lets you specify a format for the result
CONVERT
Which of the following statements is true about the CONVERT and TRY_CONVERT functions?
CONVERT returns an error if the expression can't be converted, but TRY_CONVERT returns a NULL value.
Write an aggregate expression for the number of entries in the VendorName column, including null values
COUNT(*)
Write an aggregate expression for the number of unique values in the VendorID column
COUNT(DISTINCT VendorID)
Before you can pass a table to a stored procedure or a function as a parameter, which statement do you use to create a user-defined table type?
CREATE
Which of the following statements creates a login ID for a Windows user named AliceJackson in a domain named Sales?
CREATE LOGIN [Sales\AliceJackson] FROM WINDOWS;
Which statement can you use to create a user-defined database role?
CREATE ROLE
Which of the following statements can be coded in a batch with other statements?
CREATE TABLE
Given the following statements that declare a local variable and set its value, which of the following will cause an error? DECLARE @Example1 varchar(128); SET @Example1 = 'Invoices';
Correcta. SELECT * FROM @Example1; b. PRINT 'Table name is: ' + @Example1; c. IF @Example1 = 'Invoices' SELECT * FROM Invoices; d. SELECT * FROM sys.tables WHERE name = @Example1;
A view
Correcta. all of the above b. doesn't store any data itself c. consists only of the rows and columns specified in its CREATE VIEW statement d. is like a virtual table
Which of the following statements creates a SQL Server login ID for a user named TomBrown with the password 'abc123XYZ'.
Correctc. CREATE LOGIN TomBrown WITH PASSWORD = 'abc123XYZ';
Which of the following is not a SQL DML statement?
CreateTable
Which of the following diagrams best represents the relationship between a table of customers and a table of orders placed by customers? tables as answers
Customers CustomerID CustomerName Orders OrderID CustomerID OrderDate
Code a statement that creates a table variable named @TestTable
DECLARE @TestTable table
Code a statement that creates a table variable named @TestTable.
DECLARE @TestTable table;
Each of the following column attributes is a column constraint, except
DEFAULT
To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause?
DESC
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause
DISTINCT
Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows?
FETCH
When you create a table that includes a column for storing BLOBs, which attribute can you code to enable FILESTREAM storage for the column?
FILESTREAM
Correlation names are temporary table names assigned in which clause?
FROM
Which clause of the SELECT statement names the table that contains the data to be retrieved?
FROM
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 (Figure document, Figure 2) ?
Figure C
What statement can you use to divide a script into multiple batches?
GO
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?
InvoiceTotal - CreditTotal - PaymentTotal / 10
When you store a BLOB in a database column with FILESTREAM storage, which of the following statements is not true?
The BLOB must be smaller than 2GB.
Each of the following is a valid reason to explicitly code a transaction except for one. Which one?
The results of a SELECT query will be used as a subquery.
Which of the following is not a valid column alias name?
Total Sales
When a column in a table is defined, what determines the kind of data it can store?
a data type
If you define a column as an identity column,
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? SELECT (subquery)
a single value
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax
lets you combine the join and seearch conditions
The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and a
log file
To allow users to log on using either type of authentication, you need to set the SQL Server authentication to ________________ mode.
mixed
For each type of action query, a table can have
multiple AFTER triggers and one INSTEAD OF trigger
Subqueries can be ________________ within other subqueries.
nested
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 FULL keyword, the total number of rows that are returned must equal
none of the above
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
none of the above
The COALESCE function provides one way to substitute constant values for which values?
null
The GROUPING function lets you substitute another value for a/an ____________________ value when you use the ROLLUP or CUBE operator.
null
When you code a column list in an INSERT statement, you can omit identify columns, columns that have default values, and columns that allow __________ values.
null
Which statement can you use to explicitly start a transaction?
BEGIN TRAN
A union combines the rows from two or more what?
all of the above
To return all of the columns from the base table, which wildcard character do you include in the SELECT clause?
*
To concatenate character strings in a string expression, which operator do you use?
+
Code example 6-2 WITH Top10 AS (SELECT TOP 10 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; (Please refer to code example 6-2.) When this query is executed, there will be how many rows in the result table?
5
If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed?
8
If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed? DATEPART(month, RegistrationDate) AS Solution
8
Which statement is used to modify the structure of an existing table?
ALTER TABLE
If you code a column list in an INSERT statement that includes a column that has a default value, which keyword can you code in the VALUES clause to use the default value?
DEFAULT
Write the code for a DELETE statement that deletes every row in the Vendors table
DELETE Vendors;
The varchar and nvarchar data types to store variable-length strings. Which of the statements below is true?
Data stored using these data types occupies only the number of bytes needed to store the string
What statement do you use to execute a dynamic SQL statement?
EXEC
Which of the following statements calls the stored procedure and passes the values '2015-12-01' and 122 to its input parameters? CREATE PROC spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar;
EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2015-12-01';
A ________________ is a value that's unique within the current database and other networked versions of the database around the world
GUID (globally unique identifer)
Which of the following statements is true?
Implicit data type conversion is performed when you mix values of different data types in an expression.
Which statements are true about the ROUND function?
It returns the number rounded to the specified precision
To be able to use FILESTREAM storage, which format is required for the drive that stores the files?
NTFS
Which statement do you use to return a message to the client?
Which of the following is not a good guideline for deciding when to create an index for a column?
The column is frequently updated.
In the INSERT statement that follows, assume that all of the table and column names are spelled correctly, that none of the columns are identity columns, and that none of them have default values or accept null values. What's wrong with the statement? INSERT INTO InvoiceCopy (VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDate, InvoiceDueDate) VALUES (97, '456789', 8344.50, 0, 0, 1, '2016-08-01');
The number of items in the column list doesn't match the number in the VALUES list.
When you code a union that combines two result sets, which of the following is not true?
The result sets must be derived from different tables.
The basic code structure for many SQL statements and objects can be found in which section of the SQL Server Management Studio?
Transact-SQL snippets
____________________ characters can be used to encode most of the characters in most of the languages of the world.
Unicode
All of the following statements about application roles are true except for one. Which one?
Unlike a standard database role, an application role can contain only one member
Each of the following is a benefit provided by using views except for one. Which one?
You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what?
a comma
A user who's granted the EXECUTE object permission can execute what?
a stored procedure or function
Which of the following statements about the SPARSE attribute is true?
all of the above
You use data definition language (DDL) to create, modify, and delete the ________________ of a database.
all of the above
If you use ________________ in the select list, you must name the column since that name is used in the definition of the new table.
calculated values
A user-defined function
can return a single scalar value or a single table value
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
The intersection of a row and a column is commonly called what?
cell
Code example 6-2 WITH Top10 AS (SELECT TOP 10 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; (Please refer to code example 6-2.) In this query, the table named Top10 is coded as a
common table expression (CTE)
Which identifier can't be used in a SQL statement?
email addresses
A correlated subquery is one that
is executed once for each row in the outer query
What is the most common type of relationship between two tables?
one-to-many
The users, groups, logins, and roles that have access to a server are called ________________.
principals
To express the value of a floating-point number, you can use
scientific notation
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows
table
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
The scope of a local variable is limited to what?
the batch in which it's defined
What is lock escalation?
the conversion of several finer-grained locks to a single coarse-grain lock
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;
will fail because the ORDER BY clause isn't allowed in this view
The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
will fail because the SELECT statement returns two columns named VendorID