Question

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 you’re 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.

Answer 1

CREATE TABLE criminals (
criminal_id NUMBER(6),
last VARCHAR2(15),
first VARCHAR2(10),
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
zip CHAR(5),
phone CHAR(10),
v_status CHAR(1) DEFAULT 'N',
p_status CHAR(1) DEFAULT 'N' );

CREATE TABLE crimes (
crime_id NUMBER(9),
criminal_id NUMBER(6),
classification CHAR(1),
date_charged DATE,
status CHAR(2),
hearing_date DATE,
appeal_cut_date DATE );

ALTER TABLE crimes MODIFY (classification DEFAULT 'U');

ALTER TABLE crimes ADD (date_recorded DATE DEFAULT SYSDATE);

ALTER TABLE criminals ADD CONSTRAINT criminals_id_pk PRIMARY KEY (criminal_id);

ALTER TABLE criminals ADD CONSTRAINT criminals_vstatus_ck CHECK (v_status IN('Y','N'));

ALTER TABLE criminals    ADD CONSTRAINT criminals_pstatus_ck CHECK (p_status IN('Y','N'));

ALTER TABLE crimes ADD CONSTRAINT crimes_id_pk PRIMARY KEY (crime_id);

ALTER TABLE crimes ADD CONSTRAINT crimes_class_ck CHECK (classification IN('F','M','O','U'));

ALTER TABLE crimes     ADD CONSTRAINT crimes_status_ck CHECK (status IN('CL','CA','IA'));

ALTER TABLE crimes     ADD CONSTRAINT crimes_criminalid_fk FOREIGN KEY (criminal_id    REFERENCES criminals(criminal_id);

ALTER TABLE crimes     MODIFY (criminal_id NOT NULL);

insert into criminals
values(1015, 'Fenter','Jim','','Chesapeake','VA','23320','','N','N');

insert into criminals
values(1016, 'Saunder','Bill','11 Apple Rd','Virginia Beach','VA','23455','7678217443','N','N');

insert into criminals
values(1017, 'Painter','Troy','77 Ship Lane','Norfolk','VA','22093','7677655454','N','N');

Question 1 :

update criminals
set phone='7225659032' where criminal_id='1016';

Question 2 :

delete from criminals
where criminal_id='1017';

Question 3 & 4 :

insert into crimes
values(100,1015,'M',DATE '2009-07-15','IA','','',''); I have inserted criminal_id=1015 instead of 1010 because there is no value 1010 in criminals table and status='IA', instead of 'PD' because in check constraint, there is no 'PD'.

insert into crimes
values(130,1016,'M',DATE '2009-07-15','CA','','',''); I have inserted status='CA', instead of 'PD' because in check constraint, there is no 'PD'.

insert into crimes
values(160,1016,'M',DATE '2009-07-15','CL','','',''); I have inserted crime_id=160 instead of 130 because crime_id is a primary key, so no 2 values in this attribute can be inserted. Also classification ='M' instead of 'P' because in check constraint of classification, there is no 'P'.