SQLize | PHPize | SQLtest

-- --Задание 3 -- CREATE PROCEDURE upd -- @LocName varchar(50), -- @Height decimal(20,2) = -1, -- @Length decimal(20,2) = -1, -- @Width decimal(20,2) = -1 -- AS -- IF @LocName IN -- (SELECT LocName -- FROM RC_Locations) -- UPDATE RC_Locations SET -- Height = (CASE WHEN @Height <> -1 THEN @Height ELSE Height END), -- Length = (CASE WHEN @Length <> -1 THEN @Length ELSE Length END), -- Width = (CASE WHEN @Width <> -1 THEN @Width ELSE Width END) -- WHERE LocName=@LocName -- ELSE -- INSERT INTO RC_Locations -- VALUES(@LocName, -- (CASE WHEN @Height = -1 THEN NULL ELSE @Height END), -- (CASE WHEN @Length = -1 THEN NULL ELSE @Length END), -- (CASE WHEN @Width = -1 THEN NULL ELSE @Width END)); -- EXEC upd @LocName = 'C1-01-09', -- @Length = 0.7, -- @Width = 0.7 -- EXEC upd @LocName = '08-80-07', -- @Length = 0.3, -- @Width = 0.5 -- EXEC upd @LocName = '08-80-07', -- @Height = 1.3 --=============================================== --Создание и заполнение базы --=============================================== --Создание таблиц CREATE TABLE RC_Materials ( id int IDENTITY (1,1), NameMaterial varchar(50), ExpDate date ) CREATE TABLE RC_Providers ( id int IDENTITY (1,1), NameProvider varchar(50) ) CREATE TABLE RC_PackTypes ( id int IDENTITY (1,1), PackName varchar(10), Volume decimal(20,5) ) CREATE TABLE RC_Locations ( id int IDENTITY (1,1), LocName varchar(50), Height decimal(20,2), Length decimal(20,2), Width decimal(20,2) ) CREATE TABLE RC_Requests ( id int IDENTITY (1,1), RequestName varchar(20), Provider_id int, DeliveryDate datetime ) CREATE TABLE RC_IncomeSummary ( id int IDENTITY (1,1), Material_id int, Location_id int, Request_id int, RecordDate datetime, Quantity decimal, PackType_id int ) -------------------------------------------------------------------------------------------- --Заполнение таблиц insert into RC_Materials (NameMaterial, ExpDate) values ('Акварель (18 цветов)', '2025-08-09'), ('Мишура', null), ('Свеча рождественская', '2022-12-31'), ('Тетрадь 48л', null), ('Ручка шарик. авт', '2030-01-01'), ('Чайник Starwind 5л', '2032-05-07'), ('Дневник 5-11 кл. 48л', null) ,('Календарь 2022','2022-12-31') ,('Воздушные шары 25 шт',null) ,('Дневник 1-4 кл. 48л',null) -------------------------------------------------------------------------------------------- insert into RC_Providers values ('ООО "МегаРон"'),('ООО "Север"'),('ИП Опарина Т.И.') -------------------------------------------------------------------------------------------- insert into RC_PackTypes (PackName,Volume) values ('Короб1',0.015), ('Короб2',0.032), ('Короб3',0.04), ('Штука',0.0004) -------------------------------------------------------------------------------------------- insert into RC_Locations (LocName, Height, Length, Width) values ('C08-36-10', 0.8, 0.72, 1.20), ('M11-01-12', 0.8, 0.48, 1.0), ('08-80-03', 0.8, 0.5, 1.05), ('07-92-02', 0.9, 0.48, 0.5), ('C1-01-09', 1.0, 0.5, 0.5) -------------------------------------------------------------------------------------------- insert into RC_Requests (RequestName, Provider_id, DeliveryDate) values ('R0012074',1,'2022-03-11 11:00'), ('R0011956',3,'2022-04-24 10:00'), ('R0012087',1,'2022-05-03 08:00'), ('R0000708',2,'2022-05-04 13:00'), ('R0012070',1,'2022-06-11 15:00'), ('R0002731',3,'2022-08-05 07:30') -------------------------------------------------------------------------------------------- insert into RC_IncomeSummary (Material_id, Location_id, Request_id, RecordDate, Quantity, PackType_id) values (1, 3,1,'2022-03-11 11:10',2,1), (6, 1, 1,'2022-03-11 11:15',1,2),(4, 3,1,'2022-03-11 11:16',10,4), (2, 3,2,'2022-04-24 10:10',2,1), (3, 3, 2,'2022-04-24 10:18',20,4),(4,2,2,'2022-04-24 10:27',1,1), (7, 4,3,'2022-05-03 08:10',1,1), (10, 4, 3,'2022-05-03 08:30',1,3),(8,4,3,'2022-05-03 08:32',1,1),(5,4,3,'2022-05-03 08:35',2,1), (9,1,4,'2022-05-04 13:02',5,4),(2, 1, 4,'2022-05-04 13:10',1,3), (1,4,5,'2022-06-11 15:08',1,2),(4, 4, 5,'2022-06-11 15:10',1,1),(5, 4, 5,'2022-06-11 15:18',1,1), (8,1,6,'2022-08-05 07:50',5,4),(3, 1, 6,'2022-08-05 07:59',1,3) -------------------------------------------------------------------------------------------- --=============================================== -- Вывод информации из таблиц -- =============================================== select * from RC_Materials select * from RC_PackTypes select * from RC_Providers select * from RC_Locations select r.id, r.RequestName, p.NameProvider, r.DeliveryDate from RC_Requests r join RC_Providers p on r.Provider_id = p.id select i.id, m.NameMaterial, l.LocName, r.RequestName, i.RecordDate, i.Quantity, p.PackName from RC_IncomeSummary i join RC_Materials m on m.id = i.Material_id join RC_Locations l on l.id = i.Location_id join RC_Requests r on r.id = i.Request_id join RC_PackTypes p on p.id = i.PackType_id Select * from rc_IncomeSummary -- --Задание 1 -- SELECT LocName, -- CASE -- WHEN (SUM(pac.Volume*Quantity)/V)*100 IS NOT NULL -- THEN (SUM(pac.Volume*Quantity)/V)*100 -- ELSE 0 -- END AS fullness -- FROM -- (SELECT id,LocName, Height*Length*Width AS V -- FROM RC_Locations ) loc -- LEFT JOIN RC_IncomeSummary inc ON loc.id=inc.Location_id -- LEFT JOIN RC_PackTypes pac ON inc.PackType_id=pac.id -- GROUP BY LocName,V -- ORDER BY fullness DESC -- --Задание 2 -- SELECT LocName -- FROM RC_Locations loc -- WHERE loc.id NOT IN -- (SELECT Location_id -- FROM RC_IncomeSummary inc -- WHERE RecordDate<'2022-05-01') --Задание 3 -- EXEC upd @LocName = 'C1-01-09', -- @Length = 0.7, -- @Width = 0.7 -- EXEC upd @LocName = '08-80-07', -- @Length = 0.3, -- @Width = 0.5 -- EXEC upd @LocName = '08-80-07', -- @Height = 1.3 --ЗАДАНИЕ 4 WITH nums AS (SELECT req.RequestName, mat.NameMaterial, ROW_NUMBER() OVER (PARTITION BY req.RequestName ORDER BY RecordDate) num FROM RC_Requests req LEFT JOIN RC_IncomeSummary inc ON req.id = inc.Request_id LEFT JOIN RC_Materials mat ON inc.Material_id = mat.id) END SELECT * FROM nums WHERE num<=2