sql3

/*
1. Вывести имена клиентов, заказы для которых нужно доставить в Лондон
*/
SELECT C.ContactName, (SELECT ShipCity FROM Orders AS O WHERE O.CustomerID = C.CustomerID GROUP BY O.ShipCity)
FROM Customers as C
WHERE C.City = 'London'

/*
2. Вывести имена клиентов, у которых есть заказы между 02.02.1997 и 03.03.1997
*/
SELECT ContactName
FROM Customers AS C
WHERE (SELECT O.CustomerID FROM Orders AS O WHERE C.CustomerID = O.CustomerID AND O.ShippedDate BETWEEN '1997-02-02' AND '1997-03-03' GROUP BY O.CustomerID) IS NOT NULL

/*
3. Найти компании, занимающиеся доставкой, которые обслуживают более 80 клиентов
*/
SELECT S.CompanyName, (SELECT COUNT(*) FROM Orders AS O WHERE S.ShipperID = O.ShipVia)
FROM Shippers AS S
WHERE (SELECT COUNT(*) FROM Orders AS O WHERE S.ShipperID = O.ShipVia) > 80

/*
4. Вывести только те продукты, на которые нет скидки в таблице [Order Details].
(Если скидка равна нулю, подразумевается, что она есть). Использовать EXISTS
*/
-- Так как нет ни одной записи с пустым полем Discount в базе Order Details, вывод будет пуст.
-- Если убрать условие в скобках, то в запросе достаточно заменить IS NULL на = 0 для вывода продуктов с нулевой скидкой.
SELECT *
FROM Products AS P
WHERE EXISTS (SELECT * FROM [Order Details] AS OD WHERE P.ProductID = OD.ProductID AND OD.Discount IS NULL)

/*
5. Вывести контактную информацию о всех клиентах в верхнем регистре
*/
SELECT UPPER(ContactName) AS 'ContactName', UPPER(ContactTitle) AS 'ContactTitle' FROM Customers

/*
6. Вывести имена продавцов, начиная с 4 символа. Результат должен быть отсортирован по убыванию
*/
SELECT SUBSTRING(FirstName,4,11) AS 'part' FROM Employees ORDER BY 'part' DESC -- так как длина задана 10, то выводим до 11 символа
SELECT SUBSTRING(FirstName,4,LEN(FirstName)-4+1) AS 'part' FROM Employees ORDER BY 'part' DESC

/*
7. Вывести названия всех продуктов, рядом в столбце 'Длина названия' вывести длину названий.
Результат должен быть отсортирован по алфавиту
*/
SELECT ProductName, LEN(ProductName) AS 'Длина названия'
FROM Products
ORDER BY ProductName

/*
9. Создать тестовую таблицу Copy_Territories, полностью повторяющую по структуре таблицу Territories.
*/
CREATE TABLE Copy_Territories(
TerritoryID nvarchar(20) NOT NULL,
TerrytoryDescription nchar(50) NOT NULL,
RegionID int NOT NULL,
CONSTRAINT PK_Copy_TerrytoryID PRIMARY KEY (TerritoryID)
)

/*
10. Скопировать все данные из таблицы Territories в таблицу Copy_Territories с помощью многострочного запроса INSERT.
*/
INSERT INTO Copy_Territories SELECT TerritoryID, TerritoryDescription, RegionID FROM Territories
INSERT INTO Copy_Territories (TerritoryID, TerrytoryDescription, RegionID) (SELECT TerritoryID, TerritoryDescription, RegionID FROM Territories)

/*
11. Заменить в таблице Copy_Territories название территории с кодом 11747, на территорию “New York”.
Что бы произошло, если бы территории с кодом 11747 не существовало в таблице?
*/
-- Ответ: Запрос ничего не обновил бы.
UPDATE Copy_Territories SET TerrytoryDescription='New York' WHERE TerritoryID=11747

/*
12. Создать тестовую таблицу Copy_Orders, полностью повторяющую по структуре таблицу Orders. Скопировать все данные из Orders в Copy_orders.
*/
SELECT * INTO Copy_Orders FROM Orders

/*
13. Удалить из таблицы Territories все территории, которым относится New York.
*/
DELETE FROM Territories WHERE TerritoryDescription = 'New York'
-- EXPECTED RESULT: Удалены все террирории относящиеся к New York
-- ACTUAL RESULT: Удалить не получилось, т.к. есть зависимость от ключа TerritoryID в базе EmployeeTerritories

/*
14. Удалить из таблицы EmployeeTerritories все записи с кодом сотрудника 2.
*/
DELETE FROM EmployeeTerritories WHERE EmployeeID = 2

/*
15. Удалить таблицы Copy_Territories и Territories. Объяснить полученный результат.
*/
DROP TABLE Copy_Territories, Territories
-- Таблица Territories не "дропнулась", т.к. есть зависимовти в других таблицах от её полей.

/*
16. Создать резервную копию базы данных Northwind
*/
BACKUP DATABASE Northwind
TO DISK = 'na.bak'

------------------------------------------
/*
Создать хранимую процедуру, выводящую дату заказа (входной параметр – номер заказа) из таблицы Orders.
В случае если у процедуры при запуске нет входного параметра, предусмотреть выход из процедуры
с выводом сообщения «Введите номер заказа»
*/