Brainscape's Knowledge GenomeTM

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


See full index

Querying Data with Transact-SQL (70-761) > Skill 1.1 Create Transact-SQL SELECT queries > Flashcards

Skill 1.1 Create Transact-SQL SELECT queries Flashcards

Evaluate your level of knowledge for Skill 1.1 (Create Transact-SQL SELECT queries) for Microsoft Exam 70-761

1
Q

What are the major revisions of the ANSI SQL Standard (as of July 2016)

A 
SQL-86
SQL-89
SQL-92
SQL:1999
SQL:2003
SQL:2006
SQL:2008
SQL:2011
SQL:2016
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the two main differences between the CAST and CONVERT functions in T-SQL?

A

CAST is an ANSI SQL standard function

CONVERT has a style argument for formatting text

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

What is a relation (in the relational model) in SQL?

A

A relation is what SQL calls a table

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

What are the two main parts of a relation, in the relational model?

A

Heading

Body

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

What is a heading, in the relational model?

A

A heading is a set of attributes, each of a given type (what SQL represents with columns)

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

How are attributes identified, in the relational model?

A

Attributes are identified by name (e.g. orderid) and type name (e.g. INT)

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

What makes up the body of a relation, in the relational model?

A

A set of tuples (pronounced like “couples”, what SQL represents with rows)

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

What is a tuple?

A

A name-value pair. In the body of a relation, each tuple’s heading is the heading of the relation. (Revision: The body of a relation is a set of tuples, each tuple consisting of a set of attributes which are the same as the attributes of the relation.)

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

What does the “not equal” operator in T-SQL look like?

A

There are two forms
<> (SQL standard)
!= (non-standard)

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

In SQL Server, do columns have to have names?

A

No, but a result set with an unnamed column is non-relational

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

In order from first to last, what are the phases of logical query processing?

A 
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

In order from first to last, what is the order of the main SQL query clauses as keyed-in?

A 
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a column alias?

A

A column alias is an alternate name for a column or expression that is defined in the SELECT clause of a query

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

What phase of query processing can refer to column aliases?

A

ORDER BY (each phase can only refer to attributes that come in earlier phases of the logical query processing order)

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

The JOIN clause is part of which main phase?

A

FROM

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

What two forms of delimiters are supported in T-SQL?

A 
Double-quotes ("Sales"." Orders", SQL standard)
Square brackets([Sales].[Orders], non-standard)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is three-valued logic?

A

Three-valued logic is TRUE, FALSE, and UNKNOWN
Two values that match (TRUE)
Two values that do not match (FALSE)
An attempt to match NULL with something (UNKNOWN)

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

What are combining predicates?

A

Keywords that allow for the modification or aggregation of matching expressions. NOT, AND, and OR are combining predicates.

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

What is the order of precedence for combining predicates?

A

NOT
AND
OR

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

When using the LIKE operator, what wildcard expression lets you match any string, including an empty string?

A

% (percent)

‘D%’ would match any string starting with “D”

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

When using the LIKE operator, what wildcard expression lets you match a single character?

A

_ (underscore)

‘_D%’ would match a string where the second character is “D”

22
Q

When using the LIKE operator, what wildcard expression lets you match a single character from a list?

A

[] (square brackets)

‘[abgj]%’ would match any string beginning with a, b, g, or j

23
Q

When using the LIKE operator, what wildcard expression lets you match a range of characters from a list?

A

‘[a-j]%’ would match any string beginning with any letter from a to j

24
Q

What format for date is ALWAYS language-neutral?

A

YYYYMMDD (‘20170710’, for July 10th, 2017)

25
Q

What is the recommended way to specify a date and time range?

A

Closed-open interval is the recommended method
To specify the month of May, 2017:
dateval >= ‘20170501’ and dateval < ‘20170601’

26
Q

How does ORDER BY change the result of a SQL query?

A

The order of the rows in the output is guaranteed
The result is no longer relational
The result becomes what standard SQL calls a cursor (not a T-SQL cursor)

27
Q

What are the rules for regular identifiers?

A

The first character must be a letter defined in the Unicode standard 3.2 (a-z, A-Z, and letters from other Unicode languages)
Subsequent characters can include letters, numbers, @, $, #, and _ (underscore)
Cannot be a reserved keyword in T-SQL
Cannot have embedded spaces
Must not include supplementary characters (Newer versions of Unicode have defined additional characters in the range U+10000 to U+10FFFF called supplementary characters)

28
Q

What are the two ways you can specify sorting behavior?

A

By column name (orderid, city, zip)

By ordinal position (1,4,7)

29
Q

What two filtering predicates allow you to return a certain number or percent of rows?

A

TOP

OFFSET-FETCH (SQL standard)

30
Q

What are the two main options for filtering data using TOP?

A

TOP (x) - returns up to x rows

TOP (x) PERCENT - returns up to x percent of the rows

31
Q

Can TOP use a variable to specify the number or percent of rows returned?

A

yes

32
Q

What does deterministic mean when referring to a SQL query?

A

The same query with the same options will return the same result set every time (regardless of the host system, storage details, or system configuration)

33
Q

Is a query using the TOP option deterministic?

A

no

34
Q

How can you make the result of a query using TOP deterministic?

A

Add an ORDER BY clause
Add WITH TIES to include rows that are ordered the same
Make sure the ordering is unique by adding more columns to the ORDER BY list

35
Q

WHERE does the TOP predicate appear in a T-SQL query

A

In the SELECT clause, before the column list

36
Q

What is the general format of a TOP predicate?

A

TOP (x) [PERCENT] [WITH TIES]

37
Q

Where does the OFFSET-FETCH predicate appear in a T-SQL query?

A

After the ORDER BY clause

38
Q

What is the general format of an OFFSET-FETCH predicate?

A

OFFSET x ROW(S) [FETCH (NEXT|FIRST) y ROW(S) ONLY]

39
Q

Can OFFSET-FETCH use variables to specify the offset and number of rows returned?

A

yes

40
Q

How do you use OFFSET-FETCH or TOP to filter rows in an arbitrary order?

A

Use ORDER BY (SELECT NULL) as the ORDER BY clause

41
Q

What is a set operator?

A

Set operators (UNION, UNION ALL, INTERSECT, EXCEPT), operate on two result sets and compare complete rows between the result sets

42
Q

What is the general format for using a set operator?

A

(select statement 1)
(set operator)
(select statement 2)
[ODRER BY ]

43
Q

Can you use set operators with result sets that have differing numbers of columns or incompatible column types?

A

no

44
Q

With set operators, what type of comparison is performed?

A

Distinctness-based comparison.

45
Q

What is the difference between distinctness-based comparison and equality-based comparison?

A

In distinctness-based comparison, a comparison between two NULLS yields TRUE, instead of UNKNOWN, and a comparison between a NULL and non-NULL yields FALSE

46
Q

Why are the queries that are a part of a SQL statement using a set operator not allowed to contain ORDER BY clauses?

A

Set operators work on relational result sets. They are set operators, not cursor operators. Applying an ORDER BY clause to a result set makes it non-relational

47
Q

Can you add an ORDER BY clause to the result of a set operator?

A

yes

48
Q

What does the UNION operator do when combining two result sets?

A

It returns a set containing the unified results of both sets. Duplicate rows are discarded.

49
Q

How is the UNION ALL operator different from the UNION operator?

A

Duplicate rows are preserved

50
Q

Why would you use UNION ALL when joining two result sets where there is no potential for duplicates?

A

With UNION, SQL Server might create a query plan that includes unnecessary checking for duplicates, impacting performance.

51
Q

What does the INTERSECT operator do when combining two result sets?

A

INTERSECT returns distinct rows that are common to both sets

52
Q

What does the EXCEPT operator do when combining two result sets?

A

It returns distinct rows that appear in the FIRST result set, but not the second. Unlike UNION and INTERSECT, the order of the two sets is important

Querying Data with Transact-SQL (70-761) (4 decks)

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