SQLize | PHPize | SQLtest

--=============================================== --Создание и заполнение базы --=============================================== --Создание таблиц 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