Infosys Certified MS SQL Server Developer

Consider the following data in two table

LoanDetails:

EMIPaymentdetails:

What will be the output on executing the below code?

MERGE LoanDetails AS LD
USING EMIPaymentDetails AS EMI
ON LD.Loanid = EMI.Loanid
WHEN MATCHED THEN 
UPDATE SET LoanAmount = LoanAmount-EMI.Amountpaid
WHEN NOT MATCHED THEN 
UPDATE SET LoanAmount = LoanAmount+1500;

Which of the following statements are TRUE with respect to temporary tables? CHOOSE TWO CORRECT OPTIONS.

A company XYZ has a requirement to host database of 4 different applications on 4 different SQL Instances.

Accordingly the computer department of the organization installed 4 instances of SQL SERVER to meet the requirement on a single machine.

Identify the number of default and named instances present on the machine.

Which of the following statement is true about temporary table?

Which of the following DML operations can be performed by MERGE in SQL Server?

State whether the below mentioned statements with respect to data types are True or False?

1. NVARCHAR is used to store Unicode characters
2. TINYINT can be used to store the phone number which has 10 digits

John has created the following table in SQL Server:

CREATE TABLE Trainee (
TraineeId INT CONSTRAINT pk_TraineeId PRIMARY KEY, 
Name VARCHAR(20) NOT NULL ) 

He now wants to create a clustered index on the column Name.

Jack says, it is currently not possible as there is already a clustered index in the table.

Jill says, it is possible, only if the clustered index is created after the Primary Key Constraint is dropped.

Which of the following statements is true?

A table Student (SID, Sname, Course, Grade) where SID has a Unique Constraint is created successfully.

Tony and Steve is having the following discussion:

Tony says we can create Clustered index on column SID

Steve says we can create Non-Clustered Index on column SID

Identify whose statement is correct

Consider a Customer table created as shown below

CREATE TABLE Customer
(
     CustomerId INT CONSTRAINT pk_CustomerId PRIMARY KEY
     CustomerName VARCHAR(250) NOT NULL,
)
GO

What would be the output when the below query gets executed

Query 1: CREATE INDEX idx1 ON Customer(CustomerId)

Query 2: CREATE INDEX idx2 ON Customer(CustomerName)

What would happen when the below SQL script gets executed?

CREATE TABLE Emp
(
      EmpId INT PRIMARY KEY,
      EmpName VARCHAR(30)
)