third.sql

--http://vitu.oit.cmc.msu.ru/mod/assignment/view.php?id=1848

--1
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL 
	DROP TABLE dbo.t1;
CREATE TABLE t1 (
	f1 int,
	f2 int,
	f3 varchar(5)
);

IF OBJECT_ID('dbo.t2', 'U') IS NOT NULL 
	DROP TABLE dbo.t2;
CREATE TABLE t2 (
	f1 int,
	f4 varchar(5)
);

INSERT INTO t1(f1, f2, f3) VALUES
	(1, 10, 'aa'),
	(2, 40, 'bb'),
	(3, 50, 'cc');

INSERT INTO t2(f1, f4) VALUES
	(4, 'dd'),
	(5, 'ee');

--2
CREATE FUNCTION dbo.count_t1_t2()
RETURN (SELECT COUNT(*) FROM t1) + (SELECT COUNT(*) FROM t2);
GO

SELECT dbo.count_t1_t2();

--3
CREATE FUNCTION dbo.return_table()
RETURNS table
AS RETURN (SELECT * FROM t1);
GO

SELECT * FROM dbo.return_table();

--4
CREATE FUNCTION dbo.return_union_table()
RETURNS @T table(f1 int, f2 varchar(5))
AS
BEGIN
	INSERT INTO @T(f1, f2)
		SELECT f1, f3 FROM t1;
	INSERT INTO @T(f1, f2)
		SELECT f1, f4 FROM t2;
	RETURN
END