create

create database departaments;

use departaments;

create table Departament (
departamentId int IDENTITY PRIMARY KEY,
departamentName varchar(50) not null,
floorNumber int not null,
phone varchar(50) not null,
headOfDepartament varchar(150) not null
);

create table Employee (
employeeId int IDENTITY PRIMARY KEY,
name varchar(150) not null,
position varchar(30) not null check (position in ('Конструктор', 'Инженер', 'Техник',
'Лаборант', 'Прочий обслуживающий персонал')),
isMale bit not null,
address varchar(100) not null,
birthday date not null,
departamentId int foreign key references Departament(departamentId) null
);

create table Organization (
organizationId int IDENTITY PRIMARY KEY,
organizationName varchar(50) not null,
typeOfActivity varchar(50) not null,
city varchar(50) not null,
country varchar(50) not null,
address varchar(100) not null,
directorName varchar(150) not null
);

create table Agreement(
agreementId int IDENTITY PRIMARY KEY,
agreementNumber varchar(40) not null,
date Date not null check(date <= getdate()),
price money not null check(price > 0),
organizationId int foreign key references Organization(organizationId) null
);

create table DesignWork(
designWorkId int IDENTITY PRIMARY KEY,
startDate Date not null,
endDate Date not null,
agreementId int foreign key references Agreement(agreementId),
departamentId int foreign key references Departament(departamentId) null
);


-- Добавление записей в таблицы
use departaments

-- Departament

INSERT INTO Departament
VALUES('Отдел финансов', 420, '+375257691966', 'Рушева М М')

INSERT INTO Departament
VALUES('Отдел маркетинга', 1, '+375257391966', 'Пырко Вадим В')

INSERT INTO Departament
VALUES('Отдел разработки', 3, '+375212691966', 'Никто Н Н')

INSERT INTO Departament
VALUES('Отдел логистики', 2, '+37523331966', 'Вика М В')

INSERT INTO Departament
VALUES('Отдел технического контроля', 4, '+375257691966', 'Опять Никто Мн')

-- Employee

INSERT INTO Employee
VALUES('Рушева М М', 'Прочий обслуживающий персонал', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 2)

INSERT INTO Employee
VALUES('Г Г Г', 'Лаборант', 1, 'Jakuba Kolasa 23/3/83 Minsk', '2002-07-06', 1)

INSERT INTO Employee
VALUES('Никто Н Н', 'Конструктор', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 3)

INSERT INTO Employee(name, position, isMale, address, birthday)
VALUES('С У К', 'Конструктор', 1, 'Paris, Lenin Square', '2002-07-06')

INSERT INTO Employee
VALUES('А А А', 'Техник', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 5)

-- Organization

INSERT INTO Organization VALUES
('БелГазПром Банк', 'Банк', 'Минск', 'Беларусь', 'Сурганова 32', 'Вадим Викторович Ведрищев')

INSERT INTO Organization VALUES
('Шаурма Мираж', 'Ресторан', 'Минск', 'Беларусь', 'где то около комаровки', 'Я Не Могуэто')

INSERT INTO Organization(organizationName, typeOfActivity, city, country, address, directorName)
VALUES ('Трамп', 'Застройщик', 'Минск', 'Беларусь', 'Незалежнасьці 12', 'Господи Я Зае')

INSERT INTO Organization VALUES
('ОАО Украина', 'Церковь', 'Киев', 'Украина', 'Майдан', 'Никто Но Но')

INSERT INTO Organization VALUES
('ОАО Нильфгаард', 'Империя', 'Минск', 'Беларусь', 'Сурганова 32', 'Я Устала Капец')

-- Agreement

INSERT INTO Agreement VALUES
('BGP12221', '2022-02-24', 4422.23, 1)

INSERT INTO Agreement(agreementNumber, date, price)
VALUES('NFG-12', '1227-02-24', 800000)

INSERT INTO Agreement VALUES
('UK12', '2022-02-24', 3131212.23, 5)

INSERT INTO Agreement VALUES
('123321', '2022-02-24', 4422.23, 4)

INSERT INTO Agreement VALUES
('GG11112', '2022-02-24', 4422.23, 2)

-- DesignWork

INSERT INTO DesignWork VALUES
('2014-02-12', '2022-02-24', 1, 2)

INSERT INTO DesignWork VALUES
('2012-02-12', '2022-02-24', 2, 3)

INSERT INTO DesignWork VALUES
('2014-02-01', '2022-02-24', 4, 1)

INSERT INTO DesignWork VALUES
('2014-02-12', '2022-02-24', 1, 5)

INSERT INTO DesignWork VALUES
('2014-02-12', '2022-02-24', 2, 5)


select * from Departament
select * from Employee
select * from Organization
select * from Agreement










-- Запросы с отбором строк по условию
SELECT * FROM Employee
WHERE birthday < GETDATE()

SELECT * FROM Departament
WHERE floorNumber <= 4

SELECT * FROM DesignWork
WHERE startDate >= '2013-01-01'

-- AND, OR, NOT
SELECT * FROM Organization
WHERE city = 'Минск' OR city = 'Киев'

SELECT * FROM Agreement
WHERE price > 5000 AND date < '2022-01-01'

SELECT * FROM Employee
WHERE NOT position = 'Техник' AND NOT position = 'Конструктор'

-- compining logical operators
SELECT * FROM Organization
WHERE address LIKE '%32%' AND NOT typeOfActivity = 'Банк'

SELECT * FROM Employee
WHERE birthday BETWEEN '2001-01-01' AND '2004-01-01' AND NOT position IN('Конструктор')

-- Columns expressions
SELECT * FROM Agreement
WHERE price + agreementId < 800000

SELECT * FROM Agreement
WHERE price/100000 < agreementId

-- Sets
SELECT * FROM Agreement
WHERE price/100000 IN (2,4,8)

SELECT * FROM Employee
WHERE position IN ('Конструктор', 'Техник')

-- Ranges
SELECT * FROM Agreement
WHERE price/100000 BETWEEN 2 AND 8

SELECT * FROM Employee
WHERE position BETWEEN 'Лаборант' AND 'Техник'

-- Pattern
SELECT * FROM Employee
WHERE address LIKE '%53%'

SELECT * FROM Employee
WHERE name LIKE 'С%'

-- not null
SELECT * FROM Employee
WHERE departamentId IS NULL

SELECT * FROM Employee
WHERE departamentId IS NOT NULL








--Многотабличные запросы

SELECT * FROM Employee, Departament

SELECT * FROM Organization CROSS JOIN Employee

------

SELECT * FROM Employee, Departament
WHERE Employee.departamentId = Departament.departamentId

SELECT * FROM Agreement
INNER JOIN Organization ON Organization.organizationId = Agreement.organizationId

-------

SELECT * FROM Agreement
INNER JOIN Organization ON Organization.organizationId = Agreement.organizationId
WHERE typeOfActivity = 'Банк'

SELECT * FROM DesignWork
INNER JOIN Departament ON Departament.departamentId = DesignWork.departamentId
WHERE departamentName LIKE '% % %'



SELECT * FROM DesignWork
INNER JOIN Departament ON Departament.departamentId = DesignWork.departamentId
INNER JOIN Agreement ON Agreement.agreementId = DesignWork.agreementId





SELECT * FROM Employee
FULL JOIN Departament ON Departament.departamentId = Employee.departamentId

SELECT * FROM Agreement
FULL JOIN Organization ON Organization.organizationId = Agreement.organizationId



SELECT * FROM Employee
LEFT JOIN Departament ON Departament.departamentId = Employee.departamentId

SELECT * FROM Agreement
LEFT JOIN Organization ON Organization.organizationId = Agreement.organizationId



SELECT * FROM Departament
RIGHT JOIN Employee ON Departament.departamentId = Employee.departamentId

SELECT * FROM Agreement
RIGHT JOIN Organization ON Organization.organizationId = Agreement.organizationId



SELECT em1.*
FROM Employee em1, Employee em2
WHERE em2.name LIKE '%Рушева%' AND em2.address = em1.address AND NOT em1.name LIKE '%Рушева%'

SELECT agr1.*
FROM Agreement agr1, Agreement agr2
WHERE agr2.agreementNumber = 'BGP12221' AND agr2.date = agr1.date AND NOT agr1.agreementNumber = 'BGP12221'






--Запросы на группировку и сортировку, изменение, удаление и использование внутренних функций


-- COUNT
SELECT COUNT(*) as total_count_of_agreements FROM Agreement

SELECT COUNT(departamentId) as total_count_of_departaments FROM Employee

-- SUM
select sum(price) as total_price from Agreement

select sum(employeeId) as sum_of_id from Employee

-- UPPER/LOWER
select upper(name) as upper_name from Employee

select lower(name) as lower_name from Employee

-- DateTime Functions
select datediff(day, startDate, endDate) as count_of_days from DesignWork

select day(birthday) as day, month(birthday) as month, year(birthday) as year from Employee

-- Grouping by one column
select address, Count(*) from Employee
group by address

select price, sum(price) from Agreement
group by price

-- Grouping by several columns

select date, price, count(*) from Agreement
group by date, price


select startDate, endDate, count(*) from DesignWork
group by startDate, endDate

-- GroupBy + Having
select date, price, count(*) from Agreement
group by date, price
having count(*) > 1

select price, sum(price) from Agreement
group by price
having sum(price) <> 800000

-- Having without GroupBy
select sum(price) from Agreement
having sum(price) <> 800000

select count(*) from Agreement
having count(*) > 4

-- OrderBy
select * from Employee
order by name

select * from Employee
order by name desc

-- insert date

select * from Employee
select * from Departament
INSERT INTO Employee
VALUES('Рушева М М', 'Прочий обслуживающий персонал', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 2)

INSERT INTO Departament
VALUES('Отдел разработки', 3, '+37512345678', 'Дольский Евгений')
select * from Employee
select * from Departament

-- update data
select * from DesignWork
update DesignWork
set startDate = DATEADD(DAY, 1, startDate)
select * from DesignWork

select * from Employee
update Employee
set name = lower(name)
select * from Employee

-- update data with where
select * from Agreement
update Agreement
set price = price + 1
where price <> 800000
select * from Agreement

select * from Employee
update Employee
set name = 'Рушева М М'
where name LIKE '%ушева%'
select * from Employee

-- delete data
select * from Employee
delete Employee
where name like '%орович%'
select * from Employee

select * from Departament
delete Departament
where phone = '+37512345678'
select * from Departament


















------------------------------------------------
use departaments

exec sp_addrole 'manager'
exec sp_addrole 'director'

grant delete, insert, update, select on Organization to director
grant select on Agreement to manager

exec sp_addlogin 'sialiaukaivan', 'selll2518', 'departaments'
exec sp_adduser 'sialiaukaivan', 'sialiaukaivan'
exec sp_addrolemember 'director', 'sialiaukaivan'

exec sp_addlogin 'MBN_Joker', 'selll2518', 'departaments'
exec sp_adduser 'MBN_Joker', 'MBN_Joker'
exec sp_addrolemember 'manager', 'MBN_Joker'









-----------------------------------------------------------------------------------------------
--1 запрос для создания временной таблицы через переменную типа TABLE
use departaments

-- timed table
select agreementId, sum(price) as total_price
into #AgreementSummary
from Agreement
group by agreementId

select * from Agreement
join #AgreementSummary on Agreement.agreementId= #AgreementSummary.agreementId

drop table #AgreementSummary
--2 запроса с использованием условной конструкции IF
-- if
declare @youngest Date

select @youngest = max(birthday) from Employee

if datediff(year, @youngest, getdate()) >= 18
print 'Все сотрудники совершеннолетние'
else
print 'НЕ ВСЕ совершеннолетние'

----------------
declare @sum money, @departamentName varchar(100), @countOfWork int, @amountOfGroup int

SELECT @sum = sum(price),
@departamentName = departamentName,
@countOfWork = count(*)
from DesignWork
inner join Departament on Departament.departamentId = DesignWork.departamentId
inner join Agreement on Agreement.agreementId = DesignWork.agreementId
group by Departament.departamentName
having count(*) >= 2

IF @sum > 4000
BEGIN
PRINT 'Название отдела: ' + @departamentName
PRINT 'Количество работ ' + CONVERT(varchar(50), @countOfWork)
PRINT 'Сумма ' + CONVERT(varchar(50), @sum)
END;
ELSE
PRINT 'Слишком дешево'


-- 2 запроса с использованием цикла WHILE
DECLARE @number INT, @factorial INT
SET @factorial = 1;
SET @number = 10;

WHILE @number > 0
BEGIN
SET @factorial = @factorial * @number
SET @number = @number - 1
END;

PRINT @factorial

---------

CREATE TABLE #Accounts ( CreatedAt DATE, Balance MONEY)

DECLARE @rate FLOAT, @period INT, @summa MONEY, @date DATE
SET @date = GETDATE()
SET @rate = 0.065;
SET @period = 5;
SET @summa = 10000;

WHILE @period > 0
BEGIN
INSERT INTO #Accounts VALUES(@date, @summa)
SET @period = @period - 1
SET @date = DATEADD(year, 1, @date)
SET @summa = @summa + @summa * @rate
END;

SELECT * FROM #Accounts

-- sklyar function

GO

--1 запрос для создания скалярной функции
CREATE FUNCTION dbo.calculate_work_days(@startDate date, @endDate date)
RETURNS int AS
BEGIN
RETURN datediff(DAY, @startDate, @endDate)
END

GO
drop function dbo.calculate_work_days

GO
SELECT dbo.calculate_work_days(startDate, endDate) as 'number of work days'
FROM DesignWork

-- Table value from function


---1 запрос для создания функции, которая возвращает табличное значение
GO
CREATE FUNCTION dbo.getMonthName(@DATE date) RETURNS CHAR(15)
WITH EXECUTE AS CALLER AS
BEGIN
DECLARE @month int;
DECLARE @monthName char(15);
SET @month= MONTH(@DATE)

IF (@month=1) SET @monthName='Январь';
IF (@month=2) SET @monthName='Февраль';
IF (@month=3) SET @monthName='Март';
IF (@month=4) SET @monthName='Апрель';
IF (@month=5) SET @monthName='Май';
IF (@month=6) SET @monthName='Июнь';
IF (@month=7) SET @monthName='Июль';
IF (@month=8) SET @monthName='Август';
IF (@month=9) SET @monthName='Сентябрь';
IF (@month=10) SET @monthName='Октябрь';
IF (@month=11) SET @monthName='Ноябрь';
IF (@month=12) SET @monthName='Декабрь';

RETURN(@monthName);
END;
GO
SELECT *, dbo.getMonthName(birthday) AS 'Месяц'
FROM Employee;

-- Procedure to select data
GO


-- 2 запроса для создания процедуры извлечения данных из таблицы базы данных SELECT
create procedure Employees as
begin
select * from Employee
end
GO

exec Employees
GO

create procedure EmployeesWithMonthName as
begin
SELECT *, dbo.monthName(birthday) AS 'Месяц'
FROM Employee;
end
GO
exec EmployeesWithMonthName
GO
-- Procedure without params
GO


--2 запроса для создания процедуры без параметров

create procedure CurrentMonthName as
begin
print dbo.monthName(getdate())
end
GO

exec CurrentMonthName
GO

create procedure multiplication as
begin
declare @a int;
set @a = 2;
declare @b int;
set @b = 10;

print convert(varchar(5), @a * @b)
end
GO
exec multiplication

--2 запроса для создания процедуры с входными параметрами

CREATE PROCEDURE AddEmployee
@name VARCHAR(50),
@position VARCHAR(100),
@isMale bit,
@address VARCHAR(100),
@birthday date,
@departamentId int
AS
IF(not @birthday >= getdate())
INSERT INTO Employee
VALUES(@name, @position , @isMale, @address, @birthday, @departamentId)
ELSE
PRINT 'Неверная дата рождения'
RETURN
GO

EXEC AddEmployee 'Боровой Владислав', 'Техник', 1, 'Jakuba Kolasa 53/3/83', '2031-08-17', null
GO


--2 запроса для создания процедуры обновления данных в таблице базы данных UPDATE
CREATE PROCEDURE UpdateEmployee
@id int,
@birthday date
AS
update Employee
set birthday= @birthday
where @id = employeeId
GO

exec UpdateEmployee 4, '2021-08-17'
GO




select * from Organization
GO

--запрос для создания процедуры с входными параметрами и RETURN


CREATE PROCEDURE UpdateOrganizationCity
@id int,
@city nvarchar(50)
AS
IF(@city = 'Гродна')
RETURN

update Organization
set city= @city
where @id = organizationId
GO

exec UpdateOrganizationCity 4, 'Гродна'
GO

exec UpdateOrganizationCity 1, 'Киев'
GO










--Создать функцию для выполнения четырех арифметических ‘+’,
--‘-’, ‘*’ и ‘/’ над целыми операндами типа bigint,
--выполнив кодирование и проверку

CREATE FUNCTION dbo.Calculator
(
@opd1 bigint,
@opd2 bigint,
@oprt char(1) = '*'
)
RETURNS bigint
AS
BEGIN
declare @result bigint;
set @result =
case @Oprt
when '+' then @opd1 + @opd2
when '-' then @opd1 - @opd2
when '*' then @opd1 * @opd2
when '/' then @opd1 / @opd2
else 0
end

return @result
END
GO

SELECT dbo.Calculator(4,465, '*')
GO

-- ###################################################

CREATE FUNCTION DYNTAB (@birthday date)
RETURNS Table AS
RETURN SELECT * FROM Employee WHERE birthday = @birthday
GO

SELECT * FROM DYNTAB('2002-07-06')
GO

-- ###################################################
DROP FUNCTION Parse
GO

CREATE FUNCTION Parse
(
@string varchar(500)
)
RETURNS @table TABLE (number int identity not null, substr varchar(30))
AS
BEGIN
DECLARE @Str1 nvarchar(500), @Pos int
SET @Str1 = @String

WHILE 1 > 0
BEGIN
SET @Pos = CHARINDEX(' ', @Str1)
IF @POS > 0
BEGIN
INSERT INTO @table
VALUES (SUBSTRING (@Str1, 1, @Pos))
SET @Str1 = REPLACE(@Str1, SUBSTRING (@Str1, 1, @Pos), '')

END
ELSE
BEGIN
INSERT INTO @table VALUES (@Str1)
BREAK
END
END

RETURN
END
GO

DECLARE @TestString nvarchar(500)
Set @TestString = 'SQL Server 2019'
SELECT * FROM Parse(@TestString)
GO





--Создать функцию, возвращающую таблицу с динамическим
--набором столбцов, выполнив кодирование и
--тестирование

CREATE FUNCTION DYNTAB (@birthday date)
RETURNS Table AS
RETURN SELECT * FROM Employee WHERE birthday = @birthday
GO

SELECT * FROM DYNTAB('2002-07-06')
GO


--Создать функцию, разбивающую строку на подстроки,
--использую я в качестве разделителя пробелы,
--выполнив кодирование и тестирование

CREATE FUNCTION Parse
(
@string varchar(500)
)
RETURNS @table TABLE (number int identity not null, substr varchar(30))
AS
BEGIN
DECLARE @Str1 nvarchar(500), @Pos int
SET @Str1 = @String

WHILE 1 > 0
BEGIN
SET @Pos = CHARINDEX(' ', @Str1)
IF @POS > 0
BEGIN
INSERT INTO @table
VALUES (SUBSTRING (@Str1, 1, @Pos))
SET @Str1 = REPLACE(@Str1, SUBSTRING (@Str1, 1, @Pos), '')

END
ELSE
BEGIN
INSERT INTO @table VALUES (@Str1)
BREAK
END
END

RETURN
END
GO

DECLARE @TestString nvarchar(500)
Set @TestString = 'SQL Server 2019'
SELECT * FROM Parse(@TestString)
GO


--С помощью Transact-SQL создать три хранимые
--процедуры, семантика которых аналогична
--рассмотренным функциям, введя при этом
--дополнительный параметр для получения результата
--работы соответствующей процедуры.

CREATE PROCEDURE dbo.ProcCalculator
(
@opd1 bigint,
@opd2 bigint,
@oprt char(1) = '*',
@result bigint OUTPUT
)
AS
BEGIN
set @result =
case @Oprt
when '+' then @opd1 + @opd2
when '-' then @opd1 - @opd2
when '*' then @opd1 * @opd2
when '/' then @opd1 / @opd2
else 0
end
END
GO

declare @result bigint
EXEC ProcCalculator 2, 5, '*', @result OUTPUT
PRINT @result