Practical sql coding and report assessment

Reference no: EM133689652

Assessment Type: Practical SQL Coding and Report

Your Task

You are required to:
Complete each of the SQL queries, both provided and self-constructed.
Take a screenshot of each of the queries and results - copy and paste these into your report.
In your Report, explain and summarise the business insights found from executing the SQL queries and the data visualisations.
Submit your query script file (.sql) with all your queries using the SQL link within the assessment time limit of 2 hours.
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
Part 1: Updating the table to add a column
Update the table to add a column called recoveries using the given query

Part 2: Simple SELECT Query
Create a SELECT statement that selects the first 10 rows for all columns of data.
Take a screenshot of the query and its result and paste that in your report

Section #2: SQL for Business Insights

Part 1: Loan and Funded Amounts
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:
Equal to $10,000 dollars
Less than $10,000 dollars
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
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
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
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
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
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

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:
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.

Reference no: EM133689652

Questions Cloud

Counts the number of the loans based on terms : Write and execute a query that displays all the loan details and order it by funded amounts in Ascending order - Create an SQL statement that counts the number

Describe a healthcare program within your practice : Describe a healthcare program within your practice. What are the costs and projected outcomes of this program?

Describe why you chose children lung cancer : Describe why you chose children lung cancer? Is the topic clinically important and why? Does the topic require practice clarity?

Deficiency of this vitamin leads to goose-stepping gait : Deficiency of this vitamin leads to 'goose-stepping' gait (and other nervous system disorders) in pigs.

Practical sql coding and report assessment : How many customers defaulted on their loan obligations and What is the average interest rate - Create a SELECT statement that selects the first 10 rows

Discuss the pathophysiology of gastric ulcer : Discuss the pathophysiology of gastric ulcer and the contributing factors for the disease condition in Chandler's case

Complaining of episodic anxiety-headache and palpitations : A 39-year-old woman comes to the office complaining of episodic anxiety, headache, and palpitations.

Provide a rationale for each part of your design : Provide a rationale for each part of your design, explaining the problem, approach, and why you made the choices you made.

Positive tuberculin skin test result in the clinic : A 28-year-old man, recently emigrating from the Philippines, was noted to have a positive tuberculin skin test result in the clinic.

User Account

All Pages