first.sql

--1
IF OBJECT_ID('dbo.t3', 'U') IS NOT NULL 
	DROP TABLE dbo.t3; 
CREATE TABLE t3 (
	id int IDENTITY(1,1) PRIMARY KEY,
	f1 varchar(12),
	f2 int
);

--2
IF OBJECT_ID('dbo.t4', 'U') IS NOT NULL 
	DROP TABLE dbo.t4; 
CREATE TABLE t4 (
	id0 int IDENTITY(1,1) PRIMARY KEY,
	ff3 int,
	ff4 varchar(25)
);

--3
INSERT INTO t3(f1, f2) VALUES
	('das', 1),
	('das', 1),
	('das', 1),
	('das', 1),
	('das', 1),
	('das', 1),
	('das', 1),
	('das', 1),
	('das', 1),
	('das', 1);

--4
SELECT * FROM t3;

--5
SELECT TOP 6 * FROM t3;

--6
INSERT INTO t3(f1, f2) VALUES
	('aaa', 100),
	('aaa', 200);

--7
SELECT DISTINCT * FROM t3;

--8
SELECT DISTINCT t3.f1 FROM t3;

--9
--Нетю такого задания

--10
SELECT t3.id 'Номер', t3.f1 'Поле 1', t3.f2 'Поле 2' FROM t3;

--11
SELECT TOP (
	SELECT COUNT(*) / 2 FROM t3
) * FROM t3;

--12
INSERT INTO t4(ff4) VALUES
	('aaa'),
	('abbc'),
	('cbb'),
	('acccb'),
	('ammn'),
	('aee'),
	('beed'),
	('bsd');

--13
SELECT t4.ff4 FROM t4 WHERE t4.ff4 LIKE '%c%';

--14
SELECT t4.ff4 FROM t4 WHERE t4.ff4 LIKE 'b%';

--15
SELECT t4.ff4 FROM t4 WHERE t4.ff4 LIKE '_[a-d]%';

--16
SELECT t4.ff4 FROM t4 WHERE t4.ff4 LIKE '%[ad]';

--17
BEGIN
	DECLARE @list TABLE (
		id int IDENTITY(1, 1) PRIMARY KEY,
		ff3 int
	);
	INSERT INTO @list(ff3) VALUES
		(1),
		(3),
		(5),
		(7),
		(10),
		(15),
		(20),
		(25),
		(30);

	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 t4);
	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 t4 SET t4.ff3 = (
			SELECT ff3 FROM @list WHERE id = @i
		) WHERE t4.id0 = @i;
		SET @i = @i + 1;
	END

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

--18
SELECT * FROM t4 WHERE ff3 >= 5;

--19
SELECT * FROM t4 WHERE ff3 BETWEEN 2 AND 20;

--20
IF OBJECT_ID('dbo.t5', 'U') IS NOT NULL 
	DROP TABLE dbo.t5; 
CREATE TABLE t5 (
	id int IDENTITY(1,1) PRIMARY KEY,
	fi int,
	fv varchar(12),
	fc char(12),
	fnv nvarchar(12),
	fnc nchar(12),
	fd datetime
);

--21
IF OBJECT_ID('dbo.t6', 'U') IS NOT NULL 
	DROP TABLE dbo.t6; 
CREATE TABLE t6 (
	id int IDENTITY(1,1) PRIMARY KEY,
	fi int,
	f2v varchar(12)
);

--22, 23, 24
INSERT INTO t5(fi, fv, fc, fnv, fnc, fd) VALUES
	(10, '  Иванов', '  Смирнов', '  Потапов', '  Максимов', '20011210'),
	(20, '  Смирнов', '  Потапов', '  Максимов', '  Петров', '20101005'),
	(30, '  Потапов', '  Максимов', '  Петров', '  Иванов', '20011201'),
	(40, '  Максимов', '  Петров', '  Иванов', '  Смирнов', '20101005'),
	(50, '  Петров', '  Иванов', '  Смирнов', '  Потапов', '20011210');

--22, 25
INSERT INTO t6(fi, f2v) VALUES
	(10, 'Москва'),
	(20, 'Москва'),
	(30, 'Псков'),
	(40, 'Псков'),
	(50, 'Могилёв');

--26
SELECT * FROM t5 WHERE fv LIKE '% Смирнов';
SELECT * FROM t5 WHERE RTRIM(fv) LIKE '% Смирнов';

SELECT * FROM t5 WHERE fc LIKE '% Смирнов';
SELECT * FROM t5 WHERE RTRIM(fc) LIKE '% Смирнов';

SELECT * FROM t5 WHERE fnv LIKE '% Смирнов';
SELECT * FROM t5 WHERE RTRIM(fnv) LIKE '% Смирнов';

SELECT * FROM t5 WHERE fnc LIKE '% Смирнов';
SELECT * FROM t5 WHERE RTRIM(fnc) LIKE '% Смирнов';

--27
SELECT * FROM t5, t6 WHERE t5.fi = t6.fi;

--28
SELECT * FROM t5, t6 WHERE t5.fi = t6.fi ORDER BY t5.fv;

--29
SELECT * FROM t5, t6 WHERE t5.fi = t6.fi ORDER BY t6.f2v;

--30
SELECT * FROM t5 WHERE t5.fi BETWEEN 20 AND 40;

--31
SELECT * FROM t5 WHERE t5.fv LIKE '__т%';

--32
SELECT * FROM t5, t6 WHERE t5.fi = t6.fi AND t6.f2v LIKE 'М%';

--33
SELECT DATALENGTH(t5.fv), DATALENGTH(t5.fc), DATALENGTH(t5.fnv), DATALENGTH(t5.fnc) FROM t5 WHERE fi = 10;

--34
ALTER TABLE t5 ADD fg int;
ALTER TABLE t5 ADD fp float;

--35
BEGIN
	DECLARE @list TABLE (
		id int IDENTITY(1, 1) PRIMARY KEY,
		fg int
	);
	INSERT INTO @list(fg) VALUES
		(100),
		(100),
		(100),
		(101),
		(101),
		(102),
		(102),
		(102);

	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 t5);
	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 t5 SET t5.fg = (
			SELECT fg FROM @list WHERE id = @i
		) WHERE t5.id = @i;
		SET @i = @i + 1;
	END

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

--36
BEGIN
	DECLARE @list TABLE (
		id int IDENTITY(1, 1) PRIMARY KEY,
		fp int
	);
	INSERT INTO @list(fp) VALUES
		(10),
		(10),
		(20),
		(50),
		(5),
		(10),
		(70),
		(20);

	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 t5);
	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 t5 SET t5.fp = (
			SELECT fp FROM @list WHERE id = @i
		) WHERE t5.id = @i;
		SET @i = @i + 1;
	END

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

--37
SELECT * FROM t5 WHERE t5.fc IS NOT NULL;
SELECT * FROM t5 WHERE t5.fv IS NOT NULL;
SELECT * FROM t5 WHERE t5.fnc IS NOT NULL;
SELECT * FROM t5 WHERE t5.fnv IS NOT NULL;

--38
SELECT MAX(t5.fp), MIN(t5.fp), AVG(t5.fp) FROM t5;

--39
SELECT t5.fg, MAX(t5.fp), MIN(t5.fp), AVG(t5.fp) FROM t5 GROUP BY t5.fg;

--40
SELECT t5.fg, MAX(t5.fp), MIN(t5.fp), AVG(t5.fp) FROM t5 WHERE t5.fp > 10 GROUP BY t5.fg;

--41
SELECT t5.fg, MAX(t5.fp), MIN(t5.fp), AVG(t5.fp) FROM t5 WHERE t5.fp > (
	SELECT AVG(t5.fp) FROM t5
) GROUP BY t5.fg;

--42
SELECT t.fg, MAX(t.fp), MIN(t.fp), AVG(t.fp)
	FROM t5 t
	WHERE EXISTS (
		SELECT AVG(tt.fp) FROM t5 tt 
			GROUP BY tt.fg
			HAVING t.fp > AVG(tt.fp)
	)
	GROUP BY t.fg;

--43
SELECT fg, max_fp, min_fp, avg_fp FROM (
	SELECT t5.fg fg, MAX(t5.fp) max_fp, MIN(t5.fp) min_fp, AVG(t5.fp) avg_fp, SUM(t5.fp) sum_fp FROM t5 
		GROUP BY fg
		HAVING SUM(t5.fp) > 55
) t;

--44
IF OBJECT_ID('dbo.t7', 'U') IS NOT NULL 
	DROP TABLE dbo.t7;	
CREATE TABLE t7 (
	id int IDENTITY(1,1) PRIMARY KEY,
	fi int,
	fv varchar(12),
	fd datetime,
	fg int,
	fs int
);

--45
IF OBJECT_ID('dbo.t8', 'U') IS NOT NULL 
	DROP TABLE dbo.t8;
CREATE TABLE t8 (
	id int IDENTITY(1,1) PRIMARY KEY,
	fi int,
	f2v varchar(12),
	ff float,
	fs varchar(4)
);

--46
INSERT INTO t7(fi, fv, fd, fg, fs) VALUES
	(10, 'aa', '20101010', 100, 60),
	(20, 'bb', '20101012', 100, 50),
	(30, 'aa', '20101205', 100, 10),
	(40, 'cc', '20101206', 101, 10),
	(50, 'mm', '20101207', 101, 1 );

--47
INSERT INTO t8(fi, f2v, ff, fs) VALUES
	(10, 'a10', 100, 50),
	(10, 'a11', 200, 50),
	(10, 'a12', 10,  50),
	(20, 'b20', 5,   40),
	(20, 'b21', 1,   60),
	(40, 'd40', 6,   2 ),
	(40, 'd41', 2,   2 );


--48, 49 - не вижу разницы
SELECT * FROM t7 WHERE EXISTS (
	SELECT * FROM t8 WHERE t7.fi = t8.fi
);

--50
SELECT * FROM t7 WHERE t7.fs < ALL(SELECT t8.fs FROM t8);

--51
SELECT * FROM t7 WHERE t7.fs < ANY(SELECT t8.fs FROM t8 GROUP BY fs);

--52
SELECT * FROM t7 WHERE t7.fs = ANY(SELECT t8.fs FROM t8 GROUP BY fs);

--53
SELECT SUM(t8.ff) FROM t8
	INNER JOIN t7
		ON t7.fi = t8.fi AND t7.fg = 100
 GROUP BY t8.fi;
 --Неверное поле, так как fs - строка

--54
SELECT DISTINCT t7.fv, t7.fs FROM t7
	INNER JOIN t8
		ON t7.fi = t8.fi AND
		CHARINDEX(LEFT(t8.f2v, 1), t7.fv) > 0;

--55
SELECT DISTINCT t8.fi, t8.f2v, t8.fs FROM t8
	INNER JOIN t7
		ON t7.fi = t8.fi AND
		t7.fg = 100 AND
		CHARINDEX(LEFT(t8.f2v, 1), t7.fv) > 0;

--56
--Какой таблицы???

--57
IF OBJECT_ID('dbo.t7_dub', 'U') IS NOT NULL 
	DROP TABLE dbo.t7_dub; 
SELECT * INTO t7_dub FROM t7;

--58
IF OBJECT_ID('dbo.t7_dub2', 'U') IS NOT NULL 
	DROP TABLE dbo.t7_dub2; 
SELECT * INTO t7_dub2 FROM t7 WHERE t7.fg = 100;

--59
IF OBJECT_ID('dbo.t7_dub3', 'U') IS NOT NULL 
	DROP TABLE dbo.t7_dub3; 
SELECT * INTO t7_dub3 FROM t7 WHERE 1 = 2;

--60
INSERT INTO t7(fi, fv, fd, fg, fs) VALUES
(60, 'am', '20101010', 102, 70),
(70, 'mb', '20101012', 102, 90),
(80, 'tb', '20101012', 102, 110);

--61
UPDATE t7 SET t7.fs *= 1.3 WHERE t7.fs > 70;

--62
UPDATE t7 SET t7.fg *= 5, t7.fd = DATEADD(MONTH, 3, t7.fd) WHERE t7.fs = 1;

--63
UPDATE t7 SET t7.fg *= 1.5, t7.fd = DATEADD(YEAR, 2, t7.fd) WHERE t7.fs = 50;

--64
DELETE FROM t7 WHERE fv LIKE '%t%';

--65
SELECT 
	t7.fi 'Поле fi',
	CONCAT (
		CAST (DATEPART(WEEKDAY, t7.fd) AS varchar(1)), '-',
		CAST (DATEPART(DAY, t7.fd) AS varchar(2)), '-',
		CAST (DATEPART(MONTH, t7.fd) AS varchar(2)), '-',
		CAST (DATEPART(YEAR, t7.fd) AS varchar(4)) 
	) 'Поле fd(день недели-день-месяц-год)',
	t7.fg 'Поле fg'
	FROM t7;

--66
SELECT 
	t7.fi 'Поле fi',
	CONCAT (
		CAST (DATENAME(WEEKDAY, t7.fd) AS varchar(20)), '-',
		CAST (DATENAME(DAY, t7.fd) AS varchar(20)), '-',
		CAST (DATENAME(MONTH, t7.fd) AS varchar(20)), '-',
		CAST (DATENAME(YEAR, t7.fd) AS varchar(1000)) 
	) 'Поле fd(день недели-день-месяц-год)',
	t7.fg 'Поле fg'
	FROM t7;

--67
SELECT MAX(t.value) FROM (
	SELECT CAST ('20100111' as date) value UNION ALL
	SELECT CAST ('20100207' as date)
) t;

--68
SELECT 'abcampdfab', REPLACE('abcampdfab', 'ab', '22');

--69
SELECT DATEDIFF(DAY, '20090110', '20100310');

--70
SELECT DATEDIFF(MONTH, '20090110', '20100310');

--71
SELECT DATEDIFF(YEAR, '20090110', '20100310');

--72
SELECT CONVERT(varchar(25), GETDATE(), 0), CONVERT(varchar(25), GETDATE(), 105), CONVERT(varchar(25), GETDATE(), 106);

--73
SELECT UPPER('qqQQrr55%%'), LOWER('qqQQrr55%%');

--74
SELECT LEFT('Строка 1111', 5), RIGHT('Строка 12345', 3), LEN('12345678');

--75
SELECT * FROM t7
	WHERE t7.fd IN (
		'20101010',
		'20101012',
		'20101206'
	);

--76
DELETE FROM t7 WHERE NOT EXISTS (
	SELECT * FROM t8 WHERE t8.fi = t7.fi
);

--77
--Не понятно

--78
INSERT INTO t7(fi, fg) VALUES
	(80, 103),
	(90, 103);

--79
SELECT 
	ISNULL(t7.fd, 0),
	ISNULL(t7.fg, 0),
	ISNULL(t7.fi, 0),
	ISNULL(t7.fs, 0),
	ISNULL(t7.fv, 0),
	ISNULL(t7.id, 0)
FROM t7;

--80
SELECT SUM(t.nfs) FROM (
	SELECT ISNULL(fs, 10000) nfs, fg FROM t7
) t
GROUP BY t.fg;

--81
--Опять строки вместо чисел

--82
--Дальше лафа