SQL Server Quiz Questions And Answers
Have you ever worked on an SQL server? What do you know about this database system? We have here this "SQL server quiz questions and answers" for you. Microsoft SQL Server is a DBMS developed by Microsoft for storing and retrieving data. In this SQL quiz, we will test your knowledge and understanding of various SQL concepts and terminologies. You can easily crack this quiz if you consider yourself a true database administrator. Want to check your memory for the same? Take this test, then!
- 1.Assuming UserProfile is a table containing a column Proession which accepts NULL value. What is the result of the below query. SET ANSI_NULLS OFF SELECT Profession FROM UserProfile WHERE (Profession <> NULL)
- A.
Gives an error.
- B.
Returns all Professions which has not null values.
- C.
<> is not an operator in SQL Server.
- D.
None of the above.
- 2.What is the result of the below query: SELECT SUBSTRING('DOTNET', 1, 3) AS 'Substring'
- A.
DOT
- B.
OTN
- C.
OT
- D.
None of the above.
- 3.Which of the following is true about the SERIALIZABLE isolation level ?
- A.
It protects against phantom reads.
- B.
It causes the highest level of contention.
- C.
It causes the highest level of blocking.
- D.
All of the above.
- 4.What is the result of the below query: SELECT PATINDEX('DOT%', 'DOTNET') AS 'Index'
- A.
0
- B.
1
- C.
-1
- D.
2
- 5.Assuming Emp,EmpCompress 2 tables with same schema: EmpId(Int), Name(varchar(50)), DeptId(Int). If a company wants to move all rows for Employees working in IT Department, from the Employees table to the EmployeesArchive table. Which of the following queries can be used?
- A.
DELETE FROM EMP OUTPUT deleted.* INTO EmpCompress FROM Emp JOIN deptON EMP.DeptId = Dept.DeptId WHERE DeptName='IT';
- B.
DELETE FROM EMP OUTPUT deleted.* INTO EmpCompress FROM Emp JOIN Dept ON EMP.DeptId = Dept.DeptId ;
- C.
DELETE FROM EMP OUT PUT deleted.* INTO EmpCompress FROM Emp JOIN deptON EMP.DeptId = Dept.DeptId WHERE DeptName='IT';
- D.
None of the above.
- 6.The SELECT statement must include which of the following clause:
- A.
SELECT clause
- B.
FROM clause
- C.
Both A and B.
- D.
None of the above.
- 7.Which of the following is true?
- A.
BUILT-IN functions in SQL Server are Nondeterministic.
- B.
ISNULL function in SQL Server is Deterministic.
- C.
All of the above.
- D.
None of the above.
- 8.Which of the following is true?
- A.
NULL values are ignored for all the aggregate functions.
- B.
NULL values are included for all the aggregate functions.
- C.
When an aggregate function is included in the SELECT clause, all other expressions in the SELECT clause must either be aggregate functions or included in a GROUP BY clause.
- D.
Both A and C.
- E.
Both B and C.
- 9.Assuming UserProfile is a table with PKUserId int, Profession Varchar(50)columns. What is the result of the below query: SELECT PKUserId,Profession FROM UserProfile WHERE Profession = 'Engineer' AND (PKUserId > 12 OR PKUserId = 1)
- A.
Displays Users with Profession as 'engineer' with PKUserId > 12 as well as the users with PKUserId of 1.
- B.
Displays Users with Profession as 'engineer' with PKUserId > 12 only.
- C.
Displays Users with Profession as 'engineer' with PKUserId of 1 only.
- D.
None of the above.
- 10.BEGIN TRANSACTION INSERT INTO DemoTable VALUES(5,'XYZ','DEMO'); BEGIN TRANSACTION UPDATE DemoTable SET Col3 = 'Test' WHERE TestID = 5; COMMIT TRANSACTION; ROLLBACK; What is the result of the above code execution?
- A.
The data is rolled back to the transaction starting on line1.
- B.
The inserted row does not exist in the table.
- C.
All of the above.
- D.
None of the above.
- E.
Only B.
- 11.Assuming Emp table with EmpId(int), Name(varchar(100)),HireDate(DateTime),Designation(varchar(20)) columns and Audit table with currentDate(DateTime), EmpId columns. What is the result of the following query: INSERT INTO Emp(Name,HireDate,Designation) OUTPUT getdate(), inserted.EmpId INTO Audit VALUES ('Robot','1/1/2011','Executive');
- A.
Only inserts a new record into the Emp table.
- B.
OUTPUT clause cannot be used in the INSERT command.
- C.
Adds a new row to the Emp table, and also adds a corresponding row with the current date and time and the EmpId for the new employee into the Audit table.
- D.
None of the above.
- 12.How many transactions at a time can obtain an update lock on a resource?
- A.
1
- B.
2
- C.
3
- D.
128
- 13.Which of the following queries returns the users whose username starts with any of the character between v to z?
- A.
SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[vz]%')
- B.
SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z]%')
- C.
SELECT PKUserId, UserName FROM UserProfile WHERE (UserName LIKE '[v-z%]')
- D.
None of the above.
- 14.Which of the following options can be used for viewing lock status within your computer running SQL Server?
- A.
We can use SQL Profiler to capture lock and blocking information.
- B.
We can use the System Monitor that is part of the performance console to capture statistics on lock wait times, locks per second, and so on.
- C.
We can use the Activity Monitor in SSMS to see information on blocking processes.
- D.
We can use the sys.dm_tran_locks dynamic management view to gather information on locks being held by transactions.
- E.
All of the above.
- 15.Which of the following commands is used to retrieve information about the information contained in the current transaction log ?
- A.
DBCC LOGGING statement
- B.
DBC LOG statement
- C.
DBCC LOG statement
- D.
None of the above.
- 16.The _____ object is used to retrieve information about all active transactions on an instance.
- A.
Sys.dm_active_transactions
- B.
Sys.dm_tran_transactions
- C.
Sys.dm_tran_active_transactions
- D.
None of the above
- 17.By default, what type of index is created for a unique constraint?
- A.
Clustered index
- B.
Non-Clustered index
- C.
Both A and B
- D.
None of the above.
- 18.To rollback a portion of a transaction, We have to define ___.
- A.
Savepoints
- B.
Checkpoints
- C.
Transactions
- D.
None of the above.
- 19.Assuming the following query is executed on 31st Dec 2011. What is the result of the below query. SELECT CONVERT(varchar(30), GETDATE(), 111) AS Expr1
- A.
11/12/31
- B.
31/12/11
- C.
31/12/2011
- D.
2011/12/31
- 20.Which of the following is a best practice to reduce deadlock situations?
- A.
Access resources in the same order whenever possible within transactions
- B.
Collect and verify input data from users before opening a transaction
- C.
Keep transactions long.
- D.
All of the above.
- E.
Only A and B.
- 21.What is the default join type used when only the keyword JOIN is specified?
- A.
LEFT OUTER JOIN
- B.
RIGHT OUTER JOIN
- C.
SELF JOIN
- D.
INNER JOIN
- 22.What is the output of the below query: SELECT CHARINDEX('DOT%', 'DOTNET') AS 'CharIndex'
- A.
1
- B.
0
- C.
2
- D.
-1
- 23.Which of the following is correct?
- A.
The DELETE statement logs information on each row deleted.
- B.
The TRUNCATE TABLE statement logs information on each row deleted.
- C.
The TRUNCATE TABLE statement executes fast and requires fewer resources on the server.
- D.
All of the above.
- E.
Only A and C.
- 24.Assuming UserProfile is a table containing a column Profession which accepts NULL value. What is the result of the below query. SET ANSI_NULLS ON SELECT Profession FROM UserProfile WHERE (Profession <> NULL)
- A.
Returns records which has values in profession column
- B.
Gives an error.
- C.
Does not retrieve any records even if profession column contains values.
- D.
None of the above.
- 25.If the following code was run on 31st Dec 2011, What is the result of the below query: SELECT DATEDIFF(YEAR, GETDATE(), '8/8/2003') AS 'Difference'
- A.
-8
- B.
8
- C.
0
- D.
-1
Featured Quizzes
Popular Topics
Back to top