SQL_medium_questions.pdf

1. **What does the following SQL Server query return?** SELECT DATEDIFF(day, '2023-01-01', '2023-01-15'); A) 14 B) 15 C) 16 D) 13 **Answer:** A 2. **What is the purpose of the `IDENTITY` property in SQL Server?** A) It enforces uniqueness across multiple columns B) I...

1. **What does the following SQL Server query return?** SELECT DATEDIFF(day, '2023-01-01', '2023-01-15'); A) 14 B) 15 C) 16 D) 13 **Answer:** A 2. **What is the purpose of the `IDENTITY` property in SQL Server?** A) It enforces uniqueness across multiple columns B) It automatically generates a unique number for a new row C) It is used to define a primary key D) It is used to create a foreign key **Answer:** B 3. **Which of the following is true regarding a clustered index in SQL Server?** A) It is used to ensure unique values B) It stores the data rows in the table in sorted order C) It can be created on a non-primary key column D) It always improves query performance  **Answer:** B 4. **What is the result of the following query in SQL Server?** ```sql SELECT REPLICATE('SQL', 3); ``` A) 'SQLSQLSQL' B) 'SQL, SQL, SQL' C) 'SQL3' D) 'S' **Answer:** A 5. **What does the `WITH` keyword do in a SQL Server query when used with a subquery?** A) It creates a recursive query B) It defines a Common Table Expression (CTE) C) It creates a temporary table D) It modifies the execution plan of the query **Answer:** B 6. **What is the role of the `COALESCE` function in SQL Server?** A) It returns NULL if all expressions are NULL B) It combines multiple columns into a single value C) It returns the first non-NULL expression in the list  D) It removes duplicates from the result set **Answer:** C 7. **What is the purpose of the `CASE` statement in SQL Server?** A) It allows conditional logic within a query B) It creates an index on a column C) It sorts the result set D) It allows subqueries to be executed **Answer:** A 8. **What will happen if a DELETE statement in SQL Server is executed without a WHERE clause?** A) It will throw an error B) It will delete all records from the table C) It will delete the first record in the table D) It will delete only NULL records **Answer:** B 9. **Which of the following is used to store unstructured data, such as text files, in SQL Server?** A) VARCHAR B) IMAGE C) BLOB D) FILESTREAM  **Answer:** D 10. **What does the following query return in SQL Server?** ```sql SELECT TOP(5) * FROM Orders ORDER BY OrderDate DESC; ``` A) The first 5 orders in the table B) The last 5 orders based on OrderDate C) The 5 most recent orders based on OrderDate D) The oldest 5 orders based on OrderDate **Answer:** C 11. **Which of the following is true about SQL Server transactions?** A) A transaction is automatically committed if no errors occur B) A transaction must always be rolled back manually C) Transactions cannot be nested in SQL Server D) Transactions cannot be used with DELETE operations **Answer:** A 12. **What is the purpose of the `UNION` operator in SQL Server?** A) It returns the intersection of two result sets B) It returns all distinct rows from two or more result sets  C) It combines columns from two result sets into one column D) It creates a temporary table for multiple result sets **Answer:** B 13. **How can you force the query optimizer to use a specific index in SQL Server?** A) By using the `USE INDEX` clause B) By creating a clustered index on the table C) By using the `INDEX` hint in the query D) By rewriting the query with JOINs **Answer:** C 14. **What does the following query accomplish?** ```sql SELECT DISTINCT Name FROM Customers; ``` A) Returns all rows from the Customers table B) Returns unique customer names C) Deletes duplicate rows from the table D) Returns names that appear more than once **Answer:** B 15. **Which of the following is true regarding table variables in SQL Server?**  A) Table variables are stored in memory and are faster than temporary tables B) Table variables can store large amounts of data without performance issues C) Table variables support transactions D) Table variables can be used with non-clustered indexes **Answer:** A 16. **What is the purpose of the `ROW_NUMBER()` function in SQL Server?** A) It is used to return total number of rows in any result set B) It assigns a unique number to each row, starting from 1 C) It generates a random number for each row D) It removes duplicate rows from the result set **Answer:** B 17. **What is the difference between `CHAR` and `VARCHAR` in SQL Server?** A) `CHAR` is used for numeric data, while `VARCHAR` is for text data B) `CHAR` is fixed-length, while `VARCHAR` is variable-length C) `CHAR` is case-sensitive, while `VARCHAR` is not D) `CHAR` can store more data than `VARCHAR` **Answer:** B 18. **What will happen if an attempt is made to update a non-existing row in SQL Server?** A) The update will succeed without changing any data  B) The update will fail and return an error C) The update will insert a new row D) The update will create a duplicate of an existing row **Answer:** A 19. **How can you combine multiple result sets from different SELECT queries into a single result set in SQL Server?** A) By using the `MERGE` keyword B) By using the `UNION` keyword C) By using the `JOIN` keyword D) By using the `EXCEPT` keyword **Answer:** B 20. **Which SQL Server system stored procedure can be used to view the structure of a table?** A) `sp_helpindex` B) `sp_help` C) `sp_who` D) `sp_executesql` **Answer:** B