Oracle 12c SQL - Chapter 5: Case Study: City Jail, Part II

Note: You are limited to using the Oracle Live interface to create solutions for the problems below. SQL Live does not allow as many commands, statements, and symbols as full Oracle SQL. Specifically, the ampersand substitution variable symbol (&) may not be used for any of the solutions below because Oracle Live does not recognize it. Oracle Live is available to use here: https://livesql.oracle.com/apex/f?p=590:1000

Execute the CityJail_5.sql script (available here: https://pastebin.com/uKSmNReu) 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. Review the script so that youre familiar with the table structure and constraints, and then, after adding the data from the table below, create and execute statements to perform the following DML activities. Save the changes permanently to the database.

Criminal_ID	Last	First	Street	City	State	Zip	Phone	V_status	P_status
1015	Fenter	Jim	NULL	Chesapeake	VA	23320	NULL	N	N
1016	Saunder	Bill	11 Apple Rd	Virginia Beach	VA	23455	7678217443	N	N
1017	Painter	Troy	77 Ship Lane	Norfolk	VA	22093	7677655454	N	N

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

2. Remove criminal 1017 from the database.

3. Execute DML statements to add crime records using the following data: Crime_ID = 100, Criminal_ID = 1010, Classification = M, Date_charged = July 15, 2009, Status = PD. Crime_ID = 130, Criminal_ID = 1016, Classification = M, Date_charged = July 15, 2009, Status = PD. Crime_ID = 130, Criminal_ID = 1016, Classification = P, Date_charged = July 15, 2009, Status = CL.

4. Each statement from Question 3 produces a constraint error. Document the error numbers and messages, and briefly explain the causes of the errors. If your DML statement generates a syntax error rather than a constraint violation error, revise your statement to correct any syntax errors. You can review the CityJail_5.sql file to identify table constraints. Write the statement with the data as given AND then write a corrected statement based on the error message that you received.