Chapter 3: Writing Simple SELECT Queries Flashcards
Q: What is the purpose of the SELECT statement in T-SQL?
A: The SELECT statement is used to retrieve data from SQL Server.
Q: What is the minimum requirement for a T-SQL SELECT statement?
A: The word SELECT followed by at least one item in a SELECT list.
Q: What is a literal value in the context of a SELECT statement?
A: A literal value is a specific value defined explicitly in the SELECT statement, not retrieved from the database.
Q: How are string literal values designated in T-SQL?
A: String literal values are enclosed in single quotes (tick marks).
Q: What is the recommended practice regarding semicolons in T-SQL?
A: It is recommended to end all T-SQL statements with a semicolon, even though it is not currently required.
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)?
A: Highlight the desired statements and press F5 or click Run/Execute.
Q: What does the Messages tab display when running a query?
A: The Messages tab shows the number of rows affected, error messages, or other informational messages.
Q: What clause specifies the table name in a SELECT statement?
A: The FROM clause specifies the table name.
Q: Provide the syntax for a SELECT statement with a FROM clause.
A: SELECT <column1>, <column2> FROM <schema>.<table>;</schema></column2></column1>
Q: Why is it good practice to include the schema name when specifying a table in a SELECT statement?
A: To eliminate confusion, as different schemas can contain tables with the same name but different structures or data.
Q: How can you retrieve all columns from a table in a SELECT statement?
A: Use the asterisk (*) symbol in the SELECT list, e.g., SELECT * FROM <table>;.
Q: Why should the asterisk (*) symbol be avoided in production applications?
A: It may negatively impact performance, cause application errors if table columns change, and could expose unnecessary or sensitive data.
Q: What is the best practice for writing SELECT statements in production?
A: Specify exactly the columns needed and limit the rows to only those required.
Q: What does the GO command do in T-SQL?
A: It separates code into distinct batches but does not perform any SQL operation itself.
Q: How can you simplify creating a SELECT list when working with a large table?
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.
Q: Why are square brackets ([]) used around table or column names in SQL Server?
A: Square brackets allow you to use names that do not follow SQL Server’s naming rules or are reserved words.
Q: What is the purpose of the keyword AS in a SELECT statement?
A: The AS keyword specifies an alias for a column, allowing you to rename columns or give headers to literals in the result set.
Q: What happens if you omit a comma between two column names in a SELECT list?
A: The second column name will be treated as an alias for the first column, which may lead to unintended results.
Q: What should you do if an alias contains a space or is a reserved word?
A: Enclose the alias in square brackets ([]), single quotes (‘ ‘), or double quotes (“ “).
Q: Can you mix literals and column names in the same SELECT statement?
A: Yes, you can mix literal values and column names in one SELECT statement.
Q: How does IntelliSense assist in writing SQL queries?
A: IntelliSense provides suggestions and a list of columns or objects when typing, helping you write code faster and more accurately.
Q: How can you see a list of columns from a table using IntelliSense?
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.
Q: What is the purpose of replacing * with an alias and a period in a SELECT statement?
A: It allows you to filter and choose specific columns interactively using IntelliSense.
Q: What is a common mistake to avoid when creating a SELECT list with aliases?
A: Forgetting to include a comma between two column names, which can unintentionally turn the second column name into an alias.
Q: Why is it important to use aliases in a SELECT statement?
A: Aliases provide clearer and more meaningful column headers in the query results.
Q: What does the TOP (1000) clause do in a SELECT statement?
A: It limits the query to return only the first 1000 rows of the result set.
Q: What is the recommended way to use IntelliSense to select a specific column from a table?
A: Begin typing the alias and column prefix, and press Tab to autocomplete the desired column name.
Q: Does SQL Server require T-SQL statements to be written on a single line?
A: No, SQL Server does not care if a statement is written on one line or formatted over several lines.
Q: Why is it recommended to format T-SQL code over multiple lines?
A: Formatting over multiple lines with major clauses on new lines makes the code easier to read.
Q: Should T-SQL keywords be written in uppercase?
A: While not required, using uppercase for keywords is recommended for better readability and professionalism.
Q: Are column and table names case-sensitive in SQL Server?
A: It depends on the database collation. Most databases are case-insensitive by default, but case-sensitive collation requires exact case matching.
Q: Why should semicolons be used in T-SQL statements?
A: While not always required, semicolons are a best practice and may become mandatory in future SQL Server versions.
Q: What are the purposes of using aliases in T-SQL?
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.
Q: When should single quotes be used in T-SQL?
A: Single quotes are typically used to enclose literal dates and strings, such as ‘Smith’ or ‘2020-01-01’.
Q: What is the purpose of square brackets or double quotes in T-SQL?
A: They are used to enclose names with spaces or reserved keywords, e.g., [SELECT] or “Employee ID”.
Q: Which type of quotation is more commonly used in T-SQL: square brackets or double quotes?
A: Square brackets are more commonly used than double quotes in T-SQL.
Q: Can single quotes be used for column aliases in T-SQL?
A: Yes, but it is recommended to reserve single quotes for literal values.
Q: What is an example of using single quotes, double quotes, and square brackets in one query?
A:
SELECT BusinessEntityID AS “Employee ID”, ‘2020-04-13’ AS “Current Date” FROM [HumanResources].[Employee];
Q: What is the best way to learn T-SQL according to the text?
A: Practice by writing and figuring out the code yourself, rather than only reading examples.
Q: Why is filtering data important in T-SQL?
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.
Q: What clause is used to filter rows in a T-SQL SELECT statement?
A: The WHERE clause.
Q: What is the syntax for using a WHERE clause in a SELECT statement?
A:
SELECT <column1>, <column2>
FROM <schema>.<table>
WHERE <column> = <value>;</value></column></schema></column2></column1>
Q: What types of values are enclosed in single quotes in a WHERE clause?
A: Literal strings and dates, e.g., ‘John’ or ‘2020-01-01’.
Q: What are some common comparison operators used in the WHERE clause?
A:
= (equals)
!= or <> (not equal to)
< (less than)
> (greater than)
<= (less than or equal to)
>= (greater than or equal to)
Q: What do the != and <> operators mean in a WHERE clause?
A: Both operators mean “not equal to” and are interchangeable.
Q: How does the WHERE clause process rows in a query?
A: It evaluates expressions (predicates) to TRUE, FALSE, or UNKNOWN, returning rows where the expression is TRUE.
Q: What does the query WHERE FirstName > ‘M’ return?
A: It returns rows where the first name is alphabetically greater than ‘M’, including names like ‘Ma’ but not ‘M’.
Q: What does the query WHERE FirstName !> ‘M’ return?
A: It returns rows where the first name is not greater than ‘M’, including names that are ‘M’ or earlier in the alphabet.
Q: How do you write a one-line comment in T-SQL?
A: Use two dashes (–) before the comment text.
Q: How do you write multi-line comments in T-SQL?
A: Enclose the comment with /* at the beginning and */ at the end.
Q: What are examples of queries using a WHERE clause with a DateTime column?
A:
WHERE OrderDate > ‘2011-07-05’;
WHERE OrderDate <= ‘2011-07-05’;
Q: Why is it important to carefully check query results with WHERE clauses?
A: To ensure the filtering logic is correctly applied and returns the expected rows.
Q: How can you filter data based on numeric values in a WHERE clause?
A:
WHERE OrderQty > 10;
WHERE OrderQty <= 5;
Q: How can string columns be filtered in a WHERE clause?
A:
WHERE FirstName <> ‘Catherine’;
WHERE FirstName > ‘M’;
Q: What is the purpose of the BETWEEN operator in T-SQL?
A: The BETWEEN operator is used in the WHERE clause to specify an inclusive range of values for filtering data.
Q: What is the syntax for using BETWEEN in a query?
SELECT <column1>, <column2>
FROM <schema>.<table>
WHERE <column> BETWEEN <value1> AND <value2>;</value2></value1></column></schema></column2></column1>
Q: Are the boundary values included in the results of a BETWEEN query?
A: Yes, the values specified at the edges of the range are included in the results.
Q: Can BETWEEN be used with string, numeric, and date data types?
A: Yes, BETWEEN can be used with all these data types.
Q: What happens if the values in a BETWEEN expression are reversed?
A: The query will return no rows because the condition becomes logically invalid.
Q: How does BETWEEN handle strings, such as WHERE JobTitle BETWEEN ‘C’ AND ‘E’?
A: It includes values that start with ‘C’ or ‘D’, but excludes values starting with ‘E’ followed by additional characters.
Q: What is the result of an illogical BETWEEN expression, such as BETWEEN 25005 AND 25000?
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.
Q: How do you find values outside a range using BETWEEN?
A: Use NOT with BETWEEN, e.g.,
WHERE <column> NOT BETWEEN <value1> AND <value2>;</value2></value1></column>
Q: What happens when NOT is used with an illogical BETWEEN expression?
A: All rows in the table are returned, as no rows meet the BETWEEN condition and the NOT operator negates it.
Q: How does NOT BETWEEN handle strings, such as WHERE JobTitle NOT BETWEEN ‘C’ AND ‘E’?
A: It includes values starting with ‘A’ or ‘B’, and values starting with ‘E’ followed by other characters or greater than ‘E’.
Q: Why is it important to consider time values when using BETWEEN with datetime columns?
A: Datetime values often include both date and time, so filtering might need to account for the time portion explicitly.
Q: What is the behavior of NOT BETWEEN with date ranges?
A: It returns rows with dates before the lower boundary or after the upper boundary of the range.
Q: What does this query return:
WHERE OrderDate BETWEEN ‘2011-07-02’ AND ‘2011-07-04’;
A: It returns all orders placed on July 2, July 3, and July 4, 2011.
Q: Why is it important to list the lower value first in a BETWEEN expression?
A: To ensure the range logic is valid; otherwise, the query will not return any rows.
Q: What happens when you filter on a date column that also stores time using only the date?
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.
Q: What is the recommended query to retrieve all rows for a specific date, accounting for time?
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’;
Q: How can you accurately filter rows for a specific date using two conditions?
A: Write the query as:
WHERE MyDate >= ‘2020-01-03’ AND MyDate < ‘2020-01-04’;
Q: What does SQL Server use to store dates and times internally?
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.
Q: Why is the format ‘YYYY-MM-DD’ recommended for date values in SQL Server?
A: It ensures consistency and avoids issues with localization settings of the server.
Q: Why does the query WHERE MyDate = ‘2020-01-03’ return no rows in the example?
A: Because there are no entries with the exact time 00:00:00 on the specified date.
Q: What data type in the example allows accurate filtering on both date and time?
A: DATETIME2(0).
Q: How should you format dates with slashes (/) or spelled-out months for SQL Server?
A: Use formats recognized by the server’s localization settings, but the best practice is to always use ‘YYYY-MM-DD’.
Q: What is the effect of using improperly formatted dates in SQL queries?
A: SQL Server may not interpret the value correctly, leading to errors or unexpected results.
Q: How does the BETWEEN operator handle time values when filtering a datetime column?
A: It includes all rows with datetime values falling within the specified start and end range, including the exact boundaries.
Q: What are logical operators in a WHERE clause, and how are they used?
A: Logical operators such as AND and OR are used to combine multiple predicates in a WHERE clause to form more complex conditions.
Q: What does the AND operator do in a WHERE clause?
A: The AND operator returns rows only when all combined predicates evaluate to TRUE.
Q: What does the OR operator do in a WHERE clause?
A: The OR operator returns rows when at least one of the combined predicates evaluates to TRUE.
Q: Provide an example of a WHERE clause using both AND and OR.
WHERE FirstName = ‘Ken’ AND (LastName = ‘Myer’ OR LastName = ‘Meyer’);
Q: How should you filter datetime columns for specific date ranges?
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’;
Q: What is the purpose of the IN operator in a WHERE clause?
A: The IN operator simplifies queries by allowing you to compare a column to multiple values within a list.
Q: What is the syntax for using the IN operator?
WHERE <column> IN (<value1>, <value2>, <value3>);</value3></value2></value1></column>
Q: How can the IN operator be combined with other predicates?
WHERE FirstName = ‘Ken’ AND LastName IN (‘Myer’, ‘Meyer’);
Q: How does NOT IN differ from IN in a WHERE clause?
A: NOT IN excludes rows that match the specified values in the list, returning the opposite of IN.
Q: Provide an example of a query using NOT IN.
WHERE TerritoryID NOT IN (2, 1, 4, 5);
Q: If a value appears multiple times in the IN list, will the row appear multiple times in the results?
A: No, rows will only appear once in the results, even if the value is listed multiple times in the IN clause.
Q: Why is it important to carefully construct WHERE clauses with multiple predicates?
A: Complex WHERE clauses can lead to logical errors or unintended results if the predicates are not combined correctly.
Q: What does this query return:
WHERE MyDate >= ‘2020-01-02’ AND MyDate < ‘2020-01-04’;
A: Rows with MyDate on 2020-01-02 and 2020-01-03, but not 2020-01-04.
Q: How can the IN operator be used with numeric columns?
WHERE TerritoryID IN (2, 1, 4, 5);
Q: What happens if you use IN with dates in a WHERE clause?
A: Rows matching any of the specified dates in the list will be returned.
Q: What does NULL mean in T-SQL?
A: NULL indicates that no value has been entered for a column, meaning the value is unknown.
Q: Can NULL be compared to any value using equality operators like = or !=?
A: No, NULL is not a value and cannot be compared directly. Instead, use IS NULL or IS NOT NULL to test for NULLs.
Q: What does this query do: WHERE MiddleName IS NULL?
A: It retrieves rows where the MiddleName column has a NULL value.
Q: What happens when a NULL is part of an expression in a WHERE clause?
A: The expression evaluates to UNKNOWN, and the row is not returned unless explicitly checked with IS NULL.
Q: How do you replace NULL values in a column with a specific value?
A: Use the ISNULL function, e.g., ISNULL(MiddleName, ‘’).
Q: Why should NULLs be carefully considered when using NOT in a query?
A: NULLs can cause incomplete results because expressions involving NULLs resolve to UNKNOWN.
Q: How are NULLs handled in comparison operators like < or >?
A: NULLs are excluded from results because these comparisons resolve to UNKNOWN.
Q: What clause is used to sort query results?
A: The ORDER BY clause.
Q: What is the default sort order in SQL Server?
A: Ascending order.
Q: How do you specify descending order in the ORDER BY clause?
A: Use the DESC keyword after the column name.
Q: Can you sort by multiple columns in SQL Server?
A: Yes, specify columns in the ORDER BY clause separated by commas, e.g.,
ORDER BY ProductID, LocationID DESC;
Q: What happens to NULLs when sorting in ascending order?
A: NULLs appear first because “nothing comes before something.”
Q: Can aliases from the SELECT clause be used in the ORDER BY clause?
A: Yes, but avoid ambiguous column names in aliases, as they can cause errors.
Q: What does the following query do?
SELECT ProductID, LocationID FROM Production.ProductInventory ORDER BY LocationID;
A: It sorts the results by LocationID in ascending order.
Q: What error might occur when using an alias with the same name as an existing column in the SELECT clause?
A: An “ambiguous column name” error, e.g., Msg 209, Level 16.
Q: How do you sort results by a specific column while ensuring descending order for another column?
A: Specify the sort direction for each column, e.g.,
ORDER BY ProductID ASC, LocationID DESC;
Q: Why is it important to think about query performance in T-SQL?
A: Writing inefficient queries can degrade the performance of applications and reports, leading to slower database operations.
Q: What is the role of indexes in query performance?
A: Indexes help the database engine locate rows more efficiently, often retrieving all required columns directly from the index without accessing the table.
Q: What happens when a query filters on a non-indexed column?
A: The database engine must check the value of the column in every row of the table, leading to slower performance.
Q: Why is the order of columns in an index important?
A: The database engine can fully utilize an index only if the query filters on the leading column(s) of the index.
Q: How does SQL Server handle filtering on a secondary column in a composite index?
A: SQL Server performs an index scan, comparing the value against each entry in the index, which is less efficient than an index seek.
Q: What is an execution plan?
A: An execution plan shows how the database engine executes a query, including whether it uses indexes and the performance impact.
Q: How do you enable execution plans in SQL Server Management Studio (SSMS)?
A: Toggle the Include Actual Execution Plan icon before running the query.
Q: What is an index seek in an execution plan?
A: An index seek efficiently finds rows by directly accessing relevant entries in the index, similar to using an index in a book.
Q: What is an index scan in an execution plan?
A: An index scan evaluates all entries in the index, which is less efficient than an index seek.
Q: What is a clustered index scan, and why is it less efficient?
A: A clustered index scan evaluates every row in the table to retrieve results, which is more work than using a non-clustered index.
Q: What can an execution plan suggest to improve query performance?
A: It may suggest creating a new index tailored to the query’s requirements.
Q: How does Query 1 in Listing 3-15 perform when filtering on LastName = ‘Smith’?
A: It uses an index seek because LastName is the leading column in the index, resulting in efficient performance.
Q: Why does Query 2 in Listing 3-15, filtering on FirstName = ‘Ken’, perform less efficiently?
A: It performs an index scan because FirstName is not the leading column in the composite index.
Q: What happens in Query 3, filtering on ModifiedDate?
A: The query performs a clustered index scan because there is no index on the ModifiedDate column.
Q: Why should you avoid demanding index changes in a production database without proper analysis?
A: Index changes can have broad impacts on performance, and tuning requires expertise and understanding of overall database usage.
Q: How can execution plans help you improve your T-SQL queries?
A: They provide insights into query performance, helping identify inefficiencies and opportunities for optimization.
