Study guide hehehehe

¡Supera tus tareas y exámenes ahora con Quizwiz!

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

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?

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';

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

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

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

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?

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

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?

If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed?

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

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

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

Ver todos los conjuntos de estudio

Study guide hehehehe

Conjuntos de estudio relacionados

Chapter 1: What is Plant Biology?

Chapter 5

Colorado Life - Learn As You Go

CWL Infrequent C

Chapter 12 Quiz, Chapter 13 Quiz, Chapter 14 Quiz, Chapter 15 Quiz, Chapter 16 Quiz, Chapter 17 Quiz, Chapter 18 Quiz, Part III Test

CIS Master's set

World War II Practice

Principle of Accountant II - Final Exam

FIN320F Part II

Week 10

Quiz 7

CH03 - Labor Union

Discovering Computers 2016

Science Test 1- Section 4

PN108 PrepU Chapter 42

bus law final

Module 1

Presidential Elections: Details

Amino Acids Structure, Name Function

ACC 403 CH 1