SQL Server Basics Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does the following SQL Server query return? SELECT DATEDIFF(day, '2023-01-01', '2023-01-15');

15
16
14 (correct)
13

What is the purpose of the `IDENTITY` property in SQL Server?

It enforces uniqueness across multiple columns
It automatically generates a unique number for a new row (correct)
It is used to define a primary key
It is used to create a foreign key

Which of the following is true regarding a clustered index in SQL Server?

It is used to ensure unique values
It can be created on a non-primary key column
It stores the data rows in the table in sorted order (correct)
It always improves query performance

What is the result of the following query in SQL Server? SELECT REPLICATE('SQL', 3);

'SQLSQLSQL' (B) Signup and view all the answers

What does the `WITH` keyword do in a SQL Server query when used with a subquery?

It defines a Common Table Expression (CTE) (B) Signup and view all the answers

What is the role of the `COALESCE` function in SQL Server?

It returns the first non-NULL expression in the list (C) Signup and view all the answers

What is the purpose of the `CASE` statement in SQL Server?

It allows conditional logic within a query (B) Signup and view all the answers

What will happen if a DELETE statement in SQL Server is executed without a WHERE clause?

It will delete all records from the table (C) Signup and view all the answers

Which of the following is used to store unstructured data, such as text files, in SQL Server?

FILESTREAM (C) Signup and view all the answers

What does the following query return in SQL Server? SELECT TOP(5) * FROM Orders ORDER BY OrderDate DESC;

The 5 most recent orders based on OrderDate (A) Signup and view all the answers

Which of the following is true about SQL Server transactions?

A transaction is automatically committed if no errors occur (C) Signup and view all the answers

What is the purpose of the `UNION` operator in SQL Server?

It returns all distinct rows from two or more result sets (D) Signup and view all the answers

How can you force the query optimizer to use a specific index in SQL Server?

By using the <code>INDEX</code> hint in the query (A) Signup and view all the answers

What does the following query accomplish? SELECT DISTINCT Name FROM Customers;

Returns unique customer names (B) Signup and view all the answers

Which of the following is true regarding table variables in SQL Server?

Table variables are stored in memory and are faster than temporary tables (B) Signup and view all the answers

What is the purpose of the `ROW_NUMBER()` function in SQL Server?

It assigns a unique number to each row, starting from 1 (B) Signup and view all the answers

What is the difference between `CHAR` and `VARCHAR` in SQL Server?

<code>CHAR</code> is fixed-length, while <code>VARCHAR</code> is variable-length (C) Signup and view all the answers

Study Notes

SQL Server Query Functions and Operators

DATEDIFF: Returns the difference in days between two dates. Example: '2023-01-01' to '2023-01-15' yields 14 days.
IDENTITY property: Automatically generates unique numeric values for new rows, ensuring uniqueness for primary keys.
Clustered Index: Stores data rows in sorted order based on indexed columns, enhancing query performance when accessing sorted data.

String and Data Manipulation Functions

REPLICATE: Duplicates a specified string a defined number of times. Example: REPLICATE('SQL', 3) returns 'SQLSQLSQL'.
COALESCE: Returns the first non-NULL value from the list of expressions provided.
CASE statement: Allows conditional logic within SQL queries, enabling branching decisions based on certain criteria.

SQL Server Data Modification and Transactions

A DELETE statement executed without a WHERE clause removes all records from the specified table.
Transactions: Automatically commit if no errors occur, ensuring data integrity during batch operations.

Data Retrieval and Result Set Operations

TOP(n): Limits the number of records returned by a query. Example: SELECT TOP(5) FROM Orders ORDER BY OrderDate DESC returns the five most recent orders.
UNION: Combines results from multiple queries, returning all distinct rows from the involved sets.

Indexing and Query Optimization

INDEX hint: Forces SQL Server to use a specific index during query execution to enhance performance.
Table variables: Stored in memory, often resulting in faster access than temporary tables; however, they have limitations with large data and transaction support.

SQL Data Types and Lengths

CHAR vs VARCHAR: CHAR is fixed-length, suited for data of known lengths, while VARCHAR is variable-length, more flexible for different data sizes.

Special Functions

ROW_NUMBER(): Generates a sequential integer for each row in the result set, starting at one, useful for pagination.

Miscellaneous SQL Facts

Distinct records: SELECT DISTINCT filters duplicate values in a query result, e.g., unique customer names from the Customers table.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge on SQL Server fundamentals with this quiz. It includes questions about date functions, identity properties, and more. Perfect for beginners and those looking to refresh their skills.