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 CAST((SELECT COUNT(*) FROM accounts WHERE id = from_account_id AND close_date >= NOW() AND open_date <= NOW()) AS BOOLEAN); END; $$ LANGUAGE 'plpgsql'; -- Вспомогательная функция, проверяющая, что счет - дебетовый CREATE OR REPLACE FUNCTION CheckAccountIsDebit(acc_id INTEGER) RETURNS BOOLEAN AS $$ BEGIN RETURN (SELECT is_debit FROM accounts WHERE 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 SET balance = balance - trasfer_amount WHERE id = from_account_id; ELSE UPDATE accounts SET balance = balance + trasfer_amount WHERE id = from_account_id; END IF; IF CheckAccountIsDebit(to_account_id) THEN UPDATE accounts SET balance = balance + trasfer_amount WHERE id = to_account_id; ELSE UPDATE accounts SET balance = balance - trasfer_amount WHERE 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, IF cl.company_id IS NOT NULL THEN "Company" ELSE "Individual" END IF AS owner_type, IF cl.company_id IS NOT NULL comp.name ELSE CONCAT(i.surname, " ", i.name) END IF 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, 0, 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, IF a.is_debit THEN "Дебетовая" ELSE "Кредитная" END IF, a.balance, CONCAT(SUBSTRING(c.card_number, 0, 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, (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) AS debit_sum, (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) 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 ON cl.id = accounts.client_id WHERE NOW() >= a.open_date AND NOW() <= a.close_date GROUP BY i.id ORDER BY money_amount; -- Содержит более подробную информацию про транзакции, которые были совершены между аккаунтами физических лиц 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() - INTERVAL '1 month' 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() - INTERVAL '1 month' GROUP BY cl.id ) ) SELECT cl.id, IF cl.company_id IS NOT NULL THEN "Company" ELSE "Individual" END IF AS client_type, IF cl.company_id IS NOT NULL THEN comp.name ELSE CONCAT(i.surname, " ", i.name), END IF 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 cl.individual_id = i.id JOIN sent_table AS st ON st.id = cl.id JOIN recieved_table 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 "Not enough money on account: %", id; END IF; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER check_balance_not_less_zero_trigger BEFORE INSERT, 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 EXCEPTION "Can't delete the client %. He still has active accounts", client_id 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; 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; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER check_no_more_active_cards_trigger() BEFORE INSERT ON accounts FOR EACH ROW EXECUTE FUNCTION check_no_more_active_cards();