lab_5_Lebedev.sql

CREATE DATABASE ÑáîðíàÿDB
ON PRIMARY 
( NAME = SbornayaDB, FILENAME = 'D:\labDB\lab5\ÑáîðíàÿDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON 
( NAME = SbornayaDB_log, FILENAME = 'D:\labDB\lab5\ÑáîðíàÿDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB );
GO

CREATE TABLE Ñáîðíàÿ  (
    ðåãèîí INT PRIMARY KEY NOT NULL,
    êàïèòàí NVARCHAR(100) NOT NULL
);

INSERT INTO Ñáîðíàÿ(ðåãèîí, êàïèòàí)
VALUES (1, 'Èâàíîâ Èâàí Èâàíû÷')


INSERT INTO Ñáîðíàÿ(ðåãèîí, êàïèòàí)
VALUES (2, 'Âèí Äèçåëü')

SELECT * FROM [Ñáîðíàÿ];

GO

ALTER DATABASE ÑáîðíàÿDB
ADD FILEGROUP ÑáîðíàÿFG;
GO

ALTER DATABASE ÑáîðíàÿDB
ADD FILE 
( NAME = 'ÑáîðíàÿData',
  FILENAME = 'D:\labDB\lab5\ÑáîðíàÿData.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB )
TO FILEGROUP ÑáîðíàÿFG;
GO

ALTER DATABASE ÑáîðíàÿDB
MODIFY FILEGROUP ÑáîðíàÿFG DEFAULT;
GO

CREATE TABLE ÃîÊëóá (
    Íàçâàíèå NVARCHAR(100) PRIMARY KEY NOT NULL,
    Àäðåñ NVARCHAR(512) NOT NULL,
	Ðåãèîí TINYINT NOT NULL,
	Ðåéòèíã SMALLINT NULL
);

INSERT INTO ÃîÊëóá(Íàçâàíèå, Àäðåñ, Ðåãèîí, Ðåéòèíã)
VALUES ('Èâàíîâ Èâàí Èâàíû÷', 'ëó÷øåå ìåñòî', 100, 1487)


INSERT INTO ÃîÊëóá(Íàçâàíèå, Àäðåñ, Ðåãèîí, Ðåéòèíã)
VALUES ('sente', 'Ìîñêâà', 0, 1900)

SELECT * FROM ÃîÊëóá;
GO

DROP TABLE ÃîÊëóá;
GO

ALTER DATABASE ÑáîðíàÿDB
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
/*
Ýòîò øàã èçìåíÿåò ãðóïïó ôàéëîâ ïî óìîë÷àíèþ îáðàòíî íà PRIMARY. Ýòî îçíà÷àåò, ÷òî âñå íîâûå òàáëèöû, 
ñîçäàâàåìûå ïîñëå ýòîãî èçìåíåíèÿ, áóäóò ñîçäàâàòüñÿ â ãðóïïå ôàéëîâ PRIMARY, åñëè íå óêàçàíî èíîå.
*/

ALTER DATABASE ÑáîðíàÿDB
REMOVE FILE ÑáîðíàÿData;
GO

ALTER DATABASE ÑáîðíàÿDB
REMOVE FILEGROUP ÑáîðíàÿFG;
GO

CREATE SCHEMA ÑáîðíàÿÑõåìà;
GO

ALTER SCHEMA ÑáîðíàÿÑõåìà TRANSFER dbo.Ñáîðíàÿ;
GO

DROP TABLE ÑáîðíàÿÑõåìà.Ñáîðíàÿ;
GO

DROP SCHEMA ÑáîðíàÿÑõåìà;
GO