hw3.sql

/* 1
SELECT name, price, quantity, category, discount, discountPercentage, price * quantity as [можливий дохід за хліб та молоко], (price * quantity) * (discountPercentage * 0.01) as [можливий дохід за хліб та молоко з урахуванням знижок]
FROM productTable
WHERE category IN (N'Bakery', N'Dairy') */
/* 2
SELECT name, price, quantity, date_of_delivery
FROM productTable
WHERE quantity > 10 AND date_of_delivery >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AND date_of_delivery >= DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) */
/* 3
SELECT name, price, quantity, date_of_delivery
FROM productTable
WHERE date_of_delivery >= DATEADD(MONTH, -1, CAST(GETDATE() AS DATE)) */
/* 4
SELECT name, price, quantity, category, producer
FROM productTable --
WHERE category IN (N'Snacks') AND producer NOT LIKE N'Roshen' */
/* 5
SELECT name, price, quantity, category, producer
FROM productTable --
WHERE category LIKE N'%a%' AND name LIKE N'c%' */
/* 6
SELECT name, price, quantity, category, producer
FROM productTable --
WHERE name LIKE N'[c-m]%' */
/* 7
SELECT name, price, quantity, date_of_delivery
FROM productTable
WHERE price < 50 AND date_of_delivery >= DATEADD(WEEK, -1, CAST(GETDATE() AS DATE)) */
/* 8
SELECT name, price, quantity, category
FROM productTable
WHERE category IN (N'Drinks') AND quantity > 100 */
/* 9
SELECT name, price, quantity, category
FROM productTable
WHERE price > 100 AND price < 200
ORDER BY price */
/* 10
SELECT name, price, price * 0.95 as '5% off', quantity, category
FROM productTable
ORDER BY price */
/* 12
DELETE FROM productTable
WHERE quantity < 100 AND price > 70 */
/* 13
DELETE FROM productTable
WHERE category IN (N'Alcohol', N'Dairy') */
/* 14
SELECT name, price, category
FROM productTable
WHERE name LIKE N'%o%o%o%' AND name NOT LIKE N'%o%o%o%o%' */
/* 15
SELECT name, price, category
from productTable
WHERE LEN(name) = 5 */
/* 16
SELECT name, price, quantity, date_of_delivery
FROM productTable
WHERE date_of_delivery < DATEADD(MONTH, -3, CAST(GETDATE() AS DATE)) */
/* 17
SELECT TOP 5 name, price, quantity, category
FROM productTable
ORDER BY price DESC; */
/* 18
DELETE FROM productTable
WHERE producer = NULL AND discountPercentage > 10 */