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