ch10_caseStudy.pks 2.72 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- THIS WEEK WE DO CASE STUDY

/*
problem 1

List the following information for all crimes that have a period greater than 14 days between the date charged and the hearing date: crime ID, classification, date charged, hearing date, and number of days between the date charged and the hearing date
*/
SELECT CRIME_ID, CLASSIFICATION, DATE_CHARGED, HEARING_DATE, TRUNC(HEARING_DATE - DATE_CHARGED,0) AS DAYS_BETWEEN FROM CRIMES WHERE HEARING_DATE - DATE_CHARGED > 14;

/**
problem 2

Produce a list showing each active police officer and his or her community assignment, indicated by the second letter of the precinct code. Display the community description listed in the following chart, based on the second letter of the precinct code.
*/

SELECT FIRST, LAST, OFFICER_ID, PRECINCT, SUBSTR(PRECINCT, 2, 1) PRE, DECODE(SUBSTR(PRECINCT, 2, 1), 'A', 'SHADY GROVE', 'B', 'CENTER CITY', 'C', 'BAY LANDING') FROM OFFICERS;


/**
problem 3

Produce a list of sentencing information to include criminal ID, name (displayed in all uppercase letters), sentence ID, sentence start date, and length in months of the sentence. The number of months should be shown as a whole number. The start date should  be displayed in the format "December 17, 2009"
*/

SELECT CRIMINAL_ID, UPPER(FIRST ||' '||LAST) AS NAME, SENTENCE_ID, TO_CHAR(START_DATE, 'MONTH DD, YYYY') AS SENTANCE_START, TRUNC(MONTHS_BETWEEN(END_DATE, START_DATE),0) AS MONTHS FROM CRIMINALS JOIN SENTENCES USING (CRIMINAL_ID);


/**
 Problem 4
 
 A list of all amounts owed is needed. Create a list showing each criminal name, charge ID, total amount owed (fine amount plus court fee), amount paid, amount owed, and payment due date. If nothing has been paid to date, the amount paid is NULL. Include only criminals who owe some amount of money. Display the dollar amount with a sign and two decimals.*/

SELECT UPPER(FIRST ||' '||LAST) AS NAME, CHARGE_ID, TO_CHAR(NVL(FINE_AMOUNT,0) + NVL(COURT_FEE,0), '$999.99') AS TOTAL_OWED, TO_CHAR(NVL(AMOUNT_PAID,0), '$999.99') AS AMOUNT_PAID, TO_CHAR((NVL(FINE_AMOUNT,0) + NVL(COURT_FEE,0)) - NVL(AMOUNT_PAID,0), '$999.99') AS BALANCE FROM CRIMINALS JOIN CRIMES USING (CRIMINAL_ID) JOIN CRIME_CHARGES USING (CRIME_ID) WHERE (NVL(FINE_AMOUNT,0) + NVL(COURT_FEE,0)) - NVL(AMOUNT_PAID,0) > 0;



 /**
 Problem 5
 
 Display the criminal name an probation start date for all criminals who have a probation period greater than 2 months. Also, display the date that's two months from the beginning of the probation period, which will serve as a review date.*/

 SELECT UPPER(FIRST ||' '||LAST) AS NAME, START_DATE, ADD_MONTHS(START_DATE, 2) AS REVIEW_DATE FROM CRIMINALS JOIN SENTENCES USING (CRIMINAL_ID) WHERE MONTHS_BETWEEN(END_DATE, START_DATE) >= 2 AND TYPE = 'P';


 /**
 DON'T DO PROBLEM 6
 */