Submitted by: Submitted by duncachinno
Views: 347
Words: 466
Pages: 2
Category: Science and Technology
Date Submitted: 05/06/2014 08:40 PM
-------------------------------------------------
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...