-
Notifications
You must be signed in to change notification settings - Fork 0
/
AddingTables.sql
109 lines (83 loc) · 1.97 KB
/
AddingTables.sql
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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
--Creating these TABLES below
--Aliases, Criminals, Crimes, Sentences, Prob_officers, Crime_charges, Crime_officers, Officers, Appeals, Crime_code
CREATE TABLE Aliases
(Alias_ID NUMBER(6),
Criminal_ID NUMBER(6,0),
Alias VARCHAR2(10));
--Commit
CREATE TABLE Criminals
(Criminal_ID NUMBER(6,0),
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,0),
Criminal_ID NUMBER(6,0),
Classification CHAR(1),
Date_charged DATE,
Status CHAR(2),
Hearing_date DATE,
Appeal_cut_date DATE);
CREATE TABLE Sentences
(Sentence_ID NUMBER(6),
Criminal_ID NUMBER(6),
Type CHAR(1),
Prob_ID NUMBER(5),
Start_date DATE,
End_date DATE,
Violations NUMBER(5));
CREATE TABLE Prob_officers
(Prob_ID NUMBER(5),
Last VARCHAR2(15),
First VARCHAR2(10),
Street VARCHAR2(30),
City VARCHAR2(20),
State CHAR(2),
Zip CHAR(5),
Phone CHAR(10),
Email VARCHAR2(30),
Status CHAR(1) DEFAULT 'A');
CREATE TABLE Crime_charges
(Charge_ID NUMBER(10,0),
Crime_ID NUMBER(9,0),
Crime_code NUMBER(3,0),
Charge_status CHAR(2),
Fine_amount NUMBER(7,2),
Court_fee NUMBER(7,2),
Amount_paid NUMBER(7,2),
Pay_due_date DATE);
CREATE TABLE Crime_officers
(Crime_ID NUMBER(9,0),
Officer_ID NUMBER(8,0));
CREATE TABLE Officers
(Officer_ID NUMBER(8,0),
Last VARCHAR2(15),
First VARCHAR2(10),
Precinct CHAR(4),
Badge VARCHAR2(14),
Phone CHAR(10),
Status CHAR(1) DEFAULT 'A');
CREATE TABLE Appeals
(Appeal_ID NUMBER(5),
Crime_ID Number(9,0),
Filing_date DATE,
Hearing_date DATE,
Status CHAR(1) DEFAULT 'P');
CREATE TABLE Crime_Codes
(Crime_code NUMBER(3,0),
Code_description VARCHAR2(30));
--Altering or modifying TABLES
ALTER TABLE Crimes
MODIFY (Classification DEFAULT 'U');
ALTER TABLE Crimes
ADD (Date_recorded DATE DEFAULT SYSDATE);
ALTER TABLE Prob_officers
ADD (Pager# CHAR(10));
ALTER TABLE Aliases
MODIFY (Alias VARCHAR2(20));