second.sql

--http://vitu.oit.cmc.msu.ru/mod/assignment/view.php?id=1820
--1
IF OBJECT_ID('dbo.firma', 'U') IS NOT NULL 
	DROP TABLE dbo.firma; 
CREATE TABLE firma (
	codf int,
	fname varchar(10),
	fio varchar(18),
	city varchar(15)
);

--2
IF OBJECT_ID('dbo.zakaz', 'U') IS NOT NULL 
	DROP TABLE dbo.zakaz; 
CREATE TABLE zakaz (
	codz int,
	codf int,
	codgotov int,
	price int
);

--3
IF OBJECT_ID('dbo.gotov', 'U') IS NOT NULL 
	DROP TABLE dbo.gotov; 
CREATE TABLE gotov (
	codgotov int,
	textgotov varchar(10)
);

--4
INSERT INTO firma(codf, fname, fio, city) VALUES
	(1, 'AAA', 'Иванов',  'Москва'),
	(2, 'BBB', 'Смирнов', 'Москва'),
	(3, 'CCC', 'Никитин', 'Псков'),
	(4, 'DDD', 'Петров',  'Новгород');

--5
INSERT INTO zakaz(codz, codf, codgotov, price) VALUES
	(1, 1, 0, 100),
	(2, 1, 0, 200),
	(3, 2, 1, 200),
	(4, 3, 1, 300),
	(5, 2, 1, 400),
	(6, 4, 2, 500),
	(7, 4, 2, 500);

--5
INSERT INTO gotov(codgotov, textgotov) VALUES
	(0, 'в работе'),
	(1, 'готов'),
	(2, 'отложен');

--6
ALTER TABLE firma ADD CONSTRAINT firma_codf_unique UNIQUE (codf);
ALTER TABLE gotov ADD CONSTRAINT gotov_codgotov_unique UNIQUE (codgotov);
ALTER TABLE zakaz ADD CONSTRAINT zakaz_codf_fk FOREIGN KEY (codf) REFERENCES firma(codf) ON DELETE CASCADE;
ALTER TABLE zakaz ADD CONSTRAINT zakaz_codgotov_fk FOREIGN KEY (codgotov) REFERENCES gotov(codgotov) ON DELETE CASCADE;

--7
SELECT 
	zakaz.codz  'Номер заказа', 
	firma.fname 'Название фирмы'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf;

--8
SELECT
	zakaz.codz 'Номер заказа',
	firma.city 'Город'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf;

--9
SELECT 
	firma.fname      'Название фирмы',
	SUM(zakaz.price) 'Общая сумма по всем заказам фирмы',
	COUNT(*)         'Количество заказов фирмы'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf
GROUP BY firma.fname;

--10
SELECT 
	SUM(zakaz.price) 'Сумма заказов по городу',
	firma.city       'Город'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf
GROUP BY firma.city;

--11
SELECT 
	SUM(zakaz.price) 'Сумма заказов по городу Москва'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf AND
WHERE firma.city = 'Москва';

--12
SELECT 
	COUNT(*) 'Количество заказов у фирмы CCC'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf
WHERE firma.fname = 'CCC';

--13
SELECT 
	COUNT(*)        'Количество заказов у фирмы BBB',
	gotov.textgotov 'Готовность'
FROM zakaz
LEFT JOIN gotov
	ON zakaz.codgotov = gotov.codgotov
LEFT JOIN firma
	ON zakaz.codf = firma.codf
WHERE
	gotov.textgotov = 'готов' AND
	firma.fname     = 'BBB'
GROUP BY gotov.textgotov;

--14
SELECT 
	zakaz.codz      'Номер заказа',
	gotov.textgotov 'Готовность заказа'
FROM zakaz
LEFT JOIN gotov
	ON zakaz.codgotov = gotov.codgotov;

--15
SELECT 
	zakaz.codz      'Номер заказа',
	gotov.textgotov 'Готовность заказа'
FROM zakaz
LEFT JOIN gotov
	ON zakaz.codgotov = gotov.codgotov
WHERE gotov.textgotov = 'готов';

--16
SELECT DISTINCT
	firma.city      'Город',
	firma.fname     'Название фирмы',
	gotov.textgotov 'Готовность'
FROM firma
LEFT JOIN zakaz
	ON zakaz.codf = firma.codf
LEFT JOIN gotov
	ON gotov.codgotov = zakaz.codgotov
WHERE gotov.textgotov = 'готов';

--17
SELECT DISTINCT
	zakaz.codz      'Номер заказа',
	firma.fname     'Название фирмы',
	gotov.textgotov 'Готовность заказа'
FROM firma
LEFT JOIN zakaz
	ON zakaz.codf = firma.codf
LEFT JOIN gotov
	ON gotov.codgotov = zakaz.codgotov;

--18
SELECT DISTINCT
	gotov.textgotov 'Категория готовности заказа',
	COUNT(*)        'Количество заказов'
FROM zakaz
LEFT JOIN gotov
	ON gotov.codgotov = zakaz.codgotov
GROUP BY gotov.textgotov;

--19
IF OBJECT_ID('dbo.group', 'U') IS NOT NULL 
	DROP TABLE dbo.[group]; 
CREATE TABLE [group] (
	nomgr int,
	codmng int
);

--20
IF OBJECT_ID('dbo.kto', 'U') IS NOT NULL 
	DROP TABLE dbo.kto; 
CREATE TABLE kto (
	codmember int,
	codstatus int,
	fio varchar(18)
);

--21
IF OBJECT_ID('dbo.status', 'U') IS NOT NULL 
	DROP TABLE dbo.[status]; 
CREATE TABLE [status] (
	codstatus int,
	statustext varchar(255)
);

--22
IF OBJECT_ID('dbo.groupmember', 'U') IS NOT NULL 
	DROP TABLE dbo.groupmember; 
CREATE TABLE groupmember (
	nomgr int,
	codmember int
);

--23
INSERT INTO [group](nomgr, codmng) VALUES
	(1, 1),
	(2, 5),
	(3, 7);

--24
INSERT INTO [status](codstatus, statustext) VALUES
	(1, 'руководитель группы'),
	(2, 'ведущий специалист'),
	(3, 'специалист');

--25
INSERT INTO kto(codmember, codstatus, fio) VALUES
	(1, 1,  'Иванов М'),
	(2, 2,  'Смирнов В'),
	(3, 2,  'Николаев В'),
	(4, 2,  'Тимофеев В'),
	(5, 1,  'Соловьев М'),
	(6, 3,  'Петров С'),
	(7, 1,  'Никитин М'),
	(8, 3,  'Мишутин С'),
	(9, 3,  'Самарин С'),
	(10, 3, 'Петин С');

--26
ALTER TABLE zakaz ADD nomgr int;

--27
BEGIN
	DECLARE @list TABLE (
		id int IDENTITY(1, 1) PRIMARY KEY,
		nomgr int
	);
	INSERT INTO @list(nomgr) VALUES
		(1),
		(2),
		(2),
		(2),
		(2),
		(3),
		(3);

	DECLARE @size_temp int;
	DECLARE @size_original int;
	DECLARE @size_min int;

	SET @size_temp = (SELECT COUNT(*) value FROM @list);
	SET @size_original = (SELECT COUNT(*) FROM zakaz);
	SET @size_min = (SELECT MIN(value) FROM (
			SELECT @size_temp value UNION ALL
			SELECT @size_original 
		) t
	);

	DECLARE @i int;
	SET @i = 1;
	WHILE @i <= @size_min
	BEGIN
		UPDATE zakaz SET zakaz.nomgr = (
			SELECT nomgr FROM @list WHERE id = @i
		) WHERE zakaz.codz = @i;
		SET @i = @i + 1;
	END

	IF @i <= @size_temp
	BEGIN
		INSERT INTO zakaz(nomgr)
			SELECT nomgr FROM @list WHERE id >= @i;
	END
	
END
GO

--28
INSERT INTO groupmember(nomgr, codmember) VALUES
	(1, 1),
	(1, 2),
	(1, 3),
	(1, 4),
	(1, 6),
	(1, 8),
	(2, 5),
	(2, 2),
	(2, 3),
	(2, 8),
	(2, 9),
	(3, 7),
	(3, 2),
	(3, 4),
	(3, 10);

--29
ALTER TABLE kto ADD CONSTRAINT kto_codmember_unique UNIQUE (codmember);
ALTER TABLE [status] ADD CONSTRAINT status_codstatus_unique UNIQUE (codstatus);
ALTER TABLE [group] ADD CONSTRAINT group_nomgr_unique UNIQUE (nomgr);

ALTER TABLE zakaz ADD CONSTRAINT zakaz_nomgr_fk FOREIGN KEY (nomgr) REFERENCES [group](nomgr);
ALTER TABLE kto ADD CONSTRAINT kto_codstatus_fk FOREIGN KEY (codstatus) REFERENCES [status](codstatus);
ALTER TABLE [group] ADD CONSTRAINT group_codmng_fk FOREIGN KEY (codmng) REFERENCES kto(codmember);
ALTER TABLE groupmember ADD CONSTRAINT groupmember_nomgr_fk FOREIGN KEY (nomgr) REFERENCES [group](nomgr);
ALTER TABLE groupmember ADD CONSTRAINT groupmember_codmember_fk FOREIGN KEY (codmember) REFERENCES kto(codmember);

--30
SELECT
	kto.fio             'Исполнители заказа 1',
	[status].statustext 'Статус исполнителей'
FROM kto
LEFT JOIN [status]
	ON kto.codstatus = [status].codstatus
LEFT JOIN groupmember
	ON kto.codmember = groupmember.codmember
LEFT JOIN [group]
	ON groupmember.nomgr = [group].nomgr
LEFT JOIN zakaz
	ON [group].nomgr = zakaz.nomgr
WHERE zakaz.codz = 1;

--31
SELECT
	zakaz.codz 'Номер заказа',
	kto.fio    'Руководитель группы'
FROM zakaz
LEFT JOIN [group]
	ON zakaz.nomgr = [group].nomgr
LEFT JOIN kto
	ON [group].codmng = kto.codmember;

--32
SELECT
	[group].nomgr    'Номер группы',
	SUM(zakaz.price) 'Сумма по заказам'
FROM zakaz
LEFT JOIN [group]
	ON zakaz.nomgr = [group].nomgr
GROUP BY [group].nomgr;

--33
SELECT
	[group].nomgr       'Номер группы',
	kto.fio             'Исполнитель',
	[status].statustext 'Статус'
FROM [group]
LEFT JOIN groupmember
	ON [group].nomgr = groupmember.nomgr
LEFT JOIN kto
	ON groupmember.codmember = kto.codmember
LEFT JOIN [status]
	ON kto.codstatus = [status].codstatus
ORDER BY [group].nomgr, kto.codstatus;

--34
SELECT
	zakaz.codz  'Номер заказа',
	firma.fname 'Фирма',
	kto.fio     'Исполнитель'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf
LEFT JOIN [group]
	ON zakaz.nomgr = [group].nomgr
LEFT JOIN groupmember
	ON [group].nomgr = groupmember.nomgr
LEFT JOIN kto
	ON groupmember.codmember = kto.codmember;

--35
SELECT
	zakaz.codz  'Код заказа',
	firma.fname 'Фирма',
	COUNT(*)    'Количество исполнителей'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf
LEFT JOIN [group]
	ON zakaz.nomgr = [group].nomgr
LEFT JOIN groupmember
	ON [group].nomgr = groupmember.nomgr
GROUP BY zakaz.codz, firma.fname;

--36
SELECT
	firma.fname      'Название фирмы',
	COUNT(*)         'Количество заказов',
	SUM(zakaz.price) 'Сумма по всем заказам'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf
GROUP BY firma.fname
HAVING SUM(zakaz.price) > 500;

--37
SELECT
	zakaz.nomgr 'Номер заказа',
	firma.fname 'Название фирмы',
	kto.fio     'ФИО специалиста'
FROM zakaz
LEFT JOIN firma
	ON zakaz.codf = firma.codf
LEFT JOIN [group]
	ON [group].nomgr = zakaz.nomgr
LEFT JOIN groupmember
	ON [group].nomgr = groupmember.nomgr
LEFT JOIN kto
	ON groupmember.codmember = kto.codmember
LEFT JOIN [status]
	ON kto.codstatus = [status].codstatus
WHERE [status].statustext = 'специалист';