SQL Server Multiple Choice Questions

Test your SQL Server knowledge with these comprehensive multiple-choice questions. Hover over any question to reveal the correct answer.

Jump to Section:

1. Introduction 2. Setup 3. DB Operations 4. Basic Queries 5. DML 6. Advanced Filtering 7. Multiple Tables 8. Aggregation 9. Subqueries 10. Views/Indexes 11. Functions 12. Transactions 13. Stored Procedures

1. Introduction to SQL

1. What does SQL stand for? Easy
A. Structured Query Language
B. Simple Query Language
C. Standard Query Language
D. Sequential Query Language
Answer: A. Structured Query Language
2. Which of these is NOT a SQL sublanguage? Easy
A. DDL
B. DML
C. DCL
D. DTL
Answer: D. DTL
3. Which SQL command is used to define database structures? Medium
A. Data Manipulation Language
B. Data Definition Language
C. Data Control Language
D. Data Transaction Language
Answer: B. Data Definition Language
4. What was the original purpose of SQL? Medium
A. To manipulate data in hierarchical databases
B. To provide an interface for relational databases
C. To create object-oriented databases
D. To encrypt database communications
Answer: B. To provide an interface for relational databases
5. Which organization first standardized SQL? Hard
A. IEEE
B. ANSI
C. ISO
D. W3C
Answer: B. ANSI

2. Setting Up SQL Server Environment

6. Which tool is the primary GUI for SQL Server administration? Easy
A. SQL Server Management Studio (SSMS)
B. SQL Server Configuration Manager
C. SQL Server Data Tools
D. SQL Server Profiler
Answer: A. SQL Server Management Studio (SSMS)
7. What is the default port for SQL Server? Easy
A. 1433
B. 1521
C. 3306
D. 5432
Answer: A. 1433
8. Which command displays version information in SQL Server? Medium
A. SHOW VERSION;
B. SELECT @@VERSION;
C. DISPLAY VERSION;
D. GET VERSION();
Answer: B. SELECT @@VERSION;
9. What is the SQL Server equivalent of MySQL's LIMIT clause? Medium
A. TOP
B. LIMIT
C. FIRST
D. OFFSET-FETCH
Answer: A. TOP (or D. OFFSET-FETCH for more complex scenarios)
10. Which authentication mode is more secure in SQL Server? Hard
A. Windows Authentication
B. SQL Server Authentication
C. Mixed Mode
D. They are equally secure
Answer: A. Windows Authentication

3. Database Operations

11. Which command creates a new database in SQL Server? Easy
A. ADD DATABASE
B. CREATE DATABASE
C. NEW DATABASE
D. MAKE DATABASE
Answer: B. CREATE DATABASE
12. How do you list all databases in SQL Server? Easy
A. LIST DATABASES;
B. SELECT name FROM sys.databases;
C. SHOW DATABASES;
D. GET DATABASES;
Answer: B. SELECT name FROM sys.databases;
13. Which command selects a database to work with in SQL Server? Medium
A. USE database_name;
B. SELECT database_name;
C. CONNECT database_name;
D. OPEN database_name;
Answer: A. USE database_name;
14. What does the DROP DATABASE command do in SQL Server? Medium
A. Backs up a database
B. Deletes a database permanently
C. Renames a database
D. Compresses a database
Answer: B. Deletes a database permanently
15. Which function displays the currently selected database in SQL Server? Hard
A. SHOW DATABASE();
B. SELECT DB_NAME();
C. CURRENT_DATABASE();
D. ACTIVE_DATABASE();
Answer: B. SELECT DB_NAME();

4. Basic SQL Queries

16. Which SQL keyword retrieves data from a database? Easy
A. GET
B. SELECT
C. EXTRACT
D. FETCH
Answer: B. SELECT
17. Which clause filters records in a SELECT statement? Easy
A. FILTER BY
B. WHERE
C. HAVING
D. CONDITION
Answer: B. WHERE
18. What does the asterisk (*) represent in a SELECT statement? Medium
A. All columns in the table
B. Primary key only
C. All numeric columns
D. All indexed columns
Answer: A. All columns in the table
19. Which keyword eliminates duplicate rows from the result? Medium
A. UNIQUE
B. DISTINCT
C. DIFFERENT
D. NOCOPY
Answer: B. DISTINCT
20. What is the purpose of the ORDER BY clause? Hard
A. To filter records
B. To group records
C. To sort the result set
D. To limit the number of rows returned
Answer: C. To sort the result set

5. Modifying Data (DML)

21. Which statement inserts new data into a table? Easy
A. ADD RECORD
B. INSERT INTO
C. CREATE ROW
D. NEW ENTRY
Answer: B. INSERT INTO
22. Which statement modifies existing records? Easy
A. MODIFY
B. ALTER
C. UPDATE
D. CHANGE
Answer: C. UPDATE
23. Which statement removes records from a table? Medium
A. REMOVE
B. DELETE
C. DROP
D. ERASE
Answer: B. DELETE
24. What is the difference between DELETE and TRUNCATE in SQL Server? Medium
A. DELETE is faster than TRUNCATE
B. TRUNCATE can be rolled back
C. DELETE can use a WHERE clause
D. TRUNCATE resets identity values
Answer: C. DELETE can use a WHERE clause (and D is also correct in SQL Server)
25. Which clause is used with UPDATE to specify which records to modify? Hard
A. FILTER
B. WHERE
C. HAVING
D. CONDITION
Answer: B. WHERE

6. Advanced Filtering

26. Which operator searches for a specified pattern in a column? Easy
A. LIKE
B. MATCH
C. SIMILAR TO
D. CONTAINS
Answer: A. LIKE
27. Which wildcard represents any single character in a LIKE clause? Medium
A. %
B. _
C. *
D. ?
Answer: B. _ (underscore)
28. Which operator is used to combine multiple conditions in a WHERE clause? Easy
A. AND
B. PLUS
C. ALSO
D. WITH
Answer: A. AND
29. What does the BETWEEN operator do? Medium
A. Checks if a value is within a range
B. Compares two tables
C. Joins multiple conditions
D. Creates a temporary table
Answer: A. Checks if a value is within a range
30. Which clause is used to filter groups after GROUP BY is applied? Hard
A. WHERE
B. FILTER
C. HAVING
D. GROUP FILTER
Answer: C. HAVING

7. Working with Multiple Tables

31. Which join returns all rows when there is a match in either table? Medium
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. FULL OUTER JOIN
Answer: D. FULL OUTER JOIN
32. What is the purpose of a foreign key? Medium
A. To uniquely identify each record
B. To create a relationship between tables
C. To speed up queries
D. To encrypt data
Answer: B. To create a relationship between tables
33. Which join returns only matching rows from both tables? Easy
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. FULL JOIN
Answer: A. INNER JOIN
34. What is a self-join? Hard
A. Joining a table to itself
B. Joining without any condition
C. Joining multiple tables at once
D. Joining temporary tables
Answer: A. Joining a table to itself
35. Which join returns all rows from the left table and matched rows from the right table? Medium
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. CROSS JOIN
Answer: B. LEFT JOIN

8. Aggregation and Grouping

36. Which function returns the average value of a numeric column? Easy
A. MEAN()
B. AVG()
C. AVERAGE()
D. MEDIAN()
Answer: B. AVG()
37. What is the difference between WHERE and HAVING clauses? Hard
A. WHERE filters rows before grouping, HAVING filters after
B. HAVING works with aggregates, WHERE doesn't
C. WHERE can be used in any SELECT, HAVING only with GROUP BY
D. All of the above
Answer: D. All of the above
38. Which function counts the number of rows in a result set? Easy
A. SUM()
B. COUNT()
C. TOTAL()
D. NUMBER()
Answer: B. COUNT()
39. What does GROUP BY do? Medium
A. Filters rows before aggregation
B. Groups rows by column values
C. Sorts the result set
D. Joins multiple tables
Answer: B. Groups rows by column values
40. Which function returns the highest value in a column? Easy
A. TOP()
B. MAX()
C. HIGHEST()
D. PEAK()
Answer: B. MAX()

9. Subqueries

41. What is a subquery in SQL? Medium
A. A query that runs after the main query
B. A query nested inside another query
C. A backup query
D. A query that modifies tables
Answer: B. A query nested inside another query
42. Which operator compares a value to a list from a subquery? Medium
A. EQUAL
B. IN
C. LIKE
D. MATCH
Answer: B. IN
43. What is a correlated subquery? Hard
A. A subquery that references the outer query
B. A subquery that joins multiple tables
C. A subquery with aggregate functions
D. A subquery that runs in parallel
Answer: A. A subquery that references the outer query
44. Which clause can contain a subquery in a SELECT statement? Medium
A. SELECT
B. FROM
C. WHERE
D. All of the above
Answer: D. All of the above
45. What is the purpose of EXISTS with subqueries? Hard
A. To check if a subquery returns any rows
B. To count rows in a subquery
C. To join subquery results
D. To sort subquery results
Answer: A. To check if a subquery returns any rows

10. Views and Indexes

46. What is a view in SQL? Medium
A. A physical table
B. A virtual table based on a SELECT
C. A graphical representation
D. A stored procedure
Answer: B. A virtual table based on a SELECT
47. What is the main purpose of an index? Medium
A. To enforce integrity
B. To improve query performance
C. To provide backup
D. To encrypt data
Answer: B. To improve query performance
48. Which statement creates a view? Easy
A. MAKE VIEW
B. CREATE VIEW
C. NEW VIEW
D. DEFINE VIEW
Answer: B. CREATE VIEW
49. What is a clustered index in SQL Server? Hard
A. An index that determines the physical order of data
B. An index on multiple columns
C. An index that covers all columns
D. An index with duplicate values
Answer: A. An index that determines the physical order of data
50. Which statement creates an index? Medium
A. MAKE INDEX
B. CREATE INDEX
C. NEW INDEX
D. ADD INDEX
Answer: B. CREATE INDEX

11. Basic SQL Functions

51. Which function returns the current date and time in SQL Server? Easy
A. CURRENT_TIMESTAMP
B. GETDATE()
C. SYSDATETIME()
D. All of the above
Answer: D. All of the above (All work in SQL Server)
52. Which function is used to concatenate strings in SQL Server? Easy
A. CONCAT()
B. + operator
C. Both A and B
D. JOIN()
Answer: C. Both A and B (CONCAT() and + both work in SQL Server)
53. Which function converts a string to uppercase in SQL Server? Easy
A. UPPER()
B. UCASE()
C. TO_UPPER()
D. Both A and B
Answer: A. UPPER() (UCASE() is not valid in SQL Server)
54. What does the ISNULL() function do in SQL Server? Medium
A. Checks if a value is NULL
B. Replaces NULL with a specified value
C. Counts the number of NULL values
D. Converts data types
Answer: B. Replaces NULL with a specified value
55. Which function extracts a portion of a string in SQL Server? Medium
A. EXTRACT()
B. SUBSTRING()
C. SLICE()
D. PART()
Answer: B. SUBSTRING()

12. Transactions and ACID

56. Which SQL statement starts a transaction in SQL Server? Medium
A. BEGIN TRANSACTION
B. BEGIN TRAN
C. Both A and B
D. START TRANSACTION
Answer: C. Both A and B (Both are valid in SQL Server)
57. What does the "A" in ACID stand for? Medium
A. Availability
B. Atomicity
C. Accuracy
D. Authentication
Answer: B. Atomicity
58. Which statement permanently saves a transaction's changes? Easy
A. SAVE
B. COMMIT
C. END
D. FINISH
Answer: B. COMMIT
59. What does the "I" in ACID represent? Medium
A. Integration
B. Isolation
C. Inclusion
D. Inspection
Answer: B. Isolation
60. Which statement undoes a transaction's changes? Easy
A. UNDO
B. ROLLBACK
C. REVERT
D. CANCEL
Answer: B. ROLLBACK

13. Stored Procedures

61. What is a stored procedure? Medium
A. A precompiled collection of SQL statements
B. A backup of database procedures
C. A special type of table
D. A graphical interface
Answer: A. A precompiled collection of SQL statements
62. Which statement creates a stored procedure in SQL Server? Hard
A. CREATE PROCEDURE
B. MAKE PROCEDURE
C. NEW PROCEDURE
D. DEFINE PROCEDURE
Answer: A. CREATE PROCEDURE
63. How do you execute a stored procedure in SQL Server? Easy
A. EXECUTE
B. EXEC
C. Both A and B
D. CALL
Answer: C. Both A and B (EXECUTE and EXEC both work in SQL Server)
64. What is an advantage of stored procedures? Medium
A. Improved performance
B. Enhanced security
C. Code reusability
D. All of the above
Answer: D. All of the above
65. Which statement modifies an existing stored procedure in SQL Server? Hard
A. CHANGE PROCEDURE
B. MODIFY PROCEDURE
C. ALTER PROCEDURE
D. UPDATE PROCEDURE
Answer: C. ALTER PROCEDURE

More SQL Server Resources

SQL Server Basics

Learn fundamental SQL Server concepts and commands

View Notes
SQL Server Tricks

Advanced techniques and interview tips

View Tricks