During the early days when i planned to permanently enter into Database Administration field, i searched a lot of preliminary books and code snippets. As T-SQL is core of SQL Server DBA, i spend most of time to cover the concepts and to gain as much skill as possible. During that time i prepared some code refreshers that i usually consulted later for practice of T-SQL. So primarily following code and explanation is for beginners and field switchers those want to strat careers in Database Administration. Most of concepts here will be helpful in iinterview questions and day to day T-SQL paradigm. I will try to update this post later also when required.
Code used here is run under pubs database and is compatible to SQL Server 2000 and SQL Server 2005.
Select 'Wellcome to T-SQL'
Concatenation and alias
Concatenation is done using ‘+’. Alias is also used in two ways. Alias can be used in ‘select’ and ‘from’ statement
USE pubs GO SELECT au_fname+au_lname fullName, [STATE]+','+zip AS location FROM authors auth
Here in above script concatenation is used in select list. Alias is used in select list (fullname) and in from clause (auth)
Comparison on string data
Comparison operators can also be used with text/string data
SELECT [STATE] FROM authors WHERE [STATE] > 'ma'
Above script will compare the given strings according to alphabetical order. It will return states that start with alphabets after ma.
IN operator may be used instead of multiple OR
SELECT au_fname,au_lname, [STATE] FROM authors WHERE STATE IN ('TN','OR','MI','MD')
Between operator may be used instead of > and <>
SELECT price FROM titles WHERE price BETWEEN 10 AND 20
T-SQL uses two wild cards
‘%’ is used multiple match and ‘_’ is used for single char match
SELECT au_fname, au_lname FROM authors WHERE au_fname LIKE 'Abr%'
Above script will look for names with Abr as first three characters and any characters after these three chars.
SELECT au_fname, au_lname FROM authors WHERE au_fname LIKE 'Ab_aham'
Above script will look for names starting with Ab and ending with aham, and any character there in place of _.
If we have to find % inside data, then use escape character. Normally @ is used
SELECT notes FROM titles WHERE notes LIKE '%@%%' ESCAPE '@'
Pattern matching may be used when we have a number of expected variations for search
SELECT au_lname, au_fname FROM authors WHERE au_fname LIKE '[l,m,s]%'
Suppose we want to retrieve four letter name whose first char is capital and other are small
SELECT au_lname, au_fname FROM authors WHERE au_lname LIKE '[A-Z][a-z][a-z][a-z]'
Suppose in a case sensitive column we want to retrieve book with title
“life without fear”, but do not know that without is capital or small
SELECT title_id, title FROM titles WHERE title LIKE '%[Ww][Ii][Tt][Hh][Oo][Uu][Tt]%'
We want to retreive just those authors whose first name is 4 char long
SELECT au_fname FROM authors WHERE au_fname LIKE '____'
We can use negate by using <>, !=, ^, not
SELECT au_fname FROM authors WHERE au_fname LIKE '[^][^][^][^]'
You have a column that accepts just 6 chars while inserting data you have to be careful for signs and digits use ‘[A-z][A-z][A-z][A-z][A-z][A-z]’
Order by may be used with column names or numbers. Also we may use calculations for order criteria. Nulls will be placed at start or end of list as spcified by DBMS
SELECT au_fname+au_lname name FROM authors ORDER BY name
Distinct is used just after select key word. Only one distinct may be there per SQL statement. Will be applied to all columns in select list
Distinct performs like group by except it does not sort the data
SELECT DISTINCT [TYPE] FROM titles
Group by works same as distinct. True power comes with aggregate functions like count(). An aggregate can not be specified in group by clause. when using group with aggregate, u have to specify all non aggregate. clolumns in group by clause
SELECT [STATE], COUNT(*) FROM authors GROUP BY [STATE]
- Having clause works like where with a fundamental difference.
- Where clause defines set of data on which grouping is perfomed.
- Having clause defines which groups are going to be return to user. Having clause generally contains aggregates as part of selection criteria
Compute and compute by
Compute is used to generate summary data. It is backword compatibility. Analysis services roll up is advance to it. Following restrictions are applied.
- U can use columns in select list only.
- U must order by compute by column.
- U can use any aggregate function except count(*).
- Columns listed after compute by should be identical to those ordered in select clause