DML Queries and the Where clause Using the ssh Oracle databa

-DML Queries and the Where clause “Using the ssh, Oracle database 11g”

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

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

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)

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

5-Set the mail_flag column to N for all criminals that don’t have a street address

6-Change the phone number for criminal 1016 to 7225659032

7-Remove criminal 1017 from the database

**please write the answer by computer to be clear...

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

Solution

2)

INSERT INTO CRIMINALS VALUES(1015,\'Fenter\',\'Jim\',,\'chesapeake\',\'VA\',23320,,\'N\',\'N\')

INSERT INTO CRIMINALS VALUES(1016,\'Wayne\',\'Bruce\',\'11 Apple Rd\',\'Virginia Beach\',\'VA\',23455,7678217443,\'N\',\'N\')

INSERT INTO CRIMINALS VALUES(1017,\'Kent\',\'Clark\',\'77 Ship Lane\',\'Norfolk\',\'VA\',22093,7677655454,\'N\',)

3)

ALTER TABLE CRIMINALS ADD Mail_Flag Char(1)

4)

UPDATE CRIMINALS SET Mail_Flag = \'Y\'

5)

UPDATE CRIMINALS SET Mail_Flag = \'N\' where street = NULL

6)

UPDATE CRIMINALS SET Phone = 7225659032 where Criminal_ID = 1016

7)

DELETE FROM CRIMINALS WHERE Criminal_ID = 1017