Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

More straglers

memorize.ai (lvl 286)

(0)

13 cards

()

Section 1

Preview this deck

CROSS JOIN

Front

1 / 13

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (13)

Section 1

(13 cards)

CROSS JOIN

Front

When there is no WHERE clause this will return the Cartesian product

Back

HAVING

Front

filter grouped aggregate values instead of individual row values. ex: HAVING AVG(p.UnitPrice) > 50

Back

covering index

Front

includes all columns required by the query.

Back

COLLATE

Front

ensures case sensitivity

Back

STRING_SPLIT

Front

table valued function with two arguments - a string and a seperator. it returns a table with a single column named Value

Back

Four types of table expressions

Front

1) Derived tables 2) Common table expressions (CTEs) 3) Views 4) Inline table-valued functions

Back

OPENJSON

Front

table-valued function that parses JSON text and displays it as columns and rows. Must specify how data is displayed using the WITH clause. ex: SELCT * FROM OPENJSON(@j, N'$.customers') WITH ( CustomerID int '$.id', CustomerName nvarchar(50) '$.name', City nvarchar(50) '$.city' );

Back

GROUP BY CUBE(X.CategoryName, S.StateProvinceName)

Front

produces totals for each Category, each StateProvinceName, and grand totals

Back

GROUP BY ROLLUP(X.CategoryName, S.StateProvinceName)

Front

produce grand totals, totals for each Category, but not totals for StateProvinceName.

Back

WHERE are CUBE and ROLLUP used?

Front

Group by clause only, not SELECT

Back

SWITCHOFFSET

Front

returns the date in UTC, takes two arguments (data type to be returned, time zone required)

Back

Table Expressions

Front

named queries that can be used instead of a table or view.

Back

define a field that should start at 1001 and be incremented by 5

Front

IDENTITY(int, 1001, 5)

Back