T- SQL checklist

I used this checklist recently as a T-SQL refresher.

The last job where I actually worked with this language was a few years back.

Since I interviewed for a position that requires T-SQL experience, a quick crash "course" was needed.



SELECT DISTINCT = selects distinct values for a column



WHERE clause

   - LIKE operator: for partial matches; the % wildcard is useful in these cases

  - IN operator: the column value is from a list of values

  - BETWEEN operator: the column value is in a range


ORDER BY - sorts the results of the query

GROUP BY - groups the results

HAVING - where clause for groups



|| - operator used for concatenating the values of 2 fields

UNION queries - has the results of both queries, minus the duplicated values

INTERSECT queries - has only the results that exist in both queries

MINUS queries - has only the results from the 1st query that do not exist in the 2nd query

EXISTS - returns true if the subquery has at least 1 result


FUNCTIONS

    COUNT - returns the number of rows that satisfy the WHERE clause

    SUM - sums all values for a column

    AVG - computes the average of all values for a column

    MAX - returns the maximum value for a column

    ADD_MONTHS - add a number of months to a date field

    LAST_DAY - returns the last day of a month

    STRING functions: LTRIM, RTRIM, REPLACE, SUBSTR, LENGTH

    TYPE CONVERSION functions




EMBEDDED QUERIES

JOIN TABLES

DML: INSERT INTO/UPDATE/DELETE




ADVANCED CONCEPTS

      VIEWS

     TRANSACTIONS: BEGIN, SAVEPOINT, ROLLBACK, COMMIT

     CURSORS

     STORED PROCEDURES

     TRIGGERS

      TEMPORARY TABLES

Share this

Alex Siminiuc