SQLize Online / PHPize Online  /  SQLtest Online

DROP TABLE IF EXISTS transactions; DROP TABLE IF EXISTS cards; DROP TABLE IF EXISTS accounts; DROP TABLE IF EXISTS clients; DROP TABLE IF EXISTS passports; DROP TABLE IF EXISTS individuals; DROP TABLE IF EXISTS companies; CREATE TABLE IF NOT EXISTS companies ( id SERIAL PRIMARY KEY, ein TEXT NOT NULL UNIQUE, name TEXT NOT NULL, registration_date TIMESTAMP NOT NULL ); CREATE TABLE IF NOT EXISTS individuals ( id SERIAL PRIMARY KEY, itin TEXT NOT NULL UNIQUE, ssn TEXT NOT NULL UNIQUE, name TEXT NOT NULL, surname TEXT NOT NULL, registration_date TIMESTAMP NOT NULL, main_work_place INTEGER, CONSTRAINT FK_MainWorkPlaceIndividuals FOREIGN KEY (main_work_place) REFERENCES companies(id) ON DELETE SET NULL ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS passports ( passport_number TEXT PRIMARY KEY, person_id INTEGER NOT NULL, police_department TEXT NOT NULL, birth_date TIMESTAMP NOT NULL, birth_place TEXT NOT NULL, family_members_count INTEGER NOT NULL, valid_from TIMESTAMP NOT NULL, valid_to TIMESTAMP DEFAULT CAST('9999-12-31 23:59:59' AS TIMESTAMP), current_address TEXT NOT NULL, CONSTRAINT FK_PersonIDPassports FOREIGN KEY (person_id) REFERENCES individuals(id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS clients ( id SERIAL PRIMARY KEY, company_id INTEGER, individual_id INTEGER, CONSTRAINT FK_CompanyIDCLients FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_IndividualIDCLients FOREIGN KEY (individual_id) REFERENCES individuals(id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS accounts ( id SERIAL PRIMARY KEY, client_id INTEGER NOT NULL, is_debit BOOLEAN NOT NULL, balance DOUBLE PRECISION NOT NULL, percent DOUBLE PRECISION NOT NULL, open_date TIMESTAMP NOT NULL, close_date TIMESTAMP NOT NULL DEFAULT CAST('9999-12-31 23:59:59' AS TIMESTAMP), currency TEXT NOT NULL DEFAULT 'RUB', CONSTRAINT FK_CLientIDAccounts FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS transactions ( id SERIAL PRIMARY KEY, from_account_id INTEGER NOT NULL, to_account_id INTEGER NOT NULL, sum DOUBLE PRECISION NOT NULL, time TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT FK_FromAccountIDCLients FOREIGN KEY (from_account_id) REFERENCES accounts(id) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT FK_ToAccountIDCLients FOREIGN KEY (to_account_id) REFERENCES accounts(id) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT CHK_NotToMyself CHECK (from_account_id != to_account_id) ); CREATE TABLE IF NOT EXISTS cards ( card_number TEXT PRIMARY KEY, account_id INTEGER NOT NULL, cvc TEXT NOT NULL, pin_code TEXT NOT NULL, valid_from TIMESTAMP NOT NULL, valid_to TIMESTAMP NOT NULL DEFAULT CAST('9999-12-31 23:59:59' AS TIMESTAMP), CONSTRAINT FK_AccountIDCards FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO companies (ein, name, registration_date) VALUES ('123456789', 'ABC Company', '2023-01-15 08:00:00'), ('987654321', 'XYZ Corporation', '2022-11-20 10:30:00'), ('555555555', 'Acme Inc.', '2024-02-05 14:45:00'), ('777777777', 'Globex Corporation', '2023-07-10 09:20:00'), ('999999999', 'Initech Ltd.', '2022-05-28 11:00:00'), ('888888888', 'Wayne Enterprises', '2023-09-12 13:10:00'), ('222222222', 'Stark Industries', '2024-01-03 16:25:00'), ('111111111', 'Umbrella Corporation', '2022-04-18 08:45:00'), ('666666666', 'Weyland-Yutani Corp', '2023-11-30 12:15:00'), ('444444444', 'Tyrell Corporation', '2022-08-22 10:00:00'), ('333333333', 'Waystar Royco', '2024-03-08 09:30:00'), ('777777888', 'Blue Sun Corporation', '2022-06-14 11:20:00'), ('999999888', 'Gringotts Wizarding Bank', '2023-10-01 14:55:00'), ('888888777', 'Stoic Technologies', '2024-02-18 15:40:00'), ('111222333', 'Spacely Space Sprockets', '2023-05-25 07:50:00'), ('444555666', 'Buy n Large', '2022-12-07 10:10:00'), ('777888999', 'Rich Industries', '2024-01-19 12:35:00'), ('999888777', 'Virtucon', '2023-08-14 09:00:00'), ('333222111', 'Gringotts Bank', '2022-07-26 13:20:00'), ('666555444', 'Vought International', '2024-03-01 11:15:00'), ('123987456', 'Tyrell Corp', '2023-04-02 14:30:00'), ('789654123', 'Oscorp Industries', '2022-09-10 16:00:00'), ('321654987', 'Aperture Science', '2024-02-25 08:20:00'), ('987654323', 'LuthorCorp', '2023-11-12 10:45:00'), ('654321987', 'LexCorp', '2022-06-05 12:55:00'), ('852963741', 'Monarch Solutions', '2024-01-08 09:10:00'), ('369258147', 'Wayne Enterprises', '2023-07-15 11:40:00'), ('951753852', 'Gringotts', '2022-04-30 13:50:00'), ('753951852', 'Spacely Sprockets', '2024-03-18 15:05:00'); INSERT INTO individuals (itin, ssn, name, surname, registration_date, main_work_place) VALUES ('123-45-6789', '987-65-4321', 'John', 'Doe', '2023-01-15 08:00:00', 1), ('987-65-4521', '123-45-6789', 'Jane', 'Smith', '2022-11-20 10:30:00', 2), ('555-55-5555', '111-11-1111', 'Michael', 'Johnson', '2024-02-05 14:45:00', 3), ('777-77-7777', '222-22-2222', 'Emily', 'Brown', '2023-07-10 09:20:00', 4), ('999-99-9999', '333-33-3333', 'Jessica', 'Davis', '2022-05-28 11:00:00', 5), ('888-88-8888', '444-44-4444', 'Robert', 'Wilson', '2023-09-12 13:10:00', 6), ('222-22-2222', '555-55-5555', 'David', 'Martinez', '2024-01-03 16:25:00', 7), ('111-11-1111', '666-66-6666', 'Jennifer', 'Anderson', '2022-04-18 08:45:00', 8), ('666-66-6666', '777-77-7777', 'Matthew', 'Taylor', '2023-11-30 12:15:00', 9), ('444-44-4444', '888-88-8888', 'Amanda', 'Harris', '2022-08-22 10:00:00', 10), ('333-33-3333', '999-99-9999', 'Christopher', 'Thompson', '2024-03-08 09:30:00', 11), ('777-77-8888', '111-22-3333', 'Sarah', 'Garcia', '2022-06-14 11:20:00', 12), ('999-99-8888', '444-55-6666', 'Daniel', 'Young', '2023-10-01 14:55:00', 13), ('888-88-7777', '111-22-3433', 'Melissa', 'Lee', '2024-02-18 15:40:00', 14), ('111-22-3733', '777-88-9999', 'Kevin', 'Lopez', '2023-05-25 07:50:00', 15), ('444-55-6266', '999-88-7777', 'Laura', 'Scott', '2022-12-07 10:10:00', 16), ('777-88-9999', '444-55-6616', 'Jason', 'Clark', '2024-01-19 12:35:00', 17), ('999-88-7777', '222-33-4444', 'Michelle', 'Wright', '2023-08-14 09:00:00', 18), ('333-22-1111', '888-99-7777', 'Ryan', 'Roberts', '2022-07-26 13:20:00', 19), ('666-55-4444', '111-99-2222', 'Ashley', 'Nguyen', '2024-03-01 11:15:00', 20), ('123-98-7456', '789-65-4123', 'Justin', 'Turner', '2023-04-02 14:30:00', 21), ('789-65-4123', '321-65-4987', 'Megan', 'Baker', '2022-09-10 16:00:00', 22), ('321-65-4987', '987-65-4301', 'Eric', 'Parker', '2024-02-25 08:20:00', 23), ('987-65-7321', '654-32-1987', 'Nicole', 'Stewart', '2023-11-12 10:45:00', 24), ('654-32-1987', '852-96-3741', 'Brandon', 'Evans', '2022-06-05 12:55:00', 25), ('852-96-3741', '369-25-8147', 'Stephanie', 'Hill', '2024-01-08 09:10:00', 26), ('369-25-8147', '951-75-3852', 'David', 'Morris', '2023-07-15 11:40:00', 27), ('951-75-3852', '753-95-1852', 'Hannah', 'Cole', '2022-04-30 13:50:00', 28), ('753-95-1852', '987-65-4331', 'Alexander', 'Rivera', '2024-03-18 15:05:00', 29); INSERT INTO passports (passport_number, person_id, police_department, birth_date, birth_place, family_members_count, valid_from, valid_to, current_address) VALUES ('P123456', 1, 'New York Police Department', '1990-05-15 00:00:00', 'New York, USA', 3, '2022-01-01 00:00:00', '2032-01-01 00:00:00', '123 Main St, New York'), ('P987654', 2, 'Los Angeles Police Department', '1988-10-20 00:00:00', 'Los Angeles, USA', 2, '2021-06-10 00:00:00', '2031-06-10 00:00:00', '456 Oak Ave, Los Angeles'), ('P555555', 3, 'Chicago Police Department', '1995-03-05 00:00:00', 'Chicago, USA', 4, '2023-02-15 00:00:00', '2033-02-15 00:00:00', '789 Elm St, Chicago'), ('P777777', 4, 'Houston Police Department', '1992-07-10 00:00:00', 'Houston, USA', 1, '2024-04-20 00:00:00', '2034-04-20 00:00:00', '321 Maple Ave, Houston'), ('P999999', 5, 'Miami Police Department', '1987-12-25 00:00:00', 'Miami, USA', 2, '2020-09-30 00:00:00', '2030-09-30 00:00:00', '567 Pine St, Miami'), ('P888888', 6, 'Dallas Police Department', '1993-09-12 00:00:00', 'Dallas, USA', 5, '2025-08-05 00:00:00', '2035-08-05 00:00:00', '789 Cedar St, Dallas'), ('P222222', 7, 'Philadelphia Police Department', '1998-01-03 00:00:00', 'Philadelphia, USA', 3, '2022-11-15 00:00:00', '2032-11-15 00:00:00', '101 Oak St, Philadelphia'), ('P111111', 8, 'Phoenix Police Department', '1991-04-18 00:00:00', 'Phoenix, USA', 2, '2023-07-20 00:00:00', '2033-07-20 00:00:00', '456 Elm St, Phoenix'), ('P666666', 9, 'San Antonio Police Department', '1996-11-30 00:00:00', 'San Antonio, USA', 4, '2021-03-25 00:00:00', '2031-03-25 00:00:00', '789 Maple St, San Antonio'), ('P444444', 10, 'San Diego Police Department', '1989-08-22 00:00:00', 'San Diego, USA', 1, '2024-10-10 00:00:00', '2034-10-10 00:00:00', '321 Pine St, San Diego'), ('P333333', 11, 'San Francisco Police Department', '1994-03-08 00:00:00', 'San Francisco, USA', 3, '2022-05-15 00:00:00', '2032-05-15 00:00:00', '101 Cedar St, San Francisco'), ('P777888', 12, 'Boston Police Department', '1997-06-14 00:00:00', 'Boston, USA', 2, '2023-08-30 00:00:00', '2033-08-30 00:00:00', '567 Oak St, Boston'), ('P999888', 13, 'Seattle Police Department', '1990-10-01 00:00:00', 'Seattle, USA', 4, '2024-12-25 00:00:00', '2034-12-25 00:00:00', '789 Pine St, Seattle'), ('P888777', 14, 'Denver Police Department', '1994-02-18 00:00:00', 'Denver, USA', 1, '2021-11-05 00:00:00', '2031-11-05 00:00:00', '321 Cedar St, Denver'), ('P111222', 15, 'Portland Police Department', '1992-05-25 00:00:00', 'Portland, USA', 3, '2023-03-10 00:00:00', '2033-03-10 00:00:00', '101 Pine St, Portland'), ('P444555', 16, 'Detroit Police Department', '1999-12-07 00:00:00', 'Detroit, USA', 2, '2022-04-20 00:00:00', '2032-04-20 00:00:00', '456 Maple St, Detroit'), ('P777488', 17, 'Memphis Police Department', '1991-01-19 00:00:00', 'Memphis, USA', 4, '2024-09-15 00:00:00', '2034-09-15 00:00:00', '789 Cedar St, Memphis'), ('P999777', 18, 'Nashville Police Department', '1996-08-14 00:00:00', 'Nashville, USA', 1, '2022-02-05 00:00:00', '2032-02-05 00:00:00', '101 Oak St, Nashville'), ('P333222', 19, 'Austin Police Department', '1988-07-26 00:00:00', 'Austin, USA', 3, '2023-06-30 00:00:00', '2033-06-30 00:00:00', '456 Pine St, Austin'), ('P666555', 20, 'Fort Worth Police Department', '1995-03-01 00:00:00', 'Fort Worth, USA', 2, '2025-05-15 00:00:00', '2035-05-15 00:00:00', '789 Elm St, Fort Worth'), ('P123987', 21, 'Charlotte Police Department', '1993-04-02 00:00:00', 'Charlotte, USA', 4, '2021-09-20 00:00:00', '2031-09-20 00:00:00', '101 Maple St, Charlotte'), ('P789654', 22, 'Indianapolis Police Department', '1998-09-10 00:00:00', 'Indianapolis, USA', 1, '2024-08-15 00:00:00', '2034-08-15 00:00:00', '456 Oak St, Indianapolis'), ('P321654', 23, 'San Jose Police Department', '1994-02-25 00:00:00', 'San Jose, USA', 3, '2022-07-10 00:00:00', '2032-07-10 00:00:00', '789 Cedar St, San Jose'), ('P987254', 24, 'Jacksonville Police Department', '1997-11-12 00:00:00', 'Jacksonville, USA', 2, '2023-01-05 00:00:00', '2033-01-05 00:00:00', '101 Elm St, Jacksonville'), ('P654321', 25, 'San Francisco Police Department', '1992-06-05 00:00:00', 'San Francisco, USA', 4, '2025-04-15 00:00:00', '2035-04-15 00:00:00', '456 Pine St, San Francisco'), ('P852963', 26, 'Columbus Police Department', '1991-01-08 00:00:00', 'Columbus, USA', 1, '2021-03-20 00:00:00', '2031-03-20 00:00:00', '789 Oak St, Columbus'), ('P369258', 27, 'Charlotte Police Department', '1994-07-15 00:00:00', 'Charlotte, USA', 3, '2023-11-10 00:00:00', '2033-11-10 00:00:00', '101 Maple St, Charlotte'), ('P951753', 28, 'San Antonio Police Department', '1993-04-30 00:00:00', 'San Antonio, USA', 2, '2022-10-05 00:00:00', '2032-10-05 00:00:00', '456 Cedar St, San Antonio'), ('P753951', 29, 'Dallas Police Department', '1999-03-18 00:00:00', 'Dallas, USA', 4, '2024-12-01 00:00:00', '2034-12-01 00:00:00', '789 Elm St, Dallas'); INSERT INTO clients (company_id, individual_id) VALUES (1, NULL), (2, NULL), (3, NULL), (4, NULL), (5, NULL), (6, NULL), (7, NULL), (8, NULL), (9, NULL), (10, NULL), (11, NULL), (12, NULL), (13, NULL), (14, NULL), (15, NULL), (16, NULL), (17, NULL), (18, NULL), (19, NULL), (20, NULL), (21, NULL), (22, NULL), (23, NULL), (24, NULL), (25, NULL), (26, NULL), (27, NULL), (28, NULL), (29, NULL), (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6), (NULL, 7), (NULL, 8), (NULL, 9), (NULL, 10), (NULL, 11), (NULL, 12), (NULL, 13), (NULL, 14), (NULL, 15), (NULL, 16), (NULL, 17), (NULL, 18), (NULL, 19), (NULL, 20), (NULL, 21), (NULL, 22), (NULL, 23), (NULL, 24), (NULL, 25), (NULL, 26), (NULL, 27), (NULL, 28), (NULL, 29); INSERT INTO accounts (client_id, is_debit, balance, percent, open_date) VALUES (1, TRUE, 1500.00, 4.0, '2022-01-05 10:00:00'), (2, FALSE, 2000.00, 3.5, '2022-01-15 11:00:00'), (3, TRUE, 3000.00, 6.0, '2022-01-25 12:00:00'), (4, TRUE, 1200.00, 3.0, '2022-02-05 09:00:00'), (5, FALSE, 2500.00, 5.25, '2022-02-15 08:00:00'), (6, TRUE, 1800.00, 4.75, '2022-02-25 10:00:00'), (7, TRUE, 3200.00, 6.25, '2022-03-05 11:00:00'), (8, FALSE, 1400.00, 3.25, '2022-03-15 12:00:00'), (9, TRUE, 2000.00, 4.5, '2022-03-25 09:00:00'), (10, FALSE, 2300.00, 5.0, '2022-04-05 08:00:00'), (11, TRUE, 3500.00, 6.5, '2022-04-15 10:00:00'), (12, FALSE, 1600.00, 3.5, '2022-04-25 11:00:00'), (13, TRUE, 2200.00, 4.25, '2022-05-05 12:00:00'), (14, TRUE, 2500.00, 4.75, '2022-05-15 09:00:00'), (15, FALSE, 3800.00, 5.75, '2022-05-25 08:00:00'), (16, TRUE, 1800.00, 3.25, '2022-06-05 10:00:00'), (17, FALSE, 2700.00, 5.0, '2022-06-15 11:00:00'), (18, TRUE, 2800.00, 5.25, '2022-06-25 12:00:00'), (19, FALSE, 4000.00, 6.0, '2022-07-05 09:00:00'), (20, TRUE, 2000.00, 3.75, '2022-07-15 08:00:00'), (21, TRUE, 2200.00, 4.0, '2022-07-25 10:00:00'), (22, FALSE, 1900.00, 3.5, '2022-08-05 11:00:00'), (23, TRUE, 3000.00, 5.0, '2022-08-15 12:00:00'), (24, FALSE, 2700.00, 4.25, '2022-08-25 09:00:00'), (25, TRUE, 2600.00, 4.75, '2022-09-05 08:00:00'), (26, FALSE, 3200.00, 6.0, '2022-09-15 10:00:00'), (27, TRUE, 2300.00, 4.5, '2022-09-25 11:00:00'), (28, FALSE, 3400.00, 5.25, '2022-10-05 12:00:00'), (29, TRUE, 2800.00, 3.75, '2022-10-15 09:00:00'), (30, FALSE, 3100.00, 5.0, '2022-10-25 08:00:00'), (31, TRUE, 1500.00, 4.0, '2022-01-05 10:00:00'), (32, FALSE, 2000.00, 3.5, '2022-01-15 11:00:00'), (33, TRUE, 3000.00, 6.0, '2022-01-25 12:00:00'), (34, TRUE, 1200.00, 3.0, '2022-02-05 09:00:00'), (35, FALSE, 2500.00, 5.25, '2022-02-15 08:00:00'), (36, TRUE, 1800.00, 4.75, '2022-02-25 10:00:00'), (37, TRUE, 3200.00, 6.25, '2022-03-05 11:00:00'), (38, FALSE, 1400.00, 3.25, '2022-03-15 12:00:00'), (39, TRUE, 2000.00, 4.5, '2022-03-25 09:00:00'), (40, FALSE, 2300.00, 5.0, '2022-04-05 08:00:00'), (41, TRUE, 3500.00, 6.5, '2022-04-15 10:00:00'), (42, FALSE, 1600.00, 3.5, '2022-04-25 11:00:00'), (43, TRUE, 2200.00, 4.25, '2022-05-05 12:00:00'), (44, TRUE, 2500.00, 4.75, '2022-05-15 09:00:00'), (45, FALSE, 3800.00, 5.75, '2022-05-25 08:00:00'), (46, TRUE, 1800.00, 3.25, '2022-06-05 10:00:00'), (47, FALSE, 2700.00, 5.0, '2022-06-15 11:00:00'), (48, TRUE, 2800.00, 5.25, '2022-06-25 12:00:00'), (49, FALSE, 4000.00, 6.0, '2022-07-05 09:00:00'), (50, TRUE, 2000.00, 3.75, '2022-07-15 08:00:00'), (51, TRUE, 2200.00, 4.0, '2022-07-25 10:00:00'), (52, FALSE, 1900.00, 3.5, '2022-08-05 11:00:00'), (53, TRUE, 3000.00, 5.0, '2022-08-15 12:00:00'), (54, FALSE, 2700.00, 4.25, '2022-08-25 09:00:00'), (55, TRUE, 2600.00, 4.75, '2022-09-05 08:00:00'), (56, FALSE, 3200.00, 6.0, '2022-09-15 10:00:00'), (57, TRUE, 2300.00, 4.5, '2022-09-25 11:00:00'), (58, FALSE, 3400.00, 5.25, '2022-10-05 12:00:00'), (58, TRUE, 5000.00, 0, '2022-08-15 12:00:00'); INSERT INTO transactions (from_account_id, to_account_id, sum, time) VALUES (1, 2, 1000.00, '2024-01-01 08:00:00'), (2, 3, 2500.00, '2024-01-02 10:30:00'), (3, 4, 1500.00, '2024-01-03 12:45:00'), (4, 5, 3000.00, '2024-01-04 14:15:00'), (5, 6, 2000.00, '2024-01-05 16:30:00'), (6, 7, 1800.00, '2024-01-06 09:45:00'), (7, 8, 2200.00, '2024-01-07 11:00:00'), (8, 9, 1200.00, '2024-01-08 13:20:00'), (9, 10, 2800.00, '2024-01-09 15:45:00'), (10, 11, 1900.00, '2024-01-10 17:00:00'), (11, 12, 2100.00, '2024-01-11 08:30:00'), (12, 13, 1600.00, '2024-01-12 10:45:00'), (13, 14, 2700.00, '2024-01-13 12:00:00'), (14, 15, 2300.00, '2024-01-14 14:20:00'), (15, 16, 1400.00, '2024-01-15 16:40:00'), (16, 17, 3200.00, '2024-01-16 08:15:00'), (17, 18, 1800.00, '2024-01-17 09:30:00'), (18, 19, 2700.00, '2024-01-18 11:45:00'), (19, 20, 2000.00, '2024-01-19 13:00:00'), (20, 21, 2500.00, '2024-01-20 15:15:00'), (21, 22, 2100.00, '2024-01-21 17:30:00'), (22, 23, 1900.00, '2024-01-22 08:45:00'), (23, 24, 1700.00, '2024-01-23 10:00:00'), (24, 25, 3000.00, '2024-01-24 12:30:00'), (25, 26, 2800.00, '2024-01-25 14:45:00'), (26, 27, 2200.00, '2024-01-26 16:00:00'), (27, 28, 2600.00, '2024-01-27 08:20:00'), (28, 29, 1900.00, '2024-01-28 09:45:00'), (29, 30, 3100.00, '2024-01-29 11:15:00'), (30, 1, 2400.00, '2024-01-30 13:40:00'); -- INSERT INTO transactions (from_account_id, to_account_id, sum) -- VALUES -- (31, 32, 1000.00), -- (32, 33, 1500.00), -- (33, 34, 2000.00), -- (34, 35, 1200.00), -- (35, 36, 1800.00), -- (36, 37, 2500.00), -- (37, 38, 2200.00), -- (38, 39, 3000.00), -- (39, 40, 2800.00), -- (40, 41, 3200.00), -- (41, 42, 3500.00), -- (42, 43, 4000.00), -- (43, 44, 2300.00), -- (44, 45, 2700.00), -- (45, 46, 1900.00), -- (46, 47, 2100.00), -- (47, 48, 2400.00), -- (48, 49, 2600.00), -- (49, 50, 2900.00), -- (50, 51, 3300.00), -- (51, 52, 3600.00), -- (52, 53, 3800.00), -- (53, 54, 2700.00), -- (54, 55, 3100.00), -- (55, 56, 3400.00), -- (56, 57, 3700.00), -- (57, 58, 4100.00), -- (58, 31, 3200.00); INSERT INTO cards (card_number, account_id, cvc, pin_code, valid_from) VALUES ('1234567890113456', 1, '123', '9876', '2022-01-01'), ('2345678901224567', 2, '234', '8765', '2022-02-01'), ('3456789012335678', 3, '345', '7654', '2022-03-01'), ('4567890123446789', 4, '456', '6543', '2022-04-01'), ('5678901234587890', 5, '567', '5432', '2022-05-01'), ('6789012345638901', 6, '678', '4321', '2022-06-01'), ('7890123456799012', 7, '789', '3210', '2022-07-01'), ('8901234567810123', 8, '890', '2109', '2022-08-01'), ('9012345678961234', 9, '901', '1098', '2022-09-01'), ('0123456789032345', 10, '012', '0987', '2022-10-01'), ('1234567890183456', 11, '123', '9876', '2022-11-01'), ('2345673901224567', 12, '234', '8765', '2022-12-01'), ('3456789012355678', 13, '345', '7654', '2023-01-01'), ('4537890123446789', 14, '456', '6543', '2023-02-01'), ('5678901234557890', 15, '567', '8765', '2023-03-01'), ('6789012345668901', 16, '678', '8765', '2023-04-01'), ('7890123456749012', 17, '789', '8765', '2023-05-01'), ('8901234567890123', 18, '890', '2109', '2023-06-01'), ('9012342678961234', 19, '901', '1098', '2023-07-01'), ('0123456789062345', 20, '012', '0987', '2023-08-01'), ('1234567890163456', 21, '123', '9876', '2023-09-01'), ('2345678901264567', 22, '234', '8765', '2023-10-01'), ('3456789012385678', 23, '345', '7654', '2023-11-01'), ('4567890123416789', 24, '456', '6543', '2023-12-01'), ('5678901234547890', 25, '567', '5432', '2024-01-01'), ('6789012345658901', 26, '678', '4321', '2024-02-01'), ('7890123456769012', 27, '789', '3210', '2024-03-01'), ('8901234567830123', 28, '890', '2109', '2024-04-01'), ('9012345678951234', 29, '901', '1098', '2024-05-01'); -- Данный индекс полезен, поскольку зачастую банкам или же полиции приходится устанавливать личность клиента, -- зная только имя и фамилию (высокая селективность) CREATE INDEX ON individuals(name, surname); -- Данные индексы полезны, поскольку зачастую бывает необходимо узнавать транзакции, произошедшие в определенный день -- или в которых учавствовали определенные счета CREATE INDEX ON transactions(time); CREATE INDEX ON transactions(from_account_id); CREATE INDEX ON transactions(to_account_id); -- Данный индекс позволяет собрать статистику по работникам определенной компании CREATE INDEX ON individuals(main_work_place); -- Данный индекс позволяет собрать статистику по городу рождения и отделению полиции, в котором был получен паспорт. -- Это может быть полезно для опреления более бедных и богатых регионов и позволяет лучше разобраться в особенностях -- отношения к банкам у определенных групп населения CREATE INDEX ON passports(birth_place, police_department); -- Вспомогательная функция проверяющая валидность счета CREATE OR REPLACE FUNCTION CheckAccountValidity(id INTEGER) RETURNS BOOLEAN AS $$ BEGIN RETURN EXISTS(SELECT * FROM accounts AS a WHERE a.id = $1 AND close_date >= NOW() AND open_date <= NOW()); END; $$ LANGUAGE 'plpgsql'; -- Вспомогательная функция, проверяющая, что счет - дебетовый CREATE OR REPLACE FUNCTION CheckAccountIsDebit(acc_id INTEGER) RETURNS BOOLEAN AS $$ BEGIN RETURN (SELECT is_debit FROM accounts AS a WHERE a.id = acc_id); END; $$ LANGUAGE 'plpgsql'; -- Данная процедура проивзодит транзакцию, автоматически обновляя балансы аккаунтов CREATE OR REPLACE PROCEDURE MakeTransaction(IN from_account_id INTEGER, IN to_account_id INTEGER, IN trasfer_amount DOUBLE PRECISION) AS $$ BEGIN IF NOT CheckAccountValidity(from_account_id) THEN RAISE 'Unknown or expired from account: %', from_account_id; END IF; IF NOT CheckAccountValidity(to_account_id) THEN RAISE 'Unknown or expired to account: %', to_account_id; END IF; IF CheckAccountIsDebit(from_account_id) THEN UPDATE accounts AS a SET balance = balance - trasfer_amount WHERE a.id = from_account_id; ELSE UPDATE accounts AS a SET balance = balance + trasfer_amount WHERE a.id = from_account_id; END IF; IF CheckAccountIsDebit(to_account_id) THEN UPDATE accounts AS a SET balance = balance + trasfer_amount WHERE a.id = to_account_id; ELSE UPDATE accounts AS a SET balance = balance - trasfer_amount WHERE a.id = to_account_id; END IF; INSERT INTO transactions(from_account_id, to_account_id, sum) VALUES (from_account_id, to_account_id, trasfer_amount); END; $$ LANGUAGE 'plpgsql'; -- Данная функция возвращает список счетов, с которыми не производились транзакции в последние cnt_days дней CREATE OR REPLACE FUNCTION GetInactiveAccounts(cnt_days INTEGER) RETURNS TABLE(account_id INTEGER, owner_type TEXT, owner TEXT, balance DOUBLE PRECISION) AS $$ BEGIN RETURN QUERY WITH cnt_days_transactions AS ( SELECT * FROM transactions WHERE time >= NOW() - MAKE_INTERVAL(days => cnt_days) ) SELECT a.id, CASE WHEN cl.company_id IS NOT NULL THEN 'Company' ELSE 'Individual' END AS owner_type, CASE WHEN cl.company_id IS NOT NULL THEN comp.name ELSE CONCAT(i.surname, ' ', i.name) END AS owner, a.balance FROM accounts AS a JOIN clients AS cl ON cl.id = a.client_id LEFT OUTER JOIN companies AS comp ON cl.company_id = comp.id LEFT OUTER JOIN individuals AS i ON i.id = cl.individual_id WHERE a.id NOT IN (SELECT from_account_id FROM cnt_days_transactions) AND a.id NOT IN (SELECT to_account_id FROM cnt_days_transactions); END; $$ LANGUAGE 'plpgsql'; -- Это представление предоставляет актуальные пасспортные данные пользователя, скрывая номер пасспорта CREATE OR REPLACE VIEW PersonPassportDetails AS SELECT i.id as person_id, i.name, i.surname, EXTRACT(YEAR FROM AGE(NOW(), p.birth_date)) AS age, p.current_address, CONCAT(SUBSTRING(p.passport_number, 1, 2), '****', SUBSTRING(p.passport_number, 6, 1)) FROM individuals AS i JOIN passports AS p ON i.id = p.person_id WHERE NOW() <= p.valid_to AND NOW() >= p.valid_from; -- Аналогичное представление со скрытыми данными активных карт пользователя CREATE OR REPLACE VIEW PersonCardsDetails AS SELECT i.id AS person_id, i.name, i.surname, CASE WHEN a.is_debit THEN 'Дебетовая' ELSE 'Кредитная' END AS card_type, a.balance, CONCAT(SUBSTRING(c.card_number, 1, 4), REPEAT('*', 12)), TO_CHAR(c.valid_to, 'mm/yy') AS expiration_date FROM individuals AS i JOIN clients AS cl ON i.id = cl.individual_id JOIN accounts AS a ON cl.id = a.client_id JOIN cards AS c on c.account_id = a.id WHERE NOW() <= c.valid_to AND NOW() >= c.valid_from; -- Представление, содержащее сумму всех активных кредитных и дебетовых счетов компании в рублях CREATE OR REPLACE VIEW CompaniesAccountsInfo AS SELECT c.id, c.name, c.registration_date, COALESCE((SELECT SUM(a.balance) FROM clients AS cl JOIN accounts AS a ON cl.id = a.client_id WHERE a.is_debit AND cl.company_id = c.id AND NOW() >= a.open_date AND NOW() <= a.close_date AND a.currency = 'RUB'), 0) AS debit_sum, COALESCE((SELECT SUM(a.balance) FROM clients AS cl JOIN accounts AS a ON cl.id = a.client_id WHERE NOT a.is_debit AND cl.company_id = c.id AND NOW() >= a.open_date AND NOW() <= a.close_date AND a.currency = 'RUB'), 0) AS credit_sum FROM companies AS c; -- Представление, содержащее информацию о клиентах, которые держат наибольшее количество рублей на счетах банка CREATE OR REPLACE VIEW BestClients AS SELECT i.id, i.name, i.surname, SUM(a.balance) as money_amount FROM individuals AS i JOIN clients AS cl ON cl.individual_id = i.id JOIN accounts AS a ON cl.id = a.client_id WHERE NOW() >= a.open_date AND NOW() <= a.close_date AND a.currency = 'RUB' GROUP BY i.id ORDER BY money_amount DESC; -- Содержит более подробную информацию про транзакции, которые были совершены между аккаунтами физических лиц CREATE OR REPLACE VIEW FullTransactionInfoIndividuals AS SELECT i1.id AS from_person_id, i1.name AS from_name, i1.surname AS from_surname, i2.id AS to_person_id, i2.name AS to_name, i2.surname AS to_surname, t.sum, t.time FROM individuals AS i1 JOIN clients as cl1 ON i1.id = cl1.individual_id JOIN accounts AS a1 ON a1.client_id = cl1.id JOIN transactions AS t ON t.from_account_id = a1.id JOIN accounts AS a2 ON a2.id = t.to_account_id JOIN clients as cl2 ON a2.client_id = cl2.id JOIN individuals AS i2 ON i2.id = cl2.individual_id; CREATE OR REPLACE VIEW MonthlyTransactionVolume AS WITH sent_table AS ( SELECT cl.id, COALESCE(SUM(t.sum), 0) as sent_sum FROM clients AS cl LEFT OUTER JOIN accounts AS a ON cl.id = a.client_id LEFT OUTER JOIN transactions AS t ON t.from_account_id = a.id WHERE t.time >= NOW() - MAKE_INTERVAL(months => 1) AND a.currency = 'RUB' GROUP BY cl.id ), recieved_table AS ( SELECT cl.id, COALESCE(SUM(t.sum), 0) as received_sum FROM clients AS cl LEFT OUTER JOIN accounts AS a ON cl.id = a.client_id LEFT OUTER JOIN transactions AS t ON t.to_account_id = a.id WHERE t.time >= NOW() - MAKE_INTERVAL(months => 1) AND a.currency = 'RUB' GROUP BY cl.id ) SELECT cl.id, CASE WHEN cl.company_id IS NOT NULL THEN 'Company' ELSE 'Individual' END AS client_type, CASE WHEN cl.company_id IS NOT NULL THEN comp.name ELSE CONCAT(i.surname, ' ', i.name) END AS client_name, st.sent_sum, rt.received_sum FROM clients AS cl LEFT OUTER JOIN companies AS comp ON cl.company_id = comp.id LEFT OUTER JOIN individuals AS i ON cl.individual_id = i.id JOIN sent_table AS st ON st.id = cl.id JOIN recieved_table AS rt ON rt.id = cl.id; -- Данный триггер проверяет, что при изменении баланса (или создании нового) на дебетовом счете не может оказаться сумма < 0 CREATE OR REPLACE FUNCTION check_balance_not_less_zero() RETURNS TRIGGER AS $$ BEGIN IF NEW.is_debit AND NEW.balance < 0 THEN RAISE EXCEPTION 'not enough money on account: %', NEW.id; END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER check_balance_not_less_zero_trigger BEFORE INSERT OR UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION check_balance_not_less_zero(); -- Данный триггер проверяет, что перед удалением клиента у него нет активных счетов CREATE OR REPLACE FUNCTION check_client_no_active_accounts() RETURNS TRIGGER AS $$ BEGIN IF EXISTS(SELECT * FROM accounts AS a WHERE OLD.id = a.client_id AND NOW() <= a.valid_to) THEN RAISE 'Can''t delete the client %. He still has active accounts', client_id; END IF; RETURN OLD; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER check_client_no_active_accounts_trigger BEFORE DELETE ON clients FOR EACH ROW EXECUTE FUNCTION check_client_no_active_accounts(); -- Данный триггер проверяет, что перед удалением счета на нем не осталось денег (в случае дебетового), задолженности (в случае кредитного) CREATE OR REPLACE FUNCTION check_account_is_empty() RETURNS TRIGGER AS $$ BEGIN IF OLD.balance > 0 THEN RAISE EXCEPTION 'Can''t delete account %. Balance is not null: %', OLD.id, OLD.balance; END IF; RETURN OLD; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER check_account_is_empty_trigger BEFORE DELETE ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_is_empty(); -- Данный триггер проверяет, что при добавлении новой карты, привязанной к счету, не существует старых валидных карт CREATE OR REPLACE FUNCTION check_no_more_active_cards() RETURNS TRIGGER AS $$ BEGIN IF EXISTS(SELECT * FROM cards AS c WHERE NEW.account_id = c.account_id AND NOW() <= c.valid_to) THEN RAISE EXCEPTION 'There is still active card on account %. Can''t add new card', NEW.account_id; END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER check_no_more_active_cards_trigger BEFORE INSERT ON accounts FOR EACH ROW EXECUTE FUNCTION check_no_more_active_cards(); SELECT * FROM PersonPassportDetails; SELECT * FROM PersonCardsDetails; SELECT * FROM CompaniesAccountsInfo; SELECT * FROM BestClients; SELECT * FROM FullTransactionInfoIndividuals; SELECT * FROM MonthlyTransactionVolume; SELECT * FROM FullTransactionInfoIndividuals; SELECT i.id, a.id as account_id, a.is_debit, a.balance FROM individuals AS i JOIN clients as cl on cl.individual_id = i.id JOIN accounts as a on a.client_id = cl.id; CALL MakeTransaction(33, 34, 1000); SELECT i.id, a.id as account_id, a.is_debit, a.balance FROM individuals AS i JOIN clients as cl on cl.individual_id = i.id JOIN accounts as a on a.client_id = cl.id; SELECT * FROM GetInactiveAccounts(5000);