Skip to content

Instantly share code, notes, and snippets.

@davinaleong

davinaleong/BCG SQL - 3.2.6.sql

Last active April 23, 2025 08:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save davinaleong/b2155cf3e65cc38daf7c56e0cd0559b4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save davinaleong/b2155cf3e65cc38daf7c56e0cd0559b4 to your computer and use it in GitHub Desktop.
Download ZIP
IU 3.2.6 SQL 101 SQL Mini-project
Raw
BCG SQL - 3.2.6.sql
# IU3.2.6SQLMini-project
# Davina Leong
/*
[Q1] Create a new database schema (named 'sql_mini_proj') in a local
instance of MySQL Workbench, and set it as default schema
*/
CREATE SCHEMA IF NOT EXISTS sql_mini_proj;
USE sql_mini_proj;
/*
[Q2] Use the Table Data Import Wizard to import the 4 CSV files individually
and generate 4 tables in the database. Refer to "IU 3.2.2 MySQL Local Lab
Setup Guide.pptx" to recap how to do this.
*/
-- Done
/*
[Q3] Write a query to print the names of the tables from the 'sql_mini_proj'
database
*/
SELECT * FROM cc_holders;
SELECT * FROM cc_trans;
SELECT * FROM cust_bank;
SELECT * FROM cust_pi;
/*
[Q4] Explore the tables (e.g., using SELECT * and LIMIT) to gain a better
understanding of the data.
Create an ER diagram for the data (e.g., with LucidChart) using a template
that resembles the format of the following sample:
*/
SELECT * FROM cc_holders, cc_trans, cust_bank, cust_pi
LIMIT 10;
/*
[Q5] From the customer personal information table, find the number of
unique branches (based on branch code) that the bank has.
*/
SELECT * FROM cust_pi; -- To see the table
SELECT COUNT(DISTINCT(c.branch_code)) AS unique_branch_code
FROM cust_pi c;
/*
[Q6] From the credit card transactions table, find the total number of
transactions where the area of expense relates to travel.
*/
SELECT COUNT(transaction_id) AS total_number_of_transactions
FROM cc_trans
WHERE area_of_expense LIKE "%TRAVEL%";
/*
[Q7] From the customer banking information table, find the customer IDs of
customers with more than 1 accounts in a different bank and with credit risk
score of less than 610.
*/
SELECT customer_id
FROM cust_bank
WHERE num_of_accts_in_different_bank > 1
AND credit_risk_score < 610;
/*
[Q8] From the credit card transactions table, find the maximum transaction
amount (using appropriate alias like 'max_trans_amt') for each card type
(using GROUP BY), and ordered by the maximum transaction amount in
descending order.
*/
SELECT card_type, MAX(transaction_amt) AS max_trans_amt
FROM cc_trans
GROUP BY card_type
ORDER BY max_trans_amt DESC;
/*
[Q9] Find the credit account number, age, and location of credit card holders
who opened their account in bank branches with branch codes containing the
digits 54 or 34 (Hint: Use LIKE).
*/
SELECT c.card_number, p.age, p.location
FROM cc_holders h
LEFT JOIN cust_pi p ON h.customer_id = p.customer_id
LEFT JOIN cc_trans c ON h.cc_account_no = c.cc_account_no
WHERE p.branch_code LIKE "%54%" OR p.branch_code LIKE "%34%";
/*
[Q10] From the credit card holders table, find the average holding duration
(in days, rounded to nearest integer) of a credit card account (HINT: Find
date difference between start and end date using DATEDIFF).
*/
SELECT DATEDIFF(end_date, start_date) AS avg_holding_duration
FROM cc_holders
WHERE cc_account_no = 785302001;
/*
[Q11] Find the average credit limit (rounded to 2 decimal places) for
customers who hold a credit card, are salaried, belong to an urban location,
and aged between 40 and 50.
*/
SELECT ROUND(h.limit, 2) AS cc_holders_limit
FROM cc_holders h
LEFT JOIN cc_trans c ON h.cc_account_no = c.cc_account_no
LEFT JOIN cust_pi p ON h.customer_id = p.customer_id
WHERE p.salaried = 1 AND p.location LIKE "%URBAN%" AND p.age BETWEEN 40 AND 50;
/*
[Q12] Find the credit card account number, customer age, region, and
account start date of salaried credit card holders, along with a new column
'start_period' that categorizes the date that the credit card account was
approved. The start_period' column is based on the following logic:
Old: Start date before 1 Jan 2015 (i.e., 2015-01-01)
Mid: Joined on or after 1 Jan 2015, but before 1 Jan 2019
New: Joined on or after 1 Jan 2019
*/
SELECT t.cc_account_no, p.age, p.region, p.account_date_opened,
(CASE
WHEN h.start_date < "2015-01-01" THEN "Old"
WHEN h.start_date < "2029-01-01" THEN "Mid"
ELSE "New"
END) AS start_period
FROM cust_pi p
LEFT JOIN cc_holders h ON p.customer_id = h.customer_id
LEFT JOIN cc_trans t ON h.cc_account_no = t.cc_account_no;
/*
[Q13 - OPTIONAL] Create a view called `cr_limit_gt_800k` which returns
the customer ID, gender, education, and marital status of customers (from
customer personal information table) with credit limit of greater than or
equal to $800,000.
*/
DROP VIEW IF EXISTS cr_limit_gt_800k;
CREATE VIEW cr_limit_gt_800k AS
SELECT p.customer_id, gender, education, marital_status
FROM cust_pi p
LEFT JOIN cc_holders c ON p.customer_id = c.customer_id
WHERE c.limit >= 800000;
SELECT * FROM cr_limit_gt_800k;
/*
[Q14 - OPTIONAL] Create a stored procedure called `oct_ended` that
returns the credit card account ID, customer ID, and total liabilities of credit
cards which ended in the month of October (across all years). Call the stored
procedure and inspect the output.
*/
-- Select statement to test logic
/*
SELECT h.cc_account_no, h.customer_id, b.total_liabilities
FROM cust_bank b
LEFT JOIN cc_holders h ON b.customer_id = h.customer_id
WHERE MONTH(end_date) = 10;
*/
DROP PROCEDURE IF EXISTS oct_ended;
DELIMITER &&
CREATE PROCEDURE oct_ended(IN card_month INT)
BEGIN
SELECT h.cc_account_no, h.customer_id, b.total_liabilities
FROM cust_bank b
LEFT JOIN cc_holders h ON b.customer_id = h.customer_id
WHERE MONTH(end_date) = card_month;
END &&
DELIMITER ;
CALL oct_ended(10);
/*
[Q15 - OPTIONAL] Find the IDs and total transaction amount of the top 10
customers based on the sum of transaction dollar value, and where these
customers have a history of payment delays (i.e. payment_delays=1).
*/
SELECT b.customer_id, ROUND(SUM(t.transaction_amt), 2) AS total_transaction_amt
FROM cust_bank b
LEFT JOIN cc_holders h ON b.customer_id = h.customer_id
LEFT JOIN cc_trans t ON h.cc_account_no = t.cc_account_no
WHERE b.payment_delays = 1
GROUP BY b.customer_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment