99%(75)74 out of 75 people found this document helpful
This preview shows page 4 out of 4 pages.
ORDER BY COUNT(isbn) DESC)WHERE ROWNUM <= 5;Case Study: City Jail1.SELECT first, lastFROM (SELECT first, last, COUNT(crime_id) cntFROM criminals JOIN crimes USING (criminal_id)GROUP BY first, lastORDER BY cnt DESC)WHERE ROWNUM <= 3;Note: This problem is trying to highlight an issue with the TOP-N analysis. Many criminals have committed one crime. The TOP-N analysis returns the top three rows, even though other criminals have the same number of crimes.2.CREATE VIEW crimes_vuAS SELECT criminal_id, first, last, p_status, crime_id, date_charged, status, charge_id, crime_code, charge_status, pay_due_date,NVL(court_fee,0) + NVL(fine_amount,0) - NVL(amount_paid,0) dueFROM criminals JOIN crimes USING (criminal_id)JOIN crime_charges USING (crime_id)WITH READ ONLY;3.CREATE MATERIALIZED VIEW officer_infoREFRESH COMPLETESTART WITH SYSDATENEXT SYSDATE + 14AS SELECT officer_id, last, first, precinct, badge, phone, status, COUNT(crime_id) cntFROM officers JOIN crime_officers USING (officer_id)GROUP BY officer_id, last, first, precinct, badge, phone, status;