TSQL

--DDL
--drop database TSQLBook - оформление комментариев "--"

Alter database TSQLBook Collate Cyrillic_General_CI_AS

use TSQLBook  -- используя (в БД) [имя БД]
create table Students -- создать таблицу [Students]
(
	Id int not null IDENTITY, 	-- с автоинкрементом (IDENTITY)
	FName nvarchar(20),			
	LName nvarchar(20),
	Phone char(12),
	Email varchar(20)
);

alter table students			-- изменяем таблицу
alter column lname nvarchar(30) not null	-- изменяем столбец - максимальную длину(30) и запрещаем хранить null в столбце (not null)

alter table students
add MNane nvarchar(30)

alter table students
drop column MNane

--DML
--insert
insert into Students (FName,LName,Phone,Email)
values
(N'пупкин',N'василий','8903111111','q@q.ru'),
(N'иванов',N'иван','+7894561323','w@W')

--команда для включения возможности добавления поля Id вручную
set identity_insert students on

insert Students --into можно не писать в TSQL
(id,FName,LName,Phone,Email)
values
(3, null,N'дмитрий','8903111112','q@2wf.ru');

--команда для отключения возможности добавления поля Id вручную
set identity_insert students off

--select
CREATE TABLE StudentPhones
(
    Id int,
	LastName nvarchar(20),
	PhoneNumber char(12)
);

insert StudentPhones
	select id,lname,phone from Students

update Students set Phone = null where id = 4

update Students
	set Phone = sp.PhoneNumber
	from Students s
	join StudentPhones sp on s.Id = sp.Id

-- вывод результатов операции
delete Students
output deleted.Id, deleted.LName
where id = 3

--отображение данных которые изменились - старые и новыe
update Students
set Phone = '(123)1231456'
output inserted.Id, inserted.LName, inserted.Phone as [новый телефон], 
		deleted.Phone "old phone"
		where id = 5

--ПЕРЕНОС (удаление из одной таблицы и вставка в другую) данных
delete Students
output deleted.Id, deleted.LName, deleted.Phone into StudentPhones

-- переменная типа таблица, хранится в озу
declare @deletedTable table (Id int, lastname nvarchar(20))

delete StudentPhones
output deleted.Id, deleted.LastName into @deletedTable

select * from @deletedTable

Логический порядок обработки инструкции SELECT

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE или WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

SELECT подробно

-- WHERE - условие выборки строк

--1. Нахождение строки с помощью простого равенства
SELECT * FROM Employees
WHERE Salary = 10000

SELECT * FROM Employees
WHERE Department = 'sales'

-- Операторы сравнения (=, не равно: <> или !=, >, <, >=, <=, !<, !>)
--2. Нахождение строк с использованием оператора сравнения
SELECT * FROM Employees
WHERE BirthDate > '19900101'

SELECT * FROM Employees
WHERE BirthDate !> '19900101'

-- Логические операторы (ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, SOME)
--3. Нахождение строк, которые должны удовлетвор¤ть нескольким условиям
SELECT * FROM Employees
WHERE Department = 'sales' AND Salary >= 6000 
--4. Нахождение строк, удовлетворяющих любому из нескольких условий
SELECT * FROM Employees
WHERE Department = 'sales' OR Department = 'supply'

SELECT * FROM Employees
--WHERE Department = 'sales' OR Department = 'supply' AND Salary >= 6000 -- not correct
WHERE Salary >= 6000 AND (Department = 'sales' OR Department = 'supply')

--5. IN - нахождение строк, находящихся в списке значений
SELECT * FROM Employees
WHERE Department IN ('sales', 'supply', 'law', 'logistics')

SELECT * FROM Employees
WHERE Department NOT IN ('sales', 'supply', 'law', 'logistics') -- см. подзапросы
-- ALL, ANY | SOME, EXISTS - см. подзапросы

--6. BETWEEN - нахождение строк, содержащих значение, расположенное между двумя значениями
SELECT * FROM Employees
WHERE BirthDate > '19900101' AND BirthDate < '19930101'

SELECT * FROM Employees
WHERE BirthDate BETWEEN '19900101' AND '19930101'

--7. -- LIKE - нахождение строк, содержащих значение как часть строки
SELECT * FROM Employees
WHERE Department LIKE 'sales'

-- Wildcard Characters - подстановочные символы (%, _, [], [^])
-- % - поиск значений, которые начинаются с символов до символа %
SELECT * FROM Employees
WHERE Phone LIKE '063%'

-- _ - вместо _ может быть любой символ
SELECT * FROM Employees
WHERE Id LIKE '_2'

-- [*,*] - список значений для первого значения, то есть вместо квадратных скобок будет подставляться 1 символ, перечисленный в скобках
SELECT * FROM Employees
WHERE Id LIKE '[2,4]2'

-- [*-*] - диапазон значений для первого значения, то есть вместо квадратных скобок будет подставляться 1 символ, перечисленный в скобках
SELECT * FROM Employees
WHERE Id LIKE '[2-5]2'
-- [^*-*] - диапазон ЗАПРЕШЕННЫХ значений для первого значения, то есть вместо квадратных скобок будет подставляться 1 символ, перечисленный в скобках
SELECT * FROM Employees
WHERE Id LIKE '[^2-5]2'

--ESCAPE - самостоятельно

--8. Сравнение с NULL
SELECT * FROM Employees
WHERE Salary IS NULL

SELECT * FROM Employees
WHERE Salary IS NOT NULL

SELECT * FROM Employees
WHERE Salary IN (4000, 7000) -- NULL не войдет

-- список, включающий в себя null
SELECT * FROM Employees
WHERE Salary IN (4000, 7000) 
OR Salary IS NULL

CASE GROUP BY

-- Выражение CASE 
-- 1) простое выражение CASE

SELECT Id, LName, Salary,

CASE
	WHEN Salary >= 400000 THEN 'chief'
	WHEN Salary >= 200000 THEN 'manager'
	WHEN Salary IS NULL THEN 'unknown'
	ELSE 'worker'
END AS Position,

CASE
	WHEN Salary >= 400000 THEN 'chief'
	WHEN Salary >= 200000 THEN 'manager'
	WHEN Salary IS NULL THEN 'unknown'
END AS Position2

FROM Employees 

-- 2) поисковое выражение CASE 
SELECT Id, LName, Department, Salary,

CASE Department										--CASE 
	WHEN 'Microsoft' THEN '100%'
	WHEN 'Google' THEN '90%'
	WHEN 'Chami' THEN '80%'							--    ...
	WHEN 'Amazon' THEN '0%'
	ELSE '10%'
END AS [Bonus %],

Salary/100 *
CASE Department
	WHEN 'Microsoft' THEN 100
	WHEN 'Google' THEN 90
	WHEN 'Chami' THEN 80
	WHEN 'Amazon' THEN 0
	ELSE 10
END AS Bonus,

(Salary/100 *
CASE Department
	WHEN 'Microsoft' THEN 100
	WHEN 'Google' THEN 90
	WHEN 'Chami' THEN 80
	WHEN 'Amazon' THEN 0
	ELSE 10
END) + Salary AS [Salary & Bonus]

FROM Employees 

-- IIF (начиная с SQL Server 2012)
SELECT Id, LName, Department, Salary,

IIF(Salary >= 200000, 'manager', 'woker') AS Position

-- CASE WHEN Salary >= 6000 THEN 'manager' ELSE 'woker'

FROM Employees 


ALTER TABLE Employees
ADD Gender bit

UPDATE Employees
SET Gender = IIF(Id%2 = 0, 1, 0)

SELECT Id, LName,
IIF (Gender = 0, 'woman', 'man') AS Gender
FROM Employees


-- GROUP BY
SELECT Department, Gender FROM Employees
GROUP BY Department, Gender

SELECT DISTINCT Department, Gender FROM Employees

-- HAVING само по себе не существует, только в паре с GROUP BY
SELECT Department FROM Employees
GROUP BY Department
	HAVING Department LIKE 'L%'