MTA: Database Fundamentals Exam Prep (Section 3)

Only one, reference

CREATE TRIGGER can apply to ____ table in the current database, though it can ____ objects in another.

Analytic functions

CUME_DIST LEAD FIRST_VALUE PERCENTILE_CONT LAG PERCENTILE_DISC LAST_VALUE PERCENT_RANK

OFFSET

Used to specify the number of rows to be skipped.

Sequence, not tied

A ____ is ____ to any table.

1024

A new view can have a maximum of ____ columns.

Cascade, Constraint

All referential ____ actions and ____ checks must succeed before a DML trigger fires.

Subquery

An inner select, which is always enclosed in parentheses.

PARTITION BY

Argument for telling the windows function to be applied to each partition separately and that computation will be restarted for each partition.

ROLLUP()

Argument that can be used to generate the simple GROUP BY aggregate rows as well as the subtotal/super-aggregate rows and also a grand total row.

GROUPING SETS()

Argument that can be used to specify multiple groupings of data in a single query.

Nested, 32

By default triggers can be ____ up to a maximum of ___ levels.

db_datareader, sysadmin, SELECT

Members of the ____/db_owner fixed database roles or the ____ fixed server fol can always run ____ at wish.

SUM

Produces the total of all values in a numeric expression.

Logon triggers

Special triggers that fire primarily in response to the LOGON event, which is raised only when a user session is getting established.

AFTER argument

Specifies that the DML trigger can be fired only when all operations in the triggering statement are successfully completed.

COUNT(), performing

You should not use the ____ aggregate in a subquery for ____ an existence check.

EXECUTE AS argument

For explicitly specifying the security context under which the current trigger fires.

RAW mode

Generates a single <row> element per row in the returned rowset.

EXPLICIT mode

Gives more control over how the XML results are formatted.

COUNT

Gives the exact number of selected rows

MAX

Gives the highest value in an expression

MIN

Gives the lowest value in an expression

Data manipulation language (DML)

Used to run queries and add, delete or edit records.

SET NO_BROWSETABLE ON

Used to set NO_BROWSETABLE to function.

INCREMENT BY

Used to specify a value increment or decrement in the sequence object.

FOR

Used to specify either the BROWSE or the XML option.

COLLATE

Used to specify that operation should be performed following a different collation.

INSTEAD OF argument

Used to specify that the DML trigger is to be executed instead of the triggering statement. Cannot be used for DDL or logon triggers.

BROWSE

Used to specify that updates be allowed when you view data under a DB-Library browse mode cursor.

FETCH

Used to specify the number of rows to return as soon as the OFFSET is processed.

db_owner, db_datawriter

Users with the ____ and ____ fixed database roles may also update sequence objects.

db_owner, db_ddladmin

Users with the ____ and the ____ fixed database roles may create, alter and drop sequence objects.

Aggregate functions

AVG MIN CHECKSUM_AGG SUM COUNT STDEV COUNT_BIG STDEVP GROUPING VAR GROUPING_ID VARP MAX

Ranking functions

RANK NTILE DENSE_RANK ROW_NUMBER

Batch, first

If CREATE TRIGGER is part of a ____, it must be the ____ statement.

ORDER BY, TOP

Subqueries can have an ____ clause when a ____ clause is also used.

Sequence

A user-defined schema bound object for generating a sequence of numeric values.

Compute row aggregate functions

AVG COUNT MAX MIN SUM

SELECT permission

Can be inherited from the schema permissions or CONTROL permissions on a table.

FOR BROWSE

Cannot be used in SELECT statements joined by the UNION operator.

Directly, indirectly, SUBSTRING

Cannot have tables joined ____ on ntext, test, or image columns. However, this can be done ____ using ____.

AVG

Computes the average of all values in a numeric expression.

Nested, sp_configure

Disable nested triggers by setting the ____ triggers options of ____ to a value of 0.

Logical processing order

FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP

FROM, WHERE, FROM

Inner joins can be specified in the ____ clause or the ____ clause. Outer joins can be specified only in the ____ clause.

SELECT TOP, ORDER BY

It is recommended whenever you use the ____ statement you use the ____ clause to indicate the rows that are to be affected.

TOP

Only a specified first set or percent of rows can be returned.

DISTINCT

Only unique rows can appear in the result set.

START WITH

Option that can be used to specify a value less than or equal to the maximum and greater than or equal to the minimum value specified.

NO_BROWSETABLE

Option to turn on so that all the SELECT statements will behave as if the FOR BROWSE option has been appended to the statements.

XML argument

Results of the query will be returned as an XML document.

COMPUTE, FOR BROWSE

Subqueries cannot have any ____ or ____ clauses.

AUTO mode

Supports nesting in the resulting XML.

Permissions, table, sysadmin, db_ddladmin

TRUNCATE TABLE ____ are granted to the ____ owner, the members of the ____ fixed server role and the db_owner/____ fixed database roles.

DATABASE argument

The DDL trigger is for applying to the entire database.

ALL SERVER argument

The DDL/logon trigger is to be applied to the current server.

UNBOUNDED PRECEDING

The window will start at the first row of the partition.

Left, right, full

There are three types of outer joins: ____, ____, and ____.

32 levels

There is a maximum of ____ of nesting possible with subqueries.

Aggregate functions, single, ignore

Use ____ to calculate on a set of values in order to return a ____ value. They generate summary values in query results set. Other than COUNT they will ____ null values by default. They are all deterministic.

Analytic functions, compute things

Use ____ to compute an aggregate value based on a group of row. It is possible for them to return more than one row for each group. Use them to ____ things like moving averages, percentages, etc.

Ranking functions, partition

Use ____ to return a ranking value for each row within a ____. They are nondeterministic.

HAVING

Used for specifying search conditions for a group or an aggregate.

Inner join

Used to combine and add records from two sources to a query's results when the values can meet the criteria defined.

Left outer join

Used to combine rows from two sources where all of the left side rows will be included.

Right outer join

Used to combine rows from two sources where all of the right side rows will be included.

UNION

Used to combine the results of multiple queries into a single result set which includes all the rows that belong to all queries participating. Removes duplicates unless ALL is included.

SELECT...INTO

Used to create a new table in the default filegroup with the insertion of the resulting rows from the query into it.

CREATE SEQUENCE

Used to create a sequence.

CREATE TRIGGER

Used to create a trigger, which is a special kind of stored procedure that will automatically run when an event takes place.

CREATE VIEW

Used to create a view.

Data definition language (DDL)

Used to create and delete databases/tables, define table rows and columns, create indexes and take other actions that change the structure of the database.

TRUNCATE TABLE

Used to delete all rows from a table without logging the individual row deletions.

OVER

Used to determine the partitioning and ordering of a rowset before allowing the associated window function to be applied.

WITH CHECK OPTION

Used to ensure that no changes to the underlying data can be made through the view that would cause the data to not conform to the definition. Can also be used to ensure the data will remain visible through the view when modified through the view.

SCHEMABINDING

Used to ensure that the base table cannot be modified to affect the view definition.

COMPUTE

Used to generate subtotals in a result set. ntext, text and image cannot be dealt with through these.

GROUP BY

Used to group a selected set of rows into a set of summary rows by the values of columns or expressions.

DML triggers

Used to invoke when a user modifies data through DML.

Self join

Used to join a table/view to itself.

ORDER BY

Used to order the result set of a query and at the same time limit the rows that are returned.

WITH ENCRYPTION argument

Used to prevent the trigger from being published during replication.

DATEADD

Used to produce a new date/time value which is based on adding an interval to the concerned date.

DATEPART

Used to produce and integer that forms a specified part of a date.

DATEDIFF

Used to produce the number of date/time boundaries that get crossed between two different dates.

Full outer join

Used to retrieve all the rows from both sides where the join condition is evaluated to true.

Joins

Used to retrieve data from two or more tables or views.

GETDATE

Used to retrieve the current date and time in SQL Server.

Outer join

Used to return all rows from the joined tables/views even where there is no matching row between them.

Cross join

Used to return the product but not the sum of the two sources. Returns the set of all possible row combinations.

Server, logon, CONTROL SERVER

You can create a DDL trigger with ____ scope or a ____ trigger if you have ____ permission on the server.

DML, ALTER

You can create a ____ trigger if you have ____ permission on the table or view it will be created on.

Current, first statement

You can create a view only in the ____ database and CREATE VIEW must be the ____ in the query.

HAVING, SELECT

You can use ____ only with a ____ statement. May want to use it inside a GROUP BY clause, and text, image and ntext's cannot be used.

Cannot, FOREIGN KEY, itself

You ____ run TRUNCATE TABLE on tables that are referenced by a ____ constraint unless it is referencing ____.

Cannot, indexed view, fire

You ____ run TRUNCATE TABLE on tables that participate in an ____ and they cannot ____ any trigger.

Cannot, partitioned

You ____ use SELECT....INTO to create a ____ table.

Database, ALTER ANY DATABASE

You can create a DDL trigger with ____ scope if you have ____ DDL TRIGGER permission in the current database.

Only one, INSERT, DELETE

____ INSTEAD OF trigger per ____, UPDATE or ____ statement on a table/view.

Sequence value types

tinyint smallint int bigint