Chapter 3: Writing Simple SELECT Queries

Question 1

Q: What is the purpose of the SELECT statement in T-SQL?

Answer 1

A: The SELECT statement is used to retrieve data from SQL Server.

Question 2

Q: What is the minimum requirement for a T-SQL SELECT statement?

Answer 2

A: The word SELECT followed by at least one item in a SELECT list.

Question 3

Q: What is a literal value in the context of a SELECT statement?

Answer 3

A: A literal value is a specific value defined explicitly in the SELECT statement, not retrieved from the database.

Question 4

Q: How are string literal values designated in T-SQL?

Answer 4

A: String literal values are enclosed in single quotes (tick marks).

Question 5

Q: What is the recommended practice regarding semicolons in T-SQL?

Answer 5

A: It is recommended to end all T-SQL statements with a semicolon, even though it is not currently required.

Question 6

Q: How can you run only a portion of the code in a query window in Azure Data Studio (ADS) or SQL Server Management Studio (SSMS)?

Answer 6

A: Highlight the desired statements and press F5 or click Run/Execute.

Question 7

Q: What does the Messages tab display when running a query?

Answer 7

A: The Messages tab shows the number of rows affected, error messages, or other informational messages.

Question 8

Q: What clause specifies the table name in a SELECT statement?

Answer 8

A: The FROM clause specifies the table name.

Question 9

Q: Provide the syntax for a SELECT statement with a FROM clause.

Answer 9

A: SELECT <column1>, <column2> FROM <schema>.<table>;</schema></column2></column1>

Question 10

Q: Why is it good practice to include the schema name when specifying a table in a SELECT statement?

Answer 10

A: To eliminate confusion, as different schemas can contain tables with the same name but different structures or data.

Question 11

Q: How can you retrieve all columns from a table in a SELECT statement?

Answer 11

A: Use the asterisk (*) symbol in the SELECT list, e.g., SELECT * FROM <table>;.

Question 12

Q: Why should the asterisk (*) symbol be avoided in production applications?

Answer 12

A: It may negatively impact performance, cause application errors if table columns change, and could expose unnecessary or sensitive data.

Question 13

Q: What is the best practice for writing SELECT statements in production?

Answer 13

A: Specify exactly the columns needed and limit the rows to only those required.

Question 14

Q: What does the GO command do in T-SQL?

Answer 14

A: It separates code into distinct batches but does not perform any SQL operation itself.

Question 15

Q: How can you simplify creating a SELECT list when working with a large table?

Answer 15

A: Right-click on the table and select the top 1000 rows, then modify the query by removing the TOP (1000) restriction and unwanted columns.

Question 16

Q: Why are square brackets ([]) used around table or column names in SQL Server?

Answer 16

A: Square brackets allow you to use names that do not follow SQL Server’s naming rules or are reserved words.

Question 17

Q: What is the purpose of the keyword AS in a SELECT statement?

Answer 17

A: The AS keyword specifies an alias for a column, allowing you to rename columns or give headers to literals in the result set.

Question 18

Q: What happens if you omit a comma between two column names in a SELECT list?

Answer 18

A: The second column name will be treated as an alias for the first column, which may lead to unintended results.

Question 19

Q: What should you do if an alias contains a space or is a reserved word?

Answer 19

A: Enclose the alias in square brackets ([]), single quotes (‘ ‘), or double quotes (“ “).

Question 20

Q: Can you mix literals and column names in the same SELECT statement?

Answer 20

A: Yes, you can mix literal values and column names in one SELECT statement.

Question 21

Q: How does IntelliSense assist in writing SQL queries?

Answer 21

A: IntelliSense provides suggestions and a list of columns or objects when typing, helping you write code faster and more accurately.

Question 22

Q: How can you see a list of columns from a table using IntelliSense?

Answer 22

A: After aliasing a table in the SELECT statement (e.g., FROM HumanResources.Employee AS EMP), type the alias followed by a period (e.g., EMP.) to see the list of columns.

Question 23

Q: What is the purpose of replacing * with an alias and a period in a SELECT statement?

Answer 23

A: It allows you to filter and choose specific columns interactively using IntelliSense.

Question 24

Q: What is a common mistake to avoid when creating a SELECT list with aliases?

Answer 24

A: Forgetting to include a comma between two column names, which can unintentionally turn the second column name into an alias.

Question 25

Q: Why is it important to use aliases in a SELECT statement?

Answer 25

A: Aliases provide clearer and more meaningful column headers in the query results.

Question 26

Q: What does the TOP (1000) clause do in a SELECT statement?

Answer 26

A: It limits the query to return only the first 1000 rows of the result set.

Question 27

Q: What is the recommended way to use IntelliSense to select a specific column from a table?

Answer 27

A: Begin typing the alias and column prefix, and press Tab to autocomplete the desired column name.

Question 28

Q: Does SQL Server require T-SQL statements to be written on a single line?

Answer 28

A: No, SQL Server does not care if a statement is written on one line or formatted over several lines.

Question 29

Q: Why is it recommended to format T-SQL code over multiple lines?

Answer 29

A: Formatting over multiple lines with major clauses on new lines makes the code easier to read.

Question 30

Q: Should T-SQL keywords be written in uppercase?

Answer 30

A: While not required, using uppercase for keywords is recommended for better readability and professionalism.

Question 31

Q: Are column and table names case-sensitive in SQL Server?

Answer 31

A: It depends on the database collation. Most databases are case-insensitive by default, but case-sensitive collation requires exact case matching.

Question 32

Q: Why should semicolons be used in T-SQL statements?

Answer 32

A: While not always required, semicolons are a best practice and may become mandatory in future SQL Server versions.

Question 33

Q: What are the purposes of using aliases in T-SQL?

Answer 33

A:
To provide a name for an expression.
To return user-friendly column names.
To reduce typing by shortening table names used multiple times in a query.

Question 34

Q: When should single quotes be used in T-SQL?

Answer 34

A: Single quotes are typically used to enclose literal dates and strings, such as ‘Smith’ or ‘2020-01-01’.

Question 35

Q: What is the purpose of square brackets or double quotes in T-SQL?

Answer 35

A: They are used to enclose names with spaces or reserved keywords, e.g., [SELECT] or “Employee ID”.

Question 36

Q: Which type of quotation is more commonly used in T-SQL: square brackets or double quotes?

Answer 36

A: Square brackets are more commonly used than double quotes in T-SQL.

Question 37

Q: Can single quotes be used for column aliases in T-SQL?

Answer 37

A: Yes, but it is recommended to reserve single quotes for literal values.

Question 38

Q: What is an example of using single quotes, double quotes, and square brackets in one query?

Answer 38

A:
SELECT BusinessEntityID AS “Employee ID”, ‘2020-04-13’ AS “Current Date” FROM [HumanResources].[Employee];

Question 39

Q: What is the best way to learn T-SQL according to the text?

Answer 39

A: Practice by writing and figuring out the code yourself, rather than only reading examples.

Question 40

Q: Why is filtering data important in T-SQL?

Answer 40

A: Filtering data allows queries to return only a subset of rows from a table, which is often needed in applications to display relevant information.

Question 41

Q: What clause is used to filter rows in a T-SQL SELECT statement?

Answer 41

A: The WHERE clause.

Question 42

Q: What is the syntax for using a WHERE clause in a SELECT statement?

Answer 42

A:
SELECT <column1>, <column2>
FROM <schema>.<table>
WHERE <column> = <value>;</value></column></schema></column2></column1>

Question 43

Q: What types of values are enclosed in single quotes in a WHERE clause?

Answer 43

A: Literal strings and dates, e.g., ‘John’ or ‘2020-01-01’.

Question 44

Q: What are some common comparison operators used in the WHERE clause?

Answer 44

A:
= (equals)
!= or <> (not equal to)
< (less than)
> (greater than)
<= (less than or equal to)
>= (greater than or equal to)

Question 45

Q: What do the != and <> operators mean in a WHERE clause?

Answer 45

A: Both operators mean “not equal to” and are interchangeable.

Question 46

Q: How does the WHERE clause process rows in a query?

Answer 46

A: It evaluates expressions (predicates) to TRUE, FALSE, or UNKNOWN, returning rows where the expression is TRUE.

Question 47

Q: What does the query WHERE FirstName > ‘M’ return?

Answer 47

A: It returns rows where the first name is alphabetically greater than ‘M’, including names like ‘Ma’ but not ‘M’.

Question 48

Q: What does the query WHERE FirstName !> ‘M’ return?

Answer 48

A: It returns rows where the first name is not greater than ‘M’, including names that are ‘M’ or earlier in the alphabet.

Question 49

Q: How do you write a one-line comment in T-SQL?

Answer 49

A: Use two dashes (–) before the comment text.

Question 50

Q: How do you write multi-line comments in T-SQL?

Answer 50

A: Enclose the comment with /* at the beginning and */ at the end.

Question 51

Q: What are examples of queries using a WHERE clause with a DateTime column?

Answer 51

A:
WHERE OrderDate > ‘2011-07-05’;
WHERE OrderDate <= ‘2011-07-05’;

Question 52

Q: Why is it important to carefully check query results with WHERE clauses?

Answer 52

A: To ensure the filtering logic is correctly applied and returns the expected rows.

Question 53

Q: How can you filter data based on numeric values in a WHERE clause?

Answer 53

A:
WHERE OrderQty > 10;
WHERE OrderQty <= 5;

Question 54

Q: How can string columns be filtered in a WHERE clause?

Answer 54

A:
WHERE FirstName <> ‘Catherine’;
WHERE FirstName > ‘M’;

Question 55

Q: What is the purpose of the BETWEEN operator in T-SQL?

Answer 55

A: The BETWEEN operator is used in the WHERE clause to specify an inclusive range of values for filtering data.

Question 56

Q: What is the syntax for using BETWEEN in a query?

Answer 56

SELECT <column1>, <column2>
FROM <schema>.<table>
WHERE <column> BETWEEN <value1> AND <value2>;</value2></value1></column></schema></column2></column1>

Question 57

Q: Are the boundary values included in the results of a BETWEEN query?

Answer 57

A: Yes, the values specified at the edges of the range are included in the results.

Question 58

Q: Can BETWEEN be used with string, numeric, and date data types?

Answer 58

A: Yes, BETWEEN can be used with all these data types.

Question 59

Q: What happens if the values in a BETWEEN expression are reversed?

Answer 59

A: The query will return no rows because the condition becomes logically invalid.

Question 60

Q: How does BETWEEN handle strings, such as WHERE JobTitle BETWEEN ‘C’ AND ‘E’?

Answer 60

A: It includes values that start with ‘C’ or ‘D’, but excludes values starting with ‘E’ followed by additional characters.

Question 61

Q: What is the result of an illogical BETWEEN expression, such as BETWEEN 25005 AND 25000?

Answer 61

A: No rows are returned because no values can meet the criteria of being greater than or equal to 25005 and less than or equal to 25000.

Question 62

Q: How do you find values outside a range using BETWEEN?

Answer 62

A: Use NOT with BETWEEN, e.g.,

WHERE <column> NOT BETWEEN <value1> AND <value2>;</value2></value1></column>

Question 63

Q: What happens when NOT is used with an illogical BETWEEN expression?

Answer 63

A: All rows in the table are returned, as no rows meet the BETWEEN condition and the NOT operator negates it.

Question 64

Q: How does NOT BETWEEN handle strings, such as WHERE JobTitle NOT BETWEEN ‘C’ AND ‘E’?

Answer 64

A: It includes values starting with ‘A’ or ‘B’, and values starting with ‘E’ followed by other characters or greater than ‘E’.

Question 65

Q: Why is it important to consider time values when using BETWEEN with datetime columns?

Answer 65

A: Datetime values often include both date and time, so filtering might need to account for the time portion explicitly.

Question 66

Q: What is the behavior of NOT BETWEEN with date ranges?

Answer 66

A: It returns rows with dates before the lower boundary or after the upper boundary of the range.

Question 67

Q: What does this query return:

WHERE OrderDate BETWEEN ‘2011-07-02’ AND ‘2011-07-04’;

Answer 67

A: It returns all orders placed on July 2, July 3, and July 4, 2011.

Question 68

Q: Why is it important to list the lower value first in a BETWEEN expression?

Answer 68

A: To ensure the range logic is valid; otherwise, the query will not return any rows.

Question 69

Q: What happens when you filter on a date column that also stores time using only the date?

Answer 69

A: You may retrieve incomplete results because the query will only match rows where the time is precisely 00:00:00 for the specified date.

Question 70

Q: What is the recommended query to retrieve all rows for a specific date, accounting for time?

Answer 70

A: Use a BETWEEN clause with a range that includes all possible times on that date:

WHERE MyDate BETWEEN ‘2020-01-03 00:00:00’ AND ‘2020-01-03 23:59:59’;

Question 71

Q: How can you accurately filter rows for a specific date using two conditions?

Answer 71

A: Write the query as:

WHERE MyDate >= ‘2020-01-03’ AND MyDate < ‘2020-01-04’;

Question 72

Q: What does SQL Server use to store dates and times internally?

Answer 72

A: SQL Server stores dates as integers representing the number of days since 1900-01-01 and times as the number of clock ticks past midnight.

Question 73

Q: Why is the format ‘YYYY-MM-DD’ recommended for date values in SQL Server?

Answer 73

A: It ensures consistency and avoids issues with localization settings of the server.

Question 74

Q: Why does the query WHERE MyDate = ‘2020-01-03’ return no rows in the example?

Answer 74

A: Because there are no entries with the exact time 00:00:00 on the specified date.

Question 75

Q: What data type in the example allows accurate filtering on both date and time?

Answer 75

A: DATETIME2(0).

Question 76

Q: How should you format dates with slashes (/) or spelled-out months for SQL Server?

Answer 76

A: Use formats recognized by the server’s localization settings, but the best practice is to always use ‘YYYY-MM-DD’.

Question 77

Q: What is the effect of using improperly formatted dates in SQL queries?

Answer 77

A: SQL Server may not interpret the value correctly, leading to errors or unexpected results.

Question 78

Q: How does the BETWEEN operator handle time values when filtering a datetime column?

Answer 78

A: It includes all rows with datetime values falling within the specified start and end range, including the exact boundaries.

Question 79

Q: What are logical operators in a WHERE clause, and how are they used?

Answer 79

A: Logical operators such as AND and OR are used to combine multiple predicates in a WHERE clause to form more complex conditions.

Question 80

Q: What does the AND operator do in a WHERE clause?

Answer 80

A: The AND operator returns rows only when all combined predicates evaluate to TRUE.

Question 81

Q: What does the OR operator do in a WHERE clause?

Answer 81

A: The OR operator returns rows when at least one of the combined predicates evaluates to TRUE.

Question 82

Q: Provide an example of a WHERE clause using both AND and OR.

Answer 82

WHERE FirstName = ‘Ken’ AND (LastName = ‘Myer’ OR LastName = ‘Meyer’);

Question 83

Q: How should you filter datetime columns for specific date ranges?

Answer 83

A: Use >= for the start date and < for one day after the end date, e.g.:

WHERE MyDate >= ‘2020-01-02’ AND MyDate < ‘2020-01-04’;

Question 84

Q: What is the purpose of the IN operator in a WHERE clause?

Answer 84

A: The IN operator simplifies queries by allowing you to compare a column to multiple values within a list.

Question 85

Q: What is the syntax for using the IN operator?

Answer 85

WHERE <column> IN (<value1>, <value2>, <value3>);</value3></value2></value1></column>

Question 86

Q: How can the IN operator be combined with other predicates?

Answer 86

WHERE FirstName = ‘Ken’ AND LastName IN (‘Myer’, ‘Meyer’);

Question 87

Q: How does NOT IN differ from IN in a WHERE clause?

Answer 87

A: NOT IN excludes rows that match the specified values in the list, returning the opposite of IN.

Question 88

Q: Provide an example of a query using NOT IN.

Answer 88

WHERE TerritoryID NOT IN (2, 1, 4, 5);

Question 89

Q: If a value appears multiple times in the IN list, will the row appear multiple times in the results?

Answer 89

A: No, rows will only appear once in the results, even if the value is listed multiple times in the IN clause.

Question 90

Q: Why is it important to carefully construct WHERE clauses with multiple predicates?

Answer 90

A: Complex WHERE clauses can lead to logical errors or unintended results if the predicates are not combined correctly.

Question 91

Q: What does this query return:

WHERE MyDate >= ‘2020-01-02’ AND MyDate < ‘2020-01-04’;

Answer 91

A: Rows with MyDate on 2020-01-02 and 2020-01-03, but not 2020-01-04.

Question 92

Q: How can the IN operator be used with numeric columns?

Answer 92

WHERE TerritoryID IN (2, 1, 4, 5);

Question 93

Q: What happens if you use IN with dates in a WHERE clause?

Answer 93

A: Rows matching any of the specified dates in the list will be returned.

Question 94

Q: What does NULL mean in T-SQL?

Answer 94

A: NULL indicates that no value has been entered for a column, meaning the value is unknown.

Question 95

Q: Can NULL be compared to any value using equality operators like = or !=?

Answer 95

A: No, NULL is not a value and cannot be compared directly. Instead, use IS NULL or IS NOT NULL to test for NULLs.

Question 96

Q: What does this query do: WHERE MiddleName IS NULL?

Answer 96

A: It retrieves rows where the MiddleName column has a NULL value.

Question 97

Q: What happens when a NULL is part of an expression in a WHERE clause?

Answer 97

A: The expression evaluates to UNKNOWN, and the row is not returned unless explicitly checked with IS NULL.

Question 98

Q: How do you replace NULL values in a column with a specific value?

Answer 98

A: Use the ISNULL function, e.g., ISNULL(MiddleName, ‘’).

Question 99

Q: Why should NULLs be carefully considered when using NOT in a query?

Answer 99

A: NULLs can cause incomplete results because expressions involving NULLs resolve to UNKNOWN.

Question 100

Q: How are NULLs handled in comparison operators like < or >?

Answer 100

A: NULLs are excluded from results because these comparisons resolve to UNKNOWN.

Question 101

Q: What clause is used to sort query results?

Answer 101

A: The ORDER BY clause.

Question 102

Q: What is the default sort order in SQL Server?

Answer 102

A: Ascending order.

Question 103

Q: How do you specify descending order in the ORDER BY clause?

Answer 103

A: Use the DESC keyword after the column name.

Question 104

Q: Can you sort by multiple columns in SQL Server?

Answer 104

A: Yes, specify columns in the ORDER BY clause separated by commas, e.g.,

ORDER BY ProductID, LocationID DESC;

Question 105

Q: What happens to NULLs when sorting in ascending order?

Answer 105

A: NULLs appear first because “nothing comes before something.”

Question 106

Q: Can aliases from the SELECT clause be used in the ORDER BY clause?

Answer 106

A: Yes, but avoid ambiguous column names in aliases, as they can cause errors.

Question 107

Q: What does the following query do?

SELECT ProductID, LocationID FROM Production.ProductInventory ORDER BY LocationID;

Answer 107

A: It sorts the results by LocationID in ascending order.

Question 108

Q: What error might occur when using an alias with the same name as an existing column in the SELECT clause?

Answer 108

A: An “ambiguous column name” error, e.g., Msg 209, Level 16.

Question 109

Q: How do you sort results by a specific column while ensuring descending order for another column?

Answer 109

A: Specify the sort direction for each column, e.g.,

ORDER BY ProductID ASC, LocationID DESC;

Question 110

Q: Why is it important to think about query performance in T-SQL?

Answer 110

A: Writing inefficient queries can degrade the performance of applications and reports, leading to slower database operations.

Question 111

Q: What is the role of indexes in query performance?

Answer 111

A: Indexes help the database engine locate rows more efficiently, often retrieving all required columns directly from the index without accessing the table.

Question 112

Q: What happens when a query filters on a non-indexed column?

Answer 112

A: The database engine must check the value of the column in every row of the table, leading to slower performance.

Question 113

Q: Why is the order of columns in an index important?

Answer 113

A: The database engine can fully utilize an index only if the query filters on the leading column(s) of the index.

Question 114

Q: How does SQL Server handle filtering on a secondary column in a composite index?

Answer 114

A: SQL Server performs an index scan, comparing the value against each entry in the index, which is less efficient than an index seek.

Question 115

Q: What is an execution plan?

Answer 115

A: An execution plan shows how the database engine executes a query, including whether it uses indexes and the performance impact.

Question 116

Q: How do you enable execution plans in SQL Server Management Studio (SSMS)?

Answer 116

A: Toggle the Include Actual Execution Plan icon before running the query.

Question 117

Q: What is an index seek in an execution plan?

Answer 117

A: An index seek efficiently finds rows by directly accessing relevant entries in the index, similar to using an index in a book.

Question 118

Q: What is an index scan in an execution plan?

Answer 118

A: An index scan evaluates all entries in the index, which is less efficient than an index seek.

Question 119

Q: What is a clustered index scan, and why is it less efficient?

Answer 119

A: A clustered index scan evaluates every row in the table to retrieve results, which is more work than using a non-clustered index.

Question 120

Q: What can an execution plan suggest to improve query performance?

Answer 120

A: It may suggest creating a new index tailored to the query’s requirements.

Question 121

Q: How does Query 1 in Listing 3-15 perform when filtering on LastName = ‘Smith’?

Answer 121

A: It uses an index seek because LastName is the leading column in the index, resulting in efficient performance.

Question 122

Q: Why does Query 2 in Listing 3-15, filtering on FirstName = ‘Ken’, perform less efficiently?

Answer 122

A: It performs an index scan because FirstName is not the leading column in the composite index.

Question 123

Q: What happens in Query 3, filtering on ModifiedDate?

Answer 123

A: The query performs a clustered index scan because there is no index on the ModifiedDate column.

Question 124

Q: Why should you avoid demanding index changes in a production database without proper analysis?

Answer 124

A: Index changes can have broad impacts on performance, and tuning requires expertise and understanding of overall database usage.

Question 125

Q: How can execution plans help you improve your T-SQL queries?

Answer 125

A: They provide insights into query performance, helping identify inefficiencies and opportunities for optimization.