База данных "Company 1"
На панели инструментов SQL Server Management Studio нажимите "Создать запрос". Откроется окно запросов. Скопируйте в окно запросов следующий код для создания базы данных, её таблицы и заполнения таблицы данными:
CREATE DATABASE company1;
GO
USE company1;
CREATE TABLE Org (
ID INT PRIMARY KEY IDENTITY,
Deptnumb INT,
Deptname VARCHAR(30),
Manager INT,
Division VARCHAR(30),
Location VARCHAR(30)
UNIQUE (Deptnumb)
);
INSERT INTO Org (Deptnumb, Deptname, Manager, Division, Location) VALUES
(10, 'Head Office', 160, 'Corporate', 'New York'),
(15, 'New England', 50, 'Eastern', 'Boston'),
(20, 'Mid Atlantic', 10, 'Eastern', 'Washington'),
(38, 'South Atlantic', 30, 'Eastern', 'Atlanta'),
(42, 'Great Lakes', 100, 'Midwest', 'Chicago'),
(51, 'Plains', 140, 'Midwest', 'Dallas'),
(66, 'Pacific', 270, 'Western', 'San Francisco'),
(84, 'Mountain', 290, 'Western', 'Denver');
CREATE TABLE Staff (
ID INT PRIMARY KEY IDENTITY (10, 10),
Name VARCHAR(30) NOT NULL,
Dept INT,
Job VARCHAR(30),
Years INT,
Salary FLOAT,
Comm FLOAT
FOREIGN KEY (Dept) REFERENCES Org (Deptnumb) ON DELETE CASCADE
);
INSERT INTO Staff (Name, Dept, Job, Years, Salary, Comm) VALUES
('Sanders', 20, 'Mgr', 7, 18357.50, NULL),
('Pernal', 20, 'Sales', 8, 18171.25, 612.45),
('Marenghi', 38, 'Mgr', 5, 17506.75, NULL),
('O`Brien', 38, 'Sales', 6, 18006.00, 846.55),
('Hanes', 15, 'Mgr', 10, 20659.80, NULL),
('Quigley', 38, 'Sales', NULL, 16808.30, 650.25),
('Rothman', 15, 'Sales', 7, 16502.83, 1152.00),
('James', 20, 'Clerc', NULL, 13504.60, 128.20),
('Koonitz', 42, 'Sales', 6, 18001.75, 1386.70),
('Plotz', 42, 'Mgr', 7, 18352.80, NULL),
('Ngan', 15, 'Clerc', 5, 12508.20, 206.60),
('Naughton', 38, 'Clerc', NULL, 12954.75, 180.00),
('Yamaguchi', 42, 'Clerc', 6, 10505.90, 75.60),
('Fraye', 51, 'Mgr', 6, 21150.00, NULL),
('Williams', 51, 'Sales', 6, 19456.50, 637.65),
('Molinare', 10, 'Mgr', 7, 22959.20, NULL),
('Kermisch', 15, 'Clerc', 4, 12258.50, 110.10);
