1. Worksheets

The purpose of this weeks worksheet is to provide additional SQL practice. Please attempt the worksheet without using provided answers. 

2. Questions and Completion

To receive credit for completing the worksheet, you must say ‘Done’ on the worksheet folder.

If you have questions as you go through this worksheet, please feel free to email the instructor.

3. Prerequisites: Oracle SQL Developer

If you have not done so already, download Oracle SQL Developer for your specific OS.

Now connect to the database using the instructions posted on D2L.

Test your connection. If successful, you are good to start.

4. Difference between IN, NOT IN and EXISTS. 

Build a database using the SQL schema here. 

(i) We want to know the justiceId and leaning/category of justices that are handling cases. Write a SQL statement using IN.

(ii) We want to know which justices are not handling any cases. Write an SQL statement with NOT IN. Explain your answer. 

(iii) Write the correct SQL statement for (ii). 

(iv) How will you write (iii) using NOT EXISTS. 

5. Using CTEs for Data Analytics

A histogram is an accurate representation of the distribution of numerical data. To construct a histogram, the first step is to "bin" (or "bucket") the range of values—that is, divide the entire range of values into a series of intervals—and then count how many values fall into each interval. The bins are usually specified as consecutive, non-overlapping intervals of a variable. The bins (intervals) must be adjacent, and are often (but are not required to be) of equal size.

Use your Loan Table created in HW1 to do the following exercise:

(i) Write a CTE to calculate a histogram of the risk score, i.e., each bin represents the risk score and the count is the number of loans with that risk score. Display the result of histogram in SQLDeveloper.

(ii) Spot the Risk score with the maximum number of Loans. Call it RiskScoreWithMaxLoans

(iii) Using your Histogram and RiskScoreWithMaxLoans CTE, write a SQL query to find description of Loans which have this Risk_Score.

6. Check answers

Answers are here, but look at these answers after you have tried the queries.