Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
use sql to answer the following question an erd has been provide List all criminals along with crime status and appeal status (if applicable). The reports need to include the criminal ID, name, crime classification, date charged, appeal filing date, and appeal status. Show all criminals, regardless of whether they have filed an appeal.   Hint: Join between tables criminals, crimes , appeals Inner join between criminals and crimes  but      LEFT JOIN with appeals

use sql to answer the following question an erd has been provide List all criminals along with crime status and appeal status (if applicable). The reports need to include the criminal ID, name, crime classification, date charged, appeal filing date, and appeal status. Show all criminals, regardless of whether they have filed an appeal.   Hint: Join between tables criminals, crimes , appeals Inner join between criminals and crimes  but      LEFT JOIN with appeals

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 has been provide

List all criminals along with crime status and appeal status (if applicable). The reports need to include the criminal ID, name, crime classification, date charged, appeal filing date, and appeal status. Show all criminals, regardless of whether they have filed an appeal.

 

Hint: Join between tables criminals, crimes , appeals

Inner join between criminals and crimes  but      LEFT JOIN with appeals 

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
steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
SQL Query
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