SQL Server Query Questions & Answers

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

Answer: D

A new database user can be created using SSMS and T SQL.

The syntax for creating a new user in T SQL is:

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

Visit Create New Users for more information.

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;

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

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;

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

SQL Server Query MCQs