TSQL – introduction – the Select statement (2 of 4)

This is part 2 of a series of 4 blogs, where I introduce you to TSQL.I will go through the following content in this part:

  1. The simple Select statement
  2. Where statement
  3. Ordering / Sorting
  4. Join
  5. Simple grouping

With these four types of SQL Queries, then you are ready to extracting and working with data from a SQL Server. Of course you need to practice, but Google is a good place to start learning more.

The simple select statement

You have already learned your first TSQL Statement:

select *
from sys.databases

This simple SQL Statement is read from below to top, and * means all columns, so I could translate this statement into: From the table Sys.databases show me all columns and all rows. You should get a resultpane like what you see below.

Now, in general retrieving all columns by using * is not a good idea, often (well, I would say always), we should tell what columns we want to show, so I will modify my query and write:

select    
        [database_ID]        as DatabaseID
        , [name]                as DatabaseName
from sys.databases

You should get a resultpane like this (please note: You will not have the same rows, because you dont have the same databases in your SQL ServeR):

Actually I did two things:

  1. I only selected the columns that I should use, and in this situation it was name and Database_ID.
  2. I added an Alias to the columns so that I could get userfriendly names.

Where

You have now seen how to extract data from a SQL Server, and how to limit the output by writing explicitly the columns that you want to see. Now we want to reduce the number of rows, and we always do that by a where clause. So please try to write:

select    
        [database_ID]        as DatabaseID
        , [name]                as DatabaseName
from sys.databases
where database_ID<7

This query will return all rows that where database_ID is less than 7:

Where is complex and you can do a lot of operations, including the simple =, <, >, <>, like, not like. It is all about comparing something to something else.

NOTE!!! The TSQL Language is very simple, but you Must remember to write things in the right order, Select before From and From before where and so on.

Ordering / sorting data

As you migt see from the previous example, then the rows are not sorted. 2 is the last database and it should have been in second place, so we can use the clause ORDER BY to sort something.

select    
        [database_ID]        as DatabaseID
        , [name]                as DatabaseName
from sys.databases
where database_ID<7
Order by DatabaseID

This will order the output. One small detail is that in the order by you can use a columns alias, that means that the next two queries are equally good:

— QUERY 1
select    
        [database_ID]        as DatabaseID
        , [name]                as DatabaseName
from sys.databases
where database_ID<7
Order by name

— QUERY 2
select    
        [database_ID]        as DatabaseID
        , [name]                as DatabaseName
from sys.databases
where database_ID<7
Order by DatabaseName

I can write name or databasename. Also note — QUERY 1. That is a comment and an easy way to write comments is to use — (minus x 2).

Joining

No matter how much I would like to, then I don’t have a change to show everything about joining in this small blog, so what I will do is, that I will show you one of the joins, and that is called an Inner join. we have many other types of joins: Left outer join, Right outer join, full join and Cross join to mention the most important.

Joining is about getting data from more than one table, and that is nice to know how to do, because Microsoft SQL Server is a relational database, and relational means that we have data in many tables and we must know how to extract all data. If you write the following statement:

use master
go

select object_id, name
from sys.objects
where name=’sysfiles1′

Then you get information about one of the tables in master database called sysfiles1. What if we want to see the columns in that table? then we could write:

select column_ID
        , name
from sys.columns
where object_ID=8

Now sometimes it would be nice to have all that in one query, so I could write:

select    sys.objects.object_id    as TableID
        , sys.objects.name        as TableName
        , column_id                as ColumnID
        , sys.columns.name        as ColumnName
from sys.objects
    inner join sys.columns on sys.objects.object_ID=sys.columns.object_id
where sys.objects.name=’sysfiles1′

A lot of new stuff happened in this query. Lets try to work through it, by looking at the two lines in the from clause (the lines starting with FROM and Inner join).

First of all I wants to get data from a table called sys.objects. I want to join it with a table called sys.columns and I will do it on the column object_ID that are in sys.objects and object_id that are in the table  sys.columns.

Inner join means that I should only see rows that are in both tables.

In the select statement we have two columns with the name NAME. therefore we must write the column with the table name in front.

Please test and play with that until you are familiar with the inner join.

Grouping

Sometimes we want to group to e.g. count the number of rows. In the example above, it could be nice to change the query, so that we could see how many rows that are in the table sysfiles1. When we want to group, we need (in 95% of all situations) a new line called GROUP BY and some kind of aggregate function:

use master
go

select    sys.objects.object_id    as TableID
        , sys.objects.name        as TableName
        , count(*)                as [Number of Columns]
from sys.objects
    inner join sys.columns on sys.objects.object_ID=sys.columns.object_id
where sys.objects.name='sysfiles1'
group by sys.objects.object_id    
        , sys.objects.name        

As you can see, then I have added a group by and in the select clause I have added count(*). Count is an aggregate like Sum, avg, max, min and so on, and a simple learing rule is: If you want to aggregate, then you columns in the select clause should either be an aggregate (SUM, COUNT e.g. ) or be in the GROUP BY section. NOTE! You can’t use aliases in the group by.
Now you are ready to try creating objects, by using DDL Queries.

<<– Go back to part 1      Go to part 3 –>>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s