Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/73

Click to flip

73 Cards in this Set

  • Front
  • Back
Which of the following literals are language-dependent for the DATETIME data type?
(Choose all that apply.)
A. '2012-02-12'
B. '02/12/2012'
C. '12/02/2012'
D. '20120212'
Correct Answers: A, B, and C
A. Correct: The form '2012-02-12' is language-neutral for the data types DATE, DATETIME2, and DATETIMEOFFSET, but language-dependent for DATETIME and SMALLDATETIME.
B. Correct: The form '02/12/2012' is language-dependent.
C. Correct: The form '12/02/2012' is language-dependent.
D. Incorrect: The form '20120212' is language-neutral.
What are the mandatory clauses in a SELECT query, according to T-SQL?
A. The FROM and SELECT clauses
B. The SELECT and WHERE clauses
C. The SELECT clause
D. The FROM and WHERE clauses
Correct Answer: C
A. Incorrect: The FROM and SELECT clauses are mandatory in a SELECT query according to standard SQL but not T-SQL.
B. Incorrect: The WHERE clause is optional in T-SQL.
C. Correct: According to T-SQL, the only mandatory clause is the SELECT clause.
D. Incorrect: The FROM and WHERE clauses are both optional in T-SQL.
What is the difference between the following functions when the TRY is used?
CAST, CONVERT, PARSE

TRY_CAST, TRY_CONVERT, TRY_PARSE
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.
The following code fails.
SELECT CAST('abc' AS INT);
Conversely, the following code returns a NULL.
SELECT TRY_CAST('abc' AS INT);
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.
SELECT productid, productname, unitprice, discontinued,
CASE discontinued
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END AS discontinued_desc
FROM Production.Products

The searched form uses predicates in the WHEN clause and the first value that evaluates to TRUE determones which WHEN expression is returned.
SELECT productid, productname, unitprice,
CASE
WHEN unitprice < 20.00 THEN 'Low'
WHEN unitprice < 40.00 THEN 'Medium'
WHEN unitprice >= 40.00 THEN 'High'
ELSE 'Unknown'
END AS pricerange
FROM Production.Products;
Why is it important to use the appropriate type for attributes?
A. Because the type of your attribute enables you to control the formatting of the values
B. Because the type constrains the values to a certain domain of supported values
C. Because the type prevents duplicates
D. Because the type prevents NULLs
Correct Answer: B
A. Incorrect: Formatting isn’t a responsibility of the type or the data layer in general; rather, it is the responsibility of the presentation layer.
B. Correct: The type should be considered a constraint because it limits the values allowed.
C. Incorrect: The type itself doesn’t prevent duplicates. If you need to prevent duplicates, you use a primary key or unique constraint.
D. Incorrect: A type doesn’t prevent NULLs. For this, you use a NOT NULL constraint.
Which of the following functions would you consider using to generate surrogate keys?
(Choose all that apply.)
A. NEWID
B. NEWSEQUENTIALID
C. GETDATE
D. CURRENT_TIMESTAMP
Correct Answers: A and B
A. Correct: The NEWID function creates GUIDs in random order. You would consider it when the size overhead is not a major issue and the ability to generate a unique
value across time and space, from anywhere, in random order is a higher priority.
B. Correct: The NEWSEQUENTIALID function generates GUIDs in increasing order within the machine. It helps reduce fragmentation and works well when a single
session loads the data, and the number of drives is small. However, you should carefully consider an alternative using another key generator, like a sequence object,
with a smaller type when possible.
C. Incorrect: There’s no assurance that GETDATE will generate unique values; therefore, it’s not a good choice to generate keys.
D. Incorrect: The CURRENT_TIMESTAMP function is simply the standard version of GETDATE, so it also doesn’t guarantee uniqueness.
When concatenating character strings, what is the difference between the plus
(+) operator and the CONCAT function?
The + operator by default yields a NULL result on NULL input, whereas the CONCAT function treats NULLs as empty strings.

SELECT empid, country, region, city,
country + N',' + region + N',' + city AS location
FROM HR.Employees;
empid country region city location
------ -------- ------- -------- ----------
1 USA WA Redmond USA,WA,Redmond
2 UK NULL London NULL

SELECT empid, country, region, city,
CONCAT(country, N',' + region, N',' + city) AS location
FROM HR.Employees;
empid country region city location
------ -------- ------- ------ ------------
1 USA WA Redmond USA,WA,Redmond
2 UK NULL London UK,London
Describe what the following functions are used for and list the ones that are standard.

COALESCE() , NULLIF() , ISNULL(), IIF(), CHOOSE()
All of these functions can be considered abbreviates of the CASE expression. They are used to handle NULL

CASE
WHEN IS NOT NULL THEN
WHEN IS NOT NULL THEN
ELSE NULL
END
COALESCE() and NULLIF() are standard. IIF and CHOOSE were added to T-SQL to simplify migrations from ACCESS platforms.
What are the three T-SQL query clauses that enable you to filter data based on predicates?
The ON, WHERE, and HAVING clauses.

ON: Inner Joins - filters only rows for which the predicate evaluates to true.
ON: Outer Joins - ON predicate determines which rows from the nonpreserved side get matched to rows from the preserved side—not whether to return the rows from the preserved side.

WHERE: returns rows for which the predicate evaluates to TRUE.

HAVING: uses a predicate but evaluates the predicate per group as opposed to per row.
Which of the following predicates are search arguments (SARG)? (Choose all that apply.)
A. DAY(orderdate) = 1
B. companyname LIKE 'A%'
C. companyname LIKE '%A%'
D. companyname LIKE '%A'
E. orderdate > = '20120212' AND orderdate < '20120213'
F. YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2
Correct Answers: B and E
A. Incorrect: This predicate applies manipulation to the filtered column, and hence isn’t a search argument.
B. Correct: The LIKE predicate is a search argument when the pattern starts with a known prefix.
C. Incorrect: The LIKE predicate isn’t a search argument when the pattern starts with a wild card.
D. Incorrect: The LIKE predicate isn’t a search argument when the pattern starts with a wild card.
E. Correct: Because no manipulation is applied to the filtered column, the predicate is a search argument.
F. Incorrect: This predicate applies manipulation to the filtered column, and hence isn’t a search argument.
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.
2.) When query filters have the predicate set in the form of a search argument, it allows SQL Server to evaluate the option using indexes as opposed to a full table scan.
Which of the following literals are language-dependent for the DATETIME data type? (Choose all that apply.)
A. '2012-02-12'
B. '02/12/2012'
C. '12/02/2012'
D. '20120212'
Correct Answers: A, B, and C
A. Correct: The form '2012-02-12' is language-neutral for the data types DATE,DATETIME2, and DATETIMEOFFSET, but language-dependent for DATETIME and SMALLDATETIME.
B. Correct: The form '02/12/2012' is language-dependent.
C. Correct: The form '12/02/2012' is language-dependent.
D. Incorrect: The form '20120212' is language-neutral.
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.
WHERE orderdate = '02/12/07';
American, this form probably means February 12, 2007
British, this form probably means December 2, 2007
Japanese, this form probably means December 7, 2002
Language-neutral means that the date can only be interpreted one way, for example the form '20070212' is always interpreted as ymd, regardless of your language.
When a query doesn’t have an ORDER BY clause, what is the order in which the rows are returned?
A. Arbitrary order
B. Primary key order
C. Clustered index order
D. Insertion order
Correct Answer: A
A. Correct: Without an ORDER BY clause, ordering isn’t guaranteed and is said to be arbitrary—it’s optimization-dependent.
B. Incorrect: Without an ORDER BY clause, there’s no guarantee for ordering.
C. Incorrect: Without an ORDER BY clause, there’s no guarantee for ordering.
D. Incorrect: Without an ORDER BY clause, there’s no guarantee for ordering.
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?
A. Fewer than three rows
B. Three rows or fewer
C. Three rows
D. Three rows or more
E. More than three rows
F. Fewer than three, three, or more than three rows
Correct Answer: F
A. Incorrect: If there are at least three rows in the query result without TOP, the query will return at least three rows.
B. Incorrect: If there are more than three rows in the result, as well as ties with the third row, the query will return more than three rows.
C. Incorrect: If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are more than three rows in the result, as
well as ties with the third row, the query will return more than three rows.
D. Incorrect: If there are fewer rows than three in the query result without TOP, the query will return only those rows.
E. Incorrect: If there are three rows or less in the query result without TOP, the query won’t return more than three rows.
F. Correct: If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are at least three rows in the result and no ties with the third, the query will return three rows. If there are more than three rows in the result, as well as ties with the third row, the query will return more than three rows.
Describe the characteristics of the following Joins in terms of matching?

Cross Join
Inner Join
Outer Join
Multi-Joins
Cross joins return a Cartesian product of the rows from both sides.

Inner joins match rows based on a predicate and return only matches.

Outer joins match rows based on a predicate and return both matches and nonmatches from the tables marked as preserved.

Multi-join queries involve multiple joins. They can have a mix of different join types. You can control the logical join ordering by using parentheses or by repositioning the
ON clauses.
What is the difference between the ON clause and the WHERE clause?
A. The ON clause uses two-valued logic and the WHERE clause uses three-valued logic.
B. The ON clause uses three-valued logic and the WHERE clause uses two-valued logic.
C. In outer joins, the ON clause determines filtering and the WHERE clause determines matching.
D. In outer joins, the ON clause determines matching and the WHERE clause determines filtering.
Correct Answer: D
A. Incorrect: Both clauses use three-valued logic.
B. Incorrect: Both clauses use three-valued logic.
C. Incorrect: ON determines matching and WHERE determines filtering.
D. Correct: ON determines matching and WHERE determines filtering.
Which keywords can be omitted in the new standard join syntax without changing the meaning of the join? (Choose all that apply.)
A. JOIN
B. CROSS
C. INNER
D. OUTER
Correct Answers: C and D
A. Incorrect: The JOIN keyword cannot be omitted in the new syntax for joins.
B. Incorrect: If the CROSS keyword is omitted from CROSS JOIN, the keyword JOIN alone means inner join and not cross join anymore.
C. Correct: If the INNER keyword is omitted from INNER JOIN, the meaning is retained.
D. Correct: If the OUTER keyword is omitted from LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, the meaning is retained.
Which syntax is recommended to use for cross joins and inner joins, and why?
A. The syntax with the JOIN keyword because it’s consistent with outer join syntax and is less prone to errors.
B. The syntax with the comma between the table names because it’s consistent with outer join syntax and is less prone to errors.
C. It is recommended to avoid using cross and inner joins.
D. It is recommended to use only lowercase characters and omit default keywords, as in join instead of INNER JOIN because it increases energy consumption.
Correct Answer: A
A. Correct: The syntax with the JOIN keyword is consistent with the only standard syntax available for outer joins and is less prone to errors.
B. Incorrect: Outer joins don’t have a standard syntax based on commas.
C. Incorrect: There’s no such recommendation. Cross and inner joins have a reason to exist.
D. Incorrect: There’s no such evidence.
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.

Self-Contained
SELECT productid, productname, unitprice
FROM Production.Products
WHERE supplierid IN
(SELECT supplierid
FROM Production.Suppliers
WHERE country = N'Japan');

Correlated Subquery
SELECT categoryid, productid, productname, unitprice
FROM Production.Products AS P1
WHERE unitprice =
(SELECT MIN(unitprice)
FROM Production.Products AS P2
WHERE P2.categoryid = P1.categoryid);
What are table expression and what are the four forms supported by T-SQL.
Table expressions are named queries.
T-SQL supports four forms of table expressions:
■ Derived tables
■ Common table expressions (CTEs)
■ Views
■ Inline table-valued functions
Which of the table expression are visible only in the scope of the statement that defines them? (Choose all that apply)
A. Derived tables
B. Common table expressions (CTEs)
C. Views
D. Inline table-valued functions
Correct Answer: A and B

Derived tables and CTEs are table expressions that are
visible only in the scope of the statement that defines them. After that statement terminates, the table expression is gone. Hence, derived tables and CTEs are not reusable.

For reusability,you need to store the definition of the table expression as an object in the database, and for this you can use either views or inline table-valued functions.
Which two table expressions are stored as an object in the database and what is the main difference between them?
A. Derived tables
B. Common table expressions (CTEs)
C. Views
D. Inline table-valued functions
Correct Answer: C and D

The main difference between views and inline table-valued functions is that a view doesn’t accept input parameters and an inline table-valued functions does.
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
correlations to elements from the left table.
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
skipping capability, making it useful for ad-hoc paging purposes.

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

The above query defines ordering based on order date descending, followed by order ID descending; it then skips 50 rows and fetches the next 25 rows.

::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}
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
the OFFSET clause with 0 ROWS.

The ORDER BY clause plays two roles: One role is telling the OFFSET-FETCH option which rows it needs to filter. Another role is it's usual role determining presentation ordering in the query.
Which of the following OFFSET-FETCH options are valid in T-SQL? (Choose all that apply.)
A. SELECT … ORDER BY orderid OFFSET 25 ROWS
B. SELECT … ORDER BY orderid FETCH NEXT 25 ROWS ONLY
C. SELECT … ORDER BY orderid OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY
D. SELECT … OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY
Correct Answer: A and C
A. Correct: T-SQL supports indicating an OFFSET clause without a FETCH clause.
B. Incorrect: Contrary to standard SQL, T-SQL does not support a FETCH clause without an OFFSET clause.
C. Correct: T-SQL supports indicating both OFFSET and FETCH clauses.
D. Incorrect: T-SQL does not support OFFSET-FETCH without an ORDER BY clause.
Name the Set Operators which are supported by T-SQL.
■ UNION
■ INTERSECT
■ EXCEPT
■ UNION ALL (a multi-set operator)
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.

UNION ALL operator unifies the results of the two input queries, but doesn’t try to eliminate duplicates.
Describe the Row return actions of eack of the SET Operators:

■ UNION
■ INTERSECT
■ EXCEPT
■ UNION ALL (a multi-set operator)
UNION: unifies the results of the two input queries with an implied DISTINCT, so that duplicate rows are not returned.

INTERSECT: returns only distinct rows that are common to both sets.

EXCEPT: returns distinct rows that appear in the first
query but not the second.

UNION ALL: unifies the results of the two input queries, but doesn’t try to eliminate duplicates.
In which operator does the order of the input queries matter?
A. UNION
B. UNION ALL
C. INTERSECT
D. EXCEPT
Correct Answer: D
A. Incorrect: With UNION, the order of the inputs doesn’t matter.
B. Incorrect: With UNION ALL, the order of the inputs doesn’t matter.
C. Incorrect: With INTERSECT, the order of the inputs doesn’t matter.
D. Correct: With EXCEPT, the order of the inputs matters.
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.

CUBE - accepts a list of expressions as inputs and defines all possible grouping sets that can be generated from the inputs—including the empty grouping set.

ROLLUP - when the elements form a hierarchy, you use the ROLLUP
What is the difference between the COUNT(*) aggregate function and the COUNT() general set function?
A. COUNT(*) counts rows; COUNT() counts rows where is not NULL.
B. COUNT(*) counts columns; COUNT() counts rows.
C. COUNT(*) returns a BIGINT; COUNT() returns an INT.
D. There’s no difference between the functions.
Correct Answer: A
A. Correct: The COUNT(*) function doesn’t operate on an input expression; instead, it counts the number of rows in the group. The COUNT() function operates on an expression and ignores NULLs. Interestingly, COUNT() returns 0 when all inputs are NULLs, whereas other general set functions like MIN,
MAX, SUM, and AVG return a NULL in such a case.

B. Incorrect: COUNT(*) counts rows.

C. Incorrect: COUNT(*) returns an INT.

D. Incorrect: Clearly, there is a difference between the functions in the treatment of NULLs.
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

SELECT shipperid,
COUNT(*) AS numorders,
COUNT(shippeddate) AS shippedorders,
FROM Sales.OrderValues
GROUP BY shipperid;
.
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
(
SELECT
[1st pivoted column] , -- grouping column
[2nd pivoted column], -- spreading column
[last pivoted column] -- aggregation column
FROM [database_table])
SELECT [1st pivoted column], [1], [2], [3]
FROM PivotData
PIVOT(SUM(]last pivoted column]) FOR shipperid IN ([1],[2],[3]) ) AS P;

SUM = any group aggregate function.
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.

Window functions have nothing to do with Microsoft Windows, but they do provide a way to work with data grouped into logical windows.
What are the four Ranking Window Functions?
•ROW_NUMBER: Assigns a sequential number to each row in the result set.

•RANK: Ranks each row in the result set. If values in the ranking column are the same, they receive the same rank. However, the next number in the ranking sequence is skipped.

•DENSE_RANK: Ranks each row in the result set. If values in the ranking column are the same, they receive the same rank. The next number in the ranking sequence is then used to rank the row or rows that follow.

•NTILE: Divides the result set into the number of groups specified as an argument to the function. A group number is then assigned to each row identifying which group the row belongs to
What are the Window Aggregate Functions?
Window aggregate functions are the same as the group aggregate functions (for example,
SUM, COUNT, AVG, MIN, and MAX), except window aggregate functions are applied to a
window of rows defined by the OVER clause.
What is the name of the key clause in Window Functions?
The OVER clause

The key to understanding SQL Server window functions is in the OVER clause, which can be defined on any window function used in a query’s select list. The clause determines how to partition and sort a result set in order to apply the window function. In fact, you can think of a window function as one that supports the OVER clause.

OVER () AS
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.

ORDER BY : Sorts one or more columns or expressions within the partition. The ORDER BY clause applies strictly within the context of the partition.

ROWS|RANGE: Further defines how the data is displayed within the partition.
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 .

LAG function returns a value from a row in the window partition that, based on the window ordering, is the specified number of rows before the current row.

LEAD function returns a value from a row in the window partition that, based on the window ordering, is the specified number of rows after the current row.

By default, the LAG and LEAD functions assume an offset of one row if an explicit offset wasn't specified.

FIRST_VALUE and LAST_VALUE return the requested value from the first and last rows, respectively, from the applicable window frame.
What do the RANK and DENSE_RANK functions compute?
A. The RANK function returns the number of rows that have a lower ordering value (assuming ascending ordering) than the current; the DENSE_RANK function returns the number of distinct ordering values that are lower than the current.
B. The RANK function returns one more than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one more than the number of distinct ordering values that are lower than the current.
C. The RANK function returns one less than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one less than the number of distinct ordering values that are lower than the current.
D. The two functions return the same result unless the ordering is unique.
Correct Answer: B
A. Incorrect: These definitions are one less than the correct ones.
B. Correct: These are the correct definitions.
C. Incorrect: These definitions are two less than the correct ones.
D. Incorrect: The opposite is true—the two functions return the same result when the ordering is unique.
Why are window functions allowed only in the SELECT and ORDER BY clauses of a
query?
A. Because they are supposed to operate on the underlying query’s result, which is
achieved when logical query processing gets to the SELECT phase.
B. Because Microsoft didn’t have time to implement them in other clauses.
C. Because you never need to filter or group data based on the result of window functions.
D. Because in the other clauses, the functions are considered door functions (also known as backdoor functions).
Correct Answer: A
A. Correct: Window functions are supposed to operate on the underlying query’s result
set. In terms of logical query processing, this result set is reached in the SELECT phase.
B. Incorrect: Standard SQL defines this restriction, so it has nothing to do with Microsoft’s time constraints.
C. Incorrect: There are practical reasons to want to filter or group data based on the results of window functions.
D. Incorrect: There are neither door functions nor backdoor functions in SQL.
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.

Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.
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.

INSTEAD OF: fires instead of the event it is associated with. Can be defined on permanent tables and views.
Syntax of a DML Trigger
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME }

::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

FOR is for Server Scope
Syntax of a DDL Trigger
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

FOR is for Server Scope
What are the main query clauses specified in the "keyed-in" order - the order in which they are typed?
1) SELECT
2) FROM
3) WHERE
4) GROUP BY
5) HAVING
6) ORDER BY
What are the main query clauses ordered in the logical processing order - the conceptual interpretation order?
1) FROM
2) WHERE
3) GROUP BY
4) HAVING
5) SELECT
6) ORDER BY
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.

SELECT empid, country, YEAR(hiredate) AS yearhired, yearhired - 1 AS prevyear
FROM HR.Employees;
Invalid. An Alias created by the SELECT phase isn't even visible to other expressions that appear in the same SELECT list.

The reason that this isn’t allowed is that, conceptually, T-SQL evaluates all expressions that appear in the same logical query processing phase in an all-at-once manner. Note the use of the word conceptually. SQL Server won’t necessarily physically process all expressions at the
same point in time, but it has to produce a result as if it did. This behavior is different than many other programming languages where expressions usually get evaluated in a left-toright order, making a result produced in one expression visible to the one that appears to its
right. But T-SQL is different.
What are the relational model's core mathematical foundations?
Set Theory and predicate logic.

By a "set", we mean any collection into a whole of unordered collection of unique, non-duplicated items.

A Predicate is an expression that when attributed to some object, makes a proposition either True or False.
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.
Because of this, every piece of code written with T-SQL where NULLs are possible in the data - one needs to understand the treatment of those NULLs in the query.
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.

When data is international, or your application natively works with Unicode data, you should use Unicode data types so you don’t lose information.
Describe the identity column property.
A property that automatically generates keys in
an attribute of a numeric type with a scale of 0; namely, any integer type (TINYINT,
SMALLINT, INT, BIGINT) or NUMERIC/DECIMAL with a scale of 0.
Describe the Sequence object.
An independent object in the database from which you can
obtain new sequence values. Like identity, it supports any numeric type with a scale of 0. Unlike identity, it’s not tied to a particular column; instead, as mentioned, it is an
independent object in the database.
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 ] )

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

Can also be used to substitute a NULL for something else: COALESCE(region, '')
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.

Query hints are specified as part of the OPTION clause.
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
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.

SET STATISTICS IO { ON | OFF }

SET STATISTICS IO ON;
GO
SELECT * FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS IO OFF;
RESULTS
Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,lob read-ahead reads 0.
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.
Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.

FROM t WITH (TABLOCK, INDEX(myindex))
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.

There are three types of Hints:
JOIN Hint - hint forces the type of join algorithm that is used. Can be used in SELECT,UPDATE and DELETE

QUERY Hint - used when certain kind of logic has to be applied to a whole query. Can be used in SELECT,UPDATE,DELETE, ONSERT and MERGE.

TABLE Hint - used when certain kind of locking mechanism of tables has to be controlled. Can be used in SELECT,UPDATE,DELETE, ONSERT and MERGE.
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.

JOIN hints are specified in the FROM clause of the DML statement (DELETE, SELECT or UPDATE).

DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER LOOP JOIN Sales.SalesPerson AS sp
ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
Place the following operators in order of precedence.

OR
AND
NOT
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.

NOT
AND
OR
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
result based on indicated ordering.

SELECT TOP (3) or SELECT TOP (3) PERCENT
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.