Brainscape's Knowledge GenomeTM

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


See full index

70-461 Querying Microsoft SQL Server 2012 > Measure Up Create Database Objects > Flashcards

Measure Up Create Database Objects Flashcards

1
Q

What are the two types of DML triggers?

A

After

Instead of

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

What should you use to ensure that the underlying table structure cannot be modified?

A

With Schemabinding

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

What should you use to ensure that no modification can be made that would prevent a row currently included in the view from being excluded from the view?
Note: when a query is issued against this view that attempts to change the value of the column data, an error occurs.

A

With Check Option

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

What should you use to hide the definition of how a view was coded?

A

With Encryption

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

Which constraint is used to uniquely identify each record and not allow any blank values?

A

Primary Key constraint

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

What should you use to ensure that only metadata for the view can be read, not that of the underlying base tables?

A

With View_Metadata

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

Which clause enforces the restriction that only rows that match the condition defined by the view can be modified through the view?

A

With Check Option

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

List the statement Syntax order used to turn off constraint checking

A

Alter [Table]

NoCheck Constraint

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

List the statement syntax order used to fix a table accidentally saved as part of the incorrect schema.

A

Alter [Schema]

Transfer [Table]

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

Which function can be used within a trigger to determine whether an Update or Insert statement has attempted to modify the value in a column?

A

Update()

**Note: you pass the name of the column to the function within the parentheses

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

List the statement Syntax order used to modify an existing column in a table.

A

Alter [Table]

Alter [Column]

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

Which command should be used to remove a table?

A

Drop [Table]

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

What commands are used to remove rows from a table, but not to delete the entire table structure?
Note: 2 commands can be used.

A

Truncate [Table]

Delete [Table]

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

Can an index be created on a view that is based on a query that performs a Right Join?

A

No
Note: A right join is an an outer join. You cannot create an index on a view that is based on a query that performs an outer join.

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

What statement should be used to combine the result sets and return all the records from both Select statements?

A

Union All

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

What command should be used to remove a view?

A

Drop View

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

What command should be used to remove rows from a view?

A

Delete View

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

Can you use Alter [Column] to rename a column name?

A

No

Note: To rename a column, you must execute the sp-rename stored procedure.

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

List the statement Syntax order used to add new columns/fields to a pre-existing table?

A

Alter [Table]

Add [Column(s)]

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

What commands should you use if you want to create a Table in a Schema that doesn’t exist?

A

Create [Schema]
Create [Table]
Note: Because the Schema doesn’t exist, both should be created.

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

A foreign key constraint can be defined in the column definition by using either what keyword or clause?

A

References

Foreign Key References

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

Which clause causes the data to be formatted with each table represented by an element and each column represented as an attribute?

A

For XML Auto clause

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

Which clause is used to identify the name of the outer element?

A

For XML Path

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

What view hint should be used to force the query optimizer to use indexes built on the view?

A

NOEXPAND view hint

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

What view hint should be used to force the execution plan to ignore the index?

A

Force_Scan view hint

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

Which command is used to modify existing columns in a table but not create new ones?

A

Alter [Column]

Note: Use Add [Column] to add a new column

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

To create a column that does not allow NULL values, which keyword is used?

A

Not Null

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

What keyword is used to create a computed column and specifying the formula used to calculate the column in parentheses?

A

As
Note: looks like this
Column As (Column + Column)

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

Which trigger should be used to update view when more than one base table needs to be updated?

A

Instead Of

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

If you want to ensure that when a record is deleted in the parent table of the relationship, the value of the Foreign Key is updated. What Referential Integrity option is used for this task?

A

On Delete Set Default

Note: This will ensure that the value of the Foreign Key is updated.

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

What keyword can be used in a Correlated Subquery to filter the results set so that it contains only the rows that match at least one row in the other table?

A

Exists

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

What type of integrity is used to validate entries for specific columns?

A

Domain Integrity

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

What type of integrity is used to define a row as a unique entity?

A

Entity Integrity

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

What type of integrity is used to define relationships between tables?

A

Referential Integrity

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

Which function would be used to return a varbinary bit pattern indicating which columns in a table or view had been modified?

A

Columns_Updated

36
Q

Which function would be used to return event information used with a DDL Trigger?

A

EVENTDATA

37
Q

What type of value will the Update function return if the column referenced with the function has been modified?

A

Boolean

38
Q

What constraint is used to provide a relationship between tables?

A

Foreign Key constraint

39
Q

Does the Unique constraint allow for a single Null value?

A

Yes

40
Q

What clause needs to be used within a View to be able to use the Order By statement?

A

Top

41
Q

What clause is used to return unique records?

A

Distinct

42
Q

What can you use with Order By to sort the order?

A

Asc or Desc

43
Q

What type of view is a special type of view that is defined by a Union All of tables structured in the same way but, stored separately as multiple tables?

A

Partitioned View

44
Q

What clause allows you to create a subquery that returns True as soon as it encounters a match?

Note: It’s the optimal way to create a query based on whether there is a row corresponding that matches a condition in a different table.

A

Exists

45
Q

Are Alter Column and Add Column valid statements within a view?
For example If you need to add a column to a view?

A

No

Use the Select statement in a view to include a column

46
Q

You can create a column that contains sequential integers by creating a column as what type of column?

A

Identity

Note: you need to specify the data type before using the identity property

47
Q

The identity property accepts two values, what are they?

A

The starting value and the increment value

48
Q

You can apply what type of constraint to a calculated column on if you include the Persisted keyword.

A

Check

49
Q

What function is used to generate a Globally Unique Identifier (GUID) that is sequential to the last value generated?

A

NEWSEQENTIALID()

50
Q

Does the data type need to be identified before or after the Not Null statement?

A

Before

51
Q

The ISNULL function accepts two parameters, what are they?

A

A value to check and a replacement value.

Note: If the value to check is not Null, the value is returned. If the value to check is Null, the replacement value is returned.

52
Q

Can you define a data type for a computed column?

A

No

53
Q

The Persisted keyword must occur before or after the computation?

A

After

54
Q

What keyword needs to be included if you are defining a Check constraint on a computed column?

A

Persisted

55
Q

You can change the nullability of a column from Not Null to Null by using what statement?

A

Alter Table [Table]

Alter Column [Column]

56
Q

What keyword allows you to define a common table expression (CTE), which allows you to create a temporary result set?

A

With

57
Q

What does CTE stand for?

A

Common Table Expression

58
Q

What operator is used to join two tables with the same set of columns?

A

Union

59
Q

The Union operator keeps only what kind of rows?

A

distinct rows

60
Q

The Union All operator keeps what kind of rows?

A

duplicate rows

61
Q

What join returns all the records in the table specified in the From clause, even if there is not a match with any record in the table specified in the Join clause?

A

Left

62
Q

When you use an aggregate function, you need to either use 1 of 2 clauses, what are they?

A

Group By or Over

63
Q

What function returns the name of the month?

A

DATENAME

64
Q

What join is used to include all rows in the table specified in the Join clause, even if there is not a match with the rows in the table specified in the From clause?

A

Right

65
Q

What keyword allows you to define different ways to group data, for example grouping total sales by month.

A

Grouping Sets

Example: you can group total sales by month.
Oct | Nov | Dec
$632.00 | $257.18 | $1679.30

66
Q

What clause can be used to combine the results from one Select statement with the results from another Select statement.
Note: Clause not Join

A

Union

67
Q

Both result sets need to have a compatible column list to use what clause?

A

Union

68
Q

To create an auto generated column with a uniqueidentifier (GUID), it needs to default with either 1 of 2 functions. What are the functions?

A

NEWID() or NEWSEQENTAILID()

69
Q

To turn off constraint checking when inserting data into a table, what should be used?

A

With No Check

70
Q

When creating an indexed view on a table that contains computed columns, you need to ensure that all columns in the Select statement are what?

A

Deterministic

71
Q

A column must be marked Persisted if it has what constraint or if it participates in what type of relationship?

A

Check constraint

Foreign Key relationship

72
Q

In order to index a view, you need to create it with what attribute?

A

Schemabinding

73
Q

Which option ensures that only the rows included in the view can be modified through the view?

A

Check Option

74
Q

Is the Check Option clause placed after or before the Select statement that defines the view?

A

After

75
Q

What attribute is used to prevent certain type of client libraries from viewing information about the base tables?

A

VIEW_METADATA

76
Q

Can you use a wildcard in the selection list when defining a schema bound view?

A

No

77
Q

When the view is schema bound, you need to refer to the table how?

A

Using its two part name.

78
Q

What field is used to automatically increment the value of the field when a new record is added?

A

Identity(1,1)

Note: Identity (1,1) would start counting at the number 1 and increment the value by 1 for each row inserted into the table.

79
Q

The Identity field has two arguments, what are they and what do they do?

A

Seed - Starts the counting

Increment - what value to add to the current count

80
Q

What constraint is used to force each record to have a unique value?

A

Primary Key Constraint

81
Q

Does the Primary Key field allow for Null values?

A

No

82
Q

What should be added to the view to improve performance of a view?

A

Index

83
Q

Does Aliasing have any effect on query performance?

A

No

84
Q

This clause with the Select statement is used to prevent duplicate records in the result set and return unique records

A

Distinct

85
Q

This clause is used to restrict the total number of records to return from the result set

A

Top

86
Q

Which View can be used to modify the data of an underlying base table?

  • Select with Top
  • Select with Top and Check Option
  • Select with Count
  • Select with Count and Check Option
A

Select with Top

Note: the rest are considered non-updatable and cannot be used to modify the data in an underlying base table.
Hint: No check or count, only top

70-461 Querying Microsoft SQL Server 2012 (2 decks)

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