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 5 - Aggregate Functions, spatial aggregates > Flashcards

Session 5 - Aggregate Functions, spatial aggregates Flashcards

1
Q

List all window Functions

A 
CUME_DIST
DENSE_RANK
FIRST_VALUE
LAG
LAST_VALUE
LEAD
NTILE
PERCENT_RANK
RANK
ROW_NUMBER
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

CUME_DIST Function

A

Calculate the cumulative distribution of a value in a set of values

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

DENSE_RANK Function

A

Assign a rank value to each row within a partition of a result, with no gaps in rank values.

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

FIRST_VALUE Function

A

Get the value of the first row in an ordered partition of a result set.

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

LAG Function

A

Provide access to a row at a given physical offset that comes before the current row.

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

LAST_VALUE Function

A

Get the value of the last row in an ordered partition of a result set.

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

LEAD Function

A

Provide access to a row at a given physical offset that follows the current row.

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

NTILE Function

A

Distribute rows of an ordered partition into a number of groups or buckets

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

PERCENT_RANK Function

A

Calculate the percent rank of a value in a set of values.

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

RANK Function

A

Assign a rank value to each row within a partition of a result set

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

ROW_NUMBER Function

A

Assign a unique sequential integer to rows within a partition of a result set, the first row starts from 1.

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

Aggregate Over Syntax

A

AGGREGATE() OVER (PARTITION BY COLUMN1, COLUMN2 ORDER BY COLUMN1, COLUMN2 DESC)

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

what happens to the total when you have an order by and a sum (pertaining to Over clause)

A

the total is incremented

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

What is the syntax using ROWS in Over Clause

A

ROWS BETWEEN [NUMBER] PRECEDING AND [NUMBER] FOLLOWING

UNBOUNDED AND CURRENT ROW (Instead of Preceding or Following) Can also be used instead of number

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

What is the syntax using RANGE in Over Clause

A

RANGE BETWEEN [UNBOUNDED or CURRENT ROW or 0] PRECEDING AND [UNBOUNDED or CURRENT ROW or 0] FOLLOWING

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

Which is more efficient, ROW or RANGE?

A

ROW. Range has to worry about ties and is overall slower

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

What is required with ROW and RANGE

A

ORDER BY.

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

What is default when using OVER (Regarding ROWS and RANGE) Without ORDER BY? With ORDER BY?

A

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING IS DEFAULT

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IS DEFAULT WHERE THERE IS ORDER BY

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

What do ROW_NUMBER(), RANK(), DENSE_RANK() OVER () require?

A

ORDER BY

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

How do you use ROW_NUMBER() without specifying a column to order by?

A

ROW_NUMBER OVER (ORDER BY (SELECT NULL))

This will be faster because it doesn’t have to order your data

21
Q

How do you define multiple grouping sets?

A

Use ROLLUP within Group By clause:

SELECT COL1, COL2, COL3, SUM(COL4)
FROM TABLE
GROUP BY ROLLUP(COL1, COL2, COL3)

THIS WILL SUM COL1 THEN SHOW NULL, THEN COL1 AND COL2 WITH TOTAL SHOWING NULLS, THEN ALL THREE AND SO ON.

22
Q

How can you tell if a column is grouped in a ROLLUP statement?

A

Use GROUPING(COLUMN) as ‘ALIAS’

This will show 1 if it is grouped or 0 if not.

23
Q

How can you determine which columns are being grouped in a ROLLUP statement?

A

GROUPING_ID(COL1, COL2, COL3)

This will set columns in a binary order (COL1 - 1, COL2 - 2, COL3 - 4…) and will add the columns that are grouped (IE, if COL1 and COL3 were grouped in the row, the value would be 5)

24
Q

What are all of the extensions of the group by clause?

A

ROLLUP
CUBE
GROUPING SETS

25
Q

What is a GROUPING SET?

A

It allows you to specify which columns you want to rollup.

GROUP BY GROUPING SETS((COL1, COL2, COL3), (COL2, COL3),(COL1),())

() - Means ALL columns

26
Q

What data is returned when using CUBE within the GROUP BY CLAUSE?

A

Every possible combination of group by will be performed:

SELECT COL1, COL2, COL3
FROM TABLE
GROUP BY CUBE

SUM(COL1), COL2, COL3
SUM(COL1), SUM(COL2), COL3
SUM(COL1), SUM(COL3), COL2
SUM(COL2), COL1, COL3
SUM(COL2), SUM(COL3), COL1
SUM(COL3), COL1, COL2
SUM(COL1),SUM(COL2),SUM(COL3)
27
Q

How can you order grouped sums with nulls at bottom?

A

ORDER BY
CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END, COL1,
CASE WHEN COL2 IS NULL THEN 1 ELSE 0 END, COL2,

28
Q

What is Geometry data type used for?

A

Place points of data on a graph

29
Q

How would you place point 3,4 on a Geometry data point?

A

geometry::STGeomFromText(‘POINT (3 4)’, 0)

30
Q

Name and describe all Geography Aggregate Methods

A

CollectionAggregate
Combines all of the shapes, but keeps all of the shapes as their own shape, but all on a single row
ConvexHullAggregate
creates a complex shape around the entirety of the shapes
EnvelopeAggregate
Creates a straight line shape around all of the shapes
UnionAggregate
Aggregate together shapes which puts all points and shapes into a single shape, so any line that goes through another shape is merged into the original

31
Q

What are PERCENTILE_CONT(PARAM) and PERCENTILE_DISC(PARAM)?

A

PERCENTILE_CONT(PARAM) Finds the value closest to the parameter in a continuous line (one that doesn’t exist in the data per se
PERCENTILE_DIST(PARAM) Finds the value closest to the parameter in a continuous line that DOES exist in the data

32
Q

What is the syntax of PERCENTILE_CONT(PARAM) and PERCENTILE_DISC(PARAM)?

A

PERCENTILE_CONTDIST WITHIN GROUP (ORDER BY order_by_expression) OVER (partition_by_clause)

33
Q

How do you create a geometry point?

A

use:
geometry::STGeomFromText(‘POINT (X Y)’, 0)
OR
geometry::Point(x, y, 0)

34
Q

How do you create multiple points in a single geometry statement?

A

geometry::STGeomFromText(‘MULTIPOINT ((X Y), (X Y), (X Y)), 0)

35
Q

What are the Methods on Geometry Instances?

A 
STArea
STAsBinary
STAsText
STBoundary
STBuffer
STCentroid
STContains
STConvexHull
STCrosses
STCurveN
STCurveToLine
STDifference
STDimension
STDisjoint
STDistance
STEndpoint
STEnvelope
STEquals
STExteriorRing
STGeometryN
STGeometryType
STInteriorRingN
STIntersection
STIntersects
STIsClosed
STIsEmpty
STIsRing
STIsSimple
STIsValid
STLength
STNumCurves
STNumGeometries
STNumInteriorRing
STNumPoints
STOverlaps
STPointN
STPointOnSurface
STRelate
STSrid
STStartPoint
STSymDifference
STTouches
STUnion
STWithin
STX
STY
36
Q

How do you create a geometry line?

A

geometry::STGeomFromText(‘LINESTRING (X Y, X Y, X Y…)’, 0)

37
Q

How do you create multiple geometry lines in the same statement?

A

geometry::STGeomFromText(‘MULTILINESTRING((X Y, X Y…), (X Y, X Y…)), 0)

38
Q

How do you create a geometry Polygon?

A

geometry::STGeomFromText(‘POLYGON ((X Y, X Y…))’, 0)

The FIRST and LAST coordinates MUST be the same

39
Q

How do you create a geometry Circle?

A

geometry::STGeomFromText(‘CIRCULARSTRING(X Y, X Y…)’’, 0)

The FIRST and LAST coordinates MUST be the same

40
Q

How do you find the intersection of two objects?

A

CurrentRow.STIntersection(@PreviousObject)

41
Q

How do you find the shortest distance between two objects?

A

CurrentRow.STDistance(@PreviousObject)

42
Q

How do you combine two objects into a single row?

A

CurrentRow.STUnion(@PreviousObject)

43
Q

How do you set a point using Geography?

A

geography::STGeomFromText(‘POINT (LONG LAT)’, 4326)

44
Q

How can you filter based on objects that intersect an object?

A

WHERE CURRENTGEOMETRY.Filter(geometry::Parse(‘POLYGON ((X Y)…)’)) = 1

This creates a filter based on the polygon that you pass to it. 1 means it will intersect with it.

45
Q

How do you combine all shapes into a single shape?

A

geometry::UnionAggregate(GeometryObject)

ie:
declare @I as geometry
SELECT @I = geometry::UnionAggregate(GXY)
FROM DBO.GeometryTable

46
Q

How do you combine all shapes while keeping each shape as its own shape?

A

geometry::CollectionAggregate(GeometryObject)

ie:
declare @I as geometry
SELECT @I = geometry::CollectionAggregate(GXY)
FROM DBO.GeometryTable

47
Q

How do you combine shapes by putting a box around all of the shapes to combine?

A

geometry::EnvelopeAggregate(GeometryObject)

ie:
declare @I as geometry
SELECT @I = geometry::EnvelopeAggregate(GXY)
FROM DBO.GeometryTable

48
Q

How do you combine shapes by putting a new shape as close around the shapes as possible?

A

geometry::ConvexHullAggregate(GeometryObject)

ie:
declare @I as geometry
SELECT @I = geometry::ConvexHullAggregate(GXY)
FROM DBO.GeometryTable

Sql 70-761 Certification (9 decks)

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