How to create a SQL database

Content:
1. Install SQL Server
2. Create sample database

---

In order to create a SQL database, you will need a SQL Server installed on your box. You can install any free SQL servers like Microsoft SQL Server (Express), MySql or even Sqlite.

The script below is for MS SQL Server and demostrates how to create a simple database with tables for Persons and Departments. This database will be used in code examples that will demonstrate data access.

In the management tool that you installed with server run the commands bellow:

Create database

SQL
  DROP DATABASE
  CREATE DATABASE Basics COLLATE Latin1_General_CI_AI

Create schema

SQL
   CREATE SCHEMA MY_SCHEMA AUTHORIZATION dbo

Create tables

SQL
CREATE TABLE MY_SCHEMA.departments (
	id int identity primary key clustered,
	name varchar(20),
  head int
)

CREATE TABLE MY_SCHEMA.persons (
	id int identity primary key clustered,
	firstName varchar(20), -- CHECK (FirstName IN ( 'John', 'Petr', 'Aul')),
	lastName varchar(20) not null,
	height int CHECK ( height < 250),
	birthday datetime,
	photo varbinary(max),
	department_id int CONSTRAINT Dept FOREIGN KEY REFERENCES MY_SCHEMA.Departments(id)

Populate data

SQL
INSERT INTO MY_SCHEMA.departments (name) VALUES ('IT')
INSERT INTO MY_SCHEMA.departments (name) VALUES ('Payroll')

INSERT INTO MY_SCHEMA.persons (firstName, lastName, height, birthday, department_id) 
VALUES ('John', 'First', 180, '1960-01-02', 1)

INSERT INTO MY_SCHEMA.persons (firstName, lastName, height, birthday, department_id) 
VALUES ('Paul', 'First', 190, '1970-03-02', 1)

INSERT INTO MY_SCHEMA.persons (firstName, lastName, height, birthday, department_id) 
VALUES ('George', 'First', 190, '1980-05-02', 1)