New

Long-Term Learning

Learn efficiently and remember over time.

Start Long-Term Learning

Get personalized study reminders at intervals optimized for better retention.
Track your progress on this set by creating a folder
Or add to an existing folder

Add this set to a folder

  • 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.

    Data manipulation language (DML)

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

    SELECT permission

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

    db_datareader, sysadmin, SELECT

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

    Logical processing order

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

    DISTINCT

    Only unique rows can appear in the result set.

    TOP

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

    SELECT...INTO

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

    Cannot, partitioned

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

    Subquery

    An inner select, which is always enclosed in parentheses.

    COMPUTE, FOR BROWSE

    Subqueries cannot have any ____ or ____ clauses.

    ORDER BY, TOP

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

    32 levels

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

    COMPUTE

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

    Compute row aggregate functions

    AVG
    COUNT
    MAX
    MIN
    SUM

    AVG

    Computes the average of all values in a numeric expression.

    COUNT

    Gives the exact number of selected rows

    MAX

    Gives the highest value in an expression

    MIN

    Gives the lowest value in an expression

    SUM

    Produces the total of all values in a numeric expression.

    COUNT(), performing

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

    FOR

    Used to specify either the BROWSE or the XML option.

    BROWSE

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

    FOR BROWSE

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

    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.

    SET NO_BROWSETABLE ON

    Used to set NO_BROWSETABLE to function.

    XML argument

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

    RAW mode

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

    AUTO mode

    Supports nesting in the resulting XML.

    EXPLICIT mode

    Gives more control over how the XML results are formatted.

    GROUP BY

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

    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.

    HAVING

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

    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.

    ORDER BY

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

    COLLATE

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

    OFFSET

    Used to specify the number of rows to be skipped.

    FETCH

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

    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.

    OVER

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

    PARTITION BY

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

    UNBOUNDED PRECEDING

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

    Ranking functions

    RANK
    NTILE
    DENSE_RANK
    ROW_NUMBER

    Ranking functions, partition

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

    Aggregate functions

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

    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

    CUME_DIST
    LEAD
    FIRST_VALUE
    PERCENTILE_CONT
    LAG
    PERCENTILE_DISC
    LAST_VALUE
    PERCENT_RANK

    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.

    GETDATE

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

    DATEADD

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

    DATEDIFF

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

    DATEPART

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

    TRUNCATE TABLE

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

    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.

    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.

    CREATE TRIGGER

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

    Batch, first

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

    Only one, reference

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

    DML triggers

    Used to invoke when a user modifies data through DML.

    Logon triggers

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

    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.

    WITH ENCRYPTION argument

    Used to prevent the trigger from being published during replication.

    EXECUTE AS argument

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

    AFTER argument

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

    Cascade, Constraint

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

    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.

    Only one, INSERT, DELETE

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

    Nested, 32

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

    Nested, sp_configure

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

    DML, ALTER

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

    Server, logon, CONTROL SERVER

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

    Database, ALTER ANY DATABASE

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

    Joins

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

    Inner join

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

    Outer join

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

    Left, right, full

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

    FROM, WHERE, FROM

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

    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.

    Full outer join

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

    Cross join

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

    Self join

    Used to join a table/view to itself.

    Directly, indirectly, SUBSTRING

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

    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.

    CREATE VIEW

    Used to create a view.

    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.

    Current, first statement

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

    1024

    A new view can have a maximum of ____ columns.

    Sequence

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

    Sequence, not tied

    A ____ is ____ to any table.

    Sequence value types

    tinyint
    smallint
    int
    bigint

    CREATE SEQUENCE

    Used to create a sequence.

    db_owner, db_ddladmin

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

    db_owner, db_datawriter

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

    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.

    INCREMENT BY

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

    MINVALUE

    Used to specify lower bounds for the sequence object.

    MAXVALUE

    Used to specify upper bounds for the sequence object.

    CYCLE

    Used to tell the sequence object if the sequence should restart. Default is NO.

    Full-text queries

    Can be issued against character-based data in a table when there is a full-text index on that table.

    CONTAINS

    Specified in the WHERE or HAVING clauses. Used for precise or fuzzy matches.

    FREETEXT

    Specified in the WHERE or HAVING clauses. Used to match the meaning of words, phrases and strings.

    CONTAINSTABLE

    Rowset-valued function that is referenced like a table for using CONTAINS type search.

    FREETEXTTABLE

    Rowset-valued function that is referenced like a table for using FREETEXT type search.

    LIKE

    Used in a regular SELECT and WHERE class to find a specified pattern in a column.

    Transaction

    A sequence of operations performed as a single logical unit of work.

    Atomic

    Means that every piece of work in the transaction is an integral part of a single unit. All works within the transaction much complete for it to be considered as committed.

    Distributed Transaction Coordinator (DTC)

    Windows server service that can coordinate transactions that update multiple transaction protected resources on a single server computer or distributed across networked server computers.

    Mutual Authentication Required

    The highest secured communication mode you can use in a non-clustered setup.

    BEGIN TRAN, COMMIT TRAN

    To perform multi-statement transactions you need a ____ at the beginning and a ____ at the end.

    ROLLBACK TRAN

    Used to undo all of the work since the last BEGIN TRAN statement.

    TRY...CATCH

    Block of code used for processing errors in T-SQL code.

    CATCH

    Where the error can be dealt with. Once dealt with control will be transferred to the first statement after END CATCH.

    RAISERROR

    Used to fine-tune error-handling. Can have multiple severities that will handle the errors differently.

    Severity 11 to 19

    Control will be transferred to the associated CATCH block. Can provide information on the error that caused the CATCH block to run.

    Severity 10 or lower

    Will only return an informational message to the batch without invoking CATCH.

    Severity 20 or higher

    Will directly close the database connection without invoking CATCH.

    THROW

    An alternative to RAISERROR. Can raise an exception and transfer execution to CATCH within TRY...CATCH block. Will cause a statement batch to end.

    Please allow access to your computer’s microphone to use Voice Recording.

    Having trouble? Click here for help.

    We can’t access your microphone!

    Click the icon above to update your browser permissions above and try again

    Example:

    Reload the page to try again!

    Reload

    Press Cmd-0 to reset your zoom

    Press Ctrl-0 to reset your zoom

    It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

    Please upgrade Flash or install Chrome
    to use Voice Recording.

    For more help, see our troubleshooting page.

    Your microphone is muted

    For help fixing this issue, see this FAQ.

    Star this term

    You can study starred terms together

    NEW! Voice Recording

    This is a Plus feature