Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
List all crime charges with a balance owed. List the charge ID, crime ID, fine amount, court fee, amount paid, and amount owed.

List all crime charges with a balance owed. List the charge ID, crime ID, fine amount, court fee, amount paid, and amount owed.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

use sql to answer the following question an erd had been provided

 List all crime charges with a balance owed. List the charge ID, crime ID, fine amount, court fee, amount paid, and amount owed.

Hint: Use this for formula   :   (fine_amount+court_fee)-amount_paid    "Owed"  to find owed amount.Use Crime_charges table

 

COMP122_W21_ERS_100.PROB_CONTACT PROB_CAT NUMBER (2) LOW_AMT NUMBER (5) NUMBER (5) VARCHAR2 (20 BYTE) HIGH_AMT CON_FREQ COMP122_W21_ERS_100.CRIMES COMP122_W21_ERS_100.APPEALS P * CRIME_ID F * CRIMINAL_ID NUMBER (9) APPEAL_ID NUMBER (5) NUMBER (6) F CRIME_ID NUMBER (9) CLASSIFICATION CHAR (1 BYTE) DATE DATE_CHARGED DATE COMP122_W21_ERS_100. FILING_DATE HEARING_DATE DATE STATUS CHAR (2 BYTE) CRIME_CHARGES HEARING_DATE NUMBE NUMBE NUMBE CHAR ( NUMBE NUMBE NUMBE DATE CHARGE_ID * STATUS CHAR (1 BYTE) APPEAL_CUT_DATE DATE CRIME_ID - APPEALS_ID_PK (APPEAL_ID) DATE_RECORDED DATE CRIME_CODE APPEALS_CRIMEID_FK (CRIME_ID) - CRIMES_ID_PK (CRIME_ID) CHARGE_STATUS FINE_AMOUNT APPEALS_ID_PK (APPEAL_ID) CRIMES_CRIMINALID_FK (CRIMINAL_ID) COURT_FEE CRIMES_ID_PK (CRIME_ID) AMOUNT_PAID PAY_DUE_DATE DATE CRIMECHARGES_ID_PK (CHAR CRIMECHARGES_CODE_FK (CF CRIMECHARGES_CRIMEID_FK COMP122_W21_ERS_100.CRIME_CODES P * NUMBER (3) VARCHAR2 (30 BYTE) CRIME_CODE CRIMECHARGES_ID_PK (CHAR CODE_DESCRIPTION COMP122_W21_ERS_100.CRIME_OFFICERS CRIMECODES_CODE_PK (CRIME_CODE) PF* CRIME_ID PF* OFFICER_ID NUMBER (9) NUMBER (8) CRIMECODES_CODE_PK (CRIME_CODE) > CRIMEOFFICERS_CID_OID_PK (CRIME_ID, OFFICER_ID) COMP122_W21_ERS_100.CRIMINALS CRIMEOFFICERS_CRIMEID_FK (CRIME_ID) P * CRIMINAL_ID NUMBER (6) CRIMEOFFICERS_OFFICERID_FK (OFFICER_ID) LAST VARCHAR2 (15 BYTE) CRIMEOFFICERS_CID_OID_PK (CRIME_ID, OFFICER_ID) FIRST VARCHAR2 (10 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (20 BYTE) CHAR (2 BYTE) CHAR (5 BYTE) CHAR (10 BYTE) STREET CITY STATE COMP122_W21_ERS_100. ZIP ALIASES PHONE V_STATUS P_STATUS CHAR (1 BYTE) CHAR (1 BYTE) * ALIAS_ID NUMBER (6) CRIMINAL_ID NUMBER (6) ALIAS VARCHAR2 - CRIMINALS_ID_PK (CRIMINAL_ID) COMP122_W21_ERS_100. ALIASES_ID_PK (ALIAS_ID) CRIMINALS_ID_PK (CRIMINAL_ID) OFFICERS * OFFICER_ID NUMBER (8) APPEALS_CRIMINALID_FK (CRI VARCHAR2 (1 VARCHAR2(1 CHAR (4 BYT VARCHAR2 (1 CHAR (10 BY| CHAR (1 BYT LAST ALIASES_ID_PK (ALIAS_ID) FIRST PRECINCT BADGE PHONE COMP122_W21_ERS_100.PROB_OFFICERS STATUS PROB_ID NUMBER (5) VARCHAR2 (15 BYTE) VARCHAR2 (10 BYTE) - OFFICERS_ID_PK (OFFICER_ID LAST OFFICERS_ID_PK (OFFICER_ID FIRST VARCHAR2 (30 BYTE) VARCHAR2 (20 BYTE) CHAR (2 BYTE) CHAR (5 BYTE) CHAR (10 BYTE) VARCHAR2 (30 BYTE) CHAR (1 BYTE) STREET CITY STATE ZIP COMP122_W21_ERS_100.SENTENCES PHONE P SENTENCE_ID NUMBER (6) EMAIL F * CRIMINAL_ID NUMBER (9) STATUS ΤΥΡE CHAR (1 BYTE) MGR_ID NUMBER (5) F PROB_ID NUMBER (5) PAGER# CHAR (10 BYTE) START_DATE DATE - PROBOFFICERS_ID_PK (PROB_ID) END_DATE DATE VIOLATIONS NUMBER (3) PROBOFFICERS_ID_PK (PROB_ID) - SENTENCES_ID_PK (SENTENCE_ID) SENTENCES_CRIMEID_FK (CRIMINAL_ID) SENTENCES_PROBID_FK (PROB_ID) SENTENCES_ID_PK (SENTENCE_ID) P F
Transcribed Image Text:COMP122_W21_ERS_100.PROB_CONTACT PROB_CAT NUMBER (2) LOW_AMT NUMBER (5) NUMBER (5) VARCHAR2 (20 BYTE) HIGH_AMT CON_FREQ COMP122_W21_ERS_100.CRIMES COMP122_W21_ERS_100.APPEALS P * CRIME_ID F * CRIMINAL_ID NUMBER (9) APPEAL_ID NUMBER (5) NUMBER (6) F CRIME_ID NUMBER (9) CLASSIFICATION CHAR (1 BYTE) DATE DATE_CHARGED DATE COMP122_W21_ERS_100. FILING_DATE HEARING_DATE DATE STATUS CHAR (2 BYTE) CRIME_CHARGES HEARING_DATE NUMBE NUMBE NUMBE CHAR ( NUMBE NUMBE NUMBE DATE CHARGE_ID * STATUS CHAR (1 BYTE) APPEAL_CUT_DATE DATE CRIME_ID - APPEALS_ID_PK (APPEAL_ID) DATE_RECORDED DATE CRIME_CODE APPEALS_CRIMEID_FK (CRIME_ID) - CRIMES_ID_PK (CRIME_ID) CHARGE_STATUS FINE_AMOUNT APPEALS_ID_PK (APPEAL_ID) CRIMES_CRIMINALID_FK (CRIMINAL_ID) COURT_FEE CRIMES_ID_PK (CRIME_ID) AMOUNT_PAID PAY_DUE_DATE DATE CRIMECHARGES_ID_PK (CHAR CRIMECHARGES_CODE_FK (CF CRIMECHARGES_CRIMEID_FK COMP122_W21_ERS_100.CRIME_CODES P * NUMBER (3) VARCHAR2 (30 BYTE) CRIME_CODE CRIMECHARGES_ID_PK (CHAR CODE_DESCRIPTION COMP122_W21_ERS_100.CRIME_OFFICERS CRIMECODES_CODE_PK (CRIME_CODE) PF* CRIME_ID PF* OFFICER_ID NUMBER (9) NUMBER (8) CRIMECODES_CODE_PK (CRIME_CODE) > CRIMEOFFICERS_CID_OID_PK (CRIME_ID, OFFICER_ID) COMP122_W21_ERS_100.CRIMINALS CRIMEOFFICERS_CRIMEID_FK (CRIME_ID) P * CRIMINAL_ID NUMBER (6) CRIMEOFFICERS_OFFICERID_FK (OFFICER_ID) LAST VARCHAR2 (15 BYTE) CRIMEOFFICERS_CID_OID_PK (CRIME_ID, OFFICER_ID) FIRST VARCHAR2 (10 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (20 BYTE) CHAR (2 BYTE) CHAR (5 BYTE) CHAR (10 BYTE) STREET CITY STATE COMP122_W21_ERS_100. ZIP ALIASES PHONE V_STATUS P_STATUS CHAR (1 BYTE) CHAR (1 BYTE) * ALIAS_ID NUMBER (6) CRIMINAL_ID NUMBER (6) ALIAS VARCHAR2 - CRIMINALS_ID_PK (CRIMINAL_ID) COMP122_W21_ERS_100. ALIASES_ID_PK (ALIAS_ID) CRIMINALS_ID_PK (CRIMINAL_ID) OFFICERS * OFFICER_ID NUMBER (8) APPEALS_CRIMINALID_FK (CRI VARCHAR2 (1 VARCHAR2(1 CHAR (4 BYT VARCHAR2 (1 CHAR (10 BY| CHAR (1 BYT LAST ALIASES_ID_PK (ALIAS_ID) FIRST PRECINCT BADGE PHONE COMP122_W21_ERS_100.PROB_OFFICERS STATUS PROB_ID NUMBER (5) VARCHAR2 (15 BYTE) VARCHAR2 (10 BYTE) - OFFICERS_ID_PK (OFFICER_ID LAST OFFICERS_ID_PK (OFFICER_ID FIRST VARCHAR2 (30 BYTE) VARCHAR2 (20 BYTE) CHAR (2 BYTE) CHAR (5 BYTE) CHAR (10 BYTE) VARCHAR2 (30 BYTE) CHAR (1 BYTE) STREET CITY STATE ZIP COMP122_W21_ERS_100.SENTENCES PHONE P SENTENCE_ID NUMBER (6) EMAIL F * CRIMINAL_ID NUMBER (9) STATUS ΤΥΡE CHAR (1 BYTE) MGR_ID NUMBER (5) F PROB_ID NUMBER (5) PAGER# CHAR (10 BYTE) START_DATE DATE - PROBOFFICERS_ID_PK (PROB_ID) END_DATE DATE VIOLATIONS NUMBER (3) PROBOFFICERS_ID_PK (PROB_ID) - SENTENCES_ID_PK (SENTENCE_ID) SENTENCES_CRIMEID_FK (CRIMINAL_ID) SENTENCES_PROBID_FK (PROB_ID) SENTENCES_ID_PK (SENTENCE_ID) P F
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
SQL Functions
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS