PLEASE ASSIST WITH THE TRADITIONAL METHOD... I HAVE THE ANSI METHOD ALREADY. Cas

Question

PLEASE ASSIST WITH THE TRADITIONAL METHOD... I HAVE THE ANSI METHOD ALREADY.

Case Study: City Jail
The following list reflects the current data requests from city managers. Provide the SQL statements that satisfy the requests. For each request, include one solution using the traditional method and one using an ANSI JOIN statement. Test the statements and show execution results.

1. List all criminals along with the crime charges filed. The report needs to include the criminal ID, name, crime code, and fine amount.

2. 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.

3. List all criminals along with crime information. The report needs to include the criminal ID, name, crime classification, date charged, crime code, and fine amount. Include only crimes classified as “Other.” Sort the list by criminal ID and date charged.

4. Create an alphabetical list of all criminals, including criminal ID, name, violent offender status, parole status, and any known aliases.

5. A table named Prob_Contact contains the required frequency of contact with a probation officer, based on the length of the probation period (the number of days assigned to probation). Review the data in this table, which indicates ranges for the number of days and applicable contact frequencies. Create a list containing the name of each criminal who has been assigned a probation period, which is indicated by the sentence type. The list should contain the criminal name, probation start date, probation end date, and required frequency of contact. Sort the list by criminal name and probation start date.

6. A column named Mgr_ID has been added to the Prob_Officers table and contains the ID number of the probation supervisor for each officer. Produce a list showing each probation officer’s name and his or her supervisor’s name. Sort the list alphabetically by probation officer name.

Explanation / Answer

CREATE TABLE aliases ( alias_ID NUMBER(6), criminal_ID NUMBER(6,0), alias_col VARCHAR2(10) ); CREATE TABLE criminals ( criminal_ID NUMBER(6,0), last_col VARCHAR2(15), first_col VARCHAR2(10), street VARCHAR2(30), city VARCHAR2(20), state CHAR(2), zip CHAR(5), phone CHAR(10), v_status CHAR(1) DEFAULT 'N', p_status CHAR(1) DEFAULT 'N' ); CREATE TABLE crimes ( crime_ID NUMBER(9,0), criminal_ID NUMBER(6,0), classification CHAR(1), data_changed DATE DEFAULT SYSDATE, status CHAR(2), hearing_date DATE, appeal_cut_date DATE, ); CREATE TABLE sentences ( sentence_ID NUMBER(6), criminal_ID NUMBER(6,0), type_col CHAR(1), prob_ID NUMBER(5), start_date DATE, end_date DATE, violations NUMBER(3) ); CREATE TABLE prob_officers ( prob_ID NUMBER(5), last_col VARCHAR2(15), first_col VARCHAR2(10), street VARCHAR2(30), city VARCHAR2(20), state CHAR(2), zip CHAR(5,0), phone CHAR(10,0), email VARCHAR2(30), status CHAR(1) DEFAULT 'A' ); CREATE TABLE crime_charges ( charge_ID NUMBER(10,0), crime_ID NUMBER(9,0), crime_code NUMBER(3,0), charge_status CHAR(2), fine_amount NUMBER(7,2), court_fee NUMBER(7,2), amount_paid NUMBER(7,2), pay_due_date DATE ); CREATE TABLE crime_officers ( crime_ID NUMBER(9,0), officer_ID NUMBER(8,0) ); CREATE TABLE officers ( officer_ID NUMBER(8,0), last_col VARCHAR2(15), first_col VARCHAR2(10), precinct CHAR(4), badge VARCHAR2(14), phone CHAR(10,0), status CHAR(1) DEFAULT 'A' ); CREATE TABLE appeals ( appeal_ID NUMBER(5), crime_ID NUMBER(9,0), filing_date DATE, hearing_date DATE, status CHAR(1) DEFAULT 'P' ); CREATE TABLE crime_codes ( crime_code NUMBER(3,0), code_description VARCHAR2(30) );

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.