SQL Set 3

1.

In SQL Server, which one of the following is true for an aligned index?

If a table and its indexes are aligned, MS SQL Server switches in between the partition structure of both the table and its indexes for a quick and an efficient data maintenance.

An index has to be in the same partition function if you have to align it with the base table.

The alignment in between the partition table and partition index will follow the same partition function in terms of arguments, datatypes, and the number of partitions.

1,2, and 3 1, 3 1, 2 2, 3

2.

In SQL Server, the stored procedure prcDispEmpDetails prints the details of the employees based on their employee code by using multiple base tables. Alice wants to view the names of all the tables involved in printing this report. Therefore, she issues the sp_helptext command for the mentioned procedure, but she is unable to view any details. Identify the reason for this error?

The database administrator has revoked read permissions from the prcDispEmpDetails procedure. The database administrator has revoked execute permission from the system stored procedure sp_helptext.

3.

You are managing an SQL Server which includes a database named Tester1. Which of the following will you use to keep the customer information safely?

You should add Log in your database, and encrypt the information at fixed time. By stored procedures and functions, you should make database run normally. You should make Transparent Database Encryption for the Tester1 database enable and back up the transaction log. You should make Transparent Database Encryption which is useful for the Tester1 database and the master database enable.

4.

You are managing an SQL Server which includes a database named Tester1. Which of the following will you use to keep the customer information safely?

The database administrator has revoked read permissions from the prcDispEmpDetails procedure. The database administrator has revoked execute permission from the system stored procedure sp_helptext.

5.

You are managing an SQL Server which includes a database called CustomersDB. There is a table called Product which is contained in the Sales Product. Which of the following is used to test to a new schema called Tester?

Add Transact-SQL statement: ALTER SCHEMA Tester TRANSFER Products. Test

Add Transact-SQL statement: ALTER USER Products WITH DEFAULT_SCHEMA = Tester;

Add Transact-SQL statement: ALTER AUTHORIZATION ON Products. Test TO Tester;

Add Transact-SQL statement: ALTER TABLE Products. Test SWITCH TO Tester.Table1;

6.

In an SQL Server, which of the following will ensure that the backup mode contains only the updated data since the last backup?

Data backup Differential backup Full backup Log backup

7.

Which of the following is used to develop an SQL Server Agent job which implements the stored procedure and also deletes the job after it is completed?

Transactions Log that is scheduled to run twice A job which utilizes an automatic job removal option A job which belongs to the database maintenance category An alert will be raised when the job is completed. You can call another a job if you are using this alert

8.

In an SQL Server, consider the toys table which has the following columns:
|ToyID|vToyName|CategoryID| | :-: | :-: | :-: |

Consider the category table which consists of the following columns:

CategoryID	cCategory

Which of the following displays the list of all the toy names that belong to the category Stuffed Toys?

SELECT vToyName FROM Toys WHERE cCategoryId IN (SELECT cCategoryId FROM CATEGORY WHERE cCategory = 'Stuffed Toys')

SELECT vToyName FROM Toys WHERE cCategoryId = (SELECT cCategoryId FROM CATEGORY WHERE cCategory = 'Stuffed Toys')

SELECT vToyName FROM Toys WHERE cCategoryId exists (SELECT cCategoryId FROM CATEGORY WHERE cCategory = 'Stuffed Toys')

SELECT vToyName FROM Toys WHERE cCategoryId IN (SELECT cCategory FROM CATEGORY WHERE cCategory = 'Stuffed Toys')

9.

Consider the table Shipment which consists of the following columns:

cOrderNo	OrderName	dShipmentDate

In SQL Server, which of the following queries is used to retrieve the Order Name, Shipment Date, Actual Delivery Date, and Days in Transit for every order?

SELECT 'Order Number' = cOrderNo, 'Shipment Date' = dShipmentDate, 'Actual Delivery Date' = dActualDeliveryDate, 'Days in Transit' = datediff (dd, dShipmentDate, dActualDeliveryDate) FROM Shipment

SELECT 'Order Number' = cOrderNo, 'Shipment Date' = dShipmentDate, 'Actual Delivery Date' = dActualDeliveryDate, 'Days in Transit' = datediff (dShipmentDate, dActualDeliveryDate) FROM Shipment

SELECT 'Order Number' = cOrderNo, 'Shipment Date' = dShipmentDate, 'Actual Delivery Date' = dActualDeliveryDate, 'Days in Transit' = datepart (dd, dShipmentDate, dActualDeliveryDate) FROM Shipment

SELECT 'Order Number' = cOrderNo, 'Shipment Date' = dShipmentDate, 'Actual Delivery Date' = dActualDeliveryDate, 'Days in Transit' = datename (dd, dShipmentDate, dActualDeliveryDate) FROM Shipment

10.

A schedule for interviews is required with the following information that has to be printed in alphabetical order of candidates name:

Candidate Name

Interviewer

Date

Week Day

The following is the structure of the original table ExternalCandidate:

ID

vFirstName

vLastName

cInterviewer

dInterviewdate

In an SQL Server, which of the following queries is used to accomplish the task?

SELECT 'Candidate Name' = vFirstName, 'Interviewer'=cInterviewer, 'Date'=datepart(dd,dInterviewDate), 'Week Day'=datename(dw, dInterviewdate) FROM ExternalCandidate Order by vFirstName

SELECT 'Candidate Name' = vFirstName, 'Interviewer'=cInterviewer, 'Date'=datepart(dd,dInterviewDate), 'Week Day'=datename(dw, dInterviewdate) FROM ExternalCandidate

SELECT 'Candidate Name' = vFirstName, 'Interviewer'=cInterviewer, 'Date'=datepart(dInterviewDate), 'Week Day'=datename(dInterviewdate) FROM ExternalCandidate Order by vFirstName

SELECT 'Candidate Name' = vFirstName, 'Interviewer'=cInterviewer, 'Date'=datepart(dd,dInterviewDate), 'Week Day'=datename(dw, dInterviewdate) FROM ExternalCandidate Order by vFirstName desc

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner

SQL Set 3

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

Detail Form

COMPANY

Products

OTHERS

Partner