Data Acquisition and Management Assessment-2 (DATA4200) Assignment Help

Subject Code:	DATA4200
Subject Name:	Data Acquisition and Management
Assessment Title:	In Class Exercise (Individual)
Assessment Type:	Practical SQL Coding and Report
Word Count:	120	Minutes	(+/-10%)
Weighting:	30%
Total Marks:	30
Submission:	In Class -Upload SQL database script and Report via Turnitin
Due Date:	Week 8

Your Task

You are required to:

1. Complete each of the SQL queries, both provided and self-constructed.

2. Take a screenshot of each of the queries and results – copy and paste these into your report. 3. In your Report, explain and summarise the business insights found from executing the SQL queries and the data visualisations.

4. Submit your query script file (.sql) with all your queries using the SQL link within the assessment time limit of 2 hours.

5. Submit your individual report via Turnitin within 72 hours after the end of your in-class exercise.

Background

The SQL Server contains data on Peer-to-Peer Lending. Connection details will be provided on the day of the assessment.

Assessment Instructions

Follow the instructions to create the lendingclub table and populate it with data from the lending_club_loans_10K.csv file. You will be shown how to access the from the lending_club_loans_10K.csv before the assessment.

Section #1: Server Connection and Simple SQL Queries (1 Marks)

Part 1: Updating the table to add a column (1/2 Mark)

Update the table to add a column called recoveries using the given query

Part 2: Simple SELECT Query (1/2 Mark)

1. Create a SELECT statement that selects the first 10 rows for all columns of data.

2. Take a screenshot of the query and its result and paste that in your report

Section #2: SQL for Business Insights (14 marks)

Part 1: Loan and Funded Amounts (2 marks)

• Write and execute a query that displays all the loan details and order it by funded amounts in Ascending order

• Modify the SQL statement to show the number customers(count) where the funded amount is:

o Equal to $10,000 dollars

o Less than $10,000 dollars

o Greater than $10,000 dollars

Take screenshots of the above queries, its result and paste same in your report for interpretation.

Part 2: Loan Terms (2 Marks)

Create an SQL statement that counts the number of the loans based on terms. For example, how many terms (count of loans) are of 36 months? How many are of 60 months?

Take screenshots of the query and its result and paste that in your report for interpretation.

Part 3: Interest Rate (2 Marks)

Create an SQL statement that answers the following business questions:

• What is the average interest rate?

• What is the highest interest rate?

• What is the lowest interest rate?

Take screenshots of the query and its result and paste that in your report for interpretation.

Part 4: Loan Status (3 Marks)

Create an SQL statement that:

• Counts the number of loans for each unique loan status.

• For each of the unique statuses found above, create an SQL statement that displays the first 100 rows of data.

Take screenshots of the query and its result and paste that in your report for interpretation.

Part 5: Loan Grades (3 Marks)

Create and SQL statement that answers the following business questions:

• Display each Loan Grade and the count of the number of loans related to it. • Display each loan sub-grade and the count of the number of loans related to it.

Take screenshots of the query and its result and paste that in your report for interpretation.

Part 6: Loan Defaults/Delinquencies (2 Marks)

Create an SQL statement that answers the following questions:

• How many customers defaulted on their loan obligations?

NOTE: The count should include both Late and Defaulted loan statuses Take screenshots of the query and its result and paste that in your report for interpretation.

Section #3: SQL and Data Visualization for Business Insights (15 Marks)

Part 1: Data Acquisition and Data Visualisations

• Create a SELECT statement that returns all rows and columns of data.

• Export and save the results of the query as CSV file.

• Import the CSV file in either PowerBI or Tableau.

• Select the following variables for visualisations:

o ID, Member ID, Loan Amount, Funded Amount, Term, Interest Rate, Term, Instalment, Grade, Sub-Grade, Employee Length, Home Ownership, Annual Income, Verification Status, Issue Date, Loan Status, Purpose, Address State, DTI, Earliest Credit Line, Outstanding Principal, Total Payment, Recoveries, Collections, Last Payment Date, Last Payment Amount, and Application Type.

• From the data selected, create 3 kinds of unique visualisations, to discover more business insights, and interpret the results. Carefully consider the columns that are used for each kind of visualization.

• Additionally, can you identify the kinds of customers that default on their loans? Take screenshots of each of the visualisations and paste that in your report for interpretation.

Important Study Information

Academic Integrity Policy

KBS values academic integrity. All students must understand the meaning and consequences of cheating, plagiarism and other academic offences under the Academic Integrity and Conduct Policy.

What is academic integrity and misconduct?

What are the penalties for academic misconduct?

How can I appeal my grade?

Late submission of assignments (within the Assessment Policy)

Length Limits for Assessments

Penalties may be applied for assessment submissions that exceed prescribed limits.

Study Assistance

Students may seek study assistance from their local Academic Learning Advisor or refer to the resources on the MyKBS Academic Success Centre page. Further details can be accessed at

Generative AI Traffic Lights

Please see the level of Generative AI that this assessment has been designed to accept:

Traffic

Light

Amount of Generative Artificial Intelligence (AI) usage Evidence Required

This

assessment (✓)

✓

Level 1

This assessment fully integrates Generative AI, encouraging you to harness the technology’s full potential in collaboration with your own expertise.

It will highlight your ability to demonstrate how effectively you can work alongside AI to achieve sophisticated outcomes, blending human intellect and artificial intelligence.

Your collaboration with AI must be

clearly referenced and documented in the appendix of your submission, including all prompts and

responses used for the assessment.

Level 2

This assessment invites you to engage with Generative AI as a means of expanding your creativity and idea generation.

It will highlight your ability to complement your original thinking with the capabilities of AI. For example, through brainstorming and preliminary concept development.

Your collaboration with AI must be

clearly referenced and documented in the appendix of your submission, including all prompts and

responses used for the assessment.

Level 3

This assessment showcases your individual knowledge and skills in the absence of Generative AI support.

It will highlight your personal abilities. For example, to analyse, synthesise, and create based on your own understanding and learning.

Use of generative AI is prohibited and may potentially result in penalties for

academic

misconduct, including but not limited to a mark of zero for the assessment.

Assessment Marking Guide

DATA4200 Assessment 2	Rubric /30
Section 1: Server Connection and Simple SQL Queries
0-0.5	1	/1
Has demonstrated limited achievement: Some of the steps have been followed and completed correctly.	Has achieved all or most of: Most or all the steps have been followed through to completion.	1
Section 2: SQL for Business Insights
0-9	10-14	/14
Has demonstrated limited achievement: Some of the steps have been followed and completed correctly	Has achieved all or most of: Most or all the steps have been followed through to completion.	14
Section 3: SQL and Data Visualization for Business Insights
0-6	7-15	/15
Has demonstrated limited achievement: The SQL query has been constructed, some visualizations performed, and some interpretations were made. Some business insights were obtained, especially with regards to the kinds of customers that defaulted on their loans.	Has achieved all or most of: The SQL query has been constructed, at least 3 visualizations were created, and interpretations were made. Business insights were obtained, especially regarding the kinds of customers that defaulted on their loans.	5 10