Session 6 - Sub queries, with, CTE, pivot, functions, GUIDS, XML, JSON, Temporal Tables Flashcards
1
Q
What are the keywords you can use in a subquery comparison?
A
ANY/SOME
ALL
2
Q
What is the equivalent word for ANY/SOME
A
OR
3
Q
What is the equivalent word for ALL
A
AND
4
Q
How would you return all values from table1 where that each contain COL1 using a subquery and COL1 doesn’t match any value from table2?
A
SELECT * FROM TABLE1 WHERE COL1 <> ALL (SELECT COL1 FROM TABLE2)
5
Q
What is a correlated subquery?
A
A subquery that relies on the original query and is used in the SELECT portion of the query:
SELECT EMPNAME, EMPNUMBER, (SELECT ID FROM TABLE2 T2 WHERE T2.EMPNAME = T1.EMPNAME) AS ID FROM TABLE1 T1
6
Q
How do you run a recursive query?
A
Start with Anchor then union to the original table with a join to the anchor (Using CTE):
with MyTable ( Select EmployeeNumber, 0 as BossLevel from EmployeeTable where ManagerNumber = 0 UNION ALL Select EmployeeNumber, BossLevel + 1 from EmployeeTable e join MyTable o on e.ManagerNumber = o.EmployeeNumber )
7
Q
How can you join a derived table to another table in a query?
A
SELECT a.COL1, a.COL2, b.COL2
FROM TABLE1 a
JOIN (SELECT COL1, COL2 FROM TABLE 2) b
ON a.COL1 = b.COL2
You HAVE to alias the derived table, otherwise the system doesn’t know what it’s named
8
Q
What are the different functions?
A
Multi-Statement table Function
Inline Table Function
Scalar Function
9
Q
What Function statement uses multiple statements to return a table?
A
Multi-Statement table function
10
Q
What function statement uses a single statement to return a table?
A
Inline Table Function
11
Q
What Function statement returns a single value?
A
Scalar Function
12
Q
What is the syntax for creating a Multi-Statement Table Function?
A
CREATE FUNCTION [dbo].[FunctionName] ( @param1 int, @param2 char(5) ) RETURNS @returntable TABLE ( [c1] int, [c2] char(5) ) AS BEGIN INSERT @returntable SELECT @param1, @param2 RETURN END
13
Q
What is the syntax for creating a Inline Table Function?
A
CREATE FUNCTION [dbo].[FunctionName] ( @param1 int, @param2 char(5) ) RETURNS TABLE AS RETURN ( SELECT @param1 AS c1, @param2 AS c2 )
14
Q
What is the syntax for creating a Scalar Function?
A
CREATE FUNCTION [dbo].[FunctionName] ( @param1 int, @param2 int ) RETURNS INT AS BEGIN
RETURN @param1 + @param2 END
15
Q
How can you join a table with a table function?
A
Use Apply ( select * from table1 a cross apply ( select * from tablefunction(a.col1))
16
Q
What are the two different ways to use apply? What is the difference?
A
CROSS APPLY - Like INNER JOIN
OUTER APPLY - Like LEFT JOIN
17
Q
What is a synonym?
A
It takes the place of the table name. If table changes name, then query could still find it
18
Q
What is the syntax for creating a synonym?
A
CREATE SYNONYM [SYNONYMNAME]
FOR [TABLENAME]
19
Q
What are the two commands used to run Dynamic SQL?
A
EXECUTE(SQL STATEMENT IN STRING)
SYS.SP_EXECUTESQL @STATEMENT, @PARAMS
20
Q
What is the syntax needed to run SYS.SP_EXECUTESQL?
A
EXECUTE SYS.SP_EXECUTESQL @STATEMENT, @PARAMS, @VAR1, @VAR2
@STATEMENT & @PARAMS HAVE TO BE NVARCHAR
EX:
DECLARE @COMMAND NVARCHAR(MAX) = N’SELECT * FROM JDE_PRODUCTION.PRODDTA.F0101 WHERE ABAN8 = @AB’
DECLARE @PARAMS NVARCHAR(MAX) = N’@AB INT’
DECLARE @AB INT = 41302
EXECUTE SYS.SP_EXECUTESQL @COMMAND, @PARAMS, @AB
21
Q
Where is it beneficial to use a correlated subquery?
A
When the select statement will return a small number of rows.
22
Q
What is the name of a sub-query used in the FROM clause?
A
Derived Table.
23
Q
What is the name of a sub-query used in the SELECT clause?
A
Correlated Subquery.
24
Q
How would you get the top 5 values from a window function?
A
Use a WHERE clause on a derived table or WITH :
SELECT * FROM ( SELECT EMPLOYEENUMBER, RANK() OVER (PARTITION BY DEPARTMENT ORDER BY EMPLOYEENUMBER) AS 'RANK' FROM DBO.MYTABLE ) AS RANKEDTABLE WHERE RANK <= 5
25
What is the syntax of a pivot table?
```
With PIVOTTABLE AS
(SELECT STATEMENT)
PIVOT (AGGREGATE(VALUES))
FOR ROWSTOMAKECOLUMNS
IN (COLUMN NAMES IN HARD BRACKETS) AS ALIAL
```
26
What is the syntax of an UNPIVOT table?
SELECT *
FROM PIVOTTABLE
UNPIVOT (ALIASFORVALUES FOR SPREADEDCOLUMNS IN ([COL1], [COL2])) AS ALIAS
27
What are some drawbacks of identity?
* You can't insert it into an existing column
| * Transactions made with identity makes identity not able to be rolled back
28
What does GUID stand for?
Globally Unique Identifier
29
What is a GUID?
a 128 bit resource for making a unique number
30
What is the datatype for a GUID?
uniqueidentifier
31
How do you create a GUID?
Use NEWID()
DECLARE @NEWGUID AS uniqueidentifier
SET @NEWGUID = NEWID()
32
How is a GUID returned?
As a Hexadecimal
33
What is the function used to create a GUID used in an Indexed column? What is the limitation?
NEWSEQUENTIALID()
It can only be used in a column containing a DEFAULT constraint.
34
What is the major problem with using GUIDS?
They are VERY large, 16 Bytes.
35
What is the best way to store a unique number?
SEQUENCE?
36
What is the syntax for creating a new SEQUENCE?
```
CREATE SEQUENCE sequenceName AS INT --OR Any numerical datatype
START WITH beginningNumber
INCREMENT BY number
MINVALUE number
MAXVALUE number
CYCLE --MEANING THAT IT RESTARTS
```
37
What table stores sequences?
sys.sequences
38
What is the syntax for getting the next value of a SEQUENCE?
SELECT NEXT VALUE FOR sequenceName AS Alias
39
How do you add a DEFAULT SEQUENCE constraint to a column that already exists on a table?
ALTER TABLE tableName
| ADD CONSTRAINT DefaultConstraintName DEFAULT NEXT VALUE FOR sequenceName FOR Column
40
How do you restart a sequence?
ALTER SEQUENCE sequenceName RESTART
41
How do you reset an Identity column?
SET IDENTITY_INSERT tableName ON
42
What is the way to add an attribute to an element in XML?
set @xml.modify('insert attribute AttributeName{"Attribute Value"} into (XMLQuery)[1]')
43
How do you convert a data query into XML?
SELECT * FROM TABLE FOR XML [RAW, AUTO, PATH, EXPLICIT]
44
What XML output will return only row elements with all data as attributes?
XML RAW
```
Returns:
/| Row col1="value" col2="value2" /|\
/| Row col1="value1" col2="value21" /|\
/| Row col1="value2" col2="value22" /|\
/| Row col1="value3" col2="value23" /|\
```
45
What is the syntax for changing the element name for XML RAW?
put it in parenthesis:
SELECT * FROM TABLE FOR XML RAW('MyElement')
Returns:
/| MyElement col1="value" col2="value2" /|\
/| MyElement col1="value1" col2="value21" /|\
/| MyElement col1="value2" col2="value22" /|\
/| MyElement col1="value3" col2="value23" /|\
46
How can you change the output of XML RAW to show Elements instead of Attributes?
FOR XML RAW, ELEMENTS
```
Returns:
/| Row |\
/| col1 |\ Value /| col1 /|\
/| col2 |\ Value2 /| col2 /|\
/| Row /|\
```
47
How can you return XML as nested elements for joined tables with the aliases as Element Names and all other columns as attributes? Can you return as elements as well?
FOR XML AUTO. Yes you can also return elements:
FOR XML AUTO, ELEMENTS
Returns:
```
/| tableA |\
/| col1 |\
Value1
/|/ col1 |\
/| tableB |\
/| col2 |\
Value2
/|/ col2 |\
/|/ tableB |\
/|/ tableA |\
```
48
How can you return specific columns as elements or attributes? How do you specify?
FOR XML PATH('RootElement')
```
Attributes are returned when column is an alias with @ sign
IE:
select a.first_name as '@col1',
a.last_name as 'col2',
b.store_name as 'nestedelement/col3'
from sqltest.staffs a
join sqltest.stores b
on a.store_id = b.store_id
for xml path('MyElement')
```
Returns:
```
/|MyElement col1="Fabiola"|\
/|col2|\Jackson/|/col2|\
/| nestedelement |\
/|col3|\ Santa Cruz Bikes /|/col3|\
/|/ nestedelement |\
/|/ MyElement |\
```
49
How can you return non duplicates where you have a joined query?
You have to use a nested query, return the nested query FOR XML PATH(''), TYPE
You have to include Type because that will return it as XML instead of HTML
```
IE:
SELECT COL1,
COL2,
(SELECT COL3 FROM TABLE2 WHERE COL1=TABLE1.COL1 FOR XML PATH(''), TYPE) AS NESTEDELEMENT
FROM TABLE1
```
50
How can you return well formed XML from any of the FOR XML types?
Add ROOT('RootElement') to any FOR XML [PATH, AUTO, RAW], ROOT('RootElement')
51
What XML output provides the greatest flexibility but is the most complicated?
FOR XML EXPLICIT
52
What is the syntax to begin FOR XML EXPLICIT?
SELECT 1 AS Tag, NULL AS Parent
53
What is the syntax for selecting a column as an element in FOR XML EXPLICIT?
SELECT 1 AS TAG, NULL AS PARENT,
| COL1 AS [ElementName!1!COL1ALIAS] --the 1 relates to the tag
54
How do you combine result sets in FOR XML EXPLICIT?
```
SELECT 1 AS TAG, NULL AS PARENT,
, A.COL1 AS [ELEMENTS!1!COL1]
, A.COL2 AS [ELEMENTS!1!COL2]
, NULL AS [ELEMENTS!2!COL3]
FROM TABLE1 AS A
UNION ALL
SELECT 2 AS TAG, 1 AS PARENT
, B.COL1 AS COL1
, NULL AS COL2
, COL3
ORDER BY COL1, [ELEMENTS!2!COL3]
FOR XML EXPLICIT
```
55
What are the commands called for shredding XML?
XQUERY
56
What is the XQUERY command for getting a single value from xml?
VALUE:
SELECT @X.VALUE('(/ELEMENT/ELEMENT2/ITEM/@ATTRIBUTE)[1]', 'DATATYPE')
It must return a single value (singleton).
57
What is the XQUERY command to find out if there actually is a value where you are pointing within XML?
EXISTS: --Returns 1 or 0 if that node actually exists or not
SELECT @X.EXISTS('(/ELEMENT/ELEMENT2/ITEM/@ATTRIBUTE)[1]')
58
How do you update an XML Value with XQUERY?
MODIFY: --You have to use the word SET before a modify command
--It's also case sensitive
SET @X.MODIFY('replace value of (/ELEMENT/ELEMENT2/ITEM/@ATTRIBUTE)[1] with "VALUE"')
59
How can you insert a value into XML using XQUERY?
MODIFY with insert:
SET @x.modify('insert new value into (/element/element2)[1]')
60
What are the commands and query order for XQUERY?
```
for (in)
let
where
order by
return
```
61
How do you use FOR in XQUERY?
SELECT @x.query('for $ValueRetrieved in /element/element2/item return string($ValueRetrieved)')
--Returning the value will return the entire element while string($value) will return just the value
62
How can you concatenate strings in XQUERY?
concat(firstString, secondString)
63
With this XML, how can you use XQUERY to return the items ordered by the cost?
```
declare @x xml
set @x = '
Shopping
ShoppingTrip
item Apples /item
item Bananas /item
item Cherries /item
/ShoppingTrip
/Shopping
'
```
select @x.query('for $ValueRetrieved in /Shopping/ShoppingTrip[1]/Item order by $valueRetrieved/@Cost return concat(string($ValueRetrieved),";")')
64
With this XML, how can you use XQUERY to return the items where the cost is greater than or equal to 4?
```
declare @x xml
set @x = '
Shopping
ShoppingTrip
item Apples /item
item Bananas /item
item Cherries /item
/ShoppingTrip
/Shopping
'
```
select @x.query('for $ValueRetrieved in /Shopping/ShoppingTrip[1]/Item where $ValueRetrieved/@Cost >= 4 return concat(string($ValueRetrieved),";")')
65
With this XML, how can you use XQUERY to assign the cost into a variable and return it?
```
declare @x xml
set @x = '
Shopping
ShoppingTrip
item Apples /item
item Bananas /item
item Cherries /item
/ShoppingTrip
/Shopping
'
```
select @x.query('for $ValueRetrieved in /Shopping/ShoppingTrip[1]/Item let $CostVariable := $ValueRetrieved/@Cost return concat($CostVariable,";")')
66
Using the below XML, how can you return the two shoppingtrip elements into separate rows using XQUERY?
```
declare @x xml
set @x = '
Shopping
ShoppingTrip
item Apples /item
item Bananas /item
item Cherries /item
/ShoppingTrip
ShoppingTrip
item Diamonds /item
item Emeralds /item
item Furniture /item
/ShoppingTrip
/Shopping
```
Use NODES:
select tbl.col.query('.')
from @x.nodes('/Shopping/ShoppingTrip') as tbl(col)
67
Using the below XML, how can you return all of the costs into separate rows using XQUERY?
```
declare @x xml
set @x = '
Shopping
ShoppingTrip
item Apples /item
item Bananas /item
item Cherries /item
/ShoppingTrip
ShoppingTrip
item Diamonds /item
item Emeralds /item
item Furniture /item
/ShoppingTrip
/Shopping
'
```
Use NODES:
select tbl.col.value('.', 'varchar(50)') as Item
tbl.col.value('@Cost', 'varchar(50)') as Cost
from @x.nodes('/Shopping/ShoppingTrip/Item') as tbl(col)
68
How do you query an XML field within a table to be able to shred the data from the XML?
--You have to use CROSS APPLY
select tbl.col.value('.', 'varchar(50)') as Item
tbl.col.value('@Cost', 'varchar(50)') as Cost
from #tblXML CROSS APPLY
xmlCol.nodes('/Shopping/ShoppingTrip/Item') as tbl(col)
69
What are the three ways to import and export XML?
BCP
Bulk Insert
Openrowset
70
What is the syntax for running BCP and where do you run it?
BCP is a program that is run from the command prompt:
bcp [database].[schema].[Table] out [in] filename.out -N -T
-N -- Unicode -T -- Trusted Windows Connection
out -- Exporting | in -- Importing
71
What does BCP stand for?
Bulk Copy Program
72
What is the syntax for BULK INSERT?
BULK INSERT [table] from 'c:\temp\data.txt'
73
What is the syntax for OPENROWSET?
INSERT INTO [Table] SELECT * FROM OPENROWSET(BULK 'c:\temp\data.txt', SINGLE_BLOB) AS alias
74
How do you add SCHEMA when you're exporting data as XML?
use:
FOR XML RAW, xmldata
OR
FOR XML RAW, xmlschema
75
What is the syntax for creating an XML index?
CREATE PRIMARY XML INDEX indexName on [table](XMLColumn)
76
What are the other XML Indexes (besides primary) that help the most for speeding up queries for XML?
Path
Value
Property
77
What is the syntax for creating a secondary XML index?
CREATE XML INDEX indexName ON [table](XMLColumn)
| USING XML INDEX primaryIndexName FOR [Path, Value, Property]
78
What are the requirements for creating XML indexes?
Secondary XML Indexes require a Primary XML Index
| A Primary XML Index requires that the host table has a Primary Key.
79
What is the syntax to return a single value for JSON?
select json_value(@json, '$.name')
80
What is the syntax to return an object or array for JSON?
select json_query(@json, '$') --$being the root
81
How can I return an error when a value isn't found using json_query or json_value?
select json_value(@json, 'strict $.name')
82
How can you update a value in JSON?
select json_modify(@json, 'strict $.object.attributeToChange', 'newValue')
83
How can I update an OBJECT in JSON?
select json_modify(@json, 'strict $.objectToChange', json_query('{"key":"value"}'))
84
What is the syntax to pull data from JSON into a table? Use the following JSON as an example:
```
set @json = '
{
"name":"Cody",
"items":[
{"item":"Apples","cost":5},
{"item":"Bananas","cost":6},
{"item":"Cherries","cost":7}
]
}'
```
```
SELECT * FROM openjson(@json, '$.items')
WITH (item varchar(15), cost int) --Hint
returns:
item cost
Apples 5
Bananas 6
Cherries 7
```
85
How can we convert a table to JSON?
SELECT item, cost
from table
FOR JSON PATH, root('items') --root will create the root level
86
What are the types of solutions for changing data and keeping history?
Type 0: History is not preserved. Data reflect original values
Type 1: Data reflect latest values (previous values are overwritten)
Type 2: Every version of data is represented with separate row in the table, usually with columns that represent period of validity
Type 3: Keeping limited history for selected column(s) using additional columns in the same row
Type 4: Keeping history in the separate table while original dimension table keeps the latest (current) table rows versions
87
What is a temporal table?
It is a historical table following Type 4 history keeping. It has a start and end date for data validity and the original table keeps the current data with a start and end date as well.
88
What are the columns and extras needed to be added to a table to make it into a temporal table?
```
ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START,
ValidTo datetime2(2) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
```
WITH (SYSTEM_VERSIONING = ON)
89
What is required on a table for a temporal table to be generated?
The table needs to have a primary key.
90
What are the steps to dropping a table that has a temporal table connected to it? (Or in other words is running system versioning)
ALTER TABLE [tableName] SET ( SYSTEM_VERSIONING = OFF)
DROP TABLE [tableName]
DROP TABLE [temporalTable]
91
What is the syntax to specify what table you want to use for the temporal table?
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[tableName]))
92
What is the syntax for adding a temporal table to an existing table?
--You have to specify dates when adding the tables
ALTER TABLE [tableName]
ADD
ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START CONSTRAINT default_Constraint DEFAULT SYSDATETIME()
,ValidTo datetime2(2) GENERATED ALWAYS AS ROW END CONSTRAINT default_constraint2 DEFAULT CONVERT(datetime2(2), '9999-12-31 23:59:59')
,PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
GO
ALTER TABLE [tableName]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [historyTableName]))
93
What is the syntax to query a temporal table as of a specific date?
SELECT * FROM [temporalTable] FOR SYSTEM_TIME AS OF '2020-01-01'
94
What are the three different ways to query between dates for a temporal table?
FOR SYSTEM_TIME
FROM startDateTime TO endDateTime --Current and historic
BETWEEN startDateTime TO endDateTime--Current and historic
CONTAINED IN (startDateTime, endDateTime) --Only looks at historic data
95
How can you hide temporal table date columns?
add HIDDEN
| IE: ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START HIDDEN
