Query Data with Advanced T-SQL Components Flashcards
What are 4 forms of table expressions?
Derived tables, common table expressions (CTEs), views, and inline table-valued functions
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?
- All columns returned by the inner query must have names
- All column names must be unique
- The inner query is not allowed to have an ORDER BY clause
Normally the inner query of a table expression is not allowed to have an ORDER BY clause. What is the exception?
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
When should you use a temporary table/table variable over a table expression?
When you need to persist the result of a query for further processing.
Is the result set of a view stored in the database?
No, only the view’s definition is stored in the database
Syntax to create a view
CREATE VIEW viewname
AS
SELECT col1, col2 FROM table1;
What is the main difference between VIEWS and inline table-valued functions?
Views do not accept input parameters but inline table-valued functions do.
Syntax to create an inline table-valued function
CREATE FUNCTION fxname(@parameter datatype)
RETURNS TABLE
AS
RETURN
SELECT col1, col2
FROM table1
WHERE parameter = @parameter
How do you retrieve data from an inline table-valued function? (Syntax)
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
True or False. Derived tables:
- Must have an alias
- Can be referred to multiple times in the same query
- Can use internal or external aliases for columns
- Can refer to parameters or variables
- Cannot be nested within other derived tables
- Must have an alias - True
- Can be referred to multiple times in the same query - False
- Can use internal or external aliases for columns - True
- Can refer to parameters or variables - True
- Cannot be nested within other derived tables - False
In which clause do you use derived tables?
In the FROM clause
Common table expression (CTE) syntax
WITH ctealias AS
( innerquery )
outerquery
Syntax for multiple CTEs
When can you refer to a another CTE within a CTE?
WITH C1 AS
( SELECT .. FROM T1 WHERE… ),
C2 AS
( SELECT … FROM C1 WHERE …)
outerquery;
Each CTE can refer to a previously defined CTE
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;
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.
What does APPLY do?
Difference between CROSS APPLY and OUTER APPLY
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
Syntax for window functions
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
In which clauses can you use window functions?
SELECT and ORDER BY
What are 4 window ranking functions and what do they do?
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
What are 4 window offset functions and how do they work?
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
What 3 elements do you need to pivot data?
- What do you want to see on rows? i.e. on rows or grouping element
- What do you want to see on columns? i.e. on cols or spreading element
- What do you want to see in the intersection of each distinct row and column value? i.e. data or aggregation element.
Syntax to pivot data
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:
- You should define a table expression that returns the three elements involved in pivoting.
- The PIVOT operator returns a table result. Remember to alias it.
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?
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;
Why should you define a table expression that returns the 3 elements involved in pivoting instead of querying the underlying table directly?
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
What are 4 limitations of the PIVOT operator?
- 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
- The COUNT(*) function isn’t allowed as the aggregate function. You need to use COUNT(col).
- Limited to using only one aggregate function
- The IN clause of the PIVOT operator accepts a static list of spreading values. It doesn’t support a subquery as input.
Syntax to unpivot data
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
True or False. UNPIVOT filters out rows with NULLs
True
GROUPING SETS syntax
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.
What’s the difference between the CUBE and ROLLUP clauses?
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) ()
When is GROUPING function useful? What does it do? How is GROUPING_ID different?
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.
What are 7 elements required to mark a table as temporal? Syntax inside CREATE TABLE statement particular to temporal table
- A primary key constraint
- Two DATETIME2 columns to store the start and end of the validity period of the row
- The start column needs to be marked with the clause GENERATED ALWAYS AS ROW START.
- The end column needs to be marked with the clause GENERATED ALWAYS AS ROW END.
- The designation of the pair of columns that store the row’s validity period with the clause PERIOD FOR SYSTEM_TIME (startcol, endcol)
- The table option SYSTEM_VERSIONING needs to be set to ON
- 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));
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 _________________.
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.
When you insert data in an empty temporal table, what happens in the history table?
Nothing. It’s still empty.
What happens in the history table when you delete rows from a temporal table?
The deleted row appears in the history table with the end column set to the start time of the transaction that deleted the row.
An update is handled as a delete plus insert. Explain what this means in terms of what happens in the temporal and history tables.
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.
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?
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).
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?
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.
What will happen to rows with degenerate intervals as the validity period when you query the temporal table using the FOR SYSTEM_TIME clause?
SQL Server will discard those rows
Syntax to query a temporal table using the FOR SYSTEM_TIME FROM syntax and explain how it works
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.
Syntax to query a temporal table using the FOR SYSTEM_TIME BETWEEN syntax and explain how it works
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.
Syntax to query a temporal table using the FOR SYSTEM_TIME CONTAINED IN syntax and explain how it works
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.
What does the FOR SYSTEM_TIME ALL output when you use it to query a temporal table?
It returns all row versions from both the temporal and history tables, excluding degenerate intervals.
Syntax for XML tags. Syntax for XML tags when an element has no nested elements.
True or False: XML is case sensitive
True
How do you use the following special characters in the values of your XML document?
&, “, < , >, ‘
What’s another alternative?
What’s the different between and XML document and an XML fragment?
An XML document has a single root node. Without the root node, you get an XML fragment.
Which of the following is an example of an attribute-centric XML presentation and which is an element-centric XML presentation?
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.
What is a namespace?
How do you define namespaces and aliases in a quey that returns XML?
A namespace is a set of names where all the names are unique
WITH XMLNAMESPACE(‘uri’ AS alias)
Place it before the SELECT statement
Syntax for creating XML from a query result using the RAW option.
What does this produce?
How do you make it element-centric?
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;
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’);
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.
Is the default XML output of FOR XML AUTO attribute-centric or element-centric?
How do you change it?
attribute-centric
FOR XML AUTO, ELEMENTS;
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?
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.
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?
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.
When you use FOR XML AUTO, how should you order the columns in the SELECT list?
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.
It’s good to include an _______ clause when you’re using FOR XML AUTO in order to guarantee the order of returned rows.
ORDER BY
By default, no elements are created for a column whose value is null. How can you override this?
By adding the XSINIL keyword to the ELEMENTS option
eg. FOR XML RAW (‘Employee’), ROOT (‘Employees’), ELEMENTS XSINIL;
What are the 4 FOR XML modes?
FOR XML RAW
FOR XML AUTO
FOR XML EXPLICIT
FOR XML PATH
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?
element-centric
You can modify the default behavior by using the at (@) symbol to define attributes or the forward slash (/) to define the hierarchy.
What will the following query return?
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’);
What will be the result of the following query?
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;
What must you first call to write queries against an XML document by using OPENXML? What does it do?
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.
After you shred an XML document using OPENXML, you must remove the DOM presentation by using which system stored procedure?
sys.sp_xml_removedocument
Example of using OPENXML to shred an XML
What is XQuery?
Is XQuery case-sensitive?
It’s a standard language to query XML instances and return XML output.
Yes, XQuery is case-sensitive.
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.
SELECT @x.query( ‘for $x in /bookstore/book
where $x/price>30
order by $x/title
return $x/title’)
What are 7 types of nodes in XQuery?
Element, attribute, text, namespace, processing-instruction, comment, and document (root) nodes.
Syntax for if else statement in XQuery by itself and after return
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 () ‘)
Two ways to declare namespace with a prefix when using XQuery
Two ways to declare a namespace without a prefix when using XQuery
With prefix:
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
What are the components of a Qname?
A namespace prefix and a local name, except for XQuery variables, which don’t have a prefix.
How do you add comments in XQuery?
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 <location> element children of the <root> element. :)<br></br>/AWMI:root/AWMI:Location<br></br>') as Result<br></br>FROM Production.ProductModel<br></br>where ProductModelID=7; </root></location>
What does the following XQuery return?
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.
How do you refer to SQL variables in XQuery?
You can use the sql:variable() extension function to refer to SQL variables
What do the following XPath expressions do?
nodename
/
//
.
..
@
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
What do the following XPath expressions select?
bookstore
/bookstore
bookstore/book
//book
bookstore//book
//@lang
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
What do the following XPath expressions select?
/bookstore/book[1]
/bookstore/book[last()]
//title[@lang]
//title[@lang=’en’]
/bookstore/book[price>35.00]/title
/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
Path expression syntax for a location step
axisname::nodetest[predicate]
Which function do you use to retrieve the atomic value of an element in XQuery?
data() function
e.g.
How do you add multiple iterator variable in an XQuery FLWOR statement?
for $var1 in expression, $var2 in expression
Note: var2’s expression can refer to $var1
What’s the difference between an existential quantifier and a universal quantifier?
What is the syntax for quantified expressions in XQuery?
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
<result>All work centers have workcenterLocation ID</result>
else
<result>Not all work centers have workcenterLocation ID</result>
’) as Result
FROM Production.ProductModel
where ProductModelID=7
instance of operator syntax. What does it do?
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’)
cast as operator syntax
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”)’)
What can you use to refer to a SQL column in an XQuery?
sql:column(“columname”)
Given the code in the image, what will the following return?
select @x.query(‘string(/)’)
This is a comment 10
just text
20
Difference between data(), string(), and text() functions in untyped and typed XML. Use the image as an example
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 <age> element. On the other hand, fn:data(/age) returns integer 12. The fn:string(/age) yields the string "12".</age>
Given the following XML, use the value() method to extract the product ID
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.
Rewrite the following using the exist() method
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
SELECT c1, c2, c3
FROM T
WHERE c3.exist( ‘/root[@a=sql:column(“c1”)]’) = 1
What is a major difference between FOR JSON AUTO and FOR JSON PATH?
FOR JSON AUTO outputs dot-separated aliases (for example, Info.MiddleName) as keys with dots, not as nested objects.
What will the following query output?
What will the following query output?
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
Note that when joining tables, the columns in the second table are nested.
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?
FOR JSON AUTO, ROOT(‘rootname’)
FOR JSON PATH, INCLUDE_NULL_VALUES
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
If you use OPENJSON without providing an explicit schema for the results, the function will return a table with which three columns?
key: the name of the JSON property
value: the actual value of the JSON property
type: JSON data type as an int
What will the following query return?
SELECT \*
FROM OPENJSON('{"name": "John", "surname":"Doe", "age":45}')
What will the following query return?
SELECT \*
FROM OPENJSON('["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]')
What will be the result of the following query?
What will the following query output?
This query uses an explicit schema for the output
Delete the duplicate rows in the following table “dupes”
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.
How do you create a temporary table?
CREATE TABLE #temptable (col1 datatype…)
If you create a temporary table within a procedure, will it still exist after the procedure is completed?
No, it gets dropped automatically upon completition of stored procedure execution.
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
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.
