TestTaskSolutionByTania, Sql Server

TestTaskSolutionByTania

/*Тестовые задания на позицию Junior SQL Developer*/

-- Создание и заполнение таблиц
create table dbo.Promo (
	ID			int
	,Store		varchar(255)
	,Product	varchar(255)
	,DateBegin	date
	,DateEnd	date
	)

insert into dbo.Promo (ID, Store, Product, DateBegin, DateEnd)
values
	(1,		'Tesco',	'Gum',			'2018-07-21',	'2018-07-29')
	,(2,	'Tesco',	'Fish',			'2018-08-01',	'2018-08-17')
	,(3,	'Tesco',	'Juice',		'2018-06-06',	'2018-06-15')
	,(6,	'Tesco',	'Shampoo',		'2018-06-28',	'2018-07-07')
	,(7,	'Tesco',	'Coffee',		'2018-06-14',	'2018-06-30')
	,(9,	'Tesco',	'Sugar',		'2018-07-05',	'2018-07-19')
	,(10,	'Tesco',	'Tea',			'2018-06-01',	'2018-06-05')
	,(11,	'Tesco',	'Milk',			'2018-08-03',	'2018-08-14')
	,(12,	'Tesco',	'Wet Wipes',	'2018-08-20',	'2018-08-31')
	,(13,	'Billa',	'Shampoo',		'2018-06-28',	'2018-07-07')
	,(14,	'Billa',	'Coffee',		'2018-06-12',	'2018-06-27')
	,(15,	'Billa',	'Sugar',		'2018-08-01',	'2018-08-12')
	,(16,	'Billa',	'Tea',			'2018-06-04',	'2018-06-18')
	,(17,	'Billa',	'Milk',			'2018-07-07',	'2018-07-21')
	,(18,	'Billa',	'Wet Wipes',	'2018-08-10',	'2018-08-25')
	,(19,	'Auchan',	'Coffee',		'2018-06-05',	'2018-06-18')
	,(20,	'Auchan',	'Fish',			'2018-07-22',	'2018-08-02')
	,(21,	'Auchan',	'Sugar',		'2018-07-01',	'2018-07-31')

create table dbo.ProductPrice (
	Pricelist	varchar(255)
	,Product	varchar(255)
	,Price		decimal(18, 2)
	)

insert into dbo.ProductPrice (Pricelist, Product, Price)
values
	('Regular',		'T-shirt',		10)
	,('Regular',	'Pants',		20)
	,('Regular',	'Sweatshirt',	15)
	,('Regular',	'Bike',			100)
	,('Regular',	'Skate',		50)
	,('Exclusive',	'T-shirt',		8)
	,('Exclusive',	'Pants',		17)
	,('Exclusive',	'Sweatshirt',	13)
	,('Exclusive',	'Bike',			90)
	,('Exclusive',	'Skate',		43)
	,('Summer',		'T-shirt',		12)
	,('Summer',		'Pants',		20)
	,('Summer',		'Sweatshirt',	12)
	,('Summer',		'Bike',			110)
	,('Summer',		'Skate',		57)

create table dbo.ProductHierarchy (
	ID			int
	,Name		varchar(255)
	,ID_Parent	int null
	,Level		int
	)

insert into dbo.ProductHierarchy (ID, Name, ID_Parent, Level)
values
	(1,		'Food',					null,	1)
	,(2,	'Non food',				null,	1)
	,(3,	'Chocolates',			1,		2)
	,(4,	'Beverage',				1,		2)
	,(5,	'Sweets',				1,		2)
	,(6,	'Wet Wipes',			2,		2)
	,(7,	'Shampoo',				2,		2)
	,(8,	'Dark chocolate',		3,		3)
	,(9,	'White chocolate',		3,		3)
	,(10,	'Milk chocolate',		3,		3)
	,(11,	'Juices', 				4,		3)
	,(12,	'Water', 				4,		3)
	,(13,	'Boiled sweets',		5,		3)
	,(14,	'Chewy sweets',			5,		3)
	,(15,	'Buble Gum',			5,		3)
	,(16,	'Antibacterial wipes',	6,		3)
	,(17,	'Aroma wipes',			6,		3)
	,(18,	'Dry Shampoo',			7,		3)
	,(19,	'Clarifying Shampoo',	7,		3)

/*
Для каждой таблицы нужно выполнить соответствующее задание
Код нужно писать сразу под заданием и комментировать после решения задачи
После выполнения всех задач, сохранить и прислать файл в формате *.sql или ссылку на страницу
*/

-- 1. Таблица: dbo.Promo
--    Вывести номер акции, название магазина и название продукта, а также
--    номер акции и название продукта, где акция
--    пересекается по датам проведения для данного магазина

SELECT
    l.ID,
    l.Store,
    l.Product,
    r.ID AS AnotherID,
    r.Product AS AnotherProduct
FROM
    dbo.Promo as l
LEFT JOIN
    dbo.Promo as r
ON (l.Store = r.Store)
    AND (l.DateBegin  < r.DateEnd)
    AND (r.DateBegin < l.DateEnd)
    -- если нужно, чтобы продукты различались, тогда нужно добавить:
    -- AND (l.Product != r.Product)

-- 2. Таблица: dbo.Promo
--    Вывести номер акции, название магазина и название продукта, а также
--    названия магазинов и название продуктов, где акция
--    пересекается по датам проведения с акциями в других магазинах

SELECT
    l.ID,
    l.Store,
    l.Product,
    r.Store AS AnotherStore,
    r.Product AS AnotherProduct
FROM
    dbo.Promo as l
LEFT JOIN
    dbo.Promo as r
ON (l.Store != r.Store)
    AND (l.DateBegin  < r.DateEnd)
    AND (r.DateBegin < l.DateEnd)

-- 3. Таблица: dbo.ProductPrice
--    Необходимо написать запрос, который выводит цены
--    по каждому продукту (в строках) и прайс-листам (в столбцах)

SELECT
    Product, 
    CAST([Regular] as decimal(18, 2)) AS Regular,
    CAST([Exclusive] as decimal(18, 2)) AS Exclusive,
    CAST([Summer] as decimal(18, 2)) AS Summer
FROM  
    (SELECT Product, Price, Pricelist
    FROM dbo.ProductPrice) AS SourceTable  
    PIVOT  
    (  
    AVG(Price) 
    FOR Pricelist IN ([Regular], [Exclusive], [Summer])
    ) AS PivotTable;

-- 4. Таблица: dbo.ProductHierarchy
--    Требуется написать запрос, который преобразует иерархию в плоскую (денормализованную) структуру, т.е.
--    у каждого уровня иерархии должен быть свой столбец (ID_Level1, Name_Level1, ID_Level2, Name_Level2, ID_Level3, Name_Level3)
--    Считаем, что иерархия сбалансирована, т.е. в каждой ветке иерархии одинаковое кол-во уровней
SELECT
    Level1.ID_Level1,
    Name_Level1,
    Level2.ID_Level2,
    Name_Level2,
    Level3.ID_Level3,
    Name_Level3
FROM
    (SELECT
        ID AS ID_Level3,
        Name AS Name_Level3,
        ID_Parent as ID_Level2
    FROM dbo.ProductHierarchy
    WHERE Level = 3) AS Level3
FULL JOIN 
    (SELECT
        ID AS ID_Level2,
        Name AS Name_Level2,
        ID_Parent as ID_Level1
    FROM dbo.ProductHierarchy
    WHERE Level = 2) AS Level2
ON Level2.ID_Level2 = Level3.ID_Level2
FULL JOIN 
    (SELECT
        ID AS ID_Level1,
        Name AS Name_Level1
    FROM dbo.ProductHierarchy
    WHERE Level = 1) AS Level1
ON Level1.ID_Level1 = Level2.ID_Level1


run \| edit \| history \| help	0