func

-- 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