Measure Up Create Database Objects

Question 1

What are the two types of DML triggers?

Answer 1

After

Instead of

Question 2

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

Answer 2

With Schemabinding

Question 3

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.

Answer 3

With Check Option

Question 4

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

Answer 4

With Encryption

Question 5

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

Answer 5

Primary Key constraint

Question 6

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

Answer 6

With View_Metadata

Question 7

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

Answer 7

With Check Option

Question 8

List the statement Syntax order used to turn off constraint checking

Answer 8

Alter [Table]

NoCheck Constraint

Question 9

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

Answer 9

Alter [Schema]

Transfer [Table]

Question 10

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?

Answer 10

Update()

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

Question 11

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

Answer 11

Alter [Table]

Alter [Column]

Question 12

Which command should be used to remove a table?

Answer 12

Drop [Table]

Question 13

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

Answer 13

Truncate [Table]

Delete [Table]

Question 14

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

Answer 14

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.

Question 15

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

Answer 15

Union All

Question 16

What command should be used to remove a view?

Answer 16

Drop View

Question 17

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

Answer 17

Delete View

Question 18

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

Answer 18

No

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

Question 19

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

Answer 19

Alter [Table]

Add [Column(s)]

Question 20

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

Answer 20

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

Question 21

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

Answer 21

References

Foreign Key References

Question 22

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

Answer 22

For XML Auto clause

Question 23

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

Answer 23

For XML Path

Question 24

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

Answer 24

NOEXPAND view hint

Question 25

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

Answer 25

Force_Scan view hint

Question 26

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

Answer 26

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

Question 27

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

Answer 27

Not Null

Question 28

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

Answer 28

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

Question 29

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

Answer 29

Instead Of

Question 30

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?

Answer 30

On Delete Set Default Note: This will ensure that the value of the Foreign Key is updated.

Question 31

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?

Answer 31

Exists

Question 32

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

Answer 32

Domain Integrity

Question 33

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

Answer 33

Entity Integrity

Question 34

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

Answer 34

Referential Integrity

Question 35

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

Answer 35

Columns_Updated

Question 36

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

Answer 36

EVENTDATA

Question 37

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

Answer 37

Boolean

Question 38

What constraint is used to provide a relationship between tables?

Answer 38

Foreign Key constraint

Question 39

Does the Unique constraint allow for a single Null value?

Answer 39

Yes

Question 40

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

Answer 40

Top

Question 41

What clause is used to return unique records?

Answer 41

Distinct

Question 42

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

Answer 42

Asc or Desc

Question 43

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?

Answer 43

Partitioned View

Question 44

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.

Answer 44

Exists

Question 45

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

Answer 45

No | Use the Select statement in a view to include a column

Question 46

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

Answer 46

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

Question 47

The identity property accepts two values, what are they?

Answer 47

The starting value and the increment value

Question 48

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

Answer 48

Check

Question 49

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

Answer 49

NEWSEQENTIALID()

Question 50

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

Answer 50

Before

Question 51

The ISNULL function accepts two parameters, what are they?

Answer 51

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.

Question 52

Can you define a data type for a computed column?

Answer 52

No

Question 53

The Persisted keyword must occur before or after the computation?

Answer 53

After

Question 54

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

Answer 54

Persisted

Question 55

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

Answer 55

Alter Table [Table] | Alter Column [Column]

Question 56

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

Answer 56

With

Question 57

What does CTE stand for?

Answer 57

Common Table Expression

Question 58

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

Answer 58

Union

Question 59

The Union operator keeps only what kind of rows?

Answer 59

distinct rows

Question 60

The Union All operator keeps what kind of rows?

Answer 60

duplicate rows

Question 61

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?

Answer 61

Left

Question 62

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

Answer 62

Group By or Over

Question 63

What function returns the name of the month?

Answer 63

DATENAME

Question 64

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?

Answer 64

Right

Question 65

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

Answer 65

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

Question 66

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

Answer 66

Union

Question 67

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

Answer 67

Union

Question 68

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

Answer 68

NEWID() or NEWSEQENTAILID()

Question 69

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

Answer 69

With No Check

Question 70

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?

Answer 70

Deterministic

Question 71

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

Answer 71

Check constraint | Foreign Key relationship

Question 72

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

Answer 72

Schemabinding

Question 73

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

Answer 73

Check Option

Question 74

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

Answer 74

After

Question 75

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

Answer 75

VIEW_METADATA

Question 76

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

Answer 76

No

Question 77

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

Answer 77

Using its two part name.

Question 78

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

Answer 78

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.

Question 79

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

Answer 79

Seed - Starts the counting | Increment - what value to add to the current count

Question 80

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

Answer 80

Primary Key Constraint

Question 81

Does the Primary Key field allow for Null values?

Answer 81

No

Question 82

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

Answer 82

Index

Question 83

Does Aliasing have any effect on query performance?

Answer 83

No

Question 84

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

Answer 84

Distinct

Question 85

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

Answer 85

Top

Question 86

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

Answer 86

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