Case Study: City Jail

Execute the CityJail_5.sql script to rebuild the CRIMINALS and CRIMES tables of the City Jail

database. The statements at the beginning of this script drop existing tables in your schema

with the same table names.

N O T E

The CityJail_5.sql script is not included in the data files; its included with the solution files, so it will be provided by your instructor.

Review the script so that youre familiar with the table structure and constraints, and then do the following:

ADD THIS SCRIPT FIRST TO THE CITYJAIL DATABASE

1a.

INSERT INTO criminals (criminal_id, last, first, street, city, state, zip, phone, v_status, p_status)

VALUES (&id, '&last','& first','&street', '&city', '&state', '&zip', '&phone', '&v_status', '&p_status');

1. Add the following criminals, using the script created in the previous step. No value needs to be entered at the prompt if it should be set to the DEFAULT column value.

Query the CRIMINALS table to confirm that new rows have been added.

--use script from 1a.

2. Add a column named Mail_flag to the CRIMINALS table. The column should be

assigned a datatype of CHAR(1).

3. Set the Mail_flag column to a value of Y for all criminals.

4. Set the Mail_flag column to N for all criminals who dont have a street address recorded in the database.

5. Change the phone number for criminal 1016 to 7225659032.

6. Remove criminal 1017 from the database.