Sql Hw 4

-------------------------------------------------

HW 4 –DML Queries and the Where clause

Use CityJail_5.sql for these queries

1. Create a script to allow a user to add new criminals (providing prompts to the user) to the CRIMINALS table

Update Criminals

Set Criminal_ID = ‘&Criminal_ID’

Last = ‘&Last’

First = ‘&First’

Street = ‘&Street’

City = ‘&City’

St = ‘&St’

Zip = ‘&Zip’

Phone = ‘&Phone’

V = ‘&V’

P = ‘&P’;

***Received an error message on multiple attempts, used Insert Into command. I tried using Substution Variable.

2. 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 DEFUALT column value. Query the criminals table to confirm that the new rows have been added. IF YOU CANNOT ADD THE CRIMINALS WITH SCRIPT IN QUESTION 1—Add them with an insert into command

Criminal_ID | Last | First | Street | City | State | Zip | Phone | V_Status | P_Status |

1015 | Fenter | Jim | | Chesapeake | VA | 23320 | | N | N |

1016 | Wayne | Bruce | 11 Apple Rd | Virginia Beach | VA | 23455 | 7678217443 | N | N |

1017 | Kent | Clark | 77 Ship Lane | Norfolk | VA | 22093 | 7677655454 | N | N |

SQL> insert into criminals

2 values (1015, 'Fenter', 'Jim', '', 'Chesapeake', 'VA', 23320, '', 'N', 'N');

SQL> insert into criminals

2 values (1016, 'Wayne', 'Bruce', '11 Apple Rd', 'Virginia Beach', 'VA', 23452, 7678217443, 'N', 'N');

SQL> insert into criminals

2 values (1017, 'Kent', 'Clark', '77 Ship Lane', 'Norfolk', 'VA', 22093, 7677655454, 'N', 'N');

3. Add a a column named Mail_Flag to the criminals table the column should be assigned a datatype of Char(1) (review table creation PPT for how to do this)

Alter Table Criminals

Add (mail_flag char(1));

4. Set the mail_flag column to a value of Y for all criminals...