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

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