TSQL ‚Äď introduction – Working with data (4 of 4)

At this time you should be able to select data and working with objects, and now we will very quickly look at three important statements. They are called DML statements (Data Manipulation Language):

  1. Insert
  2. Update
  3. Delete

First we create a new database and a table:

use master
go

create database tsqldemo
go

use tsqldemo
go

create table    firstTable    (ID        int        identity    primary key
                             , name    varchar(200)
                             , birthPlace    varchar(200)
                            )

If that works and you get “Command(s) completed successfully), then we can continue with datamanipulation.

INSERT

Insert is very complex, and we will only look at the most simple form: Inserting simple values.

The syntax is

Insert into firsttable (name, birthPlace) values ('Soren', 'Vejle')

The first () where it says Name and birthplace, tells the system, that you want to insert values into the columns name and birthplace. We don’t want to insert into ID column, because it is an Identity column and SQL server takes care of that for us.

then we have the word Values and then the second (), where we have added the values (remember: text is put into single quotes ‘).

It’s so easy and you can try to write: Select * from firsttable. You should have one row.

UPDATE

Update has another syntax, so if I want to update firsttable, I can write:

Update firsttable
set name='Pernille'

It’s quite easy. You can also add a where clause:

Update firsttable
set name=’Soren’
Where name=’Pernille’

then it is only rows that meets the where clause that are updated.

DELETE

Delete has the following syntax:

Delete firsttable
where name='Soren'

ONE IMPORTANT NOTE ABOUT DELETE!!!!!!!! Always write a delete as a select clause, and then change Select * from xxxx to Delete XXXX. What i mean is. If I wan’t to delete all entries from my table where name = ‘Soren’, then I would write:

Select *
from firsttable
where name='Soren'

and then change it to:

Delete firsttable
where name='Soren'

That’s it. You now know the basics of TSQL. You have a lot to learn, but you have a good basis to move on. Use books online and Google to find more information or just write me on this blog, and I will try to help you.

<<– Back to step 3

TSQL ‚Äď introduction – Working with objects (3 of 4)

In this chapter you will learn about DDL Queries. DDL (Data Definition Language) is used to Created, Drop or altering new objects. an object could be a database, a table, a user an much more, but no matter what object, then DDL is very easy: You just need to learn three words:

  1. CREATE
  2. ALTER
  3. DROP

When you know these words, then you just need to learn the syntax for all the different types of objects. I will focus on Databases and tables.

CREATE

Create is very simple, and if you want to create a new database, you simply write

create database tsqldemo

Please try that and press f5. Now you have a new database, and if you want to test that, then clear the query window and write USE tsqlDemo. Press F5. If you don’t get any errors, then the database is created, and you are now in the database, ready to write some queries.

Why is it then difficult? Well, look at this link and see all the syntax for the Create database. You can do quite a lot of things, and if you don’t know how to configure things correctly, then you are in deep trouble. But don’t worry. Now you know how to create a database at least.

Before we start creating new tables, then we will make sure that you are in the right database. In the top left corner there is a drop down menu, and it should say the name of your database. It shows what database you are writting queries against:

As you can see, then I am in the wrong database, so I need to

  1. Use the dropdown to find my database OR
  2. write USE TSQLDEMO

Now I am ready to create the first table, and I write:

use tsqldemo
go

create table firstTable    (ID            int                identity        primary key
                         , name        varchar(200)
                        )    

I don’t need to write use tsqldemo and go if I am in the right database, but I am not ūüôā

As you might see, then we again just write CREATE object type, object name. Object type = table and object name=first table. Click on this link to see the full documentation for the Create Table.

Now I know that a table definition should always include () and within the brachets, you always write the columns that you want.

I have two columns, ID and name, and ID has a datatype Int for Integer and name has a datatype of varchar and it can be up to 200 characters long.

The ID column is an Identity, that means that the SQL server puts in the value and it adds 1 for each new row. that means: first row is 1, next is 2 and so on.

The ID column is also a primary key, that means that you can only have one value with the same ID (One column with ID 1 and so on).

NOW YOU HAVE ALL THE objects you need to start storing data (but we will wait with that a little while).

ALTER

Alter is when you want to change an existing object, that could be adding a column to a table, changing a property on a database or something else.

If I want to add a column to my table i just write:

alter table firstTable
add  birthPlace    varchar(200);

DROP

Drop is deleting an object, BUT please note that we are not talking about DELETING an object, because we DELETE DATA but DROP OBJECTS, so if I want to drop my table, then I write:

drop table firstTable

If I want to drop my database I write:

use master 
go

drop database tsqldemo

when dropping a database, it is important to learn that you must close all connections, and the best way when testing, it is very easy just to write Use master, so that you are not in the database you want to drop.

That’s it. You are now ready to learn to fill in data.

<<– Go back to blog 2¬†¬†¬†¬† go to blog 4 –>

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 –>>

TSQL – introduction (1 of 4)

I have created this blog, for people that want’s a small introduction to Microsoft’s querylanguage, T-SQL (Transact SQL). The blog is split into three parts:

  1. Introduction / getting started.
  2. DDL Queries (DDL stands for Data Definition Lanuage = We create tables e.g.)
  3. DML queries (DML stands for Data Manipulation Language = We extract or manipulate data)

As you can see from the bulletpoints, then the first learing is: What can we use TSQL for. Well, basically you can use TSQL for many purposes, but in general we split it into two types of work: working with the data and creating/dropping/altering objects, like tables, views, stored procedures e.g. (we get back to that later). The desire to work with data often comes from a need to extract data from some kind of system (e.g. your ERP-system).

For this blog, you must have a version of Microsoft SQL Server installed (2008, 2008R2 or 2012. I used 2008R2 and 2012).

Getting started
We will need to start the Sql Server Management studio (SSMS), and you do that by going to Start –> Programs –> Microsoft SQL Server xxx (where xxx = your version).¬†


When you start the program SQL SERVER MANAGEMENT STUDIO, then you will se a connection screen, where you are asked to connect to an instance of SQL server:

enter your servername and either choose windows authentication or choose SQL Server authentication and then enter your credentials. TYPICAL SETTINGS are localhost as Servername (just enter .) and then Windows Authentication. Atleast that is typical if you run the database on your local machine where you have installed it to learn about SQL server. One detail: You can see that server type is Database Engine. Leave that for now, I will explain that in another post later.

Press connect, and you will se the following picture (or at least something that looks like it):

In the top left corner, there is a button called New Query. Click on that, and then you get a white screen:

Now you just need to learn one button: F5. F5 = Execute, and every time you want’s to run a query, then you mark the query and then run it by clicking F5. when you do that, then you get a screen that looks like this (may vary depending on user settings):

Why not end this first part by writting an SQL Statement. Please try to write:

use master
go

select *
from sys.databases

When you have written that, then press F5 (please note!! If you have highlighted some text, then that is the only thing that will be executed, so either highlight everything or nothing).

You should get a resultpane at the bottom of the window:

Use Master is a simple TSQL statement that changes the execution context to a specific database. In this situation the Masterdatabase, that is a systemdatabase in SQL server. We will work in that database, but Please be careful, because if you make any wrong changes to that database, then you will be in serious problems.

GO is actually not a TSQL statement, but a statement that practically means: “OK, here is a go, please send everything above the go statement (from top of the query window or from the last GO-statement) to the SQL server and execute it.
SELECT * FROM SYS.DATABASES is a very simple T-SQL Statement, that Retrieves all rows and columns from a table called sys.databases.
Congratulation, now you have written and executed your first T-SQL Statement.

Before we move on to blog #2 in this short introduction to T-SQL, then I would point out, that if you are going to work a lot with the SQL server, then I recommend that you download the demo databases ADVENTUREWORKS from www.codeplex.com. Google Download Adventureworks xxxx, where xxxx is your version (e.g. 2008R2).

Go to part 2 –>