Query Data with Advanced T-SQL Components

Question 1

What are 4 forms of table expressions?

Answer 1

Derived tables, common table expressions (CTEs), views, and inline table-valued functions

Question 2

Because a table expression is supposed to represent a relation, the inner query defining it needs to be relational. What are 3 requirements to satisfy this?

Answer 2

1. All columns returned by the inner query must have names
2. All column names must be unique
3. The inner query is not allowed to have an ORDER BY clause

Question 3

Normally the inner query of a table expression is not allowed to have an ORDER BY clause. What is the exception?

Answer 3

The exception is if you use the TOP or OFFSET-FETCH option in the inner query. In this case, the ORDER BY clause serves a meaning that is not related to presentation ordering, rather it’s part of the filter’s specification

Question 4

When should you use a temporary table/table variable over a table expression?

Answer 4

When you need to persist the result of a query for further processing.

Question 5

Is the result set of a view stored in the database?

Answer 5

No, only the view’s definition is stored in the database

Question 6

Syntax to create a view

Answer 6

CREATE VIEW viewname
AS
SELECT col1, col2 FROM table1;

Question 7

What is the main difference between VIEWS and inline table-valued functions?

Answer 7

Views do not accept input parameters but inline table-valued functions do.

Question 8

Syntax to create an inline table-valued function

Answer 8

CREATE FUNCTION fxname(@parameter datatype)
RETURNS TABLE
AS
RETURN
SELECT col1, col2
FROM table1
WHERE parameter = @parameter

Question 9

How do you retrieve data from an inline table-valued function? (Syntax)

Answer 9

SELECT alias.col1, alias.col2, alias.col3
FROM schema.fxname(parameter) as alias

Notes:

• Always use 2-part function name (schema and function)
• Always alias the function

Question 10

True or False. Derived tables:

1. Must have an alias
2. Can be referred to multiple times in the same query
3. Can use internal or external aliases for columns
4. Can refer to parameters or variables
5. Cannot be nested within other derived tables

Answer 10

1. Must have an alias - True
2. Can be referred to multiple times in the same query - False
3. Can use internal or external aliases for columns - True
4. Can refer to parameters or variables - True
5. Cannot be nested within other derived tables - False

Question 11

In which clause do you use derived tables?

Answer 11

In the FROM clause

Question 12

Common table expression (CTE) syntax

Answer 12

WITH ctealias AS

( innerquery )

outerquery

Question 13

Syntax for multiple CTEs

When can you refer to a another CTE within a CTE?

Answer 13

WITH C1 AS

( SELECT .. FROM T1 WHERE… ),

C2 AS

( SELECT … FROM C1 WHERE …)

outerquery;

Each CTE can refer to a previously defined CTE

Question 14

The following is an example of a recursive CTE. Explain what it’s doing.

WITH EmpsCTE AS

( SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 9
UNION ALL
SELECT M.empid, M.mgrid, M.firstname, M.lastname
FROM EmpsCTE AS S
JOIN HR.Employees AS M
ON S.mgrid = M.empid )

SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;

Answer 14

The first query is the “anchor” member and returns a valid relational result. The anchor member is invoked only once. The second query acts as the “recursive” member. It has to have a reference to the CTE name. It is invoked repeatedly until it returns an empty result set. Think of it as if the results of the second query are added to the first in each repetition. Here, in each round, the recursive member joins the previous result set with the HR.Employees table to return the direct manager of the employee from the previous round. The recursive query stops when it doesn’t find a manager.

Question 15

What does APPLY do?

Difference between CROSS APPLY and OUTER APPLY

Answer 15

APPLY evaluates the left input first, and for each of the left rows, it applies the table expression provided as the right input. The right input can be a derived table query or table function. The left input provides info to the right input.

CROSS APPLY does not return the left row if the right table expression returns an empty set, but OUTER APPLY does

Question 16

Syntax for window functions

Answer 16

Fx OVER(PARTITION BY col1

ORDER BY col2

ROWS BETWEEN UNBOUNDED PRECEDING//FOLLOWING AND CURRENT ROW)

Note: instead of UNBOUNDED can also have n rows PRECEDING/FOLLOWING

Question 17

In which clauses can you use window functions?

Answer 17

SELECT and ORDER BY

Question 18

What are 4 window ranking functions and what do they do?

Answer 18

ROW_NUMBER: creates unique incrementing integers starting with 1 within the window partition based on the window ordering

RANK: rank values based on window ordering. If two values tie, they’ll receive same rank, e.g. 2. Then it’ll skip a value for the next rank, e.g. 1, 2, 2, 4

DENSE RANK: same as RANK but doesn’t have gaps

NTILE: Can arrange rows within partition in a requested number of equally sized tiles

Question 19

What are 4 window offset functions and how do they work?

Answer 19

LAG(col, numrows): returns an element from the row in the current partition that is a requested number of rows before the current row.

LEAD(col, numrows): returns an element from the row in the current partition that is a requested number of rows after the current row.

FIRST_VALUE(col): returns a value expression from the first row in the window frame

LAST_VALUE(col): returns a value expression from the last row in the window frame

Question 20

What 3 elements do you need to pivot data?

Answer 20

1. What do you want to see on rows? i.e. on rows or grouping element
2. What do you want to see on columns? i.e. on cols or spreading element
3. What do you want to see in the intersection of each distinct row and column value? i.e. data or aggregation element.

Question 21

Syntax to pivot data

Answer 21

WITH PivotData AS

( SELECT groupingcol, spreadingcol, aggregationcol

FROM tablename )

SELECT (select list)

FROM PivotData

PIVOT( aggregatefx(aggregation col)

FOR spreadingcol IN (distinct spreading values)) AS alias.

Notes:

1. You should define a table expression that returns the three elements involved in pivoting.
2. The PIVOT operator returns a table result. Remember to alias it.

Question 22

In some cases you might get NULLs in the pivoted table. What should you do if you want to return something else instead of a NULL in those cases?

Answer 22

Use the ISNULL or COALESCE function in the outer query’s select list. e.g.

WITH PivotData AS

( SELECT custid, shipperid, freight

FROM Sales.Orders )

SELECT custid,

ISNULL([1], 0.00) as [1],

ISNULL([2], 0.00) as [2],

ISNULL([3], 0.00) as [3]

FROM PivotData

PIVOT(SUM(freight) FOR shipperid IN ([1], [2], [3])) as P;

Question 23

Why should you define a table expression that returns the 3 elements involved in pivoting instead of querying the underlying table directly?

Answer 23

Note that you don’t specify the grouping element in the PIVOT operator. It’s determined by elimination. So, if the table has more than the 3 cols, all extra cols will be considered a grouping element

Question 24

What are 4 limitations of the PIVOT operator?

Answer 24

1. The aggregation and spreading elements cannot directly be results of expressions; instead, they must be column names from the queried table. You can, however, apply expressions in the query defining the table expression, assign aliases to those expressions, and then use the aliases in the PIVOT operator
2. The COUNT(*) function isn’t allowed as the aggregate function. You need to use COUNT(col).
3. Limited to using only one aggregate function
4. The IN clause of the PIVOT operator accepts a static list of spreading values. It doesn’t support a subquery as input.

Question 25

Syntax to unpivot data

Answer 25

SELECT col1, namescol, valuescol FROM sourcetable UNPIVOT(valuescol FOR namescol IN (name1, name2)) as alias Notes: - The namescol refers to the columns that you're trying to get back into rows. - valuescol are the values in the table. - You need to come up with your own labels for the namescol and valuescol

Question 26

True or False. UNPIVOT filters out rows with NULLs

Answer 26

True

Question 27

GROUPING SETS syntax

Answer 27

SELECT col1, col2, COUNT(\*) AS numorders FROM Sales.Orders GROUP BY GROUPING SETS ((col1, col2), (col1), (col2), ()); Note: the empty grouping sets aggregates all rows.

Question 28

What's the difference between the CUBE and ROLLUP clauses?

Answer 28

CUBE defines all possible grouping sets from the inputs, e.g. SELECT col1, col2, COUNT(\*) as numorders FROM Sales.Orders GROUP BY CUBE (col1, col2); It will define four grouping sets: (col1, col2), (col1), (col2), () The ROLLUP clause forms grouping sets based on a hierarchy, e.g. SELECT shipcountry, shipregion, shipcity, COUNT(\*) AS numorders FROM Sales.Orders GROUP BY ROLLUP(shipcountry, shipregion, shipcity); It will define four grouping sets: (shipcountry, shipregion, shipcity), (shipcountry, shipregion), (shipcountry) ()

Question 29

When is GROUPING function useful? What does it do? How is GROUPING\_ID different?

Answer 29

The GROUPING function is useful when you're doing multiple grouping sets and need to figure out whether a NULL means it was aggregated over that column or it there was a NULL in the data. The GROUPING function returns 1 when the element comes from an aggregate and 0 when it doesn't. For example, GROUPING(col1) will return a 1 when the aggregate is done over the whole column and a 0 when the aggregate is done within the categories in col1. GROUPING\_ID returns an integer representing a bitmap. The position of each column determines the value, e.g. for 4 columns: 8 4 2 1. If you had 3 cols and GROUPING would return 1 1 1, GROUPING\_ID would return 1+2+4 = 7. If GROUPING would return 0 1 1, GROUPING\_ID would return 1+2+0 = 3.

Question 30

What are 7 elements required to mark a table as temporal? Syntax inside CREATE TABLE statement particular to temporal table

Answer 30

1. A primary key constraint 2. Two DATETIME2 columns to store the start and end of the validity period of the row 3. The start column needs to be marked with the clause GENERATED ALWAYS AS ROW START. 4. The end column needs to be marked with the clause GENERATED ALWAYS AS ROW END. 5. The designation of the pair of columns that store the row's validity period with the clause PERIOD FOR SYSTEM\_TIME (startcol, endcol) 6. The table option SYSTEM\_VERSIONING needs to be set to ON 7. A linked history table, which SQL Server can create for you CREATE TABLE tablename ( col1 datatype NOT NULL CONSTRAINT PK\_tablename PRIMARY KEY(col1), ...more cols.. validfrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, validto DATETIME2(3) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM\_TIME (validfrom, validto) ) WITH (SYSTEM\_VERSIONING = ON (HISTORY\_TABLE = histtablename));

Question 31

When you modify a temporal table, in what time zone will change times be recorded? If you apply multiple changes in a transaction, will each change have a different change time? When you insert rows into a temporal table, SQL Server sets the start column to the transactions' start time and the end time to \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_.

Answer 31

UTC time zone If you apply multiple changes in a transaction, the transaction start time is considered the effective change time for all changes in the transaction. When you insert rows into a temporal table, SQL Server sets the end time to the maximum possible point in time in the data type, e.g. 9999-12-31 23:59:59.999.

Question 32

When you insert data in an empty temporal table, what happens in the history table?

Answer 32

Nothing. It's still empty.

Question 33

What happens in the history table when you delete rows from a temporal table?

Answer 33

The deleted row appears in the history table with the end column set to the start time of the transaction that deleted the row.

Question 34

An update is handled as a delete plus insert. Explain what this means in terms of what happens in the temporal and history tables.

Answer 34

The temporal table will have the new state of the modified rows with the start column set to the change time, and the history table will have the old state of the modified rows with the end column set to the change time.

Question 35

What will be the validfrom and validto times in the history table when you update a row in the temporal table several times within the same transaction?

Answer 35

The in-between changes will have a degenerate interval as the validity period where the validfrom value will be equal to the validto value (which will be the start time of the transaction).

Question 36

Syntax to query a temporal table using the FOR SYSTEM\_TIME AS OF syntax What will the results of querying the temporal table look like?

Answer 36

SELECT col1, col2 FROM tablename FOR SYSTEM\_TIME AS OF '20151101 14:06:00.000' This will give you the state of the table at the time specified.

Question 37

What will happen to rows with degenerate intervals as the validity period when you query the temporal table using the FOR SYSTEM\_TIME clause?

Answer 37

SQL Server will discard those rows

Question 38

Syntax to query a temporal table using the FOR SYSTEM\_TIME FROM syntax and explain how it works

Answer 38

SELECT col1, col2 FROM tablename FOR SYSTEM\_TIME FROM 'startdatetime' TO 'enddatetime'; You get all rows that have a validity period that intersects with the input period, exclusive of the two input delimiters. You get rows with a validfrom datetime before the end datetime input and a validto datetime after the start datetime input.

Question 39

Syntax to query a temporal table using the FOR SYSTEM\_TIME BETWEEN syntax and explain how it works

Answer 39

SELECT col2, col2 FROM tablename FOR SYSTEM\_TIME BETWEEN 'startdatetime' AND 'enddatetime'; You get rows with a validfrom datetime before or equal to the end datetime input and a validto datetime after the start datetime input.

Question 40

Syntax to query a temporal table using the FOR SYSTEM\_TIME CONTAINED IN syntax and explain how it works

Answer 40

SELECT col2, col2 FROM tablename FOR SYSTEM\_TIME CONTAINED IN('startdatetime', 'enddatetime); This will return rows with a validity period that is entirely contained within the input period, inclusive of both input delimiters. You get rows with a validfrom \>= startdatetime and validto \<= enddatetime.

Question 41

What does the FOR SYSTEM\_TIME ALL output when you use it to query a temporal table?

Answer 41

It returns all row versions from both the temporal and history tables, excluding degenerate intervals.

Question 42

Syntax for XML tags. Syntax for XML tags when an element has no nested elements.

Answer 42

Question 43

True or False: XML is case sensitive

Answer 43

True

Question 44

How do you use the following special characters in the values of your XML document? &, ", \< , \>, ' What's another alternative?

Answer 44

Question 45

What's the different between and XML document and an XML fragment?

Answer 45

An XML document has a single root node. Without the root node, you get an XML fragment.

Question 46

Which of the following is an example of an attribute-centric XML presentation and which is an element-centric XML presentation?

Answer 46

First one is attribute-centric: Elements have attributes. Attributes can have their own names and their values are enclosed in quotes. Second one is element-centric: Every attribute is a nested element of the original element.

Question 47

What is a namespace? How do you define namespaces and aliases in a quey that returns XML?

Answer 47

A namespace is a set of names where all the names are unique WITH XMLNAMESPACE('uri' AS alias) Place it before the SELECT statement

Question 48

Syntax for creating XML from a query result using the RAW option. What does this produce? How do you make it element-centric?

Answer 48

SELECT ... FROM ... FOR XML RAW ('rowname'), ROOT ('rootname'); Notes: - rowname is optional to name each row. By the defaults rows will be named "row" - ROOT adds a root element The raw mode generates a single XML element for each row in the result set returned by the query. Every row converts to a single element named row, and columns translate to the attributes of this element. To make it element-centric: FOR XML RAW ('rowname'), ROOT ('rootname'), ELEMENTS;

Question 49

What will be the structure of the XML output of the following query? SELECT Employee.EmployeeID, ContactInfo.FirstName, ContactInfo.MiddleName, ContactInfo.LastName FROM HumanResources.Employee AS Employee INNER JOIN Person.Contact AS ContactInfo ON ContactInfo.ContactID = Employee.ContactID WHERE ContactInfo.FirstName = 'Rob' FOR XML AUTO, ROOT ('Employees');

Answer 49

Note that the ContactInfo element is a child element of Employee. This is because the first column in the SELECT list comes from the Employee table followed by columns from the ContactInfo table.

Question 50

Is the default XML output of FOR XML AUTO attribute-centric or element-centric? How do you change it?

Answer 50

attribute-centric FOR XML AUTO, ELEMENTS;

Question 51

Where will the column FullName appear in the XML output of the following query? SELECT Employee.EmployeeID, (ContactInfo.FirstName + ' ' + ContactInfo.LastName) AS FullName, ContactInfo.EmailAddress FROM HumanResources.Employee AS Employee INNER JOIN Person.Contact AS ContactInfo ON ContactInfo.ContactID = Employee.ContactID WHERE ContactInfo.FirstName = 'Rob' FOR XML AUTO, ROOT ('Employees'), ELEMENTS XSINIL; What is the general rule for columns that are not associated with a table in the FROM clause?

Answer 51

Because the FullName column appears in the SELECT list after the EmployeeID column, the FullName column is added as a child element of Employee. In general, columns that are not associated with a table in the FROM clause (like calculated columns) are nested at the deepest level wherever they appear.

Question 52

What does the XML output of the following query look like? SELECT EmployeeID, LoginID, (SELECT FirstName, LastName FROM Person.Contact AS EmployeeName WHERE EmployeeName.ContactID = Employee.ContactID FOR XML AUTO, TYPE, ELEMENTS) FROM HumanResources.Employee AS Employee WHERE EmployeeID = 168 FOR XML AUTO; What is the TYPE option in the subquery for?

Answer 52

The output is a mix between attribute-centric and element-centric. The TYPE option is necessary to specify that the data returned by the subquery be returned as the XML type. You must include the TYPE option to preserve the data as XML in the outer SELECT statement.

Question 53

When you use FOR XML AUTO, how should you order the columns in the SELECT list?

Answer 53

You should order the columns following a one-to-many relationship. For example, a customer can have many orders, so you should have customer columns before order columns in the query.

Question 54

It's good to include an _______ clause when you're using FOR XML AUTO in order to guarantee the order of returned rows.

Answer 54

ORDER BY

Question 55

By default, no elements are created for a column whose value is null. How can you override this?

Answer 55

By adding the XSINIL keyword to the ELEMENTS option eg. FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

Question 56

What are the 4 FOR XML modes?

Answer 56

FOR XML RAW FOR XML AUTO FOR XML EXPLICIT FOR XML PATH

Question 57

By default, FOR XML PATH returns attribute-centric or element-centric results? By default, FOR XML PATH elements are defined based on column names. You can modify the default behavior by using what 2 things?

Answer 57

element-centric You can modify the default behavior by using the at (@) symbol to define attributes or the forward slash (/) to define the hierarchy.

Question 58

What will the following query return? ## Footnote SELECT e.EmployeeID AS "@EmpID", c.FirstName, c.MiddleName, c.LastName FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID WHERE c.FirstName = 'Rob' FOR XML PATH ('Employee'), ROOT ('Employees');

Answer 58

Question 59

What will be the result of the following query? ## Footnote ELECT e.EmployeeID AS "@EmpID", c. FirstName AS "EmployeeName/FirstName", c. MiddleName AS "EmployeeName/MiddleName", c. LastName AS "EmployeeName/LastName" FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID WHERE c.FirstName = 'Rob' FOR XML PATH ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;

Answer 59

Question 60

What must you first call to write queries against an XML document by using OPENXML? What does it do?

Answer 60

You must first call sp\_xml\_preparedocument. This parses the XML document and returns a handle to the parsed document that is ready for consumption. The parsed document is a document object model (DOM) tree representation of various nodes in the XML document. The document handle is passed to OPENXML. OPENXML then provides a rowset view of the document, based on the parameters passed to it.

Question 61

After you shred an XML document using OPENXML, you must remove the DOM presentation by using which system stored procedure?

Answer 61

sys.sp\_xml\_removedocument

Question 62

Example of using OPENXML to shred an XML

Answer 62

Question 63

What is XQuery? Is XQuery case-sensitive?

Answer 63

It's a standard language to query XML instances and return XML output. Yes, XQuery is case-sensitive.

Question 64

Write an XQuery FLWOR expression to get all book titles of books that cost more than $30 from XML contained in @x. Order by book title.

Answer 64

SELECT @x.query( 'for $x in /bookstore/book where $x/price\>30 order by $x/title return $x/title')

Question 65

What are 7 types of nodes in XQuery?

Answer 65

Element, attribute, text, namespace, processing-instruction, comment, and document (root) nodes.

Question 66

Syntax for if else statement in XQuery by itself and after return

Answer 66

SELECT @x.query(' if (expression1 ) then expression2 else expression3 ') Note: parentheses around the if expression are required. else is required, but it can be just else (). SELECT @x.query(' for $variable in xpathexpression return if ( expression1 ) then expression2 else () ')

Question 67

Two ways to declare namespace with a prefix when using XQuery Two ways to declare a namespace without a prefix when using XQuery

Answer 67

With prefix: ## Footnote SELECT CatalogDescription.query(' **declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";** /PD:ProductDescription/PD:Summary ') as Result FROM Production.ProductModel where ProductModelID=19; **WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS PD)** SELECT CatalogDescription.query(' /PD:ProductDescription/PD:Summary ') as Result FROM Production.ProductModel where ProductModelID=19; Without prefix: SELECT CatalogDescription.query(' **declare default element namespace "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";** /ProductDescription/Summary ') as Result FROM Production.ProductModel WHERE ProductModelID=19 **WITH XMLNAMESPACES (DEFAULT 'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription')** SELECT CatalogDescription.query(' /ProductDescription/Summary ') as Result FROM Production.ProductModel WHERE ProductModelID=19

Question 68

What are the components of a Qname?

Answer 68

A namespace prefix and a local name, except for XQuery variables, which don't have a prefix.

Question 69

How do you add comments in XQuery?

Answer 69

use (: :) e.g. SELECT Instructions.query(' (: declare prefix and namespace binding in the prolog. :) declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; (: Following expression retrieves the element children of the element. :)
/AWMI:root/AWMI:Location
') as Result
FROM Production.ProductModel
where ProductModelID=7;

Question 70

What does the following XQuery return?

Answer 70

The boolean functions true() and false() can be used to represent boolean values. The direct element constructor specifies an expression in curly braces. This is replaced by its value in the resulting XML.

Question 71

How do you refer to SQL variables in XQuery?

Answer 71

You can use the sql:variable() extension function to refer to SQL variables

Question 72

What do the following XPath expressions do? nodename / // . .. @

Answer 72

nodename: Selects all nodes with the name "nodename" /: Selects from the root node //: Selects nodes in the document from the current node that match the selection no matter where they are .: Selects the current node ..: Selects the parent of the current node @: Selects attributes

Question 73

What do the following XPath expressions select? bookstore /bookstore bookstore/book //book bookstore//book //@lang

Answer 73

bookstore : Selects all nodes with the name "bookstore" /bookstore : Selects the root element bookstore bookstore/book : Selects all book elements that are children of bookstore //book : Selects all book elements no matter where they are in the document bookstore//book : Selects all book elements that are descendant of the bookstore element, no matter where they are under the bookstore element //@lang : Selects all attributes that are named lang

Question 74

What do the following XPath expressions select? /bookstore/book[1] /bookstore/book[last()] //title[@lang] //title[@lang='en'] /bookstore/book[price\>35.00]/title

Answer 74

/bookstore/book[1] : Selects the first book element that is the child of the bookstore element /bookstore/book[last()] : Selects the last book element that is the child of the bookstore element //title[@lang] : Selects all the title elements that have an attribute named lang //title[@lang='en'] : Selects all the title elements that have a "lang" attribute with a value of "en" /bookstore/book[price\>35.00]/title : Selects all the title elements of the book elements of teh bookstore element that have a price element with a value greater than 35.00

Question 75

Path expression syntax for a location step

Answer 75

axisname::nodetest[predicate]

Question 76

Which function do you use to retrieve the atomic value of an element in XQuery?

Answer 76

data() function e.g.

Question 77

How do you add multiple iterator variable in an XQuery FLWOR statement?

Answer 77

for $var1 in expression, $var2 in expression Note: var2's expression can refer to $var1

Question 78

What's the difference between an existential quantifier and a universal quantifier? What is the syntax for quantified expressions in XQuery?

Answer 78

Existential quantifier: Given two sequences, if any item in the first sequence has a match in the second sequence, based on the comparison operator that is used, the returned value is True. Universal quantifier: Given two sequences, if every item in the first sequence has a match in the second sequence, the returned value is True. some / every variable in expression1 satisfies expression2 e.g. SELECT Instructions.query(' declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; if (every $WC in //AWMI:root/AWMI:Location satisfies $WC/@LocationID) then All work centers have workcenterLocation ID else Not all work centers have workcenterLocation ID ') as Result FROM Production.ProductModel where ProductModelID=7

Question 79

instance of operator syntax. What does it do?

Answer 79

expression instance of SequenceType[Occurrence indicator] It checks whether an expression is a certain sequence type. Occurrence indicator can either be ? or omitted. The ? occurrence indicator indicates that Expression can return zero or one item. If the ? occurrence indicator is specified, instance of returns True when the Expression type matches the specified SequenceType, regardless of whether Expression returns a singleton or an empty sequence. If the ? occurrence indicator is not specified, sequence of returns True only when the Expression type matches the Type specified and Expression returns a singleton. e.g. declare @x xml set @x='' select @x.query('125 instance of xs:integer')

Question 80

cast as operator syntax

Answer 80

expression cast as AtomicType? e.g. declare @x xml set @x='' select @x.query('"2" cast as xs:integer?') You can also use the atomic type constructor functions. For example, instead of using the cast as operator, "2" cast as xs:integer?, you can use the xs:integer() constructor function, as in the following example: declare @x xml set @x='' select @x.query('xs:integer("2")')

Question 81

What can you use to refer to a SQL column in an XQuery?

Answer 81

sql:column("columname")

Question 82

Given the code in the image, what will the following return? select @x.query('string(/)')

Answer 82

This is a comment 10 just text 20

Question 83

Difference between data(), string(), and text() functions in untyped and typed XML. Use the image as an example

Answer 83

Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age). Typed XML: The expression /age/text() returns a static error for any simple typed element. On the other hand, fn:data(/age) returns integer 12. The fn:string(/age) yields the string "12".

Question 84

Given the following XML, use the value() method to extract the product ID

Answer 84

SET @ProdID = @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' ) SELECT @ProdID Note: Although there is only one ProductID attribute in the XML instance, the static typing rules require you to explicitly specify that the path expression returns a singleton. Therefore, the additional [1] is specified at the end of the path expression.

Question 85

Rewrite the following using the exist() method ## Footnote CREATE TABLE T (c1 int, c2 varchar(10), c3 xml) GO SELECT c1, c2, c3 FROM T WHERE c3.value( '/root[1]/@a', 'integer') = c1 GO

Answer 85

SELECT c1, c2, c3 FROM T WHERE c3.exist( '/root[@a=sql:column("c1")]') = 1

Question 86

What is a major difference between FOR JSON AUTO and FOR JSON PATH?

Answer 86

FOR JSON AUTO outputs dot-separated aliases (for example, Info.MiddleName) as keys with dots, not as nested objects.

Question 87

What will the following query output?

Answer 87

Question 88

What will the following query output? ## Footnote SELECT TOP 2 H.SalesOrderNumber, H.OrderDate, D.UnitPrice, D.OrderQty FROM Sales.SalesOrderHeader H INNER JOIN Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID FOR JSON AUTO

Answer 88

Note that when joining tables, the columns in the second table are nested.

Question 89

FOR JSON AUTO and FOR JSON PATH output options: How do you add a single, top-level elment to the JSON output? How do you include null values in the JSON output? How do you remove the square brackets that surround the JSON output?

Answer 89

FOR JSON AUTO, ROOT('rootname') FOR JSON PATH, INCLUDE\_NULL\_VALUES FOR JSON AUTO, WITHOUT\_ARRAY\_WRAPPER

Question 90

If you use OPENJSON without providing an explicit schema for the results, the function will return a table with which three columns?

Answer 90

key: the name of the JSON property value: the actual value of the JSON property type: JSON data type as an int

Question 91

What will the following query return? ## Footnote ``` SELECT \* FROM OPENJSON('{"name": "John", "surname":"Doe", "age":45}') ```

Answer 91

Question 92

What will the following query return? ## Footnote ``` SELECT \* FROM OPENJSON('["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]') ```

Answer 92

Question 93

What will be the result of the following query?

Answer 93

Question 94

What will the following query output?

Answer 94

This query uses an explicit schema for the output

Question 95

Delete the duplicate rows in the following table "dupes"

Answer 95

DELETE FROM dupes WHERE ID NOT IN (SELECT MIN(id) FROM dupes GROUP BY name) You use a subquery with an aggregate function to arbitrarily choose which ID to retain. In this case, we're keeping the name with the smallest id value.

Question 96

How do you create a temporary table?

Answer 96

CREATE TABLE #temptable (col1 datatype...)

Question 97

If you create a temporary table within a procedure, will it still exist after the procedure is completed?

Answer 97

No, it gets dropped automatically upon completition of stored procedure execution.

Question 98

If you update a CTE, does this update the underlying base table? e.g. WITH Employees\_Name\_Gender AS( SELECT Id, Name, Gender FROM tblEmployee) UPDATE Employees\_Name\_Gender SET Gender = 'Female' WHERE Id = 1

Answer 98

Yes, but either the CTE must be created on only one base table or if the CTE is based on more than one table, the UPDATE must affect only one base table (otherwise the CTE is not updatable) Note that when the CTE if based on more than one base table, the update might not always work as expected. Always double check the effect on the underlying table.