Question

Note: Use the City Jail database created with the CityJail_8.sql script that you ran for the Chapter 8 case study. 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

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 toprobation). 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.

Answer 1

Note: Use the City Jail database created with the CityJail_8.sql script that you ran for the Chapter 8 case study. 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

CREATE TABLE aliases ( alias_ID numeric(6), criminal_ID numeric(6,0), alias_col VARCHAR(10) );

CREATE TABLE criminals
( criminal_ID numeric(6,0), last_col VARCHAR(15), first_col VARCHAR(10), street VARCHAR(30),
city VARCHAR(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 numeric(9,0),
criminal_ID numeric(6,0), classification CHAR(1), data_changed datetime DEFAULT getdate(),
status CHAR(2), hearing_datetime datetime, appeal_cut_datetime datetime, );

CREATE TABLE sentences ( sentence_ID numeric(6), criminal_ID numeric(6,0), type_col CHAR(1),
prob_ID numeric(5), start_datetime datetime, end_datetime datetime, violations numeric(3) );

CREATE TABLE prob_officers ( prob_ID numeric(5), last_col VARCHAR(15), first_col VARCHAR(10),
street VARCHAR(30), city VARCHAR(20), state CHAR(2), zip CHAR(5), phone CHAR(10),
email VARCHAR(30), status CHAR(1) DEFAULT 'A' );

CREATE TABLE crime_charges ( charge_ID numeric(10,0), crime_ID numeric(9,0),
crime_code numeric(3,0), charge_status CHAR(2), fine_amount numeric(7,2),
court_fee numeric(7,2), amount_paid numeric(7,2), pay_due_datetime datetime );

CREATE TABLE crime_officers ( crime_ID numeric(9,0), officer_ID numeric(8,0) );
CREATE TABLE officers ( officer_ID numeric(8,0), last_col VARCHAR(15), first_col VARCHAR(10),
precinct CHAR(4), badge VARCHAR(14), phone CHAR(10), status CHAR(1) DEFAULT 'A' );

CREATE TABLE appeals ( appeal_ID numeric(5), crime_ID numeric(9,0), filing_datetime datetime,
hearing_datetime datetime, status CHAR(1) DEFAULT 'P' );
CREATE TABLE crime_codes ( crime_code numeric(3,0), code_description VARCHAR(30) );

ALTER TABLE crimes ADD CONSTRAINT def DEFAULT 'U' FOR classification;
ALTER TABLE crimes ADD CONSTRAINT datedef DEFAULT getdate() for date_recorded
ALTER TABLE prob_officers ADD pager# numeric(10);
ALTER TABLE aliases alter column alias_col VARCHAR(20)

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

SELECT c.criminal_id, c.v_status, c.p_status, a.alias_ID
FROM criminals c, aliases a
WHERE c.criminal_id = a.criminal_id
ORDER BY c.last_COL, c.first_COL;

SELECT c.criminal_id, c.first_COL, c.last_COL,
c.v_status, c.p_status, a.alias_ID
FROM criminals c LEFT OUTER JOIN aliases a
ON c.criminal_id = a.criminal_id
ORDER BY first_COL,last_COL

SELECT с.criminal_id, c.v-status, c. P-status, a.alias_ID FROM criminals c, aliases a WHERE c. criminalid = a, criminalid ORDER BY c.last_COL, c.first_COL - - SELECT c.criminal_id, c.first_COL, c.last_COL c.v_status, c.p status, a.alias_ID FROM criminals c LEFT OUTER JOIN aliases a ON c. criminalid = a, criminalid ORDER BY first COL, last COL - - 00 % Results a Messages criminal_id v_statusp_status alias_ID 1101 2 102 criminal id first_COLlast COL v_status p 101 status alias_ID TEST TEST A 1 2 102 TESTTEST B

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 toprobation). 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.

SELECT c.last_col, c.first_col, s.start_datetime, s.end_datetime
FROM criminals c, sentences s
WHERE c.criminal_id = s.criminal_id
AND s.type_col = 'P' ORDER BY last_col, first_col, start_datetime;
/*JOIN Method */
SELECT c.last_col, c.first_col, s.start_datetime, s.end_datetime
FROM criminals c JOIN sentences s ON s.type_col = 'P'
ORDER BY last_col, first_col, start_datetime;

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.

SELECT pro.last_col, pro.first_col, mgr.last_col AS "Manager last_col Name",
mgr.first_col AS "Manager first_col Name"
FROM prob_officers pro, prob_officers mgr
WHERE pro.prob_ID = mgr.prob_id
ORDER BY pro.last_col, pro.first_col;
  
/*JOIN Method */

SELECT pro.last_col, pro.first_col, mgr.last_col AS "Manager last_col Name", mgr.first_col AS "Manager first_col Name"
FROM prob_officers pro LEFT OUTER JOIN prob_officers mgr ON pro.prob_ID = mgr.prob_id
ORDER BY last_col, first_col;