DROP DATABASE
CREATE DATABASE Basics COLLATE Latin1_General_CI_AI
More info and samples on: www.devarchweb.net
Create schema CREATE SCHEMA MY_SCHEMA AUTHORIZATION dbo
More info and samples on: www.devarchweb.net
Create Persons and Department tablesCREATE 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)
More info and samples on: www.devarchweb.net
Populate Persons and Departments tables dataINSERT 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)