In the previous SQL Basics posts, we’ve looked at querying our data, CRUD operations and transactions.

Let’s now look at creating databases, tables etc. These statements come under the acronym DDL (Database Definition Language).

Creating a database

We can create a database easily in SQL Server using

CREATE DATABASE Employee;

Now this is not accepted by every SQL type database, for example file based databases would probably not support this as the existence of the file is the database.

Creating Tables

To create a table on a database, we might use the following command in SQL Server

USE Employee;

This will ensure that we do not need to prefix our table definitions etc. with the database name. So we can create a table as

CREATE TABLE Person(
   Id int,
   FirstName nchar (20),
   LastName nchar (20),
   Age int
)

Note: the types supported by different databases may differ, but generally you’ll have numeric, string and date/time types (at least).

In the example Person table, we created a table with an Id, FirstName, LastName and Age. We’ve not created any primary key for this, so let’s now delete (or DROP) the table and try again with the following (see below for how to DROP the table)

CREATE TABLE Person(
   Id int IDENTITY(1, 1) not null,
   FirstName nchar (20) not null,
   LastName nchar (20) not null,
   Age int null,
   CONSTRAINT PK_Person PRIMARY KEY CLUSTERED
   (
      Id ASC
   )
)

Now we’ve also been more explicit in stating whether columns may be NULL (Note: NULL is the default for a column and some databases may not support explicit setting to NULL as it’s assumed NULL already). We’re using IDENTITY to create an auto incrementing ID. This may not be available in all SQL databases. Then finally we create a primary key constraint on the table named PK_Person.

DROP TABLE

To delete a table we call

DROP TABLE Person;

obviously all the data will be lost when we drop a table. We also may need to remove relationships to the Person table first, if any exist.

ALTER TABLE

As it’s name suggests, ALTER TABLE allows us to amend a TABLE. It may also be used as part of the TABLE creation process when foreign keys exist with tables which have not yet been created. i.e. we might create all the tables then ALTER TABLE to create the foreign keys etc.

Let’s just make a simple change to add a Nationality field to the table

ALTER TABLE Person 
ADD Nationality nchar(2) null

Whoops Nationality is a little on small size, so we need to alter this column, let’s use

ALTER TABLE Person 
ALTER COLUMN Nationality nchar(20)

Or we could remove the column thus

ALTER TABLE Person 
DROP COLUMN Nationality