ORDER BY COUNTisbn DESC WHERE ROWNUM 5 Case Study City Jail 1 SELECT first last

Order by countisbn desc where rownum 5 case study

This preview shows page 4 out of 4 pages.

ORDER BY COUNT(isbn) DESC) WHERE ROWNUM <= 5; Case Study: City Jail 1. SELECT first, last FROM (SELECT first, last, COUNT(crime_id) cnt FROM criminals JOIN crimes USING (criminal_id) GROUP BY first, last ORDER 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_vu AS 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) due FROM criminals JOIN crimes USING (criminal_id) JOIN crime_charges USING (crime_id) WITH READ ONLY; 3. CREATE MATERIALIZED VIEW officer_info REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 14 AS SELECT officer_id, last, first, precinct, badge, phone, status, COUNT(crime_id) cnt FROM officers JOIN crime_officers USING (officer_id) GROUP BY officer_id, last, first, precinct, badge, phone, status;
Image of page 4

You've reached the end of your free preview.

Want to read all 4 pages?

  • Spring '16
  • English, Foreign key, ISBN, primary key

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture