-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.sql
117 lines (117 loc) · 2.28 KB
/
queries.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
110
111
112
113
114
115
116
117
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name varchar(50) NOT NULL,
date_of_birth date NOT NULL,
city varchar(100) NOT NULL,
zipcode VARCHAR(10) NOT NULL,
status SMALLINT NOT NULL
);
CREATE TABLE accounts (
account_id SERIAL PRIMARY KEY,
customer_id integer NOT NULL,
account_type varchar(50),
opening_date date DEFAULT CURRENT_TIMESTAMP,
amount numeric(10, 2) NOT NULL,
status INTEGER NOT NULL DEFAULT 1,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE transactions (
transaction_id SERIAL PRIMARY KEY,
account_id INTEGER NOT NULL,
amount INTEGER NOT NULL,
transaction_type varchar(10) NOT NULL,
transaction_date date NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
INSERT INTO customers(
customer_id,
name,
date_of_birth,
city,
zipcode,
status
)
VALUES (
2000,
'Steve',
'1978-12-15',
'Delhi',
'110075',
1
),
(
2001,
'Arlan',
'1988-05-21',
'Newburgh NY',
'12550',
1
),
(
2002,
'Hardley',
'1988-04-30',
'Englewood NJ',
'07631',
1
),
(
2003,
'Ben',
'1988-01-04',
'Manchester NH',
'03102',
0
),
(
2004,
'Nina',
'1988-04-14',
'Clarkston MI',
'48348',
1
),
(
2005,
'Osman',
'1988-11-08',
'Hyattsville MD',
'20782',
0
);
INSERT INTO accounts(
account_id,
customer_id,
opening_date,
account_type,
amount,
status
)
VALUES (
95470,
2000,
'2020-08-22 10:20:06',
'saving',
6823.23,
1
),
(
95471,
2002,
'2020-08-09 10:27:22',
'checking',
3342.96,
1
),
-- (95472,2001,'2020-08-09 10:35:22', 'saving', 7000, 1),
(
95473,
2003,
'2020-08-09 10:38:22',
'saving',
5861.86,
1
);