Week 13 Homework in IT STEP Academy (Data Manipulation Language)

Week 13 Homework.sql

USE [Цінники]

-- 1. Порахувати можливий дохід за автоматичні вимикачі та рубильники (з урахуванням знижок на ці товари).
SELECT ROUND(SUM(price_EUR * (1 - discount / 100) * quantity), 2) 
			AS 'Виручка за автоматичні вимикачі та рубильники, євро'
-- https://learn.microsoft.com/ru-ru/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver16
-- https://learn.microsoft.com/ru-ru/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
-- https://learn.microsoft.com/ru-ru/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver16
FROM [dbo].[Електротовари]
WHERE category IN ('TMAX moulded case circuit breakers', 'ETIMAT Circuit breakers', 
				'OTC switch disconnectors', 'OT switch-disconnectors', 'Switching and protective devices');


-- 2. Отримати інформацію про товари, які були доставлені позавчора (10 березня) та будуть доставлені через 5 днів 
-- (17 березня) від сьогодні, 12 березня, в кількості більше 2 одиниць.
SELECT *
FROM [dbo].[Електротовари]
WHERE (date_of_delivery = CONVERT(DATE, DATEADD(DAY, -2, GETDATE()))
		OR date_of_delivery = CONVERT(DATE, DATEADD(DAY, 5, GETDATE())))
	AND quantity > 2;
-- https://learn.microsoft.com/ru-ru/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16
-- https://learn.microsoft.com/ru-ru/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver16
-- https://learn.microsoft.com/ru-ru/sql/t-sql/functions/getdate-transact-sql?view=sql-server-ver16


-- 3. Показати назви та ціни товарів, доставлених протягом останнього місяця.
SELECT product_type, price_EUR
FROM [dbo].[Електротовари]
WHERE date_of_delivery BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE();


-- 4. Показати на екран назви та ціни товарів торгової марки ABB, але тільки не виробництва Фінляндії.
SELECT product_type, price_EUR
FROM [dbo].[Електротовари]
WHERE producer = 'ABB' AND country != 'Finland';


-- 5. Показати на екран усі товари, назви яких починаються на букву "O", 
-- а категорії не містять велику букву "C".
SELECT *
FROM [dbo].[Електротовари]
WHERE product_type COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'O%' 
  AND category COLLATE SQL_Latin1_General_CP1_CS_AS NOT LIKE '%C%';
-- https://learn.microsoft.com/ru-ru/sql/t-sql/statements/collations?view=sql-server-ver16


-- 6. Показати на екран усі товари, назви яких починаються з "C" і до "L" включно.
SELECT *
FROM [dbo].[Електротовари]
WHERE product_type LIKE '[C-L]%';


-- 7. Показати всі товари, вартість яких менше 10 євро, і дата постачання яких впродовж 
-- тижня від поточної дати.
SELECT *
FROM [dbo].[Електротовари]
WHERE price_EUR < 10 AND date_of_delivery BETWEEN GETDATE() AND DATEADD(WEEK, 1, GETDATE());


-- 8. Показати всі товари торгової марки Phoenix Contact, кількість яких не менше 4.
SELECT *
FROM [dbo].[Електротовари]
WHERE producer = 'Phoenix Contact' AND quantity >= 4;


-- 9. Отримати інформацію про товари ціною від 10 до 100 євро, відсортувавши ціну за зростанням.
SELECT *
FROM [dbo].[Електротовари]
WHERE price_EUR BETWEEN 10 AND 100
ORDER BY price_EUR ASC;
-- https://learn.microsoft.com/ru-ru/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16


-- 10. Зменшити ціни на всі товари на 5%.
BEGIN TRANSACTION;
-- https://learn.microsoft.com/ru-ru/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver16
UPDATE [dbo].[Електротовари]
-- https://learn.microsoft.com/ru-ru/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16
SET price_EUR = ROUND(0.95 * price_EUR, 2);
SELECT *
FROM [dbo].[Електротовари];
-- Відміняємо зміни!
ROLLBACK;
-- https://learn.microsoft.com/ru-ru/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-ver16
SELECT *
FROM [dbo].[Електротовари];


-- 11. Встановити термін дії гарантії не більше 1 року від дати доставки на всі товари, окрім торгової марки SEL.
BEGIN TRANSACTION;
-- Tasks Back Up...
UPDATE [dbo].[Електротовари]
SET warranty_end_date = DATEADD(YEAR, 1, date_of_delivery)
WHERE warranty_end_date > DATEADD(YEAR, 1, date_of_delivery) AND producer != 'SEL';
SELECT *
FROM [dbo].[Електротовари];
ROLLBACK;
SELECT *
FROM [dbo].[Електротовари];


-- 12. Видалити всі товари, мінімальна кількість замовлення яких 10 штук, а ціна більше 5 євро.
BEGIN TRANSACTION;
DELETE FROM [dbo].[Електротовари]
-- https://learn.microsoft.com/ru-ru/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver16
WHERE min_order_quantity > 10 AND price_EUR > 5;
SELECT *
FROM [dbo].[Електротовари];
ROLLBACK;
SELECT *
FROM [dbo].[Електротовари];


-- 13. Видалити всі товари від постачальників "Вольтлайн" та "Електроконтроль".
BEGIN TRANSACTION;
DELETE FROM [dbo].[Електротовари]
WHERE supplier = 'Voltline' OR supplier = 'Electrocontrol';
SELECT *
FROM [dbo].[Електротовари];
ROLLBACK;
SELECT *
FROM [dbo].[Електротовари];


-- 14. Показати на екран усі товари, у замовному коді яких міститься РІВНО 3 цифри "0" 
-- (у будь-яких місцях замовного коду, не обов'язково підряд).
SELECT *
FROM [dbo].[Електротовари]
-- https://learn.microsoft.com/ru-ru/sql/t-sql/functions/len-transact-sql?view=sql-server-ver16
WHERE order_code LIKE '%0%0%0' 
	AND LEN(REPLACE(order_code, '0', '')) = LEN(order_code) - 3;
-- https://learn.microsoft.com/ru-ru/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver16


-- 15. Видалити всі товари, замовні коди яких складаються з 7 цифр.
BEGIN TRANSACTION;
DELETE FROM [dbo].[Електротовари]
WHERE order_code LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
	AND NOT order_code LIKE '%[^0-9]%';
-- замовний код не повинен містити жодних інших символів, крім цифр.
SELECT *
FROM [dbo].[Електротовари];
ROLLBACK;
SELECT *
FROM [dbo].[Електротовари];


-- 16. Видалити всі товари, термін дії гарантії на які не перевищує 1 рік від поточної дати.
BEGIN TRANSACTION;
DELETE FROM [dbo].[Електротовари]
WHERE warranty_end_date <= DATEADD(YEAR, 1, GETDATE());
SELECT *
FROM [dbo].[Електротовари];
ROLLBACK;
SELECT *
FROM [dbo].[Електротовари];


-- 17. Показати 5 найдорожчих товарів.
SELECT TOP 5 *
-- https://learn.microsoft.com/ru-ru/sql/t-sql/queries/top-transact-sql?view=sql-server-ver16
FROM [dbo].[Електротовари]
ORDER BY price_EUR DESC;


-- 18. Видалити всі товари постачальників не "Вольтлайн" та не "СВ Альтера", 
-- або ж якщо знижка на ці товари більше 10%.
BEGIN TRANSACTION;
DELETE FROM [dbo].[Електротовари]
WHERE (supplier NOT IN ('Voltline', 'SV Altera')) OR discount > 10;
SELECT *
FROM [dbo].[Електротовари];
ROLLBACK;
SELECT *
FROM [dbo].[Електротовари];