Brainscape's Knowledge GenomeTM

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


See full index

Microsoft Certifications > SQL Certification Exam 461 > Flashcards

SQL Certification Exam 461 Flashcards

1
Q

What three things make a nonrelational query relational?

A
  • Every element has a name
  • There are no duplicates
  • There is no order
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Name two approximate numeric data types

A

1) Real

2) Float

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

How many bytes is a float

A

8

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

How many bytes is a real?

A

4

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

What function generates a sequential DEFAULT identifier for a UNIQUEIDENTIFIER field?

A

NEWSEQUENTIALID()

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

What column attribute requires it contain a Unique identifier?

A

UNIQUEIDENTIFIER

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

What function returns the current date?

A

GetDate() returns DATETIME

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

What year range does DATETIME support?

A

1753 - 9999

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

What function returns the most accurate DateTime value?

A

SYSDATETIME returns DATETIME2

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

What is the year range for DATETIME2?

A

year 0001 to 9999

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

What function will return a part of a date (month, day, or year) as a scalar value?

A

DATEPART(month, string date)

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

What function returns a string value for a part of a date - and the month name spelled out?

A

DATENAME(month, string date)

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

What function adds a number of days, months, or years to a date?

A

DATEADD(month, 1, string date)

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

What function returns the difference between dates in days, months, or years?

A

DATEDIFF(month, start date, end date )

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

What function returns a substring?

A

SUBSTRING(string date, starting, length)

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

What function returns the string length in characters?

A

LEN

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

What function returns the string length in bytes?

A

DATALENGTH

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

What function replaces all occurrences of characters in a string?

A

REPLACE(string, ‘x’, ‘y’) replaces x’s with y’s

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

What function produces a string repeated n times?

A

REPLICATE(‘xyz’, 10) repeats xyz 10 times

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

What function replaces a series of characters with a new string?

A

STUFF(string, starting, length, string)

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

Show a simple form of CASE statement.

A 
CASE scalar-expression
  WHEN 0 THEN 'No'
  WHEN 1 THEN 'Yes'
  ELSE  'Unknown'
END AS alias
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Show the advanced form of CASE statement.

A 
CASE 
  WHEN unitPrice < 20.00 THEN 'Low"
  WHEN unitPrice < 40.00 THEN 'Medium'
  ELSE  'High'
END AS priceRange
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What function returns the first of its arguments that is not NULL?

A

COALESCE(a, b, c, …)

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

What function performs and IF-THEN-ELSE?

A

IFF(expression, true, false)

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

What function takes a scalar and uses that to index into its argument list to select a value to return?

A

CHOOSE(index, arg1, arg2, arg3, …)

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

In expressions is AND processed BEFORE or AFTER the OR operator?

A

Before

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

LIKE wildcards: %

A

0 or more of any character

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

LIKE wildcards: _ (underscore)

A

any single character

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

LIKE wildcards: [ABCDE]

A

any one character in the list

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

LIKE wildcards: [0-9]

A

any one character in the range

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

LIKE wildcards ^ caret

A

NOT the following argument

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

OFFSET-FETCH requires what clause?

A

ORDER BY

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

example OFFSET-FETCH

A

after order by

OFFSET n ROWS FETCH NEXT m ROWS ONLY;

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

What does a CROSS JOIN produce?

A

Each row in Left paired with Each row in right

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

What does an INNER JOIN produce?

A

Produces pairing of Left and Right rows that match a predicate

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

What does a LEFT OUTER JOIN produce?

A

ALL rows from left and pairing those on the right that match a predicate

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

What does a RIGHT OUTER JOIN product?

A

ALL rows from the right and pairing those on the left that match a predicate.

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

What defines a self-contained subquery?

A

It has no dependency (references) on the outer containing query.

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

What is a correlated subquery?

A

One that references a column from the outer query or table.

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

List four types of Table Expressions.

A

1) Derived Tables
2) Common Table Expressions (CTEs)
3) Views
4) Inline table-valued functions

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

How is a derived table defined?

A

From (Select … returning a table of rows) as T

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

Show CTE form.

A 
WITH myCTE
AS
(
   Inner query
)
Select ... FROM myCTE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Show View form.

A

CREATE VIEW myView
AS
SELECT … FROM table;

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

Show inline table-valued function form.

A

CREATE FUNCTION name(args) RETURNS TABLE
AS
RETURN
SELECT statement returning table;

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

Which function UNION or UNION ALL returns only DISTINCT pairs?

A

UNION… UNION ALL keeps all duplicates

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

What operator returns only rows that are DISTINCT between two sets.

A

query1 INTERSECT query2

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

What operator returns rows in the first that are not in the second set.

A

query1 EXCEPT query2

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

What date function returns the End of the Month value.

A

EOM(date)

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

Show the form of a GROUPING SET

A 
GROUP BY GROUPING SETS
(
   (  colA, colB, YEAR(colC) ),
   (  colA ),
   (  YEAR(colC)  ),
   (   )
)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

What’s a CUBE GROUPING SET?

A 
Defines all possible combinations of its argument:
GROUP BY CUBE(  A,  B ); 
produces sets:
(  A,  B  )
(  A  )
(  B  )
(  )
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

What does a ROLLUP GROUPING SET product?

A 
Given an argument such as:
GROUP BY ROLLUP ( country, state, city)
it produces hierarchical sets:
(country, state, city),
(country, state),
(country),
( )
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

Pivoting: rows are also known as _____

A

grouping element

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

Pivoting: columns are also known as _____

A

spreading element

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

Pivoting: data is also known as ______

A

aggregation element

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

Show the form of a PIVOT.

A 
WITH PivotData AS
(
   Select
       custid,     -- grouping  - rows
       shipperid,  -- spreading - columns
       freight       -- aggregation - data
   From Sales.Orders
)
SELECT custid, [1]. [2]. [3]
FROM PivotData
   PIVOT(SUM(freight))
       FOR shipperid IN ([1], [2], [3]) 
AS P;
56
Q

What clause is added to aggregates to determine a range to apply the aggregate to using values in the CURRENT row.

A

OVER (PARTITION BY column)

57
Q

Show example of partitioning and bounding.

A

SELECT custid, ordered, orderdate, val,
SUM(val) OVER ( PARTITION BY custid
ORDER BY orderdate, ordered
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as runningtotal
FROM Sales.OrderValues;

58
Q

List the four window offset functions.

A

LEAD, LAG, FIRST_VALUE, LAST_VALUE

59
Q

XML replacement text for & (ampersand)

A

ampersand then amp;

60
Q

XML replacement text for “ (double quote)

A

ampersand then quot;

61
Q

XML replacement text for < (less than)

A

ampersand then lt;

62
Q

XML replacement text for > (greater than)

A

ampersand then gt;

63
Q

XML replacement text for ‘ (apostrophe)

A

ampersand then apos;

64
Q

Show XML CDATA section syntax

A

< ! [ CDATA[ the text here ] ] >

65
Q

What clause specifies XML to be produced one line per row called “row” and all fields as attributes.

A

FOR XML RAW

66
Q

Show clause for defining XML namespaces.

A 
Before Select:
WITH XMLNAMESPACES('my name space' AS alias)
67
Q

Show clause of XML standard output.

A

FOR XML AUTO, ELEMENTS, ROOT(‘my root’)

68
Q

FOR XML comes after what clause

A

ORDER BY

69
Q

What does ELEMENTS do in FOR XML?

A

The columns are shown as sub-elements rather than attributes of the first column of the select statement.

70
Q

XML: How can you force a column to appear as an attribute?

A

Give it an alias name with @: “Customer AS ‘@custid’” … FOR XML PATH

71
Q

Show XML PATH example.

A 
SELECT EmployeeID "@EmpID", 
       FirstName  "EmpName/First", 
       MiddleName "EmpName/Middle", 
       LastName   "EmpName/Last" ... 
FOR XML PATH('Employee'), Root('AllEmps')
72
Q

Converting XML to a table is called _____

A

Shredding

73
Q

List three options that can be added to a Create View statement.

A

WITH ENCRYPTION
WITH SCHEMABINDING
WITH VIEW_METADATA

74
Q

List four restrictions of views.

A

1) they cannot be ordered (no ORDER BY allowed)
2) you can’t pass parameters to them
3) they can’t build tables either permanent or temporary
4) they can only reference permanent tables

75
Q

Boolean operators: True or False
DECLARE @X as N’ ‘;
SELECT @x.query(‘(1, 2, 3) = (2, 4)’)

A

True

76
Q

Boolean operators: True or False
DECLARE @X as N’ ‘;
SELECT @x.query(‘(1, 2, 3) != (1)’)

A

True

77
Q

How do you move a Categories table from schema A to schema B?

A

ALTER SCHEMA B TRANSFER A.Categories;

78
Q

What’s the difference between DECIMAL and NUMERIC?

A

None

79
Q

Which is the most accurate: REAL, FLOAT, or DECIMAL

A

DECIMAL

80
Q

Give the format of a primary key constraint.

A

CONSTRAINT name PRIMARY KEY(column)

81
Q

Give the format of a unique constraint.

A

CONSTRAINT name UNIQUE(column)

82
Q

Must a Unique constraint column be NOT NULL?

A

No, but it can only have one row with that column containing NULL.

83
Q

Show format of Foreign Key constraint.

A

ALTER TABLE xyz WITH CHECK
ADD CONSTRAINT fk_xyz FOREIGN KEY(xyzID)
REFERENCES foreignTable(primaryOrUnique)

84
Q

What statement allows you to insert a value into an identity column?

A

SET IDENTITY_INSERT tablename ON;

85
Q

Rather than using a non-deterministic UPDATE, what can you use instead to generate an error if multiple rows match a join?

A

MERGE

86
Q

What function returns the current (last used) identity value

A

IDENTITY_CURRENT

87
Q

DELETE requires what permissions?

A

DELETE

88
Q

TRUNCATE requires what permissions?

A

ALTER

89
Q

Which resets the identity counter (DELETE or TRUNCATE)?

A

TRUNCATE

90
Q

Which (DELETE OR TRUNCATE) can be used on a table with an index?

A

DELETE only

91
Q

What is the rule for using DELETE or TRUNCATE on a table that has foreign keys pointing to it?

A

DELETE will work as long as no foreign key references match the deleting records. Truncate is disallowed in all cases.

92
Q

What does SCOPE_IDENTITY generate?

A

The identity value last generated in the current scope and session.

93
Q

What does the @@IDENTITY variable generate?

A

The identity value last generate in the current session regardless of scope.

94
Q

What does IDENTITY_CURRENT generate?

A

The last value generated in the table regardless of scope or session.

95
Q

Show statement to reseed the identity value.

A

DBCC CHECKIDENT(‘table’, RESEED, value)

96
Q

Show statement to create a sequence.

A 
Create SEQUENCE mySeq
minvalue 1
maxvalue 100
start with 5
increment by 2
cycle
97
Q

Show how to acquire the next sequence number.

A

SELECT NEXT VALUE FOR mySeq;

98
Q

Statement to cause a sequence to restart at another value.

A

ALTER SEQUENCE mySeq RESTART WITH 1;

99
Q

Give the form of the MERGE statement.

A 
MERGE INTO target_table AS TGT
USING  source_table AS SRC
   ON predicate
WHEN MATCHED [AND predicate]
   THEN action
WHEN NOT MATCHED [BY TARGET] [AND predicate]
    THEN action
WHEN NOT MATCHED BY SOURCE [AND predicate]
   THEN action;
100
Q

Using MERGE, what database object identifier indicates the action performed.

A

OUTPUT $action, …

101
Q

List five types of locks.

A

1) Shared
2) Exclusive
3) Update
4) Intent
5) Schema

102
Q

List 3 types of transaction main isolation levels.

A

1) Read Committed
2) Read Uncommitted
3) Read Committed Snapshot - same as Read Committed but not blocking on saved records.

103
Q

List 3 other transaction isolation levels.

A

1) Repeatable Read (phantom possible)
2) Snapshot (no phantom)
3) Serializable (strongest) - all reads repeatable and news rows not allowed in underlying tables.

104
Q

What is the default isolation level for a session?

A

Read Committed

105
Q

What is the default isolation level for a Windows Azure Database?

A

Read Committed Snapshot

106
Q

Show a safe way to convert data.

A

SELECT TRY_CONVERT(DateTime, ‘1752-12-31’) (returns NULL because DATETIME begins at 1/1/1753

107
Q

Show an example of parsing a string to a data type.

A

SELECT TRY_PARSE(‘1’ AS INTEGER);

108
Q

List the functions that can provide error information in a catch block.

A

1) ERROR_NUMBER
2) ERROR_MESSAGE
3) ERROR_SEVERITY
4) ERROR_LINE
5) ERROR_PROCEDURE
6) ERROR_STATE

109
Q

Show format of the sp_executesql statement.

A

EXEC sp_executesql @statement, @params, @ param1, …

110
Q

Name three kinds of WAITFOR

A

WAITFOR DELAY,
WAITFOR TIME,
WAITFOR RECEIVE

111
Q

Show example of using sp_executesql.

A

set @command = N’Select * from catalog where SalesId = @Id;’
set @parameterType = N’@Id INT’;
set @parameterValue = 123;
sp_executesql @command, @parameterType, @parameterValue;

112
Q

When and how should you test to see if an Insert, Update, or Delete affected any rows?

A

Test @@ROWCOUNT = 0 very first row of the trigger. Must be first row.

113
Q

What two kinds of triggers are there?

A

After and Instead of

114
Q

Are result sets allowed in Triggers?

A

It is deprecated.

115
Q

INSTEAD OF triggers are typically used on Views. Why?

A

Because you can only update one table in a view. The trigger would provide the capability to make a coherent update of multiple tables possible.

116
Q

Besides @@ROWCOUNT, what two functions can you use in a trigger to get useful information.

A

UPDATE() - determines is a particular column was updated

COLUMNS_UPDATED() - gives a

117
Q

show format of a table-valued function.

A 
CREATE FUNCTION fn 
(
@parm1 as int,
@parm2 as varchar(10)
)
RETURNS TABLE AS RETURN
(
   SELECT ....
);
118
Q

Show form of a multistatement Table-Valued UDF (under defined function)

A 
CREATE FUNCTION fn
(
   @parm1 as int,
   @parm2 as char(4)
)
RETURNS @returntable TABLE
(
   c1 int,
   c2 char(4)
)
AS
BEGIN
   INSERT @returntable
        SELECT @parm1, @parm2
   RETURN
END;
119
Q

List five UDF Options.

A 
Encryption
schemabinding
returns null on null input
called on null input
execute as
120
Q

Define three characteristics of ISOLATION LEVEL SERIALIZABLE

A

Ensures transaction statement:
1 - cannot read data altered but not committed,
2 - cannot modify data READ by the current transaction until transaction commits,
3 - cannot insert rows with keys in the range read by the current transaction until it completes.

121
Q

Define READ UNCOMMITTED isolation level

A

I can read rows that have been altered by other transactions even though they haven’t been committed yet.

122
Q

Define READ COMMITTED isolation level

A

I cannot read rows that have been altered by other transactions before they have been committed.

123
Q

Define REPEATABLE READ isolation level

A

1) Cannot read data altered by other transactions but not yet committed
2) Other transactions cannot modify data I have read (making my read “repeatably consistent”)
3) Other transactions can insert rows however.

124
Q

Define SNAPSHOT isolation level

A

All data I read will be consistent with the state it was in at the beginning of my transaction.

125
Q

Describe the use of NTILE

A

NTILE(5) OVER (Order by Rate Desc), …

divides the partitions into tiles or groups or approximately equal sizes.

126
Q

Describe RANK versus DENSE RANK

A

RANK orders the entities and assigns the same rank to those that are a match - though the rank at all times means: “how many items are less than me.” DenseRank means “how many RANKS are less than me.”

127
Q

List some style parameters for Convert for dates.

A

101 - US month/day/year
103 - British/French day/month/year
104 - German day.month.year
105 - Italian day-month-year

128
Q

What is the value range for tinyint

A

0 to 255 – 1 byte

129
Q

What is the value range for smallint

A

-32,768 to 32,767 — 2 bytes

130
Q

What is the value range for int

A

2 billion – 4 bytes

131
Q

How many bytes are used for bigint

A

8 bytes

132
Q

What is the equivalent to COMMIT WORK

A

COMMIT TRANSACTION

133
Q

How is a ROLLBACK WORK different from a ROLLBACK TRANSACTION.

A

ROLLBACK WORK can only rollback to the beginning of the transaction - not to a save point.

134
Q

What is the effect of SET XACT_ABORT ON or OFF?

A

ON requires a complete rollback for any error while OFF allows low-severity errors to continue.

135
Q

What function can you use to generate a RANDOM identifier for a UNIQUEIDENTIFIER field either in the declaration or in a query?

A

NEWID()

Microsoft Certifications (1 decks)

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