Chapter07-SubQueriesI.sql

/* Part 1*/
SELECT AVG(COUNT(CRIME_ID))
	FROM CRIME_OFFICERS
	GROUP BY OFFICER_ID;

/* Part 2 */
SELECT COUNT(CRIME_ID), STATUS
	FROM CRIMES
	GROUP BY STATUS;

/*Part 3 */
SELECT MAX(COUNT(CRIME_ID)) "Most Crimes by One Person"
	FROM CRIMES
	GROUP BY CRIMINAL_ID;

/* Part 4*/
SELECT MIN(FINE_AMOUNT) "Lowest Fine"
	FROM CRIME_CHARGES;

/*Part 5 */
SELECT CRIMINAL_ID,LAST, FIRST,COUNT(SENTENCE_ID)
	FROM CRIMINALS JOIN SENTENCES USING(CRIMINAL_ID)
	GROUP BY CRIMINAL_ID,LAST, FIRST
	HAVING COUNT(SENTENCE_ID)>1;

/*Part 6 */
SELECT PRECINCT,COUNT(CHARGE_STATUS)
	FROM CRIME_CHARGES NATURAL JOIN CRIME_OFFICERS NATURAL JOIN OFFICERS
	WHERE CHARGE_STATUS='GL'
	GROUP BY PRECINCT
	HAVING COUNT(CHARGE_STATUS)>7;

/*Part 7 */
SELECT CLASSIFICATION,TO_CHAR(SUM(FINE_AMOUNT+COURT_FEE),'$9,999.99') "Total Collections", TO_CHAR(SUM(FINE_AMOUNT+COURT_FEE-AMOUNT_PAID),'$9,999.99') "Amount Owed"
	FROM CRIMES NATURAL JOIN CRIME_CHARGES
	GROUP BY CLASSIFICATION;

/*Part 8*/
SELECT CLASSIFICATION, CHARGE_STATUS, COUNT(*)
	FROM CRIMES NATURAL JOIN CRIME_CHARGES
	GROUP BY GROUPING SETS ((CLASSIFICATION, CHARGE_STATUS), ());

/*Part 9a */
SELECT CLASSIFICATION, CHARGE_STATUS, COUNT(*)
	FROM CRIMES NATURAL JOIN CRIME_CHARGES
	GROUP BY GROUPING SETS ((CLASSIFICATION, CHARGE_STATUS), (CLASSIFICATION),(CHARGE_STATUS),());
/*Part 9b */
SELECT CLASSIFICATION, CHARGE_STATUS, COUNT(*)
	FROM CRIMES NATURAL JOIN CRIME_CHARGES
	GROUP BY CUBE (CHARGE_STATUS, CLASSIFICATION);

/*Part 10a */
SELECT CLASSIFICATION, CHARGE_STATUS, COUNT(CHARGE_ID) "Number of Charges"
	FROM CRIMES NATURAL JOIN CRIME_CHARGES
	GROUP BY ROLLUP (CLASSIFICATION,CHARGE_STATUS);

/*Part 10b */
SELECT CLASSIFICATION, CHARGE_STATUS, COUNT(*)
	FROM CRIMES NATURAL JOIN CRIME_CHARGES
	GROUP BY GROUPING SETS ((CLASSIFICATION, CHARGE_STATUS), (CLASSIFICATION), ());