Based on the relational hierarchy, dropping the tables if they already exist:
DROP TABLE IF EXISTS loan_payments;
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
address VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
zip VARCHAR(20)
);
SELECT * FROM customers;
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
customer_id INT,
account_type VARCHAR(50),
balance DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
SELECT * FROM accounts;
CREATE TABLE transactions (
transaction_id INT PRIMARY KEY,
account_id INT,
transaction_date DATE,
transaction_amount DECIMAL(10, 2),
transaction_type VARCHAR(10),
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
SELECT * FROM transactions;
CREATE TABLE loans (
loan_id INT PRIMARY KEY,
customer_id INT,
loan_amount DECIMAL(10, 2),
interest_rate DECIMAL(5, 2),
loan_term INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
SELECT * FROM loans;
CREATE TABLE loan_payments (
payment_id INT PRIMARY KEY,
loan_id INT,
payment_date DATE,
payment_amount DECIMAL(10, 2),
FOREIGN KEY (loan_id) REFERENCES loans(loan_id)
);
SELECT * FROM loan_payments;
Ensuring data integrity and consistency while inserting data into the above tables.
INSERT INTO customers (customer_id, first_name, last_name, address, city, state, zip)
VALUES
(1, 'John', 'Doe', '123 Elm St', 'Toronto', 'ON', 'M4B1B3'),
(2, 'Jane', 'Smith', '456 Maple Ave', 'Ottawa', 'ON', 'K1A0B1'),
(3, 'Michael', 'Johnson', '789 Oak Dr', 'Montreal', 'QC', 'H1A1A1'),
(4, 'Emily', 'Davis', '101 Pine Rd', 'Calgary', 'AB', 'T2A0A1'),
(5, 'David', 'Wilson', '202 Birch Blvd', 'Vancouver', 'BC', 'V5K0A1'),
(6, 'Emma', 'Clark', '505 Cedar St', 'Halifax', 'NS', 'B3H0A1'),
(7, 'James', 'Martinez', '606 Spruce Ln', 'Winnipeg', 'MB', 'R3C0A1'),
(8, 'Olivia', 'Garcia', '707 Fir St', 'Edmonton', 'AB', 'T5A0A1'),
(9, 'William', 'Lopez', '808 Redwood Dr', 'Victoria', 'BC', 'V8W0A1'),
(10, 'Ava', 'Anderson', '909 Cypress Ave', 'Quebec City', 'QC', 'G1A0A1'),
(11, 'Alexander', 'Thomas', '1010 Willow Rd', 'St. John''s', 'NL', 'A1A0A1'),
(12, 'Isabella', 'Lee', '1111 Poplar St', 'Fredericton', 'NB', 'E3B0A1'),
(13, 'Daniel', 'Harris', '1212 Ash Blvd', 'Charlottetown', 'PE', 'C1A0A1'),
(14, 'Sophia', 'Young', '1313 Beech Dr', 'Yellowknife', 'NT', 'X1A0A1'),
(15, 'Matthew', 'King', '1414 Cedar Ln', 'Whitehorse', 'YT', 'Y1A0A1'),
(16, 'Charlotte', 'Scott', '1515 Elm St', 'Iqaluit', 'NU', 'X0A0A1'),
(17, 'Joseph', 'Green', '1616 Maple Ave', 'Regina', 'SK', 'S4P0A1'),
(18, 'Amelia', 'Adams', '1717 Oak Dr', 'Saskatoon', 'SK', 'S7K0A1'),
(19, 'Christopher', 'Baker', '1818 Pine Rd', 'Thunder Bay', 'ON', 'P7A0A1'),
(20, 'Mia', 'Nelson', '1919 Birch Blvd', 'London', 'ON', 'N6A0A1'),
(21, 'Andrew', 'Mitchell', '2020 Spruce Ln', 'Hamilton', 'ON', 'L8P0A1'),
(22, 'Harper', 'Roberts', '2121 Fir St', 'Kitchener', 'ON', 'N2G0A1'),
(23, 'Joshua', 'Turner', '2222 Redwood Dr', 'Windsor', 'ON', 'N9A0A1'),
(24, 'Evelyn', 'Phillips', '2323 Cypress Ave', 'Kingston', 'ON', 'K7L0A1'),
(25, 'Daniel', 'Campbell', '2424 Willow Rd', 'St. Catharines', 'ON', 'L2R0A1'),
(26, 'Abigail', 'Parker', '2525 Poplar St', 'Barrie', 'ON', 'L4M0A1'),
(27, 'James', 'Evans', '2626 Ash Blvd', 'Guelph', 'ON', 'N1H0A1'),
(28, 'Emily', 'Edwards', '2727 Beech Dr', 'Brantford', 'ON', 'N3T0A1'),
(29, 'Michael', 'Collins', '2828 Cedar Ln', 'Thunder Bay', 'ON', 'P7B0A1'),
(30, 'Elizabeth', 'Stewart', '2929 Elm St', 'Peterborough', 'ON', 'K9H0A1'),
(31, 'David', 'Sanchez', '3030 Maple Ave', 'North Bay', 'ON', 'P1B0A1'),
(32, 'Sophia', 'Morris', '3131 Oak Dr', 'Belleville', 'ON', 'K8N0A1'),
(33, 'John', 'Rogers', '3232 Pine Rd', 'Timmins', 'ON', 'P4N0A1'),
(34, 'Olivia', 'Reed', '3333 Birch Blvd', 'Orillia', 'ON', 'L3V0A1'),
(35, 'William', 'Cook', '3434 Spruce Ln', 'Midland', 'ON', 'L4R0A1'),
(36, 'Ava', 'Morgan', '3535 Fir St', 'Collingwood', 'ON', 'L9Y0A1'),
(37, 'Alexander', 'Bell', '3636 Redwood Dr', 'Stratford', 'ON', 'N5A0A1'),
(38, 'Isabella', 'Murphy', '3737 Cypress Ave', 'Woodstock', 'ON', 'N4S0A1'),
(39, 'Daniel', 'Bailey', '3838 Willow Rd', 'Orangeville', 'ON', 'L9W0A1'),
(40, 'Sophia', 'Rivera', '3939 Poplar St', 'Milton', 'ON', 'L9T0A1'),
(41, 'Matthew', 'Cooper', '4040 Ash Blvd', 'Georgetown', 'ON', 'L7G0A1'),
(42, 'Charlotte', 'Richardson', '4141 Beech Dr', 'Newmarket', 'ON', 'L3Y0A1'),
(43, 'Joseph', 'Cox', '4242 Cedar Ln', 'Aurora', 'ON', 'L4G0A1'),
(44, 'Amelia', 'Howard', '4343 Elm St', 'Bradford', 'ON', 'L3Z0A1'),
(45, 'Christopher', 'Ward', '4444 Maple Ave', 'Keswick', 'ON', 'L4P0A1'),
(46, 'Mia', 'Brooks', '4545 Oak Dr', 'Stouffville', 'ON', 'L4A0A1'),
(47, 'Andrew', 'Gray', '4646 Pine Rd', 'Uxbridge', 'ON', 'L9P0A1'),
(48, 'Harper', 'James', '4747 Birch Blvd', 'Port Perry', 'ON', 'L9L0A1'),
(49, 'Joshua', 'Bennett', '4848 Spruce Ln', 'Beaverton', 'ON', 'L0K0A1'),
(50, 'Evelyn', 'Barnes', '4949 Fir St', 'Sutton', 'ON', 'L0E0A1'),
(51, 'Daniel', 'Ross', '5050 Redwood Dr', 'Pefferlaw', 'ON', 'L0E0A1'),
(52, 'Abigail', 'Henderson', '5151 Cypress Ave', 'Mount Albert', 'ON', 'L0G0A1'),
(53, 'James', 'Jenkins', '5252 Willow Rd', 'Queensville', 'ON', 'L0G0A1'),
(54, 'Emily', 'Perry', '5353 Poplar St', 'Sharon', 'ON', 'L0G0A1'),
(55, 'Michael', 'Butler', '5454 Ash Blvd', 'Holland Landing', 'ON', 'L9N0A1'),
(56, 'Elizabeth', 'Long', '5555 Beech Dr', 'East Gwillimbury', 'ON', 'L9N0A1'),
(57, 'David', 'Patterson', '5656 Cedar Ln', 'King City', 'ON', 'L7B0A1'),
(58, 'Sophia', 'Hughes', '5757 Elm St', 'Nobleton', 'ON', 'L0G0A1'),
(59, 'John', 'Flores', '5858 Maple Ave', 'Schomberg', 'ON', 'L0G0A1'),
(60, 'Olivia', 'Washington', '5959 Oak Dr', 'Tottenham', 'ON', 'L0G0A1'),
(61, 'William', 'Butler', '6060 Pine Rd', 'Alliston', 'ON', 'L9R0A1'),
(62, 'Ava', 'Simmons', '6161 Birch Blvd', 'Angus', 'ON', 'L0M0A1'),
(63, 'Alexander', 'Foster', '6262 Spruce Ln', 'Stayner', 'ON', 'L0M0A1'),
(64, 'Isabella', 'Gonzalez', '6363 Fir St', 'Wasaga Beach', 'ON', 'L9Z0A1'),
(65, 'Daniel', 'Bryant', '6464 Redwood Dr', 'Elmvale', 'ON', 'L0L0A1'),
(66, 'Sophia', 'Alexander', '6565 Cypress Ave', 'Midland', 'ON', 'L4R0A1'),
(67, 'Matthew', 'Russell', '6666 Willow Rd', 'Penetanguishene', 'ON', 'L9M0A1'),
(68, 'Charlotte', 'Griffin', '6767 Poplar St', 'Victoria Harbour', 'ON', 'L0K0A1'),
(69, 'Joseph', 'Diaz', '6868 Ash Blvd', 'Port McNicoll', 'ON', 'L0K0A1'),
(70, 'Amelia', 'Hayes', '6969 Beech Dr', 'Waubaushene', 'ON', 'L0K0A1'),
(71, 'Christopher', 'Myers', '7070 Cedar Ln', 'Coldwater', 'ON', 'L0K0A1'),
(72, 'Mia', 'Ford', '7171 Elm St', 'Orillia', 'ON', 'L3V0A1'),
(73, 'Andrew', 'Hamilton', '7272 Maple Ave', 'Gravenhurst', 'ON', 'P1P0A1'),
(74, 'Harper', 'Graham', '7373 Oak Dr', 'Bala', 'ON', 'P0C0A1'),
(75, 'Joshua', 'Sullivan', '7474 Pine Rd', 'Bracebridge', 'ON', 'P1L0A1'),
(76, 'Evelyn', 'Wallace', '7575 Birch Blvd', 'Huntsville', 'ON', 'P1H0A1'),
(77, 'Daniel', 'Woods', '7676 Spruce Ln', 'Burks Falls', 'ON', 'P0A0A1'),
(78, 'Abigail', 'Cole', '7777 Fir St', 'Sundridge', 'ON', 'P0A0A1'),
(79, 'James', 'West', '7878 Redwood Dr', 'South River', 'ON', 'P0A0A1'),
(80, 'Emily', 'Jordan', '7979 Cypress Ave', 'North Bay', 'ON', 'P1B0A1'),
(81, 'Michael', 'Owens', '8080 Willow Rd', 'Mattawa', 'ON', 'P0H0A1'),
(82, 'Elizabeth', 'Reynolds', '8181 Poplar St', 'Sturgeon Falls', 'ON', 'P2B0A1'),
(83, 'David', 'Fisher', '8282 Ash Blvd', 'Verner', 'ON', 'P0H0A1'),
(84, 'Sophia', 'Ellis', '8383 Beech Dr', 'Field', 'ON', 'P0H0A1'),
(85, 'John', 'Harrison', '8484 Cedar Ln', 'Temagami', 'ON', 'P0H0A1'),
(86, 'Olivia', 'Gibson', '8585 Elm St', 'New Liskeard', 'ON', 'P0J0A1'),
(87, 'William', 'McDonald', '8686 Maple Ave', 'Haileybury', 'ON', 'P0J1K0');`
SELECT TOP 10 * FROM customers;
INSERT INTO accounts (account_id, customer_id, account_type, balance)
VALUES
(1, 45, 'Savings', 1000.50),
(2, 12, 'Checking', 2500.75),
(3, 78, 'Savings', 1500.00),
(4, 34, 'Checking', 3000.25),
(5, 56, 'Savings', 500.00),
(6, 23, 'Checking', 1200.50),
(8, 67, 'Checking', 2200.00),
(9, 14, 'Savings', 900.25),
(11, 3, 'Savings', 1100.75),
(12, 81, 'Checking', 2700.00),
(13, 29, 'Savings', 1300.25),
(14, 64, 'Checking', 3200.50),
(15, 47, 'Savings', 700.75),
(16, 18, 'Checking', 1400.00),
(18, 5, 'Checking', 1600.50),
(19, 76, 'Savings', 400.75),
(20, 21, 'Checking', 2000.00),
(21, 53, 'Savings', 300.25),
(22, 37, 'Checking', 2400.50),
(24, 11, 'Checking', 2600.00),
(25, 66, 'Savings', 100.25),
(26, 25, 'Checking', 2800.50),
(28, 7, 'Checking', 2900.00),
(29, 58, 'Savings', 75.25),
(30, 32, 'Checking', 3100.50),
(31, 71, 'Savings', 125.75),
(32, 9, 'Checking', 3300.00),
(33, 85, 'Savings', 150.25),
(34, 41, 'Checking', 3500.50),
(35, 62, 'Savings', 175.75),
(36, 27, 'Checking', 3700.00),
(38, 15, 'Checking', 3900.50),
(39, 74, 'Savings', 225.75),
(40, 19, 'Checking', 4100.00),
(41, 51, 'Savings', 250.25),
(42, 36, 'Checking', 4300.50),
(43, 83, 'Savings', 275.75),
(44, 13, 'Checking', 4500.00),
(45, 68, 'Savings', 300.25),
(46, 24, 'Checking', 4700.50),
(48, 6, 'Checking', 4900.00),
(49, 57, 'Savings', 350.25),
(50, 31, 'Checking', 5100.50),
(51, 72, 'Savings', 375.75),
(52, 10, 'Checking', 5300.00),
(53, 86, 'Savings', 400.25),
(54, 42, 'Checking', 5500.50),
(55, 63, 'Savings', 425.75),
(56, 28, 'Checking', 5700.00),
(58, 16, 'Checking', 5900.50),
(59, 75, 'Savings', 475.75),
(60, 20, 'Checking', 6100.00),
(61, 52, 'Savings', 500.25),
(62, 35, 'Checking', 6300.50),
(63, 84, 'Savings', 525.75),
(64, 12, 'Checking', 6500.00),
(65, 69, 'Savings', 550.25),
(66, 26, 'Checking', 6700.50),
(68, 8, 'Checking', 6900.00),
(69, 59, 'Savings', 600.25),
(70, 33, 'Checking', 7100.50),
(71, 73, 'Savings', 625.75),
(72, 17, 'Checking', 7300.00),
(73, 87, 'Savings', 650.25),
(74, 43, 'Checking', 7500.50),
(75, 61, 'Savings', 675.75),
(76, 22, 'Checking', 7700.00),
(78, 4, 'Checking', 7900.50),
(79, 55, 'Savings', 725.75),
(80, 30, 'Checking', 8100.00),
(81, 70, 'Savings', 750.25),
(82, 2, 'Checking', 8300.50),
(83, 82, 'Savings', 775.75),
(84, 40, 'Checking', 8500.00),
(85, 65, 'Savings', 800.25),
(86, 21, 'Checking', 8700.50),
(88, 1, 'Checking', 8900.00),
(89, 54, 'Savings', 850.25),
(90, 38, 'Checking', 9100.50),
(91, 77, 'Savings', 875.75),
(92, 44, 'Checking', 9300.00),
(93, 79, 'Savings', 900.25),
(94, 39, 'Checking', 9500.50),
(95, 60, 'Savings', 925.75),
(96, 48, 'Checking', 9700.00),
(98, 49, 'Checking', 9900.50),
(99, 80, 'Savings', 975.75),
(100, 50, 'Checking', 10100.00);
SELECT TOP 10 * FROM accounts;
INSERT INTO transactions (transaction_id, account_id, transaction_date, transaction_amount, transaction_type)
VALUES
(1, 45, '2024-01-01', 100.50, 'Deposit'),
(2, 12, '2024-01-02', 200.75, 'Withdrawal'),
(3, 78, '2024-01-03', 150.00, 'Deposit'),
(4, 34, '2024-01-04', 300.25, 'Withdrawal'),
(5, 56, '2024-01-05', 250.00, 'Deposit'),
(7, 89, '2024-01-07', 225.50, 'Deposit'),
(9, 14, '2024-01-09', 325.00, 'Deposit'),
(10, 92, '2024-01-10', 375.25, 'Withdrawal'),
(11, 3, '2024-01-11', 100.50, 'Deposit'),
(12, 81, '2024-01-12', 200.75, 'Withdrawal'),
(13, 29, '2024-01-13', 150.00, 'Deposit'),
(14, 64, '2024-01-14', 300.25, 'Withdrawal'),
(16, 18, '2024-01-16', 175.00, 'Withdrawal'),
(17, 99, '2024-01-17', 225.50, 'Deposit'),
(18, 5, '2024-01-18', 275.75, 'Withdrawal'),
(19, 76, '2024-01-19', 325.00, 'Deposit'),
(20, 21, '2024-01-20', 375.25, 'Withdrawal'),
(21, 53, '2024-01-21', 100.50, 'Deposit'),
(23, 88, '2024-01-23', 150.00, 'Deposit'),
(24, 11, '2024-01-24', 300.25, 'Withdrawal'),
(25, 66, '2024-01-25', 250.00, 'Deposit'),
(26, 25, '2024-01-26', 175.00, 'Withdrawal'),
(27, 94, '2024-01-27', 225.50, 'Deposit'),
(29, 58, '2024-01-29', 325.00, 'Deposit'),
(30, 32, '2024-01-30', 375.25, 'Withdrawal'),
(31, 71, '2024-01-31', 100.50, 'Deposit'),
(32, 9, '2024-02-01', 200.75, 'Withdrawal'),
(33, 85, '2024-02-02', 150.00, 'Deposit'),
(34, 41, '2024-02-03', 300.25, 'Withdrawal'),
(35, 62, '2024-02-04', 250.00, 'Deposit'),
(37, 98, '2024-02-06', 225.50, 'Deposit'),
(38, 15, '2024-02-07', 275.75, 'Withdrawal'),
(39, 74, '2024-02-08', 325.00, 'Deposit'),
(40, 19, '2024-02-09', 375.25, 'Withdrawal'),
(41, 51, '2024-02-10', 100.50, 'Deposit'),
(42, 36, '2024-02-11', 200.75, 'Withdrawal'),
(43, 83, '2024-02-12', 150.00, 'Deposit'),
(44, 13, '2024-02-13', 300.25, 'Withdrawal'),
(45, 68, '2024-02-14', 250.00, 'Deposit'),
(46, 24, '2024-02-15', 175.00, 'Withdrawal'),
(47, 95, '2024-02-16', 225.50, 'Deposit'),
(48, 6, '2024-02-17', 275.75, 'Withdrawal'),
(50, 31, '2024-02-19', 375.25, 'Withdrawal'),
(51, 72, '2024-02-20', 100.50, 'Deposit'),
(53, 86, '2024-02-22', 150.00, 'Deposit'),
(54, 42, '2024-02-23', 300.25, 'Withdrawal'),
(55, 63, '2024-02-24', 250.00, 'Deposit'),
(56, 28, '2024-02-25', 175.00, 'Withdrawal'),
(58, 16, '2024-02-27', 275.75, 'Withdrawal'),
(59, 75, '2024-02-28', 325.00, 'Deposit'),
(60, 20, '2024-02-29', 375.25, 'Withdrawal'),
(61, 52, '2024-03-01', 100.50, 'Deposit'),
(62, 35, '2024-03-02', 200.75, 'Withdrawal'),
(63, 84, '2024-03-03', 150.00, 'Deposit'),
(64, 12, '2024-03-04', 300.25, 'Withdrawal'),
(65, 69, '2024-03-05', 250.00, 'Deposit'),
(66, 26, '2024-03-06', 175.00, 'Withdrawal'),
(67, 96, '2024-03-07', 225.50, 'Deposit'),
(68, 8, '2024-03-08', 275.75, 'Withdrawal'),
(69, 59, '2024-03-09', 325.00, 'Deposit'),
(70, 33, '2024-03-10', 375.25, 'Withdrawal'),
(71, 73, '2024-03-11', 100.50, 'Deposit'),
(74, 43, '2024-03-14', 300.25, 'Withdrawal'),
(75, 61, '2024-03-15', 250.00, 'Deposit'),
(76, 22, '2024-03-16', 175.00, 'Withdrawal'),
(77, 91, '2024-03-17', 225.50, 'Deposit'),
(78, 4, '2024-03-18', 275.75, 'Withdrawal'),
(79, 55, '2024-03-19', 325.00, 'Deposit'),
(80, 30, '2024-03-20', 375.25, 'Withdrawal'),
(81, 70, '2024-03-21', 100.50, 'Deposit'),
(82, 2, '2024-03-22', 200.75, 'Withdrawal'),
(83, 82, '2024-03-23', 150.00, 'Deposit'),
(84, 40, '2024-03-24', 300.25, 'Withdrawal'),
(85, 65, '2024-03-25', 250.00, 'Deposit'),
(86, 21, '2024-03-26', 175.00, 'Withdrawal'),
(87, 93, '2024-03-27', 225.50, 'Deposit'),
(88, 1, '2024-03-28', 275.75, 'Withdrawal'),
(89, 54, '2024-03-29', 325.00, 'Deposit'),
(90, 38, '2024-03-30', 375.25, 'Withdrawal'),
(92, 44, '2024-04-01', 200.75, 'Withdrawal'),
(93, 79, '2024-04-02', 150.00, 'Deposit'),
(94, 39, '2024-04-03', 300.25, 'Withdrawal'),
(95, 60, '2024-04-04', 250.00, 'Deposit'),
(96, 48, '2024-04-05', 175.00, 'Withdrawal'),
(97, 90, '2024-04-06', 225.50, 'Deposit'),
(98, 49, '2024-04-07', 275.75, 'Withdrawal'),
(99, 80, '2024-04-08', 325.00, 'Deposit'),
(100, 50, '2024-04-09', 375.25, 'Withdrawal');
SELECT TOP 10 * FROM transactions;
INSERT INTO loans (loan_id, customer_id, loan_amount, interest_rate, loan_term)
VALUES
(1, 45, 10000.50, 5.5, 36),
(2, 12, 20000.75, 4.5, 48),
(3, 78, 15000.00, 6.0, 60),
(4, 34, 30000.25, 3.5, 24),
(5, 56, 25000.00, 5.0, 36),
(6, 23, 17500.50, 4.0, 48),
(8, 67, 27500.00, 3.0, 24),
(9, 14, 32500.25, 5.5, 36),
(11, 3, 10000.75, 6.0, 60),
(12, 81, 20000.00, 3.5, 24),
(13, 29, 15000.25, 5.0, 36),
(14, 64, 30000.50, 4.0, 48),
(15, 47, 25000.75, 6.5, 60),
(16, 18, 17500.00, 3.0, 24),
(18, 5, 27500.50, 4.5, 48),
(19, 76, 32500.75, 6.0, 60),
(20, 21, 37500.00, 3.5, 24),
(21, 53, 10000.25, 5.0, 36),
(22, 37, 20000.50, 4.0, 48),
(24, 11, 30000.00, 3.0, 24),
(25, 66, 25000.25, 5.5, 36),
(26, 25, 17500.50, 4.5, 48),
(28, 7, 27500.00, 3.5, 24),
(29, 58, 32500.25, 5.0, 36),
(30, 32, 37500.50, 4.0, 48),
(31, 71, 10000.75, 6.5, 60),
(32, 9, 20000.00, 3.0, 24),
(33, 85, 15000.25, 5.5, 36),
(34, 41, 30000.50, 4.5, 48),
(35, 62, 25000.75, 6.0, 60),
(36, 27, 17500.00, 3.5, 24),
(38, 15, 27500.50, 4.0, 48),
(39, 74, 32500.75, 6.5, 60),
(40, 19, 37500.00, 3.0, 24),
(41, 51, 10000.25, 5.5, 36),
(42, 36, 20000.50, 4.5, 48),
(43, 83, 15000.75, 6.0, 60),
(44, 13, 30000.00, 3.5, 24),
(45, 68, 25000.25, 5.0, 36),
(46, 24, 17500.50, 4.0, 48),
(48, 6, 27500.00, 3.0, 24),
(49, 57, 32500.25, 5.5, 36),
(50, 31, 37500.50, 4.5, 48),
(51, 72, 10000.75, 6.0, 60),
(52, 10, 20000.00, 3.5, 24),
(53, 86, 15000.25, 5.0, 36),
(54, 42, 30000.50, 4.0, 48),
(55, 63, 25000.75, 6.5, 60),
(56, 28, 17500.00, 3.0, 24),
(58, 16, 27500.50, 4.5, 48),
(59, 75, 32500.75, 6.0, 60),
(60, 20, 37500.00, 3.5, 24),
(61, 52, 10000.25, 5.0, 36),
(62, 35, 20000.50, 4.0, 48),
(63, 84, 15000.75, 6.5, 60),
(64, 12, 30000.00, 3.0, 24),
(65, 69, 25000.25, 5.5, 36),
(66, 26, 17500.50, 4.5, 48),
(68, 8, 27500.00, 3.5, 24),
(69, 59, 32500.25, 5.0, 36),
(70, 33, 37500.50, 4.0, 48),
(71, 73, 10000.75, 6.5, 60),
(72, 17, 20000.00, 3.0, 24),
(73, 87, 15000.25, 5.5, 36),
(74, 43, 30000.50, 4.5, 48),
(75, 61, 25000.75, 6.0, 60),
(76, 22, 17500.00, 3.5, 24),
(78, 4, 27500.50, 4.0, 48),
(79, 55, 32500.75, 6.5, 60),
(80, 30, 37500.00, 3.0, 24),
(81, 70, 10000.25, 5.5, 36),
(82, 2, 20000.50, 4.5, 48),
(83, 82, 15000.75, 6.0, 60),
(84, 40, 30000.00, 3.5, 24),
(85, 65, 25000.25, 5.0, 36),
(86, 21, 17500.50, 4.0, 48),
(88, 1, 27500.00, 3.0, 24),
(89, 54, 32500.25, 5.5, 36),
(90, 38, 37500.50, 4.5, 48),
(91, 77, 10000.75, 6.0, 60),
(92, 44, 20000.00, 3.5, 24),
(93, 79, 15000.25, 5.0, 36),
(94, 39, 30000.50, 4.0, 48),
(95, 60, 25000.75, 6.5, 60),
(96, 48, 17500.00, 3.0, 24),
(98, 49, 27500.50, 4.5, 48),
(99, 80, 32500.75, 6.0, 60),
(100, 50, 37500.00, 3.5, 24);
SELECT TOP 10 * FROM loans;
INSERT INTO loan_payments (payment_id, loan_id, payment_date, payment_amount)
VALUES
(1, 45, '2024-01-01', 100.00),
(4, 89, '2024-01-04', 250.00),
(5, 12, '2024-01-05', 300.00),
(6, 34, '2024-01-06', 350.00),
(7, 56, '2024-01-07', 400.00),
(8, 78, '2024-01-08', 450.00),
(9, 90, '2024-01-09', 500.00),
(10, 11, '2024-01-10', 550.00),
(11, 22, '2024-01-11', 600.00),
(12, 33, '2024-01-12', 650.00),
(13, 44, '2024-01-13', 700.00),
(14, 55, '2024-01-14', 750.00),
(15, 66, '2024-01-15', 800.00),
(17, 88, '2024-01-17', 900.00),
(18, 99, '2024-01-18', 950.00),
(20, 21, '2024-01-20', 1050.00),
(21, 32, '2024-01-21', 1100.00),
(22, 43, '2024-01-22', 1150.00),
(23, 54, '2024-01-23', 1200.00),
(24, 65, '2024-01-24', 1250.00),
(25, 76, '2024-01-25', 1300.00),
(27, 98, '2024-01-27', 1400.00),
(28, 9, '2024-01-28', 1450.00),
(29, 20, '2024-01-29', 1500.00),
(30, 31, '2024-01-30', 1550.00),
(31, 42, '2024-01-31', 1600.00),
(32, 53, '2024-02-01', 1650.00),
(33, 64, '2024-02-02', 1700.00),
(34, 75, '2024-02-03', 1750.00),
(35, 86, '2024-02-04', 1800.00),
(37, 8, '2024-02-06', 1900.00),
(38, 19, '2024-02-07', 1950.00),
(39, 30, '2024-02-08', 2000.00),
(40, 41, '2024-02-09', 2050.00),
(41, 52, '2024-02-10', 2100.00),
(42, 63, '2024-02-11', 2150.00),
(43, 74, '2024-02-12', 2200.00),
(44, 85, '2024-02-13', 2250.00),
(45, 96, '2024-02-14', 2300.00),
(47, 18, '2024-02-16', 2400.00),
(48, 29, '2024-02-17', 2450.00),
(49, 40, '2024-02-18', 2500.00),
(50, 51, '2024-02-19', 2550.00),
(51, 62, '2024-02-20', 2600.00),
(52, 73, '2024-02-21', 2650.00),
(53, 84, '2024-02-22', 2700.00),
(54, 95, '2024-02-23', 2750.00),
(55, 6, '2024-02-24', 2800.00),
(57, 28, '2024-02-26', 2900.00),
(58, 39, '2024-02-27', 2950.00),
(59, 50, '2024-02-28', 3000.00),
(60, 61, '2024-02-29', 3050.00),
(61, 72, '2024-03-01', 3100.00),
(62, 83, '2024-03-02', 3150.00),
(63, 94, '2024-03-03', 3200.00),
(64, 5, '2024-03-04', 3250.00),
(65, 16, '2024-03-05', 3300.00),
(67, 38, '2024-03-07', 3400.00),
(68, 49, '2024-03-08', 3450.00),
(69, 60, '2024-03-09', 3500.00),
(70, 71, '2024-03-10', 3550.00),
(71, 82, '2024-03-11', 3600.00),
(72, 93, '2024-03-12', 3650.00),
(73, 4, '2024-03-13', 3700.00),
(74, 15, '2024-03-14', 3750.00),
(75, 26, '2024-03-15', 3800.00),
(77, 48, '2024-03-17', 3900.00),
(78, 59, '2024-03-18', 3950.00),
(79, 70, '2024-03-19', 4000.00),
(80, 81, '2024-03-20', 4050.00),
(81, 92, '2024-03-21', 4100.00),
(82, 3, '2024-03-22', 4150.00),
(83, 14, '2024-03-23', 4200.00),
(84, 25, '2024-03-24', 4250.00),
(85, 36, '2024-03-25', 4300.00),
(87, 58, '2024-03-27', 4400.00),
(88, 69, '2024-03-28', 4450.00),
(89, 80, '2024-03-29', 4500.00),
(90, 91, '2024-03-30', 4550.00),
(91, 2, '2024-03-31', 4600.00),
(92, 13, '2024-04-01', 4650.00),
(93, 24, '2024-04-02', 4700.00),
(94, 35, '2024-04-03', 4750.00),
(95, 46, '2024-04-04', 4800.00),
(97, 68, '2024-04-06', 4900.00),
(98, 79, '2024-04-07', 4950.00),
(99, 1, '2024-04-08', 5000.00),
(100, 33, '2024-04-10', 1000.00);
SELECT TOP 10 * FROM loan_payments;
-- Query to retrieve all customer information:
SELECT * FROM customers;
SELECT * FROM accounts
WHERE customer_id = 12;
SELECT a.account_id, a.customer_id, c.first_name, c.last_name, a.balance
FROM accounts AS a
LEFT JOIN customers AS c
ON a.customer_id = c.customer_id;
SELECT c.customer_id, c.first_name, c.last_name, l.loan_id, l.loan_amount, l.interest_rate, l.loan_term,
(l.loan_amount + (l.loan_amount * (l.interest_rate / 100) * l.loan_term)) AS total_due_with_interest,
COALESCE(SUM(lp.payment_amount), 0) AS total_paid,
((l.loan_amount + (l.loan_amount * (l.interest_rate / 100) * l.loan_term)) - COALESCE(SUM(lp.payment_amount), 0)) AS loan_balance
FROM customers AS c
INNER JOIN loans AS l
ON c.customer_id = l.customer_id
LEFT JOIN loan_payments AS lp
ON l.loan_id = lp.loan_id
GROUP BY c.customer_id, c.first_name, c.Last_name, l.loan_id, l.loan_amount, l.interest_rate, l.loan_term
ORDER BY c.customer_id;
SELECT DISTINCT c.customer_id, c.first_name, c.last_name
FROM customers AS c
INNER JOIN accounts AS a
ON c.customer_id = a.customer_id
INNER JOIN transactions AS t
ON a.account_id = t.account_id
WHERE t.transaction_date BETWEEN '2024-03-01' AND '2024-03-31';
SELECT c.customer_id, c.first_name, c.last_name, SUM(a.balance) AS total_balance
FROM customers AS c
JOIN accounts AS a
ON c.customer_id = a.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;
SELECT loan_term, AVG(loan_amount) AS average_loan_amount
FROM loans
GROUP BY loan_term;
SELECT loan_id, loan_amount, interest_rate, loan_term,
(loan_amount + (loan_amount * (interest_rate / 100) * loan_term)) AS total_loan_amount,
(loan_amount * (interest_rate / 100) * loan_term) AS total_interest
FROM loans
GROUP BY loan_id, loan_amount, interest_rate, loan_term;
SELECT TOP 1 transaction_type, COUNT(*) AS frequency
FROM transactions
GROUP BY transaction_type
ORDER BY frequency DESC;
SELECT a.account_id, t.transaction_type,
COUNT(*) AS transaction_count,
SUM(t.transaction_amount) AS total_amount
FROM transactions AS t
INNER JOIN accounts AS a
ON t.account_id = a.account_id
GROUP BY a.account_id, t.transaction_type
ORDER BY a.account_id, t.transaction_type;
DROP VIEW IF EXISTS ActiveLoansWithLargePayments;
CREATE VIEW ActiveLoansWithLargePayments AS
SELECT l.loan_id, l.customer_id, l.loan_amount, l.interest_rate, l.loan_term, lp.payment_id, lp.payment_date, lp.payment_amount
FROM loans AS l
JOIN loan_payments AS lp
ON l.loan_id = lp.loan_id
WHERE lp.payment_amount > 1000;
SELECT TOP 10 * FROM ActiveLoansWithLargePayments;
CREATE INDEX idx_transaction_date ON transactions (transaction_date);
Note
Since there isn't enough data in the transactions table, and it takes less than a second to load the complete table, no visible and measurable performance could be observed in pulling data from the indexed or non-indexed transactions table. However, as the data grows, the difference in performance becomes more evident.