DBA-120: Module 1-10 terms

To modify an existing view, you use which statement?

ALTER VIEW

The WITH CHECK OPTION clause

prevents a row in a view form being updated if that would cause the row to be excluded from the view

The WITH ENCRYPTION clause of the CREATE VIEW statement

prevents users from seeing the code that defines the view

Unlike most database objects, when you invoke a user-defined function, you must always preface the name of the function with the

schema name

When you use Windows authentication to connect to a database, SQL Server

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

In a join, column names only need to be qualified where?

when the same names are used in both tables

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

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

HAVING

Code a statement that tests whether the database named TestDB exists.

IF DB_ID ('TestDB') IS NOT NULL

Which statement can you use to control the flow of execution based on a true/false condition?

IF...ELSE

Which function returns the string with any leading spaces removed?

LTRIM

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)

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

Not, And, Or

Which clause specifies the number of rows that should be skipped before rows are returned from the result set?

OFFSET

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

Which of the following statements returns the value of a variable named @InvoiceCount?

RETURN @InvoiceCount;

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

ROLLUP

You can use the Object Dependencies dialog box of the Management Studio to do all but one of the following. Which one is it?

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

A subquery is a/an ______________ statement that's coded within another SQL statement.

SELECT

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

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

Which function returns the specified number of characters from the string starting at the specified position?

SUBSTRING

Which of the following types of statements isn't an action query? a. Insert b. Update c. Delete d. Select

Select

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

WHERE

A union combines the rows from two or more what? a. SELECT statements b. all of the above c. queries d. result tables

all of the above

Which keyword lets you control the number of rows that are returned by a query? a. ALL b. DISTINCT c. TOP d. all of the above

all of the above

You typically use the return value of a stored procedure to

indicate to the calling program whether the stored procedure completed successfully

A search condition in the ________________ clause is applied before the rows are grouped while a search condition in the _________________ clause isn't applied until after the grouping.

WHERE, HAVING

A table that's used to create a view is called a what?

base

Check constraints you create using DDL can be defined at the a. table level b. column level c. both a and b d. none of the above

both a and b

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

join

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?

+

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.

Each of the following statements about triggers is true except for one. Which one? a. A trigger can't be directly called or invoked. b. A trigger doesn't accept input or return output parameters. c. A trigger can have more than one batch. d. The code of a trigger can execute in place of the action query to which it's assigned.

A trigger can have more than one batch.

When you create a table using the Management Studio, the table is automatically stored in the default schema. If you want to transfer the table to a different schema, you can use the which statement?

ALTER SCHEMA

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

AVG(InvoiceTotal)

Which functions perform a calculation on the values of a column from selected rows?

Aggregate

Some database designers write their own SQL statements for creating a database, its tables, and its indexes instead of using the Management Studio. Why? a. They want to have complete control over how the database is created. b. The scripts generated by the Management Studio are harder to understand. c. It's easier to modify your own script if you want to use it to create the same database for another database management system later on. d. All of the above

All of the above

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

All of the aggregate functions ignore null values, except for which function?

COUNT(*)

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

COUNT(*)

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

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

DDL

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

DECLARE @TestTable table;

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

DISTINCT

To delete an existing view, you use which statement?

DROP VIEW

To test whether one or more rows are returned by a subquery, you can use which operator?

EXISTS

Which clause specifies the number of rows that should be retrieved after skipping the specified number of rows?

FETCH

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 isn't a common error when entering and executing SQL statements? a. Forgetting to select the required database b. Misspelling the name of a table c. Misspelling a keyword d. Forgetting to attach the required database

Forgetting to attach the required database

What statement can you use to divide a script into multiple batches?

GO

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

INSTEAD OF DROP_VIEW

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? a. InvoiceTotal - CreditTotal - PaymentTotal / 10 b. (InvoiceTotal - PaymentTotal - CreditTotal) / 10 c. (InvoiceTotal - (PaymentTotal + CreditTotal)) * 0.10 d. ((InvoiceTotal - PaymentTotal) - CreditTotal) / 10

InvoiceTotal - CreditTotal - PaymentTotal / 10

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less? a. InvoiceTotal <= 1000 b. NOT (InvoiceTotal > 1000) c. InvoiceTotal IN (0,1000) d. InvoiceTotal BETWEEN 0 AND 1000

InvoiceTotal IN (0,1000)

Which statements are true about the ROUND function?

It returns the number rounded to the specified precision

Which function would you use to retrieve data from a subsequent row in a result set? And which function would you use to retrieve data from a previous row?

LEAD, LAG

How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?

ON DATABASE

Which of the following is not a valid column alias name? a. Total b. Total Sales c. TotalSales d. "Total Sales"

Total Sales

What SQL dialect does Microsoft SQL Server use?

Transact-SQL

If you want to filter the result set that's returned by a SELECT statement, you must include which clause?

WHERE

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

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

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

a column of one or more rows

When a column in a table is defined, what determines the kind of data it can store?

a data type

In the View Designer, you can a. display the results of a view b. specify the selection criteria and sort order for a view c. edit the design of an existing view d. all of the above

all of the above

You can invoke a table-valued user-defined function

anywhere you'd refer to a table or a view

Expressions coded in the HAVING clause

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

Expressions coded in the WHERE clause

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

When you need to code multiple conditions in a join, it's best to

code only join conditions in the ON clause

Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.

column alias

The IIF function determines the value it returns based on what type of expression?

conditional

To make a parameter for a stored procedure optional, what do you assign to it?

default value

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

foreign

To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table.

foreign key

A correlated subquery is one that

is executed once for each row in the outer query

What can you use to combine data from two or more tables into a single result set?

join

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

A SQL Server database consists of two files: a database file and a ___________.

log file

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

What is the most common type of relationship between two tables?

one-to-many

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

parition

Stored procedures execute faster than an equivalent SQL script because stored procedures are what?

precompiled

What uniquely identifies each row in a table?

primary key

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

referential integrity

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

script

When you use the Check Constraints dialog box, all of the constraints are at the which level so they can refer to any of the columns in the table?

table

The scope of a local variable is limited to what?

the batch in which it's defined

When you use the Management Studio to create a check constraint, you can specify whether you want the constraint enforced for insert or ________________ operations.

update