Search This Blog

Lets go..

Monday

SQL exam 70-461 questions and answers part 6


MS-SQL exam 70-461 questions and answers dumps
QUESTION 61
As part of a new enterprise project, you're designing a new table to store financial transactions. This table could eventually store millions of rows and so storage space is very important.
One of the columns in the table will store either a 1 or 0 value.
Which data type would be most appropriate?
A. bit
B. tinyint
C. numeric
D. float

QUESTION 62
You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?
A. Float
B. Money
C. Smallmoney
D. Numeric

Correct Answer:  





QUESTION 63
which of the following conversions is NOT allowed either implicitly or explicitly using CONVERT OR CAST?
A. varchar to decimal
B. decimal to bit
C. date to varchar
D. datetime to text

QUESTION 64
Your manager has asked you to design a new table and enforce rules for the type of information that can be stored in a particular column. The information must match a particular pattern e.g. a number between 1 and 7. This checking must be performed during insert or update operations.
Which parameter should you use when you create the column?
A. CHECK
B. DEFAULT
C. Foreign Key
D. Primary Key
Answer: A

QUESTION 65
Complete the missing word below:
A ___ is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table.
A. Constraint
B. DLL
C. View
D. Trigger
Answer: 
QUESTION 66
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline.
Which Transact-SQL batch should you use?

A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP
EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Correct Answer:  

QUESTION 67
Your manager has asked you to check the index stats for a particular table called Address. You have executed the following query (see below) and it reported that the avg_fragmentation_in_percent is 15%. What should you do?
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'Test'), OBJECT_ID(N'Address'), NULL, NULL , 'DETAILED');
A. Run ALTER INDEX REBUILD WITH (ONLINE = ON)
B. Run ALTER INDEX REORGANIZE
C. Rebuild the table
D. Nothing, the index fragmentation is at a safe level
Answer:  

QUESTION 68
You need to write a new query that will return names from the employee table for people who are not part of the contractors table (the two tables share a unique id, similar to social security or NI number). Your query will use a sub query to check that the employee is not in the contractors table. How might you achieve this?
A. Use COALESCE
B. Use EXISTS
C. Use IS_MEMBER
D. Use EXP
Answer:  
QUESTION 69
You need to delete a stored procedure from the SQL Server 2012 database. The stored procedure is called MyProc. Which statement below is correct?

A. drop MyProc
B. delete procedure MyProc
C. delete MyProc
D. drop procedure MyProc
Answer:  

QUESTION 70
As part of a new HR project you're creating several stored procedures that will add logging information to the logs table. This logging information is very detailed and should contain carriage returns to make paragraphs more readable. How can you add carriage returns to text when inserting into a table?
A. Use CASE
B. Use CHAR
C. Use TEXTPTR
D. Use COS
Answer: B

61
62
63
64
65
66
67
68
69
70
A
D
D
A
D
B
B
B
D
B


QUESTION 71
SQL Server 2012 uses the standard four-part naming convention when referencing database objects. Which of the following is the correct example of the four-part naming?
A. server_name.schema_name.database_name.object_name
B. schema_name.object_name.server_name.database_name
C. object_name.server_name.schema_name.database_name
D. server_name.database_name.schema_name.object_name
Answer:



QUESTION 72
You're designing a new SQL Server 2012 query for the HR department. The query will find records from the persons table for people whose name starts with 'Ja'.
Which WHERE statement would be the correct choice?
A. where (name like 'Ja%')
B. where (name = 'Ja%')
C. where (name > 'Ja')
D. where (name like '%Ja%')
Answer:

QUESTION 73
You've created a standard stored procedure that's not a CLR procedure. This procedure has several parameters, some of which are output parameters.
Which of the following CANNOT be passed back as an output parameter from a stored procedure?
A. Text
B. image
C. Ntext
D. Any of these
Answer:

QUESTION 74
You're designing a new SQL Server 2012 table that will hold millions of records. The table will be used by a finance application to show historical transactions. The table only has 10 columns but it's vital that the table performs well.
How many primary keys should you create for the table for best performance?
A. 2
B. 10
C. 3
D. 1
Answer:

QUESTION 75
Your manager has asked you to design a new table and enforce rules for the type of information that can be stored in a particular column. The information must match a particular pattern e.g. a number between 1 and 7. This checking must be performed during insert or update operations. Which parameter should you use when you create the column?
A. CHECK
B. DEFAULT
C. Foreign Key
D. Primary Key
Answer:

QUESTION 76
What is missing from the T-SQL statement below for creating and using a cursor?
DECLARE mycursor CURSOR FOR SELECT * FROM Vendor FETCH NEXT FROM mycursor;

A. KEYSET
B. OPEN
C. STATIC
D. SCROLL
Answer
QUESTION 77
Which of the following is an example of a scalar subquery?

A. select max(price) from products
B. select sum(price) from products
C. select min(price) from products
D. All of these
Answer:
QUESTION 78
If you use the BEGIN TRANSACTION statement, then execute 2 insert statements and 2 delete statements what will happen if you execute ROLLBACK TRANSACTION?
A. Only the INSERT statements will be rolled back
B. All of the INSERTS and DELETES will be rolled back
C. Only the last INSERT and last DELETE statement will be rolled back
D. Only the DELETE statements will be rolled back
Answer:  

QUESTION 79
You're designing a new table that will hold information about medical records. Another table called documents contains a foreign key that references this table. To make sure information is cleaned up when a delete operation is performed, you've added an ON DELETE CASCADE statement to the foreign key relationship.
What will happen when a row is deleted from the medical records table?
A. Corresponding rows in the referenced table (documents) will be deleted
B. All the values that make up the foreign key are set to NULL in the documents table
C. All the values that comprise the foreign key are set to their default values in the documents table
D. Rows are deleted from the medical records table but the documents table is unchanged
Answer:  

QUESTION 80
What is wrong with the T-SQL batch statement below:
@find varchar(30);
SET @find = 'Man';
SELECT LastName FROM Person WHERE LastName = @find;
A. You can't use the @find parameter in a query
B. DECLARE is missing
C. Nothing, it's fine
D. You should use SELECT instead of SET
Answers
71
72
73
74
75
76
77
78
79
80
D
A
D
D
A
B
D
B
A
B

SQL exam 70-461 questions and answers part 5



MS-SQL exam 70-461 questions and answers dumps
QUESTION 41
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Ÿ Future modifications to the table definition will not affect the applications' ability to access data.
Ÿ The new object can accommodate data retrieval and data modification.
Ÿ You need to achieve this goal by using the minimum amount of changes to the applications. What should you create for each application?


A. Synonyms
B. Common table expressions
C. Views
D. Temporary tables

 
 

QUESTION 42
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Ÿ Future modifications to the table definition will not affect the applications' ability to access data.
Ÿ The new object can accommodate data retrieval and data modification.
Ÿ You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application?


A. views
B. table partitions
C. table-valued functions
D. stored procedures
 
QUESTION 43
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
1.       Returns a result set based on supplied parameters.
2.       Enables the returned result set to perform a join with a table.

Which object should you use?

A. Inline user-defined function
B. Stored procedure
C. Table-valued user-defined function
D. Scalar user-defined function

Correct Answer: Below


QUESTION 44
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
Ÿ Status information must be logged to a status table.
Ÿ If the status table does not exist at the beginning of the batch, it must be created.

Which object should you use?

A. Scalar user-defined function
B. Inline user-defined function
C. Table-valued user-defined function
D. Stored procedure

Correct Answer: Below

QUESTION 45
You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
Ÿ Takes an input variable
Ÿ Returns a table of values
Ÿ Cannot be referenced within a view

Which object should you use?

A. Scalar-valued function
B. Inline function
C. User-defined data type
D. Stored procedure

Correct Answer: Below

QUESTION 46
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a unique constraint?

A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID

Correct Answer: Below

QUESTION 47
Confidential information about the employees is stored in a separate table named EmployeeData.
One record exists within EmployeeData for each record in the Employee table. You need to assign
the appropriate constraints and table properties to ensure data integrity and visibility. On which
column in the Employee table should you use an identity specification to include a seed of 1,000
and an increment of 1?

A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID

Correct Answer: Below


QUESTION 48
Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you create a Primary Key constraint for this table?


A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID

Correct Answer: Below

QUESTION 49
Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database?

A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID

Correct Answer: Below


QUESTION 50
Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData.One record exists within EmployeeData for each record in the Employee table.

You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you a create a self-reference foreign key constraint?

A. DateHired
B. DepartmentID
C. EmployeelD
D. EmployeeNum
E. FirstName
F. JobTitle
G. LastName
H. MiddleName
I. ReportsToID

Correct Answer:  
41
42
43
44
45
46
47
48
49
50
C
A
C
D
D
D
C
C
C
I


Hints:
Unique constraint
                EmployeeNum

self-reference foreign key constraint
ReportsToID

Primary Key constraint for this table?
Or
a seed of 1,000 and an increment of 1
or
Foreign Key constraint that references a different table
                EmployeelD