Notes

--1
SELECT name_sel, city_sel FROM Sellers UNION SELECT Name_cust, city_cust FROM Customer
SELECT city_sel AS city FROM Sellers UNION ALL SELECT city_cust AS city FROM Customer ORDER BY city DESC




--3
CREATE TABLE Products (prod_id int PRIMARY KEY not null,
name nvarchar(50) null,
price int null,
description nvarchar(50) null)

CREATE TABLE Sell_2015 (id_sel int PRIMARY KEY not null,
prod_id int not null,
count int null,
sel_date date null)

SELECT * INTO Sell_2016 FROM Sell_2015
SELECT * INTO Sell_2017 FROM Sell_2015

INSERT INTO Products (prod_id, name, price, description) VALUES
(1, 'Notebook Asser15.6 intel core i5', 500, 'ddr 500'),
(2, 'Notebook Asser13.3 intel core i5', 600, 'ssd 125'),
(3, 'Notebook Asser13.3 intel core i7', 750, 'ssd 250'),
(4, 'Notebook HP13.3 intel core i7', 800, 'ssd 250'),
(5, 'Notebook HP15.6 intel core i7', 600, 'ddr 1trb'),
(6, 'Mouse laser', 10, 'Geniuse'),
(7, 'Mouse Bletooth', 15, 'HP')



INSERT INTO Sell_2015 (id_sel, prod_id, count, sel_date) VALUES
(1, 1, 1, '2015-04-01'),
(2, 3, 1, '2015-08-25'),
(3, 6, 5, '2015-10-10'),
(4, 2, 1, '2015-10-25'),
(5, 7, 3, '2015-12-23')

INSERT INTO Sell_2016 (id_sel, prod_id, count, sel_date) VALUES
(1, 2, 1, '2016-02-12'),
(2, 3, 2, '2016-02-26'),
(3, 4, 1, '2016-04-12'),
(4, 5, 2, '2016-06-20'),
(5, 8, 4, '2016-09-23')


INSERT INTO Sell_2017 (id_sel, prod_id, count, sel_date) VALUES
(1, 1, 1, '2017-04-12'),
(2, 4, 1, '2017-05-26'),
(3, 5, 1, '2017-07-12'),
(4, 7, 5, '2017-09-20')



--5
SELECT name, id_sel, sel_date, count FROM Products, Sell_2015 WHERE Sell_2015.prod_id = Products.prod_id UNION
SELECT name, id_sel, sel_date, count FROM Products, Sell_2016 WHERE Sell_2016.prod_id = Products.prod_id UNION
SELECT name, id_sel, sel_date, count FROM Products, Sell_2017 WHERE Sell_2017.prod_id = Products.prod_id
ORDER BY count

SELECT name, id_sel, sel_date, count FROM Products, Sell_2015 WHERE Sell_2015.prod_id = Products.prod_id UNION ALL
SELECT name, id_sel, sel_date, count FROM Products, Sell_2016 WHERE Sell_2016.prod_id = Products.prod_id UNION ALL
SELECT name, id_sel, sel_date, count FROM Products, Sell_2017 WHERE Sell_2017.prod_id = Products.prod_id
ORDER BY count



--6
SELECT Products.name, Sell_2017.count, Sell_2017.sel_date FROM Products, Sell_2017 WHERE Sell_2017.prod_id = Products.prod_id UNION
SELECT Products.name, Sell_2016.count, Sell_2016.sel_date FROM Products, Sell_2016 WHERE Sell_2016.prod_id = Products.prod_id
ORDER BY Sell_2017.sel_date DESC


SELECT Products.name, Sell_2017.count, Sell_2017.sel_date FROM Products, Sell_2017 WHERE Sell_2017.prod_id = Products.prod_id UNION ALL
SELECT Products.name, Sell_2016.count, Sell_2016.sel_date FROM Products, Sell_2016 WHERE Sell_2016.prod_id = Products.prod_id
ORDER BY Sell_2017.sel_date DESC






--7
SELECT year(sel_date), MAX(count) FROM Sell_2015 GROUP BY year(sel_date)
UNION
SELECT year(sel_date), MAX(count) FROM Sell_2016 GROUP BY year(sel_date)
UNION
SELECT year(sel_date), MAX(count) FROM Sell_2017 GROUP BY year(sel_date)

--8
SELECT Products.name FROM Products, Sell_2015 WHERE Sell_2015.prod_id = Products.prod_id
EXCEPT
SELECT Products.name FROM Products, Sell_2016 WHERE Sell_2016.prod_id = Products.prod_id



--9
SELECT Products.name, Products.prod_id FROM Products, Sell_2015 WHERE Sell_2015.prod_id = Products.prod_id
INTERSECT
SELECT Products.name, Products.prod_id FROM Products, Sell_2016 WHERE Sell_2016.prod_id = Products.prod_id
INTERSECT
SELECT Products.name, Products.prod_id FROM Products, Sell_2017 WHERE Sell_2017.prod_id = Products.prod_id


--10
create table test_table_pivot(
id int not null,
name nvarchar(30) null,
lastname nvarchar(30) null,
year int null,
sum_bonus float null
)

insert into test_table_pivot (name, lastname, year, sum_bonus) values
('Simon', 'Karapetyan', 2017, 200),
('Simon', 'Karapetyan', 2017, 100),
('Simon', 'Karapetyan', 2018, 150),
('Simon', 'Karapetyan', 2018, 100),
('Simon', 'Karapetyan', 2019, 100),
('Simon', 'Karapetyan', 2020, 120),
('Simon', 'Karapetyan', 2020, 80),
('Narine', 'Vardazaryan', 2017, 100),
('Narine', 'Vardazaryan', 2017, 100),
('Narine', 'Vardazaryan', 2018, 100),
('Narine', 'Vardazaryan', 2019, 80),
('Narine', 'Vardazaryan', 2019, 120),
('Vardan', 'Nazaryan', 2017, 200),
('Vardan', 'Nazaryan', 2017, 120),
('Vardan', 'Nazaryan', 2018, 150),
('Vardan', 'Nazaryan', 2018, 150),
('Vardan', 'Nazaryan', 2020, 160)

select name, lastname, year, sum(sum_bonus) as total
from test_table_pivot
group by name, lastname, year

select name, lastname, [2017], [2018], [2019], [2020]
from(
select name, lastname, sum_bonus, [year] from test_table_pivot
) as new_t
pivot (sum(sum_bonus) for [year] in ([2017], [2018], [2019], [2020])) as test

create table product_stock(
id int not null,
product_name nvarchar(50) null,
stock1 int null,
stock2 int null,
stock3 int null,
stock4 int null
)

insert into product_stock (product_name, stock1, stock2, stock3, stock4) values
('mouse', 10, 10, 20, 25),
('keyboard', 18, null, 34, 10),
('printer', 2, 9, 6, 5),
('scanner', null, 6, 10, 3)

select * from product_stock

select product_name, stocks, quantity from product_stock
unpivot (quantity for stocks in (stock1, stock2, stock3, stock4)) as new_unpivot



--11
SELECT * INTO TOTAL_SELLS FROM Sell_2015
INSERT INTO TOTAL_SELLS (prod_id, count, sel_date)
SELECT prod_id, count, sel_date FROM Sell_2015
UNION
SELECT prod_id, count, sel_date FROM Sell_2016
UNION
SELECT prod_id, count, sel_date FROM Sell_2017
ORDER BY sel_date






select name, coalesce([2015], [2016], [2017], [2015] + [2016] + [2017]) as count
from(
SELECT name, count, year(sel_date) AS [year] FROM Products, TOTAL_SELLS WHERE Products.prod_id = TOTAL_SELLS.prod_id
) as new_t
pivot( sum(count) for [year] in ([2015], [2016], [2017])) as test