SQL Server Query MCQs

Question 11: What are the characteristics of the HAVING clause?

• Must come after the GROUP BY clause
• Must come before the GROUP BY clause
• Cannot include any conditions
• Is used to filter out grouping records.

Check Answer

Question 12: Consider the Employee table below. Write a query to change the salary of an employee whose EmpID is 3 to 25000.

• Update Salary = 25000 WHERE EmpId = 3;
• UPDATE Employee SET Salary = 25000 WHERE EmpId = 3;
• UPDATE Employee SET Salary = 25000;
• INSERT Salary = 25000 WHERE EmpId = 3;

Check Answer

Question 13: Which of the following query inserts a row in the Employee table?

• INSERT INTO Employee(FirstName, LastName, EMail, PhoneNo, Salary, DeptId)
• INSERT VALUES INTO Employee(FirstName, LastName, EMail, Phone, HireDate, Salary) AS
• INSERT VALUES INTO Employee AS
• INSERT TO Employee VALUES(‘John','King','[email protected]');

Check Answer

Question 14: Write a query to disable a trigger trgEmpUpdate on the Employee table.

• DROP TRIGGER TRIGGER dbo. trgEmpUpdate
• DISABLE TRIGGER dbo. trgEmpUpdate
• DELETE TRIGGER dbo. trgEmpUpdate
• DISABLE trgEmpUpdate TRIGGER;

Check Answer

Question 15: How do you include an alias name for the EmpID column of the Employee table in a SELECT statement in SQL Server?

• SELECT EmpId AS 'Employee ID', FirstName, DeptID FROM Employee
• SELECT EmpId RENAME 'Employee ID', FirstName, DeptID FROM Employee
• SELECT 'Employee ID', FirstName, DeptID FROM Employee
• A column cannot be given an alias in T-SQL.

Check Answer

Question 16: Write a query to create a new user 'Nancy' in the HR database for Login 'David'.

• ADD USER 'Nancy' FOR LOGIN 'David';
• CREATE 'Nancy' USING LOGIN 'David';
• FOR LOGIN 'David' CREATE USER 'Nancy';
• CREATE USER 'Nancy' FOR LOGIN 'David';

Check Answer

USE <database-name>
         CREATE USER <user-name> FOR LOGIN <login-name>

Question 17: Identify the valid query to assign SELECT permission to user Nancy on the Employee table in the HR database.

• GRANT CONTROL TO Nancy FOR Employee;
• ASSIGN SELECT PERMISSION TO Nancy;
• GRANT SELECT ON Employee TO Nancy;
• ASSIGN SELECT PERMISSION ON Employee TO Nancy;

Check Answer

Question 18: Write a query to display the FirstName and LastName of the Employee table as a single column as LastName, FirstName

• SELECT LastName, FirstName FROM Employee
• SELECT LastName + ', ' + FirstName FROM Employee
• SELECT CONCAT(LastName , ', ' , FirstName) FROM Employee
• SELECT (LastName ',' FirstName) FROM Employee

Check Answer

Question 19: How do you delete a UNIQUE constraint named UNQ_Email in the Employee table using T-SQL?

• DELETE CONSTRAINT UNQ_Email FROM Employee;
• ALTER TABLE Employee DROP CONSTRAINT UNQ_Email;
• ALTER TABLE Employee DELETE CONSTRAINT UNQ_Email;
• DROP CONSTRAINT UNQ_Email FROM Employee;

Check Answer

Question 20: Using a subquery, write a query to fetch all employees (Empid, FirstName, Salary) from the Employee table having a salary less than the average salary.

• SELECT EmpId, FirstName, Salary FROM Employee WHERE Salary < AVG(Salary);
• SELECT EmpId, FirstName, Salary FROM Employee HAVING Salary < AVG(Salary);
• SELECT EmpId, FirstName, Salary FROM Employee WHERE Salary < (SELECT AVG(Salary) FROM Employee);
• SELECT EmpId, FirstName, AVG(Salary) FROM Employee HAVING AVG(Salary);

Check Answer