Untitled
unknown
pgsql
2 years ago
18 kB
3
Indexable
/*
1. Вывести названия продуктов (без повторений) из бронзы или гранита и ценой от 3_000 до 7_000
2. Вывести названия компаний, содержащие в названии цифры.
3. Вывести названия компаний (без повторений), поставляющие компьютеры, но не клавиатуры.
4. Вывести названия компаний (без повторений), поставляющие как машины, так и мотоциклы.
5. Для компании, имеющей максимальную суммарную грузоподъемность, вывести минимальную цену продуктов.
6. Для каждой компании, имеющей суммарную грузоподъемность транспортых средств более 100, вывести количество продуктов компании.
7. Вывести названия компаний, имеющих товаров на сумму не менее 1_000_000_000 или не менее 10 единиц транспорта.
*/
drop table if exists supplier_3x4, goods_zpu, supplier_goods_info_drl, transport_l6p cascade;
create table supplier_3x4
(
id uuid primary key,
name text,
address text,
phone text
);
create table goods_zpu
(
id uuid primary key,
name text,
material text
);
create table supplier_goods_info_drl
(
goods_id uuid references goods_zpu,
supplier_id uuid references supplier_3x4,
primary key (goods_id, supplier_id),
price numeric,
quantity integer
);
create table transport_l6p
(
id uuid primary key,
registration_mark text,
load_capacity integer,
supplier_id uuid references supplier_3x4
);
insert into supplier_3x4 values
('49955aa5-8da1-477b-8218-8334407e3f10', 'Schinner, Breitenberg and Lubowitz', '13144 Koch Inlet', '(771) 836-9161 x54136'),
('d67fdff0-330a-4d33-b690-6a1c9032a321', 'Bartell - Zboncak', '2372 Samara Courts', '296.584.0861 x26094'),
('6d9df098-d227-4d20-a8d1-4611a2b33374', 'Kunde, Reinger and Funk', '54111 Richie Centers', '869-865-0506 x962'),
('310c500a-aeee-42c4-a224-97b2ee916bcc', 'Keebler and Sons', '225 Devonte View', '439-857-6131 x3259'),
('6126f5d5-7210-4e50-9fa8-b7772c1392e1', 'Wisoky - Gorczany', '1143 Monserrate Skyway', '905-401-4637 x92353'),
('baa10bd0-86f4-43d4-b0e3-bec9385fc0cb', 'Botsford and Sons', '26039 Hilpert Point', '(567) 631-6404 x789'),
('4b9d8d45-b1ab-471e-abc4-fcdd7bece55f', 'Cole and Sons', '902 Gulgowski Valleys', '1-359-774-8052 x3416'),
('24949655-7d16-481c-b53e-8bc80a01dbb0', 'Strosin - Turner', '8137 Zackary Bridge', '442.235.2708'),
('9f9451de-9ab1-4a0f-b268-0f32a908e787', 'Purdy, McLaughlin and Hermann', '113 Helen Mill', '(470) 746-2060'),
('8c108505-560f-4561-9c28-6c181f4e78b8', 'Pfeffer, Becker and Greenfelder', '0619 Morar Valleys', '569-612-4047 x9642'),
('c86f3e1e-461c-4b2c-954a-7e3eb522b9d9', 'Lebsack, Conroy and Kshlerin', '6709 Corine Estates', '1-908-392-7110 x21768'),
('4ce64d82-5df0-49c5-b718-44d349aff853', 'Bailey, Watsica and Okuneva', '0363 Makenna Squares', '652-770-9023 x961'),
('89ff78a7-3058-403e-b0f7-2295e90ad959', 'Fahey - Orn', '23983 Henri Gateway', '206-825-6292'),
('87b015b4-34f6-4fc1-acdc-1658cadd69e4', 'Rippin LLC', '211 Daugherty Shores', '(889) 334-1419 x356'),
('5e220679-d062-4a46-ab41-9ad8dff08b6d', 'Frami - Feest', '124 Olson Dam', '512.515.8317 x659'),
('726af242-bd12-42fa-8506-11d0e318f1af', 'Rippin - O''Connell', '572 Anderson Cape', '760.986.4207 x620'),
('e901e658-7347-4e40-ac56-5883f9b0a863', 'Lowe, Collier and Konopelski', '9098 Chaim Spurs', '836-625-8350'),
('19321809-beaa-485b-813c-cd99ee269152', 'Sauer, Satterfield and Greenfelder', '93556 Emiliano Coves', '1-531-354-9203 x43435'),
('6f2eac8d-f294-455a-8f02-621ca97ff3fa', 'Fritsch Inc', '361 Hagenes Loaf', '565.595.1662 x710'),
('dc9af8a2-cd81-4ccc-898f-e4511f090205', 'Kihn - Hodkiewicz', '74858 Dach Valleys', '1-515-214-8049');
insert into goods_zpu values
('86bc1fe6-281f-41ec-a2af-7ce840d8db47', 'Fish', 'Fresh'),
('216047d7-b63d-415f-956f-417287b4b66b', 'Sausages', 'Bronze'),
('dfc856d5-951d-4742-92a6-4fd95442a7bb', 'Chair', 'Bronze'),
('fe627f5f-34ee-4e9a-b60e-aa663fd99492', 'Chair', 'Fresh'),
('096e212d-f0d1-4352-a7e1-ad73289183aa', 'Tuna', 'Bronze'),
('78599277-f18c-4e58-a566-5d3c3a2cc4d6', 'Salad', 'Granite'),
('287fa6d2-e0c4-43ed-8a6b-a4acd9a6d4c6', 'Mouse', 'Metal'),
('5ddc382e-e07b-45fd-93a6-7e0040317a13', 'Table', 'Metal'),
('6d097b72-3018-4dcd-a42a-800eeaef8523', 'Bike', 'Fresh'),
('b2ec5a5e-8a2f-44ea-98d0-15bdf76a93ca', 'Tuna', 'Wooden'),
('17b14002-3f49-4a06-b0d1-c525aeba3e8d', 'Shoes', 'Plastic'),
('89bd1cf7-95d7-4a76-97ac-52fd19d90dcf', 'Hat', 'Granite'),
('cd0dde4d-0c41-42c2-95d9-351b99024f0a', 'Shoes', 'Plastic'),
('33c3aa0e-4cc1-40d4-bd28-0de8af6a7950', 'Bacon', 'Bronze'),
('f145c2a2-3183-4427-9aba-e6697ebedbae', 'Car', 'Granite'),
('fbd3355d-b894-40e6-bb68-20f60ddfd5ae', 'Fish', 'Fresh'),
('47b7030c-fe53-49ae-a4ce-dcc4077a602c', 'Pizza', 'Granite'),
('c229694e-df9c-48ae-8f8a-aeb345e9b432', 'Fish', 'Cotton'),
('d641d997-999b-4e5a-bc2c-2e38c000631f', 'Hat', 'Soft'),
('3c6c3e6b-746e-4ec4-bd12-589f312f68aa', 'Bike', 'Wooden'),
('24d56184-7371-434f-8325-c454d1d5cf03', 'Hat', 'Plastic'),
('62840b4a-d21f-41b4-9e02-64d0c0283a52', 'Shoes', 'Cotton'),
('6adafcd7-594d-454b-b2e0-fccf5f1219fe', 'Pizza', 'Frozen'),
('18be35c2-316c-47f8-9831-7f591ec0ef78', 'Bacon', 'Frozen'),
('dfa6af06-2b4f-4069-9dd3-101efd5f30a5', 'Towels', 'Frozen'),
('b623589a-5f4e-4094-a1eb-ec72f0b4c58b', 'Soap', 'Fresh'),
('447813d5-6a23-4834-89b8-3d4740a9b110', 'Bike', 'Cotton'),
('15f8a362-605f-4a02-be58-6ab0b5360639', 'Chair', 'Cotton'),
('90bad7a3-8efa-4ef6-a2bb-bef62dc9eeb8', 'Chips', 'Steel'),
('b6ebadcb-8f06-4a50-8525-7b0fa4766162', 'Pants', 'Plastic'),
('dcb86870-6961-4785-8a4d-e88f19cadc6d', 'Towels', 'Granite'),
('66a78c27-3a91-4fd3-acd4-817cce635e50', 'Keyboard', 'Bronze'),
('a1871a34-2475-4bcb-b997-76b0e7d3a43f', 'Shoes', 'Granite'),
('6db72cf4-4758-493b-8662-87a80f3152be', 'Soap', 'Soft'),
('a4ecd866-dac0-4645-90e3-41ce69202206', 'Car', 'Steel'),
('b79ad998-3c9a-4c41-950f-38810d269bbd', 'Hat', 'Metal'),
('89093d1c-6b0e-4ed6-bfa5-cfdc860ed62e', 'Chips', 'Concrete'),
('b1c3823b-4b0e-4e05-941e-5aaed2656fb6', 'Mouse', 'Metal'),
('4ec2d639-0698-48c2-877c-3d11cac30fc5', 'Soap', 'Metal'),
('8a8419b9-a92d-4c71-9686-77aac4d8a3e8', 'Fish', 'Steel'),
('cef1dcde-c4fa-4d14-b8ee-9eb48d477bc8', 'Chair', 'Cotton'),
('6a256d41-e2c7-4b97-b863-d31989dffb53', 'Chicken', 'Plastic'),
('c5d8e516-7d96-41f5-a5ac-8da834131ff5', 'Fish', 'Fresh'),
('b6fcab64-05ae-4eeb-af96-c48f2d130611', 'Shirt', 'Rubber'),
('a3c35f40-0fe8-420e-8bb9-afe28aaf162d', 'Salad', 'Bronze'),
('15a5d624-a194-422e-8663-4c6c13c4bb59', 'Pants', 'Soft'),
('3c2a371f-5d55-4b33-b42e-11f0ecf45a30', 'Chips', 'Frozen'),
('69df3f4e-d21d-4a33-825a-e53ab426eea5', 'Fish', 'Soft'),
('b97b2c33-a6ba-4756-8314-288142853090', 'Pizza', 'Wooden'),
('f04bd00f-4693-4f40-b66d-57d0ae261014', 'Hat', 'Soft'),
('1a6dca03-f7fa-4091-84d2-84b097469320', 'Table', 'Soft'),
('d5319b78-eeb4-48b3-8e99-01f37c242c13', 'Bacon', 'Plastic'),
('fe1890e2-ea2c-4367-9269-0007989a9487', 'Fish', 'Fresh'),
('f819894c-0f87-4f72-8039-f1b39df069f7', 'Towels', 'Concrete'),
('0681341d-32c4-4a0a-b005-1e768d13b1cf', 'Car', 'Wooden'),
('3045a295-222c-4454-9e75-56ec76ca443f', 'Salad', 'Fresh'),
('8d529847-9ba8-4bd5-aa7f-cae148aedaec', 'Shoes', 'Frozen'),
('0293adcc-c033-409f-b48b-c67f08268fb5', 'Sausages', 'Fresh'),
('b8337309-cf8d-4478-abcf-324b5e05306f', 'Keyboard', 'Frozen'),
('33c9b334-7333-4a78-8ba9-fbfb13d481c3', 'Soap', 'Bronze'),
('575145ab-4137-495b-9d72-5cf1ccdb302c', 'Shoes', 'Metal'),
('acba15f2-49be-42a0-b3f6-47277a915c87', 'Table', 'Granite'),
('cad826a6-cb11-4fe1-a7ab-14e247123eed', 'Table', 'Rubber'),
('1c197569-175d-4b70-a0a2-7fc9fa2a7b22', 'Pizza', 'Rubber'),
('c212d2cf-9adf-4fb5-a6de-978199f330dc', 'Fish', 'Soft'),
('20291f0b-dde3-490d-85ff-3e0f1ba8aa92', 'Car', 'Concrete'),
('e6b6be7b-7d66-40a4-b318-356c0ee98ee2', 'Soap', 'Bronze'),
('41e3f158-6452-4406-9ddc-c68e56e0fea6', 'Pants', 'Metal'),
('f8c99896-2af8-4ae1-9696-b51830b0e4bf', 'Mouse', 'Concrete'),
('d8cac161-6248-492e-8540-95f6a98d40ab', 'Chair', 'Rubber'),
('c986a92e-4927-4e15-ad94-ab76889112e5', 'Pizza', 'Metal'),
('b3cc5d23-e81f-44e9-814a-7a0e9cabb8f4', 'Pants', 'Bronze'),
('3c97e97b-f37a-4d5c-b864-644be1185ffc', 'Towels', 'Cotton'),
('f028e4e7-09e8-4aa2-b482-fe64738dd7f2', 'Mouse', 'Metal'),
('855264d8-6975-443a-b98a-f6957de90123', 'Sausages', 'Fresh'),
('67a36c19-b1b8-4c10-8a05-28eedf2bafca', 'Chips', 'Frozen'),
('c4c08960-afd0-48f8-978c-6efbd2f5bf95', 'Tuna', 'Frozen'),
('bb6dfc58-83ee-467b-a784-6a6f0f29f54b', 'Pizza', 'Concrete'),
('664b3798-4b00-4dd2-b3f3-bfc078d62ac3', 'Pizza', 'Bronze'),
('c998d58a-7cb0-4cf6-9151-0d9efb2585fd', 'Bike', 'Fresh'),
('99ad5994-4adb-4dfc-9f36-427fe5e285ea', 'Cheese', 'Frozen'),
('02505744-faf7-4bd0-a9fb-4a9a09dc42fc', 'Chips', 'Plastic'),
('1fd5cb8c-b2bc-4b78-820a-da7970b98823', 'Keyboard', 'Plastic'),
('6b671500-052a-41d9-a9b4-fe90062e4896', 'Shirt', 'Rubber'),
('f121e2b8-84bb-44ac-ad70-7240599abef6', 'Chips', 'Cotton'),
('d1573538-c6a1-4936-a73c-97b11a2797e8', 'Chair', 'Soft'),
('0294b1d7-e9f9-4b24-bdac-4b2c003d2b76', 'Salad', 'Plastic'),
('93b05570-f96e-4fc9-9c4b-7136d5784398', 'Chicken', 'Wooden'),
('2a9c46f2-15f5-4c3c-aa57-eee2a361374f', 'Hat', 'Concrete'),
('cbff44d2-ea9f-493c-a436-6eff910a0873', 'Table', 'Concrete'),
('8a64347b-6fb7-43f8-ab9e-9a0102301ab4', 'Salad', 'Fresh'),
('1a3bfb6b-94c6-4f9a-8aca-9f4ed7f98e48', 'Sausages', 'Soft'),
('96133494-7939-4dde-9d95-90f58a50efbb', 'Computer', 'Rubber'),
('ad56bb6e-c183-45e6-9889-f8d33be4dde5', 'Ball', 'Plastic'),
('e89bed39-67fc-433c-aa1a-bcbd023ea05d', 'Shirt', 'Soft'),
('00ca880c-b905-422c-a69c-1f739b888ed7', 'Bike', 'Steel'),
('2945da8a-067f-4c47-a4b8-e675bbb875c2', 'Mouse', 'Soft'),
('368dbfad-98b0-4e63-8d77-f632a2c7f626', 'Fish', 'Steel'),
('a09fb2ba-d938-422e-a8f6-7f8b663e96bd', 'Chips', 'Plastic'),
('1b4d2c1a-a27c-4900-9433-d807ba6b85ae', 'Chips', 'Concrete');
insert into transport_l6p values
('27266337-af31-4b7e-872b-6638be36cd2f', 'NG60RMJ', '20', '49955aa5-8da1-477b-8218-8334407e3f10'),
('fd2a8ab2-bb1e-4f1b-87d8-e26b2d5fc595', 'HK57AAQ', '20', '87b015b4-34f6-4fc1-acdc-1658cadd69e4'),
('b0657b7c-8bd8-4954-a109-a486a6657805', 'XL57YST', '5', '6f2eac8d-f294-455a-8f02-621ca97ff3fa'),
('db7b1d88-a579-480b-bbe7-428991f0d12e', 'OO99BRW', '15', '5e220679-d062-4a46-ab41-9ad8dff08b6d'),
('44744456-7842-42bd-ad67-8c67f73e6a6e', 'GQ04KMB', '5', '89ff78a7-3058-403e-b0f7-2295e90ad959'),
('845a5d83-ac7c-4efe-845c-26775073f58c', 'YO73XTH', '20', '49955aa5-8da1-477b-8218-8334407e3f10'),
('63429ecb-5030-469d-8fb4-210fe96a7fbc', 'JU58UEQ', '20', '310c500a-aeee-42c4-a224-97b2ee916bcc'),
('93eaeb83-8428-40cd-aa95-ba39c9130bf9', 'YB33PKJ', '15', 'c86f3e1e-461c-4b2c-954a-7e3eb522b9d9'),
('4f83acd0-3eb2-47af-9114-dea3f213453b', 'UC94PDC', '5', '87b015b4-34f6-4fc1-acdc-1658cadd69e4'),
('6881ac0d-4d91-46f5-8a1c-6008b349e1b5', 'CN04NLA', '20', '4b9d8d45-b1ab-471e-abc4-fcdd7bece55f'),
('2acddc00-dc84-463f-ba09-4cc198adef33', 'HW91GGT', '30', '4b9d8d45-b1ab-471e-abc4-fcdd7bece55f'),
('239d1278-976b-45be-aeff-a90068061e1a', 'DG94SNL', '30', '6126f5d5-7210-4e50-9fa8-b7772c1392e1'),
('f14d8e23-23e7-4798-b636-d0a7a5bbc4f8', 'EX36QKK', '5', '89ff78a7-3058-403e-b0f7-2295e90ad959'),
('d080b8cb-cfad-4e9a-af3f-ebc5ec479589', 'NZ22WHD', '15', 'c86f3e1e-461c-4b2c-954a-7e3eb522b9d9'),
('7f5a5833-7b3a-4d21-a42b-c5fcb97ef950', 'TZ16PYR', '20', 'e901e658-7347-4e40-ac56-5883f9b0a863'),
('058fa28f-e2cb-4cc8-b9fc-63d40e7bcec6', 'EN73HKR', '30', 'dc9af8a2-cd81-4ccc-898f-e4511f090205'),
('d1c34338-8cf0-4556-b4d4-99610087e12d', 'DW48PMQ', '30', '4b9d8d45-b1ab-471e-abc4-fcdd7bece55f'),
('7ffd258d-0159-4697-b6a6-fcd030b0d610', 'WR48JNN', '10', 'dc9af8a2-cd81-4ccc-898f-e4511f090205'),
('f1e0327d-2716-466c-9f67-3be43361388d', 'WA35MNU', '20', 'baa10bd0-86f4-43d4-b0e3-bec9385fc0cb'),
('a0a9f150-c15e-4d0d-b09d-c34f15edfeb4', 'BL45LXP', '15', '6126f5d5-7210-4e50-9fa8-b7772c1392e1'),
('f7410c22-5cc8-42ff-8ff0-3477bf65a5a9', 'AD25WCK', '10', '24949655-7d16-481c-b53e-8bc80a01dbb0'),
('0738a84d-2e64-496f-b164-20f3cfdbfc1a', 'JX09IGK', '20', '5e220679-d062-4a46-ab41-9ad8dff08b6d'),
('74e619cb-9ca3-45b0-a544-e5159ca542b9', 'WW46HJE', '20', 'c86f3e1e-461c-4b2c-954a-7e3eb522b9d9'),
('ba7c3ce1-e92b-461a-b5e4-85b34ff64bc1', 'IP93DJF', '10', '49955aa5-8da1-477b-8218-8334407e3f10'),
('1402bd44-1f55-49aa-a5f8-481a9e74d252', 'NC38QRH', '15', '89ff78a7-3058-403e-b0f7-2295e90ad959'),
('46f985a0-119d-4d18-abdb-0e2c01a7ba99', 'RT21MWY', '10', 'e901e658-7347-4e40-ac56-5883f9b0a863'),
('8dd5da28-93e7-4906-b967-3945c714c007', 'SX59VKF', '20', '4ce64d82-5df0-49c5-b718-44d349aff853'),
('e8828d05-81a7-4d57-a6ab-5719211638df', 'UD25SCG', '5', 'baa10bd0-86f4-43d4-b0e3-bec9385fc0cb'),
('bc768aec-504a-495f-b3af-46a260ffe876', 'EB90MNW', '15', '87b015b4-34f6-4fc1-acdc-1658cadd69e4'),
('a6d3426f-c6eb-4bbd-b9fe-e227ff15236f', 'QA91EBY', '30', '6d9df098-d227-4d20-a8d1-4611a2b33374'),
('8581b584-c6f5-4d28-817f-21826738fdac', 'LU24MOP', '5', '9f9451de-9ab1-4a0f-b268-0f32a908e787'),
('30639994-4626-4d8c-a17e-67f13d6e5874', 'EE52QJV', '10', '5e220679-d062-4a46-ab41-9ad8dff08b6d'),
('66daa7e5-8c02-40fc-853f-4d7ec52fd3dd', 'UW00KYX', '5', '8c108505-560f-4561-9c28-6c181f4e78b8'),
('cbadb321-8b14-4999-b76a-44c79eafb6d5', 'FL91NQC', '15', '49955aa5-8da1-477b-8218-8334407e3f10'),
('90512052-6949-4912-b657-f59dc7e02b15', 'ZH22QDU', '15', '310c500a-aeee-42c4-a224-97b2ee916bcc'),
('d498e988-5318-4085-9546-41352247d1ed', 'EZ25XPL', '10', '87b015b4-34f6-4fc1-acdc-1658cadd69e4'),
('3185445b-6947-4a88-91e9-c080bc23e1c7', 'AT35YQP', '10', '6d9df098-d227-4d20-a8d1-4611a2b33374'),
('64bf9f15-2e15-4a83-8e99-f17a2cf4e1e1', 'YS13AEE', '10', '9f9451de-9ab1-4a0f-b268-0f32a908e787'),
('dbe59977-0d77-4fee-8357-a99c5d888052', 'GV20ANZ', '20', '9f9451de-9ab1-4a0f-b268-0f32a908e787'),
('18a60b75-7238-44c9-a07c-4bc01376d560', 'YX02NQI', '20', '6d9df098-d227-4d20-a8d1-4611a2b33374'),
('d37724d7-4d24-437c-b034-13ecb78d59d1', 'TF91NWM', '5', '6d9df098-d227-4d20-a8d1-4611a2b33374'),
('9061fd40-6e29-4fe6-9807-997dcbaf5543', 'VO24EWH', '30', '726af242-bd12-42fa-8506-11d0e318f1af'),
('353c24d5-7195-4d81-9833-647f47685472', 'GT57ATY', '10', '310c500a-aeee-42c4-a224-97b2ee916bcc'),
('5e1cfa2c-5a74-44df-9a4e-f19fc34187f8', 'UH13WNJ', '10', '87b015b4-34f6-4fc1-acdc-1658cadd69e4'),
('9e452177-3215-4ebd-ba19-c4664a37063a', 'VE79BIW', '20', '726af242-bd12-42fa-8506-11d0e318f1af'),
('67f7be6f-f239-420f-a378-73ba95fc914c', 'FI35PKJ', '10', '9f9451de-9ab1-4a0f-b268-0f32a908e787'),
('12c83a16-d19b-45a5-9c9c-2e24fb9414a7', 'QW14PKO', '15', '5e220679-d062-4a46-ab41-9ad8dff08b6d'),
('3d741d34-4b97-4368-8835-6b5f2086eb69', 'PS43TKJ', '20', '6126f5d5-7210-4e50-9fa8-b7772c1392e1'),
('bfdbdc80-fc30-4c53-8f61-43bdd8ab309f', 'MG60UWN', '20', '4ce64d82-5df0-49c5-b718-44d349aff853'),
('3fdeb641-8ae0-47c2-a817-91375136cac9', 'WQ80EPJ', '30', '19321809-beaa-485b-813c-cd99ee269152');
insert into supplier_goods_info_drl
select goods_zpu.id, supplier_3x4.id, random() * 1000, random() * 10000
from goods_zpu,
supplier_3x4
where random() < 0.3;
#1
SELECT goods_zpu.name, supplier_goods_info_drl.price
FROM goods_zpu
INNER JOIN supplier_goods_info_drl ON goods_zpu.id = supplier_goods_info_drl.goods_id
INNER JOIN supplier_3x4 ON supplier_goods_info_drl.supplier_id = supplier_3x4.id
WHERE (goods_zpu.material = 'Bronze' OR goods_zpu.material = 'Granite')
AND price BETWEEN 3000 AND 7000;
#2
SELECT DISTINCT name
FROM supplier_3x4
WHERE name ~ '\d';
#3
SELECT DISTINCT s.name
FROM supplier_3x4 AS s
JOIN supplier_goods_info_drl AS sgi ON s.id = sgi.supplier_id
JOIN goods_zpu AS g on sgi.goods_id = g.id
WHERE g.name = 'Computer'
AND s.id NOT IN (SELECT s.id
FROM supplier_3x4 AS s
JOIN supplier_goods_info_drl AS sgi ON s.id = sgi.supplier_id
JOIN goods_zpu AS g on sgi.goods_id = g.id
WHERE g.name = 'Keyboard');
#4
SELECT DISTINCT s.name
FROM supplier_3x4 AS s
JOIN supplier_goods_info_drl AS sgi ON s.id = sgi.supplier_id
JOIN goods_zpu AS g on sgi.goods_id = g.id
WHERE g.name = 'Car'
AND s.id IN (SELECT s.id
FROM supplier_3x4 AS s
JOIN supplier_goods_info_drl AS sgi ON s.id = sgi.supplier_id
JOIN goods_zpu AS g on sgi.goods_id = g.id
WHERE g.name = 'Bike');
#5
WITH company_load_capacity AS (
SELECT supplier_id, SUM(load_capacity) AS total_load_capacity
FROM transport_l6p
GROUP BY supplier_id
),
max_load_company AS (
SELECT supplier_id
FROM company_load_capacity
ORDER BY total_load_capacity DESC
LIMIT 1
)
SELECT min(price)
FROM supplier_goods_info_drl
WHERE supplier_id IN (SELECT supplier_id from max_load_company);
#6
WITH company_load_capacity AS (
SELECT supplier_id, SUM(load_capacity) AS total_load_capacity
FROM transport_l6p
GROUP BY supplier_id
HAVING SUM(load_capacity) > 100
)
SELECT supplier_id, count(goods_id)
FROM supplier_goods_info_drl
WHERE supplier_id IN (SELECT supplier_id FROM company_load_capacity)
GROUP BY supplier_id;
#7
WITH total_goods_price AS (
SELECT supplier_id, SUM(price*quantity) as total_price
FROM supplier_goods_info_drl
GROUP BY supplier_id
),
total_transport_units as (
SELECT supplier_id, COUNT(id) as total_trans
FROM transport_l6p
GROUP BY supplier_id
)
SELECT s.name
FROM supplier_3x4 as s
WHERE s.id IN (
SELECT supplier_id
FROM total_goods_price
WHERE total_price >= 1000000000
UNION
SELECT supplier_id
FROM total_transport_units
WHERE total_trans >= 10
);
Editor is loading...