Deck 10: Managing Databases With SQL Server 2014

Question 1

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

Answer 1

False

Question 2

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

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 2014 provides two ways to create tables-graphically and through SQL code.

Question 5

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

Question 6

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

Question 7

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 8

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 9

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

Question 10

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

Question 11

The SQL Server GUI program is the Enterprise Manager.

Question 12

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

Question 13

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 14

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

Question 15

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 16

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

Question 17

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

Question 18

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 19

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

Question 20

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

Question 21

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

Question 22

When using SQL Server,developers place explicit locks.

Question 23

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

Question 24

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

Question 25

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 26

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 27

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

Question 28

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

Question 29

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 30

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

Question 31

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

Question 32

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

Question 33

SQL Server built-in system function names start with the @@ symbol.

Question 34

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

Question 35

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

Question 36

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

Question 37

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

Question 38

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

Question 39

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 40

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

Question 41

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

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

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

Question 44

SQL Server tables can be created and modified using ________.

A)Enterprise Manager
B)Query Analyzer
C)Microsoft SQL Server Management Studio
D)Both A and B are correct

Question 45

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

A)Enterprise Manager
B)Query Analyzer
C)Microsoft SQL Server Management Studio
D)Both A and B are correct

Question 46

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

Question 47

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

Question 48

SQL Server logins can be based on Windows Operating System security or SQL server-specific security.

Question 49

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

Question 50

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

Question 51

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

Question 52

Which of the following is true about SQL Server Import and Export Wizard?

A)SQL Server Native Client should be selected as the destination.
B)The user can't specify that the column names are in the first row.
C)There are no problems with modifying the default column attributes.
D)The user can specify the primary key to be set on the imported table.

Question 53

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

Question 54

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

Question 55

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 56

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

A)Enterprise Manager
B)Query Analyzer
C)Microsoft SQL Server Management Studio
D)Both A and B are correct

Question 57

The recommended SQL Server security is Windows-only security.

Question 58

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

Question 59

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)There is no default-the database creator must specify how many data files and log files will be created.

Question 60

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

Question 61

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

A)@@
B)#
C)##
D)@

Question 62

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 Excel
D)PL/SQL

Question 63

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.

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

Question 65

Which type(s)of index 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)Both A and B are correct

Question 66

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

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

Question 67

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)Both A and B are correct

Question 68

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

A)BEFORE trigger
B)INSTEAD OF trigger
C)CONCURRENT trigger
D)AFTER trigger

Question 69

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

Question 70

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.

Question 71

Which of the following is true about the SQL Server Import and Export Wizard?

A)The spreadsheet data must already be normalized in order to be imported.
B)There may be problems importing Excel 2013 data and we may need to install Excel 2007 compatibility software.
C)The Import and Export Wizard sometimes creates blank rows at the end of the imported table.
D)Both B and C are correct.

Question 72

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

A)BEFORE
B)INSTEAD OF
C)CONCURRENT
D)Both A and C are correct

Question 73

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)being underlined

Question 74

SQL Server system function names start with the symbol ________.

A)@@
B)#
C)##
D)@

Question 75

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

A)The name of the stored procedure is abcCust.
B)The name of the stored procedure is abc.
C)The parameter Cost is given a value of 5.
D)Both A and C are correct.

Question 76

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)RUN PROCEDURE
B)CREATE PROCEDURE
C)ALTER PROCEDURE
D)MODIFY PROCEDURE

Question 77

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

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

Question 78

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

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

Question 79

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

Question 80

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