Deck 10: Managing Databases With SQL Server 2008 R2

Question 1

The Microsoft SQL Server Management Studio cannot verify SQL statements until they are executed.

Answer 1

False

Question 2

The Microsoft SQL Server Management Studio Manager can be used to create a new database.

Answer 2

True

Question 3

To use an SQL Server reserved word as a user identifier,it must be enclosed in square brackets [ ].

Answer 3

True

Question 4

SQL Server 2008 provides two ways to create tables-graphically and through SQL code.

Question 5

Stored procedures and triggers in SQL Server may become confused while executing stored procedures and triggers when SQL Server special words such as TRANSACTION appear as table or other names,even if they are enclosed in square brackets [ ].

Question 6

The developer can direct SQL Server to create indexes on non-key fields.

Question 7

When viewing the SQL Server list of tables in a database,dbo means "domain base object" and indicates a system table.

Question 8

SQL Statements can be submitted to SQL Server from the Microsoft SQL Server Management Studio.

Question 9

The SQL Server default when creating a new database is to create two data files and one log file for each database.

Question 10

In an SQL Server clustered index,the data are stored in the bottom level of the index and in the same order as the index.

Question 11

SQL Server automatically creates an index on primary key fields only.

Question 12

In the Microsoft SQL Server Management Studio,the starting value for a field that has been set as an IDENTITY in SQL Server is set in the seed (StartingValue)property.

Question 13

SQL Server supports three types of indexes: clustered,unclustered,and random.

Question 14

With SQL Server,the preferred way to create database structures is with SQL statements.

Question 15

The SQL Server GUI program is the Enterprise Manager.

Question 16

In the Microsoft SQL Server Management Studio,the amount to add to the surrogate key value when adding a new row is specified by the increment property.

Question 17

The name TRANSACTION is so special to SQL Server that no stored procedures will work on a table with that name.

Question 18

In addition to the standard SQL Server 2008 R2 tools,SQL statements such as CREATE TABLE can now also be executed by using Microsoft's PowerShell.

Question 19

When viewing a table in the GUI tools table design window in SQL Server,the notation that should supply the values for a surrogate key is that the Identity property of that column is set to "Yes."

Question 20

When viewing a table in the GUI tools table design window in SQL Server,the primary key is indicated by a black arrow.

Question 21

SQL Server AFTER triggers may be assigned to either tables or views.

Question 22

With SQL Server,a cluster index has the data with and in the same order as the bottom level of the index.

Question 23

SQL Server AFTER triggers may be used with insert and update actions,but not delete actions.

Question 24

One means of processing an SQL Server database is to create application code in a programming language and invoke SQL Server DBMS commands from that program.

Question 25

Database views can only be created in SQL Server through the use of SQL commands.

Question 26

SQL Server automatically makes determinations of whether or not to promote or demote a lock.

Question 27

SQL Server user built-in system functions start with the @@ symbol.

Question 28

PL/SQL is a programming language for SQL Server that adds programming constructs to the SQL language.

Question 29

SQL Server supports AFTER and INSTEAD OF triggers,but not BEFORE triggers.

Question 30

With SQL Server,the clustered index is faster for data retrieval.

Question 31

When using SQL Server,developers place explicit locks.

Question 32

In SQL Server,insert and update triggers store old values in a pseudotable named deleted.

Question 33

In SQL Server,insert and update triggers store new values in a pseudotable named inserted.

Question 34

One means of processing an SQL Server database is to save groups of SQL Server DBMS commands in a text file and then process this file in the Microsoft SQL Server Management Studio.

Question 35

Data can be entered directly into a table in Enterprise Manager or via SQL INSERT statements submitted through the Microsoft SQL Server Management Studio.

Question 36

With SQL Server,only one cluster index is allowed per table.

Question 37

With SQL Server,a nonclustered index does not contain data but has pointers to the data.

Question 38

When you use the Microsoft SQL Server Management Studio to run and rerun a set of SQL Server DBMS commands stored in a text file,the stored procedure must start with CREATE PROCEDURE.

Question 39

In SQL Server,triggers can roll back the transaction that caused them to be fired.

Question 40

SQL Server user variables and parameters start with the @ symbol.

Question 41

The SQL Server default when creating a new database is to create ________ for each database.

A)one data file and one log file
B)one data file and two log files
C)two data files and one log file
D)two data files and two log files
E)there is no default the database creator must specify how many data files and log files will be created

Question 42

When recovering a database in SQL Server,it is possible to process the log to a particular point in time or to a transaction mark.

Question 43

The default transaction isolation level for SQL Server is Read Committed.

Question 44

SQL Server security roles provide a simple way to control user privileges in a database.

Question 45

SQL Server bulk-logged recovery differs from other log-based recovery methods because it does not include log entries for changes that cause large log entries.

Question 46

In SQL Server,a differential backup makes a copy of the entire database.

Question 47

In SQL Server,it is not possible to make dirty reads.

Question 48

In SQL Server,the full recovery model creates a log for every change to the database.

Question 49

The default cursor concurrency for a dynamic cursor in SQL Server is optimistic.

Question 50

Locking hints included in an SQL statement will override locking behavior based on explicit transaction isolation level statements.

Question 51

Which of the following is generally used to create a new database?

A)Enterprise Manager
B)Query Analyzer
C)Microsoft SQL Server Management Studio
D)A and B
E)A,B and C

Question 52

The SQL Server GUI interface database management program is the ________.

A)Enterprise Manager
B)Query Analyzer
C)Microsoft SQL Server Management Studio
D)A and B
E)A,B and C

Question 53

In SQL Server,SCROLL_LOCK is a version of pessimistic locking.

Question 54

SQL Server supports three recovery models: simple,complex and bulk logged.

Question 55

SQL statements can be passed to SQL Server using the ________.

A)Enterprise Manager
B)Query Analyzer
C)Microsoft SQL Server Management Studio
D)A and B
E)A,B and C

Question 56

SQL Server security provides three modes of authentication.

Question 57

SQL Server tables can be created and modified using ________.

A)Enterprise Manager
B)Query Analyzer
C)Microsoft SQL Server Management Studio
D)A and B
E)A,B and C

Question 58

In SQL Server,a interactive backup makes a copy of the changes that have been made since the last time a backup of the entire database was made.

Question 59

In SQL Server,locking behavior can be modified by providing locking hints in the WITH parameter of the FROM clause in SELECT statements.

Question 60

The recommended SQL Server security is Windows-only security.

Question 61

Which type(s)of index(s)in SQL Server has/have a bottom level that does not contain data but has pointers to the data?

A)Clustered
B)Nonclustered
C)Unique
D)baseline
E)B and D

Question 62

A stored procedure can be stored in a text file and run with the Microsoft SQL Server Management Studio.If we are running the code that creates this procedure for the third time,it must start with ________.

A)STORED PROCEDURE
B)CREATE PROCEDURE
C)ALTER PROCEDURE
D)MODIFY PROCEDURE
E)RUN PROCEDURE

Question 63

A view may not be assigned to a(n)________.

A)BEFORE trigger
B)INSTEAD OF trigger
C)CONCURRENT trigger
D)AFTER trigger
E)Any of the above.

Question 64

When creating a T-SQL statement,you should:

A)predefine it graphically in Enterprise Manager.
B)limit it to one table.
C)limit it to two tables.
D)end it with the semicolon normally used to terminate SQL statements.
E)include the word "View" in the view name.

Question 65

Which of the following is not a type of trigger supported by SQL Server?

A)BEFORE
B)INSTEAD OF
C)CONCURRENT
D)AFTER
E)A and C

Question 66

Which of the following is known to be true if the command EXEC abcCust @Cost = 5 is used to invoke a stored procedure in the Microsoft SQL Server Management Studio?

A)Cost = 5 is a CHECK constraint.
B)The name of the stored procedure is abcCust.
C)The name of the stored procedure is abc.
D)Cust is given the value 5.
E)abcCust is the name of a table or view in the database.

Question 67

What name is so special to SQL Server that no stored procedure will work on a table with that name,not even if it is enclosed in brackets?

A)NAME
B)UPDATE
C)TRANSACTION
D)SQL
E)KEY

Question 68

Which of the following are ways of processing an SQL Server database?

A)From an application written in a programming language such as C# or Visual Basic
B)From text files processed by the SQL Server Query Analyzer
C)From invoked SQL Server stored procedures
D)From invoked SQL Server triggers
E)All of the above.

Question 69

Which of the following is true about indexes in SQL Server?

A)SQL Server automatically creates indexes for columns appearing in WHERE clauses in queries.
B)SQL Server pads all indexes.
C)SQL Server supports filling up to a limit of 70 percent.
D)SQL Server automatically creates indexes for foreign keys.
E)All of the above.

Question 70

For triggers on insert and update actions,the new column values are stored in ________.

A)a table named new:TableName
B)a table named old:TableName
C)a pseudotable name inserted
D)a pseudotable named deleted
E)None of the above.

Question 71

The language available in SQL Server that adds programming constructs to the SQL language is known as ________.

A)PL/SQL
B)SQLP
C)C++
D)INTERACT-SQL
E)TRANSACT-SQL

Question 72

Which of the following is true of clustered indexes?

A)They are normally slower than nonclustered indexes for retrieving data.
B)Only one is allowed per table.
C)They are normally slower than nonclustered indexes for updating data.
D)They do not have data in the bottom level.
E)A and C

Question 73

Using only SQL Server tools,you can enter data into a table by ________.

A)entering it into a table grid in the Microsoft SQL Server Management Studio
B)using INSERT statements through the Microsoft SQL Server Management Studio
C)using an SQL Server form
D)A or B
E)A,B or C

Question 74

Anytime you want to use an SQL Server reserved word as a user identifier,enclose it in ________.

A){ }
B)[ ]
C)( )
D)" "
E)< >

Question 75

Which type of index in SQL Server is faster for data retrieval?

A)Clustered
B)Nonclustered
C)Unique
D)Baseline
E)There is no advantage to any particular index for data retrieval,only for data updating.

Question 76

The column(s)that is/are the primary key in an SQL table are indicated by ________ when viewed graphically in Enterprise Manager.

A)the black arrow symbol
B)the outline arrow symbol
C)the key symbol
D)the asterisk symbol
E)being underlined

Question 77

When creating SQL Server tables in a database,SQL statements can now also be submitted by using ________.

A)Internet Explorer
B)Microsoft PowerShell
C)Microsoft Word
D)Microsoft Excel
E)PL/SQL

Question 78

When reviewing a table in the SQL Server GUI tools,which property should be set to "Yes" to indicate that the column is a surrogate key for which SQL Server should automatically supply values?

A)Identity
B)Surrogate
C)AutoIncrement
D)AutoNumber
E)Sequence

Question 79

SQL Server user variables and parameters start with the symbol ________.

A)/*
B)#
C)##
D)@
E)@@

Question 80

SQL Server system functions start with the symbol ________.

A)/*
B)#
C)##
D)@
E)@@