SQL-Projects/Chapter05-InsertUpdate.sql at master · rgreer2311/SQL-Projects · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
INSERT INTO Criminals (Criminal_ID, Last, First, Street, City, State, Zip, Phone, V_Status, P_status)
	VALUES (:Criminal_ID, :Last, :First, :Street, :City, :State, :Zip, :Phone, :V_Status,:P_status);


INSERT INTO Criminals (Criminal_ID, Last, First, City, State, Zip)
	VALUES (1015,'Fenter', 'Jim', 'Chesapeake', 'VA', 23320);

INSERT INTO Criminals  (Criminal_ID, Last, First, Street, City, State, Zip, Phone)
	VALUES (1016,'Saunder', 'Bill', '11 Apple Rd', 'Virginia Beach', 'VA',23455,7678217443);

INSERT INTO Criminals  (Criminal_ID, Last, First, Street, City, State, Zip, Phone)
	VALUES (1017,'Painter', 'Troy', '77 Ship Lane', 'Norfolk', 'VA', 22093,7677655454);
/* Part1 */

ALTER TABLE Criminals
	ADD (Mail_flag CHAR(1));

UPDATE Criminals
	SET Mail_flag='Y';

UPDATE Criminals
	SET Mail_flag='N'
	WHERE Street IS NULL;

UPDATE Criminals
	SET Phone='7225659032'
	WHERE Criminal_ID=1016;

DELETE FROM Criminals
	WHERE Criminal_ID=1017;
commit;
/* Part2 */

INSERT INTO Crimes (Crime_ID, Criminal_ID, Classification, Date_charged, Status)
	VALUES(100,1010,'M','15-JUL-09', 'PD');

INSERT INTO Crimes (Crime_ID, Criminal_ID, Classification, Date_charged, Status)
	VALUES(130,1016,'M', '15-JUL-09','PD');


INSERT INTO Crimes (Crime_ID, Criminal_ID, Classification, Date_charged, Status)
	VALUES(130,1016,'P', '15-JUL-09','CL');