SQL Server Set 3

1.

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

2.

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

3.

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

4.

Consider the Toys table which has the following columns:

ToyID	vToyName	mToyRate

In an SQL Server, which of the following queries is used to select the following:

Maximum toy rate
Minimum toy rate
Average toy rate in the same query

SELECT 'Maximum'=MAX (mToyRate), 'Minimum'=MIN (mToyRate) FROM Toys

SELECT 'Maximum'=MAX (mToyRate), 'Minimum'=MIN (mToyRate), 'Average'=AVG (mToyRate)FROM Toys

SELECT 'Maximum'=MAX (mToyRate), 'Minimum'=MIN (mToyRate), 'Average'=AVG (mToyRate)FROM Toys Group by MAX (mToyRate), MIN(mToyRate), AVG (mToyRate)

SELECT 'Minimum'=MIN(mToyRate), 'Average'=AVG (mToyRate)FROM Toys

5.

You are managing a SQL database server which includes a database called TestEmpDB for the ABCCompany.com. The listed stored procedure is included in TestEmpDB.

11 CREATE PROCEDURE Emp.TestProcedure
12 AS
13 IF OBJECT_ID(' Employee. TableTest1') IS NOT NULL
14 DROP TABLE Employee.TableTest1;
15
16 CREATE TABLE Employee. TableTest1
17 (Id int PRIMARY KEY CLUSTERED,
18 Name varchar(100);
19 );
20
21 ...
22 GO

The listed exception is displayed when a user called Clerk tries to call Emp.TestProcedure

"Msg 262, Level 14, State 1, Procedure Emp.TestProcedure, Line 15 CREATE TABLE permission permitted in database ' TestEmpDB '."

In order to solve the problem, which is the correct answer?

You should add the ALTER permission on the Products schema to Clerk. You should modify the CREATE TABLE permission You should add the WITH EXECUTE AS 'dbo' clause between lines 11 and 12. You should add the WITH EXECUTE AS 'dbo' clause between lines 13 and 14.

6.

In an SQL Server, consider the following trigger on the sales table:

Create trigger trgSalesDel
On DB_Sales
For delete
As
Rollback transaction

What is the output of the following command:

Delete DB_Sales
Where datepart(yy,tran_dt) < 2000

Sales details for the items sold in the year 2000 will be deleted from the sales table. No records will be deleted from the sales table. The sales details for the items sold in and before the year 2000 will be deleted from the sales table. Sales details of items sold before the year 2000 will be deleted from the sales table.

7.

You are a SQL DBA developing a database for your company. Your peer John runs an order entry application that requires select, update, and insert permissions on the products tables to work. Access to the other tables in the database should not be restricted. He should not have any select, insert, or update permissions when accessing the table directly. What needs to be done in the described scenario?

Create one group account that denies select, insert, or update permissions on the products table. Add John as a member of that group. Create one user account that denies select, insert, or update permissions on the products table. Add John as a member of that account. Create one user-database role that denies select, insert, or update permissions on the products table. Add John as a member of that database role. Then create an application role with select, insert, and update permissions on the products tables. Create one user-database role that denies select, insert, or update permissions on the entire order entry database. Add Peter as a member of that database role, then create an application role with select, insert, and update permissions on the products tables.

8.

In an SQL Server, which of the following queries will you use to get the list of all the employees working in any department along with all those who are not assigned to any department?

EMP_ID	User_name	F_Name	L_name	Dep_ID
1	AAlice	Alice	Smith	1
2	BBob	Bob	Jones	1
3	BBen	Ben	Johnson	2
4	MMike	Mike	White	2
5	AAnita	Anita	Williams	3
6	LLisa	Lisa	Davis	3
7	MMary	Mary	Brown	4
8	JJohn	John	Wilson	4

Dep_ID	Dep_name
1	Technical Innovations
2	Technical Support
3	Operations
4	Customer Delight

SELECT * FROM EmployeeTbl e1 LEFT OUTER JOIN DepartmentsTbl e2 ON e1.Dep_ID = e2.Dep_ID

SELECT * FROM EmployeeTbl e1 RIGHT OUTER JOIN DepartmentsTbl e2 ON e1.Dep_ID = e2.Dep_ID

SELECT * FROM EmployeeTbl e1 CROSS JOIN DepartmentsTbl e2 ON e1.Dep_ID = e2.Dep_ID

SELECT * FROM EmployeeTbl e1 FULL OUTER JOIN DepartmentsTbl e2 ON e1.Dep_ID = e2.Dep_ID

9.

In an SQL Server, triggers can be nested up to___________ levels in SQL.

32 10 7 20

10.

In an SQL Server, you have to create a table called Employee2017. A user-defined datatype called typemployeecode needs to be created for the cEmployeeID attribute of the Employee2017 table.

Which of the following actions will you take to use the datatype in the Employee2017 table?

Use the sp_bindefault command to bind the datatype to the cEmployeeID attribute of the employee table Use the sp_bindrule command to bind the datatype to any attribute of the table Use the sp_addtype command to add the datatype to the employee table and use this datatype in the create table statement of the employee table Use the sp_addtype command to add the datatype to the employee table

SQL Server 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

COMPANY

Products

OTHERS

Partner

SQL Server 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