Untitled
unknown
pgsql
a year ago
30 kB
16
Indexable
/*
1. Для компании, имеющей максимальное количество траснпортных средств, вывести название компании и суммарное количество продуктов.
2. Для каждой компании, имеющей товаров на сумму от 100_000_000 до 120_000_000 или суммарную грузоподъемность от 70 до 80, вывести название компании и количество транспортных средств.
3. Для каждой компании, имеющей количество транспортных средств до 10 и общее количество товаров от 10_000, вывести название компании и максимальную цену продуктов.
Для допуска к теории нужно решить минимум 2 из 3.
*/
drop table if exists provider_KEC, goods_YBH, provider_goods_info_i4L, auto_cqx cascade;
create table provider_KEC
(
id uuid primary key,
name text,
address text,
phone text,
email text
);
create table goods_YBH
(
id uuid primary key,
name text,
material text
);
create table provider_goods_info_i4L
(
goods_id uuid references goods_YBH,
provider_id uuid references provider_KEC,
primary key (goods_id, provider_id),
price numeric,
quantity integer
);
create table auto_cqx
(
id uuid primary key,
registration_mark text,
load_capacity integer,
provider_id uuid references provider_KEC
);
insert into provider_KEC values
('673ec199-5fd4-44af-9b7e-133e4ed58729', 'Barrows and Sons', '960 Purdy Cove', '1-553-640-5878 x577', 'Denis.Vandervort@hotmail.com'),
('995d9a6c-0607-491f-8f45-26487c09ce7c', 'Bosco and Sons', '4894 Franey Light', '890.595.1083 x08599', 'Cory.Denesik@yahoo.com'),
('7482dd96-6504-47fc-87f4-acb4bd3058e2', 'Krajcik and Sons', '19493 Franey Groves', '549-233-2854', 'Gaetano21@yahoo.com'),
('4171ba79-0961-4b79-b149-a3f9f51fff0d', 'Beahan, Goldner and Considine', '1880 Skylar Crest', '1-501-267-3577 x4804', 'Angelica.Buckridge@hotmail.com'),
('633137b4-37ed-424e-ab3c-20d878d45033', 'Lind - Reynolds', '126 Delilah Parkways', '1-392-700-7670 x7557', 'Jammie7@gmail.com'),
('8d1b3608-4182-41ac-a281-9edc5873fd4a', 'Harvey, Kassulke and Smitham', '10197 Aufderhar Lock', '(497) 929-3694 x99951', 'Arturo84@hotmail.com'),
('b0ab3a3b-f045-47d5-8c2c-ec442ef7792c', 'Bednar, Wehner and Will', '70923 Deja Station', '971.982.8630 x910', 'Vallie_Mohr@gmail.com'),
('73954c88-3a51-4bdd-aebb-6071daafe9b7', 'Miller LLC', '588 Cristopher Locks', '1-394-343-3613 x775', 'Jeanette35@gmail.com'),
('cacb0f42-f794-47f8-8379-6bb9a3572856', 'Turner and Sons', '86409 Hilario Via', '(626) 785-8350 x685', 'Janet_Gleason@hotmail.com'),
('1f572ea7-c108-4020-9e29-b0f59b228ef1', 'Blanda, Rogahn and Johnston', '8242 Rice Vista', '(618) 494-3178 x4264', 'Everardo13@yahoo.com'),
('7a24a1de-a768-4624-a1db-7819efb4e0b2', 'Cruickshank, McKenzie and Bode', '540 Monserrate Branch', '962-909-8377 x79884', 'Cory81@yahoo.com'),
('466607b4-c552-4df5-91dc-0783f082b91c', 'Batz, Senger and Heidenreich', '4980 Carroll Mountains', '(908) 908-1605', 'Jordan_Mayer61@yahoo.com'),
('e0fb1b37-e427-4ae9-86ff-08b7e9771fac', 'VonRueden - Stanton', '1571 Pablo Plain', '(668) 898-0914 x1373', 'Sid95@hotmail.com'),
('945f2580-fc51-466c-8c16-e4e6816b6296', 'Shanahan - Schaefer', '311 Teagan Skyway', '375-727-5516 x8784', 'Gianni95@hotmail.com'),
('1d50a64c-6491-4556-94c5-a4b2617ac5f8', 'Hackett - Reynolds', '3614 Maye Pass', '(848) 720-0427 x55240', 'Ralph.Klein9@gmail.com'),
('6a5726b8-b3a6-4d62-a966-42b748791252', 'Maggio, Harris and Runolfsson', '5873 Waldo Estates', '(247) 722-2707', 'Otilia.Mueller9@yahoo.com'),
('5abf7cd0-ac2e-452b-a09b-6b03b668f731', 'Abbott and Sons', '665 Nelle Knoll', '377-375-4380', 'Keagan_Marvin-Spinka@hotmail.com'),
('59fc7ed7-e41e-44d0-a036-81ffd3533d28', 'Wolff and Sons', '750 Kuhn Crescent', '1-953-719-1828', 'Clemens96@yahoo.com'),
('6e675933-3fd1-4492-a6c2-6797d79bb811', 'Johnson - Thompson', '13456 Meda Alley', '(946) 470-3253 x8774', 'Reginald43@yahoo.com'),
('d7567367-c81e-45a3-b04c-0af8785f4f49', 'Purdy Group', '403 Freda Roads', '456.233.7504 x980', 'Zion88@hotmail.com'),
('6dbf5aa8-8f59-4fd3-8712-07ef2aa6641c', 'Kiehn Group', '1988 Nicolas Stream', '511-982-0074 x6304', 'Magnus1@hotmail.com'),
('93a89120-df52-421c-afa4-16e59d4aac68', 'Fadel - Schoen', '233 Dock Valley', '538.747.2246 x778', 'Samara.Hills83@hotmail.com'),
('0501dd18-d910-44bd-aca2-366c4135eafe', 'Gutkowski Group', '2160 Beryl Garden', '593-410-0907 x084', 'Isobel48@hotmail.com'),
('4c072c90-fdbc-4574-9e5c-c5648be3402c', 'Aufderhar - Olson', '6632 Amie Keys', '219-426-9950', 'Emmie.Aufderhar@yahoo.com'),
('57e94ac6-493b-45c2-bc07-50ebad317335', 'Quitzon - Howell', '815 Feeney Union', '(467) 536-6797 x4024', 'Vickie_Carter@yahoo.com'),
('0cba2045-f4f3-4a9d-b24c-2618d6ab90c8', 'Dietrich and Sons', '3336 Rhea Wells', '227-221-3962 x110', 'Eladio5@gmail.com'),
('a7f7b005-44bb-48d1-b0b2-08c288dae5e0', 'Haag and Sons', '5493 Olen Wells', '904.311.4687 x125', 'Betsy_Koch@gmail.com'),
('6a17fa75-1f51-4cec-8ad5-9d1a2b091ef9', 'Lang - Kassulke', '459 Wellington Coves', '(504) 857-5313 x61385', 'Janis87@gmail.com'),
('94c41384-7997-47f7-a504-4cf3b0eb1c9c', 'O''Connell - Romaguera', '2229 VonRueden Cliff', '741.395.9509', 'Jensen.Grady44@gmail.com'),
('946bd733-2ee8-49ab-999f-5554f9da1efa', 'Kutch Inc', '52292 O''Conner Ports', '452-205-5287', 'Kay68@yahoo.com');
insert into goods_YBH values
('b7033dfa-cff0-4c3e-a2ef-b36403f0e5ac', 'Fish', 'Soft'),
('4e9cd65c-9a3e-4ce8-a9f3-86e7a95fee0f', 'Pants', 'Granite'),
('7cb21077-7c68-49fa-92db-978534c072cb', 'Bacon', 'Cotton'),
('dec9a43e-268c-4585-9804-a5b59676078f', 'Chair', 'Metal'),
('4f0f2702-250e-4d59-bff6-972ea56a20b7', 'Pants', 'Rubber'),
('6ba4f308-59d0-4431-9ea1-726a07252029', 'Gloves', 'Bronze'),
('1f4c3836-87a3-46ea-8dbf-bc5dc1c5629b', 'Mouse', 'Steel'),
('8be7375c-b084-48d0-995b-80b97296513f', 'Mouse', 'Bronze'),
('f9985111-b9de-4fd6-bd6c-e8ccd1e60cd8', 'Bike', 'Wooden'),
('71a15958-b058-4436-9403-b262dfa7ae54', 'Shirt', 'Cotton'),
('ca911e43-f718-463c-b211-d00fde176e2d', 'Soap', 'Frozen'),
('1ca3d938-02cb-467f-ad15-caa8983a4748', 'Car', 'Metal'),
('e0d1a039-d958-4614-a29d-fe18d1c58030', 'Bacon', 'Fresh'),
('e3f4f753-d697-4fa5-a557-8343d6c2539c', 'Fish', 'Frozen'),
('4326a11c-49dd-4b80-a349-3a163ca723dd', 'Computer', 'Frozen'),
('aef27d58-795d-4dfc-859c-31f2db9fedf2', 'Salad', 'Fresh'),
('0f356fb6-6cb9-4c4f-8b5f-cd87971dbdae', 'Sausages', 'Soft'),
('091a67ce-4db6-49b8-b3d6-fa034016b2b2', 'Bacon', 'Plastic'),
('321306d8-6b6c-494c-931c-291a8c517cd6', 'Table', 'Concrete'),
('3a13bbc5-48eb-4775-8c04-5452a5ce343f', 'Pizza', 'Rubber'),
('5a6a07b3-55a8-47a0-9844-abcfaf953c80', 'Cheese', 'Granite'),
('5f0c6c5f-1c04-46ec-9790-9798cb315a1d', 'Ball', 'Metal'),
('e7d05e20-c7f3-4c69-b627-e94eb584cc0a', 'Keyboard', 'Granite'),
('e361e94f-cafa-46bb-a7a0-43e57637a671', 'Bike', 'Cotton'),
('2b7b546d-83b2-470f-a886-80a03ab810d6', 'Salad', 'Wooden'),
('38e180f6-81ea-497b-906b-2d216ad24c0a', 'Shirt', 'Granite'),
('f7ee9ee3-ab14-4ca7-a1f7-4cd5d89c031a', 'Salad', 'Metal'),
('532a42e3-60b9-4076-9896-3cff153d6304', 'Salad', 'Frozen'),
('e47297e1-6f58-4c3e-9e67-d615255361eb', 'Table', 'Bronze'),
('3208d55e-7ded-491a-975a-665e2cbacee4', 'Ball', 'Fresh'),
('7a72477b-42d8-48e0-9b52-4afd5b31d406', 'Table', 'Soft'),
('8421c9f7-518e-4a2c-bfbb-124f4fc33149', 'Gloves', 'Metal'),
('e729d909-fae2-49b2-8cb1-5a422f67dc04', 'Pizza', 'Steel'),
('cc5f330d-f80f-4403-8c53-620a731e91a6', 'Pizza', 'Plastic'),
('9ec8e840-e44b-4433-b5f5-ba1a8886a6e6', 'Sausages', 'Bronze'),
('7dd5c300-5535-42d8-9df6-efca16780295', 'Sausages', 'Wooden'),
('6843d41a-cca5-409c-b82f-32a37bc02578', 'Sausages', 'Fresh'),
('6d802dfb-9ff2-4942-847d-ecaaba0d79d1', 'Computer', 'Frozen'),
('061096c3-b93c-4f0f-8b88-43a01d630bb8', 'Sausages', 'Fresh'),
('2c4a9e8b-ff99-4764-a405-c1d694696710', 'Sausages', 'Cotton'),
('756b9180-e677-4841-9a7b-8f908900a3c7', 'Chips', 'Soft'),
('e662d54c-50fd-4c3d-9b56-e2040901a7c0', 'Car', 'Bronze'),
('e1895c0f-99d7-44b0-a0ce-2bdc6c4cc32c', 'Hat', 'Wooden'),
('c7a591a2-cafb-4993-8d87-0d8383458bba', 'Gloves', 'Wooden'),
('1f2a379e-5d9b-444a-89bf-f55a6909eebb', 'Keyboard', 'Soft'),
('d3bfd782-58f7-48d3-8dba-046b28ec6904', 'Soap', 'Fresh'),
('74126305-37fa-4716-a4e6-f0d93699688c', 'Fish', 'Metal'),
('6ba5c6b5-4e32-48e2-b0ad-407727315a81', 'Hat', 'Plastic'),
('354e5c6b-ff5c-48f2-b01c-7b4de0454338', 'Chicken', 'Cotton'),
('e12675cb-9d53-46b3-8ef3-28ee809fc221', 'Hat', 'Metal'),
('5c10c4c3-3bcd-4138-b3c8-8a32c1f6672c', 'Shirt', 'Metal'),
('47edad7f-28e8-4f65-ac59-706cf0a78e98', 'Table', 'Wooden'),
('a2d1776f-4bda-40be-bcb9-3db4584220c0', 'Hat', 'Plastic'),
('0c119fd4-4d72-47ac-b59e-155ae8b54d90', 'Table', 'Fresh'),
('312b9b16-d394-4579-bb90-ef6f3f4b390c', 'Car', 'Fresh'),
('e2c4ae6b-08e0-405d-bb25-35084257a2c0', 'Tuna', 'Bronze'),
('1000c5dd-a8c5-4df9-a81e-db114efc0e6b', 'Tuna', 'Rubber'),
('ed2f5e4f-10e1-474d-9f7a-2be0335a8e11', 'Hat', 'Rubber'),
('f5ef32c1-8122-4850-bb60-60f9650206b3', 'Computer', 'Bronze'),
('b62d6f94-b3ed-4125-af23-3d72c5eda600', 'Sausages', 'Metal'),
('dd7ec95f-6152-41ed-b84e-eb6e09dbdc52', 'Keyboard', 'Granite'),
('59b3fcfb-8cfb-497a-b90d-dd379a4ce823', 'Salad', 'Concrete'),
('b4dfbcf4-5eb2-4c3d-bb5b-7fd0166d6c67', 'Shoes', 'Plastic'),
('d4ddfeca-eb52-431e-be7e-1ad4f7c39348', 'Chair', 'Plastic'),
('815d7b57-261d-49c3-bf29-4ece3e3f571f', 'Chips', 'Steel'),
('f265b8f3-c346-4777-896f-a489de082798', 'Towels', 'Rubber'),
('cedef268-9457-488d-b267-37a0f0a02b52', 'Chicken', 'Wooden'),
('7cedbece-b3a0-4b6e-83f7-e9101d3cc2af', 'Fish', 'Wooden'),
('1cc4fc37-cd91-46b7-b285-27b93ada0ea4', 'Hat', 'Steel'),
('52aed58f-8d14-46d6-972d-c7044d4c64f0', 'Mouse', 'Concrete'),
('0658e475-658f-4ee1-a069-d1fb09426d33', 'Chicken', 'Bronze'),
('0c89de00-8cc9-480d-9dc2-580d4c1b0c2a', 'Mouse', 'Plastic'),
('bffbe5f9-7fc7-4ec3-80f3-fb0253289e26', 'Keyboard', 'Concrete'),
('8b20ed3f-0edd-4a36-a3d5-af6418fb03c0', 'Pizza', 'Frozen'),
('b2830bb0-d7a2-4696-ab52-c116de5409b2', 'Shirt', 'Bronze'),
('78643876-dffa-426a-8325-9e0dfa99d51f', 'Ball', 'Cotton'),
('5da5e9dd-67d5-4baa-ae45-840031b06c74', 'Cheese', 'Bronze'),
('3cc51863-07c1-412f-a633-c453d698cb0d', 'Bacon', 'Frozen'),
('ba20832c-1f8c-4ba5-87d1-db798d00eb0d', 'Bike', 'Bronze'),
('f762c760-95d8-46bf-a869-3e49d4df5e36', 'Shoes', 'Granite'),
('8440c3fb-4129-4fa8-9bcc-efc378302d42', 'Salad', 'Fresh'),
('1ed8dbc0-adfb-4b55-9106-fdc01cf51ece', 'Shirt', 'Soft'),
('5145143f-8a97-40ab-9f31-73c333c9b9fb', 'Chair', 'Wooden'),
('193cd2a5-53c8-48d9-8c7b-3ce784931a5d', 'Bike', 'Plastic'),
('c62cfae0-e810-4c78-a325-b6cb1696d5de', 'Car', 'Fresh'),
('1d469592-3be1-485f-8897-14d7c3d2d1ea', 'Soap', 'Steel'),
('4c4dae34-85e2-4571-8999-fb9709ee5595', 'Soap', 'Soft'),
('bdfc1abb-d557-4180-84e5-3231ae2927d6', 'Soap', 'Cotton'),
('b940ce9b-9f96-4f84-81f4-864569a0ef00', 'Fish', 'Wooden'),
('7328b14d-0259-4f65-ab33-222ddee8523c', 'Bike', 'Granite'),
('f2420221-4eee-4912-b528-63e6c545b5cd', 'Pants', 'Wooden'),
('d54d100e-8d07-4cc8-b41b-a54ee0865792', 'Sausages', 'Plastic'),
('4e40fc64-ea74-4a3f-bb7c-18b570879b1d', 'Bacon', 'Granite'),
('74c25db7-8913-49ce-becf-a687f2090caf', 'Fish', 'Metal'),
('595f6f24-444a-403c-9ced-96259c69c333', 'Table', 'Granite'),
('c56b45cf-6e91-4de1-ae6f-f39bcf7e07b5', 'Soap', 'Soft'),
('af631b8c-b99d-42e1-97f4-f209a4ae7597', 'Chair', 'Plastic'),
('2159f9aa-cab7-4f31-8c8b-e2202b066424', 'Soap', 'Wooden'),
('08ea5fba-c811-4f50-999b-cdb7e96964f0', 'Shoes', 'Concrete'),
('b4e00096-94f1-4f9e-aff5-b2d934e5df74', 'Chips', 'Granite'),
('6594cf22-176c-4a1d-bed3-49e018616597', 'Shoes', 'Rubber'),
('177b561a-0678-41af-af0d-93d2730a8644', 'Soap', 'Fresh'),
('46e18d48-bca2-499b-8857-d1ba1ed1eec1', 'Bike', 'Metal'),
('f6f2eb28-aa78-481c-8973-963cd2a14458', 'Tuna', 'Granite'),
('620f5c45-27c1-4c76-95e5-67595c998023', 'Hat', 'Bronze'),
('5416a62f-5935-4da9-80b4-235aeb066149', 'Chair', 'Frozen'),
('25266c13-b212-4cd6-ac19-bf52594a7815', 'Towels', 'Wooden'),
('45fd7ddc-8072-4e2f-92bc-06523470ccc1', 'Shirt', 'Metal'),
('747b455d-b2a7-4358-86a3-9c96e9df19fa', 'Ball', 'Plastic'),
('0e0d39df-682c-4124-8e74-4aef066c7650', 'Pants', 'Granite'),
('292bb4db-eeb8-4923-bbd5-67cabeec93a3', 'Bike', 'Cotton'),
('57773cb9-da29-4b6e-bcd1-981d41110e6e', 'Towels', 'Wooden'),
('73ce6bc3-643d-4dd4-85d7-bac6b615402b', 'Bike', 'Concrete'),
('3e726353-3c05-4aa4-af38-f8a97d52d2ea', 'Tuna', 'Frozen'),
('8f6c67cf-c2ba-44ec-9d02-cd03093425c4', 'Shoes', 'Fresh'),
('2210a777-57f2-4b79-909c-e2905b98db1f', 'Hat', 'Bronze'),
('c6cbe432-6496-45ee-9374-d63535bb2fc3', 'Pizza', 'Soft'),
('7c17a350-5340-4ffe-9664-c8fb9a8b058b', 'Towels', 'Plastic'),
('33aba782-b395-4b4d-a290-17e4cb84222e', 'Table', 'Granite'),
('4a22dece-7013-4048-b7f6-8e3c28044b1c', 'Shirt', 'Plastic'),
('7880cf08-ddd4-4a39-ba84-e118ebfe2f2f', 'Pizza', 'Frozen'),
('6ca28f4c-db19-4684-9977-07c771da627b', 'Chips', 'Concrete'),
('94b83632-711c-4970-9217-53c5c2d5b9d6', 'Bike', 'Frozen'),
('55fbd11b-d720-46cd-8a13-8b1430557062', 'Tuna', 'Steel'),
('afffd33a-f9ca-44b4-9b34-158e85ad8e47', 'Towels', 'Steel'),
('35e452fc-b530-4b98-ac9b-738f708f891e', 'Bacon', 'Concrete'),
('25adf1d0-24c9-4f82-a719-22e332e70f39', 'Soap', 'Metal'),
('a9e2c62f-a4f1-47bc-8739-5629834c47f1', 'Chicken', 'Concrete'),
('c62d567d-03d9-4336-9380-b31d80c7824a', 'Gloves', 'Concrete'),
('a9705870-8cb0-49f3-b9e0-8b5d67840e3a', 'Computer', 'Wooden'),
('c9410ea5-3a2c-4d7c-aa73-0edc6d8fa8c9', 'Hat', 'Wooden'),
('b3fa80cb-7e41-48e8-aa7d-79e785aa81c0', 'Fish', 'Metal'),
('4a0806c9-91d6-4a93-8693-ff960bf542da', 'Shirt', 'Frozen'),
('57f4050b-8a92-4b92-a25b-49645dc2d25e', 'Salad', 'Frozen'),
('73f5419f-6a06-456c-ac6e-61c85a59a70a', 'Computer', 'Bronze'),
('d6dc02d6-6470-43e5-866f-7f193f637096', 'Shirt', 'Cotton'),
('e51c9e8f-ba9c-493c-8396-c48ea66fe432', 'Tuna', 'Fresh'),
('2a3eef06-a7ea-4b19-a132-b2ef5320c8c5', 'Tuna', 'Plastic'),
('b4d4ce3b-b80a-461d-97ba-6455d3a5ebb8', 'Mouse', 'Steel'),
('b1966f6d-e73a-41f8-a503-3c0898f65b51', 'Gloves', 'Fresh'),
('78fd2d06-ce87-4215-b43d-8908cde3a280', 'Salad', 'Bronze'),
('8a2dd949-5409-40b7-bf47-1b6a60193926', 'Chips', 'Plastic'),
('d6fcaf97-497d-413a-af9f-0fb075dfa05d', 'Chips', 'Wooden'),
('6f1bc6a5-42eb-4382-bd98-071dd29c6360', 'Pants', 'Soft'),
('30e9b7fc-b3cf-4832-bda6-2a1f8a047d6a', 'Towels', 'Cotton'),
('49ed1197-a993-4290-a6cb-b63ee48c66b2', 'Towels', 'Concrete'),
('e0ab3d5e-82ac-4cfd-b7c8-e643505bdceb', 'Shirt', 'Granite'),
('80af043f-4f77-4964-bd7f-5a0223b2d48f', 'Soap', 'Fresh'),
('3435c304-df6c-480f-9008-c3e7aa31c468', 'Mouse', 'Bronze'),
('179d7ad5-1a45-403a-b124-0da982fbcb7b', 'Shoes', 'Plastic'),
('9fed8ed3-4040-43ca-b96d-352749fff79e', 'Soap', 'Fresh'),
('a980309a-31e9-4725-9a1c-24888fffb942', 'Shoes', 'Bronze'),
('62bd4ccf-db0a-4451-9d16-1be91ddef8f1', 'Soap', 'Steel'),
('93da9993-2c35-45f5-970e-f1283bdb74fd', 'Table', 'Wooden'),
('15ea071e-20f4-40ff-9985-8fe410c3092e', 'Shirt', 'Cotton'),
('68bbe424-60fe-498b-907e-b4f6bf1cdf3f', 'Towels', 'Soft'),
('47378d55-8a07-4005-b568-628d5cd1372d', 'Salad', 'Metal'),
('2c934569-4fd5-4440-9b4e-de7a840153f1', 'Pizza', 'Rubber'),
('a4aa4209-e5fa-4aa7-ab2f-49b40e4002c4', 'Sausages', 'Cotton'),
('7003ef5a-1ff0-4bd0-b42d-66990516be83', 'Table', 'Cotton'),
('3b66fd2a-bd5c-4f03-a85b-64d368177ed6', 'Salad', 'Cotton'),
('2e1f1d98-95e5-4a87-9377-e3d5b8b36bc8', 'Chips', 'Cotton'),
('2521fa22-58d7-46da-9301-9711b42f7395', 'Shoes', 'Bronze'),
('41ccb05a-d685-4810-946f-a2a297ca8179', 'Table', 'Bronze'),
('1fa3193d-d626-49b7-ad6d-e4703b513cc5', 'Fish', 'Frozen'),
('d283ec67-b652-4e0c-8762-499d31d13c4f', 'Soap', 'Soft'),
('c82c2a35-5aa7-4d8b-a3be-c9feae62f2b1', 'Cheese', 'Soft'),
('b9606376-1ddf-40e2-bc47-699e195c52cd', 'Shirt', 'Metal'),
('ccaabd0d-f2ee-4279-8f0a-e7d578f65b56', 'Fish', 'Plastic'),
('d235f5e6-56fb-448f-8d3a-575f31904570', 'Pizza', 'Metal'),
('fa4c660a-e677-47df-8f32-4a15e82a6cdc', 'Fish', 'Bronze'),
('a90eccf4-7037-4e82-968e-909bcfa046b9', 'Pizza', 'Fresh'),
('be7383ed-007d-4d86-a9f0-5a23155d3491', 'Mouse', 'Granite'),
('ecc72056-00d7-4fff-96bf-eb1726e163a5', 'Computer', 'Rubber'),
('b8faa0ae-9712-4f04-96bd-b15150721996', 'Ball', 'Steel'),
('38204bd7-cd65-4db7-aac2-d615a53a2220', 'Tuna', 'Rubber'),
('1726e325-e31e-433d-a8f4-29ef52c03c26', 'Chicken', 'Bronze'),
('43c6d61d-0d9a-4ad8-9134-71bb7678e1f2', 'Chair', 'Plastic'),
('5dc3961f-24a8-4063-92e3-5fea5a2eaec1', 'Chicken', 'Wooden'),
('c705de37-f2e5-4f88-9155-5024ce21fbe0', 'Fish', 'Rubber'),
('97f1edd1-6836-4e79-bcb0-14338569205c', 'Computer', 'Cotton'),
('1bc1a907-ccb4-445b-a9f8-9f3adfdf3d68', 'Chair', 'Fresh'),
('ad29d314-7bf3-4974-b7a8-1999308d7123', 'Salad', 'Fresh'),
('3ed09d88-74aa-4694-a405-7acd5ce06bf0', 'Soap', 'Fresh'),
('50f3bce7-ea7a-47c7-a47a-e405ff96f620', 'Ball', 'Bronze'),
('1caf0a90-4164-426d-a52d-d5a5b87fac6d', 'Soap', 'Plastic'),
('282e2ac5-d0df-452e-8cd0-f4ea59a61428', 'Computer', 'Concrete'),
('75c33623-cb6a-4e04-a37e-ce485f5eadc9', 'Pizza', 'Bronze'),
('1e6a5929-f427-46fa-82bd-2e5edfe182e8', 'Bacon', 'Cotton'),
('b418cbd8-e265-4b68-8fe5-97233a79de6e', 'Chips', 'Frozen'),
('9bcc28a6-96e1-47fd-ac85-3826654a41fb', 'Computer', 'Rubber'),
('c9700d21-8271-4e67-a618-146ddc8bac37', 'Ball', 'Bronze'),
('76534484-6aaa-4b6a-aec9-ebb87c9ae503', 'Ball', 'Plastic'),
('39023fc6-4cd1-41f9-8263-1d6b86ccca86', 'Cheese', 'Granite'),
('1bfba6dc-7229-420f-8989-df127760d94f', 'Chair', 'Fresh'),
('6416c6f7-92a2-4872-96bb-acc77c904712', 'Shoes', 'Bronze'),
('e6f381dd-dfa3-4d18-bf6a-80fa0484bab5', 'Cheese', 'Plastic'),
('02f2129e-1bce-4ae9-8e39-de436a3e9725', 'Tuna', 'Fresh'),
('0dad41d0-4a3a-47e1-bb12-9db648361808', 'Fish', 'Rubber'),
('ee58cb68-3962-4884-b17a-0271bf0ce9ed', 'Sausages', 'Concrete');
insert into auto_cqx values
('7c1c5e9d-bc82-4db3-a900-03a7afc3d0b3', 'XR69XND', '10', 'e0fb1b37-e427-4ae9-86ff-08b7e9771fac'),
('d5c404c1-f6d4-497c-9604-826b789e3648', 'CC83SWN', '5', '995d9a6c-0607-491f-8f45-26487c09ce7c'),
('1a3b54ae-d94f-41d7-bd01-dba1f438ac39', 'TZ87XBM', '20', 'a7f7b005-44bb-48d1-b0b2-08c288dae5e0'),
('da1fd8f2-7c2f-4b99-8a68-7ebfe3e9e681', 'LE30HQZ', '15', '5abf7cd0-ac2e-452b-a09b-6b03b668f731'),
('93faffda-7210-4d83-9ceb-55f49cd16da7', 'RC99KQL', '10', '466607b4-c552-4df5-91dc-0783f082b91c'),
('b3faa72e-d504-479a-8821-4956997caf05', 'PH08YEH', '5', '466607b4-c552-4df5-91dc-0783f082b91c'),
('8a9b4b5a-d2b5-4b23-b032-7b71014a1c03', 'IX46ABB', '5', '6a5726b8-b3a6-4d62-a966-42b748791252'),
('743f3272-0fc0-48d8-8c76-ec82ae0ce5a2', 'IT73FRM', '10', '59fc7ed7-e41e-44d0-a036-81ffd3533d28'),
('5aafbcf2-4ba1-4af6-91fd-a73a9cfa736e', 'XP45JGS', '20', '57e94ac6-493b-45c2-bc07-50ebad317335'),
('965f9628-0ed1-49ad-a9ea-399d323f1d9f', 'WU60VXU', '5', '946bd733-2ee8-49ab-999f-5554f9da1efa'),
('02cfebe4-af1b-4e2b-a98f-041f409440a9', 'RT61YDJ', '15', 'cacb0f42-f794-47f8-8379-6bb9a3572856'),
('9b486a2c-84e1-4e85-874e-8c5ba4ba649a', 'EH97IKR', '5', '673ec199-5fd4-44af-9b7e-133e4ed58729'),
('12b4ef94-e175-4536-ae91-6b57711b5f9e', 'XF10ICC', '20', '0501dd18-d910-44bd-aca2-366c4135eafe'),
('cd5bdb9c-9d20-48bf-8df4-69b63f0c2951', 'WD55POX', '5', '93a89120-df52-421c-afa4-16e59d4aac68'),
('0944b1ee-c7f9-4ef5-b416-1e431b7773d2', 'KY22BZD', '20', 'cacb0f42-f794-47f8-8379-6bb9a3572856'),
('51f71458-0502-4ea6-8f42-b0f157cd79e0', 'GE77IUC', '10', '4c072c90-fdbc-4574-9e5c-c5648be3402c'),
('43a538f9-410d-4af5-a163-13a644dbd810', 'XG30GXE', '30', '94c41384-7997-47f7-a504-4cf3b0eb1c9c'),
('514a489f-bd60-4421-ae6f-77b274112059', 'CZ73ILQ', '10', '4171ba79-0961-4b79-b149-a3f9f51fff0d'),
('3ec2003f-ccd8-4101-956b-cf79103f046a', 'HA19VTR', '5', '1f572ea7-c108-4020-9e29-b0f59b228ef1'),
('03f59daf-ceef-4da4-a393-da10dfcaf6d3', 'AZ89TZZ', '15', '466607b4-c552-4df5-91dc-0783f082b91c'),
('7a76d02f-8749-4658-a7e6-1e2ef0d310ff', 'KX45GCE', '15', '466607b4-c552-4df5-91dc-0783f082b91c'),
('58da06bc-17ce-4034-9349-216c8049d345', 'EH93RMJ', '15', '6a5726b8-b3a6-4d62-a966-42b748791252'),
('cc6be9a1-813a-4620-b9a8-aa351b4e6c93', 'YD94AMO', '20', '0cba2045-f4f3-4a9d-b24c-2618d6ab90c8'),
('f7b9a9a9-f02f-4f5c-866c-c388aff0b064', 'CQ24CRA', '20', 'b0ab3a3b-f045-47d5-8c2c-ec442ef7792c'),
('2552b8dc-e0fe-4762-aad3-57d5e6f994e4', 'CC08RGH', '20', '1d50a64c-6491-4556-94c5-a4b2617ac5f8'),
('deac6d7f-c340-4950-bc68-bfc9c51cd896', 'AQ43QSK', '20', '466607b4-c552-4df5-91dc-0783f082b91c'),
('e57bda93-47a3-4b55-b20e-a153b39e293d', 'ZX57UHO', '5', '1f572ea7-c108-4020-9e29-b0f59b228ef1'),
('0e37918d-c8e9-4801-a0d3-78fbccaca8a1', 'PU81GNO', '10', '4171ba79-0961-4b79-b149-a3f9f51fff0d'),
('44055af3-c39c-4a3b-a7f2-c13777b05220', 'OO73XLC', '10', '673ec199-5fd4-44af-9b7e-133e4ed58729'),
('29e6267a-95c3-402a-9786-ba7452ba83bd', 'UF74KXU', '30', '995d9a6c-0607-491f-8f45-26487c09ce7c'),
('a27676f4-77fe-43d5-aee3-6e7b0df5f83d', 'IF62WGO', '20', '673ec199-5fd4-44af-9b7e-133e4ed58729'),
('725e65ec-4467-4fb0-aa76-570950f8025d', 'JM36VUM', '10', 'cacb0f42-f794-47f8-8379-6bb9a3572856'),
('812c1c99-a035-4e8a-bd7e-771160910d4c', 'OQ38YXZ', '5', '93a89120-df52-421c-afa4-16e59d4aac68'),
('9600fae5-90a8-479a-b58c-8cd9ad967106', 'VB58ZJH', '5', '673ec199-5fd4-44af-9b7e-133e4ed58729'),
('1af8f146-4115-4fb2-a1a1-568d92a592a5', 'QR92OCM', '10', '466607b4-c552-4df5-91dc-0783f082b91c'),
('e5f5fe39-b76b-4b74-9e80-a3e718017d14', 'PK17YQZ', '5', '946bd733-2ee8-49ab-999f-5554f9da1efa'),
('17f23221-db25-4169-af6d-c57a7b232f77', 'AY04IGK', '30', '6e675933-3fd1-4492-a6c2-6797d79bb811'),
('81db8ff3-47d2-4b15-a245-b0546115edf1', 'RP97XJF', '30', '995d9a6c-0607-491f-8f45-26487c09ce7c'),
('6d262be4-cf61-4609-9430-27c182672390', 'YE02FPN', '10', '57e94ac6-493b-45c2-bc07-50ebad317335'),
('858c875c-15d3-4aea-8699-d647150f7b4e', 'QG95AET', '5', '8d1b3608-4182-41ac-a281-9edc5873fd4a'),
('aa96396c-4dcf-46c8-8992-ea9335d2de09', 'AX31USY', '15', '1f572ea7-c108-4020-9e29-b0f59b228ef1'),
('fa29635b-68f6-4a7f-91bc-c088433a50df', 'VV57ZDZ', '30', '6dbf5aa8-8f59-4fd3-8712-07ef2aa6641c'),
('7831bfcf-a8be-4da6-9e69-5e5c3bcd8277', 'NT10SRS', '5', '94c41384-7997-47f7-a504-4cf3b0eb1c9c'),
('8bf3e999-d8a9-4192-8959-1895d6508c0d', 'QU32IVH', '15', 'cacb0f42-f794-47f8-8379-6bb9a3572856'),
('320541ed-736e-40b4-bc54-64c7659fdaeb', 'JX47XFT', '15', 'cacb0f42-f794-47f8-8379-6bb9a3572856'),
('47360461-3c73-48d3-b2bd-34c3af5db465', 'LN32HIX', '30', '4c072c90-fdbc-4574-9e5c-c5648be3402c'),
('78f93483-3def-4e8b-8c1e-8fdcf266de7b', 'TJ12SNG', '20', '6a17fa75-1f51-4cec-8ad5-9d1a2b091ef9'),
('a8c37ffe-8a08-41b6-a4ce-e56aac2377f4', 'TO89CIA', '5', '4171ba79-0961-4b79-b149-a3f9f51fff0d'),
('467d7bca-d39a-48b8-b96e-7a31833e9f0f', 'UH20RLW', '5', 'b0ab3a3b-f045-47d5-8c2c-ec442ef7792c'),
('d95825f0-f77f-4613-88ec-6f11da9cbff2', 'AI57LJU', '5', '466607b4-c552-4df5-91dc-0783f082b91c'),
('983ebdbb-e544-4c44-a2cd-3ced7c69fcab', 'NJ66VNP', '15', '6dbf5aa8-8f59-4fd3-8712-07ef2aa6641c'),
('a6862212-ad3e-4147-970b-c18a5f4b8422', 'ZZ75VUE', '15', '6e675933-3fd1-4492-a6c2-6797d79bb811'),
('3267a30d-7c15-4c4e-83c8-6c2b1a2e3235', 'HT87WAP', '30', '59fc7ed7-e41e-44d0-a036-81ffd3533d28'),
('02a38131-70e0-45c6-9a62-4407ab99c707', 'SG56QFO', '30', '4c072c90-fdbc-4574-9e5c-c5648be3402c'),
('dd156e31-3333-45c0-8729-dc7ddc537a04', 'IS87HFT', '10', '4c072c90-fdbc-4574-9e5c-c5648be3402c'),
('b05cfb7a-07f3-456b-8374-77788d92008e', 'FC42OHW', '5', '4c072c90-fdbc-4574-9e5c-c5648be3402c'),
('e927a58e-d783-462f-b838-7392a37978a8', 'HZ50VVB', '15', 'e0fb1b37-e427-4ae9-86ff-08b7e9771fac'),
('7a574572-4a1e-4039-b85f-1a5f1aad9fc4', 'VY20MKE', '10', '4171ba79-0961-4b79-b149-a3f9f51fff0d'),
('b135f0b9-843a-4cb3-b7bc-dcb7ea9530a1', 'UE27VBF', '10', 'b0ab3a3b-f045-47d5-8c2c-ec442ef7792c'),
('42532666-262f-4668-a6b1-8a0e81dcf1f1', 'HD25NFF', '15', '946bd733-2ee8-49ab-999f-5554f9da1efa'),
('e4c330f9-830f-47a6-9e90-96708ebbd54a', 'IT65AWF', '5', '946bd733-2ee8-49ab-999f-5554f9da1efa'),
('9d81d478-1521-436f-90d2-c6b19b647bd0', 'SC77VCR', '15', '94c41384-7997-47f7-a504-4cf3b0eb1c9c'),
('41ae0f5d-75c8-49b2-9297-e862912f03c3', 'NF75VYT', '15', '7482dd96-6504-47fc-87f4-acb4bd3058e2'),
('43a2179b-bd5d-43b2-8750-1deacf154e09', 'LA86FWV', '5', 'b0ab3a3b-f045-47d5-8c2c-ec442ef7792c'),
('1fd40a5f-791a-4dd6-a187-069c9cede80b', 'PM41CRX', '30', '94c41384-7997-47f7-a504-4cf3b0eb1c9c'),
('9945bdc9-f26c-415d-bf79-9c46155a8add', 'CT38JDI', '30', 'd7567367-c81e-45a3-b04c-0af8785f4f49'),
('6d1e7016-e5dc-44b4-bcd5-ef6542fda1e4', 'PO15PSO', '10', '946bd733-2ee8-49ab-999f-5554f9da1efa'),
('12ab7e2f-cb04-4492-b386-9904bd3fefbe', 'TB56NCC', '15', '4c072c90-fdbc-4574-9e5c-c5648be3402c'),
('38b6f432-5d19-48fb-8e50-17fd27bed9fa', 'OJ54VQH', '15', '73954c88-3a51-4bdd-aebb-6071daafe9b7'),
('c021a84f-ba00-487f-8b14-c1d5791a7a63', 'ZD44DLB', '15', '94c41384-7997-47f7-a504-4cf3b0eb1c9c'),
('134a8a12-643a-4093-9da7-f545f8a05c77', 'JI26ACO', '20', '995d9a6c-0607-491f-8f45-26487c09ce7c'),
('3d02c447-4e68-439f-b75c-65e918b414a0', 'QD68DXD', '15', '1d50a64c-6491-4556-94c5-a4b2617ac5f8'),
('dc18ef4c-66df-4002-9592-60b1035a1ba4', 'JF08TMT', '10', '6a17fa75-1f51-4cec-8ad5-9d1a2b091ef9'),
('d6136296-f928-433c-b8e8-0d0b8587933f', 'OR25TDF', '30', '945f2580-fc51-466c-8c16-e4e6816b6296'),
('96f6b1fb-04fa-4ed6-b8bc-d958e9309bc5', 'MO11MWI', '30', '946bd733-2ee8-49ab-999f-5554f9da1efa'),
('f7ad6a14-e56c-45f4-9dfc-1543041e9ef0', 'CZ63WMD', '10', 'e0fb1b37-e427-4ae9-86ff-08b7e9771fac'),
('51192526-9ac3-4d87-b0df-7e06b084e4dc', 'AK79NJZ', '10', '466607b4-c552-4df5-91dc-0783f082b91c'),
('f9a739f8-da8c-4142-8bb6-f66a91123960', 'SJ74FSF', '30', '94c41384-7997-47f7-a504-4cf3b0eb1c9c'),
('246fef3b-6d09-4563-86b9-464f1c605de4', 'HP77CFL', '20', 'e0fb1b37-e427-4ae9-86ff-08b7e9771fac'),
('5f41d492-2967-4d51-a09d-56b9f9f7e388', 'BT46TTY', '20', '8d1b3608-4182-41ac-a281-9edc5873fd4a'),
('2d62b2c5-69f8-4c26-b47b-46604670c1a5', 'FO75VIB', '20', '0cba2045-f4f3-4a9d-b24c-2618d6ab90c8'),
('bef256b4-0683-4ffe-bde4-fa091088acd6', 'MO78AFL', '15', '1f572ea7-c108-4020-9e29-b0f59b228ef1'),
('2e64132a-9691-4656-a6a9-426be9328323', 'VZ17IUU', '10', '5abf7cd0-ac2e-452b-a09b-6b03b668f731'),
('1ec87d41-13dc-408f-a197-d059d5288922', 'YE86PGH', '10', '94c41384-7997-47f7-a504-4cf3b0eb1c9c'),
('23530752-5a79-46b5-94df-640963eadf26', 'TB53IDG', '20', '57e94ac6-493b-45c2-bc07-50ebad317335'),
('f8b5b2f8-8496-46a5-bbbf-f5f2d1c48cea', 'VN93MHO', '30', 'cacb0f42-f794-47f8-8379-6bb9a3572856'),
('f572d3c0-dac8-4b26-9aca-8b5d6465e63d', 'ZS83LXV', '30', '57e94ac6-493b-45c2-bc07-50ebad317335'),
('5ff9ac6c-6f4c-42af-9a2c-3bb73aeae8f1', 'VG25KHO', '20', '1d50a64c-6491-4556-94c5-a4b2617ac5f8'),
('cd9858af-597c-4878-8e68-23fe164434f8', 'HR84CNJ', '30', '946bd733-2ee8-49ab-999f-5554f9da1efa'),
('084b9154-be1e-44b9-b1a0-03776f886f03', 'KJ30TTM', '20', '0501dd18-d910-44bd-aca2-366c4135eafe'),
('b2585b5d-0082-4c36-9406-a27d80012173', 'NX95JKV', '20', '57e94ac6-493b-45c2-bc07-50ebad317335'),
('bdf72159-c8a3-429d-835d-6381f3680816', 'ZA06VTO', '10', '93a89120-df52-421c-afa4-16e59d4aac68'),
('4de53732-9cb2-4e67-a829-2a9156cca65f', 'JL30QSB', '30', '7482dd96-6504-47fc-87f4-acb4bd3058e2'),
('734023a6-b518-4dd1-ad3d-2f7718767896', 'EG57BWP', '5', '6e675933-3fd1-4492-a6c2-6797d79bb811'),
('87a48751-75c0-4305-b289-72b4d05cfffc', 'BK92QQG', '30', '8d1b3608-4182-41ac-a281-9edc5873fd4a'),
('e79407da-d846-4902-963b-edd09add47d3', 'QM09EKU', '10', '1f572ea7-c108-4020-9e29-b0f59b228ef1'),
('eb02e494-cb70-42f1-bc5a-8d24b27752b6', 'OA88MLQ', '15', '57e94ac6-493b-45c2-bc07-50ebad317335'),
('391d0e25-1a79-400d-b201-a686650e4d69', 'KZ41BVT', '15', '0cba2045-f4f3-4a9d-b24c-2618d6ab90c8'),
('b2fdd66e-4c0f-4c44-84ce-8d43f83a6dd5', 'FZ98LVG', '20', '57e94ac6-493b-45c2-bc07-50ebad317335'),
('09a66985-4471-466e-98a9-924886336438', 'NB96PAI', '20', '466607b4-c552-4df5-91dc-0783f082b91c');
insert into provider_goods_info_i4L
select goods_YBH.id, provider_KEC.id, random() * 1000, random() * 10000
from goods_YBH,
provider_KEC
where random() < 0.3;
insert into provider_KEC values
('74d1ef58-e2b7-4b6f-8549-6b90fac85315', 'Dach, Parisian and Hodkiewicz', '251 Wilkinson Rue', '546.416.1886 x9606', 'Kali.Ankunding@hotmail.com');
#1
WITH vehicle_count AS (
SELECT provider_id, COUNT(*) AS num_vehicles
FROM auto_cqx
GROUP BY provider_id
), max_vehicles AS (
SELECT provider_id
FROM vehicle_count
ORDER BY num_vehicles DESC
LIMIT 1
), total_products AS (
SELECT provider_id, SUM(quantity) AS product_count
FROM provider_goods_info_i4L
GROUP BY provider_id
)
SELECT p.name, t.product_count
FROM provider_KEC p
JOIN max_vehicles m ON p.id = m.provider_id
JOIN total_products t ON p.id = t.provider_id;
#2
WITH total_cost AS (
SELECT provider_id, SUM(price * quantity) AS cost
FROM provider_goods_info_i4L
GROUP BY provider_id
), load_capacity_agg AS (
SELECT provider_id, SUM(load_capacity) AS total_capacity
FROM auto_cqx
GROUP BY provider_id
), vehicle_count AS (
SELECT provider_id, COUNT(*) AS num_vehicles
FROM auto_cqx
GROUP BY provider_id
)
SELECT p.name, v.num_vehicles
FROM provider_KEC p
JOIN total_cost c ON p.id = c.provider_id
JOIN load_capacity_agg l ON p.id = l.provider_id
JOIN vehicle_count v ON p.id = v.provider_id
WHERE (c.cost BETWEEN 100000000 AND 120000000) OR (l.total_capacity BETWEEN 70 AND 80);
#3
WITH vehicle_count AS (
SELECT provider_id, COUNT(*) AS num_vehicles
FROM auto_cqx
GROUP BY provider_id
), total_products AS (
SELECT provider_id, SUM(quantity) AS product_count
FROM provider_goods_info_i4L
GROUP BY provider_id
), max_price AS (
SELECT provider_id, MAX(price) AS highest_price
FROM provider_goods_info_i4L
GROUP BY provider_id
)
SELECT p.name, m.highest_price
FROM provider_KEC p
JOIN vehicle_count v ON p.id = v.provider_id
JOIN total_products t ON p.id = t.provider_id
JOIN max_price m ON p.id = m.provider_id
WHERE v.num_vehicles <= 10 AND t.product_count >= 10000;
Editor is loading...