Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Data Analyst > Chapter 3: Writing Simple SELECT Queries > Flashcards

Chapter 3: Writing Simple SELECT Queries Flashcards

(125 cards)

Start Studying
1
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

A: The FROM clause specifies the table name.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Q: How does IntelliSense assist in writing SQL queries?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
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.
26
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.
27
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.
28
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.
29
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.
30
Q: Should T-SQL keywords be written in uppercase?
A: While not required, using uppercase for keywords is recommended for better readability and professionalism.
31
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.
32
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.
33
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.
34
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'.
35
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".
36
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.
37
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.
38
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];
39
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.
40
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.
41
Q: What clause is used to filter rows in a T-SQL SELECT statement?
A: The WHERE clause.
42
Q: What is the syntax for using a WHERE clause in a SELECT statement?
A: SELECT , FROM . WHERE = ;
43
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'.
44
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)
45
Q: What do the != and <> operators mean in a WHERE clause?
A: Both operators mean "not equal to" and are interchangeable.
46
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.
47
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'.
48
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.
49
Q: How do you write a one-line comment in T-SQL?
A: Use two dashes (--) before the comment text.
50
Q: How do you write multi-line comments in T-SQL?
A: Enclose the comment with /* at the beginning and */ at the end.
51
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';
52
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.
53
Q: How can you filter data based on numeric values in a WHERE clause?
A: WHERE OrderQty > 10; WHERE OrderQty <= 5;
54
Q: How can string columns be filtered in a WHERE clause?
A: WHERE FirstName <> 'Catherine'; WHERE FirstName > 'M';
55
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.
56
Q: What is the syntax for using BETWEEN in a query?
SELECT , FROM .
WHERE BETWEEN AND ;
57
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.
58
Q: Can BETWEEN be used with string, numeric, and date data types?
A: Yes, BETWEEN can be used with all these data types.
59
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.
60
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.
61
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.
62
Q: How do you find values outside a range using BETWEEN?
A: Use NOT with BETWEEN, e.g., WHERE NOT BETWEEN AND ;
63
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.
64
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'.
65
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.
66
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.
67
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.
68
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.
69
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.
70
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';
71
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';
72
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.
73
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.
74
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.
75
Q: What data type in the example allows accurate filtering on both date and time?
A: DATETIME2(0).
76
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'.
77
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.
78
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.
79
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.
80
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.
81
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.
82
Q: Provide an example of a WHERE clause using both AND and OR.
WHERE FirstName = 'Ken' AND (LastName = 'Myer' OR LastName = 'Meyer');
83
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';
84
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.
85
Q: What is the syntax for using the IN operator?
WHERE IN (, , );
86
Q: How can the IN operator be combined with other predicates?
WHERE FirstName = 'Ken' AND LastName IN ('Myer', 'Meyer');
87
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.
88
Q: Provide an example of a query using NOT IN.
WHERE TerritoryID NOT IN (2, 1, 4, 5);
89
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.
90
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.
91
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.
92
Q: How can the IN operator be used with numeric columns?
WHERE TerritoryID IN (2, 1, 4, 5);
93
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.
94
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.
95
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.
96
Q: What does this query do: WHERE MiddleName IS NULL?
A: It retrieves rows where the MiddleName column has a NULL value.
97
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.
98
Q: How do you replace NULL values in a column with a specific value?
A: Use the ISNULL function, e.g., ISNULL(MiddleName, '').
99
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.
100
Q: How are NULLs handled in comparison operators like < or >?
A: NULLs are excluded from results because these comparisons resolve to UNKNOWN.
101
Q: What clause is used to sort query results?
A: The ORDER BY clause.
102
Q: What is the default sort order in SQL Server?
A: Ascending order.
103
Q: How do you specify descending order in the ORDER BY clause?
A: Use the DESC keyword after the column name.
104
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;
105
Q: What happens to NULLs when sorting in ascending order?
A: NULLs appear first because "nothing comes before something."
106
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.
107
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.
108
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.
109
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;
110
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.
111
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.
112
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.
113
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.
114
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.
115
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.
116
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.
117
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.
118
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.
119
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.
120
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.
121
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.
122
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.
123
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.
124
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.
125
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.

Data Analyst (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions