Insert Into Pupil
  (Id, Name, DateBirth) Values
  (1, 'Charly', '19.01.2004'),
  (2, 'Sandy', '14.05.2004'),
  (3, 'Jim', '01.12.2003');

(3 row(s) affected)

You can imagine a TABLE in SQL as an Excel spreadsheet. It contains rows and columns with data in their fields.

Let us create a table for pupils. The table should contain information about the pupils unique Id, the Name and the Date of Birth.

The query for such a table will look like this:

CREATE TABLE Pupil
(
  Id int NOT NULL,
  Name varchar(50) NULL,
  DateBirth datetime NULL
);

The Query starts with the command “Create Table [Tablename] (” followed by “Fieldname Datatype Nullable“. A Fieldname should be self explanatory. Available Datatypes see chapter below. Nullable means, if the value must be set. A fields default value is null (kind of empty or let’s undefined).

Datatypes

Here is a list of commonly used Datatypes

Numeric

• int – number between -2.147.483.648 and 2.147.483.647
• bigint – number between -9.223.372.036.854.775.808 and 9.223.372.036.854.775.807
• smallint – number between -32.768 and 32.767
• tinyint – number between 0 and 255
• bit – 1 or 0, use it as boolean true and false
• decimal(precission, scale) – define the total length and the comma-length – e.g. decimal(5,2) 249,16 or decimal(8,3) 12345678,123

Date and Time

• date – date only – e.g. 16/09/1985
• time – time only – e.g. 11:15
• datetime – date and time part combined in one field – e.g. 16/09/1985 11:15

Characters and Strings

• char(size) – uses always the max size – e.g. char(15) = “Hello          “
• varchar(size) – uses only the exact size -e.g. varchar(15) = “Hello”
• text – long text, length up to 2 billion

For further information about SQL Server and available Datatypes see http://technet.microsoft.com/en-us/library/ms187752.aspx.

Introduction  << — >> Part 2

What will you learn?

Understand the creation and modification of tables, how to handle data, do calculations and gain query speed.

Content

1. Chapter 1 – Straight Into Action

   1. Create Table
   2. Insert Into
   3. Select *, Where, Group By, Age, AvgAge
   4. Alter Table (Gender, Size, Notes)
   5. Update, Delete
   6. Create Index
   7. Create Table Class, Join

2. Chapter 2 – Hands On

   1. How To Test – SQL Fiddle
   2. How To Install – Microsoft SQL Server Express
   3. Usefull Tools – SSMS, Profile

Preamble – What you should know first!

Usually you will hear at this point a lot of theory. Terms like “Database normalization”, abstraction, tuples, redundancy and so on. My point of view – forget about it – first let’s see, what a database is and can do for you! Learning by doing.

Once you got the point, you can go further into details and optimizations.

Ok, here are a few terms you should have heard of you start.

Database

Database Management System

Table

Record

Field

Value

Datatype

SQL

DDL

DML

DCL

Query

Database Tool

>> Part 1