SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Managers ( manager_id INT PRIMARY KEY, name VARCHAR(255), grade VARCHAR(50), city VARCHAR(50) ); INSERT INTO Managers (manager_id, name, grade, city) VALUES (1, 'John Doe', 'Senior', 'Париж'), (2, 'Jane Smith', 'Middle', 'Лондон'), (3, 'James Brown', 'Junior', 'Берлин'), (4, 'Chris Wilson', 'Middle', 'Мадрид'), (5, 'Russell White', 'Junior', 'Мадрид'), (6, 'Caitlin Maxwell', 'Senior', 'Мадрид'), (7, 'Robert Morales', 'Senior', 'Рим'), (8, 'Michael Walter', 'Middle', 'Мадрид'), (9, 'Travis Smith', 'Junior', 'Мадрид'), (10, 'Abigail Collins', 'Middle', 'Лондон'), (11, 'Victor Arnold', 'Junior', 'Берлин'), (12, 'Emma Hayes', 'Senior', 'Париж'), (13, 'Diana Long', 'Middle', 'Рим'), (14, 'Marcus Robinson', 'Junior', 'Берлин'), (15, 'Charlotte Murray', 'Senior', 'Париж'), (16, 'Ryan Washington', 'Junior', 'Мадрид'), (17, 'Megan Austin', 'Middle', 'Рим'), (18, 'Evelyn Edwards', 'Middle', 'Берлин'), (19, 'Katherine Ray', 'Senior', 'Лондон'), (20, 'Sarah Ward', 'Junior', 'Париж'), (21, 'Alexandra Spencer', 'Middle', 'Берлин'), (22, 'Timothy Hoffman', 'Junior', 'Лондон'), (23, 'Eric Soto', 'Senior', 'Мадрид'), (24, 'Brenda Beck', 'Junior', 'Париж'), (25, 'Sophia Schneider', 'Middle', 'Лондон'), (26, 'Bobby Fox', 'Senior', 'Берлин'), (27, 'Crystal Warren', 'Junior', 'Рим'), (28, 'Adam Tucker', 'Middle', 'Мадрид'), (29, 'Denise Lowe', 'Senior', 'Рим'), (30, 'Matthew Russell', 'Junior', 'Лондон'), (31, 'Samantha Murphy', 'Middle', 'Берлин'), (32, 'Diana Hopkins', 'Junior', 'Париж'), (33, 'Jack Anderson', 'Senior', 'Мадрид'), (34, 'Heather Carroll', 'Junior', 'Берлин'), (35, 'David Gutierrez', 'Middle', 'Рим'), (36, 'Julie Graham', 'Senior', 'Париж'), (37, 'Anthony Hernandez', 'Junior', 'Мадрид'), (38, 'Melanie Webb', 'Middle', 'Лондон'), (39, 'Christina Burton', 'Senior', 'Рим'), (40, 'Harold Perry', 'Junior', 'Мадрид'), (41, 'Jesse Robertson', 'Middle', 'Лондон'), (42, 'Gloria Duncan', 'Junior', 'Берлин'), (43, 'Bruce Daniels', 'Senior', 'Париж'), (44, 'Rebecca Watson', 'Middle', 'Берлин'), (45, 'Zachary Knight', 'Junior', 'Рим'), (46, 'Nathan Grant', 'Senior', 'Париж'), (47, 'Julia Jenkins', 'Middle', 'Мадрид'), (48, 'Sara Lane', 'Senior', 'Берлин'), (49, 'Christina Kelly', 'Junior', 'Париж'), (50, 'Chris Wilson', 'Middle', 'Мадрид'); SELECT * FROM Managers; CREATE TABLE Loyalty ( loyalty_id INT PRIMARY KEY, discount DECIMAL(3, 2), name VARCHAR(50) ); INSERT INTO Loyalty (loyalty_id, discount, name) VALUES (1, 0.05, 'Bronze'), (2, 0.10, 'Silver'), (3, 0.15, 'Gold'), (4, 0.20, 'Platinum'), (5, 0.25, 'Diamond'); SELECT * FROM Loyalty; CREATE TABLE Clients ( client_id INT PRIMARY KEY, reg_date DATE, age INT, phone VARCHAR(50), loyalty_id INT, FOREIGN KEY (loyalty_id) REFERENCES Loyalty(loyalty_id) ); INSERT INTO Clients (client_id, reg_date, age, phone, loyalty_id) VALUES (1, '2021-03-10', 28, '123-456-7890', 1), (2, '2020-11-15', 35, '123-456-7891', 3), (3, '2022-02-20', 22, '123-456-7892', 2), (4, '2024-01-31', 40, '(954)626-4329x539', 3), (5, '2023-07-01', 52, '+1-694-911-8641x4917', 2), (6, '2023-11-25', 47, '+1-911-113-9550x32020', 3), (7, '2021-11-16', 50, '+1-846-404-3386x00290', 3), (8, '2023-01-01', 39, '383.278.8258', 3), (9, '2023-05-17', 20, '+1-689-713-4760x46391', 5), (10, '2024-05-29', 43, '942-640-0789', 5), (11, '2024-05-01', 52, '(067)510-8482', 5), (12, '2022-03-24', 26, '811.842.9970', 3), (13, '2024-01-26', 63, '288.635.4193', 5), (14, '2024-06-07', 44, '(476)406-1624x163', 5), (15, '2021-09-21', 18, '(510)064-3482x064', 3), (16, '2022-12-25', 54, '+1-601-785-2984x5326', 4), (17, '2023-05-31', 67, '265.851.6353x05257', 2), (18, '2024-05-27', 30, '(240)016-1892x2059', 1), (19, '2023-12-01', 55, '001-852-229-1955x655', 4), (20, '2023-07-04', 48, '(520)416-1890x38456', 4), (21, '2021-10-05', 39, '797.212.5596x41264', 1), (22, '2022-09-01', 55, '+1-108-707-2097x81440', 1), (23, '2023-08-20', 30, '001-521-370-3769x4106', 1), (24, '2022-12-25', 62, '362-138-6912', 4), (25, '2024-08-24', 66, '+1-841-095-2103x865', 4), (26, '2023-04-02', 38, '859.776.3852', 3), (27, '2024-02-20', 59, '322.492.0144x665', 4), (28, '2022-08-11', 31, '(722)881-6836', 4), (29, '2022-07-04', 68, '+1-830-204-1619x90606', 3), (30, '2024-01-02', 20, '8672579809', 1), (31, '2022-09-20', 68, '611-928-3059x06292', 1), (32, '2023-04-18', 27, '2740110882', 3), (33, '2023-05-12', 57, '279.583.4715', 3), (34, '2021-12-11', 44, '320-022-6982x173', 4), (35, '2022-06-29', 32, '+1-513-394-5243', 4), (36, '2022-06-24', 55, '(160)894-7519x714', 4), (37, '2022-07-07', 50, '994.476.3928x768', 3), (38, '2022-05-29', 53, '853-572-4723', 5), (39, '2024-05-03', 56, '669.769.4391x46398', 5), (40, '2024-02-09', 57, '120-525-1950', 1), (41, '2024-01-28', 39, '833.734.1709x6146', 4), (42, '2023-07-11', 19, '6363156683', 2), (43, '2021-11-24', 55, '001-149-641-2882', 4), (44, '2024-08-26', 40, '+1-584-513-3913x573', 1), (45, '2021-11-22', 23, '001-158-499-0884x281', 4), (46, '2022-05-06', 36, '8026271708', 3), (47, '2022-02-13', 21, '032-375-4967x2171', 2), (48, '2022-01-17', 27, '(046)433-5339', 4), (49, '2023-09-26', 25, '236.359.9593', 2), (50, '2023-01-17', 48, '123-456-7893', 4); SELECT * FROM Clients; CREATE TABLE Orders ( order_id INT PRIMARY KEY, date DATE, time TIME, sum DECIMAL(10, 2), quantity INT, product_id INT, client_id INT, manager_id INT, FOREIGN KEY (client_id) REFERENCES Clients(client_id), FOREIGN KEY (manager_id) REFERENCES Managers(manager_id) ); INSERT INTO Orders (order_id, date, time, sum, quantity, product_id, client_id, manager_id) VALUES (1, '2023-05-10', '12:00:00', 1000.50, 2, 101, 1, 2), (2, '2022-07-15', '15:45:00', 500.00, 1, 102, 2, 3), (3, '2024-01-20', '18:30:00', 1500.75, 3, 103, 3, 1), (4, '2024-05-18', '07:42:50', 334.47, 8, 32, 15, 20), (5, '2023-05-24', '19:47:40', 638.64, 7, 34, 43, 44), (6, '2023-11-13', '00:16:21', 824.96, 7, 95, 36, 30), (7, '2024-04-12', '12:12:39', 480.12, 10, 47, 4, 8), (8, '2023-09-24', '08:55:20', 915.41, 4, 25, 38, 35), (9, '2022-12-14', '11:39:50', 407.84, 3, 56, 36, 8), (10, '2022-11-09', '13:55:03', 795.04, 4, 73, 6, 36), (11, '2022-10-23', '12:22:44', 809.87, 1, 13, 12, 10), (12, '2023-04-18', '11:30:33', 761.85, 8, 15, 3, 28), (13, '2023-04-12', '07:19:55', 2252.93, 4, 3, 22, 20), (14, '2023-03-30', '22:44:52', 39.25, 9, 93, 2, 9), (15, '2023-04-16', '18:20:37', 168.50, 5, 21, 26, 37), (16, '2023-10-07', '02:03:52', 823.47, 3, 43, 16, 45), (17, '2023-12-09', '21:46:26', 358.19, 4, 12, 15, 38), (18, '2022-10-19', '20:53:31', 118.23, 10, 66, 18, 13), (19, '2024-05-07', '13:33:07', 655.96, 5, 24, 37, 49), (20, '2022-10-11', '10:50:38', 454.41, 7, 53, 29, 11), (21, '2024-04-14', '03:46:15', 935.08, 7, 30, 1, 31), (22, '2024-09-02', '23:06:57', 315.26, 8, 49, 38, 48), (23, '2023-04-12', '07:12:55', 337.49, 1, 71, 15, 26), (24, '2024-06-27', '10:15:15', 453.28, 4, 1, 15, 48), (25, '2024-08-30', '17:43:31', 990.63, 6, 92, 34, 19), (26, '2023-04-28', '20:00:16', 469.60, 8, 6, 36, 26), (27, '2023-12-21', '04:56:59', 210.82, 7, 11, 22, 19), (28, '2024-03-25', '19:06:36', 950.56, 4, 24, 4, 41), (29, '2022-10-26', '06:14:55', 460.71, 7, 65, 38, 28), (30, '2022-12-04', '01:50:03', 743.50, 4, 86, 14, 19), (31, '2024-02-21', '16:34:06', 293.36, 1, 66, 32, 26), (32, '2023-05-05', '20:32:33', 806.34, 5, 81, 28, 46), (33, '2024-03-06', '21:49:44', 460.08, 10, 93, 3, 18), (34, '2023-07-10', '00:23:26', 524.57, 2, 13, 22, 12), (35, '2023-11-25', '02:27:15', 561.76, 7, 15, 5, 22), (36, '2022-11-17', '16:56:56', 75.83, 5, 51, 31, 43), (37, '2024-03-10', '14:59:15', 97.69, 6, 20, 14, 14), (38, '2024-07-20', '17:43:55', 846.69, 2, 37, 47, 8), (39, '2023-12-26', '23:06:11', 751.65, 8, 87, 14, 13), (40, '2023-02-17', '19:32:25', 392.24, 2, 26, 26, 27), (41, '2023-03-30', '18:50:12', 664.12, 7, 82, 50, 41), (42, '2024-07-11', '19:53:00', 183.33, 7, 9, 24, 3), (43, '2024-07-09', '08:26:37', 210.74, 2, 55, 23, 10), (44, '2024-05-12', '02:58:11', 640.16, 7, 83, 23, 18), (45, '2022-10-02', '17:24:03', 328.50, 2, 80, 5, 32), (46, '2023-04-27', '20:09:22', 407.60, 5, 99, 30, 26), (47, '2023-09-04', '23:50:02', 957.65, 10, 27, 32, 13), (48, '2024-05-13', '01:14:38', 946.13, 4, 69, 41, 39), (49, '2024-05-29', '15:09:07', 926.29, 6, 86, 17, 12), (50, '2023-09-30', '09:00:00', 750.20, 1, 110, 50, 1); SELECT * FROM Orders; -- 6. Найти город с минимальным количеством менеджеров SELECT city, COUNT(*) AS min_managers FROM Managers GROUP BY city HAVING COUNT(MIN(managers))