Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Sql 70-761 Certification > Session 6 - Sub queries, with, CTE, pivot, functions, GUIDS, XML, JSON, Temporal Tables > Flashcards

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is the equivalent word for ANY/SOME

A

OR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the equivalent word for ALL

A

AND

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
	)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the different functions?

A

Multi-Statement table Function
Inline Table Function
Scalar Function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What Function statement uses multiple statements to return a table?

A

Multi-Statement table function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What function statement uses a single statement to return a table?

A

Inline Table Function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What Function statement returns a single value?

A

Scalar Function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
		)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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))

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the syntax for creating a synonym?

A

CREATE SYNONYM [SYNONYMNAME]

FOR [TABLENAME]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the two commands used to run Dynamic SQL?

A

EXECUTE(SQL STATEMENT IN STRING)

SYS.SP_EXECUTESQL @STATEMENT, @PARAMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Where is it beneficial to use a correlated subquery?

A

When the select statement will return a small number of rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is the name of a sub-query used in the FROM clause?

A

Derived Table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is the name of a sub-query used in the SELECT clause?

A

Correlated Subquery.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is the syntax of a pivot table?

A 
With PIVOTTABLE AS 
	(SELECT STATEMENT)
PIVOT (AGGREGATE(VALUES)) 
	FOR ROWSTOMAKECOLUMNS 
	IN (COLUMN NAMES IN HARD BRACKETS) AS ALIAL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is the syntax of an UNPIVOT table?

A

SELECT *
FROM PIVOTTABLE
UNPIVOT (ALIASFORVALUES FOR SPREADEDCOLUMNS IN ([COL1], [COL2])) AS ALIAS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What are some drawbacks of identity?

A
  • You can’t insert it into an existing column

* Transactions made with identity makes identity not able to be rolled back

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What does GUID stand for?

A

Globally Unique Identifier

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What is a GUID?

A

a 128 bit resource for making a unique number

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What is the datatype for a GUID?

A

uniqueidentifier

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

How do you create a GUID?

A

Use NEWID()

DECLARE @NEWGUID AS uniqueidentifier
SET @NEWGUID = NEWID()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

How is a GUID returned?

A

As a Hexadecimal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What is the function used to create a GUID used in an Indexed column? What is the limitation?

A

NEWSEQUENTIALID()

It can only be used in a column containing a DEFAULT constraint.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What is the major problem with using GUIDS?

A

They are VERY large, 16 Bytes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is the best way to store a unique number?

A

SEQUENCE?

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What is the syntax for creating a new SEQUENCE?

A 
CREATE SEQUENCE sequenceName AS INT --OR Any numerical datatype
START WITH beginningNumber
INCREMENT BY number
MINVALUE number
MAXVALUE number
CYCLE --MEANING THAT IT RESTARTS
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What table stores sequences?

A

sys.sequences

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is the syntax for getting the next value of a SEQUENCE?

A

SELECT NEXT VALUE FOR sequenceName AS Alias

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

How do you add a DEFAULT SEQUENCE constraint to a column that already exists on a table?

A

ALTER TABLE tableName

ADD CONSTRAINT DefaultConstraintName DEFAULT NEXT VALUE FOR sequenceName FOR Column

40
Q

How do you restart a sequence?

A

ALTER SEQUENCE sequenceName RESTART

41
Q

How do you reset an Identity column?

A

SET IDENTITY_INSERT tableName ON

42
Q

What is the way to add an attribute to an element in XML?

A

set @xml.modify(‘insert attribute AttributeName{“Attribute Value”} into (XMLQuery)[1]’)

43
Q

How do you convert a data query into XML?

A

SELECT * FROM TABLE FOR XML [RAW, AUTO, PATH, EXPLICIT]

44
Q

What XML output will return only row elements with all data as attributes?

A

XML RAW

Returns:
/| Row col1="value" col2="value2" /|\
/| Row col1="value1" col2="value21" /|\
/| Row col1="value2" col2="value22" /|\
/| Row col1="value3" col2="value23" /|\
45
Q

What is the syntax for changing the element name for XML RAW?

A

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
Q

How can you change the output of XML RAW to show Elements instead of Attributes?

A

FOR XML RAW, ELEMENTS

Returns:
    /| Row |\
        /| col1 |\  Value /| col1 /|\
        /| col2 |\  Value2 /| col2 /|\
   /| Row /|\
47
Q

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?

A

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
Q

How can you return specific columns as elements or attributes? How do you specify?

A

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
Q

How can you return non duplicates where you have a joined query?

A

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
Q

How can you return well formed XML from any of the FOR XML types?

A

Add ROOT(‘RootElement’) to any FOR XML [PATH, AUTO, RAW], ROOT(‘RootElement’)

51
Q

What XML output provides the greatest flexibility but is the most complicated?

A

FOR XML EXPLICIT

52
Q

What is the syntax to begin FOR XML EXPLICIT?

A

SELECT 1 AS Tag, NULL AS Parent

53
Q

What is the syntax for selecting a column as an element in FOR XML EXPLICIT?

A

SELECT 1 AS TAG, NULL AS PARENT,

COL1 AS [ElementName!1!COL1ALIAS] –the 1 relates to the tag

54
Q

How do you combine result sets in FOR XML EXPLICIT?

A 
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
Q

What are the commands called for shredding XML?

A

XQUERY

56
Q

What is the XQUERY command for getting a single value from xml?

A

VALUE:

SELECT @X.VALUE(‘(/ELEMENT/ELEMENT2/ITEM/@ATTRIBUTE)[1]’, ‘DATATYPE’)

It must return a single value (singleton).

57
Q

What is the XQUERY command to find out if there actually is a value where you are pointing within XML?

A

EXISTS: –Returns 1 or 0 if that node actually exists or not

SELECT @X.EXISTS(‘(/ELEMENT/ELEMENT2/ITEM/@ATTRIBUTE)[1]’)

58
Q

How do you update an XML Value with XQUERY?

A

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
Q

How can you insert a value into XML using XQUERY?

A

MODIFY with insert:

SET @x.modify(‘insert new value into (/element/element2)[1]’)

60
Q

What are the commands and query order for XQUERY?

A 
for (in)
let 
where
order by
return
61
Q

How do you use FOR in XQUERY?

A

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
Q

How can you concatenate strings in XQUERY?

A

concat(firstString, secondString)

63
Q

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
'
A

select @x.query(‘for $ValueRetrieved in /Shopping/ShoppingTrip[1]/Item order by $valueRetrieved/@Cost return concat(string($ValueRetrieved),”;”)’)

64
Q

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	
'
A

select @x.query(‘for $ValueRetrieved in /Shopping/ShoppingTrip[1]/Item where $ValueRetrieved/@Cost >= 4 return concat(string($ValueRetrieved),”;”)’)

65
Q

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
'
A

select @x.query(‘for $ValueRetrieved in /Shopping/ShoppingTrip[1]/Item let $CostVariable := $ValueRetrieved/@Cost return concat($CostVariable,”;”)’)

66
Q

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
A

Use NODES:

select tbl.col.query(‘.’)
from @x.nodes(‘/Shopping/ShoppingTrip’) as tbl(col)

67
Q

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
'
A

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
Q

How do you query an XML field within a table to be able to shred the data from the XML?

A

–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
Q

What are the three ways to import and export XML?

A

BCP
Bulk Insert
Openrowset

70
Q

What is the syntax for running BCP and where do you run it?

A

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
Q

What does BCP stand for?

A

Bulk Copy Program

72
Q

What is the syntax for BULK INSERT?

A

BULK INSERT [table] from ‘c:\temp\data.txt’

73
Q

What is the syntax for OPENROWSET?

A

INSERT INTO [Table] SELECT * FROM OPENROWSET(BULK ‘c:\temp\data.txt’, SINGLE_BLOB) AS alias

74
Q

How do you add SCHEMA when you’re exporting data as XML?

A

use:

FOR XML RAW, xmldata
OR
FOR XML RAW, xmlschema

75
Q

What is the syntax for creating an XML index?

A

CREATE PRIMARY XML INDEX indexName on table

76
Q

What are the other XML Indexes (besides primary) that help the most for speeding up queries for XML?

A

Path
Value
Property

77
Q

What is the syntax for creating a secondary XML index?

A

CREATE XML INDEX indexName ON table

USING XML INDEX primaryIndexName FOR [Path, Value, Property]

78
Q

What are the requirements for creating XML indexes?

A

Secondary XML Indexes require a Primary XML Index

A Primary XML Index requires that the host table has a Primary Key.

79
Q

What is the syntax to return a single value for JSON?

A

select json_value(@json, ‘$.name’)

80
Q

What is the syntax to return an object or array for JSON?

A

select json_query(@json, ‘$’) –$being the root

81
Q

How can I return an error when a value isn’t found using json_query or json_value?

A

select json_value(@json, ‘strict $.name’)

82
Q

How can you update a value in JSON?

A

select json_modify(@json, ‘strict $.object.attributeToChange’, ‘newValue’)

83
Q

How can I update an OBJECT in JSON?

A

select json_modify(@json, ‘strict $.objectToChange’, json_query(‘{“key”:”value”}’))

84
Q

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}
	]
}'
A 
SELECT * FROM openjson(@json, '$.items')
                            WITH (item varchar(15), cost int) --Hint
returns:
item            cost
Apples        5
Bananas     6
Cherries      7
85
Q

How can we convert a table to JSON?

A

SELECT item, cost
from table
FOR JSON PATH, root(‘items’) –root will create the root level

86
Q

What are the types of solutions for changing data and keeping history?

A

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
Q

What is a temporal table?

A

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
Q

What are the columns and extras needed to be added to a table to make it into a temporal table?

A 
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
Q

What is required on a table for a temporal table to be generated?

A

The table needs to have a primary key.

90
Q

What are the steps to dropping a table that has a temporal table connected to it? (Or in other words is running system versioning)

A

ALTER TABLE [tableName] SET ( SYSTEM_VERSIONING = OFF)
DROP TABLE [tableName]
DROP TABLE [temporalTable]

91
Q

What is the syntax to specify what table you want to use for the temporal table?

A

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[tableName]))

92
Q

What is the syntax for adding a temporal table to an existing table?

A

–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
Q

What is the syntax to query a temporal table as of a specific date?

A

SELECT * FROM [temporalTable] FOR SYSTEM_TIME AS OF ‘2020-01-01’

94
Q

What are the three different ways to query between dates for a temporal table?

A

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
Q

How can you hide temporal table date columns?

A

add HIDDEN

IE: ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START HIDDEN

Sql 70-761 Certification (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions