Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
73 Cards in this Set
- Front
- Back
Which of the following literals are language-dependent for the DATETIME data type? |
Correct Answers: A, B, and C |
|
What are the mandatory clauses in a SELECT query, according to T-SQL? |
Correct Answer: C |
|
What is the difference between the following functions when the TRY is used? |
The difference between the functions without the TRY and their counterparts with the TRY is that those without the TRY fail if the value isn’t convertible, whereas those with the TRY return a NULL in such a case. |
|
What is the difference between the Simple CASE form and the Searched CASE form? |
The simple form compares an input expression to multiple possible scalar WHEN expressions and returns the result expression. |
|
Why is it important to use the appropriate type for attributes? |
Correct Answer: B |
|
Which of the following functions would you consider using to generate surrogate keys? |
Correct Answers: A and B |
|
When concatenating character strings, what is the difference between the plus |
The + operator by default yields a NULL result on NULL input, whereas the CONCAT function treats NULLs as empty strings. |
|
Describe what the following functions are used for and list the ones that are standard. |
All of these functions can be considered abbreviates of the CASE expression. They are used to handle NULL |
|
What are the three T-SQL query clauses that enable you to filter data based on predicates? |
The ON, WHERE, and HAVING clauses. |
|
Which of the following predicates are search arguments (SARG)? (Choose all that apply.) |
Correct Answers: B and E |
|
How can query filters be important to performance and in what ways? |
1.) By filtering rows in the query (as opposed to in the client), you reduce network traffic. |
|
Which of the following literals are language-dependent for the DATETIME data type? (Choose all that apply.) |
Correct Answers: A, B, and C |
|
What is language-neutral and language-dependant mean in reference to Date and Time data? |
language-dependant means that based on the language and/or location a date can be interpreted differently. |
|
When a query doesn’t have an ORDER BY clause, what is the order in which the rows are returned? |
Correct Answer: A |
|
You execute a query with TOP (3) WITH TIES and nonunique ordering. Which of the following options most accurately describes how many rows will be returned? |
Correct Answer: F |
|
Describe the characteristics of the following Joins in terms of matching? |
Cross joins return a Cartesian product of the rows from both sides. |
|
What is the difference between the ON clause and the WHERE clause? |
Correct Answer: D |
|
Which keywords can be omitted in the new standard join syntax without changing the meaning of the join? (Choose all that apply.) |
Correct Answers: C and D |
|
Which syntax is recommended to use for cross joins and inner joins, and why? |
Correct Answer: A |
|
What is the difference between Self-Contained subqueries and Correlated subqueries. |
Self-contained subqueries have no dependency on the outer query. Whereas Correlated subqueries are subqueries where the inner query has a reference to a column from the table in the outer query. |
|
What are table expression and what are the four forms supported by T-SQL. |
Table expressions are named queries. |
|
Which of the table expression are visible only in the scope of the statement that defines them? (Choose all that apply) |
Correct Answer: A and B |
|
Which two table expressions are stored as an object in the database and what is the main difference between them? |
Correct Answer: C and D |
|
What is the difference between the APPLY and JOIN operators? |
With a JOIN operator, both inputs represent static relations. With APPLY, the left side is a static relation, but the right side can be a table expression with |
|
What is the OFFSET-FETCH option used for and how does it work? |
The OFFSET-FETCH option is a filtering option that is used to filter data based on a specified number of rows and ordering. Unlike TOP, it is standard, and also has a |
|
Is the ORDER BY clause required when using OFFSET-FETCH and if so, what role does it play? |
In T-SQL, the OFFSET-FETCH option requires an ORDER BY clause to be present. Also, in T-SQL, contrary to standard SQL—a FETCH clause requires an OFFSET clause to be present. So if you do want to filter some rows but skip none, you still need to specify |
|
Which of the following OFFSET-FETCH options are valid in T-SQL? (Choose all that apply.) |
Correct Answer: A and C |
|
Name the Set Operators which are supported by T-SQL. |
■ UNION |
|
What is the difference between the set operator UNION and the multi-set operator UNION ALL? |
UNION has an implied DISTINCT property, meaning that it does not return duplicate rows. |
|
Describe the Row return actions of eack of the SET Operators: |
UNION: unifies the results of the two input queries with an implied DISTINCT, so that duplicate rows are not returned. |
|
In which operator does the order of the input queries matter? |
Correct Answer: D |
|
What makes a query a grouped query? |
When you use an aggregate function, a GROUP BY clause, or both. |
|
What are the clauses that you can use to define multiple grouping sets in the same query? |
GROUPING SETS - list all grouping sets that you want to define in the query. |
|
What is the difference between the COUNT(*) aggregate function and the COUNT() general set function? |
Correct Answer: A |
|
What types of table compression are available? |
Page or Row compression can be used on a table. Page compression also incorporates row compression. |
|
What is the difference between the results of COUNT(shippeddate) and COUNT(*) in |
The COUNT(shippeddate) ignores NULLs in the shippeddate column, and therefore the counts are less than or equal to those produced by COUNT(*) . |
|
What is the syntax of a Pivot query? |
WITH PivotData AS |
|
What are Window Functions and how are they used? |
A window function is one that can be applied to a partitioned set of rows (known as a window) in order to rank or aggregate values in that partition. |
|
What are the four Ranking Window Functions? |
•ROW_NUMBER: Assigns a sequential number to each row in the result set. |
|
What are the Window Aggregate Functions? |
Window aggregate functions are the same as the group aggregate functions (for example, |
|
What is the name of the key clause in Window Functions? |
The OVER clause |
|
What are the three subclauses supported by the OVER clause? What is their purpose? |
PARTITION BY : Partitions the result set based on one or more columns or expressions. |
|
What do Window Offset Functions do and what are their names? |
Window offset functions let you return a value from a row that's in a certain offset from the current row . |
|
What do the RANK and DENSE_RANK functions compute? |
Correct Answer: B |
|
Why are window functions allowed only in the SELECT and ORDER BY clauses of a |
Correct Answer: A |
|
What is Full-Text search? |
Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. |
|
What are the two kinds of DML triggers supported by SQL Server? |
AFTER: fires after the event it is associated with finishes. Can only be defined on permanent tables. |
|
Syntax of a DML Trigger |
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) |
|
Syntax of a DDL Trigger |
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) |
|
What are the main query clauses specified in the "keyed-in" order - the order in which they are typed? |
1) SELECT |
|
What are the main query clauses ordered in the logical processing order - the conceptual interpretation order? |
1) FROM |
|
How does the HAVING clause filter rows? How is this different from the WHERE clause? |
The HAVING clause filters data based on a predicate but is evaluated per group and so filters the group as a whole. The GROUP BY clause must be used in order to use the HAVING clause. The difference between HAVING and WHERE is that WHERE filters rows and HAVING filters groups. In the conceptual query interpretaion order HAVING is interpreted after GROUP BY, whereas WHERE is interpreted before. |
|
Is the following query valid? Explain. |
Invalid. An Alias created by the SELECT phase isn't even visible to other expressions that appear in the same SELECT list. |
|
What are the relational model's core mathematical foundations? |
Set Theory and predicate logic. |
|
How does T-SQL deviate from the relational model concerning predicate logic? |
When a predicate compares two values, the result evaluates to either true or false. However, SQL implements a general purpose mark called NULL for any kind of missing value. Thus a result can evaluate to a third logical value - unknown. |
|
What are the forms of aliasing an attribute in T-SQL? |
The forms are AS , , and |
|
What two methods can be used in T-SQL to delimit an irregular identifier? |
T-SQL supports both a proprietary way to delimit identifiers by using square brackets [phone number], and the standard form using double quotation marks, as in "phone number". |
|
When should you use unicode datatypes over regular character datatypes? |
If data is in multiple languages and you need to represent only one language besides English in your data, you can benefit from using regular character types, with lower storage requirements. |
|
Describe the identity column property. |
A property that automatically generates keys in |
|
Describe the Sequence object. |
An independent object in the database from which you can |
|
Describe how to generate Nonsequential GUIDS. |
You can generate nonsequential global unique identifiers to be stored in an attribute of a UNIQUEIDENTIFIER type. You can use the T-SQL function NEWID to generate a new GUID. |
|
Describe how to generate Sequential GUIDS. |
You can generate sequential GUIDs within the machine by using the T-SQL function NEWSEQUENTIALID. |
|
Describe the syntax and what the COALESCE expression does. |
COALESCE ( expression [ ,...n ] ) |
|
What is three-valued logic and how does it effect filtering data in a query? |
A predicate evaluates to true or false. SELECT * FROM table WHERE x = 3. However, when NULLS are possible then three-value logic comes into ploay. A predicate can then evalute to true,false and unknown. In this case both false and unknown cases are discarded. |
|
What makes a predicate a search argument and why is this important? |
When a predicate is presented in the form of a search argument (WHERE col1=10), SQL Server is able to efficiently use indexes. If NULLS are involved and they need to be taken into account, then the filtered column may need to be manipulated (WHERE col1=10 OR col1 IS NULL), then SQL Server cannot efficiently use indexes. |
|
What is a Query Hint and what is the syntax for using them? |
Query hints specify that the indicated hints should be used throughout a query. A Query Hint overides the default behavior of the query optimizer in selecting an exceution plan. |
|
What does SET STATISTICS IO do and what is the syntax? |
Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements. |
|
What is a TABLE Hint and what is the syntax for using them? |
Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options. |
|
What is a Hint and what are their categories? |
Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query. |
|
What is a JOIN Hint and what is the syntax for using them? |
The SQL Server query optimizer utilzes Physical Join Operators, which are algorithms that are used in carrying out T-SQL joins. Using a JOIN hint overrides any default the query optimizer might select for a query. |
|
Place the following operators in order of precedence. |
The NOT operator precedes AND and OR, and AND precedes OR. NOTE: parentheses have the highest precedence among ALL operators and can be used to fully control the logical evaluation order that you need. |
|
Describe the actions of the TOP option? What is the syntax? |
With the TOP option, you can filter a requested number or percent of rows from the query |
|
The ORDER BY clause is mandatory when using OFFSET-FETCH because this clause is standard, and standard SQL decided to make the ORDER BY mandatory. Microsoft simply followed the standard. With this being the case, how do you make a query using OFFSET-FETCH nondeterministic (more than one correct result)? |
If you want the ordering to be completely nondeterministic, you can specify ORDER BY (SELECT NULL) this makes it equivalent to not specifying an ORDER BY clause at all. |