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:
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 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
- Use the dropdown to find my database OR
- 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 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 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.